SQL Truncate

This command deletes all records from table. Truncate is a DDL command.

Syntax:

Example:

ORDER BY

The ORDER BY clause arranges the table or column in ascending or descending orders. By default, it is ascending order. ‘ASC’ used to indicate ascending order and ‘DESC’ used to indicate descending order.  To appear the resulting records randomly Rand() method used in MySQL.

Example: Order By in Ascending Order

Here, output is the same.

Example:

Example:

 

LIMIT

Limit keyword is used to retrieve finite number of records. Limit can be used with one parameter or two parameters.

  • Limit [one parameter]

This parameter decides number of records to display.

  • Limit [first parameter, second parameter]

Here, first parameter indicates number of records to skip and second parameter indicates the number of records to display.

Example:

Example:

 

GROUP BY

The GROUP BY clause used to club together rows who have same values. Mainly GROUP BY clause used with aggregate functions to produce a summary reports from database.

HAVING CLAUSE

The having clause is used in conjunction with aggregate functions instead of where clause. It does not mean where clause and aggregate functions are not possible in same query. We can use where clause and aggregate function in same query.

Note: Having clause applies on group of records, on the other hand where clause is applied on each single record.

Where and having clause can be used in same query.

Syntax:

Example:

SQL JOINS

Department Table

Teacher Table

Example: Write a query to display department name with their head, and how many teachers are working in respective dept.

TABLE A and TABLE B

JOIN

Join returns the match values from both table with respective of ON condition.

RIGHT JOIN

SQL Right Join returns all the records from right table and matched values also. If there is no matched values in other table, it will return null.

LEFT JOIN

SQL left join returns all the records from left table and matched values. If there is no matched values in other table, it will return null.

VIEW

View is same as a table in a database. Table is a physical entity while view is not. View is a virtual table based on result set of SQL query.

User can create view by writing SQL queries using one or more table which means, view can contain columns/field/attributes from different tables.

Syntax:

Example:

Following output screen shows that a user can perform SQL functions on view when data present in view is from single table. It is also same in case of WHERE and JOIN.

# REPLACE VIEW

In a replace view, user can change view name or contents of view by changing query inside view.

Syntax:

Example:

# DROP VIEW

User can drop view using DROP VIEW command.

Syntax:

Example:

 

NESTED QUERY

Query within another query called nested query. User can refer them outer query and inner query. Inner query named as subquery. Subquery can results in a single row output or multiple row output.  User can put subquery in front of select or where clause.

Syntax:

Example:

Q1 Display teacher names from ‘IT’ department whose salary is greater than average salary of ‘IT’ dept.

SQL SEQUENCE

In MySQL, AUTO_INCREMENT keyword is used to generate a sequence. It generates sequence of numeric values which is used to identify each record uniquely. When this is applied on column, it increments column’s value by 1 while inserting a new record into table.

Auto_increment field should be a key.

Syntax:

Example:

Apply AUTO_INCREMENT after table creation

Syntax:

 Example:

INDEX

User can create index. Data is retrieved very fast from database with the help of index.

User is not able to see index. Index is used only in searching or querying purpose.

Syntax

Example:

DROP INDEX

Syntax:

Example:

Examples

Q1 Find out highest salary from the teacher table on the teacher_id basis.

Ans: select teacher_id from teacher order by teacher_id desc limit 1;// how many record to display

or

Q2 Find out second highest salary from the teacher table on the teacher_id basis.

Ans: select teacher_id from teacher order by teacher_id desc limit 1,1; // skip 1 record,display 1 record

Q3 Find out the third highest salary from the teacher table on the teacher_id basis.

Ans: select teacher_id from teacher order by teacher_id desc limit 2,1;

Q3 Find out the nth highest salary from the teacher table on the teacher_id basis.

Ans: select teacher_id from teacher order by teacher_id desc limit n-1,1;