SQL Queries: Structured Query Language Commands (SQL Queries)

Write the SQL command to create a table with the following columns
Roll Integer
Name Text (which can accept maximum of 15 characters)
Create Table Student (Roll int, name char(15))

Insert the first record as
Roll = 101
Name = Rohan
Insert into Student values(101,'Rohan')

Display the record stored in the table Student.
Select * from Student

Output
Roll name
101 Rohan

Insert the following records in the table Student
Roll Name
201 Rajeeev
151 Rahul
181 Adi Nath
220 Shome Nath
Insert into Student values(201,'Rajeeev')
Insert into Student values(151,'Rahul')
Insert into Student values(181,'Adi Nath')
Insert into Student values(220,'Shome Nath')

Display all the records of the Student table.
Select * from Student

Output
Roll name
101 Rohan
201 Rajeeev
151 Rahul
181 Adi Nath
220 Shome Nath

Display the name column for all the records of the table Student.
Select name from Student

Output
name
Rohan
Rajeeev
Rahul
Adi Nath
Shome Nath

Write the SQL command to create a table with the following columns
Roll Integer
Name Text (which can accept maximum of 15 characters)
Mark1 Integer
Mark2 Integer
Create Table Student (Roll int, name char(15),Mark1 int, Mark2 int)

Insert a record 101,'Adi Nath',89,95 to the Student table.
Insert into Student values(101,'Adi Nath',89,95)

Insert a record 201,'Shome Nath',91,90 to the Student table.
Insert into Student values(201,'Shome Nath',91,90)

Insert a record 150,'Soham',31,90 to the Student table.
Insert into Student values(150,'Soham',31,90)

Insert a record 150,'Rohan',51,Absent to the Student table. Rohan did not appear for the second subject so he was marked absent in Mark2.
Insert into Student values(130,'Rohan',51,NULL)

Display all the records with all the columns of the Student Table.
Select * from Student

Output
Roll Name Mark1 Mark2
101 Adi Nath 89        95
201 Shome Nath 91        90
150 Soham 31        90
130 Rohan 51        NULL

Display all the records with Name and Mark1 column from the Student Table.
Select name, Mark1 from Student

Output
Name Mark1
Adi Nath 89
Shome Nath 91
Soham 31
Rohan 51

Display all records with all columns whose roll>=150 from the Student Table.
Select * from Student where roll>=150

Output
Roll Name Mark1 Mark2
201 Shome Nath 91 90
150 Soham 31 90

Display all records with all columns whose roll>=125 and roll<=175 from the Student Table.
Select * from Student where roll>=125 and roll<=175

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display all records with all columns whose roll>=125 and roll<=175 from the Student Table.
Select * from Student where roll between 125 and 175

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display all records with all columns whose name is Adi Nath from the Student Table.
Select * from Student where name='Adi Nath'

Output
Roll Name Mark1 Mark2
101 Adi Nath 89 95

Display all records with Roll and Name column from the Student Table where Roll>=150.
Select roll,name from Student where roll>=150

Output
Roll Name
201 Shome Nath
150 Soham

Display all records with all columns whose name is 'Rohan' or name is 'Soham'
Select * from Student where name='Rohan' or name='Soham'

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display all records with all columns whose name is 'Rohan' or name is 'Soham'
Select * from Student where name in('Rohan','Soham')

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display all records with all columns whose name is 'Rohan' or name is 'Soham' and did not appear for second subject.
Select * from Student where name in('Rohan','Soham') and Mark2 is NULL

Output
Roll Name Mark1 Mark2
130 Rohan 51 NULL

Display all records with all columns who was absent in Mark1 or Mark2.
Select * from Student where Mark1 is NULL or Mark2 is NULL

Output
Roll Name Mark1 Mark2
130 Rohan 51 NULL

Display all the records with Name and Total Column. The total is calculated by summing the Mark1 and Mark2.
Select Name, Mark1+Mark2 as "Total" from Student

Output
Name Total
Adi Nath 184
Shome Nath 181
Soham 121
Rohan NULL

Display all the records with Name, Mark1, Mark2 and Total who has got more than or equal to 90 in either subject or both the subject.
Select Name, Mark1, Mark2, Mark1+Mark2 as "Total" from Student where Mark1>=90 or Mark2>=90

Output
Name Mark1 Mark2 Total
Adi Nath 89        95        184
Shome Nath 91        90        181
Soham 31        90        121

Display all the records with Name, Mark1, Mark2 and Total who has been marked absent in either Mark1 or Mark2.
Select Name, Mark1, Mark2, Mark1+Mark2 as "Total" from Student where Mark1 is NULL or Mark2 is NULL

Output
Name Mark1 Mark2 Total
Rohan 51 NULL NULL

Display all the records with all the columns who has appeared for both the subject in the examination
select * from Student where Mark1 is not NULL and Mark2 is not NULL

Output
Roll Name Mark1 Mark2
101 Adi Nath 89        95
201 Shome Nath 91        90
150 Soham 31        90

Display all the records with Roll and Name columns who has appeared for both the subject in the examination
select Roll,Name from Student where Mark1 is not NULL and Mark2 is not NULL

Output
Roll Name
101 Adi Nath
201 Shome Nath
150 Soham

Display all the records with Roll and Name columns who has not appeared for both the subject in the examination
select Roll,Name from Student where Mark1 is NULL or Mark2 is NULL

Output
Roll Name
130 Rohan

Display all the records with all the columns whose name starts with "S".
select * from Student where name like 'S%'

Output
Roll Name Mark1 Mark2
201 Shome Nath 91 90
150 Soham 31 90

Display all the records with all the columns whose name ends with "th".
select * from Student where name like '%th'

Output
Roll Name Mark1 Mark2
101 Adi Nath 89 95
201 Shome Nath 91 90

Display all the records with all the columns whose second character of name is "o".
select * from Student where name like '_o%'

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display all the records with all the columns which contains the letter "h" anywhere in the name.
select * from Student where name like '%h%'

Output
Roll Name Mark1 Mark2
101 Adi Nath 89        95
201 Shome Nath 91        90
150 Soham 31        90
130 Rohan 51        NULL

Display all the records with all the columns which contains five letters and less than five letters in name.
select * from Student where name like '_ _ _ _ _'

Output
Roll Name Mark1 Mark2
150 Soham 31 90
130 Rohan 51 NULL

Display the distinct name present in the table Student.
select distinct(name) from Student

Output
Name
Adi Nath
Rohan
Shome Nath
Soham

Display the distinct Mark2 present in the table Student.
select distinct(Mark2) from Student

Output
Mark2
NULL
90
95

Display all the records with all columns in the ascending order of Roll.
select * from Student order by Roll

Output
Roll Name Mark1 Mark2
101 Adi Nath 89        95
130 Rohan 51        NULL
150 Soham 31        90
201 Shome Nath 91        90

Display all the records with all columns in the descending order of Name lexicographically.
select * from Student order by Name desc

Output
Roll Name Mark1 Mark2
150 Soham 31   90
201 Shome Nath 91        90
130 Rohan 51        NULL
101 Adi Nath 89        95

Display all the records with all columns in the descending order of Mark1+Mark2
select * from Student order by Mark1+Mark2 desc

Output
Roll Name Mark1 Mark2
101 Adi Nath 89        95
201 Shome Nath 91        90
150 Soham 31        90
130 Rohan 51        NULL

Display all the records with all columns in the ascending order of Mark1+Mark2 who has appeared for both the subjects.
select * from Student where Mark1 is not NULL and Mark2 is not NULL order by Mark1+Mark2

Output
Roll Name Mark1 Mark2
150 Soham 31        90
201 Shome Nath 91        90
101 Adi Nath 89        95

Count the Number of Records present in the table Student.
select count(*) as "Number of Students" from Student

Output
Number of Students
4

Count the number of Students who have appeared for the first subject and the second subject from the table Student.
select count(Mark1) as "Mark1", count(Mark2) as "Mark2" from Student

Output
Mark1 Mark2
4 3

Display the sum of mark1 and sum of mark2 from the table Student.
select sum(Mark1), sum(Mark2) from Student

Output
sum(Mark1) sum(Mark2)
262 275

Display the average of mark1 and average of mark2 from the table Student.
select avg(Mark1), avg(Mark2) from Student

Output
avg(Mark1) avg(Mark2)
65 91

Display the maximum of mark1 and maximum of mark2 from the table Student.
select max(Mark1), max(Mark2) from Student

Output
max(Mark1) max(Mark2)
91 95

Display the minimum of mark1 and minimum of mark2 from the table Student.
select min(Mark1), min(Mark2) from Student

Output
min(Mark1) min(Mark2)
31 90

Display the sum, average, maximum and minimum of mark1 from the table Student.
select sum(Mark1), avg(Mark1), max(Mark1), min(Mark1) from Student

Output
sum(Mark1) avg(Mark1) max(Mark1) min(Mark1)
262 65 91 31

Create a table with the following columns

Empno Integer Primary Key,

Name Character (20) Not Null,

Department Character(15),

Salary Integer

create table Employee(Empno Int Primary Key, Name Char(20) Not Null,Department Char(15),Salary Int)

Insert the following records in the Employee Table

101,'Rahul','Comp',20000

102,'Sohan','Prod',21000

103,'Anil','Comp',22000

104,'S.Nath','Acct',21000

105,'Barun','Comp',20500

106,'Ramesh','Prod',20000

insert into Employee values(101,'Rahul','Comp',20000); insert into Employee values(102,'Sohan','Prod',21000); insert into Employee values(103,'Anil','Comp',22000); insert into Employee values(104,'S.Nath','Acct',21000); insert into Employee values(105,'Barun','Comp',20500); insert into Employee values(106,'Ramesh','Prod',20000);

Display all the records from the Employee table

Select * From Employee

Output

Empno Name Department Salary 101 Rahul Comp 20000 102 Sohan Prod 21000 103 Anil Comp 22000 104 S.Nath Acct 21000 105 Barun Comp 20500 106 Ramesh Prod 20000

Display the distinct department from the table Employee

Select Distinct(Department) From Employee

Output

Department Comp Prod Acct

Display the distinct salary from the table Employee

Select Distinct(Salary) From Employee

Output

Salary 20000 21000 22000 20500

Display all the records from the Employee table in the ascending order of Salary

Select * From Employee Order By Salary

Output Empno Name Department Salary 101 Rahul Comp 20000 106 Ramesh Prod 20000 105 Barun Comp 20500 102 Sohan Prod 21000 104 S.Nath Acct 21000 103 Anil Comp 22000

Display all the records from the Employee table in the descending order of Name

Select * From Employee Order By Name Desc

Output Empno Name Department Salary 102 Sohan Prod 21000 104 S.Nath Acct 21000 106 Ramesh Prod 20000 101 Rahul Comp 20000 105 Barun Comp 20500 103 Anil Comp 22000

Display the Department and the sum of Salary from the table Employee Department wise

Select Department,Sum(Salary) From Employee Group By Department

Output Department Sum(Salary) Acct 21000 Comp 62500 Prod 41000

Display the Department and the average of Salary from the table Employee Department wise

Select Department,Avg(Salary) From Employee Group By Department

Output Department Avg(Salary) Acct 21000.0 Comp 20833.3333333333 Prod 20500.0

Display the Department and the maximum Salary from the table Employee Department wise

Select Department,Max(Salary) From Employee Group By Department

Output Department Max(Salary) Acct 21000 Comp 22000 Prod 21000

Display the Department and the minimum Salary from the table Employee Department wise

Select Department,Min(Salary) From Employee Group By Department

Output Department Min(Salary) Acct 21000 Comp 20000 Prod 20000

Display the Department, maximum and the minimum Salary from the table Employee Department wise

Select Department,Max(Salary), Min(Salary) From Employee Group By Department

Output Department Max(Salary) Min(Salary) Acct 21000 21000 Comp 22000 20000 Prod 21000 20000

Display the Department, maximum, minimum and the average Salary from the table Employee Department wise

Select Department,Max(Salary), Min(Salary),Avg(Salary) From Employee Group By Department

Output Department Max(Salary) Min(Salary) Avg(Salary) Acct 21000 21000 21000.0 Comp 22000 20000 20833.3333333333 Prod 21000 20000 20500.0

Display the Department, and the number of employees under each department from the table Employee

Select Department,Count(*) From Employee Group By Department

Output Department Count(*) Acct 1 Comp 3 Prod 2

Display the Department, and the number of employees under each department from the table Employee whose number of employees is >=2

Select Department,Count(*) From Employee Group By Department Having Count(*)>=2

Output Department Count(*) Comp 3 Prod 2

Display all the records of the Comp department in the ascending order of name from the table Employee

Select * From Employee Where Department = 'Comp' Order By Name

Output Empno Name Department Salary 103 Anil Comp 22000 105 Barun Comp 20500 101 Rahul Comp 20000

Display all the records of the Comp department or the Acct department in the descending order of name from the table Employee

Select * From Employee Where Department In ('Comp','Acct') Order By Name desc

Output Empno Name Department Salary 104 S.Nath Acct 21000 101 Rahul Comp 20000 105 Barun Comp 20500 103 Anil Comp 22000

Display all the records whose salary is more than the average salary of the company

select * from Employee where salary>=(Select avg(salary) from Employee)

Output

Empno Name Department Salary 101 Rahul Comp 20000 103 Anil Comp 22000 104 S.Nath Acct 21000

Count the number of records who is getting more than the average salary of the company

select count(*) from Employee where salary>=(Select avg(salary) from Employee)

Output

Count(*)

Display all the records whose salary is more than the average salary of the company and belongs to Comp department

select * from Employee where salary>=(Select avg(salary) from Employee) and Department='Comp' Output Empno Name Department Salary 103 Anil Comp 22000

Display all the records whose salary is more than the average salary of the company and does not belong to Comp department

select * from Employee where salary>=(Select avg(salary) from Employee) and Department != 'Comp'

Output

Empno Name Department Salary 102 Sohan Prod 21000 104 S.Nath Acct 21000

Display all the records who is getting the highest salary in the company

select * from Employee where salary=(Select max(salary) from Employee) Output Empno Name Department Salary 103 Anil Comp 22000

Display all the records who is getting the lowest salary in the company

select * from Employee where salary=(Select min(salary) from Employee)

Output Empno Name Department Salary 101 Rahul Comp 20000 106 Ramesh Prod 20000

Display all the records who is getting the highest salary in the company of the Comp department

select * from Employee where salary=(Select max(salary) from Employee) and department='Comp'

Output Empno Name Department Salary 103 Anil Comp 22000

Display all the records who is getting the highest salary in the company of the Comp department or of the Prod department

select * from Employee where salary=(Select max(salary) from Employee) and department in('Comp','Prod')

Output

Empno Name Department Salary

103 Anil Comp 22000

Display all the records who is getting the highest salary in the company and does not belong to Acct department or Prod department

select * from Employee where salary=(Select max(salary) from Employee) and department not in('Acct','Prod')

Output

Empno Name Department Salary

103 Anil Comp 22000

Display all the records who is getting the lowest salary in the company and does not belong to Acct department or Prod department

select * from Employee where salary=(Select min(salary) from Employee) and department not in('Acct','Prod')

Output

Empno Name Department Salary

101 Rahul Comp 20000

Display the name and department of all those who is getting the lowest salary in the company and does not belong to Acct department or Prod department

select name,department from Employee where salary=(Select min(salary) from Employee) and department not in('Acct','Prod')

Output

Name Department

Rahul Comp

Display all the records of the company who is getting second highest salary in the company

select * from Employee where salary=(Select max(salary) from Employee where salary<(Select max(salary) from Employee))

Output

Empno Name Department Salary 102 Sohan Prod 21000 104 S.Nath Acct 21000

SQL Queries

Wednesday, June 29, 2022

Structured Query Language Commands (SQL Queries)

No comments:

Post a Comment

Structured Query Language Commands (SQL Queries)

Report Abuse