sql学习练习题
Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands (CREATE DATABASE & CREATE TABLE, INSERT, SELECT) and some concepts (primary key, foreign key) and theory (stored procedures, user-defined functions, views). Now it’s time to discuss some interesting SQL queries.
今天是SQL实践1的一天。 到目前为止，在本系列文章中，我们已经介绍了最重要SQL命令（ CREATE DATABASE＆CREATE TABLE ， INSERT ， SELECT ）和一些概念（ 主键 ， 外键 ）和理论（ 存储过程 ， 用户定义的函数 ， 视图 ）。 现在是时候讨论一些有趣SQL查询了。
该模型 (The Model)
Let’s take a quick look at the model we’ll use in this practice.
让我们快速看一下将在此实践中使用的模型。
You can expect that in real-life situations (e.g., interview), you’ll have a data model at your disposal. If not, then you’ll have the description of the database (tables and data types + additional description of what is stored where and how the tables are related).
您可以期望，在现实生活中（例如面试），您将拥有一个数据模型。 如果不是这样，那么您将拥有数据库的描述（表和数据类型，以及关于存储在何处以及表如何关联的附加描述）。
The worst option is that you have to check all the tables first. E.g., you should run a SELECT statement on each table and conclude what is where and how the tables are related. This won’t probably happen at the interview but could happen in the real-life, e.g., when you continue working on an existing project.
最糟糕的选择是您必须先检查所有表。 例如，您应该在每个表上运行SELECT语句，并得出表与表之间的关系以及联系方式。 这可能不会在面试中发生，而是可能在现实生活中发生，例如，当您继续从事现有项目时。
开始之前 (Before We Start)
The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. Other places where this might help you are college assignments or completing tasks related to online courses.
这种SQL实践的目的是分析一些您可能在面试中遇到的典型作业。 在其他可以帮助您完成大学任务或完成与在线课程相关的任务的地方。
The focus shall be on understanding what is required and what is the learning goal behind such a question. Before you continue, feel free to refresh your knowledge on INNER JOIN and LEFT JOIN, how to join multiple tables, SQL aggregate functions, and the approach to how to write complex queries. If you feel ready, let’s take a look at the first 2 queries (we’ll have some more in upcoming articles). For each query, we’ll describe the result we need, take a look at the query, analyze what is important for that query, and take a look at the result.
重点应放在理解该问题的要求和学习目标是什么上。 在继续之前，请随时刷新有关INNER JOIN和LEFT JOIN的知识， 如何 联接 多个表 ， SQL聚合函数以及编写复杂查询的方法 。 如果您准备好了，让我们看一下前两个查询（我们将在后续文章中介绍更多内容）。 对于每个查询，我们将描述所需的结果，查看查询，分析对该查询重要的内容，然后查看结果。
SQL练习1 –聚合和左联接 (SQL Practice #1 – Aggregating & LEFT JOIN)
Create a report that returns a list of all country names (in English), together with the number of related cities we have in the database. You need to show all countries as well as give a reasonable name to the aggregate column. Order the result by country name ascending.
创建一个报告，该报告返回所有国家名称的列表（英文），以及我们在数据库中拥有的相关城市的数量。 您需要显示所有国家/地区，并对汇总列指定一个合理的名称。 按国家/地区升序排列结果。
SELECT country.country_name_eng, COUNT(city.id) AS number_of_cities
FROM country
LEFT JOIN city ON country.id = city.country_id
GROUP BY country.id, country.country_name_eng
ORDER BY country.country_name_eng ASC;
Let’s analyze the most important parts of this query:
让我们分析一下此查询的最重要部分：
- We’ve used LEFT JOIN (LEFT JOIN city ON country.id = city.country_id) because we need to include all countries, even those without any related city
- 我们使用了LEFT JOIN（ LEFT JOIN city ON country.id = city.country_id ），因为我们需要包括所有国家，甚至包括那些没有任何相关城市的国家
- We must use COUNT(city.id) AS number_of_cities and not only COUNT(*) AS number_of_cities because COUNT(*) would count if there is a row in the result (LEFT JOIN creates a row no matter if there is related data in other table or not). If we count the city.id, we’ll get the number of related cities
- 我们必须使用COUNT（city.id）个AS number_of_cities ，而不仅要使用COUNT（ *）个AS number_of_cities，因为如果结果中有一行，则COUNT（*）将会计数（LEFT JOIN创建一行，无论其他是否有相关数据表）。 如果我们计算city.id ，就会得到相关城市的数量
- The last important thing is that we’ve used GROUP BY country.id, country.country_name_eng instead of using only GROUP BY country.country_name_eng. In theory (and most cases), grouping by name should be enough. This will work OK if the name is defined as UNIQUE. Still, including a primary key from the dictionary, in cases similar to this one, is more than desired
- 最后一个重要的事情是，我们使用了GROUP BY country.id，country.country_name_eng而不是仅使用GROUP BY country.country_name_eng 。 从理论上（大多数情况下），按名称分组就足够了。 如果名称定义为UNIQUE，这将正常工作。 尽管如此，在类似于字典的情况下，包括字典中的主键还是超出了期望的
You can see the result returned in the picture below.
您可以在下面的图片中看到返回的结果。
SQL练习2 –组合子查询和聚合函数 (SQL Practice #2 – Combining Subquery & Aggregate Function)
Write a query that returns customer id and name and the number of calls related to that customer. Return only customers that have more than the average number of calls of all customers.
编写一个查询，该查询返回客户ID和名称以及与该客户相关的呼叫数量。 仅返回呼叫次数超过所有客户平均数量的客户。
SELECT
customer.id,
customer.customer_name,
COUNT(call.id) AS calls
FROM customer
INNER JOIN call ON call.customer_id = customer.id
GROUP BY
customer.id,
customer.customer_name
HAVING COUNT(call.id) > (
SELECT CAST(COUNT(*) AS DECIMAL(5,2)) / CAST(COUNT(DISTINCT customer_id) AS DECIMAL(5,2)) FROM call
);
The important things I would like to emphasize here are:
我在这里要强调的重要事项是：
- Please notice that we’ve used aggregate functions twice, once in the “main” query, and once in the subquery. This is expected because we need to calculate these two aggregate values separately – once for all customers (subquery) and for each customer separately (“main” query) 请注意，我们已经使用了两次聚合函数，一次是在“ main”查询中，一次是在子查询中。 这是预料之中的，因为我们需要分别计算这两个聚合值–对所有客户一次（子查询），对每个客户一次（“主”查询）
- The aggregate function in the “main” query is COUNT(call.id). It’s used in the SELECT part of the query, but we also need it in the HAVING part of the query (Note: HAVING clause is playing the role of the WHERE clause but for aggregate values)
- “ main”查询中的聚合函数为COUNT（call.id） 。 它在查询的SELECT部分中使用，但在查询的HAVING部分中也需要它（注意：HAVING子句扮演WHERE子句的角色，但用于聚合值）
- Group is created by id and customer name. These values are the ones we need to have in the result 通过ID和客户名称创建组。 这些值是我们需要的结果
- In the subquery, we’ve divided the total number of rows (COUNT(*)) by the number of distinct customers these calls were related to (COUNT(DISTINCT customer_id)). This gave us the average number of calls per customer
- 在子查询中，我们将总行数（ COUNT（*） ）除以与这些调用相关的不同客户的数量（ COUNT（DISTINCT customer_id） ）。 这给了我们每个客户的平均通话次数
- The last important thing here is that we used the CAST operator (CAST(… AS DECIMAL(5,2))). This is needed because the final result would probably be a decimal number. Since both COUNTs are integers, SQL Server would also return an integer result. To prevent this from happening, we need to CAST both divider and the divisor as decimal numbers
- 这里的最后一件重要的事情是我们使用了CAST运算符（ CAST（…AS DECIMAL（5,2）） ）。 这是必需的，因为最终结果可能是十进制数。 由于两个COUNT都是整数，因此SQL Server也将返回整数结果。 为了防止这种情况的发生，我们需要同时将除法器和除数同时转换为十进制数
Let’s take a look at what the query actually returned.
让我们看一下查询实际返回了什么。
结论 (Conclusion)
In today’s SQL practice, we’ve analyzed only two examples. Still, these two contain some parts you’ll often meet at assignments – either in your work, either in a testing (job interview, college assignments, online courses, etc.). In the next part, we’ll continue with a few more interesting queries that should help you solve problems you might run into.
在当今SQL实践中，我们仅分析了两个示例。 尽管如此，这两个仍然包含您在作业中经常遇到的某些部分–在您的工作中，在测试中（工作面试，大学作业，在线课程等）。 在下一部分中，我们将继续一些有趣的查询，这些查询将帮助您解决可能遇到的问题。
目录 (Table of contents)
sql学习练习题
6394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
