一些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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37724/viewspace-152598/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/37724/viewspace-152598/