ONLY MYSQL ANSWERS :
SET-1
1. Write Queries for the following Questions based on the given table: (4)
Table :STUDENT
Rolln
Name Class DOB Gender City Marks
o
1 Nandha X 06-06-1995 M Agra 551
2 Saurabh XII 07-05-1993 M Mumbai 462
3 Sanal XI 06-05-1994 F Delhi 400
4 Trisla XII 08-08-1995 F Mumbai 450
5 Stort XII 08-10-1995 M Delhi 369
6 Marisla XI 12-12-1994 F Dubai 250
7 Neha X 08-12-1995 F Moscow 377
8 Nishant X 12-06-1995 M Moscow 489
i. To display the records from table student in alphabetical order as per
name of the student.
ii. To display Class ,DOB, and city whose marks is between 450 and 551
iii. To display Name , Class and total number of students who have scored
more than 450 marks ,class wise.
iv. To increase marks of all students by 20 whose class is “XII”
ANSWERS;
i. SELECT * FROM STUDENT ORDER BY NAME;
ii. SELECT CLASS,DOB,CITY FROM STUDENT WHERE MARKS
BETWEEN 450 AND 551
iii. SELECT NAME,CLASS,COUNT(*) FROM STUDENT
GROUP BY CLASS HAVING MARKS>450;
iv. Update student SET=MARKS=MARKS+20 WHERE CLASS=’XII’;
SET-2
2. Write Queries for the following Questions based on the given table:
(4)
Table:BOOKS
Book_Id Book_Name Author_Name Publishers Price Type Qty
C0001 Fast Cook Latha Kapoor EPB 355 Cookery 5
F0001 The Tears Willam Hopkins First Publ. 650 Fiction 20
T0001 My First C++ Brain & Brook EPB 350 Text 10
T0002 C++ Brain Works A.W. Rossaine TDH 350 Text 15
F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 20
Table : ISSUED
Book_Id Quantity_issued
T0001 4
C0001 5
F0001 2
i. To show Book name , Author name and Price of books of First publ. publishers
ii. To display the names and price from books in ascending order of their price.
iii. To increase the price of all books of EPB publishers by 10% .
iv. To display the book _Id,Book_Name and Quantity_issued for all books which
have been issued (The query will require contents from both tables.)
ANSWERS:
i. SELECT BOOK_NAME,AUTHOR_NAME,PRICE FROM BOOKS
WHERE PUBLISHERS= ‘FIRST PUBL.’;
ii. SELECT BOOK_NAME,PRICE FROM BOOKS ORDER BY PRICE;
iii. UPDATE BOOKS SET PRICE =PRICE*10/100 +PRICE WHERE
PUBLISHERS=’’EPB’’;
iv. SELECT BOOKS.BOOK_ID,BOOK_NAME,QUANTITY_ISSUED FROM
BOOKS , ISSUED BOOKS.BOOK_ID=ISSUED.BOOK_ID;
SET-3
3. Write Queries for the following Questions based on the given table
Table:EMPLOYEE
EMPLOYEEI NAME SALES JOBID
D
E1 SUMIT SINHA 1100000 102
E2 VIJAY SINGH TOMAR 1300000 101
E3 AJAY RAJPAL 1400000 103
E4 MOHIT RAMANI 1250000 102
E5 SHAILJA SINGH 1450000 103
Table:JOB
JOBID JOBTITLE SALARY
101 President 200000
102 Vice President 125000
103 Administration assistant 80000
104 Accounting Manager 70000
105 Accountant 65000
106 Sales manager 80000
(i) To display employee ids , names of employees, job ids with corresponding job
titles.
(ii) To display names of employees, sales and corresponding job titles who have
achieved sales more than 1300000.
(iii) To display names and corresponding job titles of those employees who have
‘SINGH’ (anywhere) in their names.
(iv) Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as
E4 in the table ‘EMPLOYEE’
Answer =
(i) SELECT EMPLOYEEID , NAME , EMPLOYEE.JOBID , JOBTITLE FROM EMPLOYEE
NATURAL JOIN ON JOB ;
(ii) SELECT NAME , SALES , JOBTITLE FROM EMPLOYEE , JOB WHERE EMPLOYEE.JOBID =
JOB.JOBID AND SALES > 1300000 ;
(iii) SELECT NAME , JOBTITLE FROM EMPLOYEE , JOB WHERE EMPLOYEE.JOBID =
JOB.JOBID AND NAME LIKE “%SINGH%” ;
(iv) UPDATE EMPLOYEE SET JOBID = 104 WHERE EMPLOYEEID = "E4";
SET-4
4. Queries for the following Questions based on the given table
Table: EMPLOYEE
EMPID NAME DOB DEPTID DESIG SALARY
120 Alisha 23-Jan-1978 D001 Manager 75,000
123 Nitin 10-OCT-1977 D002 AO 59,000
129 Navjot 12-Jul-1971 D003 Supervisor 40,000
130 Jimmy 30-Dec-1930 D004 Sales Rep NULL
131 Faiz 06-Apr-1984 D001 Dep Manager 65,000
Table: DEPARTMENT
DEPTID DEPTNAME FLOORNO
D001 Personal 4
D002 Admin 10
D003 Production 1
D004 Sales 3
(i) To display the average salary of all employees, department wise.
(ii) To display name and respective department name of each employee whose
salary is more than 5000.
(iii) To display the name of employees whose salary not known, in alphabetical
order.
(iv) To display DEPTID from the table EMPLOYEE without repetition
Answers:
(i) SELECT AVG (SALARY) FROM EMPLOYEE GROUP BY DEPTID;
(ii) SELECT NAME, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DEPTID
=DEPARTMENT. DEPTID AND SALARY>5000;
(iii) SELECT NAME FROM EMPLOYEE WHERE SALARY IS NULL ORDER BY NAME;
(iv) SELECT DISTINCT DEPTID FROM EMPLOYEE;
SET-5
5. Write Queries for the following Questions based on the given table:
Table : PASSENGER
PNO NAME GENDER FNO
1001 Suresh MALE F101
1002 Anita FEMALE F104
1003 Harjas MALE F102
1004 Nita FEMALE F103
Table: FLIGHT
FNO START END F_DATE FARE
F101 MUMBAI CHENNAI 2021-12-25 4500
F102 MUMBAI BENGALURU 2021-11-20 4000
F103 DELHI CHENNAI 2021-12-10 5500
F104 KOLKATA MUMBAI 2021-12-20 4500
F105 DELHI BENGALURU 2021-01-15 5000
F101 MUMBAI CHENNAI 2021-12-25 4500
F102 MUMBAI BENGALURU 2021-11-20 4000
F103 DELHI CHENNAI 2021-12-10 5500
F104 KOLKATA MUMBAI 2021-12-20 4500
F105 DELHI BENGALURU 2021-01-15 5000
i. Write a query to change the fare to 6000 of the flight whose FNO is F104.
ii. Write a query to display the total number of MALE and FEMALE
PASSENGERS.
iii.Write a query to display the NAME, corresponding FARE and F_DATE of all
PASSENGERS who have a flight to START from DELHI.
iv.Write a query to delete the records of flights which end at Mumbai.
Answers:
a) UPDATE FLIGHT SET FARE= 6000 WHERE FNO=’F104’;
(b) SELECT GENDER, COUNT (*) FROM PASSENGER GROUP BY GENDER;
(c) SELECT A.NAME , B.FARE , B.F_DATE FROM PASSENGER A, FLIGHT B WHERE A.FNO=B. FNO AND
B.START=’DELHI’;
(d) DELETE FROM FLIGHT WHERE END ='MUMBAI';
