Practical SQL Queries, Cheat Sheets, and Interview Q&A for Data Scientists

• Nowadays, for professionals looking to build a career in data science, learning SQL is a must-have skill. Read more here.
• This all-inclusive SQL server tutorial helps data scientists get started with SQL straight away through many simple practical examples and exercises (with answers). The best way we learn SQL is by practice.
• The present tutorial also includes several important SQL basics cheat sheets, representing a quick guide to SQL commands and queries along with their examples and descriptions. 
• In addition to numerous references and links to free online SQL resources, we have compiled a set of top SQL interview Q&A tailored to data scientists.

Become a SQL pro! Begin your SQL journey with confidence! 

Table of Contents

1. MS SQL Server Requirements
2. RDBMS
3. SQL SELECT Statement
4. SQL Quieries with Constraints
5. SQL ORDER BY Statement
6. The SQL AND/OR Operator
7. Multi-Table INNER JOIN Queries
8. Multi-Table OUTER JOIN Queries
9. SQL Queries with Expressions
10. SQL Queries with Aggregates
11. SQL Cheat Sheets
12. Interview Q&A
13. Discussion
14. Conclusions
15. The Road Ahead
16. Resources

MS SQL Server Requirements

• For the sake of simplicity, we restrict ourselves to the MS SQL Server.

• MS SQL Server requires a minimum of 6 GB of available hard-disk space.
• Memory Express Editions: 1 GB.
• Processor Speed x64 Processor: 1.4 GHz
• Windows 10 TH1 1507 or greater with .NET Framework

• For more information on installing MS SQL Server on Server Core, see Install SQL Server on Server Core.

RDBMS

• RDBMS stands for Relational Database Management System.
• The data in RDBMS is stored in database objects called tables.
• A table is a data collection that consists of columns and rows (records).
• A field is a column in a table that is designed to maintain specific information about every record in the table (Customer ID, Address, City, etc.).
• A record is a horizontal entity in a table. A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Example: Table Vehicles

Key Takeaways:

• A relational database represents a collection of related (two-dimensional) tables.
• Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.

SQL SELECT Statement

• The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify:

SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ] 

• In the sequel, we will use the well-known Northwind sample database (aka Customers table):

Tablename	Records
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

• Example:
• Return data from the Customers table

SELECT CustomerName,City FROM Customers;

• Result:
• Number of Records: 91

• Example: Select ALL columns
• Return all the columns from the Customers table

SELECT * FROM Customers;

• Result:
• Number of Records: 91

• For the sake of completeness, we will also be using a database with data about some of Pixar’s classic movies for most of our exercises. This exercise will involve the Movies table, and the default query below currently shows all the properties of each movie:

SELECT * FROM movies;

• Number of Records: 14

Table: Movies

• Find the title of each film

SELECT title FROM movies;

• Find the director of each film

SELECT director FROM movies;

• Find the title and director of each film

SELECT title, director FROM movies; 

• Find the title and year of each film

SELECT title, year FROM movies; 

• Find all the information about each film

SELECT * FROM movies; 

See Table Movies above.

SQL Quieries with Constraints

• Let’s apply the SQL WHERE Clause to the Customers table

SELECT * FROM Customers
WHERE Country='Mexico';

• Result Number of Records: 5

• Let’s look at the Movies table and find the movie with a row id of 6

SELECT id, title FROM movies 
WHERE id = 6;

• Output

• Table Movies: Find the movies released in the years between 2000 and 2010 

SELECT title, year FROM movies
WHERE year BETWEEN 2000 AND 2010;

• Output

• Table Movies: Find the movies not released in the years between 2000 and 2010

SELECT title, year FROM movies
WHERE year < 2000 OR year > 2010;

• Table Movies: Find all the Toy Story movies

SELECT title, director FROM movies 
WHERE title LIKE "Toy Story%";

• Table Movies: Find all the movies directed by John Lasseter

SELECT title, director FROM movies 
WHERE director = "John Lasseter";

• Table Movies: Find all the movies (and director) not directed by John Lasseter

SELECT title, director FROM movies 
WHERE director != "John Lasseter";

• Table Movies: Find all the WALL-* movies

SELECT * FROM movies 
WHERE title LIKE "WALL-_";

• Table Movies: List all directors of Pixar movies (alphabetically), without duplicates

SELECT DISTINCT director FROM movies
ORDER BY director ASC;

SQL ORDER BY Statement

• Customers table: Sort the products by price

SELECT * FROM Products
ORDER BY Price;

• Result Number of Records: 77

• Customers table: Sort the products from highest to lowest price

SELECT * FROM Products
ORDER BY Price DESC;

• Result Number of Records: 77

• Customers table: Sort the products alphabetically by Product Name

SELECT * FROM Products
ORDER BY ProductName;

• Result Number of Records: 77

• Select all customers from the Customers table, sorted ascending by the “Country” and descending by the “Customer Name” column

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

• Result Number of Records: 91

The SQL AND/OR Operator

• Customers table: Select all customers from Spain that starts with the letter ‘G’

SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';

• Result Number of Records: 2

• Customers table: Select all Spanish customers that starts with either “G” or “R”

SELECT * FROM Customers
WHERE Country = 'Spain'
AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');

• Result Number of Records: 3

• Customers table: Select all customers from Germany or Spain

SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

• Result Number of Records: 16

Multi-Table INNER JOIN Queries

• Table: Movies

• Table: Box-office

• Find the domestic and international sales for each movie

SELECT title, domestic_sales, international_sales 
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

• Show the sales numbers for each movie that did better internationally rather than domestically 

SELECT title, domestic_sales, international_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
WHERE international_sales > domestic_sales;

• List all the movies by their ratings in descending order

SELECT title, rating
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

Multi-Table OUTER JOIN Queries

• Table Buildings

• Table Employees

• Find the list of all buildings that have employees

SELECT DISTINCT building FROM employees;

• Find the list of all buildings and their capacity

SELECT * FROM buildings;

• List all buildings and the distinct employee roles in each building (including empty buildings) 

SELECT DISTINCT building_name, role 
FROM buildings 
  LEFT JOIN employees
    ON building_name = building;

SQL Queries with Expressions

• We are going to have to use expressions to transform the Box Office table into something easier

• Input data

Table Movies

Table Box Office

• List all movies and their combined sales in millions of dollars

SELECT title, (domestic_sales + international_sales) / 1000000 AS gross_sales_millions
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

Query Results:

• List all movies and their ratings in percent

SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

Query Results

• List all movies that were released on even number years

SELECT title, year
FROM movies
WHERE year % 2 = 0;

Query Results

SQL Queries with Aggregates

• SQL also supports the use of aggregate expressions (or functions) that allow you to summarize information about a group of rows of data. 
• Table: Employees

• Find the longest time that an employee has been at the studio

SELECT MAX(years_employed) as Max_years_employed
FROM employees;

Output:

• For each role, find the average number of years employed by employees in that role

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

Output:

• Find the total number of employee years worked in each building

SELECT building, SUM(years_employed) as Total_years_employed
FROM employees
GROUP BY building;

Read more here.

SQL Cheat Sheets

Read more here.

Interview Q&A

Read more here.

Discussion

• Let’s discuss SQL using Python, viz. integrating SQLite3 with Python
• To use SQLite, we must import sqlite3
• Then create a connection using connect() method and pass the name of the database
• After this, a cursor object is called to be capable to send commands to the SQL
• Output: Connected to the database
• To execute a query in the database, create an object and write the SQL command in it with being commented. Example:- sql_comm = ”SQL statement”
• Execute the command: call the cursor method execute() and pass the name of the sql command as a parameter in it.

Conclusions

• SQL (Structured Query Language) is a language to operate databases
• SQL is an ANSI/ISO certified standard and mostly all the RDMS like MySQL, MS Access, Oracle, Sybase, Informix, PostgreSQL and SQL Server use SQL as a standard database query language
• SQL is a medium to communicate with the data stored in the relational database. It can perform all the CRUD (CREATE, RETRIEVE, UPDATE, DELETE) operations in the database
• SQL is used by most of the database programs present. (i.e. SQL Server, MYSQL, SQLite, etc.)
• SQL is widely popular because it offers the following advantages:

• Allows users to access data in the relational database management systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows to embed within other languages using SQL modules, libraries & pre-compilers.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.

The Road Ahead

10 Advanced SQL Concepts You Should Know for Data Science Interviews:

• Common table expressions (CTEs).
• Recursive CTEs.
• Temporary functions.
• Pivoting data with case when.
• Except versus Not In.
• Self joins.
• Rank versus dense rank versus row number.
• Calculating delta values.
• Calculating running totals.
• Date-time manipulation.

Resources

• w3schools, w3resource
• SQL Practice 1
• sqbolt
• 9 Websites you can practice SQL for FREE
• 7 Best Platforms to Practice SQL
• 17 Sites for SQL Practice
• The best & quickest way to learn SQL
• How can I practice SQL at home for free?
• Want to learn and master PL/SQL?
• Learn and share SQL
• SQL Interview Questions
• MS SQL Training
• SQL Interview Questions and Answers
• 105 SQL Interview Questions
• Basic SQL Guide