GRADE XII - MYSQL JOIN QUERY ASSIGNMENT

 

MYSQL JOINS

Given Tables

Student(RollNo, Name, Class)

Marks(RollNo, Subject, Marks)

Questions

1. Write a query using Equi Join to display Student Name and Marks.

2. Write a query using Equi Join to display all columns from both tables where RollNo matches.

3. Write a query using NATURAL JOIN to display all matching records.

4. Write a query using NATURAL JOIN to display Name, Subject, and Marks.

5. Write a query to display all possible combinations of Student and Marks using Cartesian Product.

6. Write a query using Cartesian Product with WHERE condition to display only matching RollNo records.

7. Identify the type of JOIN used in: SELECT * FROM Student, Marks;

8. Predict the number of rows returned by: SELECT * FROM Student, Marks;

9. Write a query using Equi Join to display names of students who have marks greater than 85.

10. Write a query using NATURAL JOIN to display records of Class 12 students only.

11. Write a query using Equi Join to display RollNo, Name, and Subject.

12. Write a query using Cartesian Product and filter records where Subject is 'CS'.

13. Which column is automatically removed in NATURAL JOIN output and why?

14. Write a query using Equi Join to display students who have matching records in Marks table.

15. Write a query using NATURAL JOIN and arrange the result in descending order of Marks.

16. Write a query to count total number of rows produced by Cartesian Product.

SOLUTIONS

 

1. Write a query using Equi Join to display Student Name and Marks.

SELECT S.Name, M.Marks

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo;

2. Write a query using Equi Join to display all columns from both tables where RollNo matches.

SELECT *

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo;

3. Write a query using NATURAL JOIN to display all matching records.

SELECT *

FROM Student NATURAL JOIN Marks;

4. Write a query using NATURAL JOIN to display Name, Subject, and Marks.

SELECT Name, Subject, Marks

FROM Student NATURAL JOIN Marks;

5. Write a query to display all possible combinations of Student and Marks using Cartesian Product.

SELECT *

FROM Student, Marks;

6. Write a query using Cartesian Product with WHERE condition to display only matching RollNo records.

SELECT *

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo;

7. Identify the type of JOIN used in: SELECT * FROM Student, Marks;

Answer: Cartesian Product

8. Predict the number of rows returned by: SELECT * FROM Student, Marks;

Total rows = 3 × 3 = 9 rows

9. Write a query using Equi Join to display names of students who have marks greater than 85.  

SELECT S.Name

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo AND M.Marks > 85;

10. Write a query using NATURAL JOIN to display records of Class 12 students only.

SELECT *

FROM Student NATURAL JOIN Marks

WHERE Class = 12;

11. Write a query using Equi Join to display RollNo, Name, and Subject.

SELECT S.RollNo, S.Name, M.Subject

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo;

12. Write a query using Cartesian Product and filter records where Subject is 'CS'.

SELECT *

FROM Student S, Marks M

WHERE M.Subject = 'CS';

13. Which column is automatically removed in NATURAL JOIN output and why?

Common column RollNo appears only once because NATURAL JOIN removes duplicate columns automatically.

14. Write a query using Equi Join to display students who have matching records in Marks table.

SELECT S.*

FROM Student S, Marks M

WHERE S.RollNo = M.RollNo;

15. Write a query using NATURAL JOIN and arrange the result in descending order of Marks.

SELECT *

FROM Student NATURAL JOIN Marks

ORDER BY Marks DESC;

16. Write a query to count total number of rows produced by Cartesian Product.

SELECT COUNT(*)

FROM Student, Marks;