第四章 SQL语法练习

7 SQL语法知识 - 图1

7 SQL语法知识 - 图2

7 SQL语法知识 - 图3

7 SQL语法知识 - 图4

7 SQL语法知识 - 图5

7 SQL语法知识 - 图6

7 SQL语法知识 - 图7

7 SQL语法知识 - 图8

7 SQL语法知识 - 图9

SQL 练习

目的1：

  1. 加深对表间关系的理解。
  2. 理解数据库中数据的查询方法和应用。
  3. 学会各种查询的异同及相互之间的转换方法。

内容1：

  1. 查询所有作者的作者号、姓名信息
  2. 查询所有作者的姓名、作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”，表明显示的信息是身份证信息
  3. 查询在CA州的作者姓名和城市
  4. 查询出版日期在1992.1.1-2000.12.31之间的书名和出版日期(查询1991年出版的书)
  5. 查询每个出版社出版的书
  6. 查询某店销售某书的数量
  7. 查询有销售记录的所有书信息，包括书的编号、书名、类型和价格
  8. 查询已销售书的信息
  9. 显示所有的书名（无销售记录的书也包括在内）
  10. 查询已销售书的信息（书号、书名、作者等）
  11. 查询所有出版商业（business）书籍的出版社的名称

目的2：

  1. 理解数据库中数据的其他查询方法和应用；
  2. 学会各种查询要求的实现。

内容2：

在实验1的基础上，练习查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，体会各种查询的执行过程，为简单综合应用打下良好的基础。

  1. 查询书名以T开头或者出版社号为0877，而且价格大于16的书的信息。
  2. 按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、出版社、类型、价格）
  3. 查询销售量大于30的书名及销售数量
  4. 查询在2002.1.1到2002.10.31间，每本书的销售总额
  5. 查询所有作者的所在城市和州名，要求没有重复信息
  6. 计算多少种书已被订价
  7. 查询每本书的书名、作者及它的售书总量
  8. 计算所有书的平均价格
  9. 查询价格最高的书的书名、作者及价格

目的3：

  1. 加深对数据库相关性质的理解；
  2. 各种约束性理解；
  3. 学会数据库中数据的更新的方法。

内容3：

  1. 参照以上各表给出的主键、外键设置的设置要求，在自己创建的表中进行相应的设置。
  2. 向authors表中插入一行作者信息（具体值自定）
  3. 数量超过100的商店增加10%的折扣
  4. 删除2001.10.3的订单
  5. 删除1中所建立的索引
  6. 建立CA州作者所著书的视图（包括作者号、姓名、所在州、书名、价格、出版日期）
  7. 建立付款方式是现金（cash）的订单视图
  8. 建立CA州的所有商店的视图

目的4：

  1. 在查询分析器中，练习使用IN、比较符、ANY或ALL等操作符进行查询。
  2. 练习使用EXISTS操作符进行嵌套查询操作

内容4：

  1. 在pubs数据库的titleauthor和中，用IN谓词查询来自‘CA’州(在authors表中)的作家的全部作品（title_id）和作家的代号(au_id)。
  2. 在pubs数据库中，用比较运算符引出的子查询找出在名称为“Algodata Infosystems”的出版社所在城市中的作者的姓名（au_lname, au_fname）
  3. 在pubs数据库中的titles表中，查询价格大于所有类型(TYPE)为“business”的图书价格的书名（title）和价格(price)
  4. 在pubs数据库的sales表中查找所有销售量大于所有图书平均销售量avg（qty））的书的代号(title_id)及销售量（qty）。
  5. 用带有IN的嵌套查询，查询来自城市（city）为“London”的客户所订的订单信息（customers和orders表）。
  6. 用带有IN的嵌套查询，查询Northwind数据库中的产品表（Products）中来自国家为“Germany” （在供应商表（Suppliers）表中）的供货商供应的产品信息（包括Productid，Productname， categoryid, unitprice）。
  7. 使用EXISTS子查询在Pubs数据库titles 表及publishers表中查询New Moon Books出版社所出版的图书名称（title)

目的5：

  1. 分类汇总。

内容5：

  1. 找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。
  2. 查询titles表中有几类图书。
  3. 按照州进行分类，查找每个州有几名作者。
  4. 要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。
  5. 在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。
  6. 在pubs数据库的titles表中，找出最高价大于20美元的书的种类。
  7. 找出title_id和pub_name的对应关系。
  8. 找出title_id, title和pub_name的对应关系。
  9. 查询每个作者的编号，姓名，所出的书的编号，并对结果排序。
  10. 从authors表中选择state,city列，从publisher表中选择state,city列，并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。
  11. 对上面的查询语句作修改，保留所有重复的记录。
  12. 显示所有来自CA州的作家的全部作品和作家代号。（使用IN，和连接两种方法）
  13. 查找由位于以字母 B 开头的城市中的任一出版商出版的书名：(使用exists和in两种方法)

SQL具体操作练习

简单查询学生选课数据问题

  1. 列出全部学生的信息。
  2. 列出信息系全部学生的学号及姓名。
  3. 列出所有已被选修的选修课的课号。
  4. 求c01号课成绩大于80分的学生的学号及成绩，并按成绩由高到低列出。
  5. 列出非信息系学生的名单。
  6. 查询成绩在70~80分之间的学生选课得分情况
  7. 列出选修c01号课或c03号课的全体学生的学号和成绩。
  8. 列出所有95级学生的学生成绩情况。
  9. 列出成绩为空值(或不为空值)的学生的学号和课号。
  10. 求出所有学生的总成绩。
  11. 列出每个学生的平均成绩。
  12. 列出各科的平均成绩、最高成绩、最低成绩和选课人数。

简单查询学生选课数据问题答案

  1. 1. SELECT * FROM 学生
  2. 2. SELECT 学号,姓名 FROM 学生 WHERE 专业=’信息系’
  3. 3. SELECT DISTINCT 课号 FROM 选修课
  4. 4. SELECT 学号,成绩 FROM 选课 WHERE 课号=’01’ AND 成绩>80 ORDER BY 成绩 DESC
  5. 5. 方法一：SELECT 姓名 FROM 学生 WHERE 专业<>’信息系’
  6.    方法二：SELECT 姓名 FROM 学生 WHERE NOT 专业=’信息系’
  7.    方法三：SELECT 姓名 FROM 学生 WHERE 专业!=’信息系’
  8. 6. 方法一：SELECT * FROM 选课 WHERE 成绩>=70 AND 成绩<=80
  9.    方法二：SELECT * FROM 选课 WHERE 成绩 BETWEEN 70 AND 80
  10.    不在此范围内的查询：（注意写出和以下语句等价的语句）
  11.    SELECT * FROM 选课 WHERE 成绩 NOT BETWEEN 70 AND 80
  12. 7. 方法一：SELECT 学号,成绩 FROM 选课 WHERE 课号=’c01’ OR 课号=’c03’
  13.    方法二：SELECT 学号,成绩 FROM 选课 WHERE 课号 IN (‘c01’,’c03’)
  14.    相反条件查询：SELECT 学号,成绩 FROM 选课 WHERE 课号 NOT IN (‘c01’,’c03’)
  15. 8. SELECT * FROM 选课 WHERE 学号 LIKE ‘95%’
  16.    SELECT * FROM 选课 WHERE 学号 LIKE ‘95_ _ _ _’
  17.    相反条件查询：SELECT * FROM 选课 WHERE 学号 NOT LIKE ‘98%’
  18. 9. 答案一：SELECT 学号,课号 FROM 选课 WHERE 成绩 IS NULL
  19.    答案二：SELECT 学号,课号 FROM 选课 WHERE 成绩 IS NOT NULL
  20. 10. SELECT SUM(成绩) AS 总成绩 FROM 选课
  21. 11. SELECT 学号,AVG(成绩) AS 平均成绩 FROM 选课 GROUP BY 学号
  22. 12. SELECT 课号,AVG(成绩) AS 平均成绩,MAX(成绩) AS 最高分,
  23. MIN(成绩) AS 最低分,COUNT(学号) AS 选课人数 FROM 选课 GROUP BY 课号

目的4：

  1. 在查询分析器中，练习使用IN、比较符、ANY或ALL等操作符进行查询。
  2. 练习使用EXISTS操作符进行嵌套查询操作

请完成以下习题：

  1. 在pubs数据库的titleauthor和中，用IN谓词查询来自‘CA’州(在authors表中)的作家的全部作品（title_id）和作家的代号(au_id)。
  1. select title_id,au_id from titleauthor
  2.                       where au_id in (select au_id from authors where  state='CA')  
  4. 在pubs数据库中，用比较运算符引出的子查询找出在名称为“Algodata Infosystems”的出版社所在城市中的作者的姓名（au_lname, au_fname）
  6. select au_lname, au_fname
  7. from authors 
  8. where  city= (select city
  9.          from publishers
  10.         where pub_name='Algodata Infosystems')
  12. 在pubs数据库中的titles表中，查询价格大于所有类型(TYPE)为“business”的图书价格的书名（title）和价格(price)
  14. select title,price
  15. from titles
  16. where price>all (select price
  17.          from titles
  18.          where type='business')
  20. 在pubs数据库的sales表中查找所有销售量大于所有图书平均销售量avg（qty））的书的代号(title_id)及销售量（qty）。
  21.  select title_id ,qty
  22. from sales
  23. where qty>all(select avg(qty)
  24. from sales
  25. )
  27. 用带有IN的嵌套查询，查询来自城市（city）为“London”的客户所订的订单信息（customers和orders表）。
  28. select *
  29. from orders
  30. where customerID in (select customerID
  31. from customers
  32. where city='london')
  34. 用带有IN的嵌套查询，查询Northwind数据库中的产品表（Products）中来自国家为“Germany” （在供应商表（Suppliers）表中）的供货商供应的产品信息（包括Productid，Productname， categoryid, unitprice）。
  35. SELECT Productid,Productname, categoryid,unitprice
  36. from Products
  37. where Productid in(select supplierID
  38. from Suppliers
  39. where country='Germany')

提高操作实验

  1. 练习使用EXISTS操作符进行嵌套查询操作。请完成以下习题：
  2. 使用EXISTS子查询在Pubs数据库titles 表及publishers表中查询New Moon Books出版社所出版的图书名称（title)
  4. select title
  5.         from titles
  6.         where exists (select *
  7.         from publishers
  8.         where pub_name='New Moon Books')
  11. T-SQL高级查询课堂练习及答案
  12. --练习1
  13. --找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。
  14. USE pubs
  15. GO
  16. SELECT max(price) FROM titles
  17. where type='popular_comp'
  18. GO
  20. --练习2
  21. --查询titles表中有几类图书。
  22. USE pubs
  23. GO
  24. SELECT count(distinct type) FROM titles
  25. GO
  27. --练习3
  28. --按照州进行分类，查找每个州有几名作者。
  29. USE pubs
  30. GO
  31. SELECT state, count(*) FROM authors
  32. group by state
  33. order by 1
  34. GO
  37. --练习4
  38. --要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。
  39. USE pubs
  40. GO
  41. SELECT pub_id, sum(ytd_sales) FROM titles
  42. group by pub_id
  43. order by 1
  44. GO
  46. --练习5
  47. --在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。
  48. USE pubs
  49. GO
  50. SELECT pub_id,avg(price) '平均价格' FROM titles
  51. GROUP BY pub_id
  52. HAVING avg(price) > 18
  53. GO
  56. --练习6
  57. --在pubs数据库的titles表中，找出最高价大于20美元的书的种类。
  58. USE pubs
  59. GO
  60. SELECT type,max(price) '平均价格' FROM titles
  61. GROUP BY type
  62. HAVING max(price) > 20
  63. GO
  65. --练习7
  66. --
  67. 找出title_id和pub_name的对应关系。
  68. Use pubs
  69. go
  70. Select titles.title_id, publishers.pub_name
  71. From titles JOIN publishers
  72. ON titles.pub_id=publishers.pub_id
  73. Go
  75. --练习8
  76. --找出title_id, title和pub_name的对应关系。
  77. Use pubs
  78. go
  79. Select titles.title_id, titles.title, publishers.pub_name
  80. From titles JOIN publishers
  81. ON titles.pub_id=publishers.pub_id
  82. Go
  84. --练习9
  85. --查询每个作者的编号，姓名，所出的书的编号，并对结果排序。
  86. Use pubs
  87. go
  88. Select authors.au_id,
  89.        authors.au_fname + '.' + authors.au_lname 'name',
  90.        titleauthor.title_id
  91. From authors JOIN titleauthor
  92. ON authors.au_id=titleauthor.au_id
  93. order by authors.au_id
  94. go
  97. --练习10 
  98. --从authors表中选择state,city列，从publisher表中选择state,city列，并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。
  99. use pubs
  100. go
  101. select state,city from publishers
  102. union 
  103. select state,city from authors
  104. order by 1,2
  107. --练习11
  108. --对上面的查询语句作修改，保留所有重复的记录。
  109. use pubs
  110. go
  111. select state,city from publishers
  112. union all
  113. select state,city from authors
  114. order by 1,2
  117. --练习12
  118. --显示所有来自CA州的作家的全部作品和作家代号。（使用IN，和连接两种方法）
  119. use pubs
  120. go
  121. select title_id,au_id
  122. from titleauthor
  123. where au_id in
  124. ( select au_id from authors
  125. where state = 'CA')
  126. order by title_id
  127. go
  129. use pubs
  130. go
  131. select t.title_id,t.au_id
  132. from titleauthor t join authors a
  133. on t.au_id = a.au_id
  134. where a.state = 'CA'
  135. order by title_id
  136. go
  138. --练习13
  139. --查找由位于以字母 B 开头的城市中的任一出版商出版的书名：(使用exists和in两种方法)
  141. USE pubs
  142. GO
  143. SELECT title
  144. FROM titles
  145. WHERE EXISTS
  146.    (SELECT *
  147.    FROM publishers
  148.    WHERE pub_id = titles.pub_id
  149.    AND city LIKE 'B%')
  150. GO
  153. USE pubs
  154. GO
  155. SELECT title
  156. FROM titles
  157. WHERE pub_id IN
  158.    (SELECT pub_id
  159.    FROM publishers
  160.    WHERE city LIKE 'B%')
  161. GO