CODING FROM SCRATCH
SQL Websites
amazon
G
∞
Select Star SQL
Home
Front Matter
Impetus
Pedagogy
Dataset
Technicals
Contact
Beasley's Last Statement
Impetus
When I was a data scientist at Quora, I used to have people ask me for resources for learning SQL. I struggled to find something I could stand behind because I felt that a good resource had to
struggling with them should occupy most of your time.
Exercises should be realistic and substantial.
To quote Alan Kay: "You never let [the learner] do
something that isn't the real thing—but you have to have to work
your ass off to figure out what the real thing is in the context
of the way their minds are working at that developmental
level."
Likewise, the exercises here have been designed to
introduce increasingly sophisticated SQL techniques while
exploring the dataset in ways that people would actually be
interested in.
Learning to program is learning a mental model
Our goal here isn't to learn the rules for how to use GROUP
BY or when to pick a LEFT JOIN over an INNER JOIN. We
know we've been successful if after writing a SQL query, you
can close your eyes and imagine what the computer would
do and what output it would give. Only then will you be
able to solve real-world problems with SQL.
Dataset
Our dataset documents Texas death row inmates executed from
1976, when the Supreme Court reinstated the death penalty, to
the present. It was extracted from the Texas Department of
Criminal Justice website using automatic means where possible.
However, much of the pre-1995 data is only available as images
of physical documents and these required painstaking manual
extraction.
The raw data is available as a csv for download. Because of the
manual extraction and cleaning that was done, it is probably the
most complete set of Texas death row data on the internet. You
can also explore a subset of it that I've prepared for this book:
This is an interactive code editor. You can edit query below.
Shift+Enter is the keyboard shortcut for running the query.
1 SELECT *
2 FROM executions
3 LIMIT 3
Run 1 Reset
On one level, the data is simply a part of a mundane
programming book. On another, each row represents immense
suffering, lives lost, and in some cases amazing redemption and
acceptance. In preparing for this dataset, I was deeply moved by
a number of the statements and found myself re-evaluating my
position on capital punishment. I hope that as we examine the
Technicals
While you're
and learning
you can also
have interactive
lessons sprinkled
in.
is Data Learner.
Basic SQL
Lesson 101
TUTORIAL INTRO
Learn SQL, Interview Well. Get $$$.
Start Now
Lesson 102
SQL SELECT
Learn to output data with SELECT.
Start Now
Lesson 103
SQL WHERE
Select rows based on a condition.
Start Now
Lesson 104
AND, OR, NOT X
Lesson 105
SQL BETWEEN
Lesson 106
SQL IN
Start Now
Start Now
Start Now
Intermediate SQL
Lesson 201
SQL CASE
Catch a CASE.
Start Now
Lesson 202
SQL JOINS
Analyze two tables in one go.
Start Now
Lesson 203
DATE FUNCTIONS
Learn to handle dates and timestamps.
Start Now
Lesson 207
SQL UNION
Combine tables without JOINs.
Start Now
Lesson 208
WRITE CLEAN SQL
Best practices to write maintainable SQL.
Start Now
Lesson 209
EXECUTION ORDER
A quintessential SQL interview topic.
Start Now
Lesson 210
SQL PIVOTING
Excel's PIVOT function but in SQL.
Start Now
Lesson 211
STRING FUNCTIONS
Use string functions to clean text data.
Start Now
Lesson 212
INSTACART SQL CASE
Find business trends in Instacart data!
Start Now
DataLemur
Career Resources
Free 6-Step Data Interview Crash Course
Free SQL Tutorial for Data Analytics
SQL Interview Cheat Sheet PDF
Ultimate SQL Interview Guide
Ace the Data Science Job Interview Course
Ace the Data Science Interview
Best Books for Data Analysis
SQL Squid Game
Support
Data, other Blog
Privacy policy
Terms of Service
Email Us
Interview Questions
Data Science Interview Questions
SQL Interview Questions
Python Coding Interview Questions
Questions
Machine Learning Interview Questions
Meta Data Science Interview Guide
TikTok Data Science Interview Guide
Amazon Data Science Interview Guide
Amazon SQL Interview Questions
Meta SQL Interview Questions
Microsoft SQL Interview Questions
Apple SQL Interview Questions
© 2023 DataLemur, Inc.
Tesla
New York Times
Robinhood
Snapchat
Twitter
Google
Walmart
Amazon
Facebook
Wayfair
Google
Unfinished Parts
Laptop vs Mobile Viewership
Cities With Completed Trades
Sending vs. Opening Snaps
Tweets' Rolling Averages
Odd and Even Measurements
Frequently Purchased Pairs
Average Review Ratings
Active User Retention
Y-o-Y Growth Rate
Median Google Search Frequency
Easy
Easy
Medium
Medium
Medium
Medium
Medium
Hard
Medium
Hard
I signed up for the DataLemur
beta in July when a post about it
randomly came up on my LinkedIn
feed.
I'm so glad I came to know about
DataLemur because a few months
later when I interviewed at
Amazon, I got to know of the
SQL problem from DataLemur
showed up and I got an SQL
interview which I was easily able to
ace.
- Jia Z.
Senior Data Scientist at Amazon
In Fall 2021 I used the book Ace
the Data Science Interview to land
a Product Data Science job at
Meta.
As I ramp to switch into in early
2023, DataLemur is proving itself
very handy because it allows me to
practice the SQL questions and
SQL interview questions that I didn't
in the book.
- Rakeah
Data Scientist at Meta
Udemy SQL courses but they
didn't have enough interview
practice which is why my INT
interview led me to try
DataLemur.
Using the detailed hints and nice
solutions on DataLemur I was able
to land a job at Accenture.
Accenture and get ₹4 lakh salary
increase.
- Sonal O.
BI Engineer at Accenture
amazon
∞
Meta
accenture
SQL Murder Mystery
Can you find out whodunnit?
After learning
of SQL,
this website
to solve a
murder mystery
using SQL.
There's been a Murder in SQL City! The SQL Murder Mystery is designed to be both a self-directed lesson to learn
SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.
New to SQL?
This exercise is meant more as a way to practice SQL skills than a full tutorial. If you've never used SQL at all, try
the walkthrough. If you really want to learn a lot about SQL, you may prefer a complete tutorial like Select Star
SQL.
If you're comfortable with SQL, you can dive in below!
Experienced SQL sleuths start here
A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you
somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan. 15, 2018 and
that it took place in SQL City. Start by retrieving the corresponding crime scene report from the police
department's database.
Exploring the Database Structure
Experienced SQL users can often use database queries to infer the structure of a database. But each database
system has different ways of managing this information. The SQL Murder Mystery is built using SQLite. Use this
SQL command to find the tables in the Murder Mystery database.
Run this query to find the names of the tables in this database.
This command is specific to SQLite. For other databases, you'll have to learn their specific syntax.
1 SELECT name
2 FROM sqlite_master
3 WHERE type = 'table'
Run 1 Reset
Besides knowing the table names, you need to know how each table is structured. The way this works is also
dependent upon which database technology you use. Here's how you do it with SQLite.
Run this query to find the structure of the 'crime_scene_report' table
Change the value of 'name' to see the structure of the other tables you learned about with the previous query.
1 SELECT *
2 FROM crime_scene_report
3 WHERE name = 'crime_scene_report'
Run 1 Reset
And it does
pretty complex
so it really
is a good test
you know
your stuff.