postgresSL / SQL

Question 1

Why do we use databases in Web development?

Answer 1

To store data to be quickly retrieved, esp when it’s complex data and needs to be organized
They can also be used by many users and it provides a central location

Question 2

What is PostgreSQL and what are some alternative relational databases?

Answer 2

PostgreSQL is a relational database.

Other popular relational databases include MySQL (also free), SQL Server by Microsoft, and Oracle by Oracle Corporation.

Question 3

What are some advantages of learning a relational database?

Answer 3

Good data integrity (rejects “bad” data) (see below)

A quality of many relational databases is that they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible. This means that developers can set up their database to reject “bad” data and not worry about data being “half written”.

Question 4

What is one way to see if PostgreSQL is running?

Answer 4

also, using the “sudo service postgresql status” command

using the “top” command and watching for it in the list of processes

“sudo” stands for “super user do”, aka an admin LOL

Question 5

what command starts the postgres database?

Answer 5

sudo service postgresql start

Question 6

what command stops the postgres database?

Answer 6

sudo service postgresql stop

Question 7

what command checks postgrest status?

Answer 7

sudo service postgresql status

Question 8

what does the pgweb command do?

Answer 8

it runs a server and lets you access from the browser, it queries your databases and gives you a GUI - graphical user interface, or webpage, to work in

Question 9

What is a database schema?

Answer 9

a collection of relations (relations are like tables with rows & columns)

or, definition of all that describes your database

Question 10

What is a table?

Answer 10

it has rows and columns for data, and in coding is called a relations

Question 11

What is a row?

Answer 11

horizontal grid section with attributes in each column

like an object

a row is like an instance of a class

Question 12

What is an attribute and what other names are used to describe them?

Answer 12

a column

Question 13

what is a “primary key”

Answer 13

like an ID, basically

it uniquely identifies the row

Question 14

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

Question 15

How do you retrieve specific columns from a database table?

Question 16

How do you filter rows based on some specific criteria?

Question 17

What are the benefits of formatting your SQL?

Answer 17

readability, consistency

code is read many more times than it is written!

Question 18

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

Answer 18

< > = !=

Question 19

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

Answer 19

use the limit keyword

Question 20

How do you retrieve all columns from a database table?

Answer 20

use “*”

Question 21

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

Answer 21

type “order by”

Question 22

How do you add a row to a SQL table?

Question 23

What is a tuple?

Answer 23

a list of values in parentheses

Question 24

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

Answer 24

use a comma and add another tuple, e.g.,

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 25

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

Question 26

How do you update rows in a database table?

Answer 26

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

Question 27

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

Answer 27

so you don’t delete ALL!

Question 28

How do you delete rows from a database table?

Answer 28

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

Question 29

How do you accidentally delete all rows from a table?

Answer 29

by not adding “where”

Question 30

What is a foreign key?

Answer 30

A key that is given by joining another table using the other table’s primary key

Question 31

How do you join two SQL tables?

Answer 31

using “join …. using … “

Question 32

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

Answer 32

use “as”

Question 33

how do you find the “key”

Answer 33

find the column that uniquely identifies each item

if you can’t find it just look in the “constraints” tab in the gui

Question 34

What are some examples of aggregate functions?

Answer 34

count() , sum(), total() …

Question 35

What is the purpose of a group by clause?

Answer 35

to group sth by a certain parameter
(can do it manually using other ways as leanred from previous exercises)

Question 36

What are JavaScript classes?

Question 37

When would you want to use a class?

Question 38

How do you declare a class?

Question 39

How do you inherit from another class?

Question 40

Why would you want to inherit from another class?