CPTR349  Spring '04 (100 total points)                                                                  Exam 2

 

 

Open book, open notes.  Write the queries below in SQL.  Create a document that contains the following for each:

·        the question number

·        your SQL query

·        the output generated by SQL Plus

 

Each correct answer is worth 5 points.

 

1.      For all departments, give the department id number and the name of the department.

 

       DID DNAME

---------- --------------------

         1 Hardware

         2 Operations

         3 Legal

         4 Marketing

         5 Software

         6 Production

         7 Shipping

 

7 rows selected.

 

2.      For all departments, give the department id number, name of the department, and number of employees in that department.

 

       DID DNAME                  COUNT(*)

---------- -------------------- ----------

         1 Hardware                      4

         2 Operations                   26

         3 Legal                         3

         4 Marketing                     3

         5 Software                      2

         6 Production                   22

         7 Shipping                      1

 

7 rows selected.

 

3.      For all departments, give the department id number, name of the department, and number of employees in that department who are under 25 years old.

 

       DID DNAME                  COUNT(*)

---------- -------------------- ----------

         1 Hardware                      2

         2 Operations                    4

         4 Marketing                     1

         5 Software                      1

         6 Production                    4

 


4.      For all departments that have more than 10 employees, give the department id number, name of the department, and number of employees in that department.

 

       DID DNAME                  COUNT(*)

---------- -------------------- ----------

         2 Operations                   26

         6 Production                   22

 

5.      List the employee id, name, and salary for all employees making more than $50,000.

 

       EID ENAME                              SALARY

---------- ------------------------------ ----------

 142519864 Susan Martin                        56990

 141582651 Mary Johnson                        94011

  11564812 John Williams                       74098

 287321212 Michael Miller                     131072

 248965255 Barbara Wilson                      95021

  74454898 Scott Bell                          70100

 156489494 Gil Richardson                      70100

 548977562 Donald King                         92048

 578875478 Edward Baker                       101071

 

9 rows selected.

 

6.      List the employee id, name, and salary for all employees making more than $50,000 and who are under 25 years of age.

 

ENAME                                 AGE     SALARY

------------------------------ ---------- ----------

Scott Bell                             23      70100

 

7.      List the name, age, salary, and department for all employees who are under 25 or who work in either the Software or Hardware departments.

 

ENAME                                 AGE     SALARY DNAME

------------------------------ ---------- ---------- ------------

James Smith                            68      27099 Hardware

Mary Johnson                           44      94011 Hardware

Mary Johnson                           44      94011 Software

Stanley Browne                         23      14093 Hardware

Stanley Browne                         23      14093 Software

David Anderson                         20      25199 Marketing

Jennifer Thomas                        24      34654 Hardware

Maria White                            22      24998 Operations

Charles Harris                         24      24998 Operations

Ann Mitchell                           23      32175 Operations

Daniel Lee                             23      32175 Operations

 

ENAME                                 AGE     SALARY DNAME

------------------------------ ---------- ---------- ------------

Eric Collins                           23      41008 Production

Scott Bell                             23      70100 Production

Milo Brooks                            22      39910 Production

Louis Jenkins                          20      39910 Production

 

15 rows selected.

 

8.      List the department id number and name of each department that has an employee that makes more than $50,000.

 

       DID DNAME

---------- --------------------

         1 Hardware

         2 Operations

         3 Legal

         4 Marketing

         5 Software

         6 Production

 

6 rows selected.

 

9.      Give the average salary of all employees, by department (department id number only).

 

       DID AVG(E.SALARY)

---------- -------------

         1      42464.25

         2    37995.7308

         3         70634

         4    55412.3333

         5         54052

         6         39796

         7         25971

 

10.  Give the age of the oldest member working in each department (give department id and name).

 

       DID DNAME                    OLDEST

---------- -------------------- ----------

         1 Hardware                     68

         2 Operations                   81

         3 Legal                        48

         4 Marketing                    43

         5 Software                     44

         6 Production                   51

         7 Shipping                     26

 

7 rows selected.

 

11.  Give the name and age of the oldest member(s) working in each department (give department id and name).

 

       DID DNAME                ENAME                                 AGE

---------- -------------------- ------------------------------ ----------

         1 Hardware             James Smith                            68

         2 Operations           Kenneth Hill                           81

         3 Legal                Barbara Wilson                         48

         4 Marketing            Donald King                            43

         5 Software             Mary Johnson                           44

         6 Production           Gene Edwards                           51

         7 Shipping             Linda Davis                            26

 

7 rows selected.

 

 


12.  Give the employee id number and name of all employees who do NOT work in a single department 100% of the time.

 

       EID ENAME

---------- --------------------------

 141582651 Mary Johnson

 141582657 Stanley Browne

 454565232 Louis Jenkins

 550156548 George Wright

 552455318 Ana Lopez

 556784565 Kenneth Hill

 567354612 Karen Scott

 573284895 Steven Green

 574489456 Betty Adams

 

9 rows selected.

 

13.  List the name and salary of all employees who work in more than one department.

 

ENAME                              SALARY

------------------------------ ----------

Mary Johnson                        94011

Stanley Browne                      14093

 

 

14.  List the employee id and name of all employees who are part-time (that is, their total percentage of time worked is less than 100).

 

       EID ENAME

---------- ------------------------------

 454565232 Louis Jenkins

 550156548 George Wright

 552455318 Ana Lopez

 556784565 Kenneth Hill

 567354612 Karen Scott

 573284895 Steven Green

 574489456 Betty Adams

 

 

15.  List the employee id and name of all employees in the database who do not work in any department.

 

no rows selected

 

16.  Give the name of the department(s) that has the largest budget and the name of the department’s manager.

 

DNAME                ENAME

-------------------- ------------------------------

Production           Edward Baker

 

 


17.  Give the name of every employee that works in either Software or Marketing but does not work in Hardware.

 

ENAME

------------------------------

David Anderson

Donald King

Matt Nelson

 

 

18.  The eccentric CEO of the company wants to throw a party for every employee with “son” in his or her name, e.g. Jackson and Anderson.  List all employees (eid and ename) that meet this criteria.

 

       EID ENAME

---------- ------------------------------

 141582651 Mary Johnson

 248965255 Barbara Wilson

 159542516 Matt Nelson

 486512566 David Anderson

 112348546 Joseph Thompson

 156489494 Gil Richardson

 274878974 Harry Watson

 489221823 Richard Jackson

 289562686 Thomas Robinson

 

9 rows selected.

 

 

19.  For each full-time employee (total pct_time = 100%) that works in more than one department, list their name, the name of each department, and that portion of their salary “charged” to that department.   (For example, if the employee spends 50% of their time in one department and 50% of their time in another, each department will be charged 50% of their salary).

 

ENAME                          DNAME                   CHARGED

------------------------------ -------------------- ----------

Mary Johnson                   Hardware                47005.5

Mary Johnson                   Software                47005.5

Stanley Browne                 Hardware                3523.25

Stanley Browne                 Software               10569.75

 

20.  For all departments, give the department id number, name of the department, and number of FTE (full-time equivalent employees) in that department.  (Explanation:  Each employee who works in a dept 100% of the time counts as one FTE.  Combine part-timers to make additional FTEs, e.g. an employee with 25% and another with 75% combine to make one FTE.)

 

       DID DNAME                       FTE

---------- -------------------- ----------

         1 Hardware                   2.75

         2 Operations                22.75

         3 Legal                         3

         4 Marketing                     3

         5 Software                   1.25

         6 Production                 21.5

         7 Shipping                      1

 

7 rows selected.