数据库试题

1、  有三张表:

(图书信息表)Books： BNO(图书编号), RNO(借书单号),  Bname（图书名）, author（作者）, price（单价）   

(借记卡表) cards：CNO(卡号)，name(姓名) ，CLASS（班号）

(借书记录表) Brrows：  RNo(清单号)，CNO, RDate(借书日期)，（Remark）备注

1)     用SQL语句创建 借书记录表 ：

2)     查询借了“水浒”一书的学生姓名 和班号，

3)     查询价格最高的书名和作者；

2、表销售统计表有三个字段（years年份、seasons季节、amount数量）

用一条SQL语句写，得出如下结果：

3、甲、乙、丙三人在玩牌，一共玩了一百盘，各盘的成绩都保存在Score表中。请用SQL语句查出三人各自的最高分在哪一局，输出局号和最高成绩。

字段名	属性	备注
ID	Int(自增)	局号
OptionA	Int	甲的成绩
OptionB	Int	乙的成绩
OptionC	Int	丙的成绩

4、编写SQL语句

1. 创建一个学生表，包括：学号，姓名，年龄，性别，联系电话，家庭地址
2. 修改数据表结构，添加一个信息，添加一个列：学历
3. 修改数据表结构，删除一个信息，删除家庭地址列
4. 插入以下数据记录

  学号 姓名 年龄 性别 联系电话 学历

   1    A    21   男   123456   本科

   2    B    20   男    110     本科

   3    C    22   男    119     大专

                    4    D    19   女    456     本科

1. 修改联系电话以11开头的所有学生学历
2. 删除姓名以C开头的学生信息
3. 查询性别为男而且学历为大专的学生姓名与年龄
4. 查询姓名，性别，年龄按降序排序
5. 查询平均年龄并按性别分组

 代码

/*---1---*/
DROP DATABASE IF EXISTS Test;
CREATE DATABASE Test;
USE Test;
CREATE TABLE Books(
	BNo INT PRIMARY KEY,
	RNo VARCHAR(50) UNIQUE,
	BName VARCHAR(50),
	Author VARCHAR(50),
	Price DECIMAL(5,2)
);
CREATE TABLE Cards(
	CNo VARCHAR(50) PRIMARY KEY,
	NAME VARCHAR(50),
	Class VARCHAR(50)
);
#(1)
CREATE TABLE Brrows(
	RNo VARCHAR(50),
	CNo VARCHAR(50),
	RDate DATE,
	Remark VARCHAR(255)
);
ALTER TABLE Brrows ADD CONSTRAINT fk_Brrows_Books FOREIGN KEY(RNo) REFERENCES Books(RNo);
ALTER TABLE Brrows ADD CONSTRAINT fk_Brrows_Cards FOREIGN KEY(CNo) REFERENCES Cards(CNo);
#(2)方法一
SELECT Cards.Name,Cards.Class FROM Brrows 
	INNER JOIN Books ON Brrows.RNo=Books.RNo 
	INNER JOIN Cards ON Brrows.CNo=Cards.CNo
	WHERE Books.BName='水浒';
#(2)方法二
SELECT Cards.Name,Cards.Class FROM Cards 
	INNER JOIN Brrows ON Cards.CNo=Brrows.CNo
	WHERE Brrows.RNo =(
		SELECT Books.RNo FROM Books WHERE Books.BName='水浒'
	);
SELECT BName,Author FROM Books WHERE Price =(
	SELECT MAX(Books.Price) FROM Books 
);

/*---2---*/
CREATE TABLE Sales(
	years INT,
	seasons	INT,
	amount	DECIMAL(5,2)
);
INSERT INTO Sales VALUES(2001,1,1.1);
INSERT INTO Sales VALUES(2001,2,1.2);
INSERT INTO Sales VALUES(2001,3,1.3);
INSERT INTO Sales VALUES(2001,4,1.4);
INSERT INTO Sales VALUES(2002,1,1.1);
INSERT INTO Sales VALUES(2002,2,1.2);
INSERT INTO Sales VALUES(2002,3,1.3);
INSERT INTO Sales VALUES(2002,4,1.4);
SELECT * FROM Sales;
SELECT 	years, 
	SUM(CASE seasons WHEN 1 THEN amount END) 季度1,
	SUM(CASE seasons WHEN 2 THEN amount END) 季度2,
	SUM(CASE seasons WHEN 3 THEN amount END) 季度3,
	SUM(CASE seasons WHEN 4 THEN amount END) 季度4
FROM sales GROUP BY years;

/*---3---*/
CREATE TABLE Test(
	Id INT,
	OptionA INT,
	OptionB INT,
	OptionC INT
);
INSERT INTO Test VALUES(1,70,73,76);
INSERT INTO Test VALUES(2,90,93,96);
INSERT INTO Test VALUES(3,80,83,86);

SELECT Id,OptionA FROM Test WHERE OptionA=(SELECT MAX(OptionA) FROM Test)
UNION
SELECT Id,OptionB FROM Test WHERE OptionB=(SELECT MAX(OptionB) FROM Test)
UNION
SELECT Id,OptionC FROM Test WHERE OptionC=(SELECT MAX(OptionC) FROM Test);

/*---4---*/
#(1)
CREATE TABLE Student(
	Nr INT PRIMARY KEY,
	NAME VARCHAR(50),
	Age INT,
	Sex VARCHAR(2),
	Phone VARCHAR(11),
	Address VARCHAR(50)
);
#(2)
ALTER TABLE Student ADD Education VARCHAR(50);
#(3)
ALTER TABLE Student DROP COLUMN Address;
#(4)
INSERT INTO Student VALUES(1,'A',21,'男','123456','本科');
INSERT INTO Student VALUES(2,'B',20,'男','110','本科');
INSERT INTO Student VALUES(3,'C',22,'男','119','大专');
INSERT INTO Student VALUES(4,'D',19,'女','456','本科');
#(5)
SELECT Education FROM Student WHERE Phone LIKE '11%';
#(6)
DELETE FROM Student WHERE NAME LIKE 'C%';
#(7)
SELECT NAME,Age FROM Student WHERE Sex='男' AND Education='大专';
#(8)
SELECT NAME, Sex, Age FROM Student ORDER BY Age DESC;
#(9)
SELECT Sex, AVG(Age) FROM Student GROUP BY Sex