PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

• PostgreSQL is a powerful, free, open source Relational Database Management System (RDBMS)
• MySQL, SQL Server, ORACLE

Question 2

What are some advantages of learning a relational database?

Answer 2

• widely used
• data integrity: can store and modify data in a way that makes data corruption as unlikely as possible
• developers can set up their database to reject “bad”/”half-written” data

Question 3

What is one way to see if PostgreSQL is still running?

Answer 3

• top
• sudo service postgresql status
• try connecting to it

Question 4

What is a database schema?

Answer 4

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

Question 5

What is a table?

Answer 5

• relational databases store data in relations, commonly referred to as tables
• a list of rows each having the same set of attributes

Question 6

What is a row?

Answer 6

a list of attributes that are all the same across each row

Question 7

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

Answer 7

• Structured Query Language (SQL) is the primary way of interacting with relational databases.
• SQL is a declarative programming language. In declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results. (Whereas imperative languages like JavaScript you basically tell the JavaScript runtime what to do and how to do it.)

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “columnName”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

use a ‘where’ clause - an expression that has to evaluate to true or false - so can use < > = etc..

Question 10

What are the benefits of formatting your SQL?

Answer 10

it’s easier to read - easier to debug - easier to remove stuff

Question 11

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

Answer 11

, =, !=

Question 12

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

Answer 12

use a ‘limit’ clause

Question 13

How do you retrieve all columns from a database table?

Answer 13

*

Question 14

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

Answer 14

use ‘order by’ clause

Question 15

How do you add a row to a SQL table?

Answer 15

• use the insert clause
• insert into “tableName” ( “colName”, “colName”, …)
  values ( ‘colValue’ , ‘colValue’, …)
  returning *;

Question 16

What is a tuple?

Answer 16

a list of ordered values ( )

Question 17

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

Answer 17

• specify more than one tuple of values, separated by commas

Question 18

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

Answer 18

• use ‘returning * ‘ clause after the values tuple

Question 19

How do you update rows in a database table?

Answer 19

• using the ‘update’ clause
• update “tableName”
  set “colName” = ‘colValue’ ,
  “colName” = ‘colValue’
  where “colName” = value

Question 20

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

Answer 20

• without specifying a certain row using the where clause after setting the value, the update statement would update ALL the rows!

Question 21

How do you delete rows from a database table?

Answer 21

• using the delete from keyword
• delete from “tableName”
  where “colName” = ‘colValue’
  returning * ;

Question 22

How do you accidentally delete ALL rows from a table?

Answer 22

delete from “tableName” ; BAD! - needs a where clause after the delete

Question 23

What is a foreign key?

Answer 23

one column that links one table to another table (exists in both table with the same values)

Question 24

How do you join two SQL tables?

Answer 24

• use the join clause

Question 25

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

Answer 25

using aliasing

- “tableName”.”colName” as “alias”

Question 26

What are some examples of aggregate functions?

Answer 26

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

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.