SQL homework is one of the most in-demand skills in the modern IT industry (ranked 3rd in popularity, according to the StackOverflow Developer Survey 2020, even Python comes in 4th place). Of course, the competition in this area is huge, and study sometimes turns into sheer torture – students are given huge tasks, dozens of tricky questions are asked, lab reports are arranged, and all this should be done in a short period of time. Ask for SQL homework help if you get into such a situation. An expert will help you to deal with all tasks on time.
Today we are going to share five tasks and questions that are included in the SQL homework preparation program. These are tasks from real assignments. Try your hand, too, whether you can solve them without prompting.
Input data
There is an Analysis table:
- an_id – analysis ID;
- an_name – analysis name;
- an_cost – analysis cost;
- an_price – retail price of the analysis;
- an_group – analysis group.
There is a table of analysis groups:
- gr_id – group ID;
- gr_name – group name;
- gr_temp – storage temperature mode.
There is a table of orders:
- ord_id – order ID;
- ord_datetime – date and time of the order;
- ord_an – analysis ID.
Next, we will work with these tables.
Task 1
Wording: display the name and price for all analyzes that were sold on January 3, 2021, and for the entire next week. This is a task for novice specialists. It tests basic knowledge of SELECT queries and the ability to work with date-time. Note: teachers rarely find fault with the specifics of the dialect – if you are used to working in PostgreSQL, then use the familiar functions. The main thing is to solve the problem correctly.
Task 2
Wording: cumulatively calculates how the number of sold tests increased each month of each year, broken down by group. This task is of a higher level: it can be given to both Middle and Junior specialists. It tests a basic understanding of window functions, joins, and groupings. Note: after you’ve written the first version of your query, try optimizing it. For example, you can use CTE – common table expressions.
Task 3
In this task, we will work with another table (yes, there will be only one). The query itself in this task is not complicated, but to write it, it is necessary to be able to “think in SQL.” Consider a table of customer balances:
ClientBalance (client_id, client_name, client_balance_date, client_balance_value)
- client_id – client identifier;
- client_name – full name of the client;
- client_balance_date – date of the client’s balance;
- client_balance_value – the value of the client’s balance.
Formulation: full duplicates appeared in this table at some point in time. Suggest a way to get rid of them without creating a new table.
Question 1
There is a category of “tricky” questions that junior specialists, especially like to ask. One of these types is questioned on topics that you do not pay attention to in your everyday life and which you don’t think about. You can face them when the code starts to work incorrectly. But that’s another story. Question: How does the GROUP BY clause handle NULL fields? If you do not know the answer to this question, then be sure to check your projects – maybe you have an error somewhere?
Question 2
This question is not as tricky as the previous one but rather specific. However, it requires knowledge of window functions and their intricacies, and this is the original requirement for those who study SQL.
Question: What is the difference between the RANK () function and DENSE_RANK ()? Note: by the way, based on this question; they are very fond of giving problems in different variations: number lines with the same values without breaks, with breaks, and so on. Therefore, practice at your leisure.
We have sorted out with you only five possible tasks that teachers can assign. However, in fact, there are a lot of options. And in order to really succeed in learning SQL, you need to solve as many problems as possible, understand and feel the theoretical aspects, and literally learn to “think in SQL.”
