This command deletes all records from table. Truncate is a DDL command.
Syntax:
TRUNCATE table table_name;
Example:
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 ;
Here, output is the same.
Example:
select *
from teacher
order by teacher_name desc;
Example:
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;
Example:
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;
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)
Example:
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;
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);
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;
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;
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;
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;
Example:
Create view view_student as
Select student_id,student_name,dept_name
From student
Where student_id<10;
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;
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;
# DROP VIEW
User can drop view using DROP VIEW command.
Syntax:
Drop view
view_name;
Example:
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;
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’);
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);
Example:
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;
Example:
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);
Example:
create index stud_index
on student (student_name);
DROP INDEX
Syntax:
Alter table table_name
Drop index index_name;
Example:
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
or
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 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;
