sql server 多表查询 Group by

问题描述：
已知关系模式：
S (SNO,SNAME）                       学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE)        选课关系。SCGRADE 为成绩

要求实现如下5个处理：
   1 ． 找出没有选修过“李明”老师讲授课程的所有学生姓名
   2 ． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
   3 ． 列出既学过“ 1 ”号课程，又学过“ 2 ”号课程的所有学生姓名
   4 ． 列出“ 1 ”号课成绩比“ 2 ”号同学该门课成绩高的所有学生的学号
   5 ． 列出“ 1 ”号课成绩比“ 2 ”号课成绩高的所有学生的学号及其“ 1 ”号课和“ 2 ”号课的成绩

1 . 找出没有选修过“李明”老师讲授课程的所有学生姓名
-- 实现代码：
SELECT  SNAME  FROM  S
WHERE
NOT
EXISTS (
     SELECT
*
FROM  SC,C
     WHERE  SC.CNO = C.CNO 
          AND  C.CTEACHER = ' 李明 '
AND  SC.SNO = S.SNO)

2 . 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
-- 实现代码：
SELECT  S.SNO,S.SNAME,AVG_SCGRADE = AVG (SC.SCGRADE)
FROM  S,SC,(
     SELECT  SNO
     FROM  SC
     WHERE  SCGRADE < 60
GROUP
BY  SNO
     HAVING
COUNT ( DISTINCT  CNO) >= 2
)A  WHERE  S.SNO = A.SNO  AND  SC.SNO = A.SNO
GROUP
BY  S.SNO,S.SNAME

3 . 列出既学过“ 1 ”号课程，又学过“ 2 ”号课程的所有学生姓名
-- 实现代码：
SELECT  S.SNO,S.SNAME
FROM  S,(
     SELECT  SC.SNO
     FROM  SC,C
     WHERE  SC.CNO = C.CNO
         AND  C.CNAME  IN ( ' 1 ' , ' 2 ' )
     GROUP
BY  SNO
     HAVING
COUNT ( DISTINCT  CNO) = 2
)SC  WHERE  S.SNO = SC.SNO 

4 . 列出“ 1 ”号课成绩比“ 2 ”号同学该门课成绩高的所有学生的学号
-- 实现代码：
SELECT  S.SNO,S.SNAME
FROM  S,SC SC1,SC SC2
     WHERE  SC1.CNO = ' 1 '
AND  SC2.SNO = ' 2 '
AND  SC1.CNO = S.CNO
         AND  SC1.SCGRADE > SC2.SCGRADE

5 . 列出“ 1 ”号课成绩比“ 2 ”号课成绩高的所有学生的学号及其“ 1 ”号课和“ 2 ”号课的成绩
-- 实现代码：
SELECT  SC1.SNO, [ 1号课成绩 ] = SC1.SCGRADE, [ 2号课成绩 ] = SC2.SCGRADE
FROM  SC SC1,SC SC2
WHERE  SC1.CNO = ' 1 '
AND  SC2.CNO = ' 2 '
AND  SC1.SNO = SC2.SNO
         AND  SC1.SCGRADE > SC2.SCGRADE


SQL code  问题描述：本题用到下面三个关系表：CARD     借书卡。   CNO 卡号，NAME  姓名，CLASS 班级BOOKS    图书。     BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 BORROW   借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下15个处理：   1 ． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。   2 ． 找出借书超过5本的读者,输出借书卡号及所借图书册数。   3 ． 查询借阅了"水浒"一书的读者，输出姓名及班级。   4 ． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。   5 ． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。   6 ． 查询现有图书中价格最高的图书，输出书名及作者。   7 ． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。   8 ． 将"C01"班同学所借图书的还期都延长一周。   9 ． 从BOOKS表中删除当前无人借阅的图书记录。   10 ．如果经常按书名查询图书信息，请建立合适的索引。   11 ．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。   12 ．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。   13 ．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。   14 ．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。   15 ．对CARD表做如下修改：    a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。    b. 为该表增加1列NAME（系名），可变长，最大20个字符。 1 . 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束 -- 实现代码：
CREATE
TABLE  BORROW(    CNO  int
FOREIGN
KEY
REFERENCES  CARD(CNO),    BNO  int
FOREIGN
KEY
REFERENCES  BOOKS(BNO),    RDATE  datetime ,     PRIMARY
KEY (CNO,BNO))  2 . 找出借书超过5本的读者,输出借书卡号及所借图书册数 -- 实现代码：
SELECT  CNO,借图书册数 = COUNT ( * ) FROM  BORROW GROUP
BY  CNO HAVING
COUNT ( * ) > 5
3 . 查询借阅了"水浒"一书的读者，输出姓名及班级 -- 实现代码：
SELECT
*
FROM  CARD c WHERE
EXISTS (     SELECT
*
FROM  BORROW a,BOOKS b      WHERE  a.BNO = b.BNO         AND  b.BNAME = N ' 水浒 '
AND  a.CNO = c.CNO)  4 . 查询过期未还图书，输出借阅者（卡号）、书号及还书日期 -- 实现代码：
SELECT
*
FROM  BORROW  WHERE  RDATE < GETDATE ()  5 . 查询书名包括"网络"关键词的图书，输出书号、书名、作者 -- 实现代码：
SELECT  BNO,BNAME,AUTHOR  FROM  BOOKS WHERE  BNAME  LIKE  N ' %网络% '
6 . 查询现有图书中价格最高的图书，输出书名及作者 -- 实现代码：
SELECT  BNO,BNAME,AUTHOR  FROM  BOOKS WHERE  PRICE = (     SELECT
MAX (PRICE)  FROM  BOOKS)  7 . 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出 -- 实现代码：
SELECT  a.CNO FROM  BORROW a,BOOKS b WHERE  a.BNO = b.BNO  AND  b.BNAME = N ' 计算方法 '
AND
NOT
EXISTS (         SELECT
*
FROM  BORROW aa,BOOKS bb         WHERE  aa.BNO = bb.BNO             AND  bb.BNAME = N ' 计算方法习题集 '
AND  aa.CNO = a.CNO) ORDER
BY  a.CNO  DESC
8 . 将"C01"班同学所借图书的还期都延长一周 -- 实现代码：
UPDATE  b  SET  RDATE = DATEADD ( Day , 7 ,b.RDATE) FROM  CARD a,BORROW b WHERE  a.CNO = b.CNO     AND a.CLASS = N ' C01 '
9 . 从BOOKS表中删除当前无人借阅的图书记录 -- 实现代码：
DELETE  A  FROM  BOOKS a WHERE
NOT
EXISTS (     SELECT
*
FROM  BORROW     WHERE  BNO = a.BNO)  10 . 如果经常按书名查询图书信息，请建立合适的索引 -- 实现代码：
CREATE
CLUSTERED
INDEX  IDX_BOOKS_BNAME  ON  BOOKS(BNAME) 11 . 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表） -- 实现代码：
CREATE
TRIGGER  TR_SAVE  ON  BORROW FOR
INSERT , UPDATE
AS
IF
@@ROWCOUNT > 0
INSERT  BORROW_SAVE  SELECT  i. *
FROM  INSERTED i,BOOKS b WHERE  i.BNO = b.BNO     AND  b.BNAME = N ' 数据库技术及应用 '
12 . 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名） -- 实现代码：
CREATE
VIEW  V_VIEW AS
SELECT  a.NAME,b.BNAME FROM  BORROW ab,CARD a,BOOKS b WHERE  ab.CNO = a.CNO     AND ab.BNO = b.BNO     AND  a.CLASS = N ' 力01 '
13 . 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出 -- 实现代码：
SELECT  a.CNO FROM  BORROW a,BOOKS b WHERE  a.BNO = b.BNO     AND  b.BNAME  IN (N ' 计算方法 ' ,N ' 组合数学 ' ) GROUP
BY  a.CNO HAVING
COUNT ( * ) = 2
ORDER
BY  a.CNO  DESC
14 . 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句 -- 实现代码：
ALTER
TABLE  BOOKS  ADD
PRIMARY
KEY (BNO)  15.1  将NAME最大列宽增加到10个字符（假定原为6个字符） -- 实现代码：
ALTER
TABLE  CARD  ALTER
COLUMN  NAME  varchar ( 10 )  15.2  为该表增加1列NAME（系名），可变长，最大20个字符 -- 实现代码：
ALTER

TABLE CARD ADD 系名 varchar(20)

转自：http://www.cnblogs.com/xiurui12345/archive/2012/04/10/2441522.html