Databases

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

A relational database

-Oracle, MySQL, SQL server by Microsoft

Question 2

What are some advantages of learning a relational database?

Answer 2

Doesn’t require a complex structure.
Data integrity – they can store and modify data in a way that makes data corruption as unlikely as possible.
Used frequently.
All relational databases share the same syntax–if you learn one, you can easily learn another one.
Data that’s stored in them can be compacted really well.
Prevent data replication (also good for data integrity)
Information is easy to access. Data is stored in tables and the tables can be joined together in any way that you need to. (flexible based on what your need is).

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

top command

Question 4

What’s the difference between pgweb and postgreSQL?

Answer 4

pgweb is a gui. It’s the graphical interface for postgreSQL.

you need postgreSQL for pgweb. But, you don’t need pgweb for postgreSQL.

Question 5

What is a database schema?

Answer 5

A collection of tables. Defines how the data in a relational database should be organized..

Defines the structure

Question 6

What is a table?

Answer 6

A list of rows, each having the same set of attributes.

Attributes are commonly referred to as columns. You might visualize a database table as a sort of spreadsheet where each row is a record in that spreadsheet.

Question 7

What is a row?

Answer 7

Each row in a table is a single record of data. One instance of each attribute in the table

Question 8

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

Answer 8

SQL is a declarative programming language. In declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results.

JS is an imperative language –> you tell it what you want and how to do it.

Question 9

How do you retrieve specific columns from a database table?

Answer 9

select “columnName”,
“columnName2”
from “tableName”;

Question 10

How do you filter rows based on some specific criteria?

Answer 10

where “columnName” = ‘criteria’

can use any logical operator

Question 11

What are the benefits of formatting your SQL?

Answer 11

cleaner and easier to read

Question 12

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

Answer 12

=
!=
<
>

Question 13

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

Answer 13

limit number

Question 14

How do you retrieve all columns from a database table?

Answer 14

select *

Question 15

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

Answer 15

order by

default is to sort in ascending order

ex) order by “price” desc

Question 16

How do you add a row to a SQL table?

Answer 16

insert into “table” (“col1”, “col2”, …)
values (‘val1’, ‘val2’,…);

alt syntax

Insert into "tableName"
set
   "col1" = 'val1'
   "col2"='val2'
;

Question 17

What is a tuple?

Answer 17

A list of values

Question 18

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

Answer 18

Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.

ex)
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 19

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

Answer 19

returning *;

Question 20

How do you update rows in a database table?

Answer 20

update “tableName”
set “colA” = ‘ValA’,
“colC” = ‘valc’
where “colB’=’valB’

ex)

update “products”
set “price” = 100
where “productId” = 24;

Question 21

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

Answer 21

if you don’t, you’ll update every row in the table

Question 22

How do you delete rows from a database table?

Answer 22

delete from “tableName”
where “col1”=’val1’
returning*;

ex)

delete from “products”
where “productId” = 24
returning *;

Question 23

How do you accidentally delete all rows from a table?

Answer 23

If you don’t include a where clause

Question 24

What is a foreign key?

Answer 24

A column that’s common to two tables.

A column in a table that’s constrained to match values in a column in another table.

Question 25

How do you join two SQL tables?

Answer 25

select
      "columnA"
     "columnB" 
from "tableName1" 
join "tableName2" using ("foreignKeyColumn")
-or-
select....
from "tableName1"
join "tableName2" on "tableName1". "col1" = "tableName2" . "col1"

ex)
select *
from “products”
join “suppliers” using (“supplierId”);

Question 26

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

Answer 26

select “products”.”name” as “product”,
“suppliers”.”name” as “supplier”
from “products”
join “suppliers” using (“supplierId”);

select "p"."name" as "product",
       "p"."category",
       "s"."name" as "supplier",
       "s"."state"
  from "products" as "p"
  join "suppliers" as "s" using ("supplierId")
 where "p"."category" = 'cleaning'
   and "p"."price"    < 20;

Question 27

What are some examples of aggregate functions?

Answer 27

average()
sum()
max()
min()
count()

Question 28

What is the purpose of a group by clause?

Question 29

What do aggregate functions do?

Answer 29

Combine a bunch of values into a single value