postgreSQL, SQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

a powerful, free, open source Relational Database Management System (RDBMS). It is often cited as the most advanced open source database of its kind.

alternative relational db: MySQL, SQL Server by Microsoft, and Oracle by Oracle Corporation.

Question 2

What are some advantages of learning a relational database?

Answer 2

Many problem domains can be modeled well using a relational database.
they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible.
the most widely used kind of database
satisfies ACID properties
doesnt require a complex structure

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

‘sudo service postgresql status’

top command

Question 4

pgweb

Answer 4

is just a web interface for interacting w/ postgresql (so its easier)
there are many web interfaces like pgweb, this is not the only one

Question 5

psql

Answer 5

what you write in terminal to give commands w/ postgresql

Question 6

What is a database schema?

Answer 6

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

the structure of the whole database, the “blueprint”

Question 7

What is a table?

Answer 7

list of rows each having the same set of attributes.

defines what attribures(column) each row should have

Question 8

What is a row?

Answer 8

a single record of data in a table

Question 9

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

Answer 9

primary way of interacting with relational databases.
It is a powerful way of retrieving, creating, and manipulating data in a relational database.

SQL is a declarative programming language while JS is a imperative programming language

(declarative: programmers describe the results they want and the programming environment comes up with its own plan for getting those results)
(imperative: you basically tell the JavaScript runtime what to do and how to do it)

Question 10

How do you retrieve specific columns from a database table?

Answer 10

select “column name”,
“column name”,
etc.
from “name of table”

Question 11

How do you filter rows based on some specific criteria?

Answer 11

where “column name” = or > or < or != ‘criteria’

Question 12

What are the benefits of formatting your SQL?

Answer 12

cleaner, easier to read, easier to tell what is going on

Question 13

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

Answer 13

= < > !=

Question 14

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

Answer 14

limit clause

give num of rows you want at most

Question 15

How do you retrieve all columns from a database table?

Answer 15

• (an asterisk) instead of column names

Question 16

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

Answer 16

order by “column name”

*will be ascending order by default
can use order by “column name” desc for it to be in descending order

Question 17

How do you add a row to a SQL table?

Answer 17

insert into “table name” (column1 name, column2 name, etc)

values (value for column1, value for column2, etc)

Question 18

What is a tuple?

Answer 18

a list of values (in SQL)

a row/a record

Question 19

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

Answer 19

list tuples separated by commas

Question 20

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

Answer 20

returning keyword *;
or
returning keyword specific column names;
(if you don’t want entire row returned)

Question 21

How do you update rows in a database table?

Answer 21

update "table name"
      set "column name" = 'new value',
            "column name" = 'new value',
                                    etc.
 where "column name" = < > != 'value/condition';

Question 22

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

Answer 22

if there is no where clause, it will update every row of table

Question 23

How do you delete rows from a database table?

Answer 23

delete from “table name”

where “column name” = > < != ‘value/condition’

Question 24

How do you accidentally delete all rows from a table?

Answer 24

by not adding a where clause!!

Question 25

What is a foreign key?

Answer 25

a column in a table where its values are constrained to match that of a column of another table
can be used to join tables together

constraint that one column has to match the values of another column in another table

Question 26

How do you join two SQL tables?

Answer 26

join keyword

select "column1",
            "column2",
                 etc.
   from "table1 name"
     join "table2 name" using ("foreign key")

Question 27

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

Answer 27

as keyword

select “columns1”
from “columns” as “all columns”

Question 28

What are some examples of aggregate functions?

Answer 28

max(), sum(), count(), min(), average() etc

Question 29

What is the purpose of a group by clause?

Answer 29

useful when you want to separate rows into groups and perform aggregate functions on those groups of rows

arrange multiple rows into one row based on a set of conditions or a value of a single row
groups row into a single row based on criteria (one or more columns to group by)
*all rows with same value in the stated columns will collapse into one row

Question 30

what do aggregate functions do?

Answer 30

convert a bunch of values into a single value
performs a calculation on a set of values, and returns a single value.

similar to array.reduce()?