一些SQL問題

以下除特別說明都針對Oracle中的scott使用者的emp表、dept表、salgrade表

1. 查詢emp表中每個員工的ename、sal、及其與所在部門的平均sal的差距。

2. (1) 利用emp表及salgrade表，確定每個員工的sal級別。 (2) 統計emp表中每年入職的人數。

3. 建立candidates表如下：

create candidates

(

candname varchar2(10),

gender char(1) check gender in('F','M')

)

新增記錄如下：

insert into candidates values('Neil','M');

insert into candidates values('Trevor','M');

insert into candidates values('Terresa','F');

insert into candidates values('Mary','F');

進行一次約會，要求男女搭配，用SQL給出約會的所有可能方案。

4. 三個表users、messages、messageread的結構如下：

SQL> desc users
Name Null? Type
----------------------- -------- ----------------
USERID NOT NULL NUMBER(38)
USERNAME NOT NULL VARCHAR2(25)

SQL> desc messages
Name Null? Type
----------------------- -------- ----------------
MSGID NOT NULL NUMBER(38)
MSG NOT NULL VARCHAR2(100)

SQL> desc messageread
Name Null? Type
----------------------- -------- ----------------
MSGID NOT NULL NUMBER(38)
USERID NOT NULL NUMBER(38)

表中記錄如下：

SQL> select * from users;

USERID USERNAME
---------- -------------------------
1 Bruce
2 Darren
3 Umachandar

SQL> select * from messages;

MSGID MSG
---------- ----------------------------------------------------------------------
1 Someone called and said that you made her heart double-click
2 Your floppy disk experienceda crash
3 Someone sprayed instant glue on all keyboards. Don't touuuuccchh

SQL> select * from messageread;

MSGID USERID
---------- ----------
1 1
1 2
2 2
2 3
3 3
3 1

6 rows selected.

要求查詢未被某個使用者讀取的資訊內容及使用者名稱稱：

select distinct u.username, u.userid,m.msgid, m.msg
from users u, messages m, messageread mr
where not exists(select * from messageread mmrr where u.userid=mmrr.userid and m.msgid=mmrr.msgid)

5. 下面是三個與訂單管理有關的三張表：orders、orderdetails、orderpayments:

SQL> desc orders
Name Null? Type
----------------------- -------- ----------------
ORDERID NOT NULL NUMBER(38)
CUSTID NOT NULL NUMBER(38)
ODATE NOT NULL DATE

SQL> desc orderdetails
Name Null? Type
----------------------- -------- ----------------
ORDERID NOT NULL NUMBER(38)
PARTNO NOT NULL NUMBER(38)
QTY NOT NULL NUMBER(38)

SQL> desc orderpayments
Name Null? Type
----------------------- -------- ----------------
ORDERID NOT NULL NUMBER(38)
PAYMENTS NOT NULL NUMBER(38)
VALUE NOT NULL NUMBER(38)

SQL> select * from orders;

ORDERID CUSTID ODATE
---------- ---------- ---------
1 1001 15-APR-07
2 1002 12-MAR-07

SQL> select * from orderdetails;

ORDERID PARTNO QTY
---------- ---------- ----------
1 101 5
1 102 10
2 101 8
2 102 2

SQL> select * from orderpayments;

ORDERID PAYMENTS VALUE
---------- ---------- ----------
1 1 75
1 2 75
2 1 50
2 2 50

要求查詢每個訂單的id號，客戶id，及每張訂單的qty總和，value總和：

SQL> select orderid, custid, odate,
2 (select sum(qty) from orderdetails where orderid=orders.orderid group by orderid) sum_of_qty,
3 (select sum(value) from orderpayments where orderid=orders.orderid group by orderid) sum_ofalue
4 from orders
5 /
ORDERID CUSTID ODATE SUM_OF_QTY SUM_OF_VALUE
---------- ---------- --------- ---------- ------------
1 1001 15-APR-07 15 150
2 1002 12-MAR-07 10 100

6. case的用法：

SQL> select sum(case when deptno=10 then sal else 0 end) as sum10,
2 sum(case when deptno=20 then sal else 0 end) as sum20
3 from emp
4 /

SUM10 SUM20
---------- ----------
4500 10875