Docstoc

Oracle OCP excercise quries

Document Sample
Oracle OCP excercise quries Powered By Docstoc
					MSIT-12-01                                                              Qaiser Khan


                               Assignment # 3
1)   SELECT e.last_name, e.department_id, d.department_name
     FROM employees e
     JOIN departments d
          ON d.department_id = e.department_id



2)   SELECT DISTINCT e.job_id,
           (SELECT location_id from departments where department_id = 90)
                  “Dept 90 Location”
     FROM employees e, departments d
     WHERE e.department_id = d.department_id
     AND e.department_id = 30;




3)   SELECT e.last_name, d.department_name, d.location_id, l.city
     FROM employees e, departments d, locations l
     WHERE e.department_id = d.department_id
     AND d.location_id = l.location_id
     AND e.commission_pct IS NOT NULL




4)   SELECT e.last_name, d.department_name,
     FROM employees e, departments d
     WHERE e.department_id = d.department_id
     AND e.last_name LIKE ‘%a%’




5)   SELECT e.last_name, e.job_id, e.department_id, d.department_name
      FROM employees e
     JOIN departments d
            ON e.department_id = d.department_id
     JOIN locations l
            ON d.location_id = l.location_id
     WHERE LOWER(l.city) = 'toronto'
MSIT-12-01                                                              Qaiser Khan

6)    SELECT e.last_name "Employee", e.employee_id "EMP#",
            m.last_name "Manager", m.employee_id "Mgr#"
      FROM employees e
      JOIN employees m
            ON w.manager_id = m.employee_id




7)    SELECT e.last_name "Employee", e.employee_id "EMP#",
            m.last_name "Manager", m.employee_id "Mgr#"
      FROM employees e
      LEFT OUTER JOIN employees m
            ON w.manager_id = m.employee_id




8)    SELECT e.department_id "department", e.last_name "employee",
      c.last_name "Co Worker"
      FROM employees e
      JOIN employees c
              ON e.department_id = c.department_id
      WHERE e.employee_id != c.employee_id
      ORDER BY e.department_id, e.last_name, c.last_name




9) a) DESC JOB_GRADES
   b) Schema for JOB_GRADES was not provided in HR schema given for this
assignment, so I am taking a wild guess based on the image in question.

      SELECT e.last_name, e.job_id, d.department_name,e.salary, g.grade_level
      FROM employees e, departments d, job_grades g
      WHERE e.department_id = d.department_id
      AND e.salary BETWEEN g.lowest_sal AND g.highest_sal



10)   SELECT e.last_name, e.hire_date
      FROM employees e, employees d
      WHERE d.last_name = 'Davies'
      AND d.hire_date < e.hire_date
MSIT-12-01                                                               Qaiser Khan

11)        SELECT e.last_name “Employee”, e.hire_date “Emp Hired”, m.last_name
           “Manager”, m.hire_date “Mgr Hired”
           FROM employees e, employees m
           WHERE e.manager_id = m.employee_id
           AND e.hire_date < m.hire_date




Practice 5:

      1)   True
      2)   False
      3)   True
      4)   SELECT ROUND(MAX(salary),0) "Maximum",
           ROUND(MIN(salary),0) "Minimum",
           ROUND(SUM(salary),0) "Sum",
           ROUND(AVG(salary),0) "Average"
           FROM employees



      5) SELECT job_id,
         ROUND(MAX(salary),0) "Maximum",
         ROUND(MIN(salary),0) "Minimum",
         ROUND(SUM(salary),0) "Sum",
         ROUND(AVG(salary),0) "Average"
         FROM employees
         GROUP BY job_id



      6) SELECT job_id, COUNT(*)
         FROM employees
         GROUP BY job_id



      7) SELECT COUNT(DISTINCT manager_id) "Number of Managers"
         FROM employees



      8) SELECT MAX(salary) - MIN(salary) DIFFERENCE
         FROM employees
MSIT-12-01                                                          Qaiser Khan

  9) SELECT manager_id, MIN(salary)
     FROM employees
     WHERE manager_id IS NOT NULL
     GROUP BY manager_id
     HAVING MIN(salary) > 6000
     ORDER BY MIN(salary) DESC




  10) SELECT d.department_name "Name", d.location_id "Location ",
      COUNT(*) "Number of People",
      ROUND(AVG(salary),2) "Salary"
      FROM employees e, departments d
      WHERE e.department_id = d.department_id
      GROUP BY d.department_name, d.location_id



  11) SELECT COUNT(*) “total”,
      SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0)) “1995”,
      SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0)) “1996”,
      SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0)) “1997”,
      SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0)) “1998”
      FROM employees




  12) SELECT job_id "Job",
      SUM(DECODE(department_id , 20, salary)) "Dept 20",
      SUM(DECODE(department_id , 50, salary)) "Dept 50",
      SUM(DECODE(department_id , 80, salary)) "Dept 80",
      SUM(DECODE(department_id , 90, salary)) "Dept 90",
      SUM(salary) "Total"
      FROM employees
      GROUP BY job_id

				
DOCUMENT INFO
Shared By:
Stats:
views:27
posted:4/24/2012
language:
pages:4