PostgreSQL + SQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

a Relational Database Management System

uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads

MySQL, SQL, and Oracle SQL

Question 2

What are some advantages of learning a relational database?

Answer 2

1) let you store relational data– data that connect to one another in some way
2) relational databases support good guarantees about data integrity - developers can set up their database to reject “bad” data and not worried about data being “half written”
3) most widely used kind of database

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

1) by doing
sudo service postgresql status
to check its status

2) running top command
3) pgweb (the GUI will not work if postgreSQL is not running)

Question 4

What is a database schema?

Answer 4

its a blueprint of how your data will look

a collection of tables

it defines how the data in a relational database should be organized

Question 5

What is a table?

Answer 5

a table is where relational databases are stored

a table is a list of rows with the same set of attributes

ex: “customers” table could have “customerId” “firstName” “lastName” “email” “orderNumber”
ex: all students in a “students” table could have “firstName”, “lastName” and “dataOfBirthday” attributes

(attributes are referred to as columns)

Question 6

What is a row?

Answer 6

a row share the same set of attributes

Question 7

How do you retrieve specific columns from a database table?

Answer 7

select keyword
list of the attribute name
from the name of the table

Question 8

How do you filter rows based on some specific criteria?

Answer 8

where clause, attribute name, operator, condition

this expression evaluates to true of false

Question 9

What are the benefits of formatting your SQL?

Answer 9

make your code more readable

Question 10

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

Answer 10

=, , !=

Question 11

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

Answer 11

limit follow by maximum number you want

Question 12

How do you retrieve all columns from a database table?

Answer 12

by using asterisk *

Question 13

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

Answer 13

order by clause

Question 14

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

Answer 14

SQL (similar to CSS and HTML) is a declarative programming language where programmers have to DESCRIBE the results they want and the programming environment (Web browser) comes up with its own plan for getting those results

relational databases interpret SQL and then DYNAMICALLY GENERATE A PLAN OF ACTION to perform the programmer’s commands as efficiently as possible

Whereas JavaScript is an imperative programming language where you tell the JavaScript runtime what to do and HOW TO DO IT

Question 15

How do you add a row to a SQL table?

Answer 15

insert statement

Question 16

What is a tuple?

Answer 16

a list of values

Question 17

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

Answer 17

you can specify more than one tuple of values by separating them with a comma

Question 18

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

Answer 18

using the returning clause

returning *;
instead of * you can use a coma separated list of column names

Question 19

How do you update rows in a database table?

Answer 19

update statement

Question 20

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

Answer 20

including a where clause will target specific row, without it, it will update EVERY row in the table

Question 21

How do you delete rows from a database table?

Answer 21

delete statement with a where clause

Question 22

How do you accidentally delete all rows from a table?

Answer 22

by not specifying where / including a where clause

Question 23

What is a foreign key?

Answer 23

a column (a table identifier column) that is used to establish a link between the data in two tables

the column (aka foreign key) is used in another table (aka the foreign key table)

the VALUE from a foreign table that is used to link to another table??

Question 24

How do you join two SQL tables?

Answer 24

after SELECTing the table, use the JOIN statement to join another table

Question 25

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

Answer 25

by calling the column (if you’re joining columns) follow by a period follow by the column name and an AS keyword and then the name you’re changing it to

Question 26

What are some examples of aggregate functions?

Answer 26

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

Question 27

What is the purpose of a group by clause?

Answer 27

to separate rows into groups and perform aggregate functions on those groups of rows