View source for YSL DBS501 TeamPage



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||Mohammad || saadism ||[mailto:smohammad8@learn.senecac.on.ca?subject=db301 smohammad8] ||(647) 894-7223
|-

|-
|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(15) 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(30) 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(15) NOT NULL,
  Province     CHAR(2)      NOT NULL,
  PostalCode   CHAR(6)      NOT NULL,
  Bedrooms     NUMBER(1)    NOT NULL,
  Bathrooms    NUMBER(1)    NOT NULL,
  ListingPrice NUMBER(7)    NOT NULL,
  AreaID       NUMBER(4)    NOT NULL,
  AgencyID     NUMBER(4),
  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(20) 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>

Before Creating CLIENT table, you should rename the existing CLIENT table so that
we can use that name. Execute following:
<pre>
ALTER TABLE CLIENT RENAME TO CLIENT_2;
</pre>

8. CLIENT
<pre>
CREATE TABLE CLIENT
(
  ClientID  NUMBER(6),
  Fname     VARCHAR2(15) NOT NULL,
  Lname     VARCHAR2(15) NOT NULL,
  Phone     VARCHAR2(15) NOT NULL,
  Email     VARCHAR2(30),
  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>
<pre>
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;
</pre>
<BR>

2. AGENCY<BR>
<pre>
INSERT ALL
INTO AGENCY VALUES(1055,'Sutton Group Regal Realty','416.875.9873')
INTO AGENCY VALUES(3079,'MacDonald Realty','604.986.0231')
INTO AGENCY VALUES(7024,'Royal LePage Groupe Newton','514.234.9283')
SELECT * FROM DUAL;
</pre>
<BR>

3. PROPERTY<BR>
<pre>
INSERT ALL
INTO PROPERTY VALUES(134982,q'[27 King's College Circle]','Toronto','ON','M5S1A1',2,2,380000,1001,1055)
INTO PROPERTY VALUES(149800,'17 Pleasant View Dr.','Toronto','ON','M2J2H1',3,2,470000,1001, null)
INTO PROPERTY VALUES(152932,'17 Barberry Pl. #1313','Toronto','ON','M8W7V4',1,1,259888,1001, null)
INTO PROPERTY VALUES(495820,'776 Laurelwood Dr.','Waterloo','ON','H3A2T5',2,2,295000,3001,3079)
INTO PROPERTY VALUES(500211,'409 Kelso Cr.','Cambridge','ON','H3Y7S3',3,2,485000,4001, null)
INTO PROPERTY VALUES(139204,'3043 Crown St.','Vancouver','BC','U3J3M6',5,3,1398000,5001,7024)
SELECT * FROM DUAL;
</pre>
<BR>
4. SINGLE<BR>
<pre>
INSERT ALL
INTO SINGLE VALUES(134982,3716)
INTO SINGLE VALUES(149800,1005)
INTO SINGLE VALUES(139204,5400)
SELECT * FROM DUAL;
</pre>
<BR>
5. CONDO<BR>
<pre>
INSERT ALL
INTO CONDO VALUES(152932,250)
INTO CONDO VALUES(495820,320)
INTO CONDO VALUES(500211,550)
SELECT * FROM DUAL;
</pre>
<BR>
6. OUTLET<BR>
<pre>
INSERT ALL
INTO OUTLET VALUES(1000,'Toronto Star','416.923.1782','N')
INTO OUTLET VALUES(2001,'Waterloo Living','519.098.9283','M')
INTO OUTLET VALUES(8001,'Canadian Life','416.982.0192','W')
SELECT * FROM DUAL;
</pre>
<BR>
7. ADVERTISEMENT<BR>
<pre>
INSERT ALL
INTO ADVERTISEMENT VALUES(134982,1000,'25-NOV-10',350)
INTO ADVERTISEMENT VALUES(149800,1000,'27-NOV-10',250)
INTO ADVERTISEMENT VALUES(495820,2001,'27-NOV-10',300)
SELECT * FROM DUAL;
</pre>
<BR>
8. CLIENT<BR>
<pre>
INSERT ALL
INTO CLIENT VALUES(1010,'Andrew','Lee','647.978.0192','andrew11@hotmail.com', null)
INTO CLIENT VALUES(1011,'Daniel','Smith','416.895.9762','dsmith@gmail.com',5010)
INTO CLIENT VALUES(1012,'Olivia','Wilson','647.915.2738','oliviaorange@gmail.com', null)
INTO CLIENT VALUES(1013,'Jayden','Martin','519.225.1928','jmartin@rogers.com', null)
INTO CLIENT VALUES(1014,'Maria','Brown','416.182.0021','maria.brown@hotmail.com', null)
INTO CLIENT VALUES(2010,'Ethan','Roy','226.289.0394','ethanrockstar@bell.com', null)
INTO CLIENT VALUES(2011,'Sophia','Tremblay','647.297.2918','stremblay@gmail.com',5010)
INTO CLIENT VALUES(3010,'Ryan','Wilson','604.837.1029','wilsonryan@gmail.com', null)
INTO CLIENT VALUES(3012,'Luis','Jones','416.832.6771','luis7787@rogers.com', null)
INTO CLIENT VALUES(5010,'Mary','Anderson','647.234.5690','andersonpink@gmail.com', null)
INTO CLIENT VALUES(7010,'James','White','647.298.9182','james.white@bell.com',1010)
SELECT * FROM DUAL;
</pre>
<BR>
9. DEED<BR>
<pre>
INSERT ALL
INTO DEED VALUES(149800,1010,75,5010,352300)
INTO DEED VALUES(139204,1012,100,3010,1394000)
INTO DEED VALUES(134982,2011,50,1014,200000)
SELECT * FROM DUAL;
</pre>

==== CREATE VIEW ====
<pre>
CREATE VIEW PropAreaLocSeller_VU AS
  SELECT SUBSTR((ar.AreaName || ', ' || p.City || ', ' || p.Street || ', ' || p.PostalCode), 1, 60) "Property Location",
         TO_CHAR(p.ListingPrice,'$9,999,999') "Price", sgl.LotSize || ' sqft' "LotSize",
         s.Lname || ', ' || s.Fname "Full Name", s.phone "Phone", d.OwnPct "OwnPct", NVL(ag.AgencyName, 'No Agency') "Agency"
  FROM   Property p
  JOIN Single sgl ON (p.PropertyID = sgl.PropertyID)
  JOIN Area ar    ON (p.AreaID = ar.AreaID)
  LEFT OUTER JOIN Agency ag  ON (p.AgencyID = ag.AgencyID)
  JOIN Deed d     ON (p.PropertyID = d.PropertyID)
  JOIN Client s   ON (d.SellerID = s.ClientID);
</pre>

==== Display data from VIEW ====
<pre>

SELECT   *

   FROM  PROPAREALOCSELLER_VU

   WHERE INSTR("Property Location", 'Bayview Village') !=0

   AND   INSTR("Agency", 'Sutton') !=0;

</pre>

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