Getting started with Learn SQL queries. Part 1


Contents:


We design our base
In this course, we will create a rich database, which includes many tables and links. We will consid...
Top 3 payments
Let's start with a simple request - to get the top 3 payment regarding the amount of payment. All yo...
Payments by years
We will write a request for the calculation of the amount of payments for each year. Since the compu...
Payments by months and years
First, we'll determine how we can get record groups by months and years. We have the functions
Number of employees and number of customers for each office
We divide this task into components. Consider the request, in which you need to get the number of e...
Average revenue per employee/
We will create a request for an average income per customer. To do this, in the table with payments,...
Employees without clients
To obtain employees who are not assigned to clients, it is necessary to combine these two tables usi...
Get the TOP 10 items sold
To get the TOP 10 of the sold goods, you need to merge the fields from the product table with the fi...
Employees tied to more than 5 clinics
Consider the request to identify employees, for which more than 5 customers are assigned. For this, ...
Orders with the greatest quantity of goods
To write this query, we will need to group the table entries order_details on the fiel...
Offices with less than 15 orders per year
Consider the request to obtain a list of offices with less than 15 orders for a particular year. For...
The choice of offices, except specific
For the selection of offices, other than specific (by identifier), it is convenient to use the opera...
Day of month, month, year, amount of payments
For information on this request, you need to group the entries in the payment table by field ...
Month, year, maximum amount of payments
This task is similar to the previous one. The difference is that you need to group by year and month...
Customers who did not order
Consider the request for receiving customers who did not make orders in February 2004. To do this, f...
Period of sales orders
We will select customers who have a period between two orders for more than three months. For this, ...
Orders without payments
To get customers who made an order, but did not pay for it, you need to join the columns of customer...
Customer product lines
In order to obtain product lines for each customer, you first need to link customer tables and produ...
Sample table
Sometimes it is necessary to save the resulting table as a separate table with a specific name. For ...
Adding a record to the sample
Sometimes it is necessary to add records to the table not with specific data, but using data that is...
Working with Views
Representation ( Views ) is a database object, often called a "virtual table". In one ...