Notes

1)
CREATE TABLE PRODUCTS (
ProductID NUMBER(11) PRIMARY KEY,
SupplierID NUMBER(11),
CategoryID NUMBER(11),
QuantityPerUnit VARCHAR2(20),
UnitPrice NUMBER(11),
UnitsInStock NUMBER(11),
UnitsOnOrder NUMBER(11)
);


2)
(a)
SELECT W.Pname, L.City
FROM WORKS W
JOIN LIVES L ON W.Pname = L.Pname
WHERE W.Cname = 'Wipro';

(b)
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)
SELECT W.Pname
FROM WORKS W
JOIN LIVES L ON W.Pname = L.Pname
WHERE W.City = L.City;

(d)
SELECT Pname
FROM WORKS
WHERE Cname != 'Infosys';

(e)
SELECT Pname
FROM WORKS
WHERE Salary > ALL (SELECT Salary FROM WORKS WHERE Cname = 'Oracle');

(f)
SELECT DISTINCT Cname
FROM LOCATED_IN
WHERE City IN (SELECT City FROM LOCATED_IN WHERE Cname = 'Infosys')
GROUP BY Cname
HAVING COUNT(DISTINCT City) = (SELECT COUNT(DISTINCT City) FROM LOCATED_IN WHERE Cname = 'Infosys');


3)
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)
);


4)
INSERT INTO EMP (EmpID, DeptID, EmpName, Job, HireDate, Salary, Commission)
VALUES
(1001, 'SET_01', 'Harrey', 'SE', TO_DATE('01-Jan-2009', 'dd-Mon-yyyy'), 15000, 3),
(1002, 'SET_02', 'Ron', 'SSE', TO_DATE('15-Feb-1998', 'dd-Mon-yyyy'), 20000, 4),
(1003, 'SEM_05', 'Peter', 'Manager', TO_DATE('15-April-1999', 'dd-Mon-yyyy'), 40000, 5),
(1002, 'SED_07', 'Jolie', 'Assistant Manager', TO_DATE('15-Dec-1998', 'dd-Mon-yyyy'), 50000, 5),
(1008, 'SET_08', 'Santy', 'SSE', TO_DATE('15-Feb-2000', 'dd-Mon-yyyy'), 20000, 4),
(1008, 'SED_10', 'San', 'SE', TO_DATE('10-Feb-2009', 'dd-Mon-yyyy'), 22000, 5);


5)
(a)
SELECT COUNT(*) FROM EMP WHERE Job = 'Manager';

(b)
SELECT DISTINCT Job
FROM EMP
WHERE Salary >= 3000;

(c)
SELECT EmpName
FROM EMP
WHERE SUBSTR(Job, 1, 1) <> 'M';

(d)
SELECT EmpName
FROM EMP
WHERE SUBSTR(EmpName, 1, 1) = 'S';

(e)
SELECT EmpName, HireDate
FROM EMP
WHERE Job = 'Manager' AND HireDate > TO_DATE('02-Jan-2006', 'dd-Mon-yyyy');

(f)
DESC EMP;

(g)
SELECT AVG(Salary) AS AverageSalary FROM EMP;

(h)
SELECT SYSDATE AS "DATE" FROM DUAL;

(i)
SELECT EmpName, UPPER(EmpName) AS UppercaseName
FROM EMP
WHERE EmpName = 'Santy';

(j)
CREATE SEQUENCE SEQ_EMP
START WITH 1
INCREMENT BY 1
MAXVALUE 99
CYCLE;

(k)
SELECT EmpName
FROM EMP
WHERE INSTR(EmpName, 'a') > 0 AND INSTR(EmpName, 'e') > 0;


6)
SELECT COUNT(*) AS TotalDepartments FROM DEPT;