PostgreSQL Flashcards
1
Q
PostgreSQL
use Homebrew to start and stop the psql server
A
brew services start postgresql
brew services stop postgresql
brew services restart postgresql
2
Q
PostgreSQL
create a new database from the command line
A
$ createdb dbname
3
Q
PostgreSQL
delete a database from the command line
A
$ dropdb dbname
4
Q
PostgreSQL
backup or export a database from the command line
A
$ pg_dump dbname > file.sql
5
Q
PostgreSQL
restore a database from an sql file
A
$ psql -d dbname < file.sql
6
Q
PostgreSQL
open an existing database from the command line
A
$ psql dbname
7
Q
PostgreSQL
print a list of current databases
A
\l
\list
8
Q
PostgreSQL
switch to a different database
A
\c dbname
9
Q
PostgreSQL
list existing tables
A
\dt
10
Q
PostgreSQL
create a new table in a database
A
CREATE TABLE
ex
CREATE TABLE users
( id serial,
username CHAR(25) NOT NULL,
enabled boolean DEFAULT TRUE,
PRIMARY KEY (id)
);
11
Q
PostgreSQL
different data types you can choose for fields added with CREATE TABLE
A
serial
varchar
boolean
date
time
timestamp
int
decimal / numeric
12
Q
PostgreSQL
constraints / restrictions that can be specified for columns in a PostgreSQL db
A
NOT NULL
NULL
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
13
Q
PostgreSQL
list all the columns of the users table
A
\d users
14
Q
PostgreSQL
add a new record to the users table
A
INSERT INTO
ex
INSERT INTO users (id, username, enabled)
VALUES (20, ‘Anthony Giuliano’, true);
15
Q
PostgreSQL
list all the rows and columns from the users table
A
SELECT
ex
SELECT * FROM users;
16
Q
PostgreSQL
clause to add criteria to a SELECT statement so you can filter the results
A
WHERE
ex
SELECT *
FROM users
WHERE id = 20;
17
Q
PostgreSQL
clause to sort the results of a SELECT query
A
ORDER BY
ex
SELECT *
FROM users
ORDER BY id;
SELECT *
FROM users
ORDER BY id DESC;
SELECT *
FROM users
ORDER BY enabled, id DESC;
18
Q
PostgreSQL
command to alter records in the users table
A
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’;
19
Q
PostgreSQL
clause used with WHERE to compare a string to a pattern
A
LIKE
ex
% is a wildcard character
SELECT *
FROM users
WHERE username LIKE ‘%II’;
20
Q
PostgreSQL
functions applied to a column in order to remove whitespace
A
trim()
rtrim()
ltrim()
ex
SELECT *
FROM users
WHERE rtrim(username) LIKE ‘% II’;
21
Q
PostgreSQL
command to delete rows from a table
A
DELETE
ex
DELETE FROM users
WHERE id = 20;
22
Q
PostgreSQL
command to change the schema of a table
A
ALTER TABLE
ex
ALTER TABLE users
ADD COLUMN last_login TIMESTAMP NOT NULL DEFAULT NOW();
23
Q
PostgreSQL
function that returns the current date and time
A
NOW()
24
Q
PostgreSQL
clause to change the name of an existing column
A
RENAME COLUMN TO
ex
ALTER TABLE users
RENAME COLUMN username TO full_name;
25
# 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);
26
# PostgreSQL
clause to remove a column from an existing table
DROP COLUMN
ex
ALTER TABLE users
DROP COLUMN enabled;
27
# PostgreSQL
clause to rename an existing table in your database
RENAME TO
ex
ALTER TABLE users
RENAME TO all\_users;
28
# PostgreSQL
command to delete an entire table from your database
DROP TABLE
ex
DROP TABLE all\_users;
29
# PostgreSQL
ERD (entity relationship diagram)
a graphical representation of the tables in a database and the relationships between them.
30
# 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) );
31
# 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 );
32
# PostgreSQL
an alias for NOW()
CURRENT\_TIMESTAMP
33
# 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 );
34
# 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 );
35
# 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 );
36
# 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 );
37
# PostgreSQL
set a `unique` restriction on a field
UNIQUE (field)
ex
CREATE TABLE users (
email varchar,
UNIQUE (email) );
38
# 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).
39
# 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.
40
# 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.
41
# 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.
42
# 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;
43
# 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.
44
# 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 ',')
45
# 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';
46
# PostgreSQL
function that combines columns and / or strings together into one field
CONCAT
ex
SELECT CONCAT(title, ' ', 'by', ' ', author) AS "Books By"
FROM books;
47
# PostgreSQL
alias a column name in your results
AS
ex
SELECT count(id) AS enabled\_count
FROM users
WHERE enabled = true;
48
# 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;
49
# PostgreSQL
cap the number of records returned by a select statement
LIMIT / OFFSET
ex
SELECT \*
FROM users
LIMIT 10 OFFSET 10;
50
# 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 );
51
# PostgreSQL
function that returns the length of a string
length()
ex
SELECT \* FROM books WHERE length(author) = 0;
52
# PostgreSQL
functions that aggregate record count, minimum and maximum values
count()
min()
max()
53
# PostgreSQL
functions that return the current date, time and timestamp
current\_date()
current\_time()
now()
54
# PostgreSQL
use `count` to return the number of unique values in a column
COUNT(DISTINCT field)
ex
SELECT COUNT(DISTINCT description)
FROM transactions;
55
# 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;
56
# 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);
57
# 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');
58
# 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) );
59
# PostgreSQL
three different kinds of INTEGER data types
SMALLINT: -32768 to 32767
INT: -2147483648 to 2147483647
BIGINT: -9223372036854775808 to 9223372036854775807
60
# PostgreSQL
clause to ensure that an integer column accept only positive numbers
CHECK
CREATE TABLE people (
age SMALLINT CHECK (age \> 0) );
61
# 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)
62
# PostgreSQL
create an index on a single column
CREATE INDEX ON table (field);
63
# PostgreSQL
delete an existing index on a single column
DROP INDEX index\_name
Get the index name by looking at `\d table`
64
# PostgreSQL
brew services start postgresql
brew services stop postgresql
brew services restart postgresql
use Homebrew to start and stop the psql server
65
# PostgreSQL
$ createdb dbname
create a new database from the command line
66
# PostgreSQL
$ dropdb dbname
delete a database from the command line
67
# PostgreSQL
$ pg\_dump dbname \> file.sql
backup or export a database from the command line
68
# PostgreSQL
$ psql -d dbname \< file.sql
restore a database from an sql file
69
# PostgreSQL
$ psql dbname
open an existing database from the command line
70
# PostgreSQL
\l
\list
print a list of current databases
71
# PostgreSQL
\c dbname
switch to a different database
72
# PostgreSQL
\dt
list existing tables
73
# 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
74
# PostgreSQL
serial
varchar
boolean
date
time
timestamp
int
decimal / numeric
different data types you can choose for fields added with CREATE TABLE
75
# PostgreSQL
NOT NULL
NULL
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
constraints / restrictions that can be specified for columns in a PostgreSQL db
76
# PostgreSQL
\d users
list all the columns of the `users` table
77
# PostgreSQL
INSERT INTO
ex
INSERT INTO users (id, username, enabled)
VALUES (20, 'Anthony Giuliano', true);
add a new record to the `users` table
78
# PostgreSQL
SELECT
ex
SELECT \* FROM users;
list all the rows and columns from the `users` table
79
# PostgreSQL
WHERE
ex
SELECT \*
FROM users
WHERE id = 20;
clause to add criteria to a SELECT statement so you can filter the results
80
# 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
81
# 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
82
# 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
83
# PostgreSQL
trim()
rtrim()
ltrim()
ex
SELECT \*
FROM users
WHERE rtrim(username) LIKE '% II';
functions applied to a column in order to remove whitespace
84
# PostgreSQL
DELETE
ex
DELETE FROM users
WHERE id = 20;
command to delete rows from a table
85
# PostgreSQL
ALTER TABLE
ex
ALTER TABLE users
ADD COLUMN last\_login TIMESTAMP NOT NULL DEFAULT NOW();
command to change the schema of a table
86
# PostgreSQL
NOW()
function that returns the current date and time
87
# PostgreSQL
RENAME COLUMN TO
ex
ALTER TABLE users
RENAME COLUMN username TO full\_name;
clause to change the name of an existing column
88
# 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
89
# PostgreSQL
DROP COLUMN
ex
ALTER TABLE users
DROP COLUMN enabled;
clause to remove a column from an existing table
90
# PostgreSQL
RENAME TO
ex
ALTER TABLE users
RENAME TO all\_users;
clause to rename an existing table in your database
91
# PostgreSQL
DROP TABLE
ex
DROP TABLE all\_users;
command to delete an entire table from your database
92
# PostgreSQL
a graphical representation of the tables in a database and the relationships between them.
ERD (entity relationship diagram)
93
# 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
94
# 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
95
# PostgreSQL
CURRENT\_TIMESTAMP
an alias for NOW()
96
# 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
97
# 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
98
# 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
99
# 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
100
# PostgreSQL
UNIQUE (field)
ex
CREATE TABLE users (
email varchar,
UNIQUE (email) );
set a `unique` restriction on a field
101
# 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
102
# 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
103
# 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
104
# 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
105
# 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
106
# 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
107
# 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
108
# 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
109
# PostgreSQL
CONCAT
ex
SELECT CONCAT(title, ' ', 'by', ' ', author) AS "Books By"
FROM books;
function that combines columns and / or strings together into one field
110
# PostgreSQL
AS
ex
SELECT count(id) AS enabled\_count
FROM users
WHERE enabled = true;
alias a column name in your results
111
# 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
112
# PostgreSQL
LIMIT / OFFSET
ex
SELECT \*
FROM users
LIMIT 10 OFFSET 10;
cap the number of records returned by a select statement
113
# 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
114
# PostgreSQL
length()
ex
SELECT \* FROM books WHERE length(author) = 0;
function that returns the length of a string
115
# PostgreSQL
count()
min()
max()
functions that aggregate record count, minimum and maximum values
116
# PostgreSQL
current\_date()
current\_time()
now()
functions that return the current date, time and timestamp
117
# PostgreSQL
COUNT(DISTINCT field)
ex
SELECT COUNT(DISTINCT description)
FROM transactions;
use `count` to return the number of unique values in a column
118
# 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
119
# 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
120
# 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
121
# 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
122
# PostgreSQL
SMALLINT: -32768 to 32767
INT: -2147483648 to 2147483647
BIGINT: -9223372036854775808 to 9223372036854775807
three different kinds of INTEGER data types
123
# PostgreSQL
CHECK
CREATE TABLE people (
age SMALLINT CHECK (age \> 0) );
clause to ensure that an integer column accept only positive numbers
124
# 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
125
# PostgreSQL
CREATE INDEX ON table (field);
create an index on a single column
126
# PostgreSQL
DROP INDEX index\_name
Get the index name by looking at `\d table`
delete an existing index on a single column
127
# 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
128
# PostgreSQL
What are the three sublanguages of SQL?
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Langugage (DCL)
129
# PostgreSQL
What function returns a lowercased version of a string?
lower
ex
SELECT lower('AlPhAbEt');
130
# PostgreSQL
How does the psql console display true and false values?
t and f
131
# 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);
132
# 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?
133
# PostgreSQL
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Langugage (DCL)
What are the three sublanguages of SQL?
134
# PostgreSQL
lower
ex
SELECT lower('AlPhAbEt');
What function returns a lowercased version of a string?
135
# PostgreSQL
t and f
How does the psql console display true and false values?
136
# 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
137
# PostgreSQL
add a constraint to an existing table
ALTER TABLE ADD
ex
ALTER TABLE films
ADD CHECK(year \> 0);
138
# PostgreSQL
ALTER TABLE ADD
ex
ALTER TABLE films
ADD CHECK(year \> 0);
add a constraint to an existing table
139
# PostgreSQL
create a new sequence called 'counter'
CREATE SEQUENCE counter;
140
# PostgreSQL
return the next value from the sequence called 'counter'
SELECT nextval('counter');
141
# PostgreSQL
delete the sequence called 'counter'
DROP SEQUENCE counter;
142
# PostgreSQL
CREATE SEQUENCE counter;
create a new sequence called 'counter'
143
# PostgreSQL
SELECT nextval('counter');
return the next value from the sequence called 'counter'
144
# PostgreSQL
DROP SEQUENCE counter;
delete the sequence called 'counter'
145
# PostgreSQL
create a sequence called 'even\_counter' that starts at 2 and increments by 2
CREATE SEQUENCE even\_counter
INCREMENT BY 2
MINVALUE 2;
146
# PostgreSQL
CREATE SEQUENCE even\_counter
INCREMENT BY 2
MINVALUE 2;
create a sequence called 'even\_counter' that starts at 2 and increments by 2
147
# 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;
148
# 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
149
# PostgreSQL
special value that represents the absence of any other value
NULL
150
# PostgreSQL
NULL
special value that represents the absence of any other value
151
# PostgreSQL
high level design focused on identifying entities and their relationships
conceptual schema
152
# PostgreSQL
low level database specific design focused on implementation
physical schema
153
# PostgreSQL
conceptual schema
high level design focused on identifying entities and their relationships
154
# PostgreSQL
physical schema
low level database specific design focused on implementation
155
# PostgreSQL
the number of objects on each side of a relationship (1:1, 1:M, M:M)
cardinality
156
# PostgreSQL
cardinality
the number of objects on each side of a relationship (1:1, 1:M, M:M)
157
# PostgreSQL
whether the relationship is required (1) or optional (0)
modality
158
# PostgreSQL
modality
whether the relationship is required (1) or optional (0)
159
# PostgreSQL
a constraint that enforces certain rules about what values are permitted in a foreign key field
foreign key constraint
160
# PostgreSQL
foreign key constraint
a constraint that enforces certain rules about what values are permitted in a foreign key field
161
# 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) );
162
# 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
163
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);
164
# 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;
165
# 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
166
# PostgreSQL
a situation where the database becomes inconsistent, so it contains more than one answer for a given question
update anomoly
167
# PostgreSQL
update anomoly
a situation where the database becomes inconsistent, so it contains more than one answer for a given question
168
# PostgreSQL
a situation where the schema can't store the information for an entity without having an entry in a related entity
insertion anomaly
169
# PostgreSQL
insertion anomaly
a situation where the schema can't store the information for an entity without having an entry in a related entity
170
# PostgreSQL
a situation where we lose all the information about an entity if we delete records from a related entity
deletion anomaly
171
# PostgreSQL
deletion anomaly
a situation where we lose all the information about an entity if we delete records from a related entity
172
# PostgreSQL
the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies
normalization
173
# PostgreSQL
normalization
the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies
174
# 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';
175
# 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
