1.
SQL Queries
A) Consider the following Student table.
Table : STUDENT
RollNo Name Age Department Fee Sex
1 Sunil 24 Science 50000 M
2 Rakesh 21 Commerce 40000 F
3 Santosh 22 Commerce 40000 M
4 Sudha 25 Humanities 35000 F
5 Anil 22 Commerce 40000 M
Create table student ( RollNo integer, Name varchar(10), age integer, Department
varchar(15), fee integer, Sex char(1));
Insert into student values (1, ‘Sunil’, 24, ‘Science’, 50000, ‘M’);
Insert into student values (2, ’Rakesh’, 21, ’Commerce’ , 40000, ’F’);
Insert into student values (3, ’Santosh’, 22, ’Commerce’, 40000, ’M’);
Insert into student values (4, ’Sudha’, 25, ’Humanities’, 35000, ’F’);
Insert into student values (5, ’Anil’, 22, ’Commerce’, 40000, ’M’);
Write SQL commands for the following statements.
i. To show all information about the students of Commerce department.
Select * from student where department=”Commerce”;
RollNo Name Age Department Fee Sex
2 Rakesh 21 Commerce 40000 F
3 Santosh 22 Commerce 40000 M
5 Anil 22 Commerce 40000 M
ii. To display RollNo and Name of students whose fee is less than 40000.
Select RollNo, Name from student where fee>40000;
RollNo Name
1 Sunil
iii. To list names of all students with their fee in ascending order.
Select name from student order by fee asc;
Name
Sunil
Rakesh
Santosh
Anil
Sudha
iv. To count the number of student with Age < 23.
Select count(RollNo) from student where age <23;
Count(Name)
5
v. To delete the details of student whose roll no is 5.
Delete from student where RollNo=5;
B) Consider the following Employee table.
Table : EMPLOYEE
No Name Age Department Salary Sex
1 Pankaj 54 Engg 120000 M
2 Shalini 41 Estbl 200000 F
3 Sanjay 32 Engg 350000 M
4 Sudha 25 Science 470000 F
5 Rakesh 32 Engg 250000 M
Create table employee ( No integer, Name varcher(10), age integer, department
varchar(15), salary integer, Sex char(1));
Insert into Employee values (1, ‘Pankaj’, 54, ‘Engg’, 120000, ‘M’);
Insert into Employee values (2, ‘Shalini’, 41, ‘Estbl’, 200000, ‘F’);
Insert into Employee values (3, ‘Sanjay’, 32, ‘Engg’, 350000, ‘M’);
Insert into Employee values (4, ‘Sudha’, 25, ‘Science’, 470000, ‘F’);
Insert into Employee values (5, ‘Rakesh’, 32, ‘Engg’, 250000 ,’M’);
Write SQL commands for the following statements.
i. To show all information about the employers of Engg Department.
Select * from employee where Department = ‘Engg’;
No Name Age Department Salary Sex
1 Pankaj 54 Engg 120000 M
3 Sanjay 32 Engg 350000 M
5 Rakesh 32 Engg 250000 M
ii. To display Employee’s name, Salary, Age for male employees only.
SELECT name, salary, age FROM employee WHERE gender = 'M';
Name Salary Age
Pankaj 120000 54
Sanjay 350000 32
Rakesh 250000 32
iii. To display the name and salary of those employees whose age is above 40.
SELECT name, salary FROM employee WHERE age > 40;
Name Salary
Pankaj 120000
Shalini 200000
iv. To display sum of salary whose age is below 40.
SELECT SUM(salary) FROM employee WHERE age < 40;
Sum(Salary)
720000
v. To rename the column Department to Branch.
Alter table employee change Department Branch varchar(15);
C) Consider the following Computer table.
Table : COMPUTER
I_ID Itemname Manufacturer Price
PC01 Personal Computer ABC 35000
PC05 Laptop ABC 55000
PC03 Mouse XYZ 2000
PC06 Personal Computer COMP 37000
LC03 Laptop PQR 57000
Create table computer (I_ID char(4), Itemname varchar(20), Manufacturer varchar(15), Price
integer);
Insert into computer values (‘PC01’, ‘Personal Computer’, ‘ABC’, 35000);
Insert into computer values (‘PC05’, ‘Laptop’, ‘ABC’, 55000);
Insert into computer values (‘PC03’, ‘Mouse ‘, ‘XYZ’, 2000);
Insert into computer values (‘PC06’, ‘Personal Computer’, ‘COMP’, 37000);
Insert into computer values (‘LC03’, ‘Laptop’, ‘PQR’, 57000);
Write SQL commands for the following statements.
i. To display the details of items whose price is in the range 35000 to 60000.
Select * from computer where price between 35000 and 60000;
I_ID Itemname Manufacturer Price
PC01 Personal Computer ABC 35000
PC05 Laptop ABC 55000
PC06 Personal Computer COMP 37000
LC03 Laptop PQR 57000
ii. To display distinct manufacturer name.
Select distinct Itemname from computer;
Itemname
Personal Computer
Laptop
Mouse
iii. To change the Item Id from LC03 to PC07.
Update table computer set I_ID = ‘PC07’ where I_ID= ‘LC03’;
iv. To display details of items whose manufacturer name is XYZ.
Select * from computer where manufacturer= “XYZ”;
I_ID Itemname Manufacturer Price
PC03 Mouse XYZ 2000
v. To delete the column price.
Alter table Computer drop column price;
