Consider the following employee payment database.
Emp (eno, ename, title, city)
Proj (pno, pname, budget, city)
Works (eno, pno,resp, dur)
Pay (title,salary)
Note :
Title can be ‘Manager’, ‘Programmer’ or ‘Support staff’. Default is ‘Programmer’.
Salary can not be a negative number.
Create employee table
CREATE TABLE EMP
(
ENO VARCHAR2(5) PRIMARY KEY,
ENAME VARCHAR2(15),
TITLE VARCHAR2(15) DEFAULT 'PROGRAMMER',
CITY VARCHAR2(15),
CHECK(TITLE IN('MANAGER','PROGRAMMER','SUPPORT STAFF'))
);
Create project table
CREATE TABLE PROJ
(
PNO VARCHAR2(5) PRIMARY KEY,
PNAME VARCHAR2(15),
BUDGET NUMBER(7,2),
CITY VARCHAR2(15)
);
Create work table
CREATE TABLE WORKS
(
ENO VARCHAR2(5) REFERENCES EMP(ENO),
PNO VARCHAR2(5) REFERENCES PROJ(PNO),
DUR NUMBER(3),
PRIMARY KEY(ENO,PNO)
);
Create pay table
CREATE TABLE PAY
(
TITLE VARCHAR2(15) PRIMARY KEY,
SALARY NUMBER(7,2),
CHECK(TITLE IN('MANAGER','PROGRAMMER','SUPPORT STAFF'))
);
Description / definition of employee table
DESC EMP;
Name Null? Type
ENO NOT NULL VARCHAR2(5)
ENAME VARCHAR2(15)
TITLE VARCHAR2(15)
CITY VARCHAR2(15)
Description / definition of project table
DESC PROJ;
Name Null? Type
PNO NOT NULL VARCHAR2(5)
PNAME VARCHAR2(15)
BUDGET NUMBER(7,2)
CITY VARCHAR2(15)
Description / definition of works table
DESC WORKS;
Name Null? Type
ENO NOT NULL VARCHAR2(5)
PNO NOT NULL VARCHAR2(5)
DUR NUMBER(3)
Description / definition of pay table
DESC PAY;
Name Null? Type
TITLE NOT NULL VARCHAR2(15)
SALARY NUMBER(7,2)
Insert statements
INSERT STATEMENT FOR EMPLOYEE TABLE
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E1','KRUNAL','MANAGER','Toronto');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E2','KAUSHIK','PROGRAMMER','Toronto');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E3','RAMESH','SUPPORT STAFF','Toronto');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E4','JAGDISH','SUPPORT STAFF','Toronto');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E5','DINESH','MANAGER','LONDON');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E6','DIPU','SUPPORT STAFF','LONDON');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E7','RAM','PROGRAMMER','LONDON');
INSERT INTO EMP (ENO,ENAME,TITLE,CITY) VALUES
('E8','SHYAM','PROGRAMMER','LONDON');
INSERT STATEMENT FOR PROJECT TABLE
INSERT INTO PROJ (PNO,PNAME,BUDGET,CITY) VALUES
('P1','MEDICAL',75000,'LONDON');
INSERT INTO PROJ (PNO,PNAME,BUDGET,CITY) VALUES
('P2','HOTEL',95000,'NEW YORK');
INSERT INTO PROJ (PNO,PNAME,BUDGET,CITY) VALUES
('P3','HOSPITAL',92000,'Toronto');
INSERT STATEMENT FOR WORKS TABLE
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E1','P1',6);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E2','P1',6);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E3','P1',6);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E5','P2',9);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E6','P2',9);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E7','P2',9);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E4','P3',5);
INSERT INTO WORKS (ENO,PNO,DUR) VALUES
('E8','P3',5);
INSERT STATEMENT FOR PAY TABLE
INSERT INTO PAY (TITLE,SALARY) VALUES
('PROGRAMMER',5000);
INSERT INTO PAY (TITLE,SALARY) VALUES
('MANAGER',15000);
INSERT INTO PAY (TITLE,SALARY) VALUES
('SUPPORT STAFF',3000);
Display records of employee table
SELECT * FROM EMP;
Output :
ENO ENAME TITLE CITY
E1 KRUNAL MANAGER Toronto
E2 KAUSHIK PROGRAMMER Toronto
E3 RAMESH SUPPORT STAFF Toronto
E4 JAGDISH SUPPORT STAFF Toronto
E5 DINESH MANAGER LONDON
E6 DIPU SUPPORT STAFF LONDON
E7 RAM PROGRAMMER LONDON
E8 SHYAM PROGRAMMER LONDON
Display records of project table
SELECT * FROM PROJ;
Output :
PNO PNAME BUDGET CITY
P1 MEDICAL 75000 LONDON
P2 HOTEL 95000 NEW YORK
P3 HOSPITAL 92000 Toronto
Display records of works table
SELECT * FROM WORKS;
Output :
ENO PNO DUR
E1 P1 6
E2 P1 6
E3 P1 6
E5 P2 9
E6 P2 9
E7 P2 9
E4 P3 5
E8 P3 5
Display records of pay table
SELECT * FROM PAY;
Output :
TITLE SALARY
PROGRAMMER 5000
MANAGER 15000
SUPPORT STAFF 3000
Find the cities in which the projects with maximum budget are handled.
SELECT CITY FROM PROJ WHERE BUDGET = (SELECT MAX(BUDGET) FROM PROJ);
Output :
CITY
NEW YORK
Find all the employees whose salary is less than the average salary.
SELECT C.ENO,D.SALARY FROM EMP C,PAY D WHERE C.TITLE = D.TITLE AND D.SALARY < (SELECT AVG(SALARY) FROM PAY);
Output :
ENO SALARY
E2 5000
E3 3000
E4 3000
E6 3000
E7 5000
E8 5000
For each city, how many projects are located in that city and what is the total budget over all projects in the city.
SELECT COUNT(PNO),SUM(BUDGET),CITY FROM PROJ GROUP BY CITY;
Output :
COUNT(PNO) SUM(BUDGET) CITY
1 75000 LONDON
1 95000 NEW YORK
1 92000 Toronto
For each project, what fraction of the budget is spent (in total) on salaries for the people working on that project? Sort your answer by the value of the budget.
SELECT SUM(B.BUDGET/D.SALARY) AS "FRACTION_OF_SALARY_IN_BUDGET", B.PNO,A.ENO FROM EMP A,PROJ B,WORKS C,PAY D WHERE A.ENO = C.ENO AND B.PNO = C.PNO AND A.TITLE = D.TITLE GROUP BY B.PNO,A.ENO;
Output :
FRACTION_OF_SALARY_IN_BUDGET PNO ENO
5 P1 E1
15 P1 E2
25 P1 E3
6.3333333 P2 E5
31.666667 P2 E6
19 P2 E7
30.666667 P3 E4
18.4 P3 E8
Find projects who has at least one employee working from different city than location of project.
SELECT B.PNAME,A.CITY AS "Emp_City",B.CITY AS "Project_City" FROM EMP A,PROJ B,WORKS C WHERE A.ENO = C.ENO AND B.PNO = C.PNO AND A.CITY <> B.CITY;
Output :
PNAME Emp_City Project_City
MEDICAL Toronto LONDON
MEDICAL Toronto LONDON
MEDICAL Toronto LONDON
HOTEL LONDON NEW YORK
HOTEL LONDON NEW YORK
HOTEL LONDON NEW YORK
HOSPITAL LONDON Toronto
For each project, what is the total salary amount for the people working on that project? Sort your answer by the value of the total salary amount.
SELECT SUM(C.SALARY),B.PNO FROM EMP A, WORKS B,PAY C WHERE A.ENO = B.ENO AND A.TITLE = C.TITLE GROUP BY B.PNO;
Output :
SUM(C.SALARY) PNO
23000 P1
23000 P2
8000 P3
For each project, what fraction of the budget is spent (in total) on salaries for the people working on that project? Sort your answer by the value of the budget.
SELECT SUM(B.BUDGET/D.SALARY) AS "FRACTION_OF_SALARY_IN_BUDGET", B.PNO,A.ENO FROM EMP A,PROJ B,WORKS C,PAY D WHERE A.ENO = C.ENO AND B.PNO = C.PNO AND A.TITLE = D.TITLE GROUP BY B.PNO,A.ENO;
Output :
FRACTION_OF_SALARY_IN_BUDGET PNO ENO
5 P1 E1
15 P1 E2
25 P1 E3
6.3333333 P2 E5
31.666667 P2 E6
19 P2 E7
30.666667 P3 E4
18.4 P3 E8
List all projects located in Toronto and include for each one the number of persons working on the project.
SELECT COUNT(A.ENO) FROM EMP A,WORKS B WHERE A.ENO = B.ENO AND B.PNO = (SELECT PNO FROM PROJ WHERE CITY = 'Toronto');
Output :
COUNT(A.ENO)
2