PL/SQL练习题一(基础)

基础部分：

------------------------------------------------------------------------------

下面的练习题是针对订单表和订单详表：

Order_id, Customer_id, order_date
O1, C1, 01-Jan-2000
O2, C2, 01-Jan-2002
O3, C3, 01-Apr-2002
O4, C4, 01-Apr-2003
O5, C4, 01-Jan-2006
O6,C1, 01-May-2006

Item_id, Order_id

I1, O1

I2, O1

I3, O1

I1, O2

I5, O3

准备数据

CREATE TABLE ORDER_LIST (ORDER_ID CHAR(2), CUSTOMER_ID CHAR(2), ORDER_DATE DATE);
INSERT INTO ORDER_LIST VALUES ('O1','C1',TO_DATE('2000-01-01','YYYY-MM-DD'));
INSERT INTO ORDER_LIST VALUES ('O2','C2',TO_DATE('2002-01-01','YYYY-MM-DD'));
INSERT INTO ORDER_LIST VALUES ('O3','C3',TO_DATE('2002-04-01','YYYY-MM-DD'));
INSERT INTO ORDER_LIST VALUES ('O4','C4',TO_DATE('2003-04-01','YYYY-MM-DD'));
INSERT INTO ORDER_LIST VALUES ('O5','C4',TO_DATE('2006-01-01','YYYY-MM-DD'));
INSERT INTO ORDER_LIST VALUES ('O6','C1',TO_DATE('2006-04-01','YYYY-MM-DD'));
CREATE TABLE ORDER_ITEMS (ITEM_ID CHAR(2), ORDER_ID CHAR(2));
INSERT INTO ORDER_ITEMS VALUES ('I1','O1');
INSERT INTO ORDER_ITEMS VALUES ('I2','O1');
INSERT INTO ORDER_ITEMS VALUES ('I3','O1');
INSERT INTO ORDER_ITEMS VALUES ('I1','O2');
INSERT INTO ORDER_ITEMS VALUES ('I5','O2');
INSERT INTO ORDER_ITEMS VALUES ('I1','O3');
INSERT INTO ORDER_ITEMS VALUES ('I4','O1');
INSERT INTO ORDER_ITEMS VALUES ('I1','O4');

Given order and order items tables, select customer ids of customers who placed orders with more than 3 items (having or subquery)

SELECT A.CUSTOMER_ID, COUNT(B.ITEM_ID)FROM ORDER_LIST A, ORDER_ITEMS BWHERE A.ORDER_ID = B.ORDER_IDGROUP BY A.CUSTOMER_IDHAVING COUNT(B.ITEM_ID) > 3;

Give SQL for the list of customer_ids who placed more than 1 order

SELECT Customer, COUNT(OrderID) FROM OrdersGROUP BY CustomerHAVING Count(OrderID) > 1

Give the Sql for the list of customer_ids who have placed at least 1 order in 2000 and at least 1 order in 2006.

SELECT Customer, COUNT(OrderID) FROM OrdersGROUP BY CustomerHAVING ((Count(OrderID) > 1 AND TO_CHAR(order_date,'YYYY') = 2000) OR (Count(OrderID) > 1 AND TO_CHAR(order_date,'YYYY') = 2006))

Please write a sql which can generate the number of Orders for each year, 2000 to 2006.

SELECT A.YEAR,NVL(B.ORDER_NUMBER,0)FROM (SELECT 2001 AS YEAR FROM DUALUNIONSELECT 2002 AS YEAR  FROM DUALUNIONSELECT 2003 AS YEAR  FROM DUALUNIONSELECT 2004 AS YEAR  FROM DUALUNIONSELECT 2005 AS YEAR  FROM DUALUNIONSELECT 2006 FROM DUAL) A, (SELECT TO_CHAR(ORDER_DATE,'YYYY') YEAR, COUNT(ORDER_ID) ORDER_NUMBERFROM ORDER_LISTGROUP BY TO_CHAR(ORDER_DATE,'YYYY')) BWHERE A.YEAR = B.YEAR (+)ORDER BY A.YEAR;

进阶部分：

---------------------------------------------------------------------------

下面的练习题是针对员工表：

emp_id dep_id manager_id start_date salary
-----------------------------------
1,1,null,2000-01-01,16000
2,1,1,2002-01-01,20000
3,1,1,2002-04-01,9000
4,1,1,2003-04-01,1000
5,2,null,2006-01-01,20000
6,2,5,2006-04-01,28000
7,2,5,2000-04-01,500

准备数据：

CREATE TABLE employee (emp_id NUMBER(5), dep_id NUMBER(5), manager_id NUMBER(5) ,start_date DATE, salary NUMBER(5));
INSERT INTO employee VALUES (1,1,null,TO_DATE('2000-01-01','YYYY-MM-DD'), 16000);
INSERT INTO employee VALUES (2,1,1,TO_DATE('2002-01-01','YYYY-MM-DD'), 20000);
INSERT INTO employee VALUES (3,1,1,TO_DATE('2002-04-01','YYYY-MM-DD'), 9000);
INSERT INTO employee VALUES (4,1,1,TO_DATE('2003-04-01','YYYY-MM-DD'), 1000);
INSERT INTO employee VALUES (5,2,null,TO_DATE('2006-01-01','YYYY-MM-DD'), 20000);
INSERT INTO employee VALUES (6,2,5,TO_DATE('2006-04-01','YYYY-MM-DD'), 28000);
INSERT INTO employee VALUES (7,2,5,TO_DATE('2000-04-01','YYYY-MM-DD'), 500);

Display the employee records who joins the department before their manager?

SELECT a.*FROM employee a, employee bWHERE a.manager_id = b.emp_idAND a.start_date < b.start_date;

Display employee records getting more salary than the average salary in their department?

SELECT a.*FROM employee a, (SELECT dep_id, AVG(salary) avg_salFROM employeeGROUP BY dep_id) bWHERE a.dep_id = b.dep_idAND a.salary > b.avg_sal

或相关子查询的写法：

SELECT a.*FROM employee aWHERE salary > (SELECT AVG(salary) max_salFROM employee bWHERE a.dep_id = b.dep_id);

Display the highest paid employee in each department.

SELECT a.*FROM employee a, (SELECT dep_id, MAX(salary) max_salFROM employeeGROUP BY dep_id) bWHERE a.dep_id = b.dep_id AND a.salary = b.max_sal

或

SELECT a.*FROM employee aWHERE salary = (SELECT MAX(salary) max_salFROM employee bWHERE a.dep_id = b.dep_id );