Institute Academy

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

SQL SELECT MIN, MAX Statement SELECT MIN returns the minimum value for a column. SELECT MAX returns the maximum value for a column. The SQL SELECT MIN and MAX syntax The general MIN syntax is: SELECT MIN ( column-name ) FROM table-name The general MAX syntax is: SELECT MAX ( column-name ) FROM table-name SQL SELECT MAX and MIN Examples 1) Example of MIN statement mysql> select min(age) from person; +----------+ | min(age) | +----------+ |       18 | +----------+ 1 row in set (0.00 sec) 2) Example of MAX Statement mysql> select max(age) from person; +----------+ | max(age) | +----------+ |       26 | +----------+ 1 row in set (0.00 sec)

SQL SELECT DISTINCT Statement SELECT DISTINCT returns only distinct (different) values. SELECT DISTINCT eliminates duplicate records from the results. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc. DISTINCT operates on a single column. DISTINCT for multiple columns is not supported. The SQL SELECT DISTINCT syntax The general syntax is: SELECT DISTINCT column-nam FROM table-name Can be used with COUNT and other aggregates SELECT COUNT ( DISTINCT column-name ) FROM table-name SQL SELECT Examples  1) The general syntax mysql> select distinct name from person; +-----------------+ | name            | +-----------------+ | subham ball     | | jit             | | sajal           | | sourav biswas   | | chayan das      | | shantanu biswas | | sudip saha      | | debo            | | NULL            | | mono            | | monojit         | +-----------------+ 11 rows in set (0.00 sec) 2) with COUNT a