Notes

CREATE TABLE PRODUCTS (
Product_ID NUMBER(11) PRIMARY KEY,
Supplier_ID NUMBER(11),
Category_ID NUMBER(11),
Quantity_Per_Unit VARCHAR2(20),
Unit_Price NUMBER(11),
Units_In_Stock NUMBER(11),
Units_On_Order NUMBER(11)
);

-- a) List the names of the people who work for the company Wipro along with the cities they live in.
SELECT W.Pname, L.City
FROM WORKS W
JOIN LIVES L ON W.Pname = L.Pname
WHERE W.Cname = 'Wipro';

-- b) Find the people who work for the company “Infosys” with a salary more than Rs. 50000/-.
SELECT W.Pname, L.Street, L.City
FROM WORKS W
JOIN LIVES L ON W.Pname = L.Pname
WHERE W.Cname = 'Infosys' AND W.Salary > 50000;

-- c) Find the names of the persons who live and work in the same city.
SELECT W.Pname
FROM WORKS W
JOIN LIVES L ON W.Pname = L.Pname AND W.City = L.City;

-- d) Find the names of the persons who do not work for “Infosys”.
SELECT DISTINCT W.Pname
FROM WORKS W
WHERE NOT EXISTS (
SELECT 1
FROM WORKS W2
WHERE W.Pname = W2.Pname AND W2.Cname = 'Infosys'
);

-- e) Find the persons whose salaries are more than that of all of the “Oracle” employees.
SELECT Pname
FROM WORKS
WHERE Salary > ALL (
SELECT Salary
FROM WORKS
WHERE Cname = 'Oracle'
);

-- f) Find the names of the companies that are located in every city where the company “Infosys” is located.
SELECT DISTINCT L1.Cname
FROM LOCATED_IN L1
WHERE NOT EXISTS (
SELECT 1
FROM LOCATED_IN L2
WHERE L2.Cname = 'Infosys' AND L1.City = L2.City AND L2.Cname NOT IN (
SELECT L3.Cname
FROM LOCATED_IN L3
WHERE L3.City = L1.City
)
);

CREATE TABLE EMP (
EmpID NUMBER(4) PRIMARY KEY,
DeptID VARCHAR2(10) REFERENCES DEPT(DeptID),
EmpName CHAR(10),
Job CHAR(10),
HireDate DATE,
Salary NUMBER(7, 2),
Commission NUMBER(7, 2)
);

CREATE TABLE DEPT (
DeptID VARCHAR2(10) PRIMARY KEY,
Deptname VARCHAR2(20),
No_of_Faculties NUMBER(2)
);