DATABASE SYSTEMS

ASSIGNMENT – 2

Name : Muhammad Asaad

Roll No : 22F-3879
Section : 5D
Teacher : Mam Aqsa Younas

PART- A
CREATION
Employee:
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,d
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birth_day DATE NOT NULL,
sex CHAR(1) CHECK (sex IN ('M', 'F')),
salary INT CHECK (salary >= 65000),
branch_id INT NOT NULL,
FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
);

Branch:
CREATE TABLE Branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
mgr_id INT NOT NULL,
mgr_start_date DATE NOT NULL,
FOREIGN KEY (mgr_id) REFERENCES Employee(emp_id)
);

Client:
CREATE TABLE Client (
client_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
branch_id INT NOT NULL,
FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
);
Branch Supplier:
CREATE TABLE Branch_supplier (
branch_id INT NOT NULL,
supplier_name VARCHAR(100) NOT NULL,
supply_type VARCHAR(50) NOT NULL,
PRIMARY KEY (branch_id, supplier_name),
FOREIGN KEY (branch_id) REFERENCES Branch(branch_id)
);

Works With:
CREATE TABLE Works_with (
emp_id INT NOT NULL,
client_id INT NOT NULL,
total_sales INT NOT NULL,
PRIMARY KEY (emp_id, client_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY (client_id) REFERENCES Client(client_id)
);
 INSERTIONS
Employee Table:
INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,
branch_id)
VALUES (20, 'Finn', 'Müller', TO_DATE('1967-05-12', 'YYYY-MM-DD'), 'M', 85000,
1);

INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,

branch_id)
VALUES (21, 'Lucas', 'Schmidt', TO_DATE('1972-08-30', 'YYYY-MM-DD'), 'M',
200000, 1);

INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,

branch_id)
VALUES (22, 'Jonas', 'Fischer', TO_DATE('1983-01-01', 'YYYY-MM-DD'), 'M',
65000, 2);

INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,

branch_id)
VALUES (23, 'Ella', 'Weber', TO_DATE('1964-05-22', 'YYYY-MM-DD'), 'F', 170000,
2);

INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,

branch_id)
VALUES (24, 'Ida', 'Wagner', TO_DATE('1970-03-07', 'YYYY-MM-DD'), 'F', 90000,
2);
INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,
branch_id)
VALUES (26, 'Oliver', 'Brown', TO_DATE('1989-02-03', 'YYYY-MM-DD'), 'M',
199000, 3);

INSERT INTO Employee (emp_id, first_name, last_name, birth_day, sex, salary,

branch_id)
VALUES (27, 'Leonie', 'Williams', TO_DATE('1977-07-07', 'YYYY-MM-DD'), 'F',
80000, 3);

Branch Table:
INSERT INTO Branch (branch_id, branch_name, mgr_id, mgr_start_date) VALUES
(1, 'Insurance', 20, TO_DATE('2022-02-06', 'YYYY-MM-DD'));

INSERT INTO Branch (branch_id, branch_name, mgr_id, mgr_start_date) VALUES

(2, 'Account', 22, TO_DATE('2022-04-01', 'YYYY-MM-DD'));

INSERT INTO Branch (branch_id, branch_name, mgr_id, mgr_start_date) VALUES

(3, 'Energy', 26, TO_DATE('2019-02-24', 'YYYY-MM-DD'));
Client Table:
INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES
(10, 'Daniel', 'Maier', 2);

INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES

(11, 'Ella', 'Walter', 2);

INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES

(12, 'Zhang', 'Wei', 1);

INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES

(13, 'Ivan', 'Kirillov', 3);

INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES

(14, 'John', 'Smith', 3);
INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES
(15, 'Dirk', 'Pellet', 1);

BranchSupplier Table:
INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES
(1, 'S Direkt', 'Insurance');

INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES

(1, 'Inshared', 'Insurance');

INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES

(2, 'Deutsche Bank', 'Credit');

INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES

(2, 'Commerzbank', 'Credit');

INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES

(3, 'Ostrom', 'Renewable');
INSERT INTO Branch_supplier (branch_id, supplier_name, supply_type) VALUES
(3, 'E.On Energy', 'Electricity');

Works With Table:

INSERT INTO Works_with (emp_id, client_id, total_sales) VALUES (27, 14,
120000);

INSERT INTO Works_with (emp_id, client_id, total_sales) VALUES (22, 15, 6000);

INSERT INTO Works_with (emp_id, client_id, total_sales) VALUES (20, 15, 6000);

INSERT INTO Works_with (emp_id, client_id, total_sales) VALUES (21, 11,

40000);
 PART B
Q1:
INSERT INTO Employee(emp_id, first_name, last_name, birth_day, sex, salary,
branch_id)
VALUES (28, 'Asif', 'Shahzad', TO_DATE('2004-06-12', 'YYYY-MM-DD'), 'M',
100000000, 2);

Q2:
INSERT INTO Client (client_id, first_name, last_name, branch_id) VALUES
(16, 'Aurangzaib', 'Shahzad', 2);

Q3:
UPDATE works_with
SET total_sales=total_sales*2 WHERE total_sales>10000;
SELECT emp_id,total_sales
FROM Works_with

Q4:
SELECT *
FROM Branch_supplier
WHERE supply_type='Credit'
ORDER BY supplier_name;

Q5:
SELECT *
FROM Employee
WHERE birth_day BETWEEN TO_DATE('2020-1-1','YYYY-MM-DD') AND
TO_DATE('2022-12-31','YYYY-MM-DD');

Q6:
SELECT *
FROM Employee
WHERE first_name LIKE 'L%' AND salary BETWEEN 70000 AND 150000;

Q7:
SELECT branch_id, COUNT(client_id) as TOTAL_CLIENTS
FROM Client
GROUP BY branch_id;

Q7:
SELECT branch_id, COUNT(client_id) as TOTAL_CLIENTS
FROM Client
GROUP BY branch_id;

Q8:
SELECT emp_id
FROM Employee
WHERE branch_id IN ((SELECT branch_id
FROM Branch_supplier
WHERE supply_type<>'Credit'));

Q9:
SELECT *
FROM Employee
WHERE salary NOT BETWEEN 75000 AND 200000 AND branch_id IN (SELECT
branch_id
FROM Branch
WHERE branch_name<>'Research');

Q10:
SELECT emp_id, SUM(total_sales) AS TOTAL_SALES
FROM Works_with
GROUP BY emp_id
HAVING COUNT(client_id)>3;
Q11:
SELECT *
FROM Client
WHERE branch_id IN (SELECT branch_id
FROM Branch
WHERE branch_name NOT IN('Finance','Insurance'))
ORDER BY last_name;

Q12:
SELECT emp_id, first_name, last_name
FROM Employee
WHERE NOT EXISTS (
SELECT emp_id FROM Works_with
WHERE Works_with.emp_id = Employee.emp_id)
ORDER BY last_name;
Q13:
SELECT
e.first_name AS employee_first_name, e.last_name AS employee_last_name,
m.first_name AS manager_first_name, m.last_name AS manager_last_name
FROM
Employee e, Employee m
WHERE
m.emp_id = (SELECT b.mgr_id
FROM Branch b
WHERE b.branch_id = e.branch_id)
ORDER BY
e.last_name;

Q14:
DELETE FROM Employee
WHERE salary < 70000
AND branch_id NOT IN (
SELECT branch_id FROM Branch
WHERE branch_name = 'Marketing'
);
SELECT *
FROM Employee
Q15:
UPDATE Employee
SET salary = salary * 1.15 WHERE salary < 120000
AND branch_id IN ( SELECT branch_id FROM Branch
WHERE branch_name = 'Sales'
);

PART C
Q1:
SELECT e.first_name, e.last_name, b.branch_name
FROM Employee e
JOIN Branch b ON e.branch_id = b.branch_id;
Q2:
SELECT b.branch_name, e.first_name as MGR_FIRST_NAME,e.last_name as
MGR_LAST_NAME
FROM Branch b
LEFT JOIN Employee e
ON e.emp_id=b.mgr_id;

Q3:
SELECT e.first_name, e.last_name, b.branch_name
FROM Employee e
LEFT JOIN Branch b
ON b.branch_id=e.branch_id;
Q4:
SELECT e.first_name, e.last_name, b.branch_name, e.salary
FROM Employee e
LEFT JOIN Branch b
ON e.branch_id=b.branch_id;

Q5:
SELECT e.first_name, e.last_name, c.client_id, w.total_sales
FROM Client c
INNER JOIN Works_with w
ON c.client_id = w.client_id
INNER JOIN Employee e
ON e.emp_id = w.emp_id;

Q6:
SELECT b.branch_name, bs.supplier_name, bs.supply_type
FROM Branch b
LEFT JOIN Branch_supplier bs
ON b.branch_id = bs.branch_id;

Q7:
SELECT e.first_name, e.last_name, c.client_id, c.first_name
AS client_first_name, c.last_name AS client_last_name
FROM Employee e
LEFT JOIN Works_with w ON e.emp_id = w.emp_id LEFT JOIN Client c ON
w.client_id = c.client_id;
Q8:
SELECT
e.first_name AS employee_first_name, e.last_name AS employee_last_name,
m.first_name AS manager_first_name, m.last_name AS manager_last_name
FROM
Employee e
LEFT JOIN Branch b ON e.branch_id = b.branch_id LEFT JOIN Employee m ON
b.mgr_id = m.emp_id ORDER BY e.last_name;

Q9:
SELECT
c.first_name AS client_first_name, c.last_name AS client_last_name,
e.first_name AS employee_first_name, e.last_name AS employee_last_name
FROM
Client c
LEFT JOIN Works_with w ON c.client_id = w.client_id LEFT JOIN Employee e ON
w.emp_id = e.emp_id
ORDER BY
c.last_name, c.first_name;

Q10:
SELECT
e.first_name AS employee_first_name, e.last_name AS employee_last_name,
c.first_name AS client_first_name, c.last_name AS client_last_name
FROM Employee e
LEFT JOIN Works_with w ON e.emp_id = w.emp_id LEFT JOIN Client c
ON w.client_id = c.client_id ORDER BY
e.last_name, e.first_name;
 PART D
Q1:
SELECT e.first_name, e.last_name
FROM Employee e
JOIN Works_with w ON e.emp_id = w.emp_id JOIN Branch b ON e.branch_id =
b.branch_id
GROUP BY e.emp_id, e.first_name, e.last_name, e.branch_id HAVING
SUM(w.total_sales) > (
SELECT AVG(SUM(w2.total_sales))
FROM Works_with w2
JOIN Employee e2 ON w2.emp_id = e2.emp_id WHERE e2.branch_id =
e.branch_id
GROUP BY e2.branch_id
);

Q2:
SELECT c.first_name, c.last_name
FROM Client c
JOIN Works_with w ON c.client_id = w.client_id GROUP BY c.client_id,
c.first_name, c.last_name HAVING SUM(w.total_sales) > (
SELECT AVG(SUM(w2.total_sales)) FROM Works_with w2
GROUP BY w2.client_id
);

Q3:
SELECT e.first_name, e.last_name FROM Employee e
WHERE e.salary > (
SELECT AVG(e2.salary) FROM Employee e2
WHERE e2.branch_id = e.branch_id
);
Q4:
SELECT DISTINCT c.first_name, c.last_name FROM Client c
JOIN Works_with w ON c.client_id = w.client_id JOIN Branch b ON b.branch_id =
w.emp_id
GROUP BY b.branch_id, c.client_id, c.first_name, c.last_name HAVING
SUM(w.total_sales) > 100000;

Q5:
SELECT b.branch_name FROM Branch b
JOIN Works_with w ON b.branch_id = w.emp_id GROUP BY b.branch_id,
b.branch_name
HAVING SUM(w.total_sales) > ( SELECT MAX(SUM(w2.total_sales)) FROM
Works_with w2
JOIN Branch b2 ON w2.emp_id = b2.branch_id WHERE b2.branch_id !=
b.branch_id
GROUP BY b2.branch_id
);

Q6:
SELECT DISTINCT e.first_name, e.last_name
FROM Employee e
JOIN Works_with w ON e.emp_id = w.emp_id WHERE w.total_sales > 50000;
Q7:
SELECT e.first_name, e.last_name
FROM Employee e
WHERE e.birth_day < (
SELECT MIN(e2.birth_day)
FROM Employee e2
JOIN Branch b ON e2.branch_id = b.branch_id WHERE b.branch_name =
'Finance'
);

Q8:
SELECT bs.supplier_name FROM Branch_supplier bs
JOIN Branch b ON bs.branch_id = b.branch_id WHERE (
SELECT COUNT(e.emp_id) FROM Employee e
WHERE e.branch_id = b.branch_id
) > 10;