sql-6 employee-works schema


CREATE TABLE EMPLOYEE (
EMPLOYEENAME VARCHAR2(50),
STREET VARCHAR2(50),
CITY VARCHAR(50)
);

INSERT INTO EMPLOYEE VALUES ('CHINMAY','MG-ROAD', 'MIRAJ');
INSERT INTO EMPLOYEE VALUES ('VISHAL','MALGAON', 'SANGLI');
INSERT INTO EMPLOYEE VALUES ('RUSHI','MG-ROAD', 'MIRAJ');
INSERT INTO EMPLOYEE VALUES ('VINAYAK','PAWADI', 'KOLHAPUR');
INSERT INTO EMPLOYEE VALUES ('PRUTHVI','INGALI', 'KOLHAPUR');
INSERT INTO EMPLOYEE VALUES ('RANJIT','DESHING', 'SANGLAI');
INSERT INTO EMPLOYEE VALUES ('SARU','KHOTAWADI', 'MIRAJ');
INSERT INTO EMPLOYEE VALUES ('SHREYA','SANAGAR', 'KOLHAPUR');
INSERT INTO EMPLOYEE VALUES ('YASH','MALGAV', 'NASHIK');
INSERT INTO EMPLOYEE VALUES ('SURAJ','SHANTINAGAR', 'MARATHVADA');

CREATE TABLE WORKS (
EMPLOYEENAME VARCHAR2(50),
COMPANYNAME VARCHAR2(50),
SALARY NUMBER(10,2)
);

INSERT INTO WORKS VALUES ('CHINMAY','FIRST BANK CORPORATION', 20000);
INSERT INTO WORKS VALUES ('RUSHI','FIRST BANK CORPORATION', 30000);
INSERT INTO WORKS VALUES ('VISHAL','FIRST BANK CORPORATION', 40000);
INSERT INTO WORKS VALUES ('SURAJ','THEEXPERTX', 10000);
INSERT INTO WORKS VALUES ('SHREYA','SMALL BANK CORPORATION', 30000);
INSERT INTO WORKS VALUES ('VINAYAK','INFOSYS', 20000);
INSERT INTO WORKS VALUES ('SARU','INFOSYS', 50000);
INSERT INTO WORKS VALUES ('PRUTHVI','THEEXPERTX', 50000);
INSERT INTO WORKS VALUES ('RANJIT','SMALL BANK CORPORATION', 40000);
INSERT INTO WORKS VALUES ('YASH','SMALL BANK CORPORATION', 20000);


CREATE TABLE COMPANY(
COMPANYNAME VARCHAR2(50),
CITY VARCHAR(50)
);

INSERT INTO COMPANY VALUES ('FIRST BANK CORPORATION', 'PUNE');
INSERT INTO COMPANY VALUES ('SMALL BANK CORPORATION', 'KOLHAPUR');
INSERT INTO COMPANY VALUES ('THEEXPERTX', 'MUMBAI');
INSERT INTO COMPANY VALUES ('INFOSYS', 'KOLHAPUR');

UPDATE COMPANY SET COMPANYNAME = 'SMALL BANK CORPORATION' WHERE COMPANYNAME = 'CAPG';

CREATE TABLE MANAGES (
EMPLOYEENAME VARCHAR2(50),
MANAGERNAME VARCHAR(50)
);

INSERT INTO MANAGES VALUES ('CHINMAY', 'RUSHI');
INSERT INTO MANAGES VALUES ('VISHAL', 'RUSHI');
INSERT INTO MANAGES VALUES ('SURAJ', 'PRUTHVI');
INSERT INTO MANAGES VALUES ('VINAYAK', 'SARU');
INSERT INTO MANAGES VALUES ('RANJIT', 'YASH');
INSERT INTO MANAGES VALUES ('SHREYA', 'YASH');

-- a. Find the names of all employees who work for First Bank Corporation.
SELECT W.EMPLOYEENAME FROM WORKS W WHERE W.COMPANYNAME='FIRST BANK CORPORATION';

--b. Find the names and cities of residence of all employees who work for First Bank Corporation.
SELECT E.* FROM EMPLOYEE E, WORKS W
WHERE E.EMPLOYEENAME=W.EMPLOYEENAME AND W.COMPANYNAME='FIRST BANK CORPORATION';

--c. Find the names, street addresses, and cities of residence of all employees who work for First Bank
     --Corporation and earn more than $10,000.
SELECT E.*,W.SALARY FROM EMPLOYEE E, WORKS W
WHERE E.EMPLOYEENAME=W.EMPLOYEENAME AND W.COMPANYNAME='FIRST BANK CORPORATION' AND W.SALARY>10000;
                                      --OTHERWISE--
SELECT E.*, W.SALARY FROM EMPLOYEE E INNER JOIN WORKS W
ON  E.EMPLOYEENAME = W.EMPLOYEENAME WHERE W.COMPANYNAME = 'FIRST BANK CORPORATION' AND W.SALARY > 10000 ;

-- d. Find all employees in the database who live in the same cities as the companies for which they
-- work.
SELECT E.*, C.* FROM EMPLOYEE E, WORKS W, COMPANY C
WHERE (E.EMPLOYEENAME = W.EMPLOYEENAME AND W.COMPANYNAME = C.COMPANYNAME AND E.CITY = C.CITY);

-- E. Find all employees in the database who live in the same cities and on the same streets as do their
-- managers
SELECT E.* FROM EMPLOYEE E, EMPLOYEE ME, MANAGES M
WHERE E.EMPLOYEENAME = M.EMPLOYEENAME AND M.MANAGERNAME = ME.EMPLOYEENAME AND E.STREET = ME.STREET AND E.CITY = ME.CITY;  

-- F. Find all employees in the database who do not work for First Bank Corporation.
SELECT W.EMPLOYEENAME FROM WORKS W WHERE W.COMPANYNAME <>'FIRST BANK CORPORATION';

-- G. Find all employees in the database who earn more than each employee of Small Bank Corporation
SELECT W.* FROM WORKS W
WHERE W.SALARY > (SELECT MAX(SALARY) FROM WORKS WHERE COMPANYNAME = 'FIRST BANK CORPORATION' );

-- Assume that the companies may be located in several cities. Find all companies located in every city
-- in which Small Bank Corporation is located
SELECT C.* FROM COMPANY C WHERE C.CITY = (SELECT CITY FROM COMPANY WHERE COMPANYNAME = 'SMALL BANK CORPORATION')
MINUS
SELECT * FROM COMPANY WHERE COMPANYNAME = 'SMALL BANK CORPORATION';

-- i. Find all employees who earn more than the average salary of all employees of their company
SELECT EMPLOYEENAME
FROM WORKS T
WHERE SALARY > (SELECT AVG (SALARY)
FROM WORKS S
WHERE T.COMPANYNAME = S.COMPANYNAME)

-- j. Find the company that has the most employees.
SELECT W.COMPANYNAME, COUNT(W.EMPLOYEENAME) FROM WORKS W GROUP BY W.COMPANYNAME ORDER BY COUNT(W.EMPLOYEENAME) DESC FETCH NEXT 1 ROWS ONLY;

-- k. Find the company that has the smallest payroll.
SELECT W.COMPANYNAME, MIN(W.SALARY) FROM WORKS W GROUP BY W.COMPANYNAME ORDER BY MIN(W.EMPLOYEENAME) ASC FETCH NEXT 1 ROWS ONLY;

-- l. Find those companies whose employees earn a higher salary, on average, than the average salary at
-- First Bank Corporation.
SELECT COMPANYNAME
FROM WORKS
GROUP BY COMPANYNAME
HAVING AVG (SALARY) > (SELECT AVG (SALARY)
FROM WORKS
WHERE COMPANYNAME = 'FIRST BANK CORPORATION')


-- m. Modify the database so that Jones now lives in Newtown.
UPDATE EMPLOYEE SET CITY = 'NEWTON' WHERE EMPLOYEENAME = 'JONES'

-- n. Give all employees of First Bank Corporation a 10 percent raise.
UPDATE WORKS SET SALARY = SALARY+(SALARY*(10/100)) WHERE COMPANYNAME = 'FIRST BANK CORPORATION';
SELECT * FROM WORKS;

-- o. Give all managers of First Bank Corporation a 10 percent raise.
UPDATE WORKS SET SALARY = SALARY+(SALARY*(10/100)) WHERE EMPLOYEENAME IN
(SELECT W.EMPLOYEENAME FROM WORKS W, MANAGES M WHERE W.EMPLOYEENAME = M.MANAGERNAME  AND W.COMPANYNAME = 'FIRST BANK CORPORATION');

-- p. Give all managers of First Bank Corporation a 10 percent raise unless the salary becomes greater
-- than $100,00; in such cases, give only a 3 percent raise.
UPDATE WORKS T
SET T.SALARY = T.SALARY * 1.03
WHERE T.EMPLOYEENAME IN (SELECT MANAGERNAME
FROM MANAGES)
AND T.SALARY * 1.1 > 100000
AND T.COMPANYNAME = 'FIRST BANK CORPORATION';
UPDATE WORKS T
SET T.SALARY = T.SALARY * 1.1
WHERE T.EMPLOYEENAME IN (SELECT MANAGERNAME
FROM MANAGES)
AND T.SALARY * 1.1 <= 100000
AND T.COMPANYNAME = 'FIRST BANK CORPORATION'


-- q. Delete all tuples in the works relation for employees of Small Bank Corporation
DELETE WORKS
WHERE COMPANYNAME = 'SMALL BANK CORPORATION'

Post a Comment

0 Comments