SQL PRACTICE QUESTIONS 2

In a database create the following tables with suitable constraints :

STUDENTS +-----+--------------+-----+----+----+----------------------+----------------+ |AdmNo|Name          |Class| Sec| RNo| Address              | Phone          | +-----+--------------+-----+----+----+----------------------+----------------+ |1271 |Utkarsh Madaan|  12 | C  |  1 | C-32,Punjabi Bagh    | 4356154        | |1324 |Naresh Sharma |  10 | A  |  1 | 31,Mohan Nagar       | 435654         | |1325 |Md. Yusuf     |  10 | A  |  2 | 12/21,Chand Nagar    | 145654         | |1328 |Sumedha       |  10 | B  | 23 | 59,Moti Nagar        | 4135654        | |1364 |Subya Akhtar  |  11 | B  | 13 | 12,Janak Puri        | NULL           | |1434 |Varuna        |  12 | B  | 21 | 69,Rohini            | NULL           | |1461 |David DSouza  |  11 | B  |  1 | D-34,Model Town      | 243554,98787665| |2324 |Satinder Singh|  12 | C  |  1 | 1/2,Gulmohar Park    | 143654         | |2328 |Peter Jones   |  10 | A  | 18 | 21/32B,Vishal Enclave| 24356154       | |2371 |Mohini Mehta  |  11 | C  | 12 | 37,Raja Garden       | 435654,6765787 | +-----+--------------+-----+----+----+----------------------+----------------+                           SPORTS +-----+-----------+----------+-----+ |AdmNo| Game      | CoachName|Grade| +-----+-----------+----------+-----+ |1324 |Cricket    |Narendra  | A   | |1364 |Volleball  |M.P.Singh | A   | |1271 |Volleball  |M.P.Singh | B   | |1434 |Basket Ball|I.Malhotra| B   | |1461 |Cricket    |Narendra  | B   | |2328 |Basket Ball|I.Malhotra| A   | |2371 |Basket Ball|I.Malhotra| A   | |1271 |Basket Ball|I.Malhotra| A   | |1434 |Cricket    |Narendra  | A   | |2328 |Cricket    |Narendra  | B   | |1364 |Basket Ball|I.Malhotra| B   | +-----+-----------+----------+-----+

a)

Based on these tables write SQL statements for the following queries:

i

Display the lowest and the highest classes from the table STUDENTS.

mysql> select max(class) as "highest", min(class) as "lowest" from students;

ii

Display the number of students in each class from the table STUDENTS.

mysql> select class,count(class) as "No.of Students" from student group by class;

iii

Display the number of students in class 10.

mysql> select count(class) from students where class = 10;

iv

Display details of the students of Cricket team

mysql> select student.* from students,sports where students.admno=sports.admno and game='Cricket'; mysql> select student.* from students join sports using(admno) where game='Cricket';

v

Display the Admission number, name, class, section, and roll number of the students whose grade in Sports table is 'A'.

mysql> select students.admno,name,class,sec,rno from students,sports where students.admno=sports.admno and grade='A'; mysql> select students.admno,name,class,sec,rno from students join sports using(admno) where grade='A';

vi

Display the name and phone number of the students of class 12 who play some game.

mysql> select name,phone from students,sports where students.admno=sports.admno and class=12 and game is not null; mysql> select name,phone from students join sports using(admno) where class=12 and game is not null;

vii

Display the Number of students with each coach.

mysql> select coachname,count(admno) from sports group by coachname;

viii

Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.

mysql> select name,phone from students,sports where students.admno=sports.admno and grade='A'and coachname='Narendra'; mysql> select name,phone from students join sports using(admno) where grade='A'and coachname='Narendra';

b)

Identify the Foreign Keys (if any) of these tables. Justify your choices.

Admno is the Foreign Key in Sports table, because it’s the only column/field in common to join the two given tables.

c)

Predict the output of each of the following SQL statements, verify the output by actually entering these statements:

i

SELECT class, sec, count(*) FROM students GROUP BY class, sec;

+-------+------+----------+ | class | sec  | count(*) | +-------+------+----------+ |    10 | A    |        3 | |    10 | B    |        1 | |    11 | B    |        2 | |    11 | C    |        1 | |    12 | B    |        1 | |    12 | C    |        2 | +-------+------+----------+

ii

SELECT Game, COUNT(*) FROM Sports GROUP BY Game;

+-------------+----------+ | Game        | COUNT(*) | +-------------+----------+ | Basket Ball |        5 | | Cricket     |        4 | | Volleball   |        2 | +-------------+----------+

iii

SELECT game, name, address FROM students, Sports WHERE students.admno = sports.admno AND grade = 'A';

+-------------+----------------+------------------------+ | game        | name           | address                | +-------------+----------------+------------------------+ | Cricket     | Naresh Sharma  | 31, Mohan Nagar        | | Volleball   | Subya Akhtar   | 12,Janak Puri          | | Basket Ball | Peter Jones    | 21/32B, Vishal Enclave | | Basket Ball | Mohini Mehta   | 37, Raja Garden        | | Basket Ball | Utkarsh Madaan | C-32, Punjabi Bagh     | | Cricket     | Varuna         | 69,Rohini              | +-------------+----------------+------------------------+

iv

SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;

+-------------+ | Game        | +-------------+ | Basket Ball | | Cricket     | +-------------+

In a database create the following tables with suitable constraints :

ITEMS +--------+--------------+--------------+------+ | I_Code | Name         | Category     | Rate | +--------+--------------+--------------+------+ |   1001 | Masala Dosa  | South Indian |   60 | |   1002 | Vada Sambhar | South Indian |   40 | |   1003 | Idli Sambhar | South Indian |   40 | |   2001 | Chow Mein    | Chinese      |   80 | |   2002 | Dimsum       | Chinese      |   60 | |   2003 | Soup         | Chinese      |   50 | |   3001 | Pizza        | Italian      |  240 | |   3002 | Pasta        | Italian      |  125 | +--------+--------------+--------------+------+

BILLS +--------+------------+--------+------+ | BillNo | Datee      | I_Code | Qty  | +--------+------------+--------+------+ |      1 | 2010-04-01 |   1002 |    2 | |      1 | 2010-04-01 |   3001 |    1 | |      2 | 2010-04-01 |   1001 |    3 | |      2 | 2010-04-01 |   1002 |    1 | |      2 | 2010-04-01 |   2003 |    2 | |      3 | 2010-04-02 |   2002 |    1 | |      4 | 2010-04-02 |   2002 |    4 | |      4 | 2010-04-02 |   2003 |    2 | |      5 | 2010-04-03 |   2003 |    2 | |      5 | 2010-04-03 |   3001 |    1 | |      5 | 2010-04-03 |   3002 |    3 | +--------+------------+--------+------+

a)

Based on these tables write SQL statements for the following queries:

i

Display the average rate of a South Indian item.

mysql> select avg(rate) from items where category="South Indian";

ii

Display the number of items in each category.

mysql> select category,count(name) from items group by category;

iii

Display the total quantity sold for each item.

mysql> select I_code,sum(qty) from bills group by I_code;

iv

Display total quantity of each item sold but don't display this data for the items whose total quantity sold is less than 3.

mysql> select I_code,sum(qty) from bills group by I_code having sum(qty)>=3;

v

Display the details of bill records along with Name of each corresponding item.

mysql> select billno,datee,bills.I_code,name,qty from items,bills where items.I_code=bills.I_code; mysql> select billno,datee,bills.I_code,name,qty from items join bills using(I_code);

vi

Display the details of the bill records for where the item is 'Dosa'.

mysql> select * from bills where I_code=(select I_code from items where name like '%Dosa'); mysql> select billno,datee,bills.I_code,qty from items,bills where items.I_code=bills.I_code and name like '%Dosa'; mysql> select billno,datee,bills.I_code,qty from items join bills using(I_code) where name like '%Dosa';

vii

Display the bill records for each Italian item sold.

mysql> select billno,datee,bills.I_code,qty from items,bills where items.I_code=bills.I_code and category='Italian' group by I_code; mysql> select billno,datee,bills.I_code,qty from items join bills using(I_code) where category='Italian' group by I_code;

viii

Display the total value of items sold for each bill.

mysql> select billno,sum(qty) from bills group by billno;

b)

Identify the Foreign Keys (if any) of these tables. Justify your answer.

I_code is the Foreign Key in Bills table, because it’s the only column/field in common to join the two given tables.

c)

Answer with justification (More than one answers may be correct. It all depends on your logical thinking):

i

Is it easy to remember the Category of item with a given item code? Do you find any kind of pattern in the items code? What could be the item code of another South Indian item?

Yes,  The I_Code of South Indian starts with 100 , Chinese starts with 200 and Italian starts with 300, The Item Code of another South Indian item would be 100_.

ii

What can be the possible uses of Bills table? Can it be used for some analysis purpose?

Yes, using the Bills table, we can calculate the amount of sales for each day and for each item.

iii

Do you find any columns in these tables which can be NULL? Is there any column which must not be NULL?

No, no column can be null.

In a database create the following tables with suitable constraints :

VEHICLE +-------+-----------+----+---+ |Field  |Type       |Null|Key| +-------+-----------+----+---+ |RegNo  |char(10)   |NO  |PRI| |RegDate|date       |YES |   | |Owner  |varchar(30)|YES |   | |Address|varchar(50)|YES |   | +-------+-----------+----+---+

CHALLAN +------------+--------+----+---+ |Field       |Type    |Null|Key| +------------+--------+----+---+ |Challan_No  |int(11) |NO  |PRI| |Ch_Date     |date    |YES |   | |RegNo       |char(10)|YES |   | |Offence_Code|int(3)  |YES |   | +------------+--------+----+---+

OFFENCE +------------+-----------+----+---+-------+ |Field       |Type       |Null|Key|Default| +------------+-----------+----+---+-------+ |Offence_Code|int(3)     |NO  |PRI|0      | |Off_Desc    |varchar(30)|YES |   |NULL   | |Challan_Amt |int(4)     |YES |   |NULL   | +------------+-----------+----+---+-------+

create table vehicle(RegNo char(10) primary key,RegDate date,Owner varchar(30),Address varchar(50));

create table offence(Offence_Code int(3) primary key,Off_Desc varchar(30),Challen_Amt int(4));

create table challan(Challan_No int(11) primary key,Ch_Date date,RegNo char(10),Offence_Code int(3),foreign key(RegNo) references vehicle(RegNo),foreign key(Offence_Code) references offence(Offence_code));

a)

Based on these tables write SQL statements for the following queries:

i

Display the dates of first registration and last registration from the tableVehicle.

mysql> select min(RegDate) as "First",max(RegDate) as "Last" from vehicle;

ii

Display the number of challans issued on each date.

mysql> select ch_date,count(challan_no) from challan group by ch_date;

iii

Display the total number of challans issued for each offence.

mysql> select offence_code,count(challan_no) from challan group by offence_code;

iv

Display the total number of vehicles for which the 3rd and 4th characters of RegNo are '6C'.

mysql> select count(RegNo) from vehicle where RegNo like '_ _6C%';

v

Display the total value of challans issued for which the Off_Desc is 'Driving without License'.

mysql> select count(challan_no) from challan where offence_code=(select offence_code from offence where off_desc='Driving without License');

vi

Display details of the challans issued on '2010-04-03' along with Off_Desc for each challan.

mysql> select Challan_No,Ch_date,RegNo,challan.Offence_code,Offence_Desc from challan,offence where challan.Offence_code=offence.offence_code and ch_date='2010-04-03'; mysql> select Challan_No,Ch_date,RegNo,challan.Offence_code,Offence_Desc from challan join offence  using(offence_code ) where ch_date='2010-04-03';

vii

Display the RegNo of all vehicles which have been challaned more than once.

mysql> select RegNo, count(Challan_No)from challan  group by RegNo having count(Challan_No)>1;

viii

Display details of each challan alongwith vehicle details, Off_desc, and Challan_Amt.

mysql> select * from vehicle,challan,offence where vehicle.RegNo=challan.RegNo and challan.Offence_code=offence.offence_code ;

b)

Identify the Foreign Keys (if any) of these tables. Justify your choices.

RegNo, Offence_Code are the two foreign keys in the table Challan.

c)

Should any of these tables have some more column(s)? Think, discuss in peer groups, and discuss with your teacher.

No, Not  Required

In a database create the following tables with suitable constraints:

DEPARTMENT +------+---------+--------+--------+------+ | Dept | DName   | MinSal | MaxSal | HOD  | +------+---------+--------+--------+------+ |   10 | Sales   |  25000 |  32000 |    1 | |   20 | Finance |  30000 |  50000 |    5 | |   30 | Admin   |  25000 |  40000 |    7 | +------+---------+--------+--------+------+

EMPLOYEE +----+---------+--------+--------+------+-------+------+ | No | Name    | Salary | Zone   | Age  | Grade | Dept | +----+---------+--------+--------+------+-------+------+ |  1 | Mukul   |  30000 | West   |   28 | A     |   10 | |  2 | Kritika |  35000 | Centre |   30 | A     |   10 | |  3 | Naveen  |  32000 | West   |   40 | NULL  |   20 | |  4 | Uday    |  38000 | North  |   38 | C     |   30 | |  5 | Nupur   |  32000 | East   |   26 | NULL  |   20 | |  6 | Moksh   |  37000 | South  |   28 | B     |   10 | |  7 | Shelly  |  36000 | North  |   26 | A     |   30 | +----+---------+--------+--------+------+-------+------+

a)

Based on these tables write SQL statements for the following queries:

i

Display the details of all the employees who work in Sales department.

mysql> select * from employee where dept=(select dept from department where dname='Sales');

ii

Display the Salary, Zone, and Grade of all the employees whose HOD is Nupur.

mysql> select salary,zone,grade from employee where dept=(select dept from department where hod=(select no from employee where name='Nupur'));

iii

Display the Name and Department Name of all the employees.

mysql> select name,dname from employee,department where employee.dept=department.dept;

iv

Display the names of all the employees whose salary is not within the specified range for the corresponding department.

mysql> select name from employee,department where employee.dept=department.dept and salary between minsal and maxsal;

V

Display the name of the department and the name of the corresponding HOD for all the departments.

mysql> select dname,name from department,employee where hod=no;

b)

Identify the Foreign Keys (if any) of these tables. Justify your choices.

Dept is the Foreign Key in Employee table, because it’s the only column/field in common to join the two given tables.