Twenty-five SQL practice exercises

Twenty-five SQL practice exercises

These questions and example solutions will keep your skills sharp. Structured query language (SQL) is used to retrieve and manipulate data stored in relational databases. Gaining working proficiency in SQL is an important prerequisite for many technology jobs and requires a bit of practice.

Structured query language (SQL) is used to retrieve and manipulate data stored in relational databases. Gaining working proficiency in SQL is an important prerequisite for many technology jobs and requires a bit of practice.

To complement SQL training resources (PGExercisesLeetCodeHackerRankMode) available on the web, I’ve compiled a list of my favorite questions that you can tackle by hand or solve with a PostgreSQL instance.

These questions cover the following critical concepts:

  • Basic retrieval (SELECT, FROM)
  • Creating and aliasing (WITH, AS, GENERATE_SERIES)
  • *Filtering *(DISTINCT, WHERE, HAVING, AND, OR, IN, NOT IN)
  • Aggregation (GROUP BY with COUNT, SUM, AVERAGE)
  • Joins (INNER JOIN, LEFT JOIN, FULL OUTER JOIN on one or multiple (in)equalities, CROSS JOIN, UNION and UNION ALL)
  • Conditional *statements *(CASE - WHEN - THEN - ELSE - END)
  • Window functions (RANK, DENSE_RANK, ROW_NUMBER, SUM with PARTITION BY - ORDER BY)
  • *Formatting *(LIMIT, ORDER BY, casting as an integer, float, or date, CONCAT, COALESCE)
  • Arithmetic *operations and comparisons *(+, -, *, /, //, ^, <, >, =, !=)
  • Datetime operations (EXTRACT(month/day/year))

Try it yourself

You can try these out yourself by downloading PostgreSQL and PSequel (see this tutorial for a step-by-step installation guide) and then running the queries shown in the grey boxes in the text below. PSequel is only available for Mac — if you’re using a PC, you can try one of these Windows alternatives.

Image for post

Try these queries yourself using PSequel and the input tables provided below.

The first block of text in each query shown below establishes the input table and follows the format:

WITH input_table (column_1, column_2) 
AS (VALUES 
(1, 'A'), (2, 'B'))

You can query against the input table using PSequel (shown at left) and easily construct new tables for your own problems using this template.

Web-based SQL training resources like those mentioned above fall short along a few dimensions. LeetCode, for instance, doesn’t support the use of window functions and hides its most interesting questions behind a paywall. Beyond this, running SQL queries in your browser can be extremely slow — the data sets are large and retrieval speed is often throttled for non-premium users. Locally executing a query, on the other hand, is instantaneous and allows for rapid iteration through syntax bugs and intermediate tables. I’ve found this to be a more satisfying learning experience.

The questions outlined below include example solutions confirmed to work in PostgreSQL. Note that there is often more than one way to obtain the correct answer to a SQL problem. My preference is to use common table expressions (CTEs) rather than nested subqueries — CTEs allow for a more linear illustration of the data wrangling sequence. Both approaches, however, can yield identical solutions. Feel free to leave your alternative answers in the comments!

Questions

1. Cancellation rates

From the following table of user IDs, actions, and dates, write a query to return the publication and cancellation rate for each user.

WITH users (user_id, action, date) 
AS (VALUES 
(1,'start', CAST('01-01-20' AS date)), 
(1,'cancel', CAST('01-02-20' AS date)), 
(2,'start', CAST('01-03-20' AS date)), 
(2,'publish', CAST('01-04-20' AS date)), 
(3,'start', CAST('01-05-20' AS date)), 
(3,'cancel', CAST('01-06-20' AS date)), 
(1,'start', CAST('01-07-20' AS date)), 
(1,'publish', CAST('01-08-20' AS date))),
-- retrieve count of starts, cancels, and publishes for each user
t1 AS (
SELECT user_id, 
sum(CASE WHEN action = 'start' THEN 1 ELSE 0 END) AS starts, 
sum(CASE WHEN action = 'cancel' THEN 1 ELSE 0 END) AS cancels, 
sum(CASE WHEN action = 'publish' THEN 1 ELSE 0 END) AS publishes
FROM users
GROUP BY 1
ORDER BY 1)
-- calculate publication, cancelation rate for each user by dividing by number of starts, casting as float by multiplying by 1.0
SELECT user_id, 1.0*publishes/starts AS publish_rate, 1.0*cancels/starts AS cancel_rate
FROM t1

business interview sql data-science analytics

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

2020 Best Online Masters in Analytics, Business Analytics, Data Science – Updated

We provide an updated list of best online Masters in AI, Analytics, and Data Science, including rankings, tuition, and duration of the education program.

50 Data Science Jobs That Opened Just Last Week

Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments. Our latest survey report suggests that as the overall Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments, data scientists and AI practitioners should be aware of the skills and tools that the broader community is working on. A good grip in these skills will further help data science enthusiasts to get the best jobs that various industries in their data science functions are offering.

Data Cleaning in R for Data Science

A data scientist/analyst in the making needs to format and clean data before being able to perform any kind of exploratory data analysis.

Interviewing for Data Science Internship. How to Prepare.

How can you improve? For data science theory, I recommend getting a good mathematical understanding of the most common algorithms. There are two books that I usually recommend: Pattern Recognition and Machine Learning, and First Course in Machine Learning.