SQL Class

Schema：

Book：

Loan：

Student：

Dep：

=============================================================

(1)   請找出曾被借出的書籍中，出版社名稱(publisher)是大欣出版社的資料，並顯示出借閱者姓名(name)/借書日期(loandate)/書名(bookname)/價格(price)

SELECT *

FROM book, loan

WHERE loan.code=book.code and publisher="大欣出版社";

(2)   請從書籍資料中將書籍價格(price)低於(含)250元，或者是書籍價格高於(含)450元的書籍資料找出來，並顯示出這些資料的書碼(code)/書名(bookname)/價格(price)/出版社名稱(publisher) 

SELECT code, bookname, price, publisher

FROM book

WHERE price<=250 or price>=450;

(3)   請從借閱資料檔中找出所有借過"FC0002"這本書的學生資料,並顯示其學號(stuid)/姓名(name)/借書日期(loandate)/科系名稱(depname)/書碼(code)/書名(bookname)

SELECT student.stuid, student.name, loan.loandate, depname.depname, book.code, book.bookname

FROM book, loan, student, depname

WHERE book.code=loan.code and student.stuid=loan.stuid and loan.code="FC0002" and student.depno=depname.depno;

(4)   請從借閱資料檔中找出曾經借過書，但是卻從未借過"FC0002"這本書的學生借閱資料,並顯示其學號(stuid)/姓名(name)/借書日期(loandate)/科系名稱(depname)/書碼(code)/書名(bookname)

SELECT student.stuid, student.name, loan.loandate, depname.depname, book.code, book.bookname

FROM book, loan, student, depname

WHERE book.code=loan.code and student.stuid=loan.stuid and loan.code<>"FC0002" and student.depno=depname.depno;

(5)   請從借閱書籍檔中找出書籍被借閱最多次的前5名資料，並顯示這些書籍的[書碼(code)/書名(bookname)/借閱次數(衍生屬性)]等3個欄位

SELECT TOP 5 book.code, bookname, count(loan.code) AS 借閱次數

FROM loan, book

WHERE book.code=loan.code

GROUP BY bookname

ORDER BY 借閱次數 DESC;

MySQL:

SELECT book.code, bookname, count(loan.code) AS 借閱次數

FROM loan, book

WHERE book.code=loan.code

GROUP BY bookname

ORDER BY 借閱次數 DESC

Limit 5;

(6)   顯示借閱最多的前5名學生資料，並顯示[學號(stuid)/姓名(name)/科系名稱(depname)/借閱次數(衍生屬性)]等5個欄位

SELECT student.stuid, student.name, depname.depname

FROM student, depname, (SELECT TOP 5 loan.stuid, count(loan.code) AS 借閱次數

FROM loan

GROUP BY loan.stuid

order by 2 desc)  AS cc

WHERE student.depno=depname.depno and cc.stuid=student.stuid;

MySQL:

SELECT student.stuid, student.name, depname.depname

FROM student, depname, (SELECT loan.stuid, count(loan.code) AS 借閱次數

FROM loan

GROUP BY loan.stuid

order by 2 desc limit 5)  AS cc

WHERE student.depno=depname.depno and cc.stuid=student.stuid;

(7)   從借閱記錄中找出曾借過書但卻還未還書的資料,並顯示出其[學號(stuid)/書碼(code)/借書日期(loandate)/逾期天數(衍生屬性)]等4個欄位內容，罰金之計算是以每逾期1天罰2元的公式為基礎。

SELECT loan.stuid, loan.code, now() - loan.duedate AS 逾期天數

FROM loan, book

WHERE book.code=loan.code and isloan=1 and loan.returndate is null and loan.duedate < now();

(8)   請從借閱記錄中以科系為單位，找出學生借閱次數最多的前3名科系資料，並顯示出其[科系代號(depno)/科系名稱(depname)/科主任(chief)/借閱次數(衍生屬性)]等4個欄位，並以該科學生借閱總次數由大至小排序

SELECT depname.depno, depname.depname

FROM depname, (SELECT top 1 depname.depno as nid, count(loan.code) AS 借閱數量

FROM loan, depname, student

WHERE loan.stuid=student.stuid and student.depno=depname.depno

group by depname.depno

order by 2

desc)  AS cc

WHERE depname.depno=cc.nid;

MySQL:

SELECT depname.depno, depname.depname

FROM depname, (SELECT depname.depno as nid, count(loan.code) AS 借閱數量

FROM loan, depname, student

WHERE loan.stuid=student.stuid and student.depno=depname.depno

group by depname.depno

order by 2

desc limit 1)  AS cc

WHERE depname.depno=cc.nid;

(9)   請找出書籍價格(price)低於200元或是價格(price)高於480元的書籍資料，並顯示出這些資料的[書碼(code)/書名(bookname)/價格(price)/出版社名稱(publisher)]

SELECT code, bookname, price, publisher

FROM book

WHERE price<200 or price>48;

(10)   由於網路書店週年特價，凡是每本書籍均以原價的八折(80%)價格銷售，請從書籍資料檔中，除了顯示其原有的書籍資料欄位屬性內容外，另外新增一個「8折價」的欄位屬性，內容為原本的書籍價格(price)*0.8之後小數點再四捨五入的價格。

SELECT book.*, price*0.8 AS 8折價

FROM book;

(11)   請查詢每位不同的學生各借過幾本書，並顯示出這些學生的學號(stuid)/姓名(name)/冊數(衍生屬性)，請注意沒借過書的同學也需要顯示其借書冊數為0的資訊。

SELECT student.stuid, student.name, cc.quanty

FROM student, (select loan.stuid as ccuid, count(loan.code) as quanty

from loan

group by loan.stuid)  AS cc

WHERE student.stuid=cc.ccuid;

(12)   請從書籍資料檔中查詢每一種書籍曾經被學生借過多少次，並顯示出這些書籍的書碼(code)/書名(bookname)/作者(author)/出版社(publisher)/ 借出次數(衍生屬性)，請注意沒被借過的書也需要顯示其被借出次數為0的資訊。顯示資料時請依照書碼的英數字順序由小至大排序。

SELECT book.code, book.bookname, book.author, book.publisher, cc.testtime

FROM book LEFT JOIN (select loan.code as testcode ,count(loan.stuid) as testtime

from loan

group by loan.code)  AS cc ON book.code=cc.testcode

ORDER BY book.code;

MySQL:

SELECT book.code, book.bookname, book.author, book.publisher, ifnull(cc.testtime,0)

FROM book LEFT JOIN (select loan.code as testcode ,count(loan.stuid) as testtime

from loan

group by loan.code)  AS cc ON book.code=cc.testcode

ORDER BY book.code;

(13)   請計算出目前書籍資料檔中所有書籍的平均價格。

SELECT avg(price) AS 平均價格

FROM book;

(14)   請從書籍資料檔中找出書籍價格高於所有書籍平均價格之書籍資料，並顯示出這些書籍的[書名(bookname)/出版社名稱(publisher)/價格(price)]等欄位

SELECT bookname, publisher, price

FROM book, query13

WHERE price>query13.平均價格;

(15)   查詢書籍資料中被借閱最多的前3名暢銷書，並顯示其[書碼(code),書名(bookname),借出次數(衍生屬性)]所有欄位內容資料。

SELECT book.code, book.bookname, cc.times

FROM book, (SELECT top 1 loan.code, count(loan.stuid) AS times

FROM loan

GROUP BY loan.code

order by 2 desc)  AS cc

WHERE book.code=cc.code;

SELECT book.code, book.bookname, cc.times

FROM book, (SELECT loan.code, count(loan.stuid) AS times

FROM loan

GROUP BY loan.code

order by 2 desc limit 1)  AS cc

WHERE book.code=cc.code;