Institute Academy

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

SQL INSERT INTO Statement The INSERT INTO statement is used to add new data to a database. The INSERT INTO statement adds a new record to a table. INSERT INTO can contain values for some or all of its columns. INSERT INTO can be combined with a SELECT to insert records. The SQL INSERT INTO syntax The general syntax is: 1) insert single row at a time on table INSERT INTO table-name (column-names) VALUES (values); or INSERT table-name (column-names) VALUES (values);  2) insert multiple row at a time INSERT INTO table-name (column-names) VALUES (values),(values),(values),.......; or INSERT table-name (column-names) VALUES (values),(values),(values),(values),.......;   SQL INSERT INTO Example 1) single row at a time mysql> insert into person(sno,name,age,sex) values ('3', 'chayan', 20, 'M'); Query OK, 1 row affected (0.01 sec) mysql> select *from person -> ; +-----+--------+------+------+ | sno | n

SQL WHERE Clause To limit the number of rows use the WHERE clause. The WHERE clause filters for rows that meet certain criteria. WHERE is followed by a condition that returns either true or false. WHERE is used with SELECT, UPDATE, and DELETE. The SQL WHERE syntax A WHERE clause with a SELECT statement: SELECT column-names FROM table-name WHERE condition A WHERE clause with an UPDATE statement: UPDATE table-name SET column-name = value WHERE condition A WHERE clause with a DELETE statement: DELETE table-name WHERE condition   SQL WHERE Clause Examples 1) with a select statement mysql> select name from person where age=26; +-----------------+ | name            | +-----------------+ | shantanu biswas | | sudip saha      | +-----------------+ 2 rows in set (0.00 sec) 2) with an update statement mysql> update person set sex='M' where age=25; Query OK, 0 rows affected (0.00 sec) Rows matched: