PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

powerful relational database management system

Redis MangoDB etc

Question 2

What are some advantages of learning a relational database?

Answer 2

a lot of databases use SQL languages / popular

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

collection of tables -> it sets how the table should be structured

Question 5

What is a table?

Answer 5

list of a row each having set of attribute

Question 6

What is a row?

Answer 6

list of datas in table structured

Question 7

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

Answer 7

primary way of interacting with relational databases (retrieving, creating, and
manipulating data)
Different from javascript -> imperative langue where programmer tell what to do and how to do
sql -> declarative -> program figures out and describe the results

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “column”,
if more than one column. “ “
from “table”
(optional)
where “column” = ‘value’
order by “column” (optional) desc
limit number;

Question 9

How do you filter rows based on some specific criteria?

Answer 9

where “ “ =, >,

Question 10

What are the benefits of formatting your SQL?

Answer 10

retrieve the data easily

Question 11

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

Answer 11

= < > !=

Question 12

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

Answer 12

limit _____

Question 13

How do you retrieve all columns from a database table?

Answer 13

*

Question 14

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

Answer 14

order by “column”
if descending order
order by “column” desc

Question 15

How do you add a row to a SQL table?

Answer 15

sql = `insert into "table" ("column", "column")
         values ($1, $2)
returning *;
`
values = [value for $1, value for $2]

db
.query(sql, values)
numbers area automatically generated by app due to avoid duplicate value

Question 16

What is a tuple?

Answer 16

list of values

Question 17

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

Answer 17

insert into “table” (“column”, “column)
values (‘1st’, ‘1st’)
(‘2nd’, ‘2nd’);

Question 18

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

Answer 18

using returning*

Question 19

How do you update rows in a database table?

Answer 19

all -> update “table”
set “column” = ‘new value’;
specific -> update “table”
set “column” = ‘new value’
where “column” = ‘existing value’;
multiple -> update “table”
set “column” = ‘new value’,
“column2” = ‘new value,
where “column” = ‘existing value’;

Question 20

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

Answer 20

if not, it would update all row values in the table

Question 21

How do you delete rows from a database table?

Answer 21

delete from “table”
where “column” = ‘value’;

delete from “table”
where “column” = ‘value’
and “column” , =, != ‘value’

Question 22

How do you accidentally delete all rows from a table?

Answer 22

when you don’t put where

Question 23

What is a foreign key?

Answer 23

just one column that links other table to the current table (mostly id)

Question 24

How do you join two SQL tables?

Answer 24

select*

from “table”
join “table2” using (“mainlyid”)

Question 25

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

Answer 25

select “column” as “newname”

Question 26

What are some examples of aggregate functions?

Answer 26

count, sum, max, min, avg

select \_\_\_("column")
from"table"

Question 27

What is the purpose of a group by clause?

Answer 27

the selected data gets grouped based on group by “column”