PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

It is a relational database system. Alternatives include: SQLite, MySQL

Question 2

What is one way to see if PostgreSQL is running?

Answer 2

sudo service postgresql status

Question 3

What is a database schema?

Answer 3

A database schema is a collection of tables

Question 4

What is a table?

Answer 4

A table is a list of rows each having the same set of attributes

Question 5

What is a row?

Answer 5

A row is a collection of related data

Question 6

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

Answer 6

It is a declarative language like HTML, and CSS. Meaning: All we do is tell SQL what we want to happen and it forms the plan.

Question 7

How do you retrieve specific columns from a database table?

Answer 7

You would retrieve it with: SELECT "columnName"

Question 8

How do you filter rows based on some specific criteria?

Answer 8

You would use the WHERE keyword

Question 9

What are the benefits of formatting your SQL?

Answer 9

Pure readability

Question 10

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

Answer 10

You can use:
- =
- !=
- <
- >

Question 11

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

Answer 11

You would use the LIMIT keyword (ex. LIMIT 5

Question 12

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

Answer 12

You would use the LIMIT keyword (ex. LIMIT 5)

Question 13

How do you retrieve all columns from a database table?

Answer 13

You would retrieve it with: SELECT *

Question 14

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

Answer 14

One can order it with the keywords ORDER BY "columnName" (DESC/ASC)
Where if none are specified it defaults to ASC

Question 15

How do you add a row to a SQL table?

Answer 15

~~~
INSERT INTO (“attribute1”, …“attributeN”)
VALUES (“value1”, …“valueN”);
````

Question 16

What is a tuple?

Answer 16

In SQL, a list of values is referred to as a tuple.

Question 17

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

Answer 17

One would simply list more tuples

Question 18

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

Answer 18

One can use the RETURNING statement followed by an * or any attributes desired

Question 19

How do you update rows in a database table?

Answer 19

One would need to use the UPDATE keyword and then use SET to change the value (ex. `UPDATE artists

Question 20

How do you update rows in a database table?

Answer 20

One would need to use the UPDATE keyword and then use SET to change the value (ex. UPDATE artists SET name='example')

Question 21

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

Answer 21

So you can select what data is being updated as opposed to every row in the table

Question 22

What is a foreign key?

Answer 22

A key that is used to link values from one table with another (ie. they intersect)
If a value is a foreign key in a table then it references a primary key in another table.

Question 23

How do you join two SQL tables?

Answer 23

With the JOIN and USING keywords

Question 24

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

Answer 24

With the AS keyword

Question 25

What are some examples of aggregate functions?

Answer 25

• COUNT()
• MAX()
• MIN()
• SUM()
• AVG()

Question 26

What is the purpose of a group by clause?

Answer 26

In order to group rows that we wish to report on