Page 205 - Informatics_Practices_Fliipbook_Class12
P. 205
Dept_No Dept_Name Location Mgr_Id
1 Accounts Noida 10002
2 Administration Delhi 10005
3 Home Goods Mumbai 10003
4 Automobile Jamshedpur 10004
5 Textile Mumbai 10007
Table 5.4: DEPARTMENT table
SELECT Statement Involving Multiple Tables
When a SELECT statement is used to retrieve data from multiple tables, names of all the tables are specified in the
FROM clause in the form of a table_list. Syntax of a SELECT statement involving multiple tables is given below:
SELECT attribute [, attribute] ...
FROM table_list
WHERE criterion_for_selecting_specific_tuple
To execute the above query, the FROM clause constructs the cross product of the specified tables, then the attributes
mentioned in the SELECT clause are selected from those tuples of the cross product which satisfy the criterion
specified in WHERE clause. To obtain a cross-product of tables in SQL, we only need to list all the tables whose
cross-product is required in the FROM clause. For example, the following SELECT statement yields a cross-product of
tables EMPLOYEE and DEPARTMENT.
SELECT *
FROM EMPLOYEE, DEPARTMENT;
Execution of the above SQL statement yields Table 5.5.
ID FName … Salary Dept_No Dept_No Dept_Name … Mgr_Id
10001 Raj … 100000 2 1 Accounts 10002
10001 Raj … 100000 2 2 Administration 10005
10001 Raj … 100000 2 3 Home Goods 10003
10001 Raj … 100000 2 4 Automobile 10007
10001 Raj … 100000 2 5 Textile 10004
10002 Dhiraj … 85000 1 1 Accounts 10002
10002 Dhiraj … 85000 1 2 Administration 10005
10002 Dhiraj … 85000 1 3 Home Goods 10003
. … . .
. . .
. . .
10009 Naveen … 60000 4 4 Automobile 10007
10009 Naveen … 60000 4 5 Textile 10004
10010 Savita … 50000 5 1 Accounts 10002
10010 Savita … 50000 5 2 Administration 10005
10010 Savita … 50000 5 3 Home Goods 10003
10010 Savita … 50000 5 4 Automobile 10007
10010 Savita … 50000 5 5 Textile 10004
Table 5.5: CROSS PRODUCT of EMPLOYEE and DEPARTMENT
SQL: Working with Two Tables 191

