AIM: Query on Multiple Table
What is SQL?
SQL stands for Structured Query Language designed to manipulate data in the Relational
Database Management Systems (RDBMS).Today, SQL is one of the most common
programming languages for interacting with data.
SQL: Structured Query Language
RDBMS: Relational Database Management System.
Section 1: Working with Table Structure
Create All the Below Tables.
1. Table Name: Employee
Attribute Datatype
First Name varchar(15)
Mid Name Varchar(15)
Last Name Varchar(15)
SSN Number Varchar(9)
Birthday DATE
Address Varchar(50)
Sex Varchar(1)
Salary float(10,2)
Department Number int(4)
2. Table Name: Department
Attribute Data Type
Department Name Varchar2(15)
Department Number Number(4)
Manager SSN Varchar2(9)
Manage Start Date DATE
3. Table Name: Department_Locations
Attribute Data Type
Department Number Number(4)
Department_Location Varchar2(15)
4. Table Name: Project
Attribute Data Type
Project Name Varchar2(15)
Project Number Number(4)
Project Location Varchar2(15)
Department Number Number(4)
5. Table Name: Works_On
Attribute Data Type
Employee_SSN Varchar2(9)
Project_Number Number(4)
Hours Number(3,1)
6. Table Name: Dependent
Attribute Data Type
EmployeeSSN Varchar2(9)
DependentName Varchar2(15)
Sex Varchar2(1)
Birthdate DATE
Relationship Varchar2(8)
Datatype of Oracle and MySQL:
Insert The below data in to the following tables.
1. Employee
2. Department
3. Department_Locations
4. Project
5. WORKS_ON
6. Dependent
Write down the SQL to solve the following queries.
1. List the names of all employees who work in department 5.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE
WHERE DEPARTMENT_NUMBER = 5;
2. From employee table count number of departments.
SELECT COUNT(DISTINCT DEPARTMENT_NUMBER) AS DEPT_NO
FROM EMPLOYEE;
3. List names and salaries of all employee ordered by salary.
SELECT FIRST_NAME, MID_NAME, LAST_NAME, SALARY FROM
EMPLOYEE ORDER BY SALARY DESC;
4. List the name of employees whose salary is between 30000 and 50000.
SELECT FIRST_NAME, MID_NAME, LAST_NAME, SALARY FROM
EMPLOYEE WHERE SALARY BETWEEN 30000 AND 50000;
5. List the name and address of employees who lives in Houston.
SELECT FIRST_NAME, MID_NAME, LAST_NAME, ADDRESS FROM
EMPLOYEE WHERE ADDRESS LIKE '%HOUSTON%';
6. List the ESSN of employees who works on project 3388 or project 1945.
SELECT EMPLOYEE_SSN FROM WORKS_ON WHERE
PROJECT_NUMBER = 3388 OR PROJECT_NUMBER = 1945;
7. List the location of department 1,3 and 5.
SELECT DEPARTMENT_LOCATION FROM
DEPARTMENT_LOCATIONS WHERE DEPARTMENT_NUMBER IN
(1,3,5);
8. List the name of all female employees.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE
WHERE SEX LIKE 'F';
9. List the name of employees who have no dependent.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE
WHERE SSN_NUMBER NOT IN (SELECT EMPLOYEE_SSN FROM
DEPENDENT);
10. Find the project name who is under department number 1 and project location
is Houston.
SELECT PROJECT_NAME FROM PROJECT WHERE
DEPARTMENT_NUMBER = 1 AND PROJECT_LOCATION LIKE
'HOUSTON';
11. Find the project name who is under department number 1 and project location
is not Houston.
SELECT PROJECT_NAME FROM PROJECT WHERE
DEPARTMENT_NUMBER = 1 AND PROJECT_LOCATION NOT LIKE
'HOUSTON';
Joining:
Cartesian Product: emp(eid,name,age,sal,address,deptno)
department(dno,deptname,deptlocation)
select eid,name,deptno,deptname from emp,department where deptno=dno and
deptno=2
Join: A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
Inner Join(Join), Natural join, Full Outer Join, Left Outer Join, Right Outer join
Syntax of inner join:
The INNER JOIN keyword selects records that have matching values in both
tables.
emp(eid,name,age,sal,address,dno)
department(dno,deptname,deptlocation)
select name, deptname, [Link] from employee inner join department
on [Link]=[Link] where [Link]=2;
Syntax of Natural join:
Natural Join joins two tables based on same attribute names and datatypes.
The resulting table will contain all the attributes of both the table but keep only
one copy of each common column.
SQL> select name, department_name, dno from employee natural join department
where dno=2;
Outer Join :
It is a type of Join operation in SQL. Outer join is an operation that returns
combined tuples from a specified table even if the join condition fails.
There are three types of outer join in SQL i.e.
Left Outer Join:
select *from table1 LEFT OUTER JOIN table2 on table1.column_name =
table2.column_name;
SQL> select *from student left outer join marks on
student.roll_no=marks.roll_no;
Right Outer Join:
select *
from table1 RIGHT OUTER JOIN table2
on table1.column_name = table2.column_name;
SQL> select *from student right outer join marks on
student.roll_no=marks.roll_no;
Full Outer Join:
select *
from table1 FULL OUTER JOIN table2
on table1.column_name = table2.column_name;
SQL> select *from student full outer join marks on
student.roll_no=marks.roll_no;
12. List the name of employees who work on both project 3388 and project 1945.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE,
WORKS_ON WHERE SSN_NUMBER=EMPLOYEE_SSN AND
PROJECT_NUMBER=3388 AND EMPLOYEE_SSN IN (SELECT
EMPLOYEE_SSN FROM
WORKS_ON WHERE PROJECT_NUMBER = 1945);
13. List the name of manager who starts to manage a department before sept 20,
1976.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE,
DEPARTMENT WHERE SSN_NUMBER=MANAGER_SSN AND
MANAGER_START_DATE<'20-SEP-76';
14. List the name of employees who both lives and works in saltlake city.
SELECT FIRST_NAME, MID_NAME, LAST_NAME FROM EMPLOYEE,
DEPARTMENT_LOCATIONS WHERE
employee.department_number=department_locations.department_number
AND DEPARTMENT_LOCATION
LIKE '%SALTLAKE CITY%' AND ADDRESS LIKE '%SALTLAKE
CITY%';
15. List the name and location of department 1,3 and 5.
select department_name, department_location from department,
department_locations where
department.department_number=department_locations.department_number
and department.department_number in (1,3,5);
16. List female employee name and project name and project number that she is
working on.
Groub By:
17. List the name and total working hours of each employee.
SELECT FIRST_NAME, MID_NAME, LAST_NAME, SUM(HOURS)
FROM EMPLOYEE, WORKS_ON WHERE SSN_NUMBER =
EMPLOYEE_SSN GROUP BY FIRST_NAME, MID_NAME,
LAST_NAME;
18. List department number, department name and number of employees in each
department ordered by number of employees in each department.
SELECT DEPARTMENT.DEPARTMENT_NUMBER, DEPARTMENT_NAME,
COUNT(SSN_NUMBER) "NO OF EMPLOYEES" FROM EMPLOYEE,
DEPARTMENT WHERE EMPLOYEE.DEPARTMENT_NUMBER =
DEPARTMENT.DEPARTMENT_NUMBER GROUP BY
(DEPARTMENT.DEPARTMENT_NUMBER, DEPARTMENT_NAME) ORDER
BY COUNT(SSN_NUMBER) DESC;
19. List department number, department name and
number of employees in department that have more
than 2 employees order by department number.
SELECT DEPARTMENT.DEPARTMENT_NUMBER, DEPARTMENT_NAME,
COUNT(SSN_NUMBER) "NO OF EMPLOYEES" FROM EMPLOYEE,
DEPARTMENT WHERE EMPLOYEE.DEPARTMENT_NUMBER =
DEPARTMENT.DEPARTMENT_NUMBER GROUP BY
(DEPARTMENT.DEPARTMENT_NUMBER, DEPARTMENT_NAME) HAVING
COUNT(SSN_NUMBER)>2 ORDER BY DEPARTMENT_NUMBER;
20. For each department, list department number,
number of employees and average salary.
21. List the Department Name and Department Number
whose employee average salary is between 30000 and
60000.