PostgresQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

It’s a relational database. Some other relational databases are MySQL, Oracle, and SQLite

Question 2

Important commands for starting, stopping and checking status for PostgreSQL

Answer 2

Sudo service postgreSQL (start / stop / status)

Question 3

Is PostgreSQL its own application?

Answer 3

Yes! We just interact with it. No building or debugging —> we send it a request and it sends a response.

Question 4

What is pgweb?

Answer 4

Not our code —> just code somebody else wrote to help us visualize our database.

Question 5

What are some advantages of learning a relational database?

Answer 5

It’s used literally everywhere. It also allows you to store your data somewhere secure.

Question 6

What is one way to see if PostgreSQL is running?

Answer 6

Sudo service postgresql status

Question 7

What is a database schema?

Answer 7

A collection of tables — the design of a database —> like a blueprint

Question 8

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

Answer 8

It’s the primary way of interacting with relational databases —> a declarative programming language —> programmers describe the results they want the the end gets the result

Question 9

How do you retrieve specific columns from a database table?

Answer 9

Use a select statement formatted as…

select “desired-column”,
“another-column”
from “table-name”;

Question 10

How do you filter rows based on some specific criteria?

Answer 10

Use a where clause formatted as…

where “column-name” = ‘text-to-query’;

Question 11

If you are adding a number, bool, etc, should you keep the quotes?

Answer 11

Nope — remove them!

Question 12

What are the benefits of formatting your SQL?

Answer 12

It makes it easier to read, understand, and debug.

Question 13

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

Answer 13

=, !=, <, >

Question 14

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

Answer 14

Limit x(integer);

Question 15

How do you retrieve all columns from a database table?

Answer 15

Use a select statement formatted as…

select *
from “table-name”;

Question 16

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

Answer 16

Order by “column-to-sort-by”;

Question 17

Default order is ascending or descending?

Answer 17

Ascending order. (Smallest to largest)

Question 18

How do you add a row to a SQL table?

Answer 18

Use an insert statement.
INSERT INTO “table-name” (“column1”, “column2”)
VALUES (‘value1’, ‘value2’)

Question 19

What is a tuple?

Answer 19

An ordered set of values typically wrapped in parenthesis

Question 20

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

Answer 20

Specify more than one set of tuples seperated by commas (they’ll both be returned if you use a returning statement)

Question 21

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

Answer 21

Add a returning clause after the values clause

Question 22

How do you update rows in a database table?

Answer 22

Use an sql update statement
UPDATE “table-to-update”
SET “column-to-update” = ‘value-to-update-with’
WHERE “column-name” = ‘value’

Question 23

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

Answer 23

If you don’t include one, you’ll overwrite all data in that column (very bad yikes RIP)

Question 24

How do you delete rows from a database table?

Answer 24

Use “delete”
DELETE
FROM “table-name”
WHERE “column-name” = ‘value’
RETURNING *

Question 25

How do you accidentally delete all rows from a table?

Answer 25

You leave off the where statement (big oops)

Question 26

What is a foreign key?

Answer 26

A constraint column with data that refers to values in another table that links those tables together

Question 27

How do you join two SQL tables?

Answer 27

SELECT *
FROM “table-name”
JOIN “table-to-join” using (“column-to-join-by”)

Question 28

What is JOIN ON?

Answer 28

Same as join, but give specific IDs to join on.

JOIN “table2” on “table1”.“table1ID” = “table2”.“table2ID”

Question 29

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

Answer 29

SELECT “p”.“name” as “product”,
“p”.“category”,
“s”.“name” as “supplier,
FROM “products” as “p”
JOIN “suppliers” as “s” using (“supplierId”);

Question 30

What are some examples of aggregate functions?

Answer 30

Max(), Avg(), Count(), Min(), Sum(), Every()

Question 31

What is the purpose of a group by clause?

Answer 31

Performing aggregate functions only on a specific group of rows