5-1
SELECT Titles AS 图书名称, ISBN, writers AS 作者
FROM book0868skx
WHERE Prices BETWEEN 25 AND 75;
5-2
SELECT Titles AS 图书名称, Prices AS 单价
FROM book0868skx
WHERE writers = '沈珂璇';
5-3
SELECT MAX(BKId) FROM book0868skx;
UPDATE book0868skx
SET PubDate = '2020-09-01'
WHERE BKId = (
SELECT max_bkid FROM (
SELECT MAX(BKId) AS max_bkid FROM book0868skx
) AS temp
);
SELECT * FROM book0868skx where BKId = 1100;
5-4
SELECT [lbk]p[rbk]网页链接 AS 图书名称, [lbk]p[rbk]网页链接 AS 价格
FROM book0868skx b
JOIN Publishers0868skx p ON [lbk]p[rbk]网页链接 = [lbk]p[rbk]网页链接 WHERE [lbk]p[rbk]网页链接 = '高等教育出版社'
AND MONTH(b.PubDate) = 4;
5-5
SELECT [lbk]p[rbk]网页链接 AS 图书名称, [lbk]p[rbk]网页链接 AS 作者, b.ISBN, [lbk]p[rbk]网页链接 AS 出版日期
FROM book0868skx b
JOIN BookType0868skx t ON [lbk]p[rbk]网页链接 = [lbk]p[rbk]网页链接 WHERE [lbk]p[rbk]网页链接 = '外语'
ORDER BY [lbk]p[rbk]网页链接 DESC;
5-6
SELECT COUNT(*) AS 男读者总人数
FROM People0868skx
WHERE PSex = '男性';
5-7
SELECT [lbk]p[rbk]网页链接 AS 出版社名称, SUM(b.Prices) AS 图书总价
FROM book0868skx b
JOIN Publishers0868skx p ON [lbk]p[rbk]网页链接 = [lbk]p[rbk]网页链接 GROUP BY [lbk]p[rbk]网页链接 HAVING SUM(b.Prices) > 200;
5-8
SELECT
MAX(Prices) AS 最高价格,
MIN(Prices) AS 最低价格,
AVG(Prices) AS 平均价格
FROM book0868skx;
6-1
CREATE VIEW view_read_people AS
SELECT
[lbk]p[rbk]网页链接 AS 读者姓名,
[lbk]p[rbk]网页链接 AS 图书名称,
[lbk]p[rbk]网页链接 AS 图书作者
FROM
People0868skx p
JOIN Borrow0868skx br ON [lbk]p[rbk]网页链接 = [lbk]p[rbk]网页链接 JOIN book0868skx b ON [lbk]p[rbk]网页链接 = b.BKId;
6-2
SELECT 图书名称, 图书作者
FROM view_read_people
WHERE 读者姓名 = '沈珂璇';
7
CREATE TRIGGER trigger_delete
BEFORE
DELETE ON book0868skx
FOR EACH ROW
BEGIN
DELETE FROM Penalty0868skx WHERE BKID = OLD.BKId;
DELETE FROM borrow0868skx WHERE BKID = OLD.BKId;
END;
select*from penalty0868skx;
select*from borrow0868skx;
DELETE FROM book0868skx WHERE BKId=1011;
select*from penalty0868skx;
select*from borrow0868skx;