This walkalong consists of easy questions and their solutions from sql-practice.com, written in both SQL and django
ORM, They query results are also compared for equality.
Insert, Update and Delete queries are not included to keep database consistent.
The notebook (Django Shell-plus) is located in here
First cd into the notebook directory cd server/notebook
Then open the notebook using python ../manage.py shell_plus --notebook
import os
os.environ["DJANGO_ALLOW_ASYNC_UNSAFE"] = "true"
from utils import *
from sqlparse import format
from django.db.models import (
Q,
F,
Case,
When,
Count,
Func,
Min,
Max,
Sum,
Avg,
Value,
OuterRef,
Subquery,
CharField,
)
from django.db.models.functions import Concat, Cast, Round, Length
from django.db import connection
from customer_db.models import Provinces, Patients, Doctors, Admissions
def print_sql(queryset):
formatted = format(str(queryset.query), reindent=True)
print(formatted)
sqlq = sql_raw("SELECT * FROM Patients LIMIT 3")
common_fields = [
"first_name",
"last_name",
"gender",
"birth_date",
"city",
"province_id",
"allergies",
"height",
"weight",
]
ormq = Patients.objects.values(patient_id=F("id"), *common_fields)[:3]
orm_to_df(ormq)
| | first_name | last_name | gender | birth_date | city | province_id | allergies | height | weight | patient_id |
|---:|:-------------|:------------|:---------|:-------------|:----------|:--------------|:------------|---------:|---------:|-------------:|
| 0 | Cheryl | Mason | F | 1978-05-14 | Toronto | ON | Peanuts | 170 | 65 | 1 |
| 1 | Jason | Fleming | M | 1990-08-02 | Vancouver | BC | Sulfa drugs | 185 | 80 | 2 |
| 2 | Tiffany | McDonald | F | 1985-03-27 | Calgary | AB | Lactose | 155 | 58 | 3 |
equal(sqlq, ormq)
Equal ✔️
Show first name, last name, and gender of patients who's gender is 'M'
qstr = """
SELECT
first_name,
last_name,
gender
FROM Patients
WHERE gender = 'M';
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(gender="M").values("first_name", "last_name", "gender")
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."gender"
FROM "patients"
WHERE "patients"."gender" = M
orm_to_df(ormq[:3])
| | first_name | last_name | gender |
|---:|:-------------|:------------|:---------|
| 0 | Jason | Fleming | M |
| 1 | Alex | Carter | M |
| 2 | Gavin | Bryant | M |
Show first name and last name of patients who does not have allergies. (null)
qstr = """
SELECT
first_name,
last_name
FROM Patients
WHERE allergies IS NULL;
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(allergies=None).values("first_name", "last_name")
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name"
FROM "patients"
WHERE "patients"."allergies" IS NULL
ormq1 = Patients.objects.filter(allergies__isnull=True).values(
"first_name", "last_name"
)
equal(sqlq, ormq1)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name"
FROM "patients"
WHERE "patients"."allergies" IS NULL
orm_to_df(ormq[:3])
| | first_name | last_name |
|---:|:-------------|:------------|
| 0 | Alex | Carter |
| 1 | Adam | Nguyen |
| 2 | Shane | Simpson |
Show first name of patients that start with the letter 'C'
qstr = """
SELECT first_name
FROM Patients
WHERE first_name LIKE 'C%';
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(first_name__startswith="C").values("first_name")
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name"
FROM "patients"
WHERE "patients"."first_name" LIKE C% ESCAPE '\'
orm_to_df(ormq[:3])
| | first_name |
|---:|:-------------|
| 0 | Cheryl |
| 1 | Cassandra |
| 2 | Cameron |
Show first name and last name of patients that weight within the range of 100 to 120 (inclusive) We will do 70 - 80
qstr = """
SELECT
first_name,
last_name
FROM Patients
WHERE weight BETWEEN 70 AND 80;
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(weight__range=[70, 80]).values("first_name", "last_name")
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name"
FROM "patients"
WHERE "patients"."weight" BETWEEN 70 AND 80
ormq1 = Patients.objects.filter(weight__gte=70, weight__lte=80).values(
"first_name", "last_name"
)
equal(sqlq, ormq1)
print_sql(ormq1)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name"
FROM "patients"
WHERE ("patients"."weight" >= 70
AND "patients"."weight" <= 80)
orm_to_df(ormq[:3])
| | first_name | last_name |
|---:|:-------------|:------------|
| 0 | Jason | Fleming |
| 1 | Alex | Carter |
| 2 | Gavin | Bryant |
Show first name and last name concatinated into one column to show their full name.
qstr = """
SELECT
first_name || ' ' || last_name AS full_name
FROM patients;
"""
sqlq = sql_raw(qstr)
# sqlq = sql_raw("SELECT CONCAT(first_name,' ', last_name) full_name FROM Patients;")
ormq = Patients.objects.values(full_name=Concat("first_name", Value(" "), "last_name"))
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT COALESCE("patients"."first_name",) || COALESCE(COALESCE(,) || COALESCE("patients"."last_name",),) AS "full_name"
FROM "patients"
orm_to_df(ormq[:3])
| | full_name |
|---:|:-----------------|
| 0 | Cheryl Mason |
| 1 | Jason Fleming |
| 2 | Tiffany McDonald |
Show first name, last name, and the full province name of each patient.
Example: 'Ontario' instead of 'ON'
qstr = """
SELECT
first_name,
last_name,
province_name
FROM Patients p
INNER JOIN province_names pn ON p.province_id = pn.province_id;
"""
sqlq = sql_raw(qstr)
# The filter ensures an Inner join is performed
ormq = Patients.objects.filter(province__isnull=False).values(
"first_name", "last_name", province_name=F("province__name")
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"province_names"."province_name" AS "province_name"
FROM "patients"
INNER JOIN "province_names" ON ("patients"."province_id" = "province_names"."province_id")
WHERE "patients"."province_id" IS NOT NULL
orm_to_df(ormq[:3])
| | first_name | last_name | province_name |
|---:|:-------------|:------------|:-----------------|
| 0 | Cheryl | Mason | Ontario |
| 1 | Jason | Fleming | British Columbia |
| 2 | Tiffany | McDonald | Alberta |
Show how many patients have a birth_date with 2010 as the birth year. We will do 1995
# sqlq = sql_raw("SELECT COUNT(*) birth_year FROM Patients WHERE YEAR(birth_date) = 2010;")
# for sqlite (Notice the '1995' instead of 1995)
qstr = """
SELECT COUNT(*) AS birth_count
FROM Patients
WHERE strftime('%Y', birth_date) = '1995';
"""
sqlq = sql_raw(qstr)
ormq = (
Patients.objects.filter(birth_date__year=1995)
.annotate(birth_count=Func(F("id"), function="Count"))
.values("birth_count")
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT Count("patients"."patient_id") AS "birth_count"
FROM "patients"
WHERE "patients"."birth_date" BETWEEN 1995-01-01 AND 1995-12-31
ormq = Patients.objects.filter(birth_date__year=1995).aggregate(
birth_count=Count("birth_date")
)
print(ormq)
equal(sqlq, ormq)
{'birth_count': 5}
Equal ✔️
ormq1 = Patients.objects.filter(birth_date__year=1995).count()
ormq1 = [{"birth_count": ormq1}]
equal(sqlq, ormq1)
Equal ✔️
orm_to_df(ormq)
| | birth_count |
|---:|--------------:|
| 0 | 5 |
Show the first_name, last_name, and height of the patient with the greatest height.
qstr = """
SELECT
first_name,
last_name,
Max(height) AS max_height
FROM patients;
"""
sqlq = sql_raw(qstr)
# Subclassing Max and setting contains_aggregate = False also works
# class NonAggrMax(Max):
# contains_aggregate = False
ormq = Patients.objects.annotate(max_height=Func("height", function="Max")).values(
"first_name", "last_name", "max_height"
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
Max("patients"."height") AS "max_height"
FROM "patients"
orm_to_df(ormq)
| | first_name | last_name | max_height |
|---:|:-------------|:------------|-------------:|
| 0 | Cameron | Gupta | 190 |
Show all columns for patients who have one of the following patient_ids: 1,45,534,879,1000 We will use 1,45,53,87,100
qstr = """
SELECT
*
FROM patients
WHERE patient_id IN (1, 45, 53, 87, 100);
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(id__in=[1, 45, 53, 87, 100]).values(
patient_id=F("id"), *common_fields
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."gender",
"patients"."birth_date",
"patients"."city",
"patients"."province_id",
"patients"."allergies",
"patients"."height",
"patients"."weight",
"patients"."patient_id" AS "patient_id"
FROM "patients"
WHERE "patients"."patient_id" IN (1,
45,
53,
87,
100)
orm_to_df(ormq)
| | first_name | last_name | gender | birth_date | city | province_id | allergies | height | weight | patient_id |
|---:|:-------------|:------------|:---------|:-------------|:---------|:--------------|:------------|---------:|---------:|-------------:|
| 0 | Cheryl | Mason | F | 1978-05-14 | Toronto | ON | Peanuts | 170 | 65 | 1 |
| 1 | Ella | Lemieux | F | 1991-05-01 | Halifax | NS | Lactose | 164 | 60 | 45 |
| 2 | Avery | Roy | F | 1993-05-08 | Halifax | NS | | 165 | 61 | 53 |
| 3 | Avery | Chen | F | 1989-12-07 | Winnipeg | MB | | 163 | 58 | 87 |
| 4 | Nathan | Gupta | M | 1986-01-14 | Victoria | BC | Lactose | 184 | 82 | 100 |
Show the total number of admissions
sqlq = sql_raw("SELECT COUNT(*) AS total FROM admissions;")
ormq = Admissions.objects.aggregate(total=Count("id"))
equal(sqlq, ormq)
Equal ✔️
ormq1 = Admissions.objects.count()
ormq1 = [{"total": ormq1}]
equal(sqlq, ormq1)
Equal ✔️
orm_to_df(ormq)
| | total |
|---:|--------:|
| 0 | 170 |
Show all the columns from admissions where the patient was admitted and discharged on the same day.
qstr = """
SELECT *
FROM admissions
WHERE admission_date = discharge_date;
"""
sqlq = sql_raw(qstr)
admission_fields = [
"id",
"patient_id",
"admission_date",
"discharge_date",
"diagnosis",
"attending_doctor_id",
]
ormq = Admissions.objects.filter(admission_date=F("discharge_date")).values(
*admission_fields
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "admissions"."id",
"admissions"."patient_id",
"admissions"."admission_date",
"admissions"."discharge_date",
"admissions"."diagnosis",
"admissions"."attending_doctor_id"
FROM "admissions"
WHERE "admissions"."admission_date" = ("admissions"."discharge_date")
orm_to_df(ormq[:3])
| | id | patient_id | admission_date | discharge_date | diagnosis | attending_doctor_id |
|---:|-----:|-------------:|:-----------------|:-----------------|:----------------|----------------------:|
| 0 | 159 | 59 | 2022-06-07 | 2022-06-07 | Meningitis | 5 |
| 1 | 160 | 60 | 2022-06-08 | 2022-06-08 | Anemia | 12 |
| 2 | 162 | 62 | 2022-06-10 | 2022-06-10 | Gastroenteritis | 16 |
Show the patient id and the total number of admissions for patient_id 579. We will do 57
qstr = """
SELECT
patient_id,
Count(patient_id) admissions
FROM admissions
WHERE patient_id = 57;
"""
sqlq = sql_raw(qstr)
ormq = (
Admissions.objects.filter(patient_id=57)
.annotate(admissions=Func(F("patient_id"), function="Count"))
.values("patient_id", "admissions")
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "admissions"."patient_id",
Count("admissions"."patient_id") AS "admissions"
FROM "admissions"
WHERE "admissions"."patient_id" = 57
orm_to_df(ormq[:3])
| | patient_id | admissions |
|---:|-------------:|-------------:|
| 0 | 57 | 2 |
Based on the cities that our patients live in, show unique cities that are in province_id 'NS'?
qstr = """
SELECT DISTINCT city
FROM patients
WHERE province_id = 'NS';
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(province__id="NS").values("city").distinct()
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT DISTINCT "patients"."city"
FROM "patients"
WHERE "patients"."province_id" = NS
orm_to_df(ormq)
| | city |
|---:|:--------|
| 0 | Halifax |
Write a query to find the first_name, last name and birth date of patients who has height greater than 160 and weight greater than 70
qstr = """
SELECT
first_name,
last_name,
birth_date
FROM patients
WHERE height > 160 AND weight > 70;
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(height__gt=160, weight__gt=70).values(
"first_name", "last_name", "birth_date"
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."birth_date"
FROM "patients"
WHERE ("patients"."height" > 160
AND "patients"."weight" > 70)
orm_to_df(ormq[:3])
| | first_name | last_name | birth_date |
|---:|:-------------|:------------|:-------------|
| 0 | Jason | Fleming | 1990-08-02 |
| 1 | Alex | Carter | 1976-11-18 |
| 2 | Gavin | Bryant | 1980-02-06 |
Write a query to find list of patients first_name, last_name, and allergies from Hamilton where allergies are not null. We will use Toronto
qstr = """
SELECT
first_name,
last_name,
allergies
FROM patients
WHERE
city = 'Toronto'
AND allergies IS NOT NULL;
"""
sqlq = sql_raw(qstr)
ormq = Patients.objects.filter(city="Toronto", allergies__isnull=False).values(
"first_name", "last_name", "allergies"
)
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."allergies"
FROM "patients"
WHERE ("patients"."allergies" IS NOT NULL
AND "patients"."city" = Toronto)
ormq1 = Patients.objects.filter(
~Q(allergies=None),
city="Toronto",
).values("first_name", "last_name", "allergies")
equal(sqlq, ormq1)
print_sql(ormq1)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."allergies"
FROM "patients"
WHERE (NOT ("patients"."allergies" IS NULL)
AND "patients"."city" = Toronto)
ormq2 = (
Patients.objects.filter(city="Toronto")
.exclude(allergies=None)
.values("first_name", "last_name", "allergies")
)
equal(sqlq, ormq2)
print_sql(ormq2)
Equal ✔️
SELECT "patients"."first_name",
"patients"."last_name",
"patients"."allergies"
FROM "patients"
WHERE ("patients"."city" = Toronto
AND NOT ("patients"."allergies" IS NULL))
orm_to_df(ormq[:3])
| | first_name | last_name | allergies |
|---:|:-------------|:------------|:------------|
| 0 | Cheryl | Mason | Peanuts |
| 1 | Kimberly | Lee | Pollen |
| 2 | Maria | Singh | Pollen |
Based on cities where our patient lives in, write a query to display the list of unique city starting with a vowel (a, e, i, o, u). Show the result order in ascending by city.
# You can use city LIKE '[aeiou]%'
# We are doing case insensitve matching here.
qstr = """
SELECT DISTINCT city
FROM patients
WHERE
city LIKE 'a%'
OR city LIKE 'e%'
OR city LIKE 'i%'
OR city LIKE 'o%'
OR city LIKE 'u%'
ORDER BY city;
"""
sqlq = sql_raw(qstr)
query = Q()
for ch in ["a", "e", "i", "o", "u"]:
query |= Q(city__istartswith=ch)
ormq = Patients.objects.filter(query).values("city").distinct().order_by("city")
equal(sqlq, ormq)
print_sql(ormq)
Equal ✔️
SELECT DISTINCT "patients"."city"
FROM "patients"
WHERE ("patients"."city" LIKE a% ESCAPE '\' OR "patients"."city" LIKE e% ESCAPE '\' OR "patients"."city" LIKE i% ESCAPE '\' OR "patients"."city" LIKE o% ESCAPE '\' OR "patients"."city" LIKE u% ESCAPE '\')
ORDER BY "patients"."city" ASC
ormq1 = (
Patients.objects.filter(city__regex=r"(?i)^[aeiou].+")
.values("city")
.distinct()
.order_by("city")
)
equal(sqlq, ormq1)
print_sql(ormq1)
Equal ✔️
SELECT DISTINCT "patients"."city"
FROM "patients"
WHERE "patients"."city" REGEXP (?i)^[aeiou].+
ORDER BY "patients"."city" ASC
orm_to_df(ormq1)
| | city |
|---:|:-------|
| 0 | Ottawa |