SQL数据库单表查询和多表查询

–（一）以数据库系统student数据库为基础，请使用T-SQL 语句实现进行以下操作：

--1.   查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；
--(1)
select *
from dbo.course
where dbo.course.cname like 'DB\_%s__'ESCAPE'\';
--(2)
select *
from dbo.course
where dbo.course.cname like 'DB[_]%s__';

--2.   查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
select dbo.student.sname,dbo.student.sno,dbo.course.cno,dbo.course.cname
from dbo.student,dbo.course,dbo.sc
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.student.sname like '_阳%';

--3.   列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
select dbo.student.sno,dbo.student.sname,dbo.student.sdept,dbo.sc.cno,dbo.sc.grade
from dbo.student,dbo.sc,dbo.course
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.course.cname in ('数学','大学英语')

--4.   查询缺少成绩的所有学生的详细情况；
select *
from dbo.student,dbo.sc
where dbo.student.sno=dbo.sc.sno
and dbo.sc.grade='';

--5.   查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；
select *
from dbo.student
where dbo.student.sage != (
select dbo.student.sage
from dbo.student
where dbo.student.sname='张力')

--6.   查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；
select dbo.student.sno,dbo.student.sname,AVG(dbo.sc.grade)
from dbo.student,dbo.sc
where dbo.student.sno=dbo.sc.sno
group by dbo.student.sno,dbo.student.sname
having AVG(dbo.sc.grade)>(
select AVG(dbo.sc.grade)
from dbo.student,dbo.sc
where dbo.student.sno=dbo.sc.sno
and dbo.student.sname='张力')


--7.   按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。
--其中已修学分为考试已经及格的课程学分之和；
select dbo.student.sno,dbo.student.sname,dbo.student.sdept,SUM(dbo.course.credit)
from dbo.student,dbo.course,dbo.sc
where dbo.student.sno=dbo.sc.sno
and dbo.course.cno=dbo.sc.cno
and dbo.sc.grade>60
group by dbo.student.sno,dbo.student.sname,dbo.student.sdept;

--8.   列出只选修一门课程的学生的学号、姓名、院系及成绩；
select dbo.student.sno,dbo.student.sname,dbo.student.sdept,dbo.sc.grade
from dbo.student,dbo.sc
where dbo.student.sno=dbo.sc.sno
and dbo.sc.sno in (
select dbo.sc.sno
from dbo.sc
group by dbo.sc.sno
having COUNT(cno)=1);

--9.   查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号(这里的课程号还应该包括张力没有选的课程)；
select dbo.student.sno,dbo.student.sname,dbo.sc.cno
from dbo.student,dbo.sc
where dbo.student.sno=dbo.sc.sno
and dbo.student.sno in
(select dbo.sc.sno
from dbo.sc
where dbo.sc.cno in (
select dbo.sc.cno
from dbo.sc,dbo.student
where dbo.sc.sno=dbo.student.sno
and dbo.student.sname='张力'))

--10. 查询至少选修“数据库”和“数据结构”课程的学生的基本信息；
--(1)
select *
from dbo.student
where dbo.student.sno in (
select dbo.sc.sno 
from dbo.sc
where dbo.sc.cno in (
select dbo.course.cno
from dbo.course
where dbo.course.cname in ('数据库','数据结构'))
group by dbo.sc.sno
having count(dbo.sc.cno)>=2)
--(2)
select dbo.student.sno,dbo.student.sname,dbo.student.ssex,dbo.student.sage,dbo.student.sdept,dbo.student.total
from dbo.student,dbo.sc,dbo.course
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.course.cname='数据库'
intersect
select dbo.student.sno,dbo.student.sname,dbo.student.ssex,dbo.student.sage,dbo.student.sdept,dbo.student.total
from dbo.student,dbo.sc,dbo.course
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.course.cname='数据结构'
--(3)
select dbo.student.sno,dbo.student.sname,dbo.student.ssex,dbo.student.sage,dbo.student.sdept,dbo.student.total
from dbo.student
where dbo.student.sno in (
select dbo.student.sno
from dbo.student,dbo.sc,dbo.course
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.course.cname='数据库'
and dbo.student.sno in (
select dbo.student.sno
from dbo.student,dbo.sc,dbo.course
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno
and dbo.course.cname='数据结构'))
--11. 查询没有选修张力所选修的全部课程的学生的姓名；（存在这样一门课张力选了而他没有选）
select dbo.student.sname
from dbo.student
where exists (
select cno
from dbo.sc sc1,dbo.student
where sc1.sno=dbo.student.sno
and dbo.student.sname='张力'
and not exists (
select *
from dbo.sc sc2
where sc2.cno=sc1.cno
and sc2.sno=dbo.student.sno))


--12. 查询每个专业年龄超过该专业平均年龄的学生的姓名和专业；
select s1.sname,s1.sdept
from dbo.student s1
where s1.sage>
(select AVG(s2.sage)
from dbo.student s2
where s1.sdept=s2.sdept)

--13. 查询选修了张力同学所选修的全部课程的学生的姓名；
select dbo.student.sname
from dbo.student
where not exists (
select *
from dbo.sc sc1,dbo.student
where sc1.sno=dbo.student.sno
and dbo.student.sname='张力'
and not exists (
select *
from dbo.sc sc2
where sc2.cno=sc1.cno
and sc2.sno=dbo.student.sno))


--14. 检索选修了全部课程的学生姓名；
select dbo.student.sname
from dbo.student
where not exists (
select *
from dbo.course
where not exists (
select *
from dbo.sc
where dbo.course.cno=dbo.sc.cno
and dbo.sc.sno=dbo.student.sno));

--15. 列出同时选修“1”号课程和“2”号课程的所有学生的姓名；（使用两种方法实现）
--1.
select dbo.student.sname
from dbo.sc sc1,dbo.sc sc2,dbo.student
where sc1.sno=sc2.sno
and sc1.sno=dbo.student.sno
and sc1.cno='1'
and sc2.cno='2';

--2.
select dbo.student.sname
from dbo.student
where dbo.student.sno in (
select dbo.sc.sno
from dbo.sc
where dbo.sc.cno='1'
and dbo.sc.sno in (
select dbo.sc.sno
from dbo.sc
where dbo.sc.cno='2'))

--3.
select dbo.student.sname
from dbo.student
where dbo.student.sno in (
select dbo.sc.sno
from dbo.sc
where dbo.sc.cno='1')
intersect
select dbo.student.sname
from dbo.student
where dbo.student.sno in (
select dbo.sc.sno
from dbo.sc
where dbo.sc.cno='2')

--16. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；
--(1)
select dbo.student.sno,dbo.student.sname
from dbo.student
where dbo.student.sno in (
select dbo.student.sno
from dbo.course,dbo.sc
where dbo.course.cno=dbo.sc.cno
and dbo.course.cname='数据结构')

--(2)
select dbo.student.sno,dbo.student.sname
from dbo.student
where dbo.student.sno in (
select dbo.student.sno
from dbo.sc
where dbo.sc.cno in (
select dbo.course.cno
from dbo.course
where dbo.course.cname='数据结构'))

--17. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
select dbo.student.sname,dbo.student.sage,dbo.student.sdept
from dbo.student
where dbo.student.sdept != 'CS'
and dbo.student.sage<any(
select dbo.student.sage
from dbo.student
where dbo.student.sdept='CS')

--18. 查询选课人数最多的课程号和课程名（包含并列）；
select dbo.course.cno,dbo.course.cname
from dbo.course,dbo.sc
where dbo.course.cno=dbo.sc.cno
group by dbo.course.cno,dbo.course.cname
having COUNT(*)>=all(
select count(*)
from dbo.sc
group by dbo.sc.cno)

--19. 使用集合查询列出CS系的学生以及性别为女的学生名单；
select *
from dbo.student
where dbo.student.sdept='CS'
union
select *
from dbo.student
where dbo.student.ssex='女';

--20. 查询选修了所有男生所选的全部课程的女生的学号和姓名。
select dbo.student.sno,dbo.student.sname
from dbo.student
where dbo.student.ssex='女'
and not exists (
select *
from dbo.student,dbo.sc sc1
where sc1.sno=dbo.student.sno
and dbo.student.ssex='男'
and not exists (
select *
from dbo.sc
where dbo.sc.sno=dbo.student.sno
and sc1.cno=dbo.sc.cno))

–（二）对罗斯文（Northwind）数据库完成一下查询

--1.查询每个订单购买产品的数量和总金额，显示订单号，数量，总金额
select OrderID,Quantity,UnitPrice
from dbo.[Order Details] ;

--2. 查询每个员工在7月份处理订单的数量 --(每个-包括处理了0订单的,所以要用外连接)
select dbo.Employees.EmployeeID,count(OrderID) number
from dbo.Employees left join dbo.Orders
on  dbo.Employees.EmployeeID=dbo.Orders.EmployeeID and month(OrderDate)=7
group by dbo.Employees.EmployeeID


--3. 查询每个顾客的订单总数，显示顾客ID，订单总数 (每个-包括0订单的,所以要用外连接)
select dbo.Orders.CustomerID,count(OrderID) number
from dbo.Customers left join dbo.Orders
on  dbo.Customers.CustomerID=dbo.Orders.CustomerID 
group by dbo.Orders.CustomerID



--4. 查询每个顾客的订单总数和订单总金额 (每个-包括处理了0订单的,所以要用外连接----两次left join 的用法)

select dbo.Customers.CustomerID,count(*) number,SUM(dbo.[Order Details].Unitprice*dbo.[Order Details].Quantity*(1-dbo.[Order Details].discount)) summ
from 
dbo.Customers left join dbo.Orders on dbo.Customers.CustomerID=dbo.Orders.CustomerID 
left join dbo.[Order Details] on dbo.Orders.OrderID=dbo.[Order Details].OrderID
group by dbo.Customers.CustomerID


--5. 查询每种产品的卖出总数和总金额
select dbo.[Order Details].ProductID,sum(dbo.[Order Details].Quantity) quantity,SUM(dbo.[Order Details].Unitprice*dbo.[Order Details].Quantity*(1-dbo.[Order Details].discount)) summ
from dbo.[Order Details]
group by dbo.[Order Details].ProductID;

--6. 查询购买过全部商品的顾客的ID和姓名
select dbo.Customers.CustomerID,dbo.Customers.ContactName
from dbo.Customers
where not exists(
select *
from dbo.Products
where not exists(
select *
from dbo.[Order Details],dbo.Orders
where dbo.[Order Details].OrderID=dbo.Orders.OrderID
and dbo.Orders.CustomerID=dbo.Customers.CustomerID
and dbo.[Order Details].ProductID=dbo.Products.ProductID));

–（三） 对books数据库完成以下操作

--1.  查询各种类别的图书的类别和数量（包含目前没有图书的类别）
select dbo.BookType.TypeName,COUNT(dbo.BookInfo.BookNo) number
from dbo.BookInfo right join dbo.BookType
on dbo.BookInfo.TypeID=dbo.BookType.TypeID
group by dbo.BookType.TypeName;

--2.  查询借阅了‘数据库基础’的读者的卡编号和姓名
select dbo.CardInfo.CardNo,dbo.CardInfo.Reader
from dbo.BookInfo,dbo.BorrowInfo,dbo.CardInfo
where dbo.BookInfo.BookNo=dbo.BorrowInfo.BookNo
and dbo.BorrowInfo.CardNo=dbo.CardInfo.CardNo
and dbo.BookInfo.BookName='数据库基础'

--3.  查询各个出版社的图书价格超过这个出版社图书的平均价格的图书的编号和名称。
select b1.Publisher,b1.BookNo,b1.BookName
from dbo.BookInfo b1
where b1.Price>
(select AVG(b2.Price)
from dbo.BookInfo b2
where b1.Publisher=b2.Publisher)


--4.  查询借阅过了全部图书的读者的编号和姓名
select dbo.CardInfo.CardNo,dbo.CardInfo.Reader
from dbo.CardInfo
where not exists(
select *
from dbo.BookInfo
where not exists(
select *
from dbo.BorrowInfo
where dbo.BorrowInfo.CardNo=dbo.CardInfo.CardNo
and dbo.BookInfo.BookNo=dbo.BorrowInfo.BookNo))

--5.  查询借阅图书包含李明所借的全部图书的读者的编号和姓名
select dbo.CardInfo.CardNo,dbo.CardInfo.Reader
from dbo.CardInfo
where not exists(
select *
from dbo.BorrowInfo b1,dbo.CardInfo
where b1.CardNo=dbo.CardInfo.CardNo
and dbo.CardInfo.Reader='李明'
and not exists(
select *
from dbo.BorrowInfo
where dbo.BorrowInfo.CardNo=dbo.CardInfo.CardNo
and b1.BookNo=dbo.BorrowInfo.BookNo))

--6.  查询借阅次数超过2次的读者的编号和姓名
select dbo.CardInfo.CardNo,dbo.CardInfo.Reader
from dbo.CardInfo
where dbo.CardInfo.CardNo in 
(select dbo.BorrowInfo.CardNo
from dbo.BorrowInfo
group by dbo.BorrowInfo.CardNo
having COUNT(*)>2)

--7.  查询借阅卡的类型为老师和研究生的读者人数
select dbo.CardType.TypeName,count(dbo.CardInfo.CardNo) renshu
from dbo.CardType left join dbo.CardInfo
on dbo.CardType.CTypeID=dbo.CardInfo.CTypeID
group by dbo.CardType.TypeName
having dbo.CardType.TypeName in ('教师','研究生');

--8.  查询没有被借过的图书的编号和名称
select dbo.BookInfo.BookNo,dbo.BookInfo.BookName
from dbo.BookInfo
where dbo.BookInfo.BookNo not in (
select dbo.BorrowInfo.BookNo
from dbo.BorrowInfo)

--9.  查询没有借阅过英语类型的图书的学生的编号和姓名
select dbo.CardInfo.CardNo,dbo.CardInfo.Reader
from dbo.CardInfo,dbo.BorrowInfo,dbo.CardType,dbo.BookType,dbo.BookInfo
where dbo.CardInfo.CardNo=dbo.BorrowInfo.CardNo
and dbo.CardInfo.CTypeID=dbo.CardType.CTypeID
and dbo.BookInfo.BookNo=dbo.BorrowInfo.BookNo
and dbo.BookInfo.TypeID=dbo.BookType.TypeID
and dbo.CardType.TypeName='学生'
and dbo.BookType.TypeName not in ('英语');


--10. 查询借阅了‘计算机应用’类别的‘数据库基础’课程的学生的编号读者以及该读者的借阅卡的类型。
select dbo.BorrowInfo.CardNo,dbo.CardType.TypeName
from dbo.BorrowInfo,dbo.CardType,dbo.BookType,dbo.BookInfo,dbo.CardInfo
where dbo.BookInfo.BookNo=dbo.BorrowInfo.BookNo
and dbo.BookInfo.TypeID=dbo.BookType.TypeID
and dbo.BorrowInfo.CardNo=dbo.CardInfo.CardNo
and dbo.CardInfo.CTypeID=dbo.CardType.CTypeID
and dbo.BookType.TypeName='计算机应用'
and dbo.BookInfo.BookName='数据库基础'

–（四） 对商场数据库完成以下操作
–Market (mno, mname, city)

–Item (ino, iname, type, color)

–Sales (mno, ino, price)

–其中，market表示商场，它的属性依次为商场号、商场名和所在城市；
–item表示商品，它的属性依次为商品号、商品名、商品类别和颜色；
–sales表示销售，它的属性依次为商场号、商品号和售价。用SQL语句实现下面的查询要求：

--1.  列出北京各个商场都销售，且售价均超过10000 元的商品的商品号和商品名
select dbo.item.ino,dbo.item.iname
from dbo.item
where not exists(
select *
from dbo.market
where dbo.market.city='北京'
and not exists(
select *
from dbo.sales
where dbo.sales.ino=dbo.item.ino
and dbo.sales.mno=dbo.market.mno
and dbo.sales.price>10000));

--2.  列出在不同商场中最高售价和最低售价只差超过100 元的商品的商品号、最高售价和最低售价
select dbo.sales.ino,MAX(dbo.sales.price) maxx,MIN(dbo.sales.price) minn
from dbo.sales
group by dbo.sales.ino
having MAX(dbo.sales.price)-MIN(dbo.sales.price)>100;

--3.  列出售价超过该商品的平均售价的各个商品的商品号和售价
select s1.ino,s1.price
from dbo.sales s1
where s1.price>(
select AVG(s2.price)
from dbo.sales s2
where s2.ino=s1.ino
group by s2.ino)

--4.  查询销售了全部商品的商场号，商场名和城市
select dbo.market.mno,dbo.market.mname,dbo.market.city
from dbo.market
where not exists(
select *
from dbo.item
where not exists(
select *
from dbo.sales
where dbo.sales.ino=dbo.item.ino
and dbo.sales.mno=dbo.market.mno));

--5.  查询所有商场都销售了的商品的商品号和商品名。
select dbo.item.ino,dbo.item.iname
from dbo.item
where not exists(
select *
from dbo.market
where not exists(
select *
from dbo.sales
where dbo.sales.ino=dbo.item.ino
and dbo.sales.mno=dbo.market.mno));

--6.  查询每个商场里价格最高的商品的名称（用两种方法做）
--1.
 select s1.mno,dbo.item.iname
 from dbo.sales s1,dbo.item
 where dbo.item.ino=s1.ino
 and s1.price>=all(
 select s2.price
 from dbo.sales s2
 where s2.mno=s1.mno)
 --2.
 select ss.mno,dbo.item.iname
 from dbo.item,dbo.sales,(
 select dbo.sales.mno,MAX(dbo.sales.price) maxx
 from dbo.sales
 group by dbo.sales.mno) as ss
 where dbo.sales.ino=dbo.item.ino
 and dbo.sales.mno=ss.mno
 and dbo.sales.price>=ss.maxx