Institute Academy

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

SQL OFFSET-FETCH Clause OFFSET excludes the first set of records. OFFSET can only be used with an ORDER BY clause. OFFSET with FETCH NEXT returns a defined window of records. OFFSET with FETCH NEXT is great for building pagination support. The SQL ORDER BY OFFSET syntax The general syntax to exclude first n records is: SELECT column-names FROM table-name ORDER BY column-names OFFSET n ROWS To exclude first n records and return only the next m records: SELECT column-names FROM table-name ORDER BY column-names OFFSET n ROWS FETCH NEXT m ROWS ONLY This will return only record (n + 1) to (n + 1 + m). See example below. The SQL ORDER BY OFFSET Example

SQL SELECT TOP Statement The SELECT TOP statement returns a specified number of records. SELECT TOP is useful when working with very large datasets. Non SQL Server databases use keywords like LIMIT, OFFSET, and ROWNUM. The SQL SELECT TOP syntax The general syntax is: SELECT TOP n column-names FROM table-name SQL SELECT TOP Example SELECT TOP 3 Id , ProductName , UnitPrice , Package FROM Product ORDER BY UnitPrice DESC Id ProductName UnitPrice Package 38 subham Ball 263.50 12 - 75 cl bottles 29 Chayan das 123.79 50 bags x 30 sausgs. 9 Shantanu Biswas 97.00 18 - 500 g pkgs.

SQL ORDER BY Clause SELECT returns records in no particular order. To ensure a specific order use the ORDER BY clause. ORDER BY allows sorting by one or more columns. Records can be returned in ascending or descending order. The SQL ORDER BY syntax The general syntax is: SELECT column-names FROM table-name WHERE condition ORDER BY column-names  or   SELECT column-names1, column-name2, ......... FROM table-name WHERE condition ORDER BY column-names SQL ORDER BY Examples  1) for single column mysql> select  name from person where age>20 order by name; +-----------------+ | name            | +-----------------+ | chayan das      | | monojit         | | sajal           | | sajal           | | shantanu biswas | | sourav biswas   | | subham ball     | | sudip saha      | +-----------------+ 8 rows in set (0.00 sec) 2) for multiple Column mysql> select  name,age from person where age>20 order by age; +--------------

SQL DELETE Statement DELETE permanently removes records from a table. DELETE can delete one or more records in a table. Use the WHERE clause to DELETE only specific records. The SQL DELETE syntax The general syntax is: DELETE table-name To delete specific records append a WHERE clause: DELETE table-name WHERE condition SQL DELETE Examples 1) Normal Delete or without Where Clause delete mysql> delete from person; Query OK, 4 rows affected (0.01 sec) mysql> select *from person; Empty set (0.00 sec) 2) Delete With Where Clause mysql> delete from person where sno='4'; Query OK, 1 row affected (0.01 sec) mysql> select *from person; +-----+--------+------+----------------------+------+------------+ | sno | name | age | email | sex | phone | +-----+--------+------+----------------------+------+------------+ | 1 | subham | 25 | NULL

SQL UPDATE Statement The UPDATE statement updates data values in a database. UPDATE can update one or more records in a table. Use the WHERE clause to UPDATE only specific records. The SQL UPDATE syntax The general syntax is: UPDATE table-name SET column-name = value , column-name = value , ... To limit the number of records to UPDATE append a WHERE clause: UPDATE table-name SET column-name = value , column-name = value , ... WHERE condition SQL UPDATE Examples 1) Normal update or without where condition update mysql> update person set age=25; Query OK, 4 rows affected (0.02 sec) Rows matched: 5 Changed: 4 Warnings: 0 mysql> update person set sex='M'; Query OK, 1 row affected (0.02 sec) Rows matched: 5 Changed: 1 Warnings: 0 mysql> select *from person; +-----+--------+------+----------------------+------+------------+ | sno | name | age | email | sex | pho