本文共 7059 字,大约阅读时间需要 23 分钟。
sql学习练习题
Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands (, , ) and some concepts (, ) and theory (, , ). Now it’s time to discuss some interesting SQL queries.
今天是SQL实践1的一天。 到目前为止,在本系列文章中,我们已经介绍了最重要SQL命令( , , )和一些概念( , )和理论( , , )。 现在是时候讨论一些有趣SQL查询了。
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语句,并得出表与表之间的关系以及联系方式。 这可能不会在面试中发生,而是可能在现实生活中发生,例如,当您继续从事现有项目时。
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 , , , and the approach to . 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.
重点应放在理解该问题的要求和学习目标是什么上。 在继续之前,请随时刷新有关知识, , 以及 。 如果您准备好了,让我们看一下前两个查询(我们将在后续文章中介绍更多内容)。 对于每个查询,我们将描述所需的结果,查看查询,分析对该查询重要的内容,然后查看结果。
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_citiesFROM countryLEFT JOIN city ON country.id = city.country_idGROUP BY country.id, country.country_name_engORDER BY country.country_name_eng ASC;
Let’s analyze the most important parts of this query:
让我们分析一下此查询的最重要部分:
You can see the result returned in the picture below.
您可以在下面的图片中看到返回的结果。
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 callsFROM customerINNER JOIN call ON call.customer_id = customer.idGROUP BY customer.id, customer.customer_nameHAVING 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:
我在这里要强调的重要事项是:
Let’s take a look at what the query actually returned.
让我们看一下查询实际返回了什么。
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实践中,我们仅分析了两个示例。 尽管如此,这两个仍然包含您在作业中经常遇到的某些部分–在您的工作中,在测试中(工作面试,大学作业,在线课程等)。 在下一部分中,我们将继续一些有趣的查询,这些查询将帮助您解决可能遇到的问题。
Learn SQL: Practice SQL Queries |
学习SQL:练习SQL查询 |
翻译自:
sql学习练习题
转载地址:http://jzswd.baihongyu.com/