Anjeev Singh Academy

Anjeev Singh Academy

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

CODETNAMESUBJECTDOJPERIODEXPERIENCE
1001RAVI SHANKARENG12/03/20002410
1009PRIYA RAIPHYSICS03/09/19982612
1203LISA ANANDENGLISH09/04/2000275
1045YASHRAJMATHS24/08/20002415
1123GAGANPHYSICS16/07/1999283
1167HARISH BCHEMISTRY19/10/1999275
1215UMESHPHYSICS11/05/19982216

Table: ADMIN

CODEGENDERDESIGNATION
1001MALEVICE PRINCIPAL
1009FEMALECOORDINATOR
1203FEMALECOORDINATOR
1045MALEHOD
1123MALESENIOR TEACHER
1215MALEHOD
1167FEMALESENIOR 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’;

Sorry! You cannot copy content of this page. Please contact, in case you want this content.

Scroll to Top