Postgres

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

It is a place to store data. It is an open source relational Database meaning that the data is related to other data that you are storing or the data is interconnected. Redis, MySQL, Oracle

Question 2

What are some advantages of learning a relational database?

Answer 2

It is one of the most widely used kind of database. Learning SQL is a transferable skill because a lot of relational databases are driven by the same language. Guarantees data integrity through ACID.
Atomicity consistency isolation and durability.

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

using sudo service postgresql status

Question 4

What does the ACID acronym stand for?

Answer 4

Atomicity consistency isolation and durability.

Question 5

Atomicity

Answer 5

Each statement is treated as a single unit. Meaning that if one unit fails all the units fail. Meaning there are no half transfers.

Question 6

Consistency

Answer 6

transactions can only bring the database from one valid state to another.

Question 7

Isolation

Answer 7

The database is in the same state. One transaction does not effect other transactions

Question 8

Durability

Answer 8

That once a transaction has been committed it will stay committed even if there is a crash.

Question 9

What is a database schema?

Answer 9

schema means plan or organization so its an abstraction that represents the storage model of data in a database. In a relational database schema tables will be interconnected to one another.

Question 10

What is a table?

Answer 10

It’s own entity. Would be an object that relates to other objects within the schema. Think Actor film all different tables that represent an object but relate to each other

Question 11

What is a row?

Answer 11

a row is a value that is matched with a property which is a column/ attributes.

Question 12

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

Answer 12

Structured Query Language used for managing data in a relational data base. SQL is declarative meaning that you describe the results you want and the database will try to match your input

Question 13

How do you retrieve specific columns from a database table?

Answer 13

you use a select statement and grab the attributes/columns from the table that you choose.

Question 14

How do you filter rows based on some specific criteria?

Answer 14

you use a where clause with a comparison operator and a value

Question 15

What are the benefits of formatting your SQL?

Answer 15

It is easier to read

Question 16

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

Answer 16

greater than less than not equal to and equal to

Question 17

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

Answer 17

you use the limit clause with a literal integer number

Question 18

How do you retrieve all columns from a database table?

Answer 18

use the *

Question 19

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

Answer 19

order by clause

Question 20

How do you add a row to a SQL table?

Answer 20

you use an insert statement
insert into “table” (name of your columns without the columns that get added automatically)
a tuple

Question 21

What is a tuple?

Answer 21

it is a list of values

Question 22

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

Answer 22

you add additional tuples with a comma after each besides the last tuple

Question 23

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

Answer 23

returning clause with a *

Question 24

How do you update rows in a database table?

Answer 24

update keyword table name

set “column name” and then equal = ‘value’

Question 25

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

Answer 25

So that it knows where to update or it would update every row in the table

Question 26

How do you delete rows from a database table?

Answer 26

delete statement from table

with a where clause

Question 27

How do you accidentally delete all rows from a table?

Answer 27

delete from table name with a semicolon not specifying where

Question 28

What is a foreign key?

Answer 28

Foreign key is a category that refers to another key in another table. Descendants

Question 29

How do you join two SQL tables?

Answer 29

select “a column”
from “a table”
join clause linking the two tables together
using keyword that links them together

Question 30

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

Answer 30

You use an alias name and then you have to use an as keyword later on
similar to using a variable

Question 31

What are some examples of aggregate functions?

Answer 31

max() avg() count()

expression in the middle

Question 32

What is the purpose of a group by clause?

Answer 32

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