/*
Herman van der Meulen
s1013123
*/

/*
1
*/
SELECT REPLACE('Oracle Internet Academy', 'Internet', '2004-2005') AS "The Best Class"
FROM DUAL
/
/*
2
*/
SELECT LAST_NAME, SALARY / 3 AS "Salary"
FROM H_EMPLOYEES
WHERE MOD(SALARY, 3) = 0
/
/*
3
*/
SELECT NEXT_DAY(TO_DATE('21-08-2008', 'dd-mm-yyyy'), 'FRI') AS "First Friday"
FROM DUAL
/
/*
4
*/
SELECT department_id,
  SUM(CASE WHEN salary > 10000 AND salary < 100000 THEN 1 ELSE 0 END) "$10001-$99999",
  SUM(CASE WHEN salary > 5000 AND salary <= 10000 THEN 1 ELSE 0 END) "$5001-$10000",
  SUM(CASE WHEN salary > 2000 AND salary <= 5000 THEN 1 ELSE 0 END) "$2001-$5000",
  SUM(CASE WHEN salary >= 0 AND salary <= 2000 THEN 1 ELSE 0 END) "< $2000"
FROM H_EMPLOYEES
WHERE salary IS NOT NULL
GROUP BY department_id
ORDER BY department_id
/
/*
5
*/
SELECT ID, FIRST_NAME || ' ' || LAST_NAME AS "name",to_char(salary, '$99999.00') AS "salary"
FROM F_STAFFS
/
/*
6
*/
SELECT e.LAST_NAME, e.SALARY, j.GRADE_LEVEL
FROM H_EMPLOYEES e, H_JOB_GRADES j
WHERE e.SALARY BETWEEN j.LOWEST_SAL AND j.HIGHEST_SAL
ORDER BY e.LAST_NAME
/
/*
7
*/
SELECT c.COUNTRY_NAME, c.REGION_ID, r.REGION_NAME
FROM H_COUNTRIES c, H_REGIONS r
WHERE c.REGION_ID = r.REGION_ID AND REGION_NAME = 'Americas'
/
/*
8
*/
SELECT e.FIRST_NAME, e.LAST_NAME, e.HIRE_DATE, e.JOB_ID, j.JOB_TITLE, j.MAX_SALARY
FROM H_EMPLOYEES e, H_JOBS j
WHERE e.JOB_ID = j.JOB_ID AND MAX_SALARY > 12000
/
/*
9
*/
SELECT e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_NAME
FROM H_EMPLOYEES e
FULL OUTER JOIN H_DEPARTMENTS d
ON (e.DEPARTMENT_ID = d.DEPARTMENT_ID)
/
/*
10
*/
SELECT s.DESCRIPTION, sa.SHIFT_ASSIGN_DATE
FROM F_SHIFTS s
LEFT OUTER JOIN F_SHIFT_ASSIGNMENTS sa
ON (s.CODE = sa.CODE)
/
/*
11 ('IN' niet verplicht, in dit geval eigenlijk niet nodig, maar vaak beter, voorzorg...)
*/
SELECT *
FROM D_EVENTS
WHERE THEME_CODE IN
(
  SELECT THEME_CODE
  FROM D_THEMES
  WHERE DESCRIPTION = 'Tropical'
)
/
/*
12
*/
SELECT *
FROM H_EMPLOYEES
WHERE DEPARTMENT_ID IN
(
  SELECT DEPARTMENT_ID
  FROM H_DEPARTMENTS
  WHERE DEPARTMENT_NAME = 'IT'
)
/
/*
13
*/
SELECT LAST_NAME, SALARY
FROM H_EMPLOYEES
WHERE SALARY = ANY
(
  SELECT MIN(SALARY)
  FROM H_EMPLOYEES
  GROUP BY DEPARTMENT_ID
)
/
/*
14
*/
CREATE TABLE my_songs AS
(
  SELECT *
  FROM D_SONGS
)
/
CREATE TABLE my_types AS
(
  SELECT *
  FROM D_TYPES
)
/
/*
15
*/
CREATE OR REPLACE VIEW v_d_songs AS
(
  SELECT ID, TITLE AS "Song Title", ARTIST, TYPE_CODE
  FROM MY_SONGS
  WHERE TYPE_CODE = 
  (
    SELECT CODE
    FROM MY_TYPES
    WHERE DESCRIPTION = 'New Age'
  )
)
/
/*
16
*/
CREATE OR REPLACE VIEW v_d_songs AS
(
  SELECT ID, TITLE AS "Song Title", DURATION, ARTIST, TYPE_CODE
  FROM MY_SONGS
  WHERE TYPE_CODE = 
  (
    SELECT CODE
    FROM MY_TYPES
    WHERE DESCRIPTION = 'New Age'
  )
)
/ /* zie bovenstaande aanpassing van de view!! */
INSERT INTO V_D_SONGS (ID, "Song Title", DURATION, ARTIST, TYPE_CODE)
VALUES (88, 'Mello Jello', '2 min', 'The What', 77)
/
/*
17
*/
SELECT LPAD(first_name || ' ' || last_name, LENGTH(first_name || ' ' || last_name) + 2 * LEVEL - 2, '-') AS "Organization"
FROM H_EMPLOYEES
WHERE MANAGER_ID <>
(
  SELECT MANAGER_ID
  FROM H_EMPLOYEES
  WHERE LAST_NAME = 'Mourgos'
)
CONNECT BY PRIOR employee_id = manager_id
/
/*
18
*/

/
/*
19
*/
SE