PostgreSQL

Question 1

Why do we use databases in Web development?

Question 2

What is PostgreSQL and what are some alternative relational databases?

Question 3

What are some advantages of learning a relational database?

Question 4

What is one way to see if PostgreSQL is running?

Question 5

What is a database schema?

Answer 5

A collection of tables is called a schema. A schema defines how the data in a relational database should be organized. It dictates how the table will be set up and how it will look.

Question 6

What is a table?

Answer 6

A table is what relational databases store data in. They are also known as relations, but are more commonly referred to as tables.

Question 7

What is a row?

Answer 7

A row is all the information about one entry or id of the table.

Question 8

What is an attribute and what other names are used to describe them?

Answer 8

An attribute is something related to a row. Other names used to describe them are column, property, key, characteristic, etc.

Question 9

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

Answer 9

Structured Query Language (SQL) is the primary way of interacting with relational databases and is a powerful way of retrieving, creating, and manipulating data in a relational database. It’s different from languages like JavaScript because it is a declarative programming language while languages like JavaScript are imperative programming languages.

Question 10

How do you retrieve specific columns from a database table?

Answer 10

You must “select” the columns/properties/keys in a comma separated list.

select “column”,
“property”,
“key”

Question 11

How do you filter rows based on some specific criteria?

Answer 11

After “select”ing “from” a table, use “where” followed by the criteria. The criteria will be in double quotes “ “ while the criteria value will be in single quotes ‘ ‘ if it is a string, while things like numbers and booleans will not be in quotes at all.

Question 12

What are the benefits of formatting your SQL?

Answer 12

Formatted SQL makes code look more consistent and therefore increases readability.

Question 13

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

Answer 13

=, !=, <, and >.

Question 14

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

Answer 14

At the end of a “select” statement, use “limit” followed by the number of rows you want returned. The number should not be in quotes.

Question 15

How do you retrieve all columns from a database table?

Answer 15

By using “select” with the universal operator *.

Question 16

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

Answer 16

By using “order by” after select, from, where but before limit, followed by a column name (in double quotes “ “). After the column name, you can also include desc to sort in descending order since the default sort order of the results is in ascending order.

Question 17

How do you add a row to a SQL table?

Answer 17

By using an SQL “insert” statement.

insert into “table” (“column”, “attribute”, “property”, “key”)
values (‘value’, ‘value’, number, boolean);

Question 18

What is a tuple?

Answer 18

A list of values.

Question 19

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

Answer 19

By using an SQL “insert” statement but specifying multiple “tuples” of values, separated by commas.

Question 20

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

Answer 20

By including “returning” followed by what you want to get back about the row. It can be returning * to get back the full inserted row back from the database, including its auto-generates attribute(s), or, if you only want specific values back, you can use a comma-separated list of column names instead of an * asterisk.

Question 21

How do you update rows in a database table?

Answer 21

By using an SQL “update” statement.
update “table”
set “column” = number,
“column” = ‘string’,
“column” = ‘string’
where “column” = number;

Question 22

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

Answer 22

It is important because without a where clause, your update statements would update every row in the table.

Question 23

How do you delete rows from a database table?

Answer 23

By using an SQL “delete” statement.
delete
from “row”
where “column” = 24
returning *;

Question 24

How do you accidentally delete all rows from a table?

Answer 24

By using an SQL “delete” statement but forgetting to include a where clause.

Question 25

What is a foreign key?

Answer 25

It is a key that is from another table.

Question 26

How do you join two SQL tables?

Answer 26

By using an SQL join clause.

select “p”.”name” as “product”,
“p”.”category”,
“s”.”name” as “supplier”,
“s”.”state”
from “products” as “p”
join “suppliers” as “s” using (“supplierId”)
where “p”.”category” = ‘cleaning’
and “p”.”price” < 20;

Question 27

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

Answer 27

You can alias column names by using the as keyword.

Question 28

What are some examples of aggregate functions?

Answer 28

max(), avg(), min(), sum(), every()

Question 29

What is the purpose of a group by clause?

Answer 29

When you want to separate rows into groups and perform aggregate functions on those groups of rows.