MySQL Assignment on Table Join


MySQL Assignment on Table Join. In a database create the following tables with suitable constraints :

In a database create the following tables with suitable constraints. Create Table Command Tutorial

Table : STUDENT

+------------+------------------+----------+----+---------+---------------------+--------------------+
|    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    |
+-------+----------------+-------+------+------+------------------------+----------------------------+

Table : 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	|
+---------------+-----------------------+-----------------------+---------------+

Write SQL commands based on the above tables.

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

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

Q3. Display the number of students in class 10

Q4. Display details of the students of Cricket team

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

Q6. Display the name and phone number of the students of class 12 who are playing some game.

Q7. Display the number of students with each coach.

Q8. Display the names and phone numbers of the students whose grade is ‘A’ and whose coach is Narendra.

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

Q10. Predict the output of each of the following SQL statements, and then verify the output by actually entering these statements ( MySQL Join )

i. SELECT class, sec, count(*) FROM students GROUP BY class, sec;
ii. SELECT Game, COUNT(*) FROM Sports GROUP BY Game;
iii. SELECT game, name, address FROM students, Sports WHERE students.admno = sports.admno AND grade = ‘A’;
iv. SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;

Create the following table in your database with suitable constraints. Tutorial on MySQL Create Table command

Table : 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	|
+--------+--------------+---------------+---------------+

Table : BILLS

+---------------+-----------------------+---------------+---------------+
| BillNo        |        Date	        | 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	|
+---------------+-----------------------+---------------+---------------+

Based on the above table write SQL commands

Q11. Display the average rate of a South Indian item

Q12. Display the number of items in each category.

Q13. Display the total quantity sold for each item

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

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

Q16. Display the details of the bill records for which the item is ‘Dosa’.

Q17. Display the bill records for each Italian item sold.

Q18. Display the total value of items sold for each bill.

In a database create the following Tables with suitable constraints.

Table : VEHICLE

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

Table : CHALAAN

+------------+----------+------+--------+---------+-------+
| Field	     | Type	| Null | Key	| Default | Extra |
+------------+----------+------+--------+---------+-------+
| Challan_No | int(11)  | NO   | PRI    | 0       |       |
| ch_date    | date     | Yes  |        | NULL    |       | 
| RegNo	     | char(10)	| YES  |	| NULL	  |	  |
| Offence    | int(3)	| YES  |	| NULL	  |	  |
+------------+----------+------+--------+---------+-------+

Table : OFFENCE

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

Based on the above Tables Write SQL commands

Q19. Display the dates of first registration and last registration from the table Vehicle.

Q20. Display the number of challans issued on each date.

Q21. Display the total number of challans issued for each offence.

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

Q23. Display the total value of challans issued for which the Off_Desc is ‘Driving without License’.

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

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

Q26. Display details of each challan along with vehicle details, Off_desc, and Challan_Amt

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.