PostgreSQL

Question 1

What is PostgreSQL?

Answer 1

A free, open source Relational Database Management System. That provides robust features, meets standards compliance, and reliability.

Question 2

What are some advantages of learning a relational database?

Answer 2

• To understand and solve problems based around storing and pulling up data.
• To be able to reject bad data and not worry about half written data

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

• Check to see if postgresql processes are running in top command
• Type in sudo service postgresql status

Question 4

What are some alternative relational databases?

Answer 4

MySQL, SQL Server by Microsoft, and Oracle by Oracle.

Question 5

What is a database schema?

Answer 5

A collection of tables.

- Defines how data in a relational database is organized.

Question 6

What is a table?

Answer 6

A relational database that stores data in relations.

- It is a list of rows each having the same set of attributes.

Question 7

What is a row?

Answer 7

A list from a table that has the same attributes.

Question 8

What is SQL?

Answer 8

Stands for Structured Query Language

• Primary way of interacting with relational databases
• Retrieves, creates, and manipulates data in a relational database

Question 9

How do you retrieve specific columns from a database table?

Answer 9

select keyword
comma-separated list of column names surrounded by “ “
from clause which specifies which table to get data
ends with a semicolon

Question 10

How do you filter rows based on some specific criteria?

Answer 10

Using the where clause

• Comes after the from clause
• Column headers will have double quotes while text values have single quotes

Question 11

What are the benefits of formatting your SQL?

Answer 11

Creates a consistent style and therefore makes SQL easier to read

Question 12

How is SQL different from languages like JavaScript?

Answer 12

SQL is a declarative programming language.

- Programmer describes the results they want and the program comes up with a plan to get those results

Question 13

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

Answer 13

equals sign =
greater than >
less than <
not equal !=

Question 14

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

Answer 14

Using the limit clause

• This will come last in a select statement
• Is a literal integer

Question 15

How do you retrieve all columns from a database table?

Answer 15

Using an asterisk *

Question 16

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

Answer 16

Include an order by clause

• Comes after the from clause
• Followed by a column name within “ “
• Default order is ascending

Question 17

How do you add a row to a SQL table?

Answer 17

insert into “tableName” (“columnName”, “columnName”…etc)
values (‘valueName’, ‘valueName’… etc);

No need for id values because tables will automate that

Question 18

What is a tuple?

Answer 18

A list of values within SQL

Question 19

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

Answer 19

Have another line that contains a tuple

Use a comma after every subsequent tuple

Question 20

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

Answer 20

Use a returning clause

Question 21

How do you update rows in a database table?

Answer 21

update “tableName”
set “columnName” = value
where “rowId” = value;

Question 22

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

Answer 22

If a where statement isn’t included then it will update the whole table as opposed to the targeted row

Question 23

How do you delete rows from a database table?

Answer 23

delete from “tableName”
where “id” = value
returning *;

returning clause is optional

Question 24

How do you accidentally delete all rows from a table?

Answer 24

delete from “products”;

Not including a where clause…

Question 25

How do you update multiple columns in a row?

Answer 25

update “tableName”
set “columnName” = value,
“columnName” = value
where “id” = value;

Question 26

How do you delete multiple rows in SQL?

Answer 26

delete from “tableName”
where “columnName” = value
and “columnName” = value

Question 27

What safeguard do you use to protect data when making changes?

Answer 27

Do a transaction:
Use begin command on the sql file you want to do changes

To continue:
Use rollback command to go back
Commit command to commit

Question 28

What is a foreign key?

Answer 28

An id that links one table to another table

Question 29

How do you join two SQL tables?

Answer 29

select *
from “tableName”
join “tableName2” using (“tableName2Id”);

Question 30

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

Answer 30

Alias the column names by doing:

select “tableName”.”columnName” as “name”,
“tableName2”.”columnName2” as “name2”
from “tableName”
join “tableName2” using (“tableName2Id”);

Question 31

What are some examples of aggregate functions?

Answer 31

max( )
avg( )
count( )
min( )
sum( )
every( )

Question 32

What is the purpose of a group by clause?

Answer 32

To subdivide rows in a subset into groups and performs the aggregate in each group

To get aggregates from groups of data as opposed to the data in its entirety

Question 33

When would you use an aggregate function?

Answer 33

After the select clause

Example:
select avg(“columnName”) as “columnAlias”
from “tableName”;