PostgreSQL

Question 1

PostgreSQL

use Homebrew to start and stop the psql server

Answer 1

brew services start postgresql

brew services stop postgresql

brew services restart postgresql

Question 2

PostgreSQL

create a new database from the command line

Answer 2

$ createdb dbname

Question 3

PostgreSQL

delete a database from the command line

Answer 3

$ dropdb dbname

Question 4

PostgreSQL

backup or export a database from the command line

Answer 4

$ pg_dump dbname > file.sql

Question 5

PostgreSQL

restore a database from an sql file

Answer 5

$ psql -d dbname < file.sql

Question 6

PostgreSQL

open an existing database from the command line

Answer 6

$ psql dbname

Question 7

PostgreSQL

print a list of current databases

Answer 7

\l

\list

Question 8

PostgreSQL

switch to a different database

Answer 8

\c dbname

Question 9

PostgreSQL

list existing tables

Answer 9

\dt

Question 10

PostgreSQL

create a new table in a database

Answer 10

CREATE TABLE

ex

CREATE TABLE users

( id serial,

username CHAR(25) NOT NULL,

enabled boolean DEFAULT TRUE,

PRIMARY KEY (id)

);

Question 11

PostgreSQL

different data types you can choose for fields added with CREATE TABLE

Answer 11

serial

varchar

boolean

date

time

timestamp

int

decimal / numeric

Question 12

PostgreSQL

constraints / restrictions that can be specified for columns in a PostgreSQL db

Answer 12

NOT NULL

NULL

DEFAULT

UNIQUE

PRIMARY KEY

FOREIGN KEY

Question 13

PostgreSQL

list all the columns of the users table

Answer 13

\d users

Question 14

PostgreSQL

add a new record to the users table

Answer 14

INSERT INTO

ex

INSERT INTO users (id, username, enabled)

VALUES (20, ‘Anthony Giuliano’, true);

Question 15

PostgreSQL

list all the rows and columns from the users table

Answer 15

SELECT

ex

SELECT * FROM users;

Question 16

PostgreSQL

clause to add criteria to a SELECT statement so you can filter the results

Answer 16

WHERE

ex

SELECT *

FROM users

WHERE id = 20;

Question 17

PostgreSQL

clause to sort the results of a SELECT query

Answer 17

ORDER BY

ex

SELECT *

FROM users

ORDER BY id;

SELECT *

FROM users

ORDER BY id DESC;

SELECT *

FROM users

ORDER BY enabled, id DESC;

Question 18

PostgreSQL

command to alter records in the users table

Answer 18

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’;

Question 19

PostgreSQL

clause used with WHERE to compare a string to a pattern

Answer 19

LIKE

ex

% is a wildcard character

SELECT *

FROM users

WHERE username LIKE ‘%II’;

Question 20

PostgreSQL

functions applied to a column in order to remove whitespace

Answer 20

trim()

rtrim()

ltrim()

ex

SELECT *

FROM users

WHERE rtrim(username) LIKE ‘% II’;

Question 21

PostgreSQL

command to delete rows from a table

Answer 21

DELETE

ex

DELETE FROM users

WHERE id = 20;

Question 22

PostgreSQL

command to change the schema of a table

Answer 22

ALTER TABLE

ex

ALTER TABLE users

ADD COLUMN last_login TIMESTAMP NOT NULL DEFAULT NOW();

Question 23

PostgreSQL

function that returns the current date and time

Answer 23

NOW()

Question 24

PostgreSQL

clause to change the name of an existing column

Answer 24

RENAME COLUMN TO

ex

ALTER TABLE users

RENAME COLUMN username TO full_name;

Question 25

PostgreSQL

clause to change the data type of an existing column

Answer 25

ALTER COLUMN TYPE

ex

ALTER TABLE users

ALTER COLUMN full_name TYPE VARCHAR(25);

Question 26

PostgreSQL

clause to remove a column from an existing table

Answer 26

DROP COLUMN

ex

ALTER TABLE users

DROP COLUMN enabled;

Question 27

PostgreSQL

clause to rename an existing table in your database

Answer 27

RENAME TO

ex

ALTER TABLE users

RENAME TO all_users;

Question 28

PostgreSQL

command to delete an entire table from your database

Answer 28

DROP TABLE

ex

DROP TABLE all_users;

Question 29

PostgreSQL

ERD (entity relationship diagram)

Answer 29

a graphical representation of the tables in a database and the relationships between them.

Question 30

PostgreSQL

set up a One to One relationship between two tables

Answer 30

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) );

Question 31

PostgreSQL

set up a One to Many relationship between two tables

Answer 31

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 );

Question 32

PostgreSQL

an alias for NOW()

Answer 32

CURRENT_TIMESTAMP

Question 33

PostgreSQL

set up a a Many to Many relationship between books and users

Answer 33

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 );

Question 34

PostgreSQL

clause to ensure that child records in other tables are deleted

Answer 34

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 );

Question 35

PostgreSQL

syntax to make a column a foreign key

Answer 35

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 );

Question 36

PostgreSQL

make a composite key on a join table

Answer 36

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 );

Question 37

PostgreSQL

set a unique restriction on a field

Answer 37

UNIQUE (field)

ex

CREATE TABLE users (

email varchar,

UNIQUE (email) );

Question 38

PostgreSQL

inner join syntax

Answer 38

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).

Question 39

PostgreSQL

left join syntax

Answer 39

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.

Question 40

PostgreSQL

cross join syntax

Answer 40

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.

Question 41

PostgreSQL

right join syntax

Answer 41

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.

Question 42

PostgreSQL

export records to CSV

Answer 42

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;

Question 43

PostgreSQL

the difference between COPY and \copy

Answer 43

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.

Question 44

PostgreSQL

import records from CSV

Answer 44

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 ‘,’)

Question 45

PostgreSQL

combine criteria in a WHERE clause

Answer 45

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’;

Question 46

PostgreSQL

function that combines columns and / or strings together into one field

Answer 46

CONCAT

ex

SELECT CONCAT(title, ‘ ‘, ‘by’, ‘ ‘, author) AS “Books By”

FROM books;

Question 47

PostgreSQL

alias a column name in your results

Answer 47

AS

ex

SELECT count(id) AS enabled_count

FROM users

WHERE enabled = true;

Question 48

PostgreSQL

filter out duplicate rows in a query

Answer 48

DISTINCT

ex

SELECT DISTINCT u.username

FROM users u

LEFT JOIN users_books ub ON u.id = ub.user_id;

Question 49

PostgreSQL

cap the number of records returned by a select statement

Answer 49

LIMIT / OFFSET

ex

SELECT *

FROM users

LIMIT 10 OFFSET 10;

Question 50

PostgreSQL

clause used with WHERE to return records that don’t exist in another set of records

Answer 50

NOT IN

ex

SELECT u.username

FROM users u

WHERE u.id NOT IN

( SELECT ub.user_id FROM users_books ub );

Question 51

PostgreSQL

function that returns the length of a string

Answer 51

length()

ex

SELECT * FROM books WHERE length(author) = 0;

Question 52

PostgreSQL

functions that aggregate record count, minimum and maximum values

Answer 52

count()

min()

max()

Question 53

PostgreSQL

functions that return the current date, time and timestamp

Answer 53

current_date()

current_time()

now()

Question 54

PostgreSQL

use count to return the number of unique values in a column

Answer 54

COUNT(DISTINCT field)

ex

SELECT COUNT(DISTINCT description)

FROM transactions;

Question 55

PostgreSQL

return the sums of various groups of records in one query

Answer 55

SUM, GROUP BY

ex

SELECT SUM(paid_out) AS paid_out_total, description

FROM transactions

GROUP BY description;

Question 56

PostgreSQL

function to select subfields from date / time values

Answer 56

EXTRACT(SUBFIELD FROM date_field)

ex

SELECT SUM(paid_out), EXTRACT(MONTH FROM date)

FROM transactions

GROUP BY EXTRACT(MONTH FROM date);

Question 57

PostgreSQL

function to convert various data types to formatted strings

Answer 57

TO_CHAR

ex

SELECT SUM(paid_out), TO_CHAR(date, ‘Month’)

FROM transactions

GROUP BY TO_CHAR(date, ‘Month’);

Question 58

PostgreSQL

create a field whose value can be one of only a limited number of options

Answer 58

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) );

Question 59

PostgreSQL

three different kinds of INTEGER data types

Answer 59

SMALLINT: -32768 to 32767

INT: -2147483648 to 2147483647

BIGINT: -9223372036854775808 to 9223372036854775807

Question 60

PostgreSQL

clause to ensure that an integer column accept only positive numbers

Answer 60

CHECK

CREATE TABLE people (

age SMALLINT CHECK (age > 0) );

Question 61

PostgreSQL

create a column that contains decimals

Answer 61

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)

Question 62

PostgreSQL

create an index on a single column

Answer 62

CREATE INDEX ON table (field);

Question 63

PostgreSQL

delete an existing index on a single column

Answer 63

DROP INDEX index_name

Get the index name by looking at \d table

Question 64

PostgreSQL

brew services start postgresql

brew services stop postgresql

brew services restart postgresql

Answer 64

use Homebrew to start and stop the psql server

Question 65

PostgreSQL

$ createdb dbname

Answer 65

create a new database from the command line

Question 66

PostgreSQL

$ dropdb dbname

Answer 66

delete a database from the command line

Question 67

PostgreSQL

$ pg_dump dbname > file.sql

Answer 67

backup or export a database from the command line

Question 68

PostgreSQL

$ psql -d dbname < file.sql

Answer 68

restore a database from an sql file

Question 69

PostgreSQL

$ psql dbname

Answer 69

open an existing database from the command line

Question 70

PostgreSQL

\l

\list

Answer 70

print a list of current databases

Question 71

PostgreSQL

\c dbname

Answer 71

switch to a different database

Question 72

PostgreSQL

\dt

Answer 72

list existing tables

Question 73

PostgreSQL

CREATE TABLE

ex

CREATE TABLE users

( id serial,

username CHAR(25) NOT NULL,

enabled boolean DEFAULT TRUE,

PRIMARY KEY (id)

);

Answer 73

create a new table in a database

Question 74

PostgreSQL

serial

varchar

boolean

date

time

timestamp

int

decimal / numeric

Answer 74

different data types you can choose for fields added with CREATE TABLE

Question 75

PostgreSQL

NOT NULL

NULL

DEFAULT

UNIQUE

PRIMARY KEY

FOREIGN KEY

Answer 75

constraints / restrictions that can be specified for columns in a PostgreSQL db

Question 76

PostgreSQL

\d users

Answer 76

list all the columns of the users table

Question 77

PostgreSQL

INSERT INTO

ex

INSERT INTO users (id, username, enabled)

VALUES (20, ‘Anthony Giuliano’, true);

Answer 77

add a new record to the users table

Question 78

PostgreSQL

SELECT

ex

SELECT * FROM users;

Answer 78

list all the rows and columns from the users table

Question 79

PostgreSQL

WHERE

ex

SELECT *

FROM users

WHERE id = 20;

Answer 79

clause to add criteria to a SELECT statement so you can filter the results

Question 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;

Answer 80

clause to sort the results of a SELECT query

Question 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’;

Answer 81

command to alter records in the users table

Question 82

PostgreSQL

LIKE

ex

% is a wildcard character

SELECT *

FROM users

WHERE username LIKE ‘%II’;

Answer 82

clause used with WHERE to compare a string to a pattern

Question 83

PostgreSQL

trim()

rtrim()

ltrim()

ex

SELECT *

FROM users

WHERE rtrim(username) LIKE ‘% II’;

Answer 83

functions applied to a column in order to remove whitespace

Question 84

PostgreSQL

DELETE

ex

DELETE FROM users

WHERE id = 20;

Answer 84

command to delete rows from a table

Question 85

PostgreSQL

ALTER TABLE

ex

ALTER TABLE users

ADD COLUMN last_login TIMESTAMP NOT NULL DEFAULT NOW();

Answer 85

command to change the schema of a table

Question 86

PostgreSQL

NOW()

Answer 86

function that returns the current date and time

Question 87

PostgreSQL

RENAME COLUMN TO

ex

ALTER TABLE users

RENAME COLUMN username TO full_name;

Answer 87

clause to change the name of an existing column

Question 88

PostgreSQL

ALTER COLUMN TYPE

ex

ALTER TABLE users

ALTER COLUMN full_name TYPE VARCHAR(25);

Answer 88

clause to change the data type of an existing column

Question 89

PostgreSQL

DROP COLUMN

ex

ALTER TABLE users

DROP COLUMN enabled;

Answer 89

clause to remove a column from an existing table

Question 90

PostgreSQL

RENAME TO

ex

ALTER TABLE users

RENAME TO all_users;

Answer 90

clause to rename an existing table in your database

Question 91

PostgreSQL

DROP TABLE

ex

DROP TABLE all_users;

Answer 91

command to delete an entire table from your database

Question 92

PostgreSQL

a graphical representation of the tables in a database and the relationships between them.

Answer 92

ERD (entity relationship diagram)

Question 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) );

Answer 93

set up a One to One relationship between two tables

Question 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 );

Answer 94

set up a One to Many relationship between two tables

Question 95

PostgreSQL

CURRENT_TIMESTAMP

Answer 95

an alias for NOW()

Question 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 );

Answer 96

set up a a Many to Many relationship between books and users

Question 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 );

Answer 97

clause to ensure that child records in other tables are deleted

Question 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 );

Answer 98

syntax to make a column a foreign key

Question 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 );

Answer 99

make a composite key on a join table

Question 100

PostgreSQL

UNIQUE (field)

ex

CREATE TABLE users (

email varchar,

UNIQUE (email) );

Answer 100

set a unique restriction on a field

Question 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).

Answer 101

inner join syntax

Question 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.

Answer 102

left join syntax

Question 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.

Answer 103

cross join syntax

Question 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.

Answer 104

right join syntax

Question 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;

Answer 105

export records to CSV

Question 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.

Answer 106

the difference between COPY and \copy

Question 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 ‘,’)

Answer 107

import records from CSV

Question 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’;

Answer 108

combine criteria in a WHERE clause

Question 109

PostgreSQL

CONCAT

ex

SELECT CONCAT(title, ‘ ‘, ‘by’, ‘ ‘, author) AS “Books By”

FROM books;

Answer 109

function that combines columns and / or strings together into one field

Question 110

PostgreSQL

AS

ex

SELECT count(id) AS enabled_count

FROM users

WHERE enabled = true;

Answer 110

alias a column name in your results

Question 111

PostgreSQL

DISTINCT

ex

SELECT DISTINCT u.username

FROM users u

LEFT JOIN users_books ub ON u.id = ub.user_id;

Answer 111

filter out duplicate rows in a query

Question 112

PostgreSQL

LIMIT / OFFSET

ex

SELECT *

FROM users

LIMIT 10 OFFSET 10;

Answer 112

cap the number of records returned by a select statement

Question 113

PostgreSQL

NOT IN

ex

SELECT u.username

FROM users u

WHERE u.id NOT IN

( SELECT ub.user_id FROM users_books ub );

Answer 113

clause used with WHERE to return records that don’t exist in another set of records

Question 114

PostgreSQL

length()

ex

SELECT * FROM books WHERE length(author) = 0;

Answer 114

function that returns the length of a string

Question 115

PostgreSQL

count()

min()

max()

Answer 115

functions that aggregate record count, minimum and maximum values

Question 116

PostgreSQL

current_date()

current_time()

now()

Answer 116

functions that return the current date, time and timestamp

Question 117

PostgreSQL

COUNT(DISTINCT field)

ex

SELECT COUNT(DISTINCT description)

FROM transactions;

Answer 117

use count to return the number of unique values in a column

Question 118

PostgreSQL

SUM, GROUP BY

ex

SELECT SUM(paid_out) AS paid_out_total, description

FROM transactions

GROUP BY description;

Answer 118

return the sums of various groups of records in one query

Question 119

PostgreSQL

EXTRACT(SUBFIELD FROM date_field)

ex

SELECT SUM(paid_out), EXTRACT(MONTH FROM date)

FROM transactions

GROUP BY EXTRACT(MONTH FROM date);

Answer 119

function to select subfields from date / time values

Question 120

PostgreSQL

TO_CHAR

ex

SELECT SUM(paid_out), TO_CHAR(date, ‘Month’)

FROM transactions

GROUP BY TO_CHAR(date, ‘Month’);

Answer 120

function to convert various data types to formatted strings

Question 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) );

Answer 121

create a field whose value can be one of only a limited number of options

Question 122

PostgreSQL

SMALLINT: -32768 to 32767

INT: -2147483648 to 2147483647

BIGINT: -9223372036854775808 to 9223372036854775807

Answer 122

three different kinds of INTEGER data types

Question 123

PostgreSQL

CHECK

CREATE TABLE people (

age SMALLINT CHECK (age > 0) );

Answer 123

clause to ensure that an integer column accept only positive numbers

Question 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)

Answer 124

create a column that contains decimals

Question 125

PostgreSQL

CREATE INDEX ON table (field);

Answer 125

create an index on a single column

Question 126

PostgreSQL

DROP INDEX index_name

Get the index name by looking at \d table

Answer 126

delete an existing index on a single column

Question 127

PostgreSQL

What kind of programming language is SQL?

Answer 127

SQL is a “special purpose language”, since it’s typically only used to interact with relational databases

Question 128

PostgreSQL

What are the three sublanguages of SQL?

Answer 128

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Langugage (DCL)

Question 129

PostgreSQL

What function returns a lowercased version of a string?

Answer 129

lower

ex

SELECT lower(‘AlPhAbEt’);

Question 130

PostgreSQL

How does the psql console display true and false values?

Answer 130

t and f

Question 131

PostgreSQL

Use SQL to compute the surface area of a sphere with a radius of 26.3cm, truncated to return an integer

Answer 131

SELECT trunc(4 * pi() * 26.3 ^ 2);

Question 132

PostgreSQL

SQL is a “special purpose language”, since it’s typically only used to interact with relational databases

Answer 132

What kind of programming language is SQL?

Question 133

PostgreSQL

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Langugage (DCL)

Answer 133

What are the three sublanguages of SQL?

Question 134

PostgreSQL

lower

ex

SELECT lower(‘AlPhAbEt’);

Answer 134

What function returns a lowercased version of a string?

Question 135

PostgreSQL

t and f

Answer 135

How does the psql console display true and false values?

Question 136

PostgreSQL

SELECT trunc(4 * pi() * 26.3 ^ 2);

Answer 136

Use SQL to compute the surface area of a sphere with a radius of 26.3cm, truncated to return an integer

Question 137

PostgreSQL

add a constraint to an existing table

Answer 137

ALTER TABLE ADD

ex

ALTER TABLE films

ADD CHECK(year > 0);

Question 138

PostgreSQL

ALTER TABLE ADD

ex

ALTER TABLE films

ADD CHECK(year > 0);

Answer 138

add a constraint to an existing table

Question 139

PostgreSQL

create a new sequence called ‘counter’

Answer 139

CREATE SEQUENCE counter;

Question 140

PostgreSQL

return the next value from the sequence called ‘counter’

Answer 140

SELECT nextval(‘counter’);

Question 141

PostgreSQL

delete the sequence called ‘counter’

Answer 141

DROP SEQUENCE counter;

Question 142

PostgreSQL

CREATE SEQUENCE counter;

Answer 142

create a new sequence called ‘counter’

Question 143

PostgreSQL

SELECT nextval(‘counter’);

Answer 143

return the next value from the sequence called ‘counter’

Question 144

PostgreSQL

DROP SEQUENCE counter;

Answer 144

delete the sequence called ‘counter’

Question 145

PostgreSQL

create a sequence called ‘even_counter’ that starts at 2 and increments by 2

Answer 145

CREATE SEQUENCE even_counter

INCREMENT BY 2

MINVALUE 2;

Question 146

PostgreSQL

CREATE SEQUENCE even_counter

INCREMENT BY 2

MINVALUE 2;

Answer 146

create a sequence called ‘even_counter’ that starts at 2 and increments by 2

Question 147

PostgreSQL

aggregate function that works like ruby’s Array#join

Answer 147

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;

Question 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;

Answer 148

aggregate function that works like ruby’s Array#join

Question 149

PostgreSQL

special value that represents the absence of any other value

Answer 149

NULL

Question 150

PostgreSQL

NULL

Answer 150

special value that represents the absence of any other value

Question 151

PostgreSQL

high level design focused on identifying entities and their relationships

Answer 151

conceptual schema

Question 152

PostgreSQL

low level database specific design focused on implementation

Answer 152

physical schema

Question 153

PostgreSQL

conceptual schema

Answer 153

high level design focused on identifying entities and their relationships

Question 154

PostgreSQL

physical schema

Answer 154

low level database specific design focused on implementation

Question 155

PostgreSQL

the number of objects on each side of a relationship (1:1, 1:M, M:M)

Answer 155

cardinality

Question 156

PostgreSQL

cardinality

Answer 156

the number of objects on each side of a relationship (1:1, 1:M, M:M)

Question 157

PostgreSQL

whether the relationship is required (1) or optional (0)

Answer 157

modality

Question 158

PostgreSQL

modality

Answer 158

whether the relationship is required (1) or optional (0)

Question 159

PostgreSQL

a constraint that enforces certain rules about what values are permitted in a foreign key field

Answer 159

foreign key constraint

Question 160

PostgreSQL

foreign key constraint

Answer 160

a constraint that enforces certain rules about what values are permitted in a foreign key field

Question 161

PostgreSQL

create a foreign key column

Answer 161

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) );

Question 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) );

Answer 162

create a foreign key column

Question 163

add a foreign key constraint on an existing field

Answer 163

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);

Question 164

PostgreSQL

add a not null constraint to an existing column called product_id on the orders table

Answer 164

ALTER TABLE orders

ALTER COLUMN product_id

SET not null;

Question 165

PostgreSQL

true or false: a foreign key constrain prevents that column from having NULL values

Answer 165

false; it is common to include NOT NULL and FOREIGN KEY constraints together

Question 166

PostgreSQL

a situation where the database becomes inconsistent, so it contains more than one answer for a given question

Answer 166

update anomoly

Question 167

PostgreSQL

update anomoly

Answer 167

a situation where the database becomes inconsistent, so it contains more than one answer for a given question

Question 168

PostgreSQL

a situation where the schema can’t store the information for an entity without having an entry in a related entity

Answer 168

insertion anomaly

Question 169

PostgreSQL

insertion anomaly

Answer 169

a situation where the schema can’t store the information for an entity without having an entry in a related entity

Question 170

PostgreSQL

a situation where we lose all the information about an entity if we delete records from a related entity

Answer 170

deletion anomaly

Question 171

PostgreSQL

deletion anomaly

Answer 171

a situation where we lose all the information about an entity if we delete records from a related entity

Question 172

PostgreSQL

the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies

Answer 172

normalization

Question 173

PostgreSQL

normalization

Answer 173

the process of designing schema that minimizes or eliminates the possible occurrence of update, insertion and deletion anomalies

Question 174

PostgreSQL

clause used with WHERE to compare a string to a pattern, case insensitive

Answer 174

ILIKE

ex

% is a wildcard character

SELECT *

FROM users

WHERE username ILIKE ‘%II’;

Question 175

PostgreSQL

ILIKE

ex

% is a wildcard character

SELECT *

FROM users

WHERE username ILIKE ‘%II’;

Answer 175

clause used with WHERE to compare a string to a pattern, case insensitive