数据库与系统原理期末复习题之SQL语句_数据库原理试题 sql语句-CSDN博客

本文链接：https://blog.csdn.net/weixin_49047177/article/details/122611519

本文通过六个实战案例详细介绍了SQL的应用，包括表的创建、查询、视图的创建等操作。涉及了复杂的多表联查及聚合函数使用，展示了如何通过SQL解决实际业务问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

请添加图片描述

//第一题
CREATE TABLE borrow(
	reader_id VARCHAR(10),
	book_id VARCHAR(10),
	date_borrow DATE,
	day_ INT,
	PRIMARY KEY(reader_id,book_id),
	FOREIGN KEY(book_id) REFERENCES book(book_id),
	FOREIGN KEY(reader_id) REFERENCES reader(reader_id),
	CHECK (date_borrow >'2016-1-1'),
	CHECK((day_ IS NULL)OR(day_>=1))
);

//第二题
SELECT level_,COUNT(*)FROM reader GROUP BY level_
ORDER BY LEVEL_ DESC;
SELECT level_ ,(SELECT COUNT(*)FROM reader GROUP BY level_) AS num
FROM reader
GROUP BY level_
ORDER BY LEVEL DESC;

//第三题
SELECT reader_name 
FROM reader
WHERE NOT EXISTS(
	SELECT * FROM book
	WHERE book_name='数据库原理' AND EXISTS(
		SELECT * FROM borrow
		WHERE reader.`reader_id`=borrow.`reader_id`)
	);

//第四题
SELECT reader.reader_id,reader_name
FROM reader,borrow
WHERE reader.`reader_id`=borrow.`reader_id`
GROUP BY reader_id,reader_name
HAVING COUNT(*)>1 AND SUM(day_)>30;

//第五题
DELETE FROM borrow WHERE borrow.`reader_id`=(SELECT reader_id FROM reader WHERE reader_name='王红') ;
DELETE FROM reader WHERE reader.`reader_name`='王红' ;

//第六题
CREATE VIEW Borrow_V AS
SELECT category,COUNT(*) AS hee
FROM book,borrow
WHERE book.`book_id`=borrow.`book_id`
GROUP BY category;

请添加图片描述

//第一题
CREATE TABLE starsin(
	title VARCHAR(10),
	name_ VARCHAR(10),
	rem DOUBLE,
	PRIMARY KEY(title,name_),
	FOREIGN KEY(title) REFERENCES movies(title),
	FOREIGN KEY(name_) REFERENCES moviestar(name_),
	CHECK(rem >=0.5)
);

//第二题
SELECT studioName
FROM movies
WHERE NOT EXISTS(
	SELECT * FROM moviestar
	WHERE name_='杨子'AND EXISTS(
		SELECT * FROM starsin
		WHERE movies.`title`=starsin.`title`)
	);

//第三题
SELECT moviestar.name_,gender
FROM moviestar,starsin
WHERE moviestar.`name_`=starsin.`name_`
GROUP BY name_,gender
HAVING COUNT(*)>5 AND SUM(rem)>100;

//第四题
SELECT moviestar.name_
FROM moviestar,starsin,movies
WHERE moviestar.`name_`=starsin.`name_`AND movies.`title`=starsin.`title`AND studioName='李宁';

//第五题
UPDATE starsin
SET rem=rem*2
WHERE title=(SELECT title FROM movies WHERE studioName='李明');

//第六题
CREATE VIEW S_view AS
SELECT moviestar.name_,COUNT(*),SUM(rem)
FROM starsin,moviestar
WHERE moviestar.`name_`=starsin.`name_`AND gender='女'
GROUP BY name_
HAVING SUM(rem)>1000;