SQL Revision 2025

---

Exercise 1

Database:

CREATE TABLE Patient (
    PID INT PRIMARY KEY,
    Pname VARCHAR(50),
    Paddress VARCHAR(100),
    Pgender VARCHAR(10),
    disease VARCHAR(50)
);

CREATE TABLE Doctor (
    DID INT PRIMARY KEY,
    Dname VARCHAR(50),
    Daddress VARCHAR(100),
    Department VARCHAR(50),
    salary DECIMAL(10, 2)
);

CREATE TABLE Appointment (
    PID INT,
    DID INT,
    time DATETIME,
    PRIMARY KEY (PID, DID),
    FOREIGN KEY (PID) REFERENCES Patient(PID),
    FOREIGN KEY (DID) REFERENCES Doctor(DID)
);

INSERT INTO Patient (PID, Pname, Paddress, Pgender, disease) VALUES
(1, 'Ruma Khan', '123 Main St, Cairo', 'Female', 'Tuberculosis'),
(2, 'Ruben Diaz', '456 Park Ave, Alexandria', 'Male', 'Flu'),
(3, 'Sara Ali', '789 Nile Rd, Giza', 'Female', 'Diabetes'),
(4, 'Ahmed Tarek', '321 Lotus St, Cairo', 'Male', 'Tuberculosis'),
(5, 'Lina Hassan', '654 Rose Ln, Luxor', 'Female', 'Asthma'),
(6, 'Rula Sami', '987 Palm St, Aswan', 'Female', 'TB');

INSERT INTO Doctor (DID, Dname, Daddress, Department, salary) VALUES
(101, 'Ali Hassan', '100 Hospital Rd, Cairo', 'Forensic', 10000.00),
(102, 'Mona Khalil', '200 Clinic St, Giza', 'ENT', 15000.00),
(103, 'Omar Said', '300 Med Center, Alexandria', 'Cardiology', 13000.00),
(104, 'Sara Elmasry', '400 Health Ave, Luxor', 'Forensic', 11000.00),
(105, 'Hassan Ali', '500 Doc Rd, Aswan', 'ENT', 9000.00);

INSERT INTO Appointment (PID, DID, time) VALUES
(1, 101, '2025-05-13 10:00:00'),
(1, 102, '2025-05-13 11:00:00'),
(2, 101, '2025-05-13 12:00:00'),
(3, 103, '2025-05-13 09:00:00'),
(4, 102, '2025-05-13 14:00:00'),
(5, 104, '2025-05-13 15:00:00'),
(6, 101, '2025-05-13 16:00:00');

1) List name and id of doctors whose salary is less than 12000

🧾 Output Table:

Dname	DID
Ali Hassan	101
Sara Elmasry	104
Hassan Ali	105

💻 SQL Code:

SELECT Dname, DID
FROM Doctor
WHERE salary < 12000;

---

2) Find name of patients whose name begins with 'Ru'

🧾 Output Table:

Pname
Ruma Khan
Ruben Diaz
Rula Sami

💻 SQL Code:

SELECT Pname
FROM Patient
WHERE Pname LIKE 'Ru%';

---

3) Display name and address of patients who are suffering from Tuberculosis(TB)

🧾 Output Table:

Pname	Paddress
Ruma Khan	123 Main St, Cairo
Ahmed Tarek	321 Lotus St, Cairo
Rula Sami	987 Palm St, Aswan

💻 SQL Code:

SELECT Pname, Paddress
FROM Patient
WHERE disease = 'Tuberculosis' OR disease = 'TB';

---

4) Count the number of doctors working in ENT department

🧾 Output Table:

ent_doctor_count
2

💻 SQL Code:

SELECT COUNT(*) AS ent_doctor_count
FROM Doctor
WHERE Department = 'ENT';

---

5) Display name of doctor who is receiving maximum salary

🧾 Output Table:

Dname
Mona Khalil

💻 SQL Code:

SELECT Dname
FROM Doctor
WHERE salary = (SELECT MAX(salary) FROM Doctor);

---

6) List all the patients who were checked by doctors “Ali” and “Mona”

🧾 Output Table:

Pname
Ruma Khan

💻 SQL Code:

SELECT DISTINCT P.Pname
FROM Patient P
JOIN Appointment A ON P.PID = A.PID
JOIN Doctor D ON A.DID = D.DID
WHERE D.Dname IN ('Ali Hassan', 'Mona Khalil')
GROUP BY P.Pname
HAVING COUNT(DISTINCT D.Dname) = 2;

---

Exercise 2

Database:

CREATE TABLE Employee (
    E_ID INT PRIMARY KEY,
    E_Name VARCHAR(50),
    Dept_Name VARCHAR(50),
    Salary DECIMAL(10, 2),
    Address VARCHAR(50)
);

INSERT INTO Employee (E_ID, E_Name, Dept_Name, Salary, Address) VALUES
(1, 'Mohamed', 'HR', 15000.00, 'Cairo'),
(2, 'Ahmed', 'IT', 9000.00, 'Asyut'),
(3, 'Sara', 'Finance', 12000.00, 'Cairo'),
(4, 'Salem', 'Marketing', 8000.00, 'Qena'),
(5, 'Nabil', 'HR', 11000.00, 'Alexandria'),
(6, 'Saeed', 'IT', 20000.00, 'Luxor'),
(7, 'Samir', 'Finance', 9500.00, 'Cairo'),
(8, 'Sohaib', 'Marketing', 7000.00, 'Asyut'),
(9, 'Rania', 'HR', 13000.00, 'Qena'),
(10, 'Kamal', 'IT', 8500.00, 'Giza'),
(11, 'Sameh', 'Finance', 6000.00, 'Cairo');

INSERT INTO Employee (E_ID, E_Name, Dept_Name, Salary, Address) VALUES
(12, 'Soham', 'Marketing', 7500.00, 'Asyut');

1. Display the Name of the Employee with the Maximum Salary

Output Table

E_Name
Saeed

SELECT E_Name
FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);

2. Display All Department Names with Fewer Than 10 Employees

Output Table

Dept_Name
HR
IT
Finance
Marketing

SELECT Dept_Name
FROM Employee
GROUP BY Dept_Name
HAVING COUNT(*) < 10;

3. Display Names of Employees Residing in Asyut, Cairo, or Qena

Output Table

E_Name
Mohamed
Ahmed
Sara
Salem
Samir
Sohaib
Rania
Sameh
Soham

SELECT E_Name
FROM Employee
WHERE Address IN ('Asyut', 'Cairo', 'Qena');

4. Display ID and Name of Employees Whose Name Starts with 'S' and Ends with 'M'

Output Table

E_ID	E_Name
4	Salem
12	Soham

SELECT E_ID, E_Name
FROM Employee
WHERE E_Name LIKE 'S%M';

---

Exercise 3

Database:

CREATE TABLE actor (
    act_id INTEGER PRIMARY KEY,
    act_fname CHAR(20),
    act_lname CHAR(20),
    act_gender CHAR(1)
);

CREATE TABLE director (
    dir_id INTEGER PRIMARY KEY,
    dir_fname CHAR(20),
    dir_lname CHAR(20)
);

CREATE TABLE genres (
    gen_id INTEGER PRIMARY KEY,
    gen_title CHAR(20)
);

CREATE TABLE reviewer (
    rev_id INTEGER PRIMARY KEY,
    rev_name CHAR(30)
);

CREATE TABLE movie (
    mov_id INTEGER PRIMARY KEY,
    mov_title CHAR(50),
    mov_year INTEGER,
    mov_time INTEGER,
    mov_lang CHAR(50),
    mov_dt_rel DATE,
    mov_rel_country CHAR(5)
);

CREATE TABLE movie_cast (
    act_id INTEGER,
    mov_id INTEGER,
    role CHAR(30),
    PRIMARY KEY (act_id, mov_id),
    FOREIGN KEY (act_id) REFERENCES actor(act_id),
    FOREIGN KEY (mov_id) REFERENCES movie(mov_id)
);

CREATE TABLE movie_direction (
    dir_id INTEGER,
    mov_id INTEGER,
    PRIMARY KEY (dir_id, mov_id),
    FOREIGN KEY (dir_id) REFERENCES director(dir_id),
    FOREIGN KEY (mov_id) REFERENCES movie(mov_id)
);

CREATE TABLE movie_genres (
    mov_id INTEGER,
    gen_id INTEGER,
    PRIMARY KEY (mov_id, gen_id),
    FOREIGN KEY (mov_id) REFERENCES movie(mov_id),
    FOREIGN KEY (gen_id) REFERENCES genres(gen_id)
);

CREATE TABLE rating (
    mov_id INTEGER,
    rev_id INTEGER,
    rev_stars INTEGER,
    num_o_ratings INTEGER,
    PRIMARY KEY (mov_id, rev_id),
    FOREIGN KEY (mov_id) REFERENCES movie(mov_id),
    FOREIGN KEY (rev_id) REFERENCES reviewer(rev_id)
);

-- Insert Sample Data (updated to cover all queries)
INSERT INTO actor (act_id, act_fname, act_lname, act_gender) VALUES
(1, 'Tom', 'Hanks', 'M'),
(2, 'Meryl', 'Streep', 'F'),
(3, 'Leonardo', 'DiCaprio', 'M'),
(4, 'Scarlett', 'Johansson', 'F'),
(5, 'Brad', 'Pitt', 'M'),
(6, 'Diane', 'Keaton', 'F'),  -- For Annie Hall
(7, 'Woody', 'Allen', 'M');   -- For Annie Hall

INSERT INTO director (dir_id, dir_fname, dir_lname) VALUES
(101, 'Steven', 'Spielberg'),
(102, 'Christopher', 'Nolan'),
(103, 'Greta', 'Gerwig'),
(104, 'Woody', 'Allen'),      -- For query 9
(105, 'Paul', 'Thomas Anderson');  -- For Boogie Nights

INSERT INTO genres (gen_id, gen_title) VALUES
(201, 'Drama'),
(202, 'Action'),
(203, 'Comedy'),
(204, 'Sci-Fi');

INSERT INTO reviewer (rev_id, rev_name) VALUES
(301, 'John Doe'),
(302, 'Jane Smith'),
(303, 'Alex Brown');

INSERT INTO movie (mov_id, mov_title, mov_year, mov_time, mov_lang, mov_dt_rel, mov_rel_country) VALUES
(1001, 'Forrest Gump', 1994, 142, 'English', '1994-07-06', 'USA'),
(1002, 'Inception', 2010, 148, 'English', '2010-07-16', 'USA'),
(1003, 'Little Women', 2019, 135, 'English', '2019-12-25', 'USA'),
(1004, 'The Avengers', 2012, 143, 'English', '2012-05-04', 'USA'),
(1005, 'American Beauty', 1999, 122, 'English', '1999-10-01', 'USA'),  -- For query 2 & 3
(1006, 'Boogie Nights', 1997, 155, 'English', '1997-10-10', 'USA'),   -- For query 7
(1007, 'Annie Hall', 1977, 93, 'English', '1977-04-20', 'USA'),       -- For query 10
(1008, 'No Ratings Movie', 2020, 100, 'English', '2020-01-01', 'UK'), -- For query 6 & 8
(1009, 'Manhattan', 1979, 96, 'English', '1979-04-25', 'USA');        -- For query 9

INSERT INTO movie_cast (act_id, mov_id, role) VALUES
(1, 1001, 'Forrest Gump'),
(3, 1002, 'Cobb'),
(2, 1003, 'Jo March'),
(4, 1004, 'Black Widow'),
(5, 1002, 'Saito'),
(6, 1007, 'Annie Hall'),  -- For query 10
(7, 1007, 'Alvy Singer'); -- For query 10

INSERT INTO movie_direction (dir_id, mov_id) VALUES
(101, 1001),
(102, 1002),
(103, 1003),
(102, 1004),
(105, 1006),  -- Boogie Nights by Paul Thomas Anderson
(104, 1007),  -- Annie Hall by Woody Allen
(104, 1009);  -- Manhattan by Woody Allen

INSERT INTO movie_genres (mov_id, gen_id) VALUES
(1001, 201),
(1002, 202),
(1002, 204),
(1003, 201),
(1004, 202),
(1005, 201),
(1006, 201),
(1007, 203),
(1009, 203);

INSERT INTO rating (mov_id, rev_id, rev_stars, num_o_ratings) VALUES
(1001, 301, 8, 1000),  -- 8 stars for query 5
(1001, 302, 9, 1500),  -- 9 stars for query 5
(1002, 301, 7, 2000),  -- 7 stars for query 5
(1003, 303, 6, 800),
(1004, 302, 8, 2500),  -- 8 stars for query 5
(1005, 301, 3, 1200),  -- For query 11
(1006, 302, 4, 900),   -- For query 11
(1007, 303, 5, 700);   -- For query 11

1. Display Movie Title and Release Year of All Movies

Output Table

mov_title	mov_year
Forrest Gump	1994
Inception	2010
Little Women	2019
The Avengers	2012
American Beauty	1999
Boogie Nights	1997
Annie Hall	1977
No Ratings Movie	2020
Manhattan	1979

SELECT mov_title, mov_year
FROM movie;

2. Display Release Year of the Movie 'American Beauty'

Output Table

mov_year
1999

SELECT mov_year
FROM movie
WHERE mov_title = 'American Beauty';

3. Display Movie Titles Released in 1999

Output Table

mov_title
American Beauty

SELECT mov_title
FROM movie
WHERE mov_year = 1999;

4. Display Movie Titles Released Before 1998

Output Table

mov_title
Forrest Gump
Boogie Nights
Annie Hall
Manhattan

SELECT mov_title
FROM movie
WHERE mov_year < 1998;

5. Display Reviewer Names Who Gave Seven or More Stars

Output Table

rev_name
John Doe
Jane Smith

SELECT DISTINCT r.rev_name
FROM reviewer r
JOIN rating ra ON r.rev_id = ra.rev_id
WHERE ra.rev_stars >= 7;

6. Display Movie Titles Without Any Ratings

Output Table

mov_title
No Ratings Movie
Manhattan

SELECT m.mov_title
FROM movie m
LEFT JOIN rating ra ON m.mov_id = ra.mov_id
WHERE ra.mov_id IS NULL;

7. Display Movie ID, Title, and Release Year for Movies Containing 'Boogie Nights'

Output Table

mov_id	mov_title	mov_year
1006	Boogie Nights	1997

SELECT mov_id, mov_title, mov_year
FROM movie
WHERE mov_title LIKE '%Boogie Nights%'
ORDER BY mov_year ASC;

8. Display Details of Movies Released Outside the United Kingdom

Output Table

mov_title	mov_year	mov_time	mov_dt_rel	mov_rel_country
Forrest Gump	1994	142	1994-07-06	USA
Inception	2010	148	2010-07-16	USA
Little Women	2019	135	2019-12-25	USA
The Avengers	2012	143	2012-05-04	USA
American Beauty	1999	122	1999-10-01	USA
Boogie Nights	1997	155	1997-10-10	USA
Annie Hall	1977	93	1977-04-20	USA
Manhattan	1979	96	1979-04-25	USA

SELECT mov_title, mov_year, mov_time, mov_dt_rel, mov_rel_country
FROM movie
WHERE mov_rel_country != 'UK';

9. Display Movie Titles Directed by Woody Allen

Output Table

mov_title
Annie Hall
Manhattan

SELECT m.mov_title
FROM movie m
JOIN movie_direction md ON m.mov_id = md.mov_id
JOIN director d ON md.dir_id = d.dir_id
WHERE d.dir_fname = 'Woody' AND d.dir_lname = 'Allen';

10. Display All Actor Details for 'Annie Hall' Cast

Output Table

act_id	act_fname	act_lname	act_gender
6	Diane	Keaton	F
7	Woody	Allen	M

SELECT a.*
FROM actor a
JOIN movie_cast mc ON a.act_id = mc.act_id
JOIN movie m ON mc.mov_id = m.mov_id
WHERE m.mov_title = 'Annie Hall';

11. Display Movie Years with At Least One Movie Rated Three or More Stars

Output Table

mov_year
1977
1994
1997
1999
2010
2012
2019

SELECT DISTINCT m.mov_year
FROM movie m
JOIN rating ra ON m.mov_id = ra.mov_id
WHERE ra.rev_stars >= 3
ORDER BY m.mov_year ASC;

12. Display Reviewer Name, Movie Title, and Stars for Rated Movies

Output Table

rev_name	mov_title	rev_stars
Alex Brown	Annie Hall	5
Alex Brown	Little Women	6
Jane Smith	Boogie Nights	4
Jane Smith	Forrest Gump	9
Jane Smith	The Avengers	8
John Doe	American Beauty	3
John Doe	Forrest Gump	8
John Doe	Inception	7

SELECT r.rev_name, m.mov_title, ra.rev_stars
FROM reviewer r
JOIN rating ra ON r.rev_id = ra.rev_id
JOIN movie m ON ra.mov_id = m.mov_id
WHERE ra.rev_stars IS NOT NULL
ORDER BY r.rev_name, m.mov_title, ra.rev_stars ASC;

---

Exercise 4

Database:

-- Create the employee table (emp)
CREATE TABLE emp (
    eno INT PRIMARY KEY,
    ename VARCHAR(50),
    bdate DATE,
    title VARCHAR(10),
    salary DECIMAL(10, 2),
    dno VARCHAR(5)
);

-- Create the project table (proj)
CREATE TABLE proj (
    pno INT PRIMARY KEY,
    pname VARCHAR(50),
    budget DECIMAL(15, 2),
    dno VARCHAR(5)
);

-- Create the department table (dept)
CREATE TABLE dept (
    dno VARCHAR(5) PRIMARY KEY,
    dname VARCHAR(50),
    mgreno INT
);

-- Create the works on table (workson)
CREATE TABLE workson (
    eno INT,
    pno INT,
    resp VARCHAR(20),
    hours DECIMAL(5, 2),
    PRIMARY KEY (eno, pno)
);

-- Insert data into the employee table (emp)
INSERT INTO emp (eno, ename, bdate, title, salary, dno) VALUES
(101, 'Ahmed Khaled', '1990-05-15', 'EE', 45000.00, 'D1'),
(102, 'Sara Ali', '1988-09-22', 'SA', 38000.00, 'D1'),
(103, 'Mohamed Reda', '1992-03-10', 'Manager', 60000.00, 'D2'),
(104, 'Laila Hassan', '1985-11-30', 'EE', 42000.00, 'D3'),
(105, 'Omar Salem', '1991-07-25', 'SA', 35000.00, 'D2');

-- Insert data into the department table (dept)
INSERT INTO dept (dno, dname, mgreno) VALUES
('D1', 'Consulting', 101),
('D2', 'Development', 103),
('D3', 'Marketing', 104);

-- Insert data into the project table (proj)
INSERT INTO proj (pno, pname, budget, dno) VALUES
(1001, 'Project A', 120000.00, 'D1'),
(1002, 'Project B', 45000.00, 'D2'),
(1003, 'Project C', 150000.00, 'D3'),
(1004, 'Project D', 30000.00, 'D1');

-- Insert data into the works on table (workson)
INSERT INTO workson (eno, pno, resp, hours) VALUES
(101, 1001, 'Manager', 15.50),
(102, 1002, 'Developer', 8.00),
(103, 1003, 'Manager', 12.75),
(104, 1004, 'Coordinator', 5.00),
(105, 1001, 'Analyst', 20.00);

1. Display Project Number and Name for Projects with Budget Greater Than $100,000

Output Table

pno	pname
1001	Project A
1003	Project C

SELECT pno, pname
FROM proj
WHERE budget > 100000;

2. Display Works On Records with Hours Less Than 10 and Responsibility as 'Manager'

Output Table

eno	pno	resp	hours

SELECT *
FROM workson
WHERE hours < 10 AND resp = 'Manager';

3. Display Employee Number and Name with Title 'EE' or 'SA' and Salary Greater Than $35,000

Output Table

eno	ename
101	Ahmed Khaled
102	Sara Ali
104	Laila Hassan

SELECT eno, ename
FROM emp
WHERE title IN ('EE', 'SA') AND salary > 35000;

4. Display Employee Names in Department 'D1' Ordered by Decreasing Salary

Output Table

ename
Ahmed Khaled
Sara Ali

SELECT ename
FROM emp
WHERE dno = 'D1'
ORDER BY salary DESC;

5. Display All Department Fields Ordered by Ascending Department Name

Output Table

dno	dname	mgreno
D1	Consulting	101
D2	Development	103
D3	Marketing	104

SELECT *
FROM dept
ORDER BY dname ASC;

6. Display Employee Name, Department Name, and Employee Title

Output Table

ename	dname	title
Ahmed Khaled	Consulting	EE
Sara Ali	Consulting	SA
Mohamed Reda	Development	Manager
Laila Hassan	Marketing	EE
Omar Salem	Development	SA

SELECT e.ename, d.dname, e.title
FROM emp e
JOIN dept d ON e.dno = d.dno;

7. Display Project Name, Hours Worked, and Project Number for Works On Records with Hours Greater Than 10

Output Table

pname	hours	pno
Project A	15.50	1001
Project C	12.75	1003
Project A	20.00	1001

SELECT p.pname, w.hours, w.pno
FROM workson w
JOIN proj p ON w.pno = p.pno
WHERE w.hours > 10;

8. Display Project Name, Department Name, and Budget for Projects with Budget Less Than $50,000

Output Table

pname	dname	budget
Project B	Development	45000.00
Project D	Consulting	30000.00

SELECT p.pname, d.dname, p.budget
FROM proj p
JOIN dept d ON p.dno = d.dno
WHERE p.budget < 50000;

9. Display Employee Numbers and Salaries in the 'Consulting' Department Ordered by Descending Salary

Output Table

eno	salary
101	45000.00
102	38000.00

SELECT e.eno, e.salary
FROM emp e
JOIN dept d ON e.dno = d.dno
WHERE d.dname = 'Consulting'
ORDER BY e.salary DESC;

10. Display Employee Name, Project Name, Employee Title, and Hours for All Works On Records

Output Table

ename	pname	title	hours
Ahmed Khaled	Project A	EE	15.50
Sara Ali	Project B	SA	8.00
Mohamed Reda	Project C	Manager	12.75
Laila Hassan	Project D	EE	5.00
Omar Salem	Project A	SA	20.00

SELECT e.ename, p.pname, e.title, w.hours
FROM workson w
JOIN emp e ON w.eno = e.eno
JOIN proj p ON w.pno = p.pno;

---

Exercise 5

Database:

-- Create the salesman table
CREATE TABLE salesman (
    salesman_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    commission DECIMAL(4, 2)
);

-- Create the customer table
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50),
    grade INT,
    salesman_id INT,
    FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)
);

-- Create the order table
CREATE TABLE orders (
    order_no INT PRIMARY KEY,
    purch_amt DECIMAL(10, 2),
    order_date DATE,
    customer_id INT,
    salesman_id INT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (salesman_id) REFERENCES salesman(salesman_id)
);

-- Insert data into the salesman table
INSERT INTO salesman (salesman_id, name, city, commission) VALUES
(5001, 'James Hoog', 'New York', 0.15),
(5002, 'Nail Knite', 'Paris', 0.13),
(5005, 'Pit Alex', 'London', 0.11),
(5006, 'Mc Lyon', 'Paris', 0.14),
(5003, 'Lauson Hen', 'Paris', 0.12),
(5007, 'Paul Adam', 'Rome', 0.13);

-- Insert data into the customer table
INSERT INTO customer (customer_id, customer_name, city, grade, salesman_id) VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3001, 'Brad Guzan', 'London', 200, 5005),
(3004, 'Fabian Johns', 'Paris', 300, 5006),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3009, 'Geoff Cameron', 'Berlin', 100, 5002),
(3008, 'Julian Green', 'London', 300, 5002),
(3003, 'Jozy Altidor', 'Moncow', 200, 5007);

-- Insert data into the order table
INSERT INTO orders (order_no, purch_amt, order_date, customer_id, salesman_id) VALUES
(70001, 150.5, '2016-10-05', 3005, 5002),
(70009, 270.65, '2016-09-10', 3001, 5005),
(70002, 65.26, '2016-10-05', 3002, 5001),
(70004, 110.5, '2016-08-17', 3009, 5003),
(70007, 948.5, '2016-09-10', 3005, 5002),
(70005, 2400.6, '2016-07-27', 3007, 5001),
(70008, 5760, '2016-09-10', 3002, 5001),
(70010, 1983.43, '2016-10-10', 3004, 5006),
(70003, 2480.4, '2016-10-10', 3009, 5003),
(70012, 250.45, '2016-06-27', 3008, 5002),
(70011, 75.29, '2016-08-17', 3003, 5007);

1. Display Name and Commission of All Salesmen

Output Table

name	commission
James Hoog	0.15
Nail Knite	0.13
Lauson Hen	0.12
Pit Alex	0.11
Mc Lyon	0.14
Paul Adam	0.13

SELECT name, commission 
FROM salesman;

2. Retrieve Unique Salesman IDs from Orders Table

Output Table

salesman_id
5001
5002
5003
5005
5006
5007

SELECT DISTINCT salesman_id 
FROM orders;

3. Display Names and City of Salesmen in Paris

Output Table

name	city
Nail Knite	Paris
Lauson Hen	Paris
Mc Lyon	Paris

SELECT name, city 
FROM salesman 
WHERE city = 'Paris';

4. Display All Information for Customers with Grade 200

Output Table

customer_id	customer_name	city	grade	salesman_id
3001	Brad Guzan	London	200	5005
3003	Jozy Altidor	Moncow	200	5007
3005	Graham Zusi	California	200	5002
3007	Brad Davis	New York	200	5001

SELECT * 
FROM customer 
WHERE grade = 200;

5. Display Order Details for Salesman ID 5001

Output Table

order_no	order_date	purch_amt
70002	2016-10-05	65.26
70005	2016-07-27	2400.60
70008	2016-09-10	5760.00

SELECT order_no, order_date, purch_amt 
FROM orders 
WHERE salesman_id = 5001;

6. Display Customers in New York or with Grade 100 or Less

Output Table

customer_id	customer_name	city	grade	salesman_id
3002	Nick Rimando	New York	100	5001
3007	Brad Davis	New York	200	5001
3009	Geoff Cameron	Berlin	100	5002

SELECT * 
FROM customer 
WHERE city = 'New York' OR grade <= 100;

7. Display Salesmen with Commission Between 0.12 and 0.14

Output Table

salesman_id	name	city	commission
5002	Nail Knite	Paris	0.13
5003	Lauson Hen	Paris	0.12
5006	Mc Lyon	Paris	0.14
5007	Paul Adam	Rome	0.13

SELECT * 
FROM salesman 
WHERE commission BETWEEN 0.12 AND 0.14;

8. Display Customers with Names Ending in 'n'

Output Table

customer_id	customer_name	city	grade	salesman_id
3001	Brad Guzan	London	200	5005
3008	Julian Green	London	300	5002
3009	Geoff Cameron	Berlin	100	5002

SELECT * 
FROM customer 
WHERE customer_name LIKE '%n';

9. Display Salesmen with Names Starting with 'N' and Fourth Character 'l'

Output Table

salesman_id	name	city	commission
5002	Nail Knite	Paris	0.13

SELECT * 
FROM salesman 
WHERE name LIKE 'N__l%';

10. Display Customers with NULL Grade

Output Table

customer_id	customer_name	city	grade	salesman_id

SELECT * 
FROM customer 
WHERE grade IS NULL;

11. Display Total Purchase Amount of All Orders

Output Table

SUM(purch_amt)
14495.58

SELECT SUM(purch_amt) 
FROM orders;

12. Display Number of Unique Salesmen with Customers

Output Table

COUNT(DISTINCT salesman_id)
5

SELECT COUNT(DISTINCT salesman_id) 
FROM customer;

13. Display Highest Grade for Each Customer City

Output Table

city	MAX(grade)
London	300
New York	200
Moncow	200
Paris	300
California	200
Berlin	100

SELECT city, MAX(grade) 
FROM customer 
GROUP BY city;

14. Display Highest Purchase Amount for Each Customer

Output Table

customer_id	MAX(purch_amt)
3001	270.65
3002	5760.00
3003	75.29
3004	1983.43
3005	948.50
3007	2400.60
3008	250.45
3009	2480.40

SELECT customer_id, MAX(purch_amt) 
FROM orders 
GROUP BY customer_id;

15. Display Highest Purchase Amount on August 17, 2012 for Each Salesman

Output Table

salesman_id	MAX(purch_amt)

SELECT salesman_id, MAX(purch_amt) 
FROM orders 
WHERE order_date = '2012-08-17' 
GROUP BY salesman_id;

16. Display Highest Purchase Amount with Customer ID and Order Date for Amounts Over 2000

Output Table

customer_id	order_date	MAX(purch_amt)
3009	2016-10-10	2480.40
3007	2016-07-27	2400.60
3002	2016-09-10	5760.00

SELECT customer_id, order_date, MAX(purch_amt) 
FROM orders 
GROUP BY customer_id, order_date 
HAVING MAX(purch_amt) > 2000;

17. Count Orders for August 17, 2012

Output Table

COUNT(*)
0

SELECT COUNT(*) 
FROM orders 
WHERE order_date = '2012-08-17';

18. Display Customers and Salesmen Living in the Same City

Output Table

customer_name	city	name
Nick Rimando	New York	James Hoog
Brad Davis	New York	James Hoog
Brad Guzan	London	Pit Alex
Fabian Johns	Paris	Mc Lyon

SELECT c.customer_name, c.city, s.name 
FROM customer c 
JOIN salesman s ON c.salesman_id = s.salesman_id 
WHERE c.city = s.city;

19. Display Customer Names and Their Salesmen

Output Table

customer_name	name
Nick Rimando	James Hoog
Brad Davis	James Hoog
Graham Zusi	Nail Knite
Julian Green	Nail Knite
Geoff Cameron	Nail Knite
Brad Guzan	Pit Alex
Fabian Johns	Mc Lyon
Jozy Altidor	Paul Adam

SELECT c.customer_name, s.name 
FROM customer c 
JOIN salesman s ON c.salesman_id = s.salesman_id;

20. Display Orders by Customers Not in the Same City as Their Salesmen

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id
70001	150.50	2016-10-05	3005	5002
70007	948.50	2016-09-10	3005	5002
70012	250.45	2016-06-27	3008	5002
70003	2480.40	2016-10-10	3009	5003
70004	110.50	2016-08-17	3009	5003
70011	75.29	2016-08-17	3003	5007

SELECT o.* 
FROM orders o 
JOIN customer c ON o.customer_id = c.customer_id 
JOIN salesman s ON o.salesman_id = s.salesman_id 
WHERE c.city != s.city;

21. Display Orders Issued by Salesman 'Paul Adam'

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id
70011	75.29	2016-08-17	3003	5007

SELECT o.* 
FROM orders o 
JOIN salesman s ON o.salesman_id = s.salesman_id 
WHERE s.name = 'Paul Adam';

22. Display Orders with Amounts Greater Than the Average Order Value on October 10, 2012

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id

SELECT * 
FROM orders 
WHERE purch_amt > (
    SELECT AVG(purch_amt) 
    FROM orders 
    WHERE order_date = '2012-10-10'
);

23. Display Orders Attributed to Salesmen in Paris

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id
70001	150.50	2016-10-05	3005	5002
70007	948.50	2016-09-10	3005	5002
70012	250.45	2016-06-27	3008	5002
70003	2480.40	2016-10-10	3009	5003
70004	110.50	2016-08-17	3009	5003
70010	1983.43	2016-10-10	3004	5006

SELECT o.* 
FROM orders o 
JOIN salesman s ON o.salesman_id = s.salesman_id 
WHERE s.city = 'Paris';

24. Display Orders by the Salesman with the Highest Commission

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id
70002	65.26	2016-10-05	3002	5001
70005	2400.60	2016-07-27	3007	5001
70008	5760.00	2016-09-10	3002	5001

SELECT o.* 
FROM orders o 
JOIN salesman s ON o.salesman_id = s.salesman_id 
WHERE s.commission = (
    SELECT MAX(commission) 
    FROM salesman
);

25. Display Orders with Amounts Greater Than Any Order from September 10, 2012

Output Table

order_no	purch_amt	order_date	customer_id	salesman_id

SELECT * 
FROM orders 
WHERE purch_amt > ANY (
    SELECT purch_amt 
    FROM orders 
    WHERE order_date = '2012-09-10'
);

26. Display Customers with Grades Higher Than All Customers in New York

Output Table

customer_id	customer_name	city	grade	salesman_id
3004	Fabian Johns	Paris	300	5006
3008	Julian Green	London	300	5002

SELECT * 
FROM customer 
WHERE grade > ALL (
    SELECT grade 
    FROM customer 
    WHERE city = 'New York'
);

Comments