PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

1. Open source relational databases management system

2. SQLlite, MySQL, Microsoft SQL System

Question 2

What are some advantages of learning a relational database?

Answer 2

1. Everything in web development is based on databases

2. Will basically always be using relational databases in web development

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

Sudo service postgresql status command

Question 4

What is a database schema?

Answer 4

A collection of tables that dictate how data should be organized

Question 5

What is a table?

Answer 5

A collection of rows that stored data with the same attributes

Question 6

What is a row?

Answer 6

A single record in a table

Question 7

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

Answer 7

SQL is declarative - the computer figures it out

JavaScript is imperative - the dev writes the machine instructions

Question 8

How do you retrieve specific columns from a database table?

Answer 8

Select
“column”,
“column”
From “row”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

By using where “ “ = “ “

Question 10

What are the benefits of formatting your SQL?

Answer 10

To lessen complexity

Question 11

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

Answer 11

Less than, greater than, equal

Question 12

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

Answer 12

The limit clause

Question 13

How do you retrieve all columns from a database table?

Answer 13

The universal * (star)

Question 14

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

Answer 14

Order by (desc) or (asc) clause

Question 15

How do you add a row to a SQL table?

Answer 15

The insert statement

Question 16

What is a tuple?

Answer 16

A set of parenthesis with a list of data

Question 17

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

Answer 17

Value clause, followed by “value” with command and multiple rows would be closed tuples on top of eachother

Question 18

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

Answer 18

Returning clause

Question 19

How do you update rows in a database table?

Answer 19

With an update statement

Update “table”
Set “column” = ‘new value’

Question 20

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

Answer 20

So you don’t update all the rows

Question 21

How do you delete rows from a database table?

Answer 21

Delete key word

Delete from “row”
Where “column” = ‘value’

Question 22

How do you accidentally delete all rows from a table?

Answer 22

Delete from “row”

Question 23

What is a foreign key?

Answer 23

A value in a column that has same key in another table

Question 24

How do you join two SQL tables?

Answer 24

Join clause
Join “table”
Using (shared foreign key)

Question 25

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

Answer 25

Aliasing

Select “a”.”firstName”
From “actors” as “c”

Question 26

What are some examples of aggregate functions?

Answer 26

Count(), sum(), min(), max()

Question 27

What is the purpose of a group by clause?

Answer 27

So you can specify specific rows to order