Chapter 15: Python and Databases

Question 1

One of the values within a tuple. More commonly called a “column” or “field”.

Answer 1

attribute

Question 2

When we tell the database to enforce a rule on a field or a row in a table. A common one is to insist that there can be no duplicate values in a particular field (i.e., all the values must be unique).

Answer 2

constraint

Question 3

Allows you to execute SQL commands in a database and retrieve data from the database. Similar to a socket or file handle for network connections and files, respectively.

Answer 3

cursor

Question 4

A piece of software that allows you to directly connect to a database and manipulate the database directly without writing a program.

Answer 4

database browser

Question 5

A numeric key that points to the primary key of a row in another table. Establish relationships between rows stored in different tables.

Answer 5

foreign key

eg, artist_id (foreign key) in Track table links to id (primary key) in Artist table

Question 6

Additional data that the database software maintains as rows and inserts into a table to make lookups very fast.

Answer 6

index

Question 7

A key that the “outside world” uses to look up a particular row
eg. in a table of user accounts, a person’s email address might be a good candidate as one for the user’s data

Answer 7

logical key

Question 8

Designing a data model so that no data is replicated. We store each item of data at one place in the database and reference it elsewhere using a foreign key.

Answer 8

normalization

Question 9

A numeric key assigned to each row that is used to refer to rows in another table. Often the database is configured to automatically assign them as rows are inserted.

Answer 9

primary key

eg. id (primary key) in Artist table links to artist_id (foreign key) in Track table

Question 10

An area within a database that contains tuples and attributes. More typically called a “table”.

Answer 10

relation

Question 11

A single entry in a database table that is a set of attributes. More typically called “row”.

Answer 11

tuple

Question 12

SQL command that deletes the table and all of its contents from the database.
Can’t be undone

Answer 12

DROP TABLE
DROP TABLE IF EXISTS

Question 13

method to perform commands on database once cursor is implemented

Answer 13

.execute()
cur.execute(‘SQL KEYWORD table’)

Question 14

SQL command to make a new table

Answer 14

cur.execute(‘CREATE TABLE table (column TYPE)’)

Question 15

SQL command to query info from a database

Answer 15

SELECT

Question 16

SQL keyword to rename column or table

Answer 16

AS

Question 17

return unique values

Answer 17

DISTINCT

Question 18

filter results based on specified condition

Answer 18

WHERE

Question 19

use with WHERE
match closely matching names using wildcard characters

Answer 19

LIKE

Question 20

use with WHERE
return results within a range.
Numbers: includes 2nd condition
Text: includes first specified character in 2nd condition

Answer 20

BETWEEN

Question 21

use with WHERE
meets all conditions

Answer 21

AND

Question 22

use with WHERE
meets any condition

Answer 22

OR

Question 23

sorts results
ASC: default
DESC: high to low, Z-A

Answer 23

ORDER BY

Question 24

specifies number of rows to return
always at end of query

Answer 24

LIMIT

Question 25

if-then conditional CASE WHEN ... THEN ... ELSE ... END

Answer 25

CASE

Question 26

creates a new table

Answer 26

CREATE TABLE

Question 27

adds a new row to a table

Answer 27

INSERT INTO INSERT INTO table (columns)

Question 28

edits table column(s)

Answer 28

ALTER TABLE

Question 29

edits table row(s)

Answer 29

UPDATE

Question 30

removes rows from a table needs WHERE clause to specify which rows

Answer 30

DELETE FROM

Question 31

specifies how column can be used eg. data types

Answer 31

constraints

Question 32

counts the number of rows

Answer 32

COUNT()

Question 33

sum of the values in a column

Answer 33

SUM()

Question 34

the largest / smallest value

Answer 34

MAX() / MIN()

Question 35

the average of the values in a column

Answer 35

AVG()

Question 36

round the values in a column

Answer 36

ROUND()

Question 37

used with SELECT to arrange identical data into groups WHERE **answer** ORDER BY LIMIT

Answer 37

GROUP BY

Question 38

use to filter groups GROUP BY **answer** ORDER BY LIMIT

Answer 38

HAVING

Question 39

SQL keyword specifying row data to be inserted into table

Answer 39

VALUES 'INSERT INTO table (column1, column2) VALUES (?, ?)' , (values tuple)

Question 40

SQL method that forces data to be written to the database file after editing

Answer 40

commit() eg. conn.commit()

Question 41

SQL keyword used with UPDATE command to change data in table Similar to find and replace.

Answer 41

SET UPDATE table SET column = value

Question 42

CRUD for INSERT INTO, SELECT, UPDATE, and DELETE FROM commands

Answer 42

Create Read Update Delete

Question 43

SQL clause to extract data from multiple tables and combine. Used with ON condition to define how data is to be connected

Answer 43

JOIN SELECT title, plays, name, eyes FROM Track **JOIN** Artist ON Track.artist_id = Artist.id Joins artist data and track data

Question 44

SQL command to automatically create primary keys

Answer 44

PRIMARY KEY (no value) CREATE TABLE table (id INTEGER PRIMARY KEY, other columns)

Question 45

SQL method to check last automatically inserted primary key

Answer 45

SELECT last_insert_rowid()

Question 46

SQL command to mark a logical key (text column) that tells the program to store extra information to look up that column faster

Answer 46

CREATE INDEX index_name ON table (column)

Question 47

SQL constraint keyword to ensure no duplicates

Answer 47

UNIQUE CREATE UNIQUE INDEX

Question 48

SQL command to insert a new record if the name is not already there

Answer 48

INSERT OR IGNORE INTO table (columns)

Question 49

An intermediate table that functions as the "many" end in a many-to-one relationship between two other tables

Answer 49

junction / through / connector / join table CREATE TABLE Member ( user_id INTEGER, course_id INTEGER, **PRIMARY KEY (user_id, course_id)**)