SQL Join Query Important Question Answer Class 12 Computer Science Code 083

SQL Join Query Important Question Answer

Question 1 –  Definition / Concepts / Difference Based  – 1 Mark

(i) What is Joins? What is Table Alias? How many types of Joins?

Answer: A join is a query that combines rows from two or more tables. In a join-query, more than one tables are listed in from clause.

SELECT * FROM TABLE1 T1, TABLE2 T2 WHERE T1.COMMONCOLUMN = T2.COMMONCOLUMN;

Table Alias is a temporary label/name given along with the table name in FROM clause. Used while using multiple tables, and want to refer to columns with the table names.

In the above query T1 and T2 is the alias of Table1 and Table2 respectively.

Three types of joins –

(a) Equi-Join (b) Non-Equi-join (c) Natural join

(ii) What is the Cartesian product / Unrestricted Join / Cross Join?

Answer: Cartesian product is really a cross-join that returns all the rows in all the tables listed in a query: each row in the first table is paired with all the rows in the second table. Also known as Unrestricted join.

(iii) Define – (i) Equi–join (ii) Non-Equi-join (iii) Natural Join

Answer: Equi-Join: Equi Join in SQL is used to retrieve data from multiple tables using an equality condition with the WHERE clause

Syntax :

SELECT column_list 
FROM table1, table2.... 
WHERE table1.column_name = table2.column_name;  

Example –

SELECT student.name, student.id, record.class, record.city 
FROM student, record 
WHERE student.city = record.city;

Method – II : 

Syntax :

SELECT column_list FROM table1 JOIN table2 ON (join_condition);

Example –

SELECT student.name, student.id, record.class, record.city 
FROM student 
JOIN record ON student.city = record.city;

Non-Equi-Join: Non-Equi in SQL is used to retrieve data from multiple tables using any other operator (like >, <, >=, <= ) except the equality (=) condition.

Syntax:

SELECT * 
FROM table_name1, table_name2 
WHERE table_name1.column [> |  < |  >= | <= ] table_name2.column;

Example –

SELECT student.name, record.id, record.city 
FROM student, record 
WHERE Student.id < Record.id ;

Natural Join: joins two tables based on the same attribute name and datatypes. The resulting table will contain all the attributes of both the table but keep only one copy of each common column.

Syntax:

SELECT * FROM table_name1 NATURAL JOIN table_name2;

Example –

SELECT student.name, record.id, record.city 
FROM student NATURAL JOIN record ;

(iv) Write one similarity and one difference between Euqi-join and Natural join.

Answer:

Similarity: The Equi-join and Natural join both are compared for equality.

Difference: The duplicate columns are eliminated in the Natural join, while it appears in the Equi-join.

(v) Write the difference between Equi-join and Non-Equi-join.

Answer: Equi-join is based on equality operator while Non-Equi join is based on other than equality operator.

Question 2 – Join Query Based – 1 Marks

Table: TEACHER

CODE	TNAME	SUBJECT	DOJ	PERIOD	EXPERIENCE
1001	RAVI SHANKAR	ENG	12/03/2000	24	10
1009	PRIYA RAI	PHYSICS	03/09/1998	26	12
1203	LISA ANAND	ENGLISH	09/04/2000	27	5
1045	YASHRAJ	MATHS	24/08/2000	24	15
1123	GAGAN	PHYSICS	16/07/1999	28	3
1167	HARISH B	CHEMISTRY	19/10/1999	27	5
1215	UMESH	PHYSICS	11/05/1998	22	16

Table: ADMIN

CODE	GENDER	DESIGNATION
1001	MALE	VICE PRINCIPAL
1009	FEMALE	COORDINATOR
1203	FEMALE	COORDINATOR
1045	MALE	HOD
1123	MALE	SENIOR TEACHER
1215	MALE	HOD
1167	FEMALE	SENIOR TEACHER

Write SQL statement

(i) To display the name of the Teacher and their Designation?

Answer: SELECT TNAME, DESIGNATION

FROM TEACHER T, ADMIN A

WHERE T.CODE = A.CODE;

(ii) To display the Name and Subjects of all Female Teachers?

Answer:

SELECT TNAME, SUBJECT

FROM TEACHER T, ADMIN A

WHERE T.CODE = A.CODE AND GENDER = ‘FEMALE’ ;

(iii) To display the all details of all teachers including Gender and Designation.

Answer:

SELECT T.CODE, TNAME, SUBJECT, DOJ, PERIOD, EXPERIENCE, A.GENDER, A.DESIGNATION

FROM TEACHER T, ADMIN A

WHERE T.CODE = A.CODE ;

(iv) To display details of all HOD.

Answer:

SELECT *

FROM TEACHER NATURAL JOIN ADMIN

WHERE DESIGNATION = ‘HOD’ ;

II Method:

SELECT T.*, A.GENDER, A.DESIGNATION

FROM TEACHER T, ADMIN A

WHERE T.CODE = A.CODE AND DESIGNATION = ‘HOD’;