Mysql经典入门练习题(七)

建表:

CREATE TABLE  book (
     BID CHAR(10) NOT NULL,
     title CHAR(50) DEFAULT NULL,
     author CHAR(20) DEFAULT NULL,
     PRIMARY KEY (BID)
   );
   INSERT INTO book VALUES ('B001', '人生若只如初见', '安意如');
   INSERT INTO book VALUES ('B002', '入学那天遇见你', '晴空');
   INSERT INTO book VALUES ('B003', '感谢折磨你的人', '如娜');
  INSERT INTO book VALUES ('B004', '我不是教你诈', '刘庸');
  INSERT INTO book VALUES ('B005', '英语四级', '白雪');
  CREATE TABLE  borrow (
    borrowID CHAR(10) NOT NULL,
    stuID CHAR(10) DEFAULT NULL,
    BID CHAR(10) DEFAULT NULL,
    T_time VARCHAR(50) DEFAULT NULL,
    B_time VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (borrowID)
  ) ;
  INSERT INTO borrow VALUES ('T001', '1001', 'B001', '2007-12-26 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T002', '1004', 'B003', '2008-01-05 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T003', '1005', 'B001', '2007-10-08 00:00:00', '2007-12-25 00:00:00');
  INSERT INTO borrow VALUES ('T004', '1005', 'B002', '2007-12-16 00:00:00', '2008-01-07 00:00:00');
  INSERT INTO borrow VALUES ('T005', '1002', 'B004', '2007-12-22 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T006', '1005', 'B005', '2008-01-06 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T007', '1002', 'B001', '2007-09-11 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T008', '1005', 'B004', '2007-12-10 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T009', '1004', 'B005', '2007-10-16 00:00:00', '2007-12-18 00:00:00');
  INSERT INTO borrow VALUES ('T010', '1002', 'B002', '2007-09-15 00:00:00', '2008-01-05 00:00:00');
  INSERT INTO borrow VALUES ('T011', '1004', 'B003', '2007-12-28 00:00:00', NULL);
  INSERT INTO borrow VALUES ('T012', '1002', 'B003', '2007-12-30 00:00:00', NULL);
  
  CREATE TABLE  student (
    stuID CHAR(10) NOT NULL,
    stuName VARCHAR(10) DEFAULT NULL,
    major VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (stuID)
  );
  
  INSERT INTO student VALUES ('1001', '林林', '计算机');
  INSERT INTO student VALUES ('1002', '白杨', '计算机');
  INSERT INTO student VALUES ('1003', '虎子', '英语');
  INSERT INTO student VALUES ('1004', '北漂的雪', '工商管理');
  INSERT INTO student VALUES ('1005', '五月', '数学');

题目:

1.查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期
2.查询所有借过图书的学生编号、学生名称、专业
3.查询没有借过图书的学生编号、学生名称、专业
4.查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期
5.查询借过书但有书未归还的学生编号、学生名称、图书编号、图书名称、借出日期
6.查询目前借书但未归还图书的学生名称及未还图书数量

🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
🚴‍♂️
参考答案

#查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期；
SELECT student.stuID,student.stuName,book.BID,book.title,borrow.B_time FROM
student,borrow,book where borrow.T_time >= '2007-12-15' AND borrow.B_time <= '2008-01-08'
 AND student.stuID = borrow.stuID AND borrow.BID = book.BID;
#查询所有借过图书的学生编号、学生名称、专业；
select DISTINCT student.stuID,student.stuName,student.major FROM student,borrow where 
student.stuID = borrow.stuID;
#查询没有借过图书的学生编号、学生名称、专业；
select DISTINCT student.stuID,student.stuName,student.major from student,borrow where student.stuID 
NOT IN(select DISTINCT borrow.stuID from borrow);
#查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；
select student.stuName,book.title,borrow.T_time,borrow.B_time FROM student,borrow,book
 WHERE book.author = '安意如' AND student.stuID = borrow.stuID AND book.BID = borrow.BID;
#查询借过书但有书未归还的学生编号、学生名称、图书编号、图书名称、借出日期
select student.stuID,student.stuName,book.BID,book.title,borrow.T_time FROM student,borrow,book
WHERE NOT ISNULL(borrow.T_time) AND ISNULL(borrow.B_time) AND book.BID = borrow.BID AND student.stuID = borrow.stuID;
#查询目前借书但未归还图书的学生名称及未还图书数量；
select student.stuName,COUNT(*)AS '未还数量' FROM student,borrow where not ISNULL(borrow.T_time) AND ISNULL(borrow.B_time)
AND student.stuID = borrow.stuID
GROUP BY student.stuName;

本系列题目均从网上收集,sql自己重写并修改了一些bug和错误,基本上应该没有错误,如有错误或问题可以评论留言,感谢观看