This page is to efficiently manage our group assignments!
== Member List ==
*[mailto:,,yjeong@learn.senecac.on.ca,?subject=dbs301 Email All]
{| class="wikitable sortable" border="1" cellpadding="5"
! First Name !! Last Name !! wiki id !! Learn e-mail !! Phone
|-
|[[User:Yujin.jeong | YuJin]]||Jeong||[[Special:Contributions/yujin.jeong | yujin.jeong]]||[mailto:yjeong@learn.senecac.on.ca?sujbect=gam666 yjeong]|| 647 - 832 - 6771
|-
|-
|Saad|| || || ||
|-
|-
|James|| || || ||
|-
|}
=Assignment 1=
==Due Date==
Thursday, October 21st, 4:00pm
Printout submission only( SELECT Statements + ALL outputs)
==Description==
[https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1]
==Solution==
===1.===
''YuJin''
<pre>
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, 'Mon') IN ('May', 'Nov')
ORDER BY hire_date DESC;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===2.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===3.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===4.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===5.===
''YuJin''
<pre>
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'));
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===6.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===7.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
===8.===
''YuJin''
<pre>
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;
</pre>
''Saad''
<pre>
</pre>
''James''
<pre>
</pre>
