# 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.

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

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?

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.

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.

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’;