Difference between revisions of "J.Y.S"

From CDOT Wiki
Jump to: navigation, search
(DML Statements (INSERT INTO))
(Blanked the page)
 
(24 intermediate revisions by 2 users not shown)
Line 1: Line 1:
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=dbs301 yjeong]|| 647 - 832 - 6771
 
|-
 
 
|-
 
|Saad|| || || ||
 
|-
 
 
|-
 
|James|| || || ||
 
|-
 
|}
 
 
=Assignment 3 (worth 9%)=
 
==Due Date==
 
Thursday, December 2nd, 3:00pm
 
Printout of all TIC charts and DDL / DML statements
 
 
==Solution==
 
==== DDL Statements (CREATE TABLE) ====
 
1. AREA
 
<pre>
 
CREATE TABLE AREA
 
(
 
  AreaID    NUMBER(4)    CONSTRAINT area_areaid_pk PRIMARY KEY,
 
  AreaName  VARCHAR2(30) NOT NULL CONSTRAINT area_areaname_uk UNIQUE,
 
  ElmSchool  VARCHAR2(40),
 
  MidSchool  VARCHAR2(40),
 
  HighSchool VARCHAR2(40),
 
  Comments  VARCHAR2(100)
 
);
 
</pre>
 
2. AGENCY
 
<pre>
 
CREATE TABLE AGENCY
 
(
 
  AgencyID    NUMBER(4)    CONSTRAINT agency_agencyid_pk PRIMARY KEY,
 
  AgencyName  VARCHAR2(40) NOT NULL CONSTRAINT agency_agencyname_uk UNIQUE,
 
  AgencyPhone  VARCHAR2(15) NOT NULL CONSTRAINT agency_agencyphone_uk UNIQUE
 
);
 
</pre>
 
3. PROPERTY
 
<pre>
 
CREATE TABLE PROPERTY
 
(
 
  PropertyID  NUMBER(6),
 
  Street      VARCHAR2(40) NOT NULL,
 
  City        VARCHAR2(30) NOT NULL,
 
  Province    CHAR(2)      NOT NULL,
 
  PostalCode  CHAR(6)      NOT NULL,
 
  Bedrooms    NUMBER(1)    NOT NULL,
 
  Bathrooms    NUMBER(1)    NOT NULL,
 
  ListingPrice NUMBER(6)    NOT NULL,
 
  AreaID      NUMBER(4)    NOT NULL,
 
  CONSTRAINT property_propertyid_pk  PRIMARY KEY(PropertyID),
 
  CONSTRAINT property_bedrooms_ck    CHECK(Bedrooms BETWEEN 1 AND 9),
 
  CONSTRAINT property_bathrooms_ck    CHECK(Bathrooms BETWEEN 1 AND 9),
 
  CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999),
 
  CONSTRAINT property_areaid_fk      FOREIGN KEY(AreaID) REFERENCES AREA(AreaID)
 
);
 
</pre>
 
4. SINGLE
 
<pre>
 
CREATE TABLE SINGLE
 
(
 
  PropertyID  NUMBER(6), 
 
  LotSize    NUMBER(8,2) NOT NULL,
 
  CONSTRAINT single_propertyID_pk PRIMARY KEY(PropertyID),                       
 
  CONSTRAINT single_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID),
 
  CONSTRAINT single_lotsize_ck    CHECK(LotSize > 0)
 
);
 
 
</pre>
 
5. CONDO
 
<pre>
 
CREATE TABLE CONDO
 
(
 
  PropertyID  NUMBER(6),
 
  Fee        NUMBER(8,2) NOT NULL,
 
  CONSTRAINT condo_propertyID_pk PRIMARY KEY(PropertyID),
 
  CONSTRAINT condo_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID),
 
  CONSTRAINT condo_fee_ck        CHECK(Fee > 0)
 
);
 
 
</pre>
 
6. OUTLET
 
<pre>
 
CREATE TABLE OUTLET
 
(
 
  OutletID    NUMBER(4), 
 
  OutletName  VARCHAR2(40) NOT NULL,
 
  OutletPhone VARCHAR2(15) NOT NULL,
 
  OutletType  CHAR(1)      NOT NULL,
 
  CONSTRAINT outlet_outletid_pk        PRIMARY KEY(OutletID),
 
  CONSTRAINT outlet_outlettype_ck      CHECK(OutletType IN('N','M','W','F')),
 
  CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType)
 
);
 
 
</pre>
 
7. ADVERTISEMENT
 
<pre>
 
CREATE TABLE ADVERTISEMENT
 
(
 
  PropertyID NUMBER(6),
 
  OutletID  NUMBER(4),
 
  AdvDate    DATE,
 
  AdvCost    NUMBER(6) NOT NULL,
 
  CONSTRAINT ad_adid_pk      PRIMARY KEY(PropertyID, OutletID),
 
  CONSTRAINT ad_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID),
 
  CONSTRAINT ad_outletid_fk  FOREIGN KEY(OutletID)  REFERENCES OUTLET(OutletID),
 
  CONSTRAINT ad_advcost_ck    CHECK(AdvCost > 0)
 
);
 
</pre>
 
8. CLIENT
 
<pre>
 
CREATE TABLE CLIENT
 
(
 
  ClientID  NUMBER(6),
 
  Fname    VARCHAR2(30) NOT NULL,
 
  Lname    VARCHAR2(30) NOT NULL,
 
  Phone    VARCHAR2(15) NOT NULL,
 
  Email    VARCHAR2(40),
 
  RefID    NUMBER(6),
 
  CONSTRAINT client_clientid_pk PRIMARY KEY(ClientID),
 
  CONSTRAINT client_phone_uk    UNIQUE(Phone),
 
  CONSTRAINT client_email_uk    UNIQUE(Email),
 
  CONSTRAINT client_refid_fk    FOREIGN KEY(RefID) REFERENCES CLIENT(ClientID)
 
);
 
</pre>
 
9. DEED
 
<pre>
 
CREATE TABLE DEED
 
(
 
  PropertyID  NUMBER(6),
 
  SellerID    NUMBER(6),
 
  OwnPct      NUMBER(5,2)  NOT NULL,
 
  BuyerID    NUMBER(6),
 
  BuyPrice    NUMBER(7),
 
  CONSTRAINT deed_deedid_pk    PRIMARY KEY(PropertyID, SellerID),
 
  CONSTRAINT deed_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID),
 
  CONSTRAINT deed_sellerid_fk  FOREIGN KEY(SellerID) REFERENCES CLIENT(ClientID),
 
  CONSTRAINT deed_buyerid_fk    FOREIGN KEY(BuyerID) REFERENCES CLIENT(ClientID),
 
  CONSTRAINT deed_ownpct_ck    CHECK(OwnPct > 0 AND OwnPct <= 100),
 
  CONSTRAINT deed_buyprice_ck  CHECK(BuyPrice BETWEEN 1 AND 4999999)
 
);
 
 
</pre>
 
 
==== DML Statements (INSERT INTO) ====
 
 
1. AREA<BR>
 
 
INSERT ALL
 
INTO AREA VALUES(1001, 'Bayview Village', 'Adrienne Clarkson Public School', 'Amesbury Middle School', 'Bayview Secondary School', 'Upper middle class to upper class neighborhood in Toronto.')
 
INTO AREA VALUES(3001,'Laurelwood Elizabeth Ziegler Primary School','Waterloo Middle School','Saint David Catholic Secondary School','Close to University of Waterloo and many popular amenities.')
 
INTO AREA VALUES(4001,'Silver Heights','Alison Park Primary School'.'Eastwood Middle School','King Edward Secondary School',null)
 
INTO AREA VALUES(5001,'Kitsilano','Henry Hudson Elementary School','St George School','Kitsilano Secondary School','Very well-off area.')
 
SELECT * FROM DUAL;
 
<BR>
 
 
2. AGENCY<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
 
3. PROPERTY<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
 
4. SINGLE<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
5. CONDO<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
6. OUTLET<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
7. ADVERTISEMENT<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
8. CLIENT<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
<BR>
 
9. DEED<BR>
 
INSERT ALL
 
INTO VALUES()
 
INTO VALUES()
 
INTO VALUES()
 
SELECT * FROM DUAL;
 
 
==== CREATE VIEW ====
 
 
=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==
 
<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.===
 
''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, 'mm') IN (05, 11)
 
ORDER BY 4 DESC;
 
</pre>
 
''Saad''
 
<pre>
 
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
 
/
 
</pre>
 
''James''
 
<pre>
 
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;
 
</pre>
 
 
''FINAL''
 
<pre>
 
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;
 
</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>
 
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)
 
/
 
</pre>
 
''James''
 
<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 upper(job_id) in ('IT_PROG', 'ST_CLERK')
 
order by employee_id;
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '
 
          || job_id || ' will have a new salary of ' || TO_CHAR(salary * 1.15, 'fm$999999') "Happy Employees"
 
FROM      employees
 
WHERE    salary BETWEEN 5000 AND 12000
 
AND      UPPER(job_id) IN ('IT_PROG', 'ST_CLERK')
 
ORDER BY  employee_id;
 
</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>
 
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
 
/
 
</pre>
 
''James''
 
<pre>
 
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;
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT    last_name, salary, job_id,
 
          SUBSTR(NVL(TO_CHAR(manager_id), 'No Manager'), 1, 10) "Manager #",
 
          LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "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  salary DESC;
 
 
</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>
 
SELECT SUBSTR(first_name||' '||last_name, 25) Employee, job_id,
 
        LPAD(TO_CHAR(salary,'$99,999'), 12, '=') Salary, department_name
 
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
 
/
 
</pre>
 
''James''
 
<pre>
 
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";
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT  SUBSTR(first_name ||' '|| last_name, 1, 25) "Employee", job_id,
 
        LPAD(TO_CHAR(salary, 'fm$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(UPPER(job_id), 4) NOT IN ('PRES','VP','MAN','MGR'))
 
ORDER BY 1;
 
</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>
 
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'
 
/
 
</pre>
 
''James''
 
<pre>
 
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'
 
</pre>
 
 
''FINAL''
 
<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>
 
 
===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>
 
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
 
/
 
</pre>
 
''James''
 
<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 hire_date desc;
 
</pre>
 
 
''FINAL''
 
<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 hire_date DESC;
 
</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>
 
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
 
/
 
</pre>
 
''James''
 
<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 != 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
 
</pre>
 
 
''FINAL''
 
<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 != 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;
 
</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>
 
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
 
/
 
</pre>
 
''James''
 
<pre>
 
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
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT    department_id, department_name,
 
          SUBSTR(NVL(city, 'Not Assinged 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;
 
 
</pre>
 

Latest revision as of 20:04, 17 September 2011