[SQL ] 請教SQL題目(已有擬答)
資料庫名稱：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://www.ptt.cc/bbs/Database/M.1590054444.A.A84.html
Database 近期熱門文章
PTT數位生活區 即時熱門文章
