PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

• an open source relational database management system

- others are MySQL, oracle, sql server, sqlite

Question 2

What are some advantages of learning a relational database?

Answer 2

get to learn the widely used SQL language and relational database, also can store related data with data integrity

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

‘sudo service postgresql status’ in the terminal, then see if it says ‘online’

Question 4

What is a database schema?

Answer 4

a collection of tables which defines how data in a relational database should be organized

Question 5

What is a table?

Answer 5

a list of rows of stored data with the same set of attributes

Question 6

What is a row?

Answer 6

a horizontal line of data or single instance of a record

Question 7

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

Answer 7

• a declarative programming language which interacts with relational databases
• SQL is declarative, so you tell it what you want to do, but not how to do it, while JavaScript is imperative

Question 8

How do you retrieve specific columns from a database table?

Answer 8

use ‘select’ followed by column name in double quotes

ex. select “name”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

use ‘where’, followed by name of row, comparison operator, name of cell to filter by in single quotes
ex. where “category” = ‘cleaning’;

Question 10

What are the benefits of formatting your SQL?

Answer 10

consistency and readability

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’ followed by number of rows you want returned

ex. limit 1

Question 13

How do you retrieve all columns from a database table?

Answer 13

select * (asterisk)

from “tableName”;

Question 14

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

Answer 14

order by “columnName”

Question 15

How do you add a row to a SQL table?

Answer 15

‘insert into “tableName” (“rowName”,)
values (‘data’,)
ex.
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);

Question 16

What is a tuple?

Answer 16

a list of values (wrapped in parenthesis)

Question 17

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

Answer 17

add another tuple in parenthesis after a comma

Question 18

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

Answer 18

returning *

Question 19

How do you update rows in a database table?

Answer 19

update “tableName”
set “columnName” = ‘value’
where {expression} ex. “columnName” = ‘value’

Question 20

Why use where clause in update statements?

Answer 20

so you don’t update every single row in the table

Question 21

How do you delete rows from a database table?

Answer 21

delete from "tableName"
 where {conditional}
ex. delete from "products"
 where "productId" = 24
returning *;

Question 22

How do you accidentally delete all rows from a table?

Answer 22

delete from “tableName”

no where

Question 23

begin, commit, rollback

Answer 23

begin - don’t do the transaction yet
(then do the update or delete)
commit- finish the transaction
rollback - don’t want to commit, but want to end transaction

Question 24

What is a foreign key?

Answer 24

column which refers to a column in another table

Question 25

How do you join two SQL tables?

Answer 25

join "tableName" using ("columnName")
ex.
select *
  from "products"
  join "suppliers" using ("supplierId");

Question 26

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

Answer 26

rename column:
{keyword} “tableName”.”columnName” as “renamedColumn”

rename: table
{keyword} “tableName” as “renamedTable”

Question 27

What are some examples of aggregate functions?

Answer 27

max() avg() count() min() sum() every()

Question 28

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

Answer 28

to group rows together into one row based on values of a specific column