作者preed (Freedom)
看板Database
标题[SQL ] 请教SQL题目(已有拟答)
时间Thu May 21 17:47:22 2020
资料库名称：MySQL
内容/问题描述：题目如下,第一张是题目加上ER图,第二张是四个Table(简略版)
https://imgur.com/a/7lNI4Il
目前有写出答案 但是好像错误蛮多 再请板上高手给予指正
1.
SELECT TOP 5
AuthorFirstName , AuthorLastName , COUNT(*) AS BorrowerCount ,
RANK() OVER (Order BY COUNT(*) DESC ) as ranking
FROM Author , Book , Borrower
WHERE BorrowDate BETWEEN '01/01/2017' AND '31/12/2017'
AND Author.AuthorId = Book.BookAuthor
AND Borrower.BookId = Book.BookID
ORDER BY COUNT(*) DESC;
2.
SELECT AuthorNationality , COUNT(AuthorNationality) AS CountNationality
FROM Author , Book , Borrower
WHERE BorrowDate BETWEEN '01/01/2015' AND '31/12/2017'
AND Author.AuthorId = Book.BookAuthor
AND Borrower.BookId = Book.BookID
3.
SELECT BookID , BookTitle , Genre , MAX(COUNT(Borrower.BookId))
FROM Book , Borrower
WHERE BorrowDate BETWEEN '01/01/2015' AND '31/12/2017'
AND Borrower.BookId = Book.BookID
GROUP BY Book.BookID
4.
SELECT TOP
Genre , COUNT(*) AS BorrowerCount ,
RANK() OVER (Order BY COUNT(*) DESC ) as ranking
FROM Book , Borrower ,Client
WHERE Borrower.BookId = Book.BookID
AND Borrower.ClinetId = Client.ClinetId
AND ClientDoB BETWEEN '1970' AND '1980'
ORDER BY COUNT(*) DESC;
5
SELECT TOP 5
Occupation , COUNT(*) AS BorrowerCount ,
RANK() OVER (Order BY COUNT(*) DESC ) as ranking
FROM Borrower ,Client
WHERE BorrowDate BETWEEN '01/01/2016' AND '31/12/2016'
AND Borrower.ClinetId = Client.ClinetId
ORDER BY COUNT(*) DESC;
6.
SELECT AVG(COUNT(Occupation))
FROM Borrower ,Client
WHERE Borrower.ClinetId = Client.ClinetId
GROUP BY Occupation
7.
CREATE VIEW client.title AS
SELECT BookTitle , COUNT(*) AS BorrowerCount ,
RANK() OVER (Order BY COUNT(*) DESC ) as ranking
FROM Book , Borrower ,Client
WHERE Borrower.BookId = Book.BookID
AND Borrower.ClinetId = Client.ClinetId
ORDER BY COUNT(*) DESC;
SELECT Top 20 percent
FROM client.title
8.
SELECT TOP
MONTH(BorrowDate) AS Month , COUNT(BookId)
RANK() OVER (Order BY COUNT(*) DESC ) as ranking
FROM Borrower
WHERE BorrowDate BETWEEN '01/01/2017' AND '31/12/2017'
GROUP BY MONTH(BorrowDate)
ORDER BY COUNT(*) DESC;
9.
SELECT AuthorFirstName , AuthorLastName , COUNT(Genre)
FROM Author , Book
WHERE Author.AuthorId = Book.BookAuthor
AND COUNT(Genre)>1 ;
10.
CREATE INDEX ix_Author_Name
ON Author(AuthorFirstName, AuthorLastName);
CREATE INDEX ix_Author_Nationality
ON Author(AuthorNationality);
CREATE INDEX ix_Book_Title
ON Book(BookTitle);
CREATE INDEX ix_Book_Genre
ON Book(Genre);
CREATE INDEX ix_Clinet_Name
ON Client(ClientFirstName, ClientLastName);
CREATE INDEX ix_Clinet_Occupation
ON Client(Occupation);
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 218.173.53.244 (台湾)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1590054444.A.A84.html