PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

A Relational Database Management System (RDBMS) that uses the SQL language for querying and maintaining the database.

Alternative relational databases include Microsoft SQL Server, MySQL, SQLite, and Oracle.

Question 2

What are some advantages of learning a relational database?

Answer 2

Relational databases are the most widely used kind of database. Most relational databases are also driven by SQL.

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

By using the postgresql status command.
(sudo service postgresql status)

Question 4

What is a database schema?

Answer 4

A collection of tables that define how the data in a relational database should be organized.

Question 5

What is a table?

Answer 5

A list of rows each having the same set of attributes. (Also referred to as relations)

Question 6

What is a row?

Answer 6

A row contains attributes (Also referred to as columns)

Question 7

What is SQL and how is it different from languages like JavaScript?

Answer 7

SQL is how a server can interact with a relational database by retrieving, creating, or manipulating data.

It is a declarative language meaning that developers describe the results they want and the programming environment finds a way to get those results versus saying what to do and how to do it (imperative language).

Question 8

How do you retrieve specific columns from a database table?

Answer 8

By using a ‘select’ clause followed by a comma separated list of column names in double quotes. Then a ‘from’ clause followed by a table name in double quotes.

Question 9

How do you filter rows based on some specific criteria?

Answer 9

By using a ‘where’ clause followed by a condition where there is a column name in double quotes, and operator, and the value in single quotes.

Question 10

What are the benefits of formatting your SQL?

Answer 10

Formatting SQL makes the query easier to understand.

Question 11

What are four comparison operators that can be used in a where clause?

Answer 11

=, <, > and !=.

Question 12

How do you limit the number of rows returned in a result set?

Answer 12

By using a ‘limit’ clause followed by a literal integer value.

Question 13

How do you retrieve all columns from a database table?

Answer 13

By using a ‘select’ clause followed an asterisk (star).

Question 14

How do you control the sort order of a result set?

Answer 14

By using a ‘order by’ clause followed by the column name and ‘desc’ clause (the order is ascending by default).

Question 15

How do you add a row to a SQL table?

Answer 15

The ‘insert into’ statement followed by the table name in quotes and a comma separated list of column names in quotes surrounded by parentheses.

Then, the ‘values’ statement followed by a comma separated list of values in single quotes (except numbers) surrounded by parenthesis.

Question 16

What is a tuple?

Answer 16

A list of values in the same order as the columns they belong to.

Question 17

How do you add multiple rows to a SQL table at once?

Answer 17

By adding a comma separated list of tuples after the values keyword.

Question 18

How do you get back the row being inserted into a table without a separate select statement?

Answer 18

By using the ‘returning’ clause followed by an asterisk (star).

Question 19

How do you update rows in a database table?

Answer 19

The ‘update’ statement followed by the table name in double quotes. Then, a ‘set’ keyword followed by a column name in double quotes, an operator, and a value in single quotes (except numbers).

Question 20

Why is it important to include a where clause in your update statements?

Answer 20

Not including a ‘where’ clause will update every row instead of specific rows.

Question 21

How do you delete rows from a database table?

Answer 21

The ‘delete from’ statement followed by the name of the table in quotes. Then, a ‘where’ clause followed by the column name is double quotes, an operator, and a value in single quotes (except numbers).

Question 22

How do you accidentally delete all rows from a table?

Answer 22

The ‘delete from’ statement followed by the table name in double quotes.

Question 23

What is a foreign key?

Answer 23

A column in a table that specifically refers to a column in another table. (A shared data value between tables)

(i.e., “products” have a “supplierId” column that refers to the “supplierId” column in “suppliers”)

Question 24

How do you join two SQL tables?

Answer 24

The ‘join’ clause followed by the table name in double quotes, the ‘using’ statement, and the foreign key in double quotes and parenthesis.

Question 25

How do you temporarily rename columns or tables in a SQL statement?

Answer 25

The table name in double quotes followed by the ‘as’ statement, and temporary name in double quotes. (Known as aliasing)

This can be done after the ‘select’, ‘from’, and ‘join’ clauses.

Question 26

What are some examples of aggregate functions?

Answer 26

min(), max(), sum(), avg(), and count().

json_agg(“…”)

Question 27

What is the purpose of a group by clause?

Answer 27

To separate rows into groups and perform aggregate functions on the groups of rows.