Submitted By: SWEN221101044
Chapter 8
Lab 8: Multi-table queries (JOIN
OPERATIONS)
Objectives
Introduction of Multi-table queries (JOIN OPERATIONS)
Introduction
Here are the different types of the JOINs in SQL:
• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records
from the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched
records from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right
table
INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
LEFT OUTER JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matched
records from the right table (table2). The result is NULL from the right side, if there is no
match.
Syntax
0 Lab 8: Multi-table queries (JOIN OPERATIONS)
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
RIGHT OUTER JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched
records from the left table (table1). The result is NULL from the left side, when there is no
match.
Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
FULL OUTER JOIN
The FULL OUTER JOIN keyword return all records when there is a match in either left (ta-
ble1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Practise Exercise
1. Write a query to retrieve name and address of all employees who work for the
’Research’ department.
2. Write a query to retrieve employee’s first and last name and first and last name of
his or her immediate supervisor.
41
3. Write a query to retrieve list of employees and the projects they are working on, ordered
by department and with in each department, ordered alphabetically by last name, first
name.
4. For every project located in ’Stafford’, list the project number, the controlling department
number and the department manager’s last name, birth date.
5. Find the sum of the salaries of all employees, the maximum salary, the minimum salary
and the average salary.
6. Find the sum of the salaries of all employees, the maximum salary, the minimum salary
and the average salary of all employees of the ’Research’ department.
7. Count the number of employees working in the ’Research’ department.
8. For each department, retrieve the department number, the number of employees in the
department and their average salary.
create database lab8
use lab8
CREATE TABLE Department (
Dnumber INT PRIMARY KEY,
Dname VARCHAR(255) NOT NULL,
Mgrssn CHAR(11) UNIQUE,
);
CREATE TABLE Employee (
SSN CHAR(11) PRIMARY KEY,
Fname VARCHAR(255) NOT NULL,
Lname VARCHAR(255) NOT NULL,
Bdate DATE NOT NULL,
Address VARCHAR(255),
Dno INT NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
);
CREATE TABLE Project (
Pnumber INT PRIMARY KEY,
Pname VARCHAR(255) NOT NULL,
Dnum INT NOT NULL,
Plocation VARCHAR(255) NOT NULL,
);
CREATE TABLE WorksOn (
Pno INT NOT NULL,
Essn CHAR(11) NOT NULL,
Hours INT NOT NULL,
PRIMARY KEY (Pno, Essn),
);
INSERT INTO Department (Dnumber, Dname, Mgrssn)
VALUES (1, 'Research', '123456789');
INSERT INTO Department (Dnumber, Dname, Mgrssn)
VALUES (2, 'Marketing', '987654321');
INSERT INTO Employee (SSN, Fname, Lname, Bdate, Address, Dno, Salary)
VALUES ('123456789', 'John', 'Doe', '1970-01-01', '123 Main St', 1, 80000.00);
INSERT INTO Employee (SSN, Fname, Lname, Bdate, Address, Dno, Salary)
VALUES ('987654321', 'Jane', 'Smith', '1975-02-15', '456 Elm St', 2, 75000.00);
INSERT INTO Employee (SSN, Fname, Lname, Bdate, Address, Dno, Salary)
VALUES ('333333333', 'Alice', 'Johnson', '1980-03-20', '789 Oak Ave', 1, 60000.00);
INSERT INTO Project (Pnumber, Pname, Dnum, Plocation)
VALUES (101, 'Project A', 1, 'Stafford');
INSERT INTO Project (Pnumber, Pname, Dnum, Plocation)
VALUES (102, 'Project B', 2, 'Chicago');
INSERT INTO WorksOn (Pno, Essn, Hours)
VALUES (101, '123456789', 40);
INSERT INTO WorksOn (Pno, Essn, Hours)
VALUES (101, '333333333', 30);
INSERT INTO WorksOn (Pno, Essn, Hours)
VALUES (102, '987654321', 35);
SELECT e.fname, e.lname, e.address
FROM Employee e
INNER JOIN Department d ON e.dno = d.dnumber
WHERE d.dname = 'Research';
SELECT e.fname AS EmployeeFirstName, e.lname AS EmployeeLastName,
s.fname AS SupervisorFirstName, s.lname AS SupervisorLastName
FROM Employee e
LEFT JOIN Employee s ON e.SSN = s.ssn;
SELECT e.fname, e.lname, p.pname
FROM Employee e
INNER JOIN WorksOn w ON e.ssn = w.essn
INNER JOIN Project p ON w.pno = p.pnumber
INNER JOIN Department d ON e.dno = d.dnumber
ORDER BY d.dname, e.lname, e.fname;
SELECT p.pnumber, p.dnum, e.lname, e.bdate
FROM Project p
INNER JOIN Department d ON p.dnum = d.dnumber
INNER JOIN Employee e ON d.mgrssn = e.ssn
WHERE p.plocation = 'Stafford';
SELECT
SUM(Salary) AS TotalSalary,
MAX(Salary) AS MaxSalary,
MIN(Salary) AS MinSalary,
AVG(Salary) AS AvgSalary
FROM Employee;
SELECT COUNT(*) AS ResearchEmployees
FROM Employee
WHERE Dno = (SELECT Dnumber FROM Department WHERE Dname = 'Research');
SELECT d.dnumber, COUNT(*) AS NumEmployees, AVG(e.salary) AS AvgSalary
FROM Department d
INNER JOIN Employee e ON d.dnumber = e.dno
GROUP BY d.dnumber;
