Difference between revisions of "J.Y.S"

From CDOT Wiki
Jump to: navigation, search
(→‎1.)
(→‎Assignment 1)
Line 25: Line 25:
 
[https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1]
 
[https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1]
 
==Solution==
 
==Solution==
 +
<pre>
 +
SQL> SELECT  employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name",
 +
 +
  2          job_id,
 +
 +
  3          TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY')
 +
 +
  4          "Start Date"
 +
 +
  5  FROM    employees
 +
 +
  6  WHERE    TO_CHAR(hire_date, 'mm') IN (05, 11)
 +
 +
  7  ORDER BY 4 DESC;
 +
 +
 +
 +
EMPLOYEE_ID Full Name                JOB_ID    Start Date                                       
 +
 +
----------- ------------------------- ---------- -----------------------------------------       
 +
 +
        124 Mourgos, Kevin            ST_MAN    November First in the year 1999                 
 +
 +
        178 Grant, Kimberely          SA_REP    May First in the year 1999                       
 +
 +
        174 Abel, Ellen              SA_REP    May First in the year 1996                       
 +
 +
        104 Ernst, Bruce              IT_PROG    May First in the year 1991                       
 +
 +
 +
 +
SQL> SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '
 +
 +
  2            || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees"
 +
 +
  3  FROM      employees
 +
 +
  4  WHERE    salary BETWEEN 5000 AND 12000
 +
 +
  5  AND      job_id IN ('IT_PROG', 'ST_CLERK')
 +
 +
  6  ORDER BY  employee_id;
 +
 +
 +
 +
Happy Employees                                                                                   
 +
 +
----------------------------------------------------------------------------------------------------
 +
 +
Employee named Alexander Hunold who is IT_PROG will have a new salary of $10350                   
 +
 +
Employee named Bruce Ernst who is IT_PROG will have a new salary of $6900                         
 +
 +
 +
 +
SQL> SELECT    last_name, salary, job_id,
 +
 +
  2    RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #",
 +
 +
  3    LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income"
 +
 +
  4  FROM    employees
 +
 +
  5  WHERE    (job_id LIKE 'MK%' OR manager_id IS NULL)
 +
 +
  6  AND      salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000
 +
 +
  7  ORDER BY  2 DESC;
 +
 +
 +
 +
LAST_NAME                    SALARY JOB_ID    Manager #  Total Income                           
 +
 +
------------------------- ---------- ---------- ---------- ------------                           
 +
 +
King                          24000 AD_PRES    No Manager    $288,000                           
 +
 +
Hartstein                      13000 MK_MAN    100            $156,000                           
 +
 +
 +
 +
SQL> SELECT  RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id,
 +
 +
  2          LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name
 +
 +
  3  FROM    employees e JOIN departments d
 +
 +
  4  ON      (e.department_id = d.department_id)
 +
 +
  5  WHERE    UPPER(department_name) IN ('MARKETING', 'SALES')
 +
 +
  6  AND      salary <
 +
 +
  7                    (SELECT MAX(salary)
 +
 +
  8                    FROM  employees
 +
 +
  9                    WHERE  SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR'))
 +
 +
10  ORDER BY 1;
 +
 +
 +
 +
Employee                  JOB_ID    Salary      DEPARTMENT_NAME                                 
 +
 +
------------------------- ---------- ------------ ------------------------------                 
 +
 +
Eleni Zlotkey            SA_MAN    =====$10,500 Sales                                           
 +
 +
Jonathon Taylor          SA_REP    ======$8,600 Sales                                           
 +
 +
Pat Fay                  MK_REP    ======$6,000 Marketing                                       
 +
 +
 +
 +
SQL>
 +
 +
SQL> SELECT last_name, salary, job_id
 +
 +
  2  FROM  employees
 +
 +
  3  WHERE  UPPER(job_id) NOT LIKE '%PRES'
 +
 +
  4  AND    salary >
 +
 +
  5                (SELECT MAX(salary)
 +
 +
  6                  FROM  employees
 +
 +
  7                  JOIN  departments USING (department_id)
 +
 +
  8                  JOIN  locations  USING (location_id)
 +
 +
  9                  WHERE  UPPER(city) IN ('TORONTO','OXFORD'));
 +
 +
 +
 +
LAST_NAME                    SALARY JOB_ID                                                       
 +
 +
------------------------- ---------- ----------                                                   
 +
 +
Kochhar                        17000 AD_VP                                                       
 +
 +
De Haan                        17000 AD_VP                                                       
 +
 +
 +
 +
SQL> SELECT last_name, first_name, job_id, hire_date
 +
 +
  2  FROM  employees
 +
 +
  3  WHERE  hire_date >
 +
 +
  4                    (SELECT MAX(hire_date)
 +
 +
  5                      FROM  employees
 +
 +
  6                      WHERE  department_id =
 +
 +
  7                                            (SELECT department_id
 +
 +
  8                                            FROM  departments
 +
 +
  9                                            WHERE  UPPER(department_name) = 'IT'))
 +
 +
10  AND (department_id !=
 +
 +
11                    (SELECT department_id
 +
 +
12                      FROM  departments
 +
 +
13                      WHERE  UPPER(department_name) = 'EXECUTIVE')
 +
 +
14  OR  department_id IS NULL)
 +
 +
15  ORDER BY 4 DESC;
 +
 +
 +
 +
LAST_NAME                FIRST_NAME          JOB_ID    HIRE_DATE                               
 +
 +
------------------------- -------------------- ---------- ---------                               
 +
 +
Zlotkey                  Eleni                SA_MAN    29-JAN-00                               
 +
 +
Mourgos                  Kevin                ST_MAN    16-NOV-99                               
 +
 +
Grant                    Kimberely            SA_REP    24-MAY-99                               
 +
 +
 +
 +
SQL> SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay",
 +
 +
  2        MIN(salary) "Lowest Dept/Job Pay"
 +
 +
  3  FROM  employees
 +
 +
  4  WHERE  UPPER(job_id) NOT LIKE '%VP'
 +
 +
  5  AND    department_id NOT IN
 +
 +
  6              (SELECT department_id
 +
 +
  7                FROM  departments
 +
 +
  8                WHERE  UPPER(department_name) IN ('IT','SALES'))
 +
 +
  9  GROUP BY department_id, job_id
 +
 +
10  HAVING  MIN(salary) BETWEEN 5000 AND 15000
 +
 +
11  ORDER BY 1, 2;
 +
 +
 +
 +
DEPARTMENT_ID JOB_ID    Average Dept/Job Pay Lowest Dept/Job Pay                                 
 +
 +
------------- ---------- -------------------- -------------------                                 
 +
 +
          20 MK_MAN                    13000              13000                                 
 +
 +
          20 MK_REP                    6000                6000                                 
 +
 +
          50 ST_MAN                    5800                5800                                 
 +
 +
          110 AC_ACCOUNT                8300                8300                                 
 +
 +
          110 AC_MGR                    12000              12000                                 
 +
 +
 +
 +
SQL> SELECT    department_id, department_name,
 +
 +
  2            RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City",
 +
 +
  3            COUNT(DISTINCT job_id) "# of Jobs"
 +
 +
  4  FROM      employees
 +
 +
  5  RIGHT OUTER JOIN  departments USING (department_id)
 +
 +
  6  LEFT  OUTER JOIN  locations  USING (location_id)
 +
 +
  7  GROUP BY  department_id, department_name, city;
 +
 +
 +
 +
DEPARTMENT_ID DEPARTMENT_NAME                City                      # of Jobs                 
 +
 +
------------- ------------------------------ ------------------------- ----------                 
 +
 +
          110 Accounting                    Seattle                            2                 
 +
 +
          50 Shipping                      South San Francisco                2                 
 +
 +
          80 Sales                          Oxford                            2                 
 +
 +
          10 Administration                Seattle                            1                 
 +
 +
          190 Contracting                    Seattle                            0                 
 +
 +
          90 Executive                      Seattle                            2                 
 +
 +
          20 Marketing                      Toronto                            2                 
 +
 +
          60 IT                            Southlake                          1                 
 +
 +
 +
 +
8 rows selected.
 +
 +
 +
 +
SQL> spool off
 +
</pre>
 +
==SQL Statements==
 
===1.===
 
===1.===
 
''YuJin''
 
''YuJin''

Revision as of 20:57, 16 October 2010

This page is to efficiently manage our group assignments!

Member List

First Name Last Name wiki id Learn e-mail Phone
YuJin Jeong yujin.jeong yjeong 647 - 832 - 6771
Saad
James

Assignment 1

Due Date

Thursday, October 21st, 4:00pm Printout submission only( SELECT Statements + ALL outputs)

Description

Assignment1

Solution

SQL> SELECT   employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name",

  2           job_id,

  3           TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY')

  4           "Start Date"

  5  FROM     employees

  6  WHERE    TO_CHAR(hire_date, 'mm') IN (05, 11)

  7  ORDER BY 4 DESC;

 

EMPLOYEE_ID Full Name                 JOB_ID     Start Date                                        

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

        124 Mourgos, Kevin            ST_MAN     November First in the year 1999                   

        178 Grant, Kimberely          SA_REP     May First in the year 1999                        

        174 Abel, Ellen               SA_REP     May First in the year 1996                        

        104 Ernst, Bruce              IT_PROG    May First in the year 1991                        

 

SQL> SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '

  2            || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees"

  3  FROM      employees

  4  WHERE     salary BETWEEN 5000 AND 12000

  5  AND       job_id IN ('IT_PROG', 'ST_CLERK')

  6  ORDER BY  employee_id;

 

Happy Employees                                                                                    

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

Employee named Alexander Hunold who is IT_PROG will have a new salary of $10350                    

Employee named Bruce Ernst who is IT_PROG will have a new salary of $6900                          

 

SQL> SELECT    last_name, salary, job_id,

  2     RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #",

  3     LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income"

  4  FROM     employees

  5  WHERE    (job_id LIKE 'MK%' OR manager_id IS NULL)

  6  AND      salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000

  7  ORDER BY  2 DESC;

 

LAST_NAME                     SALARY JOB_ID     Manager #  Total Income                            

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

King                           24000 AD_PRES    No Manager     $288,000                            

Hartstein                      13000 MK_MAN     100            $156,000                            

 

SQL> SELECT   RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id,

  2           LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name

  3  FROM     employees e JOIN departments d

  4  ON       (e.department_id = d.department_id)

  5  WHERE    UPPER(department_name) IN ('MARKETING', 'SALES')

  6  AND      salary <

  7                    (SELECT MAX(salary)

  8                     FROM   employees

  9                     WHERE  SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR'))

 10  ORDER BY 1;

 

Employee                  JOB_ID     Salary       DEPARTMENT_NAME                                   

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

Eleni Zlotkey             SA_MAN     =====$10,500 Sales                                            

Jonathon Taylor           SA_REP     ======$8,600 Sales                                            

Pat Fay                   MK_REP     ======$6,000 Marketing                                        

 

SQL>

SQL> SELECT last_name, salary, job_id

  2  FROM   employees

  3  WHERE  UPPER(job_id) NOT LIKE '%PRES'

  4  AND    salary >

  5                 (SELECT MAX(salary)

  6                  FROM   employees

  7                  JOIN   departments USING (department_id)

  8                  JOIN   locations   USING (location_id)

  9                  WHERE  UPPER(city) IN ('TORONTO','OXFORD'));

 

LAST_NAME                     SALARY JOB_ID                                                        

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

Kochhar                        17000 AD_VP                                                         

De Haan                        17000 AD_VP                                                         

 

SQL> SELECT last_name, first_name, job_id, hire_date

  2  FROM   employees

  3  WHERE  hire_date >

  4                     (SELECT MAX(hire_date)

  5                      FROM   employees

  6                      WHERE  department_id =

  7                                            (SELECT department_id

  8                                             FROM   departments

  9                                             WHERE  UPPER(department_name) = 'IT'))

 10  AND (department_id !=

 11                     (SELECT department_id

 12                      FROM   departments

 13                      WHERE  UPPER(department_name) = 'EXECUTIVE')

 14  OR  department_id IS NULL)

 15  ORDER BY 4 DESC;

 

LAST_NAME                 FIRST_NAME           JOB_ID     HIRE_DATE                                 

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

Zlotkey                   Eleni                SA_MAN     29-JAN-00                                

Mourgos                   Kevin                ST_MAN     16-NOV-99                                

Grant                     Kimberely            SA_REP     24-MAY-99                                

 

SQL> SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay",

  2         MIN(salary) "Lowest Dept/Job Pay"

  3  FROM   employees

  4  WHERE  UPPER(job_id) NOT LIKE '%VP'

  5  AND    department_id NOT IN

  6               (SELECT department_id

  7                FROM   departments

  8                WHERE  UPPER(department_name) IN ('IT','SALES'))

  9  GROUP BY department_id, job_id

 10  HAVING   MIN(salary) BETWEEN 5000 AND 15000

 11  ORDER BY 1, 2;

 

DEPARTMENT_ID JOB_ID     Average Dept/Job Pay Lowest Dept/Job Pay                                  

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

           20 MK_MAN                    13000               13000                                  

           20 MK_REP                     6000                6000                                   

           50 ST_MAN                     5800                5800                                  

          110 AC_ACCOUNT                 8300                8300                                  

          110 AC_MGR                    12000               12000                                  

 

SQL> SELECT    department_id, department_name,

  2            RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City",

  3            COUNT(DISTINCT job_id) "# of Jobs"

  4  FROM      employees

  5  RIGHT OUTER JOIN  departments USING (department_id)

  6  LEFT  OUTER JOIN  locations   USING (location_id)

  7  GROUP BY  department_id, department_name, city;

 

DEPARTMENT_ID DEPARTMENT_NAME                City                       # of Jobs                  

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

          110 Accounting                     Seattle                            2                  

           50 Shipping                       South San Francisco                2                  

           80 Sales                          Oxford                             2                  

           10 Administration                 Seattle                            1                  

          190 Contracting                    Seattle                            0                  

           90 Executive                      Seattle                            2                  

           20 Marketing                      Toronto                            2                  

           60 IT                             Southlake                          1                  

 

8 rows selected.

 

SQL> spool off

SQL Statements

1.

YuJin 

SELECT   employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name",
         job_id,
         TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY')
         "Start Date"
FROM     employees
WHERE    TO_CHAR(hire_date, 'mm') IN (05, 11)
ORDER BY 4 DESC;

Saad 



James





2.


YuJin



SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '
          || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees"
FROM      employees
WHERE     salary BETWEEN 5000 AND 12000
AND       job_id IN ('IT_PROG', 'ST_CLERK')
ORDER BY  employee_id;



Saad





James





3.


YuJin



SELECT    last_name, salary, job_id,
      RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #",
      LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income"
FROM       employees
WHERE       (job_id LIKE 'MK%' OR manager_id IS NULL)
AND       salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000
ORDER BY  2 DESC;




Saad





James





4.


YuJin



SELECT   RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id,
         LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name
FROM     employees e JOIN departments d
ON       (e.department_id = d.department_id)
WHERE    UPPER(department_name) IN ('MARKETING', 'SALES')
AND      salary <
                  (SELECT MAX(salary)
                   FROM   employees
                   WHERE  SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR'))
ORDER BY 1;



Saad





James





5.


YuJin



SELECT last_name, salary, job_id
FROM   employees
WHERE  UPPER(job_id) NOT LIKE '%PRES'
AND    salary >
               (SELECT MAX(salary)
                FROM   employees
                JOIN   departments USING (department_id)
                JOIN   locations   USING (location_id)
                WHERE  UPPER(city) IN ('TORONTO','OXFORD'));




Saad





James





6.


YuJin



SELECT last_name, first_name, job_id, hire_date
FROM   employees
WHERE  hire_date >
                   (SELECT MAX(hire_date)
                    FROM   employees
                    WHERE  department_id =
                                          (SELECT department_id
                                           FROM   departments
                                           WHERE  UPPER(department_name) = 'IT'))
AND (department_id !=
                   (SELECT department_id
                    FROM   departments
                    WHERE  UPPER(department_name) = 'EXECUTIVE')
OR  department_id IS NULL)
ORDER BY 4 DESC;



Saad





James





7.


YuJin



SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay",
       MIN(salary) "Lowest Dept/Job Pay"
FROM   employees
WHERE  UPPER(job_id) NOT LIKE '%VP'
AND    department_id NOT IN
            (SELECT department_id
             FROM   departments
             WHERE  UPPER(department_name) IN ('IT','SALES'))
GROUP BY department_id, job_id
HAVING   MIN(salary) BETWEEN 5000 AND 15000
ORDER BY 1, 2;




Saad





James





8.


YuJin



SELECT    department_id, department_name,
          RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City",
          COUNT(DISTINCT job_id) "# of Jobs"
FROM      employees
RIGHT OUTER JOIN  departments USING (department_id)
LEFT  OUTER JOIN  locations   USING (location_id)
GROUP BY  department_id, department_name, city;




Saad





James








					

						Retrieved from "https://wiki.cdot.senecapolytechnic.ca/w/index.php?title=J.Y.S&oldid=48177"