Databases/postgreSQL

Question 1

What is PostgreSQL?

Answer 1

“Post Gress Queue El”

relational database management system

Question 2

What are some alternative relational databases?

Answer 2

Examples: MySQL, Amazon Aurora, Google Fusion Tables, Oracle

Question 3

What are some advantages of learning a relational database?

Answer 3

Most widely used type of database
Good for storing related data
Good for supporting data integrity
Portable skill

Question 4

What is one way to see if PostgreSQL is running?

Answer 4

top

sudo service postgresql status

Question 5

What is a relational database?

Answer 5

A digital database based on the relational model of data, where all data is represented in terms of tuples, grouped into relations)
**commonly referred to as “SQL databases” because you usually do work with them using some variation of the SQL language

Question 6

What is a database schema?

Answer 6

A collection of tables, defines how the data in a relational database should be organized.
**Schema needs to be defined up front, server will make sure any data being written to database conforms to this schema

Question 7

What is a table?

Answer 7

(AKA relations) a list of rows that each have the same set of attributes

Question 8

What is a row?

Answer 8

Line of data with values for each attribute

Question 9

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

Answer 9

Structured Query Language - primary way of interacting with and managing relational databases (retrieving, creating, and manipulating data in a relational database)
**declarative - describe results wanted and environment comes up with how to get those results

Question 10

How do you retrieve specific columns from a database table?

Answer 10

select statement:

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

Question 11

How do you filter rows based on some specific criteria?

Answer 11

where (clause) “columnName” = ‘specificValue’;

Question 12

What are the benefits of formatting your SQL?

Answer 12

Easier to read, easier to see what data is being requested

Question 13

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

Answer 13

, <=, =>, =, != (predicate to truthy or falsy)

Question 14

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

Answer 14

limit # (clause) ;

Question 15

How do you retrieve all columns from a database table?

Answer 15

select *

from “tableName”

Question 16

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

Answer 16

order by “columnName”

Question 17

Declarative (SQL, CSS, HTML) vs. imperative (JavaScript) programming language

Answer 17

imperative - tell what to do and how to do it

declarative - describe results wanted and environment comes up with how to get those results

Question 18

How do you add a row to a SQL table?

Answer 18

**insert statement
insert into “tableName” (“columnName”, “columnName”)
values (‘value’, ‘value)

Question 19

What is a tuple?

Answer 19

a list of values (‘value1’, ‘value2’)

finite ordered list

Question 20

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

Answer 20

listing more than one tuple (list of values) which are separated by a comma

Question 21

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

Answer 21

returning *

Question 22

How do you update rows in a database table?

Answer 22

update “tableName”
set “columnName” = newValue
where “columnName” = oldValue;

Question 23

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

Answer 23

So that it doesn’t update every single row and only targets specific rows

Question 24

How do you delete rows from a database table?

Answer 24

delete from “tableName”
where “columnName” = ‘value’
returning *

Question 25

How do you accidentally delete all rows from a table?

Answer 25

delete from “tableName”

**with no where clause

Question 26

What is a foreign key?

Answer 26

values in one column from a column on a different table

Question 27

How do you join two SQL tables?

Answer 27

select “tableName1”.”columnName1” as “aliasColumnName1”,
“tableName2”.”columnName2” as “aliasColumnName2”,
from “tableName1”
join “tableName2” using (“columnName”);

Question 28

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

Answer 28

as clause

Question 29

What are some examples of aggregate functions?

Answer 29

max()
avg()
every()
bool_and()

Question 30

What is the purpose of a group by clause?

Answer 30

To determine how the resulting table will output and which column it should be sorted/grouped by, how to group rows together