Intro to Relational Databases Flashcards
Using an SQL query, find the names of all the animals that are not gorillas and not named ‘Max’
select name from animales where not (species = ‘gorilla’ or name = ‘Max’)
The syntax of the select statement with a where clause:
select columns from tables where condition ;
Columns are separated by commas; use * to select all columns.
The condition is a Boolean expression on column values. SQL supports the Boolean operations and, or, and not which work the same as in Python.
We can switch between the expression form (not X) and (not Y) and the form not (X or Y) because of a logic rule called DeMorgan’s Law.
Using an SQL query, find all the llamas born between January 1, 1995 and December 31, 1998. Fill in the ‘where’ clause in this query.
select name from animals where species = ‘llama’ and birthdate >= ‘1995-01-01’ and birthdate <= ‘1998-12-31’;
Using the database and SQL, find all the llamas born between January 1, 1995 and December 31, 1998.
QUERY = “select max(name) from animals;”
QUERY = “select * from animals limit 10;”
QUERY = “select * from animals where species = ‘orangutan’ order by birthdate;”
QUERY = “select name from animals where species = ‘orangutan’ order by birthdate desc;”
QUERY = “select name, birthdate from animals order by name limit 10 offset 20;”
Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.
QUERY = “select species, min(birthdate) from animals group by species;”
// Create database
create table animals (
name text,
species text,
birthdate date);
create table diet (
species text,
food text);
create table taxonomy (
name text,
species text,
genus text,
family text,
t_order text);
create table ordernames (
t_order text,
name text);
What do the following do
… limit count
… limit count offset skip
… order by columns
… order by columns desc
… group by columns
… limit count
Return just the first count rows of the result table.
… limit count offset skip
Return count rows starting after the first skip rows.
… order by columns
… order by columns desc
Sort the rows using the columns (one or more, separated by commas) as the sort key. Numerical columns will be sorted in numerical order; string columns in alphabetical order. With desc, the order is reversed (desc-ending order).
… group by columns
Change the behavior of aggregations such as max, count, and sum. With group by, the aggregation will return one row for each distinct value in columns.
What does ‘as num’ mean in an SQL query?
eg
select name, count (*) as num from animals group by name;
count (*) means count all the rows
‘as num’ means call the count column ‘num’
eg
select name, count (*) as num from animals group by name;
Describe some select clauses
These are all the select clauses we’ve seen in the lesson so far.
where
The where clause expresses restrictions — filtering a table for rows that follow a particular rule. where supports equalities, inequalities, and boolean operators (among other things):
where species = ‘gorilla’ — return only rows that have ‘gorilla’ as the value of the species column.
where name >= ‘George’ — return only rows where the name column is alphabetically after ‘George’.
where species != ‘gorilla’ and name != ‘George’ — return only rows where species isn’t ‘gorilla’ and name isn’t ‘George’.
limit / offset
The limit clause sets a limit on how many rows to return in the result table. The optional offset clause says how far to skip ahead into the results. So limit 10 offset 100 will return 10 results starting with the 101st.
order by
The order by clause tells the database how to sort the results — usually according to one or more columns. So order by species, name says to sort results first by the species column, then by name within each species.
Ordering happens before limit/offset, so you can use them together to extract pages of alphabetized results. (Think of the pages of a dictionary.)
The optional desc modifier tells the database to order results in descending order — for instance from large numbers to small ones, or from Z to A.
group by
The group by clause is only used with aggregations, such as max or sum. Without a group by clause, a select statement with an aggregation will aggregate over the whole selected table(s), returning only one row. With a group by clause, it will return one row for each distinct value of the column or expression in the group by clause.
# # Write a query that returns all the species in the zoo, and how many animals of # each species there are, sorted with the most populous species at the top. # # The result should have two columns: species and number. # # The animals table has columns (name, species, birthdate) for each individual. #
# # Write a query that returns all the species in the zoo, and how many animals of each species there are, sorted with the most populous species at the top. # # The result should have two columns: species and number. # # The animals table has columns (name, species, birthdate) for each individual. #
QUERY = “select species, count(*) as num from animals group by species order by num desc”
What is the basic syntax for the insert statement:
The basic syntax for the insert statement:
insert into table ( column1, column2, … ) values ( val1, val2, … );
If the values are in the same order as the table’s columns (starting with the first column), you don’t have to specify the columns in the insert statement:
insert into table values ( val1, val2, … );
For instance, if a table has three columns (a, b, c) and you want to insert into a and b, you can leave off the column names from the insert statement. But if you want to insert into b and c, or a and c, you have to specify the columns.
A single insert statement can only insert into a single table. (Contrast this with the select statement, which can pull data from several tables using a join.)
# # Insert a newborn baby opossum into the animals table and verify that it's # been added. To do this, fill in the rest of SELECT_QUERY and INSERT_QUERY. # # SELECT_QUERY should find the names and birthdates of all opossums. # # INSERT_QUERY should add a new opossum to the table, whose birthdate is today. # (Or you can choose any other date you like.) # # The animals table has columns (name, species, birthdate) for each individual. #
# # Insert a newborn baby opossum into the animals table and verify that it's # been added. To do this, fill in the rest of SELECT_QUERY and INSERT_QUERY. # # SELECT_QUERY should find the names and birthdates of all opossums. # # INSERT_QUERY should add a new opossum to the table, whose birthdate is today. # (Or you can choose any other date you like.) # # The animals table has columns (name, species, birthdate) for each individual. #
SELECT_QUERY = ‘’’
select name, birthdate from animals where species = ‘opossum’;
‘’’
INSERT_QUERY = ‘’’
insert into animals (name, species, birthdate) values(‘Jon’, ‘opossum’, ‘2018-02-04’);
‘’’
Only should return two rows in the table
BEFORE INSERTING: \+-------+------------+ | name | birthdate | \+=======+============+ | Tori | 2011-04-05 | | Hazel | 2009-10-24 | | Neil | 2009-08-11 | \+-------+------------+
AFTER INSERTING: \+-------+------------+ | name | birthdate | \+=======+============+ | Tori | 2011-04-05 | | Hazel | 2009-10-24 | | Neil | 2009-08-11 | | Jon | 2018-02-04 | \+-------+------------+
Describe some common SQL queries
# Then try your own queries as well! #
QUERY = “select max(name) from animals;”
QUERY = “select * from animals limit 10;”
QUERY = “select * from animals where species = ‘orangutan’ order by birthdate;”
QUERY = “select name from animals where species = ‘orangutan’ order by birthdate desc;”
QUERY = “select name, birthdate from animals order by name limit 10 offset 20;”
Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.
QUERY = “select species, min(birthdate) from animals group by species;”
QUERY = “select name, count(*) as num from animals group by name;”
QUERY = “select name, count(*) as num from animals group by name order by num desc;”
QUERY = “select name, count(*) as num from animals group by name order by num desc limit 5;”
# # Find the names of the individual animals that eat fish. # # The animals table has columns (name, species, birthdate) for each individual. # The diet table has columns (species, food) for each food that a species eats. #
# # Find the names of the individual animals that eat fish. # # The animals table has columns (name, species, birthdate) for each individual. # The diet table has columns (species, food) for each food that a species eats. #
# Old way
QUERY = '''
select animals.name
from animals join diet
on animals.species = diet.species where food = 'fish';
'''
# Shorter way
QUERY = '''
select name from animals, diet
where animals.species = diet.species
and diet.food = 'fish';
'''
What is the ‘having’ clause?
The having clause works like the where clause, but it applies after group by aggregations take place. The syntax is like this:
select columns from tables group by column having condition;
Usually, at least one of the columns will be an aggregate function such as count, max, or sum on one of the tables’ columns. In order to apply having to an aggregated column, you’ll want to give it a name using as. For instance, if you had a table of items sold in a store, and you wanted to find all the items that have sold more than five units, you could use:
select name, count(*) as num from sales having num > 5;
If you use both where and having, the where condition will filter the rows that are going into the aggregation, and the having condition will filter the rows that come out of it.
Q - List the taxonomic orders by their common names (ordernames.name), sort by how many individual animals we have of each in the zoo.
Lesson 2 - video 15
create table animals (
name text,
species text,
birthdate date);
create table diet (
species text,
food text);
create table taxonomy (
name text,
species text,
genus text,
family text,
t_order text);
create table ordernames (
t_order text,
name text);
////////////////
select ordernames.name, count(*) as num
from (animals join taxonomy
on animals.species = taxonomy.name)
as ani_tax
join ordernames
on ani_tax.t_order = ordernames.t_order
group by ordernames.name
order by num desc
Writing code with DB-API
import sqlite3
conn = sqlite3.connect(“Database1”)
cursor = conn.cursor() // the cursor is where you carry out queries
cursor.execute(“select host_key from Database1 limit 10”)
results = cursor.fetchall() //or results = cursor.fetchone()
print results
conn.close()
// ‘.execute(“queary”)’ and ‘.fetchall’ and ‘.fetch one’ are methods carried out on ‘Cursor’
// note - if doing an insert query instead of a select, you’d need to ‘.commit’ the insertion, or if something went wrong, ‘.rollback’. nb you’re changing the database here not querying it. commit and rollback are methods carried out on ‘Connection’
What is atomicity?
The database transaction happens as a whole or not at all
linked to ‘commit’ command
‘.commit’ comes after the ‘.execute’ command
What is VirtuaBox and Vagrant?
VirtualBox is the software that actually runs the virtual machine. You can download it from virtualbox.org, here. Install the platform package for your operating system.
Vagrant is the software that configures the virtual machine and lets you share files between your host computer and the virtual machine’s filesystem.
You download the Linux configuration (OS) in the /vagrant folder on your machine.
The files you see here are the same as the ones in the vagrant subdirectory on your computer (where you started Vagrant from). Any file you create in one will be automatically shared to the other. This means that you can edit code in your favorite text editor, and run it inside the VM.
Files in the VM’s /vagrant directory are shared with the vagrant folder on your computer. But other data inside the VM is not. For instance, the PostgreSQL database itself lives only inside the VM.
The PostgreSQL database server will automatically be started inside the VM. You can use the psql command-line tool to access it and run SQL statements:
If you type exit (or Ctrl-D) at the shell prompt inside the VM, you will be logged out, and put back into your host computer’s shell. To log back in, make sure you’re in the same directory and type vagrant ssh again.
If you reboot your computer, you will need to run vagrant up to restart the VM.
Never use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string
What should you use instead
Use the .execute method instead of string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string
//eg - no commas like (‘%s’)
c.execute(“insert into posts values (%s)”, (content,))
(related to SQL injection attacks)
What is input sanitisation and output sanitisation?
related to SQL injection attacks
Output sanitization — cleaning up users’ posts before displaying them — makes sure of the latter.
The downside of not sanitizing inputs is that we have to treat the contents of the database as possibly unsafe.
///
Clearing out bad data before it ever gets into the database — input sanitization — is one effective approach to preventing attacks like this one.
But we’ll still have to clean out the bad data that’s already in the database.
How do you delete spam in forum?
In psql console
update posts set content = ‘safe’ where content like ‘%spam%’;
delete from posts where content = ‘safe’;
How do you declare a primary key?
Declare a primary key
create table students ( id serial primary key, name text, birthdate date );
How do you declare multiple primary keys?
Declaring multiple primary keys. Single primary keys go at beginning. Multiple ones, like this ones go at the end
create table postal_places ( postal_code text, country text, name text, primary key (postal_code, country) );
Declaring relationships;
PRODUCTS table with ‘sku’ column and then a SALES table to represent the sales of each products.
Write code that makes sure we can’t enter a sales table ‘sku’ that doesn’t exist in the original products (ie inventory table)
Lesson 4 vid 8
so now the ‘sku’ column in the SALES table can’t be inserted unless there’s a matching ‘sku’ in the PRODUCTS table (ie because we use the keyword ‘references’
create table sales (
sku text references products (sku),
sale_date date,
count integer);
references; provides referential integrity - columns that are supposed to refer to each other are guaranteed to do so.
c = db.execute(“select * from links”)
for link_tuple in c:
link = Link(*link_tuple)
print link.votes
What does ‘Link(*link_tuple)’ do in the above code?
c = db.execute("select * from links")
# for link_tuple in c:
# link = Link(*link_tuple)
# print link.votes
What does ‘Link(*link_tuple)’ do in the above code?
‘Link’ is a tuple so using the * before it makes it into an object that we place in ‘link’. We can then use the dot notation to print the ‘votes’ for ‘link’.
How would you find roommates in this table?
This query is intended to find pairs of roommates in a table.
\+--------+----------+------+ | id | building | room | \+========+==========+======+ | 104131 | Dolliver | 14 | | 105540 | Kendrick | 3B | | 118199 | Kendrick | 1A | | 161282 | Dolliver | 7 | | 170267 | Dolliver | 1 | | 231742 | Kendrick | 3B | | 250841 | Kendrick | 2B |
(From Intro to Databases - Lesson 4: Video 10 ‘Self Joins’)
– This query is intended to find pairs of roommates in a table.
select a.id, b.id, a.building, a.room
from residences as a, residences as b
where a.building = b.building
and a.room = b.room
and a.id < b.id
order by a.building, a.room
note - the ‘and a.id < b.id’ is very important as it stops duplicates
---+--------+----------+------+ | id | id | building | room | \+========+========+==========+======+ | 413001 | 881256 | Crosby | 10 | | 496747 | 741532 | Crosby | 19 | | 612413 | 931027 | Crosby | 31 | | 170267 | 958827 | Dolliver | 1 | | 104131 | 707536 | Dolliver | 14 | | 477801 | 505241 | Dolliver | 8 | | 118199 | 824292 | Kendrick | 1A | | 105540 | 231742 | Kendrick | 3B | \+--------+--------+----------+------+
