Databases

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

An open-source relational database.

mySql / Oracle / SQL Server

Question 2

What are some advantages of learning a relational database?

Answer 2

So much work of a developer involves things that have relationships so RDB is a perfect tool

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

Question 4

What is a database schema?

Answer 4

A collection of tables

Question 5

What is a table?

Answer 5

A table holds records that have the same attributes

Question 6

What is a row?

Answer 6

A single record

Question 7

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

Answer 7

Structured Query language, used to interact with a database.
It’s declarative, you tell it what to do but not how to do it as opposed to JS which is imperative, you tell it how to do things

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “col1”, “col2”

from “table”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

where “col” = something

Question 10

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

Answer 10

= / != / < / >

Question 11

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

Answer 11

limit num;

Question 12

How do you retrieve all columns from a database table?

Answer 12

select *

Question 13

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

Answer 13

order by “col”, ascending default or desc

Question 14

How do you add a row to a SQL table?

Answer 14

insert into “table” (“col1”, “col2”)

values (“val1”, “val2”)

Question 15

What is a tuple?

Answer 15

list of values

Question 16

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

Answer 16

insert into “table” (“col1”, “col2”)
values (“val1”, “val2”),
(“val1”, “val2”)

Question 17

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

Answer 17

returning *

Question 18

How do you update rows in a database table?

Answer 18

update “table”
set “col” = newVal
where “col” = 24;

Question 19

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

Answer 19

if not, you are updating everything

Question 20

How do you delete rows from a database table?

Answer 20

delete from “table”

where “col” = 24

Question 21

How do you accidentally delete all rows from a table?

Answer 21

delete from “table”

Question 22

What is a foreign key?

Answer 22

one column that links the one table to another table.

Question 23

How do you join two SQL tables?

Answer 23

select “col”
from “table”
joining “table2” using (“col”)

Question 24

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

Answer 24

as “newName”

Question 25

What are some examples of aggregate functions?

Answer 25

sum, count, min, max

Question 26

What is the purpose of a group by clause?

Answer 26

Since the aggregate functions does something on all the rows, sometimes we want to combine that for specific groups separately