PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

A relational database; MySQL, SQL Server, Oracle

Question 2

What is a database schema?

Answer 2

All of the tables in a database

Question 3

What is a table?

Answer 3

Specifies each attribute the rows should have

Question 4

What is a row?

Answer 4

A single object in the table

Question 5

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

Answer 5

SQL is a language used to retrieve, create, and manipulate data in a relational database. It differs from JavaScript in that is a declarative programming language, so the programmer describes the results they want and the programming environment comes up with its own plan for getting those results.

Question 6

How do you retrieve specific columns from a database table?

Answer 6

With the SELECT keyword, column names enclosed with double quotes and separated by commas.

Question 7

How do you filter rows based on some specific criteria?

Answer 7

With a WHERE keyword, followed by a column you wanted to filter by enclosed in double quotes, then a comparison operator, then the value to compare against.

Question 8

What are the benefits of formatting your SQL?

Answer 8

To enhance readability for other developers.

Question 9

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

Answer 9

= , < , > , !=

Question 10

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

Answer 10

With the LIMIT keyword, followed by an integer.

Question 11

How do you retrieve all columns from a database table?

Answer 11

By replacing the list of column names with an asterisk.

Question 12

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

Answer 12

With an ORDER BY keyword, followed by the column you want to sort by enclosed in double quotes, and then an optional DESC keyword to reverse sort order.

Question 13

How do you add a row to a SQL table?

Answer 13

With an insert statement;
Insert into keywords followed by table name enclosed in double quotes, followed by an attribute list enclosed in parentheses. Then the values keyword followed any tuples.

Question 14

What is a tuple?

Answer 14

A tuple is a set of values for one row.

Question 15

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

Answer 15

By adding multiple tuples after the values keyword in an insert statement, with commas in between.

Question 16

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

Answer 16

With the returning keyword followed by an asterisk.

Question 17

How do you update rows in a database table?

Answer 17

With an update statement;
Update keyword, followed by table name enclosed in double-quotes, then the set keyword followed by the name of the attribute to be updated, then the assignment operator and whatever you are going to replace the value with.

Question 18

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

Answer 18

Because it will update every row in the table if you leave it out.

Question 19

How do you delete rows from a database table?

Answer 19

With a delete statement;
Delete from keywords, followed by the table name enclosed in double-quotes, then the where keyword followed by the name of the attribute to be checked, followed by a conditional operator and the value you are checking against.

Question 20

How do you accidentally delete all rows from a table?

Answer 20

By not specifying a where clause in your delete statement.

Question 21

What is a foreign key?

Answer 21

An attribute shared by two tables that contain the same value.

Question 22

How do you join two SQL tables?

Answer 22

By adding a join clause to a select statement;
‘join’ keyword followed by the name of the table to be joined enclosed in double-quotes, then the ‘using’ keyword followed by the attribute which the tables are linked by, enclosed in double-quotes and wrapped with parentheses.

select “products”
join “suppliers” using (“supplierId”)

Question 23

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

Answer 23

With aliasing;
We do this by adding an ‘as’ keyword after the attribute name in the select statement, followed by the alias wrapped in double-quotes.

select “products”.”name” as “product”

Question 24

What are some examples of aggregate functions?

Answer 24

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

Question 25

What is the purpose of a group by clause?

Answer 25

To separate rows into groups and perform aggregate functions on those groups of rows.