SQL试题整理

1.用一条sql语句，查询出每门课都大于80分的学生姓名

姓名    课程      分数

张三    语文        81
张三    数学        75
李四    语文        76
李四    数学        90
王五    语文        81
王五    数学        100
王五    英语        90

--创建表
CREATE TABLE STUDENT
(
  ID INT IDENTITY(1,1),
  NAME VARCHAR(12),
  COUSER VARCHAR(12),
  FENGSU INT
)

--插入数据
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('张三','语文',81)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('张三','数学',75)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('李四','语文',76)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('李四','数学',90)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('王五','语文',82)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('王五','数学',100)
INSERT INTO STUDENT(NAME,COUSER,FENGSU) VALUES('王五','英语',90)

SELECT * FROM STUDENT

--按照下例SQ语句可查出
--第一种方法：
--SELECT DISTINCT(S.NAME) FROM STUDENT S WHERE S.NAME NOT IN (SELECT NAME FROM STUDENT WHERE FENGSU < 80)

--第二种方法：过滤掉只要有1门小于等于80的学生
--SELECT NAME FROM STUDENT GROUP BY NAME HAVING MIN(FENGSU) > 80

2:
学生表
自动编号   学号         姓名

1          2005001     张三
2          2005002     李四
3          2005001     张三

删除除了自动编号不同，其他都相同的冗余学生信息。使用1条语句。

--创建表
CREATE TABLE STUDENT2
(
  ID INT IDENTITY(1,1),
  SNO VARCHAR(10),
  NAME VARCHAR(12),
)

INSERT INTO STUDENT2(SNO,NAME) VALUES('2005001','张三')
INSERT INTO STUDENT2(SNO,NAME) VALUES('2005002','李四')
INSERT INTO STUDENT2(SNO,NAME) VALUES('2005001','张三')

SELECT * FROM STUDENT2
保留最小的值（自动编码）
delete from table a where a.自动编号 not in (select min(b.自动编号) from table b group by b.学号, b.姓名)

--下面语句完成
DELETE FROM STUDENT2 WHERE ID NOT IN
(
  SELECT MIN(B.ID) AS ID FROM STUDENT2 B GROUP BY SNO,NAME
)

 

3.DEPART表里只有1个字段name，共4条记录：A, B, C, D，现在4个球进行比赛，用1条sql语句显示所有可能的比赛组合
--创建表
CREATE TABLE DEPART
(
  NAME VARCHAR(10)
)
--插入数据
INSERT INTO DEPART VALUES('A')
INSERT INTO DEPART VALUES('B')
INSERT INTO DEPART VALUES('C')
INSERT INTO DEPART VALUES('D')

SELECT * FROM DEPART
--执行SQL语句
SELECT TABLE1.NAME + ':' + TABLE2.NAME FROM DEPART TABLE1, DEPART TABLE2 WHERE TABLE1.NAME <> TABLE2.NAME ORDER BY TABLE1.NAME

4:怎样表1转变为表2

表1
--------------------------------

year     month       amount

1991     1              1.1

1991     2              1.2

1991     3              1.3

1991     4              1.4

1992     1              2.1

1992     2              2.2

1992     3              2.3

1992     4              2.4

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

表2
----------------------------------

year      m1         m2          m3          m4

1991     1.1        1.2          1.3          1.4

1992     2.1        2.2          2.3          2.4

--下列语句实现
SELECT T.YEAR,
(SELECT M1.AMOUNT FROM TABEL1 M1 WHERE M1.YEAR = T.YEAR AND M1.MONTH = 1) M1,
(SELECT M2.AMOUNT FROM TABEL1 M2 WHERE M2.YEAR = T.YEAR AND M2.MONTH = 2) M2,
(SELECT M3.AMOUNT FROM TABEL1 M3 WHERE M3.YEAR = T.YEAR AND M3.MONTH = 3) M3,
(SELECT M4.AMOUNT FROM TABEL1 M4 WHERE M4.YEAR = T.YEAR AND M4.MONTH = 4) M4
FROM TABEL1 T GROUP BY T.YEAR ORDER BY T.YEAR