PostgreSQL

Question 1

[Postgres Intro]

What is PostgreSQL and what are some alternative relational databases?

Answer 1

PostgreSQL - relational database

MySQL, SQL Server by Microsoft, Oracle

Question 2

[Postgres Intro]

What are some advantages of learning a relational database?

Answer 2

They are very common, knowing one will translate well to others

Question 3

[Postgres Intro]

What is one way to see if PostgreSQL is running?

Answer 3

$ sudo service postgresql status

with service, they won’t take up your terminal (they run in the background)

Question 4

What does SQL mean?

Answer 4

Structured Query Language

Question 5

[Postgres Database]

What is a database schema?

Answer 5

A collection of tables which define how the data in a database should be organized

Question 6

[Postgres Database]

What is a table?

Answer 6

Rows with the same set of attributes

Question 7

[Postgres Database]

What is a row?

Answer 7

One entry with one value of the data

Question 8

[Postgres Database]
What command is used to create a database?
What command is used to delete a database?

Answer 8

$ createdb databasename

$ dropdb databasename

Question 9

[SQL Select]

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

Answer 9

SQL is a declarative language where JavaScript is imperative language.

Question 10

[SQL Select]

How do you retrieve specific columns from a database table?

Answer 10

select (keyword) “col title”

from (clause) “table name”

Question 11

[SQL Select]

How do you filter rows based on some specific criteria?

Answer 11

where (clause) “category” comparison operator ‘value’

which returns true or false

Question 12

[SQL Select]

What are the benefits of formatting your SQL?

Answer 12

Makes it easier to read the information

Question 13

[SQL Select]

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

Answer 13

=, !=, <, >

Question 14

[SQL Select]

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

Answer 14

limit num;

Question 15

[SQL Select]

How do you retrieve all columns from a database table?

Answer 15

select (keyword) *

Question 16

[SQL Select]

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

Answer 16

order by “category” desc/asc

Question 17

[SQL Insert]

How do you add a row to a SQL table?

Answer 17

insert into “table name” (“cat1”, “cat2”, “cat3”, “cat4”)

values (“val1”, “val2”, “val3”, “val4”)

Question 18

[SQL Insert]

What is a tuple?

Answer 18

a list of values

Question 19

[SQL Insert]

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

Answer 19

write multiple tuples separated by commas

Question 20

[SQL Insert]

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

Answer 20

returning *;

Question 21

[SQL Update]

How do you update rows in a database table?

Answer 21

update “table”
set “category name” = ‘value’
where “category” = ‘original value’;

Question 22

[SQL Update]

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

Answer 22

If omitted, will update the value of every row

Question 23

[SQL Delete]

How do you delete rows from a database table?

Answer 23

delete from “table name”
where “category” = ‘value’
returning *;

Question 24

[SQL Delete]

How do you accidentally delete all rows from a table?

Answer 24

delete from “table name”;

[not including ‘where’ clause]

Question 25

How to make changes in a copy instead of the actual database?

Answer 25

being -> make changes

commit or rollback

Question 26

[SQL Join]

What is a foreign key?

Answer 26

Shared value in columns between two tables

Question 27

[SQL Join]

How do you join two SQL tables?

Answer 27

select *
from “table 1”
join “table 2” using (“foreign key”);

Question 28

[SQL Join]

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

Answer 28

select “table 1”.”original col name” as “temp col name”,
“table 2”.”original col name” as “temp col name 2”
from”table1 “
join”table 2” using (“foreign key”);

Question 29

How do you also change the name of a table? Why would this be helpful?

Answer 29

To make it easier to type out the table name if it is repeated

select "p"."name" as "product",
       "p"."category",
       "s"."name" as "supplier",
       "s"."state"
  from "products" as "p"
  join "suppliers" as "s" using ("supplierId");

Question 30

[SQL Aggregates]

What are some examples of aggregate functions?

Answer 30

count()
average()
sum()

Question 31

[SQL Aggregates]

What is the purpose of a ‘group by’ clause?