PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

PostgreSQL (Structured Query Language) is a powerful, free, open source, Relational Database Management System (RDBMS) that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

MySQL, SQP Server by Microsoft, and Oracle by Oracle Corporation

Question 2

What are some advantages of learning a relational database?

Answer 2

They support good guarantees about data integrity. They 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” data and not worry about data being “half-written”

All relational databases share the same syntax. Data you store can be compacted well. Does not require a complex structure - simple model. Data accuracy and integrity, high security. Flexibility, can combine or update data, joining tables.

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

Check top to list currently running processes

Question 4

What is a database schema?

Answer 4

A collection of tables. A schema defines structure - how the data in a relational database should be organized. In relational databases, you typically have to define your schema up front and the database server will make sure that any data being written to the database conforms to that schema.

Question 4

What is a database schema?

Answer 4

A collection of tables. A schema defines structure - how the data in a relational database should be organized. In relational databases, you typically have to define your schema up front and the database server will make sure that any data being written to the database conforms to that schema.

Question 5

What is a table?

Answer 5

Relational databases store data in relations, commonly referred to as tables.
A table is a list of rows each having the same set of attributes .
Tables define what attributes (columns) that each row should have.

Question 6

What is a row (tuple)?

Answer 6

A single record of data. Each row is an instance of a row type.

Question 7

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

Answer 7

SQL (structured query language) is the 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 whereas JavaScript is an imperative language where you tell the JavaScript runtime what to do and how to do it. In declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results.

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “column1”,
“column2”
from “table”;

Question 9

How do you filter rows based on some specific criteria?

Answer 9

select “column1”,
       “column2”
       “column3”
  from “table”
 where “col-filter” = ‘value’;

Question 10

What are the benefits of formatting your SQL?

Answer 10

Consistent style and readability

Question 11

What are four comparison operators that can be used in awhereclause?

Answer 11

= , != , < , >

Question 12

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

Answer 12

select “column1”,
       “column2”
  from “table”
 order by “col-order” desc
 limit 1;

Question 13

How do you retrieve all columns from a database table?

Answer 13

select *
from “table”;

Order of results is NOT predictable

Question 14

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

Answer 14

select *
from “table”
order by “column”;

Default sort order is ascending order
Add desc to sort descending order

Question 15

How do you add a row to a SQL table?

Answer 15

Insert statement

insert into “table” (“col1”, “col2”, “col3”)
values (‘val1’, ‘val2’, num3)
returning *;

Question 16

What is a tuple?

Answer 16

A list of values
A tuple is stored as a row/record
A data structure that looks like a JavaScript array, each position refers to a specific attribute

Question 17

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

Answer 17

Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.

insert into “table” (“col1”, “col2”, “col3”)
values (‘val1’, ‘val2’, num1)
(‘val3’, num2, ‘val4’)
returning *;

Question 18

How do you get back the row being inserted into a table without a separateselectstatement?

Answer 18

returning *

Question 19

How do you update rows in a database table?

Answer 19

Update statement

update “table”
  set “col1” = ‘val1’
          “col2” = num,
          “col3” = ‘val2’
 where “col” = ‘val’ or num
returning *;

Question 20

Why is it important to include awhereclause in yourupdatestatements?

Answer 20

Need to specify to only target specific rows

Question 21

Why is it important to include awhereclause in yourupdatestatements?

Answer 21

Need to specify to only target specific rows

Question 22

How do you delete rows from a database table?

Answer 22

delete from “table”
where “col” = num or ‘val’
returning *;

Question 23

How do you accidentally delete all rows from a table?

Answer 23

delete from “table”

Without specifying a where clause

Question 24

What is a foreign key?

Answer 24

A foreign key is a field (or collection of fields) in one table, that refers to the primary key in another table. The table with the foreign key is called the child table, and the table with the primary key is the called the referenced or parent table.

Question 25

How do you join two SQL tables?

Answer 25

Join clause

select *
from “table1”
join “table2” using “colId”;

Question 26

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

Answer 26

Aliasing names using ‘as’ keyword

select “table1”.”col” as “newcolname”,
“table2”.”col” as “newName”
from “table1”
join “table2” using “colId”;

Question 27

What are some examples of aggregate functions?

Answer 27

max(), avg(), count()

Question 28

What is the purpose of agroup byclause?

Answer 28

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