SQL Truncate - Tutorial And Example

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

Syntax:


TRUNCATE table table_name;

TRUNCATE table table_name;

Example:


Truncate table teacher;

Truncate table teacher;

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


select *
from teacher
order by teacher_name ;
or
select *
from teacher
order by teacher_name asc ;

select *

from teacher

order by teacher_name ;

select *

from teacher

order by teacher_name asc ;

Here, output is the same.

Example:


select *
from teacher
order by teacher_name desc;

select *

from teacher

order by teacher_name desc;

Example:


Select Teacher_id
from teacher
order by rand();

Select Teacher_id

from teacher

order by rand();

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:


Select *
from teacher
limit 5;

Select *

from teacher

limit 5;

Example:


Select *
from teacher
limit 2,3;

Select *

from teacher

limit 2,3;

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.


select dept_name, count(*)
from teacher
group by dept_name;

select dept_name, count(*)

from teacher

group by dept_name;

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:


Select function_name(column_name)
From table_name
Group by (column_name)
Having (condition)

Select function_name(column_name)

From table_name

Group by (column_name)

Having (condition)

Example:


select count(teacher_id)
from teacher
group by dept_name
having dept_name='it';

select count(teacher_id)

from teacher

group by dept_name

having dept_name='it';


Select dept_name, count(teacher_id)
from teacher
group by dept_name
having count(teacher_id)>2;

Select dept_name, count(teacher_id)

from teacher

group by dept_name

having count(teacher_id)>2;

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.


select department.dept_name,dept_head,count(teacher_name)
from department join teacher
on department.dept_name=teacher.dept_name
group by(dept_name);

select department.dept_name,dept_head,count(teacher_name)

from department join teacher

on department.dept_name=teacher.dept_name

group by(dept_name);

TABLE A and TABLE B

JOIN

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


Select *
from A JOIN B
ON A.no=B.no;

Select *

from A JOIN B

ON A.no=B.no;

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.


Select *
From A  RIGHT JOIN B
ON A.no=B.no;

Select *

From A RIGHT JOIN B

ON A.no=B.no;

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.


Select *
from A LEFT JOIN B
ON A.no=B.no;

Select *

from A LEFT JOIN B

ON A.no=B.no;

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:


create view view_name as
Select column1,column2,….
From table_name
Where condition;

create view view_name as

Select column1,column2,….

From table_name

Where condition;

Example:


Create view view_student as
Select student_id,student_name,dept_name
From student
Where student_id<10;

Create view view_student as

Select student_id,student_name,dept_name

From student

Where student_id<10;


select *
from view_student;

select *

from view_student;

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:


Create or Replace view view_name as
Select column1,column2,……
From student
Where condition;

Create or Replace view view_name as

Select column1,column2,……

From student

Where condition;

Example:


Create or Replace view view_student as
Select student_id,student_name,dept_name,classyear
From student
Where student_id between 15 and 35;

Create or Replace view view_student as

Select student_id,student_name,dept_name,classyear

From student

Where student_id between 15 and 35;

# DROP VIEW

User can drop view using DROP VIEW command.

Syntax:


Drop view
view_name;

Drop view

view_name;

Example:


Drop view
view_student;

Drop view

view_student;

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:


Select columns
From table_name
Where condition In( select column_name
From table_name
Where condition);
Select column_name =(select column_name
From table_name
Where condition),columns
From table_name
Where condition;

Select columns

From table_name

Where condition In( select column_name

From table_name

Where condition);

Select column_name =(select column_name

From table_name

Where condition),columns

From table_name

Where condition;

Example:

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


Select teacher_name
From teacher
Where salary>(select avg(salary)
From teacher where dept_name=’IT’);

Select teacher_name

From teacher

Where salary>(select avg(salary)

From teacher where dept_name=’IT’);

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:


Create table table_name
(column1 data type AUTO_INCREMENT,
column2 data type,…,
column1 Primary key);

Create table table_name

(column1 data type AUTO_INCREMENT,

column2 data type,…,

column1 Primary key);

Example:


create table samples
(sample_id int auto_increment primary key,
sample_name varchar(20),
quntity int,price float);

create table samples

(sample_id int auto_increment primary key,

sample_name varchar(20),

quntity int,price float);

Apply AUTO_INCREMENT after table creation

Syntax:


Alter table table_name
modify column
colmn_name data type primary key auto_increment;

Alter table table_name

modify column

colmn_name data type primary key auto_increment;

Example:


alter table student
modify column
student_id int auto_increment ;

alter table student

modify column

student_id int auto_increment ;

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


Create Index index_name
On table_name (column_name);

Create Index index_name

On table_name (column_name);

Example:


create index stud_index
on student (student_name);

create index stud_index

on student (student_name);

DROP INDEX

Syntax:


Alter table table_name
Drop index index_name;

Alter table table_name

Drop index index_name;

Example:


alter table student
drop index stud_index;

alter table student

drop index stud_index;

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


select max(teacher_id) from teacher;

select max(teacher_id) from teacher;

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 n^th 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;