MySql Assignment – Results from multiple Tables


MySql assignment to fetch results from multiple tables is based on SQL join commands. SQL can join multiple tables in multiple ways, this assignment will explore your skills to join these tables.  Sample Tables for this assignment are –

mysql> select * from student;
+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |
+-------+---------+--------+-------+-------+---------+--------+--------+
12 rows in set (0.01 sec)

second Table

mysql> select * from games;
+----+----------+---------+---------------+
| id | name     | fees    | coach         |
+----+----------+---------+---------------+
|  1 | Cricket  | 2000.00 | RK Sharma     |
|  2 | Football | 1800.00 | Deepak Rana   |
|  3 | Sketing  | 1500.00 | Vikas Pandey  |
|  4 | Boxing   | 1650.00 | Mayank Sharma |
+----+----------+---------+---------------+
4 rows in set (0.02 sec)

Now based on the above tables.

Q1. Name the primary keys and foreign keys in the above tables student and games.

Q2. is it compulsory to compare equal columns of both the tables to get results from both the tables?

Q3. How you can verify that a relationship is there between these two tables?

Q4. What is a fully qualified column name when the data fetched from more than two tables?

Q5. What is a Table alias? how it is useful?

Q6. What is a cartesian product in Tables?

Find out the output – results from multiple tables

Q7. select * from student, games;
Q8. select * from student, games where gameID = ID ;
Q9. select admno,s.name,stream, marks, grade, g.name, g.fees from student s, games g where s.gameID = g.id;
Q10. select admno,s.name,gender, stream,marks,grade,g.names,g.fees,coach from student s, games g where s.gameID = g.ID AND g.name=’cricket’ ;

Write SQL queries

Q11. find out the strength of students in each game.
Q12. create a report of the students having fields admno, name, gender, stream, marks, gameName, coach name using the tables student, games
Q13. display the records of all the students of commerce stream whose game is ‘boxing’.
Q14. Display the list of science stream student’s whose coach is ‘Vikas Pandey’

Hope this simple MySQL assignment on results from multiple tables will help you to learn select statement a lot. recommended MySQL assignment on Select statement.

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.