Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Employee Payment Database

Posted By: Maddison Hughes     Category: Oracle     Views: 4889

Article that create employee payment database with alter, insert, select, update, delete etc...

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

  
Share: 


Didn't find what you were looking for? Find more on Employee Payment Database Or get search suggestion and latest updates.

Maddison Hughes
Maddison Hughes author of Employee Payment Database is from London, United Kingdom.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!