Examples I
Examples I#
Let’s see some assorted SQL query examples. If you feel like it, try to write them on your own.
But first of all, let’s create a connection and import the sql extension to be able to use magics.
import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://bob:1234@localhost:5432/dvdrental')
engine.execution_options(isolation_level="AUTOCOMMIT")
%load_ext sql
%sql $engine.url
'Connected: bob@dvdrental'
How many payment transactions were greater thatn $7.00?
%%sql
SELECT count(*)
FROM payment
WHERE amount > 7;
* postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
| count |
|---|
| 1406 |
How many actors have a first name that starts with the letter P?
%%sql
SELECT count(*)
FROM actor
WHERE first_name LIKE 'P%';
* postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
| count |
|---|
| 5 |
How many unique districts are our customers from?
%%sql
SELECT count(DISTINCT district)
FROM address;
* postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
| count |
|---|
| 378 |
Retrieve the list of names of the distinct districts in the previous question, limiting the list to the first 5 ones that start with the letter B.
%%sql
SELECT DISTINCT district
FROM address
WHERE district LIKE 'B%'
LIMIT 5;
* postgresql://bob:***@localhost:5432/dvdrental
5 rows affected.
| district |
|---|
| Baden-Wrttemberg |
| Bchar |
| Bursa |
| British Colombia |
| Botosani |
How many films have a rating of R and a replacement cost between \(5 and \)15?
%%sql
SELECT count(*)
FROM film
WHERE replacement_cost BETWEEN 5 AND 15
AND rating = 'R';
* postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
| count |
|---|
| 52 |
How many films have the word Truman somewhere in the title?
%%sql
SELECT count(*)
FROM film
WHERE title ILIKE '%truman%';
* postgresql://bob:***@localhost:5432/dvdrental
1 rows affected.
| count |
|---|
| 5 |