join 6 .
SELECT customer.cid, customer.cnm, product.pid, product.pnm, a.cnt
FROM
(SELECT cid , pid , SUM(cnt) cnt
FROM cycle
GROUP BY cid , pid ) a , customer , product
WHERE customer.cid = a.cid
AND a.pid = product.pid
]
join 7
SELECT product.pid, product.pnm , a.cnt
FROM
(SELECT pid , SUM(cnt) cnt
FROM cycle
GROUP BY pid ) a , product
WHERE product.pid = a.pid
join 8
SELECT r.region_id, r.region_name , UPPER(c.country_name)
FROM countries c, regions r
WHERE r.region_id = c.region_id
AND r.region_NAME = 'Europe'
join 9
SELECT regions.region_id, regions.region_name , countries.country_name , locations.city
FROM locations, regions, countries
WHERE countries.region_id = regions.region_id
AND countries.country_id = locations.country_id
AND regions.region_name = 'Europe' ;
join 10
SELECT regions.region_id, regions.region_name, countries.country_name, locations.city , departments.department_name
FROM regions, locations, countries, departments
WHERE regions.region_id = '1'
AND regions.region_name = 'Europe'
AND regions.region_id = countries.region_id
AND locations.location_id = departments.location_id
AND locations.country_id = countries.country_id
JOIN 11
SELECT a.region_id , a.region_name , a.country_name , a.city , a.department_name , a.name
FROM
(SELECT *
FROM
(SELECT ROWNUM rn, c.region_id, r.region_name , c.country_name , l.city , d.department_name , e.first_name||e.last_name name
FROM countries c, regions r, locations l , departments d , employees e
WHERE c.country_id = l.country_id
AND c.region_id = r.region_id
AND l.location_id = d.location_id
AND d.department_id = e.department_id
AND r.region_id = '1'
AND r.region_name = 'Europe'
AND c.country_name = 'United Kingdom' ) a
WHERE RN < 11 ) a
join 12
미완성 * 7,8,9,10 정렬이 안됨
SELECT employees.employee_id , first_name||last_name name , employees.job_id , jobs.job_title
FROM employees , jobs
WHERE employee_id IN ( '206' , '205' , '200', '100' , '102' , '101' , '110', '109'
, '113' , '111' )
AND employees.job_id = jobs.job_id
ORDER BY employees.job_id ;
join 13 번 문제
SELECT b.employee_id, b.first_name||b.last_name mgr_name ,
a.employee_id employee_id , a.first_name||a.last_name name ,
a.job_id
FROM employees a JOIN employees b ON ( a.manager_id = b.employee_id )
WHERE a.manager_id = '100'
AND a.job_id = 'ST_MAN' OR a.job_id = 'SA_MAN'
이거랑 jobs 테이블이랑 조인을 해야함
SELECT *
FROM
(SELECT b.employee_id, b.first_name||b.last_name mgr_name ,
a.employee_id employee_id , a.first_name||a.last_name name ,
a.job_id
FROM employees a JOIN employees b ON ( a.manager_id = b.employee_id )
WHERE a.manager_id = '100'
AND a.job_id = 'ST_MAN' OR a.job_id = 'SA_MAN')h LEFT OUTER JOIN jobs ON ( h.job_id = jobs.job_id )
outer join 5 번 - 과제
SELECT *
FROM
(SELECT p.pid, p.pnm ,NVL(c.cid , 1 ) cid , NVL(c.day, 0 ) day ,NVL(c.cnt, 0 ) cnt
FROM product p left outer join cycle c
ON ( p.pid = c.pid )
AND cid = '1') a Left OUTER JOIN customer ON ( a.cid = customer.cid)
* 문제 : 정렬을 못함..
댓글
댓글 쓰기