Institute Academy

SQL GROUP BY Clause The GROUP BY clause groups records into summary rows. GROUP BY returns one records for each group. GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. GROUP BY can group by one or more columns. The SQL GROUP BY syntax The general syntax is: SELECT column-name FROM table-name WHERE condition GROUP BY column-names The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-name ORDER BY column-names SQL GROUP BY Examples  1) Example of Group By Clause mysql> select name from person where age>20 group by name; +-----------------+ | name            | +-----------------+ | chayan das      | | monojit         | | sajal           | | shantanu biswas | | sourav biswas   | | subham ball     | | sudip saha      | +-----------------+ 7 rows in set (0.00 sec) 2) Example of Group By Clause with  ORDER BY mysql> select

SQL IS NULL Clause NULL is a special value that signifies 'no value'. Comparing a column to NULL using the = operator is undefined. Instead, use WHERE IS NULL or WHERE IS NOT NULL. The SQL WHERE IS NULL syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name IS NULL The general not null syntax is: SELECT column-names FROM table-name WHERE column-name IS NOT NULL SQL WHERE IS NULL Examples  1) Example of is null Clause  mysql> select name from person where age is null; +------+ | name | +------+ | debo | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) 2) Example of Is not null  mysql> select name from person where age is not null; +-----------------+ | name            | +-----------------+ | subham ball     | | jit             | | sajal           | | sourav biswas   | | sajal           | | chayan das      | | shantanu biswas | | sudip saha      | | mono            | | m

SQL WHERE LIKE Statement WHERE LIKE determines if a character string matches a pattern. Use WHERE LIKE when only a fragment of a text value is known. WHERE LIKE supports two wildcard match options: % and   _ . The SQL WHERE LIKE syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name LIKE value Optional Wildcard characters allowed in 'value' are % (percent) and _ (underscore).   A % matches any string with zero or more characters.   An _ matches any single character. SQL WHERE LIKE Examples  1) any string with zero or more characters(%)  mysql> select name from person where name like 's%'; +-----------------+ | name            | +-----------------+ | subham ball     | | sajal           | | sourav biswas   | | sajal           | | shantanu biswas | | sudip saha      | +-----------------+ 6 rows in set (0.00 sec) 2) matches any single character(_) mysql> select

SQL WHERE IN Clause WHERE IN returns values that matches values in a list or sub-query. WHERE IN is a shorthand for multiple OR conditions The SQL WHERE IN syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name IN ( values ) SQL WHERE IN Examples  mysql> select name from person where age in(26); +-----------------+ | name            | +-----------------+ | shantanu biswas | | sudip saha      | +-----------------+ 2 rows in set (0.00 sec)

SQL WHERE BETWEEN Clause WHERE BETWEEN returns values that fall within a given range. WHERE BETWEEN is a shorthand for >= AND <=. BETWEEN operator is inclusive: begin and end values are included. The SQL WHERE BETWEEN syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name BETWEEN value1 AND value2 SQL WHERE BETWEEN Examples  1) Example of  Where Between Clause mysql> select name from person where age between 21 and 25; +---------------+ | name          | +---------------+ | subham ball   | | sajal         | | sourav biswas | | sajal         | | chayan das    | | monojit       | +---------------+ 6 rows in set (0.01 sec) 2) Example of  Where Between Clause with Distinct Clause mysql> select distinct(name) from person where age between 21 and 25; +---------------+ | name          | +---------------+ | subham ball   | | sajal         | | sourav biswas | | chayan

SQL WHERE AND, OR, NOT Clause WHERE conditions can be combined with AND, OR, and NOT. A WHERE clause with AND requires that two conditions are true. A WHERE clause with OR requires that one of two conditions is true. A WHERE clause with NOT negates the specified condition. The WHERE with AND, OR, NOT syntax A WHERE clause with AND: SELECT column-names FROM table-name WHERE condition1 AND condition2 A WHERE clause with OR: UPDATE table-name SET column-name = value WHERE condition1 OR condition2 A WHERE clause with NOT: DELETE table-name WHERE NOT condition SQL WHERE with AND, OR, and NOT Examples  1) Example of AND Clause mysql> select name from person where age>20 and Sex='M'; +-----------------+ | name            | +-----------------+ | subham ball     | | sajal           | | sourav biswas   | | sajal           | | chayan das      | | shantanu biswas | +-----------------+ 6 rows in set

SQL SELECT COUNT, SUM, AVG SELECT COUNT returns a count of the number of data values. SELECT SUM returns the sum of the data values. SELECT AVG returns the average of the data values. The SQL SELECT COUNT, SUM, and AVG syntax The general COUNT syntax is: SELECT COUNT ( column-name ) FROM table-name The general SUM syntax is: SELECT SUM ( column-name ) FROM table-name The general AVG syntax is: SELECT AVG ( column-name ) FROM table-name SQL SELECT COUNT, SUM, and AVG Examples   1) example of Count Statement mysql> select count(name) from person; +-------------+ | count(name) | +-------------+ |          11 | +-------------+ 1 row in set (0.00 sec) 2) Example of Sum Statement mysql> select sum(age) from person; +----------+ | sum(age) | +----------+ |      226 | +----------+ 1 row in set (0.00 sec) 5) Example of Sum Statement with Distinct Clause mysql> select sum(distinct age