PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

relational DB mgmt system
RDBMS

MySQL
SQL Server for MS
Oracle

Question 2

What are some advantages of learning a relational database?

Answer 2

short answer: widely used and very powerful

long answer:
once you learn one, you’ve learned them all except for nuances

they support good guarantees about data integrity; minimize data corruption

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

$ sudo service postgresql status

shortcut: can just run start command again and it won’t do anythin

sudo = (super user do) keyword in terminal that elevates your privileges

service = control a bg service

Question 4

pgweb

Answer 4

a TOOL to VISUALIZE our DB
**this is not your server

How to access:

In the terminal that was running $top, launch the pgweb PostgreSQL database client by typing $pgweb and pressing enter. You can stop pgweb at any time by pressing $Ctrl + C.

Visit http://localhost:8081 in your web browser. The upper left corner of the UI should say dev.

**MUST STOP SERVER CTRL + C

Question 5

psql -c ‘\l’

Answer 5

psql = logs into DB (can be used alone)

-c = command

’ \l ‘ = command in a string, [backslash l ] means list

Question 6

why PostgreSQL?

Answer 6

works will with Node and has built in functionality, esp for array and object

a lot of ppl with Node backend use postgreSQL

Question 7

What is a database schema?

Answer 7

collection of tables

the instructions to build the tables

Question 8

What is a table?

Answer 8

data stored in relations

list of rows w/ same set of attributes (columns)

Question 9

What is a row?

Answer 9

a record

Question 10

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

Answer 10

how to interact with DB’s
declarative like HTML and CSS - you tell it what you want and it will give it back to you (order may change) via its own interpretation

Question 11

How do you retrieve specific columns from a database table?

Answer 11

select “columnName”

from “tableName”

Question 12

How do you filter rows based on some specific criteria?

Answer 12

where “condition” = ‘keyword’

use logical comparison operators

Question 13

What are the benefits of formatting your SQL?

Answer 13

cleaner, minimize mistakes, easy to maintain

Question 14

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

Answer 14

=
!=
>

Question 15

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

Answer 15

limit #ofrows

Question 16

How do you retrieve all columns from a database table?

Answer 16

select * (wildcard selector)

Question 17

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

Answer 17

order by

Question 18

How do you add a row to a SQL table?

Answer 18

insert into “tableName” (“columnName”, “columnName”)

values

Question 18

How do you add a row to a SQL table?

Answer 18

insert into “tableName” (“columnName”, “columnName”)

values. (‘value’, ‘value’)

Question 19

What is a tuple?

Answer 19

a list of values

Question 20

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

Answer 20

use commas between value rows

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’),
(‘Tater Mitts’, ‘Scrub some taters!’, 6, ‘cooking’)
returning *;

Question 21

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

Answer 21

returning *;

^^ use this as last line

Question 22

How do you update rows in a database table?

Answer 22

update “tableName”
set “columnName” = ‘value’
where “columnName” = ‘value’;

Question 23

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

Answer 23

otherwise you could update all rows the whole table and cannot undo it

Question 24

How do you delete rows from a database table?

Answer 24

delete from “tableName”
where “columnName” = ‘value’
returning *;

Question 25

How do you accidentally delete all rows from a table?

Answer 25

if you don’t add where

Question 26

What is a foreign key?

Answer 26

common key btwn tables

Question 27

How do you join two SQL tables?

Answer 27

join “tableName” using (“columnName”)

Question 28

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

Answer 28

alias

“as”

Question 29

What are some examples of aggregate functions?

Answer 29

max
min
count
sum

Question 30

What is the purpose of a group by clause?

Answer 30

separate rows into groups and perform aggregate functions on them (instead of each row)