MySQL Worksheet-4

 

Q1. Write SQL queries on the basis of following table.

Relation :  Student
Name	Class	Fee	Gender	DOB
Rahul	XII	1200	M	2005-02-01
Mehul	XII	1200	M	2004-12-11
Manisha	XI	1050	F	2006-10-12
Sujoy	XI	1050	M	NULL
Sonakshi	XII	1200	F	2005-09-19
Suman	X	950	F	2008-06-16

i. Display all records from table student.
Ans. Select * from student;

ii. List names of all students from table student.
Ans. Select name  from student;

iii. List name and class of all students from table student.
Ans. Select name,class  from student;

iv. List all students studying in class XII.
Ans. Select * from student where class=’XII’;

v. List names of male students .
Ans. Select name  from student where gender=’M’;

vi. Show information of students having fee more than 1000.
Ans. Select * from student where fee>1000;

vii. Show name, fee and class of students having fee below or equal to 1100.
Ans. Select name,fee,class from student where fee<=1100;

viii. View all records in ascending order of student’s name.
Ans. Select * from student order by name;

ix. View name and fee of all students in descending order of fee.
Ans. Select name,fee from student order by fee desc;

x. List male students in descending order of their class.
Ans. Select * from student where gender=’M’ order by class;

Q2. Write SQL queries on the basis of following table.

Relation : Teacher
TName	Incharge	Salary	Gender	Dateofjoin
Rahul	XII	12000	M	2015-02-11
Mehul	XII	12500	M	2014-12-21
Manisha	XI	13500	F	2016-10-22
Sujoy	XI	21050	M	2016-10-22
Sonakshi	XII	21000	F	2015-08-19
Suman	X	16950	F	2018-06-26

i. Show information of teachers having salary more than 20000.
Ans. Select * from teacher where salary>20000;

ii. List teachers who are incharge of classes X or XII.
Ans. Select * from teacher where incharge=’X’ or incharge=’XII’;
OR
Select * from teacher where incharge in (‘X’,’XII’);

iii List teachers teaching class X or having salary more than 10000.
Ans. Select * from teacher where incharge=’X’ or salary>10000;

iv. View those records  where salary is between 10000 and 20000 .
Ans. Select * from teacher where salary between 10000 and 20000;
OR
Select * from teacher where salary>=10000 and  salary<=20000;

v. List female teachers having salary 21000 .
Ans. Select * from teacher where gender=’F’ and salary=21000;

vi. List different classes available in above table (no duplicates) .
Ans. Select distinct incharge from teacher;

vii. List salary and names of teachers having salary less than 15000 or more than 20000 .
Ans. Select salary,tname from teacher where salary<15000 or salary>20000;

viii. List name, salary and annual salary(salary*12) of all teachers.
Ans. Select tname, salary, salary*12  from teacher;

ix. List name of all teachers teaching class XII.
Ans. Select tname from teacher where incharge=’XII’;

x. List name and  salary of all teachers who joined before ‘2016-10-01’.
Ans. Select tname, salary from teacher where dateofjoin<‘2016-10-01’;

Q3. Write SQL queries on the basis of following table.

Relation : Hospital
PName	Fee	Gender	Dateofvisit
Ramesh	200	M	2020-02-11
Mohnish	250	M	2019-12-22
Muskan	350	F	2019-11-22
Sunil	250	M	2018-12-02
Sonam	null	F	2019-01-19
Sahil	16950	F	2019-02-26

i. Show information of  patients who visited  after ‘2020-01-01’.
Ans. Select * from hospital where dateofvisit>’2020-01-01′;

ii. Show information of  patients who visited  between  ‘2018-12-01’ and ‘2019-12-01’.
Ans. Select * from hospital where dateofvisit between ‘2018-12-01’ and ‘2019-12-01’;
OR
Select * from hospital where dateofvisit>= ‘2018-12-01’ and dateofvisit<= ‘2019-12-01’;

iii. Show information of  patients who visited  before  ‘2018-12-01′ or after 2019-12-01’.
Select * from hospital where dateofvisit< ‘2018-12-01’ or dateofvisit> ‘2019-12-01’;

iv. List those patients whose name starts with alphabet ‘M’.
Select * from hospital where pname like ‘M%’;

v. List those patients whose name ends with alphabet ‘m’.
Select * from hospital where pname like ‘%m’;

vi. List those patients whose name contains alphabet ‘a’.
Select * from hospital where pname like ‘%a%’;

vii. List those patients whose name starts with alphabet ‘M’ and ends with ‘n’.
Select * from hospital where pname like ‘M%n’;

viii. List those patients whose name contains 4 characters.
Select * from hospital where pname like ‘_ _ _ _ ‘;

ix. List those patients whose fee is null.
OR
List those patients who have not been charged any fee.
Select * from hospital where fee is null;

x. List those patients where fee is not null.
Select * from hospital where fee is not null;

Q4. Write SQL queries on the basis of following table.

Relation : Emp
Empid	EmpName	Department	Salary	Gender
101	Mahesh	Finance	32000	M
303	Vijay	HR	42500	M
401	Mansha	Finance	31500	F
603	Kamal	Computer	32150	M
604	Vandana	HR	42000	F
631	Sujata	Finance	39500	F

i. Increase salary of all employees by 1000.
Ans. update emp set salary=salary+1000;

ii. Decrease value of EmpID of male employees by 10.
Ans. update emp set empid=empid-10 where gender=’M’;

iii. Department of  ‘Mahesh’ should be updated as ‘HR’.
Ans. update emp set department=’HR’ where empname=’Mahesh’;

iv. Name should be updated as ‘Manish Saini’ and salary as 50000 for employee id 603.
Ans. update emp set empname=’Manish Saini’, salary=50000 where empid=603;

v. ‘Finance’ department should be updated as ‘Fin’.
Ans. update emp set department=’Fin’ where department=’Finance’;

vi. Delete records of female employees.
Ans. delete from emp where gender=’F’;

vii. Delete all records of Emp table.
Ans. delete from emp;

viii. Insert a new record with data as 700,’Raman’,’HR’,40000,’M’
Ans. insert into emp values(700,’Raman’,’HR’,40000,’M’);

ix. Add a new field Phoneno of integer type to table.
Ans. Alter table emp add phoneno int;

x. Set the size of EmpName field to 40.
Ans. Alter table emp modify empname varchar(40);

Q5. Write SQL queries on the basis of following table.

Relation : Employee
id	Name	Designation	Sal
101	Naresh	Clerk	32000
102	Ajay	Manager	42500
103	Manisha	Clerk	31500
104	Komal	Advisor	32150
105	Varun	Manager	42000
106	NULL	Clerk	32500

i. Count number of records in the table.
Ans. select count(*) from employee;

ii. Count number of names in the table.
Ans. select count(name) from employee;

iii. Count number of designations.
Ans. select count(distinct designation) from employee;

iv. Count number of clerks.
Ans. select count(*) from employee where designation=’Clerk’;

v. Find sum of salaries of all employees.
Ans. select sum(sal) from employee;

vi. Find maximum salary in the table.
Ans. select max(sal) from employee;

vii. Find minimum salary in the table.
Ans. select min(sal) from employee;

viii. Find average salary in the table.
Ans. select avg(sal) from employee;

ix. Find minimum and maximum salary of Managers.
Ans. select max(sal),min(sal) from employee where designation=’Manager’;

x. Display number of records for each individual designation.
Ans. select designation, count(*) from employee group by designation;

xi. Display number of records along with sum of salaries for each individual designation where number of records are more than 1.
Ans. select designation, count(*), sum(sal) from employee group by designation having count(*)>1;

xii. Display sum of salaries of clerks and managers
Ans. select designation, sum(sal) from employee group by designation having designation in (‘Clerk’,’Manager’);

Q6. Write SQL queries on the basis of following tables.

Relation : Employee
id	Name	Designation
101	Naresh	Clerk
102	Ajay	Manager
103	Manisha	Clerk
104	Komal	Advisor

 

Relation : Details
id	Salary	PhoneNo
101	32000	9898090909
102	45000	9888000909
103	33000	8008078787
104	40000	7009876543

i. View Name and salary of all employees.
Ans. select name,salary from employee, details where employee.id=details.id;
OR
select name,salary from employee E, details D where E.id=D.id;

ii. List Name, designation and salary of all Clerks.
Ans. select name,designation salary from employee, details where employee.id=details.id and designation=’Clerk’;
OR
select name,designation salary from employee E1, details D1 where E1.id=D1.id and designation=’Clerk’;

iii. Display id, Name and salary of all employees.
Ans. select employee.id, name,salary from employee, details where employee.id=details.id;
OR
select E1.id, name,salary from employee E1, details D1 where E1.id=D1.id;

iv. Display Name and salary of all employees having salary more than 40000
Ans. select name, salary from employee, details where employee.id=details.id and salary>40000;
OR
select name, salary from employee E, details D where E.id=D.id and salary>40000;