数据库与系统原理期末复习题之SQL语句

复习题之SQL语句"/>

数据库与系统原理期末复习题之SQL语句

//第一题
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 bookWHERE book_name='数据库原理' AND EXISTS(SELECT * FROM borrowWHERE 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 moviestarWHERE name_='杨子'AND EXISTS(SELECT * FROM starsinWHERE 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;