Understanding SQL and Relational Databases — Part 3

How to utilize filtering, ordering, and grouping features when using SQL.

Introduction

import sqlite3 
conn = sqlite3.connect('pubs_data.db')
cur = conn.cursor()
cur.execute("""SELECT * FROM titles;""").fetchall()
Image for post
https://www.google.com/url?sa=i&url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fssms%2Fvisual-db-tools%2Fdesign-database-diagrams-visual-database-tools&psig=AOvVaw1vSvc7oA0_z716qiIlgK7p&ust=1592517304324000&source=images&cd=vfe&ved=0CAIQjRxqFwoTCPCmyoCFiuoCFQAAAAAdAAAAABAD

Using the WHERE Clause

cur.execute("""SELECT * FROM titles 
WHERE title = 'Zed';""").fetchall()
cur.execute("""SELECT * FROM titles 
WHERE title = 'Zed' OR title = 'We';""").fetchall()

Using the ORDER BY and LIMIT Clauses

cur.execute("""SELECT * FROM titles ORDER BY pubdate;""").fetchall()
cur.execute("""SELECT * FROM titles 
ORDER BY pubdate DESC LIMIT 1;""").fetchone()
cur.execute("""SELECT title, type, price
FROM titles
WHERE (type = 'Sci-Fi' OR type = 'Fantasy') AND (price >= 20.00)
ORDER BY price DESC
LIMIT 15;""")

Other Modifiers

BETWEEN

cur.execute("""SELECT title FROM titles 
WHERE pubdate BETWEEN ('2017') AND
('2019');""").fetchall()

Aggregate Functions

cur.execute("""SELECT MIN(price) FROM titles;""").fetchall()
cur.execute("""SELECT title, type, price, pubdate FROM titles 
WHERE (type = 'Sci-Fi') AND (pubdate = '2019')
GROUP BY price;""").fetchall()

Conclusion

Written by

Aspiring Data Scientist — Recent Graduate of Flatiron School’s Online Data Science Bootcamp

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store