PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

PostgreSQL is a free open source relational database management system

Alternative relational databases include: MySQL (also free), SQL Server by Microsoft, and Oracle by Oracle Corporation.

Question 2

What are some advantages of learning a relational database?

Answer 2

1. If you are storing related data, then a relational database is a good choice.
2. Relational databases support good guarantees about data integrity.
3. Relational databases are arguably the most widely used kind of database.
4. And knowing the SQL language is a very portable skill given that there are so many relational databases driven by almost the same language (SQL)

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

1. Use the terminal command sudo service postgresql status.
2. Check use the terminal top command to see if postgres is running

Question 4

What is a database schema?

Answer 4

A collection of tables is called a schema. A schema defines how the data in a relational database should be organized.

Question 5

What is a table?

Answer 5

A table is a set of relational data. A table is a list of rows, the rows all have the same attributes

Question 6

What is a row?

Answer 6

A row is a single instance of a record in a spreadsheet/table

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. It is a powerful way of retrieving, creating, and manipulating data in a relational database.

JavaScript is an imperative programming language, where you basically tell the JavaScript runtime what to do and how to do it.

SQL is a declarative programming language where programmers describe the results they want and the programming environment comes up with its own plan for getting those results. (like html & css)

Question 8

How do you retrieve specific columns from a database table?

Answer 8

Start with the select keyword.

The select keyword is followed by a comma-separated list of column names, each surrounded by “double quotes”.

e.g: select “firstName”,
“lastName”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

Use the where keyword followed by the column you want to filter by in “double quotes”, followed by an optional comparison operator, followed by an optional value of that column that you want to see in ‘single quotes’ (if the value is a number, do not use quotes at all.)

Question 10

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

Answer 10

< , > , = , !=

Question 11

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

Answer 11

Use the limit keyword followed by the number of rows you want to see.

e.g: limit 10;

Question 12

How do you retrieve all columns from a database table?

Answer 12

Use the select keyword followed by *

e.g: select *

Question 13

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

Answer 13

Use the order by keyword followed by the column you want to order by in “double quotes.” order will be in ascending order by default - add desc keyword after “specified column” to sort in descending order.

Question 14

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

Answer 14

Use the order by keyword followed by the column you want to order by in “double quotes.” order will be in ascending order by default - add desc keyword after “specified column” to sort in descending order.

Question 15

How do you add a row to a SQL table?

Answer 15

insert into “table” (“columnName”, “columnName”,… )
values (‘value’, ‘value’, ‘value’, …);

values are in the same order as the columns they belong to

Question 16

What is a tuple?

Answer 16

A list of values

Question 17

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

Answer 17

Multiple data rows can be inserted into a database table at once by specifying 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

after the insert into statement and the values statement, follow with
returning clause.

e.g:
insert into “products” (“name”, “description”, “price”, “category”)

values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’)

returning *;

Question 19

How do you update rows in a database table?

Answer 19

use the update keyword followed by the “table”
then use the set keyword followed by the “column” or “columns”, “columns” you want to update.
Follow with = ‘value’

Question 20

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

Answer 20

Unless you intend on updating all rows, include a where clause in your update statement to only target specific rows

Question 21

How do you delete rows from a database table?

Answer 21

Use SQL delete statement to remove rows from tables

Question 22

How do you accidentally delete all rows from a table?

Answer 22

delete from “table”;

since there is no where statement targeting specific rows - all rows are deleted (THERE IS NO UNDO!)

Question 23

What is a foreign key?

Answer 23

A column whose values refer to the values in a column on another table

A column that links one table to another

Question 24

How do you join two SQL tables?

Answer 24

Using the join keyword followed by the referenced table, followed by the using keyword followed by the connecting column

e.g:
join “otherTable” using (“columnAttribute”);

Question 25

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

Answer 25

Use the as keyword. (column is renamed just for the result.

e.g:
select “products”.”name” as “product”,
“suppliers”.”name” as “supplier”
from “products”
join “suppliers” using (“supplierId”);

Question 26

What are some examples of aggregate functions?

Answer 26

max( ), avg( ), count( ), min( ), sum( ), every( ), json functions are also available - for more: https://www.postgresql.org/docs/10/functions-aggregate.html

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