365 Days of Coding: Day 23

I found another sql challenge site like Leetcode and HackerRank. It’s called sqlpad. I am loving the sleek and neat interface. They only have SQL challenges at the moment. Besides challenges, they also offer SQL tutorial classes and resume reviews.

I solved four easy questions. Solving SQL challenges is like learning Excel where in if you stop the using it for a while, you tend to forget the basics. So even if they are easy questions, I find it refreshing to solve them.

First question: Write a query to return the name of the store and its manager, that generated the most sales.

Table: sales_by_store

col_name	col_type
store	text
manager	text
total_sales	numeric

SELECT store, manager
FROM sales_by_store
WHERE total_sales = (SELECT MAX(total_sales) FROM sales_by_store);

Second question: Write a query to find the top 3 film categories that generated the most sales.

Table: sales_by_film_category

col_name	col_type
category	text
total_sales	numeric

SELECT category
FROM sales_by_film_category
ORDER BY total_sales DESC
LIMIT 3;

Third question: Write a query to return the titles of the 5 shortest movies by duration.

Table: film

col_name	col_id
film_id	integer
title	text
description	text
release_year	integer
length	smallint

SELECT title
FROM film
ORDER BY length
LIMIT 5;

Fourth question:

• Write a SQL query to return this staff’s first name and last name.
• Picture field contains the link that points to a staff’s profile image.
• There is only one staff who doesn’t have a profile picture.

Table: staff

col_name	col_type
staff_id	integer
first_name	text
last_name	text
username	text
picture	character varying

SELECT first_name, last_name
FROM staff
WHERE picture is NULL
LIMIT 5;

• Facebook
• Instagram
• LinkedIn
• GitHub