[DML] SQL Syntax

1. select syntax:

• select * from {table_name};
• select col1,col2,.. from {tablename};

2. select distinct statement:

• select distinct col1,col2.. from {table_name};
• select count(distinct col_name) from {table_name};
• select Count(*) AS distinctelement from(select distinct col_name from table_name);

3. where clause:

• select col_name,col_name2 from table_name where {condition};
• select * from table_name where {condition};
• NOTE: single or double quote in text values and no quote in numeric values.
• NOTE: where clause used in select, delete, update etc. statements.
• NOTE: used symobl are <,>,=,>=,<=,<> or ! (not equal),BETWEEN,LIKE,IN.

4. sql AND, OR, NOT:

• select * or colname1,colname2.. from table_name where condition AND condition AND condition..;
• select * or colname1,colname2.. from table_name where condition OR condition OR condition..;
• select * or colname1,colname2.. from table_name where NOT condition;
• select * or colname1,colname2.. from table_name where condition AND (condition OR condition);
• select * or colname1,colname2.. from table_name where NOT condition AND NOT condition;

5. order by:

• select * or colname1,colname2,.. from table_name order by colname ASC or DESC;
• select * or colname1,colname2,.. from table_name order by colname ASC,colname DESC;
• select * or colname1,colname2,.. from table_name order by colname;
• select * or colname1,colname2,.. from table_name order by colname,colname2;

6. insert into:

• insert into table_name(col_name,col_name,..) values("string_value",int_value,..,value);
• insert into table_name values(value1,value2,value3,...);

7. sql IS NULL & IS NOT NULL:

• select * from table_name where col_name IS NULL;
• select * or col_name,.. from table_name where col_name IS NOT NULL;

8. update statement:

• update table_name set col_name=value1,col_name2=value2,.. where col_name=value;
• update table_name set col_name=value;
• NOTE: overwrite all the previous values.

9. delete statement:

• delete from table_name where {condition};
• delete from table_name;
• NOTE: Delete entire data of the table.

10. select top:

• mysql (limit): select colname,.. or * from table_name where {condition} LIMIT {number};
• mysql (limit): select * or colname(s) from table_name order by colname ASC | DESC LIMIT {number};
• oracle (fetch): SELECT column_name(s) FROM table_name ORDER BY column_name(s) FETCH FIRST number ROWS ONLY;

11. min() and max():

• select min(col_name) from table_name where {condition};
• select max(col_name) from table_name where {condition};
• select min(col_name) AS min_column from table_name;

12. count(), avg(), sum():

• count: select count(col_name) from table_name where {condition};
• average: select avg(col_name) from table_name where {condition}; //only for numeric column.
• sum: select sum(col_name) from table_name where {condition}; //numeric column.

13. like operator:

• LIKE Operator Description ( All expressions )

• WHERE CustomerName LIKE 'a%' Finds any values that start with "a"

• WHERE CustomerName LIKE '%a' Finds any values that end with "a"

• WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position

• WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position

• WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length

• WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length

• WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"

• select col_name,.. from table_name where col_name LIKE '{expression}';

• select col_name,.. from table_name where col_name NOT LIKE '{expression}';

14. wildcard characters: (mysql)

• select * or col_name,.. from table_name where col_name LIKE '{expressions}';