# J.Y.S

## Member List

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

# Assignment 1

## Due Date

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

## 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

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

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

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;
```

```SELECT employee_id,
SUBSTR(last_name ||' '|| first_name, 1, 25) "Full Name",
job_id,
TO_CHAR(hire_date,'fmMonth "First in the year" YYYY') "Start Date"
FROM employees
WHERE TO_CHAR(hire_date,'fmMON') IN ('MAY','NOV')
ORDER BY hire_date DESC
/
```

James

```select employee_id "Emp#",
(last_name ||', '|| first_name) "Full Name",
job_id "Job",
to_char(trunc(hire_date, 'MONTH'), 'FMMonth Ddspth "in the year"
YYYY') "Start Date"
from employees
where to_char(hire_date, 'fmMonth') in ('May', 'November')
order by hire_date desc;
```

FINAL

```SELECT   employee_id, SUBSTR(last_name ||' '|| first_name, 1, 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 hire_date DESC;
```

### 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;
```

```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 job_id IN ('IT_PROG','ST CLERK')
AND (salary BETWEEN 5000 AND 12000)
/
```

James

```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 upper(job_id) in ('IT_PROG', 'ST_CLERK')
order by employee_id;
```

FINAL

```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       UPPER(job_id) IN ('IT_PROG', 'ST_CLERK')
ORDER BY  employee_id;
```

### 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;

```

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

James

```select last_name, salary, job_id, nvl(to_char(manager_id),'No Manager') Manager#,
to_char(salary*12,'\$999,999') "Total Income"
from employees
where (upper(job_id) like 'MK%' or manager_id IS NULL)
and salary * (1 + nvl(commission_pct, 0)) + 1000 > 10000
order by 2 desc;
```

### 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;
```

```SELECT SUBSTR(first_name||' '||last_name, 25) Employee, job_id,
FROM employees e JOIN departments d
ON (d.department_id=e.department_id)
WHERE salary < (SELECT MAX(salary)
FROM employees
WHERE UPPER(SUBSTR(job_id,4)) NOT IN ('VP', 'PRES', 'MAN', 'MGR')
)
AND UPPER(department_name) IN ('MARKETING','SALES')
ORDER BY first_name, last_name
/
```

James

```select substr(first_name ||' '|| last_name) "Employee", job_id, 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 employee;
```

### 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'));

```

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

James

```select last_name, salary, job_id
from employees
where salary > (select max(salary)
from employees join departments
using (department_id)
join locations
using (location_id)
where upper(city) in ('TORONTO','OXFORD'))
and upper(job_id) not like '%PRES'
```

### 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;
```

```SELECT last_name, first_name, job_id, hire_date
FROM employees e
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 hire_date DESC
/
```

James

```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 hire_date desc;
```

### 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;

```

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

James

```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 != all(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 department_id, job_id
```

### 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;

```

```SELECT department_id, department_name, SUBSTR(NVL(city,'Not Assigned Yet'),1,25) City,
COUNT(DISTINCT job_id) "# of Jobs"
FROM locations l RIGHT OUTER JOIN departments d
USING (location_id)
LEFT OUTER JOIN employees e
USING (department_id)
GROUP BY  department_id, department_name, city
/
```

James

```select department_id, department_name, substr(nvl(city,'Not Assigned Yet'),1,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
```