SQL/Python and SQL, Sqlitetutorial for many, sql for the weary

Question 1

basic query using sqlite3, also sorting in reverse alphabetical order

Answer 1

conn = sqlite3.connect(“jobs2.db”)
cursor = conn.cursor()
query = “select Major from recent_grads ORDER BY Major DESC;”
cursor.execute(query)
reverse_alphabetical = cursor.fetchall()

Question 2

Sort

Answer 2

SELECT
select_list
FROM
table
ORDER BY
column_1 ASC,
column_2 DESC;

Question 3

NA

Answer 3

NULL , use IS NULL

Question 4

Select and NULL

Answer 4

SQLite considers NULL values as duplicates.

select species, sex, island
from penguins
where sex is null;

https://gvwilson.github.io/sql-tutorial/

Question 5

Third smallest

Answer 5

SELECT
trackid,
name,
bytes
FROM
tracks
ORDER BY
bytes
LIMIT 1 OFFSET 2;

Question 6

Combine where and in with list generated by where

Answer 6

SELECT
TrackId,
Name,
AlbumId
FROM
Tracks
WHERE
AlbumId IN (
SELECT
AlbumId
FROM
Albums
WHERE
ArtistId = 12
);

Question 7

LIKE, with % and _

Answer 7

The percent sign % wildcard examples

The s% pattern that uses the percent sign wildcard ( %) matches any string that starts with s e.g.,son and so.

The %er pattern matches any string that ends with er like peter, clever, etc.

And the %per% pattern matches any string that contains per such as percent and peeper.
The underscore _ wildcard examples

The h_nt pattern matches hunt, hint, etc. The __pple pattern matches topple, supple, tipple, etc.
Note that SQLite LIKE operator is case-insensitive. It means “A” LIKE “a” is true.

However, for Unicode characters that are not in the ASCII ranges, the LIKE operator is case sensitive e.g., “Ä” LIKE “ä” is false.
In case you want to make LIKE operator works case-sensitively, you need to use the following PRAGMA:

PRAGMA case_sensitive_like = true

Question 8

Unix like matching

Answer 8

Glob

Question 9

Change names

Answer 9

select
flipper_length_mm / 10.0 as flipper_cm,
body_mass_g / 1000.0 as weight_kg,
island as where_found
from penguins
limit 3;

Question 10

Some aggregation functions

Answer 10

Note that it ignores NULL values

Select
max(bill_length_mm) as longest_bill,
min(flipper_length_mm) as shortest_flipper,
avg(bill_length_mm) / avg(bill_depth_mm) as weird_ratio
from penguins;

out/common_aggregations.out

This actually shouldn’t work: can’t calculate maximum or average if any values are null
SQL does the useful thing instead of the right one

longest_bill | shortest_flipper | weird_ratio |
|————–|——————|——————|
| 59.6 | 172 | 2.56087082530644 |

Question 11

Count

Answer 11

count(*) counts rows
count(column) counts non-null entries in column
count(distinct column) counts distinct non-null entries

Question 12

Averages over a group

Answer 12

select
sex,
avg(body_mass_g) as average_mass_g
from penguins
group by sex;