select子查询的练习题

好的，这是一个关于select子查询的练习题：

假设有一个名为"orders"的表格，其中包含以下列：

- order_id (订单ID)
- customer_id (客户ID)
- order_date (订单日期)
- order_total (订单总额)

请写出一条SQL查询语句，找出每个客户的最大订单总额。

答案：

SELECT customer_id, MAX(order_total) AS max_order_total
FROM orders
GROUP BY customer_id;

解释：

这条SQL语句使用了"GROUP BY"子句，将订单按照客户ID分组。然后使用"MAX"函数找出每个客户的最大订单总额，并将结果命名为"max_order_total"。最后，将结果按照客户ID进行排序。

相关问题

经典51道SQL查询练习题

好的，以下是经典的51道SQL查询练习题：

1. 查询表中所有记录

SELECT * FROM table_name;

2. 查询表中的某些列

SELECT column_name1, column_name2 FROM table_name;

3. 查询表中不重复的某些列

SELECT DISTINCT column_name1, column_name2 FROM table_name;

4. 按条件查询表中的记录

SELECT * FROM table_name WHERE condition;

5. 对查询结果进行排序

SELECT * FROM table_name ORDER BY column_name;

6. 对查询结果进行倒序排序

SELECT * FROM table_name ORDER BY column_name DESC;

7. 查询表中前 n 条记录

SELECT * FROM table_name LIMIT n;

8. 查询表中第 m 条到第 n 条记录

SELECT * FROM table_name LIMIT m, n-m+1;

9. 对查询结果进行计数

SELECT COUNT(*) FROM table_name;

10. 对查询结果进行求和

SELECT SUM(column_name) FROM table_name;

11. 对查询结果进行平均值计算

SELECT AVG(column_name) FROM table_name;

12. 对查询结果进行最大值计算

SELECT MAX(column_name) FROM table_name;

13. 对查询结果进行最小值计算

SELECT MIN(column_name) FROM table_name;

14. 对查询结果进行分组计算

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

15. 对查询结果进行分组计算并进行排序

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC;

16. 对查询结果进行多条件查询

SELECT * FROM table_name WHERE condition1 AND condition2;

17. 对查询结果进行模糊查询

SELECT * FROM table_name WHERE column_name LIKE '%keyword%';

18. 对查询结果进行通配符查询

SELECT * FROM table_name WHERE column_name LIKE '_keyword';

19. 对查询结果进行多表查询

SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

20. 对查询结果进行多表联合查询

SELECT * FROM table_name1 UNION SELECT * FROM table_name2;

21. 对查询结果进行多表交集查询

SELECT * FROM table_name1 INTERSECT SELECT * FROM table_name2;

22. 对查询结果进行多表差集查询

SELECT * FROM table_name1 EXCEPT SELECT * FROM table_name2;

23. 对查询结果进行子查询

SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

24. 对查询结果进行连接查询

SELECT * FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

25. 对查询结果进行左连接查询

SELECT * FROM table_name1 LEFT OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

26. 对查询结果进行右连接查询

SELECT * FROM table_name1 RIGHT OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

27. 对查询结果进行全连接查询

SELECT * FROM table_name1 FULL OUTER JOIN table_name2 ON table_name1.column_name=table_name2.column_name;

28. 对查询结果进行自连接查询

SELECT * FROM table_name t1, table_name t2 WHERE t1.column_name=t2.column_name;

29. 对查询结果进行临时表查询

CREATE TEMPORARY TABLE temp_table_name AS SELECT * FROM table_name WHERE condition;
SELECT * FROM temp_table_name;

30. 对查询结果进行自定义列名

SELECT column_name1 AS name1, column_name2 AS name2 FROM table_name;

31. 对查询结果进行多条件排序

SELECT * FROM table_name ORDER BY column_name1 ASC, column_name2 DESC;

32. 对查询结果进行多表连接查询并进行分组计算

SELECT table_name1.column_name1, COUNT(*) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name GROUP BY table_name1.column_name1;

33. 对查询结果进行多表连接查询并进行聚合计算

SELECT table_name1.column_name1, SUM(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name GROUP BY table_name1.column_name1;

34. 对查询结果进行多表连接查询并进行统计计算

SELECT table_name1.column_name1, COUNT(*) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1;

35. 对查询结果进行分页查询

SELECT * FROM table_name LIMIT m, n-m+1;

36. 对查询结果进行日期计算

SELECT DATE_ADD(date_column, INTERVAL 1 MONTH) FROM table_name;

37. 对查询结果进行日期格式化

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;

38. 对查询结果进行日期比较

SELECT * FROM table_name WHERE DATEDIFF(date_column1, date_column2) > 30;

39. 对查询结果进行日期范围查询

SELECT * FROM table_name WHERE date_column BETWEEN 'start_date' AND 'end_date';

40. 对查询结果进行日期部分提取

SELECT YEAR(date_column), MONTH(date_column), DAY(date_column) FROM table_name;

41. 对查询结果进行多表连接查询并进行分组计算和排序

SELECT table_name1.column_name1, COUNT(*) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC;

42. 对查询结果进行多表连接查询并进行多条件排序

SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name ORDER BY table_name1.column_name1 ASC, table_name2.column_name2 DESC;

43. 对查询结果进行多表连接查询并进行多条件分组计算和排序

SELECT table_name1.column_name1, COUNT(*) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC;

44. 对查询结果进行多表连接查询并进行多条件聚合计算和排序

SELECT table_name1.column_name1, SUM(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY SUM(table_name2.column_name2) DESC;

45. 对查询结果进行多表连接查询并进行多条件统计计算和排序

SELECT table_name1.column_name1, COUNT(*) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) ASC;

46. 对查询结果进行多表连接查询并进行多条件分组计算、聚合计算和排序

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC, SUM(table_name2.column_name2) ASC;

47. 对查询结果进行多表连接查询并进行多条件统计计算、聚合计算和排序

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) ASC, SUM(table_name2.column_name2) DESC;

48. 对查询结果进行多表连接查询并进行多条件分组计算、聚合计算、统计计算和排序

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2), AVG(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC, SUM(table_name2.column_name2) ASC;

49. 对查询结果进行多表连接查询并进行多条件分组计算、聚合计算、统计计算、排序和分页查询

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2), AVG(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC, SUM(table_name2.column_name2) ASC LIMIT m, n-m+1;

50. 对查询结果进行多表连接查询并进行多条件分组计算、聚合计算、统计计算、排序、分页查询和列过滤

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2), AVG(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 ORDER BY COUNT(*) DESC, SUM(table_name2.column_name2) ASC LIMIT m, n-m+1 OFFSET k ROWS FETCH NEXT l ROWS ONLY;

51. 对查询结果进行多表连接查询并进行多条件分组计算、聚合计算、统计计算、排序、分页查询、列过滤和条件过滤

SELECT table_name1.column_name1, COUNT(*), SUM(table_name2.column_name2), AVG(table_name2.column_name2) FROM table_name1 JOIN table_name2 ON table_name1.column_name=table_name2.column_name WHERE condition GROUP BY table_name1.column_name1 HAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC, SUM(table_name2.column_name2) ASC LIMIT m, n-m+1 OFFSET k ROWS FETCH NEXT l ROWS ONLY;

oracle数据库练习题

以下为Oracle数据库练习题的答案:

6. 查询同时学过课程1和课程2的同学的学号和姓名:

SELECT g.sno AS "学号", g.sname AS "姓名" 
FROM student g, 
    (SELECT * FROM sc WHERE cno = 1) t 
WHERE t.sno IN (SELECT sno FROM sc WHERE cno = 2) 
    AND g.sno = t.sno;

该查询使用了子查询和多表连接，首先在子查询中选择了选修了课程1的学生的学号，然后通过连接操作找到同时选修了课程2的学生，并返回他们的学号和姓名。

7. 查询学过“李丁”老师所教的所有课程的所有同学的学号和姓名:

SELECT sno AS "学号", sname AS "姓名" 
FROM student 
WHERE sno IN (
    SELECT p.sno 
    FROM (SELECT * FROM sc WHERE cno = 4) p, 
        (SELECT * FROM sc WHERE cno = 5) k 
    WHERE p.sno = k.sno
);

该查询使用了子查询和多表连接，在子查询中先选择了李丁老师所教的课程4的学生的学号，然后通过连接操作找到同时学习了课程5的学生，并返回他们的学号和姓名。

3. 查询所有同学的学号、姓名、选课数、总成绩:

SELECT z."学号", g.sname AS "姓名", z."选课数", z."总分数" 
FROM (
    SELECT s.sno AS "学号", COUNT(s.cno) AS "选课数", SUM(s.grade) AS "总分数" 
    FROM course c, sc s 
    WHERE c.cno = s.cno 
    GROUP BY s.sno 
    ORDER BY "总分数" DESC
) z, student g 
WHERE z."学号" = g.sno;

该查询使用了子查询和表连接，首先在子查询中计算了每个学生的选课数和总成绩，并按照总成绩降序排序，然后通过连接操作将学生的学号、姓名、选课数和总成绩与student表连接起来，并返回结果。

以上是关于Oracle数据库练习题的答案。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* *2* *3* [Oracle数据库练习题](https://blog.csdn.net/WhenTheWindBlows/article/details/89403828)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"]
[ .reference_list ]