PostgreSQL Flashcards
PostgreSQL
use Homebrew to start and stop the psql server
brew services start postgresql
brew services stop postgresql
brew services restart postgresql
PostgreSQL
create a new database from the command line
$ createdb dbname
PostgreSQL
delete a database from the command line
$ dropdb dbname
PostgreSQL
backup or export a database from the command line
$ pg_dump dbname > file.sql
PostgreSQL
restore a database from an sql file
$ psql -d dbname < file.sql
PostgreSQL
open an existing database from the command line
$ psql dbname
PostgreSQL
print a list of current databases
\l
\list
PostgreSQL
switch to a different database
\c dbname
PostgreSQL
list existing tables
\dt
PostgreSQL
create a new table in a database
CREATE TABLE
ex
CREATE TABLE users
( id serial,
username CHAR(25) NOT NULL,
enabled boolean DEFAULT TRUE,
PRIMARY KEY (id)
);
PostgreSQL
different data types you can choose for fields added with CREATE TABLE
serial
varchar
boolean
date
time
timestamp
int
decimal / numeric
PostgreSQL
constraints / restrictions that can be specified for columns in a PostgreSQL db
NOT NULL
NULL
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
PostgreSQL
list all the columns of the users table
\d users
PostgreSQL
add a new record to the users table
INSERT INTO
ex
INSERT INTO users (id, username, enabled)
VALUES (20, ‘Anthony Giuliano’, true);
PostgreSQL
list all the rows and columns from the users table
SELECT
ex
SELECT * FROM users;
PostgreSQL
clause to add criteria to a SELECT statement so you can filter the results
WHERE
ex
SELECT *
FROM users
WHERE id = 20;
PostgreSQL
clause to sort the results of a SELECT query
ORDER BY
ex
SELECT *
FROM users
ORDER BY id;
SELECT *
FROM users
ORDER BY id DESC;
SELECT *
FROM users
ORDER BY enabled, id DESC;
PostgreSQL
command to alter records in the users table
UPDATE
updates all records
UPDATE users
SET enabled = false;
updates a specific record specified by where clause UPDATE users
SET enabled = true
WHERE username = ‘John Smith’;
PostgreSQL
clause used with WHERE to compare a string to a pattern
LIKE
ex
% is a wildcard character
SELECT *
FROM users
WHERE username LIKE ‘%II’;
PostgreSQL
functions applied to a column in order to remove whitespace
trim()
rtrim()
ltrim()
ex
SELECT *
FROM users
WHERE rtrim(username) LIKE ‘% II’;
PostgreSQL
command to delete rows from a table
DELETE
ex
DELETE FROM users
WHERE id = 20;
PostgreSQL
command to change the schema of a table
ALTER TABLE
ex
ALTER TABLE users
ADD COLUMN last_login TIMESTAMP NOT NULL DEFAULT NOW();
PostgreSQL
function that returns the current date and time
NOW()
PostgreSQL
clause to change the name of an existing column
RENAME COLUMN TO
ex
ALTER TABLE users
RENAME COLUMN username TO full_name;
PostgreSQL
clause to change the data type of an existing column
ALTER COLUMN TYPE
ex
ALTER TABLE users
ALTER COLUMN full_name TYPE VARCHAR(25);
PostgreSQL
clause to remove a column from an existing table
DROP COLUMN
ex
ALTER TABLE users
DROP COLUMN enabled;
PostgreSQL
clause to rename an existing table in your database
RENAME TO
ex
ALTER TABLE users
RENAME TO all_users;
PostgreSQL
command to delete an entire table from your database
DROP TABLE
ex
DROP TABLE all_users;
PostgreSQL
ERD (entity relationship diagram)
a graphical representation of the tables in a database and the relationships between them.
PostgreSQL
set up a One to One relationship between two tables
The primary key of the first table is both the foreign key and the primary key of the second table.
ex
Users
CREATE TABLE users (
id serial,
username varchar not null,
primary key (id) );
Addresses
CREATE TABLE addresses (
user_id int not null,
city varchar not null,
state varchar not null,
primary key (user_id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) );
PostgreSQL
set up a One to Many relationship between two tables
The primary key of the ‘one’ table is the foreign key of the ‘many’ table.
ex
Books table
CREATE TABLE books (
id serial,
title varchar not null,
primary key (id) );
Reviews table
CREATE TABLE reviews (
id serial,
book_id int not null,
review_content varchar,
primary key (id),
foreign key (book_id) references books (id) on delete cascade );
PostgreSQL
an alias for NOW()
CURRENT_TIMESTAMP
PostgreSQL
set up a a Many to Many relationship between books and users
CREATE TABLE books_users (
user_id int not null,
book_id int not null,
checkout_date timestamp,
return_date timestamp,
primary key (user_id, book_id), – composite key
foreign key (user_id) references users (id) on update cascade,
foreign key (book_id) references books (id) on update cascade );
PostgreSQL
clause to ensure that child records in other tables are deleted
ON DELETE CASCADE
Add this clause when setting the foreign key field on the table
ex
CREATE TABLE reviews (
id serial,
book_id int not null,
user_id int not null,
review_content varchar,
rating int,
published_date timestamp default current_timestamp,
primary key (id),
foreign key (book_id) references books (id) on delete cascade,
foreign key (user_id) references users (id) on delete cascade );
PostgreSQL
syntax to make a column a foreign key
FOREIGN KEY (field) REFERENCES table (field)
ex
CREATE TABLE users_books (
book_id int NOT NULL,
FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE );
PostgreSQL
make a composite key on a join table
PRIMARY KEY (field1, field2)
ex
CREATE TABLE users_books (
book_id int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (book_id, user_id),
FOREIGN KEY (book_id) REFERENCES books (id) ON UPDATE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE );
PostgreSQL
set a unique restriction on a field
UNIQUE (field)
ex
CREATE TABLE users (
email varchar,
UNIQUE (email) );
PostgreSQL
inner join syntax
SELECT users.*, addresses.*
FROM users INNER JOIN addresses
ON users.id = addresses.user_id;
An inner join connects two tables based on the equality of one field from each table. It returns records where the two tables intersect (i.e. only records that exist in both tables).
PostgreSQL
left join syntax
SELECT users.*, addresses.*
FROM users LEFT JOIN addresses
ON users.id = addresses.user_id;
A left join returns all matching records in table A based on certain conditions. with columns from table A and B.
PostgreSQL
cross join syntax
SELECT users.*, addresses.*
FROM users CROSS JOIN addresses;
SELECT *
FROM users CROSS JOIN addresses;
A cross join, or cartensian join, returns all records from both tables. There is no ON clause.
PostgreSQL
right join syntax
SELECT users.*, addresses.*
FROM users RIGHT JOIN addresses
ON users.id = addresses.user_id;
A right join returns all matching records in table B based on certain conditions. with columns from table A and B.
PostgreSQL
export records to CSV
COPY (query)
TO ‘path’
WITH CSV HEADER;
ex
COPY
( SELECT users.*, addresses.* FROM users INNER JOIN addresses ON users.id = addresses.user_id )
TO /absolute/path/to/export.csv
WITH CSV HEADER;
PostgreSQL
the difference between COPY and \copy
COPY is server side and therefore requires absolute file paths;
\copy allows you to specify relative filepaths by initiating copies from the client. COPY is faster.
PostgreSQL
import records from CSV
COPY table_name (field1, field2, field3) FROM ‘path/to/data.csv’ WITH (DELIMITER ‘delim’, HEADER 1, FILEFORMAT CSV);
ex
COPY collections (advance_id, date_booked, amount, management_fee) FROM ‘/users/anthonygiuliano/Desktop/collections_import.csv’ WITH (HEADER 1, FORMAT CSV, DELIMITER ‘,’)
PostgreSQL
combine criteria in a WHERE clause
AND / OR
ex
SELECT *
FROM users
WHERE username = ‘John Smith’ OR username = ‘Jane Smiley’;
ex
SELECT *
FROM books
WHERE title LIKE ‘%My%’ AND author = ‘John Mayer’;
PostgreSQL
function that combines columns and / or strings together into one field
CONCAT
ex
SELECT CONCAT(title, ‘ ‘, ‘by’, ‘ ‘, author) AS “Books By”
FROM books;
PostgreSQL
alias a column name in your results
AS
ex
SELECT count(id) AS enabled_count
FROM users
WHERE enabled = true;
PostgreSQL
filter out duplicate rows in a query
DISTINCT
ex
SELECT DISTINCT u.username
FROM users u
LEFT JOIN users_books ub ON u.id = ub.user_id;
PostgreSQL
cap the number of records returned by a select statement
LIMIT / OFFSET
ex
SELECT *
FROM users
LIMIT 10 OFFSET 10;
PostgreSQL
clause used with WHERE to return records that don’t exist in another set of records
NOT IN
ex
SELECT u.username
FROM users u
WHERE u.id NOT IN
( SELECT ub.user_id FROM users_books ub );
PostgreSQL
function that returns the length of a string
length()
ex
SELECT * FROM books WHERE length(author) = 0;
PostgreSQL
functions that aggregate record count, minimum and maximum values
count()
min()
max()
PostgreSQL
functions that return the current date, time and timestamp
current_date()
current_time()
now()
PostgreSQL
use count to return the number of unique values in a column
COUNT(DISTINCT field)
ex
SELECT COUNT(DISTINCT description)
FROM transactions;
PostgreSQL
return the sums of various groups of records in one query
SUM, GROUP BY
ex
SELECT SUM(paid_out) AS paid_out_total, description
FROM transactions
GROUP BY description;
PostgreSQL
function to select subfields from date / time values
EXTRACT(SUBFIELD FROM date_field)
ex
SELECT SUM(paid_out), EXTRACT(MONTH FROM date)
FROM transactions
GROUP BY EXTRACT(MONTH FROM date);
PostgreSQL
function to convert various data types to formatted strings
TO_CHAR
ex
SELECT SUM(paid_out), TO_CHAR(date, ‘Month’)
FROM transactions
GROUP BY TO_CHAR(date, ‘Month’);
PostgreSQL
create a field whose value can be one of only a limited number of options
CREATE TYPE day AS ENUM(‘monday’, ‘tuesday’, ‘wednesday’, ‘thursday’, ‘friday’, ‘saturday’, ‘sunday’);
Then use like any other data type
CREATE TABLE messages(
id SERIAL,
day_of_week DAY DEFAULT NULL,
message VARCHAR NOT NULL,
PRIMARY KEY (id) );
PostgreSQL
three different kinds of INTEGER data types
SMALLINT: -32768 to 32767
INT: -2147483648 to 2147483647
BIGINT: -9223372036854775808 to 9223372036854775807
PostgreSQL
clause to ensure that an integer column accept only positive numbers
CHECK
CREATE TABLE people (
age SMALLINT CHECK (age > 0) );
PostgreSQL
create a column that contains decimals
NUMERIC / DECIMAL(total_digits, decimal_digits)
ex
CREATE TABLE decimal_values (
id SERIAL,
item VARCHAR NOT NULL,
value DECIMAL(13, 2) NOT NULL,
PRIMARY KEY (id) );
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.35);
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.355);
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.354);
SELECT * FROM decimal_values;
id | item | value
—-+———–+——-
1 | Test Item | 12.35
2 | Test Item | 12.36
3 | Test Item | 12.35
(3 rows)
PostgreSQL
create an index on a single column
CREATE INDEX ON table (field);
PostgreSQL
delete an existing index on a single column
DROP INDEX index_name
Get the index name by looking at \d table
PostgreSQL
brew services start postgresql
brew services stop postgresql
brew services restart postgresql
use Homebrew to start and stop the psql server
PostgreSQL
$ createdb dbname
create a new database from the command line
PostgreSQL
$ dropdb dbname
delete a database from the command line
PostgreSQL
$ pg_dump dbname > file.sql
backup or export a database from the command line
PostgreSQL
$ psql -d dbname < file.sql
restore a database from an sql file
PostgreSQL
$ psql dbname
open an existing database from the command line
PostgreSQL
\l
\list
print a list of current databases
PostgreSQL
\c dbname
switch to a different database
PostgreSQL
\dt
list existing tables
PostgreSQL
CREATE TABLE
ex
CREATE TABLE users
( id serial,
username CHAR(25) NOT NULL,
enabled boolean DEFAULT TRUE,
PRIMARY KEY (id)
);
create a new table in a database
PostgreSQL
serial
varchar
boolean
date
time
timestamp
int
decimal / numeric
different data types you can choose for fields added with CREATE TABLE
PostgreSQL
NOT NULL
NULL
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
constraints / restrictions that can be specified for columns in a PostgreSQL db
PostgreSQL
\d users
list all the columns of the users table
PostgreSQL
INSERT INTO
ex
INSERT INTO users (id, username, enabled)
VALUES (20, ‘Anthony Giuliano’, true);
add a new record to the users table
PostgreSQL
SELECT
ex
SELECT * FROM users;
list all the rows and columns from the users table
PostgreSQL
WHERE
ex
SELECT *
FROM users
WHERE id = 20;
clause to add criteria to a SELECT statement so you can filter the results
PostgreSQL
ORDER BY
ex
SELECT *
FROM users
ORDER BY id;
SELECT *
FROM users
ORDER BY id DESC;
SELECT *
FROM users
ORDER BY enabled, id DESC;
clause to sort the results of a SELECT query
PostgreSQL
UPDATE
updates all records
UPDATE users
SET enabled = false;
updates a specific record specified by where clause UPDATE users
SET enabled = true
WHERE username = ‘John Smith’;
command to alter records in the users table
PostgreSQL
LIKE
ex
% is a wildcard character
SELECT *
FROM users
WHERE username LIKE ‘%II’;
clause used with WHERE to compare a string to a pattern
PostgreSQL
trim()
rtrim()
ltrim()
ex
SELECT *
FROM users
WHERE rtrim(username) LIKE ‘% II’;
functions applied to a column in order to remove whitespace
PostgreSQL
DELETE
ex
DELETE FROM users
WHERE id = 20;
command to delete rows from a table
PostgreSQL
ALTER TABLE
ex
ALTER TABLE users
ADD COLUMN last_login TIMESTAMP NOT NULL DEFAULT NOW();
command to change the schema of a table
PostgreSQL
NOW()
function that returns the current date and time
PostgreSQL
RENAME COLUMN TO
ex
ALTER TABLE users
RENAME COLUMN username TO full_name;
clause to change the name of an existing column
PostgreSQL
ALTER COLUMN TYPE
ex
ALTER TABLE users
ALTER COLUMN full_name TYPE VARCHAR(25);
clause to change the data type of an existing column
PostgreSQL
DROP COLUMN
ex
ALTER TABLE users
DROP COLUMN enabled;
clause to remove a column from an existing table
PostgreSQL
RENAME TO
ex
ALTER TABLE users
RENAME TO all_users;
clause to rename an existing table in your database
PostgreSQL
DROP TABLE
ex
DROP TABLE all_users;
command to delete an entire table from your database
PostgreSQL
a graphical representation of the tables in a database and the relationships between them.
ERD (entity relationship diagram)
PostgreSQL
The primary key of the first table is both the foreign key and the primary key of the second table.
ex
Users
CREATE TABLE users (
id serial,
username varchar not null,
primary key (id) );
Addresses
CREATE TABLE addresses (
user_id int not null,
city varchar not null,
state varchar not null,
primary key (user_id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) );
set up a One to One relationship between two tables
PostgreSQL
The primary key of the ‘one’ table is the foreign key of the ‘many’ table.
ex
Books table
CREATE TABLE books (
id serial,
title varchar not null,
primary key (id) );
Reviews table
CREATE TABLE reviews (
id serial,
book_id int not null,
review_content varchar,
primary key (id),
foreign key (book_id) references books (id) on delete cascade );
set up a One to Many relationship between two tables
PostgreSQL
CURRENT_TIMESTAMP
an alias for NOW()
PostgreSQL
CREATE TABLE books_users (
user_id int not null,
book_id int not null,
checkout_date timestamp,
return_date timestamp,
primary key (user_id, book_id), – composite key
foreign key (user_id) references users (id) on update cascade,
foreign key (book_id) references books (id) on update cascade );
set up a a Many to Many relationship between books and users
PostgreSQL
ON DELETE CASCADE
Add this clause when setting the foreign key field on the table
ex
CREATE TABLE reviews (
id serial,
book_id int not null,
user_id int not null,
review_content varchar,
rating int,
published_date timestamp default current_timestamp,
primary key (id),
foreign key (book_id) references books (id) on delete cascade,
foreign key (user_id) references users (id) on delete cascade );
clause to ensure that child records in other tables are deleted
PostgreSQL
FOREIGN KEY (field) REFERENCES table (field)
ex
CREATE TABLE users_books (
book_id int NOT NULL,
FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE );
syntax to make a column a foreign key
PostgreSQL
PRIMARY KEY (field1, field2)
ex
CREATE TABLE users_books (
book_id int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (book_id, user_id),
FOREIGN KEY (book_id) REFERENCES books (id) ON UPDATE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE );
make a composite key on a join table
PostgreSQL
UNIQUE (field)
ex
CREATE TABLE users (
email varchar,
UNIQUE (email) );
set a unique restriction on a field
PostgreSQL
SELECT users.*, addresses.*
FROM users INNER JOIN addresses
ON users.id = addresses.user_id;
An inner join connects two tables based on the equality of one field from each table. It returns records where the two tables intersect (i.e. only records that exist in both tables).
inner join syntax
PostgreSQL
SELECT users.*, addresses.*
FROM users LEFT JOIN addresses
ON users.id = addresses.user_id;
A left join returns all matching records in table A based on certain conditions. with columns from table A and B.
left join syntax
PostgreSQL
SELECT users.*, addresses.*
FROM users CROSS JOIN addresses;
SELECT *
FROM users CROSS JOIN addresses;
A cross join, or cartensian join, returns all records from both tables. There is no ON clause.
cross join syntax
PostgreSQL
SELECT users.*, addresses.*
FROM users RIGHT JOIN addresses
ON users.id = addresses.user_id;
A right join returns all matching records in table B based on certain conditions. with columns from table A and B.
right join syntax
PostgreSQL
COPY (query)
TO ‘path’
WITH CSV HEADER;
ex
COPY
( SELECT users.*, addresses.* FROM users INNER JOIN addresses ON users.id = addresses.user_id )
TO /absolute/path/to/export.csv
WITH CSV HEADER;
export records to CSV
PostgreSQL
COPY is server side and therefore requires absolute file paths;
\copy allows you to specify relative filepaths by initiating copies from the client. COPY is faster.
the difference between COPY and \copy
PostgreSQL
COPY table_name (field1, field2, field3) FROM ‘path/to/data.csv’ WITH (DELIMITER ‘delim’, HEADER 1, FILEFORMAT CSV);
ex
COPY collections (advance_id, date_booked, amount, management_fee) FROM ‘/users/anthonygiuliano/Desktop/collections_import.csv’ WITH (HEADER 1, FORMAT CSV, DELIMITER ‘,’)
import records from CSV
PostgreSQL
AND / OR
ex
SELECT *
FROM users
WHERE username = ‘John Smith’ OR username = ‘Jane Smiley’;
ex
SELECT *
FROM books
WHERE title LIKE ‘%My%’ AND author = ‘John Mayer’;
combine criteria in a WHERE clause
PostgreSQL
CONCAT
ex
SELECT CONCAT(title, ‘ ‘, ‘by’, ‘ ‘, author) AS “Books By”
FROM books;
function that combines columns and / or strings together into one field
PostgreSQL
AS
ex
SELECT count(id) AS enabled_count
FROM users
WHERE enabled = true;
alias a column name in your results
PostgreSQL
DISTINCT
ex
SELECT DISTINCT u.username
FROM users u
LEFT JOIN users_books ub ON u.id = ub.user_id;
filter out duplicate rows in a query
PostgreSQL
LIMIT / OFFSET
ex
SELECT *
FROM users
LIMIT 10 OFFSET 10;
cap the number of records returned by a select statement
PostgreSQL
NOT IN
ex
SELECT u.username
FROM users u
WHERE u.id NOT IN
( SELECT ub.user_id FROM users_books ub );
clause used with WHERE to return records that don’t exist in another set of records
PostgreSQL
length()
ex
SELECT * FROM books WHERE length(author) = 0;
function that returns the length of a string
PostgreSQL
count()
min()
max()
functions that aggregate record count, minimum and maximum values
PostgreSQL
current_date()
current_time()
now()
functions that return the current date, time and timestamp
PostgreSQL
COUNT(DISTINCT field)
ex
SELECT COUNT(DISTINCT description)
FROM transactions;
use count to return the number of unique values in a column
PostgreSQL
SUM, GROUP BY
ex
SELECT SUM(paid_out) AS paid_out_total, description
FROM transactions
GROUP BY description;
return the sums of various groups of records in one query
PostgreSQL
EXTRACT(SUBFIELD FROM date_field)
ex
SELECT SUM(paid_out), EXTRACT(MONTH FROM date)
FROM transactions
GROUP BY EXTRACT(MONTH FROM date);
function to select subfields from date / time values
PostgreSQL
TO_CHAR
ex
SELECT SUM(paid_out), TO_CHAR(date, ‘Month’)
FROM transactions
GROUP BY TO_CHAR(date, ‘Month’);
function to convert various data types to formatted strings
PostgreSQL
CREATE TYPE day AS ENUM(‘monday’, ‘tuesday’, ‘wednesday’, ‘thursday’, ‘friday’, ‘saturday’, ‘sunday’);
Then use like any other data type
CREATE TABLE messages(
id SERIAL,
day_of_week DAY DEFAULT NULL,
message VARCHAR NOT NULL,
PRIMARY KEY (id) );
create a field whose value can be one of only a limited number of options
PostgreSQL
SMALLINT: -32768 to 32767
INT: -2147483648 to 2147483647
BIGINT: -9223372036854775808 to 9223372036854775807
three different kinds of INTEGER data types
PostgreSQL
CHECK
CREATE TABLE people (
age SMALLINT CHECK (age > 0) );
clause to ensure that an integer column accept only positive numbers
PostgreSQL
NUMERIC / DECIMAL(total_digits, decimal_digits)
ex
CREATE TABLE decimal_values (
id SERIAL,
item VARCHAR NOT NULL,
value DECIMAL(13, 2) NOT NULL,
PRIMARY KEY (id) );
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.35);
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.355);
INSERT INTO decimal_values (item, value)
VALUES (‘Test Item’, 12.354);
SELECT * FROM decimal_values;
id | item | value
—-+———–+——-
1 | Test Item | 12.35
2 | Test Item | 12.36
3 | Test Item | 12.35
(3 rows)
create a column that contains decimals
PostgreSQL
CREATE INDEX ON table (field);
create an index on a single column
PostgreSQL
DROP INDEX index_name
Get the index name by looking at \d table
delete an existing index on a single column
PostgreSQL
What kind of programming language is SQL?
SQL is a “special purpose language”, since it’s typically only used to interact with relational databases
PostgreSQL
What are the three sublanguages of SQL?
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Langugage (DCL)
PostgreSQL
What function returns a lowercased version of a string?
lower
ex
SELECT lower(‘AlPhAbEt’);
PostgreSQL
How does the psql console display true and false values?
t and f
PostgreSQL
Use SQL to compute the surface area of a sphere with a radius of 26.3cm, truncated to return an integer
SELECT trunc(4 * pi() * 26.3 ^ 2);
PostgreSQL
SQL is a “special purpose language”, since it’s typically only used to interact with relational databases
What kind of programming language is SQL?
PostgreSQL
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Langugage (DCL)
What are the three sublanguages of SQL?
PostgreSQL
lower
ex
SELECT lower(‘AlPhAbEt’);
What function returns a lowercased version of a string?
PostgreSQL
t and f
How does the psql console display true and false values?
PostgreSQL
SELECT trunc(4 * pi() * 26.3 ^ 2);
Use SQL to compute the surface area of a sphere with a radius of 26.3cm, truncated to return an integer
PostgreSQL
add a constraint to an existing table
ALTER TABLE ADD
ex
ALTER TABLE films
ADD CHECK(year > 0);
PostgreSQL
ALTER TABLE ADD
ex
ALTER TABLE films
ADD CHECK(year > 0);
add a constraint to an existing table
PostgreSQL
create a new sequence called ‘counter’
CREATE SEQUENCE counter;
PostgreSQL
return the next value from the sequence called ‘counter’
SELECT nextval(‘counter’);
PostgreSQL
delete the sequence called ‘counter’
DROP SEQUENCE counter;
PostgreSQL
CREATE SEQUENCE counter;
create a new sequence called ‘counter’
PostgreSQL
SELECT nextval(‘counter’);
return the next value from the sequence called ‘counter’
PostgreSQL
DROP SEQUENCE counter;
delete the sequence called ‘counter’
PostgreSQL
create a sequence called ‘even_counter’ that starts at 2 and increments by 2
CREATE SEQUENCE even_counter
INCREMENT BY 2
MINVALUE 2;
PostgreSQL
CREATE SEQUENCE even_counter
INCREMENT BY 2
MINVALUE 2;
create a sequence called ‘even_counter’ that starts at 2 and increments by 2
PostgreSQL
aggregate function that works like ruby’s Array#join
string_agg(field, delimiter)
ex
select (year / 10 * 10) as decade, genre, string_agg(title, ‘, ‘) as films from films group by decade, genre order by decade, genre;
PostgreSQL
string_agg(field, delimiter)
ex
select (year / 10 * 10) as decade, genre, string_agg(title, ‘, ‘) as films from films group by decade, genre order by decade, genre;
aggregate function that works like ruby’s Array#join
PostgreSQL
special value that represents the absence of any other value
NULL
PostgreSQL
NULL
special value that represents the absence of any other value
PostgreSQL
high level design focused on identifying entities and their relationships
conceptual schema
PostgreSQL
low level database specific design focused on implementation
physical schema
PostgreSQL
conceptual schema
high level design focused on identifying entities and their relationships
PostgreSQL
physical schema
low level database specific design focused on implementation
PostgreSQL
the number of objects on each side of a relationship (1:1, 1:M, M:M)
cardinality
PostgreSQL
cardinality
the number of objects on each side of a relationship (1:1, 1:M, M:M)
PostgreSQL
whether the relationship is required (1) or optional (0)
modality
PostgreSQL
modality
whether the relationship is required (1) or optional (0)
PostgreSQL
a constraint that enforces certain rules about what values are permitted in a foreign key field
foreign key constraint
PostgreSQL
foreign key constraint
a constraint that enforces certain rules about what values are permitted in a foreign key field
PostgreSQL
create a foreign key column
create a column of the same type as the primary key column it will point to, followed by a REFERENCES constraint
ex
CREATE TABLE orders (
id serial primary key,
product id int REFERENCES products (id) );
PostgreSQL
create a column of the same type as the primary key column it will point to, followed by a REFERENCES constraint
ex
CREATE TABLE orders (
id serial primary key,
product id int REFERENCES products (id) );
create a foreign key column
add a foreign key constraint on an existing field
ALTER TABLE table_name
ADD FOREIGN KEY (field_name)
REFERENCES other_table (field_name);
ex
alter table addresses add foreign key (user_id) references users (id);
PostgreSQL
add a not null constraint to an existing column called product_id on the orders table
ALTER TABLE orders
ALTER COLUMN product_id
SET not null;
PostgreSQL
true or false: a foreign key constrain prevents that column from having NULL values
false; it is common to include NOT NULL and FOREIGN KEY constraints together
PostgreSQL
a situation where the database becomes inconsistent, so it contains more than one answer for a given question
update anomoly
PostgreSQL
update anomoly
a situation where the database becomes inconsistent, so it contains more than one answer for a given question
PostgreSQL
a situation where the schema can’t store the information for an entity without having an entry in a related entity
insertion anomaly
PostgreSQL
insertion anomaly
a situation where the schema can’t store the information for an entity without having an entry in a related entity
PostgreSQL
a situation where we lose all the information about an entity if we delete records from a related entity
deletion anomaly
PostgreSQL
deletion anomaly
a situation where we lose all the information about an entity if we delete records from a related entity
PostgreSQL
the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies
normalization
PostgreSQL
normalization
the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies
PostgreSQL
clause used with WHERE to compare a string to a pattern, case insensitive
ILIKE
ex
% is a wildcard character
SELECT *
FROM users
WHERE username ILIKE ‘%II’;
PostgreSQL
ILIKE
ex
% is a wildcard character
SELECT *
FROM users
WHERE username ILIKE ‘%II’;
clause used with WHERE to compare a string to a pattern, case insensitive
