1.
Consider the following tables Passenger and Flight and write the SQL queries for (i) to (iv)
Questions.
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 Bangalore 2021-11-20 4000
F103 Delhi Chennai 2021-12-10 5500
F104 Kolkata Mumbai 2021-12-20 4500
F105 Delhi Bangalore 2021-01-15 5000
i) Write a query to change the Fare to 6000 of the Flight whose Fno is F104.
Ans) alter table flight set fare=6000 where fno=”F104”;
ii) Write a query to display the total number of male and female passengers.
Ans) select gender, count(*) from passenger group by gender;
iii) Write a query to display the Name, corresponding Fare and F_date of all passengers who
have a flight to start from Delhi.
Ans) select name, fare, f_date from passenger natural join flight where start = “Delhi”;
iv) Write a query to delete the records of flights which end at Mumbai.
Ans) delete from flight where end=”Mumbai”;
2. Consider the following tables Books and Issued and write the SQL queries for (i) to (iv)
Questions.
TABLE : Books
Book_id Book_Name Author_Name Publishers Price Type Qty
C0001 Fast Cook Lata Kapoor EPB 355 Cookery 5
F0001 The Tears William Hopkins First Publ. 650 Fiction 20
T0001 My First C++ Brian & Brooke EPB 350 Text 10
T0002 C++ Brainworks [Link] TDH 350 Text 15
F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 50
TABLE : Issued
Book_id Qty_Issued
T0001 4
C0001 5
F0001 2
i) Write a query to increase the Qty by 5 for the books of Text type.
Ans) update books set qty=qty+5 where type=”Text”;
ii) Write a query to display the names and price from books in ascending order of their Price.
Ans) select book_name, price from books order by price;
iii) Write a query to display the Book_id, Book_Name and Qty_Issued for all books which have
been issued. ( The query will require contents from both the tables.)
Ans) select book_id, book_name, qty_issued from books natural join issued;
iv) Write a query to insert a new record in the table Issued having the following data :
“F0003” ,1.
Ans) insert into issued values(“F0003”, 1 );
3. Consider the following tables Worker and Dept and write the SQL queries for (i) to (iv)
Questions. [
TABLE : Worker
Wno Name Dob Gender Dcode
1001 George K 1991-09-01 Male D01
1004 Ryma Sen 1990-12-15 Female D03
1003 Mohitesh 1987-09-04 Male D04
1002 Anil Jha 1984-10-19 Male D01
1005 Manila Sahai 1986-11-14 Female D02
TABLE : Dept
Dcode Department City
D01 Media Delhi
D02 Marketing Mumbai
D03 Infrastructure Kolkata
D04 Finanace Mumbai
i) Write a query to display wno, name, gender from the table worker in descending order of
wno.
Ans) select wno, name, gender from worker order by wno;
ii) Write a query to display the name of all the female workers from the table worker
Ans) select name from worker where gender = “female “;
iii) Write a query to display the wno and name of those workers who are born between
‘1987-01-01’ and ‘1991-12-01’.
Ans) select wno, name from worker where dob between “1987-01-01” and “1991-12-01”;
iv) Write a query to display the names of the workers of Media department.
Ans) select name from worker natural join dept where department=”Media”;
4. Consider the following table Trainer and write the queries for (i) to (iv).
TABLE : Trainer
Tid TName City DOJ
101 Sunaina Delhi 1998-10-15
102 Anu Chennai 1994-12-24
103 Raja Mumbai 2001-11-05
104 Ganesh Chennai 2006-05-09
105 Meenakshi Madurai 2019-05-06
i) Write a query to display the details of the Trainer whose Tid is 102 and 104.
Ans) select * from trainer where tid in (102,104);
ii) Write a query to display the names of the trainer in descending order of names.
Ans) select tname from trainer order by tname desc;
iii) Write a query to display the trainer names who live in Mumbai.
Ans) select tname from trainer where city=”Mumbai”;
iv) Write a query to count no of record whose names end with “a”.
Ans) select count(*) from trainer where tname like “%a”;