SQL中关于借还书查询的题目及答案.doc

-图书管理系统（借书、还书、书、库存、书类、学生）-选课系统（学生、老师、选课、课程、班级、年级）USE 坚持-1、数据库设计（命名规范、主外键）-2、某班所有学生二月份借的书目的总本数（相同的书不重复计算）(学号、借书本数)SELECT A.stuID ,COUNT(B.BID)-注意and句在哪里FROM student A LEFT JOIN borrow B ON B.stuID=A.stuID AND DATEPART(MONTH,T_TIME)=12GROUP BY A.stuID SELECT A.stuID ,COUNT(B.BID)FROM borrow B RIGHT JOIN student A ON B.stuID=A.stuID and DATEPART(MONTH,T_TIME)=12GROUP BY A.stuID -3、某班所有学生最后借的一本书的借书信息（学号、书名、借书时间）SELECT A.stuID,C.title,B.T_timeFROM student AJOIN borrow B ON A.stuID=B.stuIDJOIN book C ON C.Bid=B.BIDWHERE B.T_time=(SELECT TOP 1 B.T_timeFROM borrow BJOIN student A ON B.stuID=A.stuIDWHERE A.sclanum =二班ORDER BY T_time DESC) -5、某位老师所教课程的所有学生的借书信息。(学号、书名、借书日期)SELECT C.title ,B.T_time ,A.stuID FROM student ALEFT JOIN borrow B ON A.stuID =B.stuIDJOIN book C ON B.BID=C.Bid WHERE A.stuID IN(SELECT D.sStudentid FROM Elective DJOIN course E ON D.scoursename =E.scoursename WHERE E.SSTAFFNUMBER =)-SELECT *FROM course-SELECT *fROM teacher -SELECT *fROM -ALTER TABLE COURSE-ADD SSTAFFNUMBER VARCHAR(10)-INSERT INTO teacher ()-6、某班每位学生借的书名串联(学号、书名串联)declare STUID varchar(100)declare TITLE varchar(100)declare str varchar(1000)declare t table(stuID varchar(100),TITLE varchar(100)declare curTemp Cursorfor SELECT A.stuID ,C.title FROM student ALEFT JOIN borrow B ON A.stuID=B.stuID LEFT JOIN book C ON B.BID=C.Bid WHERE A.sclanum =二班group by A.stuID,C.title open curTempfetch next from curTemp into STUID,TITLEwhile fetch_status=0begin set str=IsNull(str+,)+RTRIM(TITLE) fetch next from curTemp into STUID,TITLEend insert into t(stuID,TITLE) values(STUID,str)close curTemp-关闭游标deallocate curTemp-销毁游标select * from t-7、每个学生借的最贵的一本书的信息（学号、书名） select student.stuID,TITLE=( select top 1 BOOK.title from borrow join BOOK on borrow.BID =book.Bid where student.stuID=borrow.stuID order by book.price DESC ) from student order by student.stuID -2、某班所有学生二月份借的书目的总本数（相同的书不重复计算）(学号、借书本数) SELECT B.stuID,COUNT(DISTINCT A.Bid)-结果正确 FROM student B left JOIN borrow A ON B.stuID=A.stuID AND DATEDIFF(MONTH,T_time,2007-12-19)=0 GROUP BY B.stuID SELECT *FROM student SELECT *FROM borrow SELECT *FROM book SELECT student.stuID, COUNT(DISTINCT BOOK.BID)-结果正确 FROM borrow RIGHT JOIN student ON student.stuID=borrow.stuID LEFT JOIN BOOK ON book.Bid=borrow.BID AND DATEDIFF(MONTH,T_time,2007-12-19)=0 GROUP BY student.stuID-查询目前借书但未归还图书的学生名称及未还图书数量select