PostgresQL/SQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

It is a relational database. Alternative relational databases include MySQL, SQL Server by Microsoft and Oracle.

Question 2

What are some advantages of learning a relational database?

Answer 2

Most widely used type of database. You can store related data easily

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

Run ‘sudo service postgresql status’ in the terminal. Can also check in your terminal by running the command ‘top’

Question 4

What is a database schema?

Answer 4

A collection of tables. A description of how the database should be structured

Question 5

What is a table?

Answer 5

A list of rows with the same set of attributes

Question 6

What is a row?

Answer 6

A set of related data describing one item

Question 7

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

Answer 7

The primary way of interacting with relational databases. It is a declarative programming language where programmers describe the results they want rather than tell it exactly what to do and how to do it.

Question 8

How do you retrieve specific columns from a database table?

Answer 8

Using the select statement and a comma-separated list of column names, each wrapped in a pair of quotes, followed by the from clause specifying which table to grab the data from and ending with a semicolon.

Question 9

How do you filter rows based on some specific criteria?

Answer 9

Using the where clause. It needs to evaluate as true or false

Question 10

What are the benefits of formatting your SQL?

Answer 10

It gives it a consistent style and it’s easier to read.

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

Using the limit clause followed by the number you want to limit it by

Question 13

How do you retrieve all columns from a database table?

Answer 13

By using *

Question 14

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

Answer 14

Using the order by clause. It is defaulted to ascending, so if you want it descending, you must include ‘desc’ at the end.

Question 15

How do you add a row to a SQL table?

Answer 15

Using the insert ‘keyword into statement’ followed by the name of the table in quotes and a list of columns individually in quotes being inserted wrapped in parentheses. The values clause and the values being inserted are also wrapped in parentheses in the same order as the columns.

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

Comma separated list of tuples.

Question 18

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

Answer 18

Using the returning clause.

Question 19

How do you update rows in a database table?

Answer 19

Using the ‘update’ statement followed by the table in quotes and the set clause reassigning the column to the new value. Include the where clause to target specific rows

Question 20

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

Answer 20

Otherwise you update the entire column.

Question 21

How do you delete rows from a database table?

Answer 21

Using the delete from statement followed by the table to be referenced to and a where clause to select the row/rows.

Question 22

How do you accidentally delete all rows from a table?

Answer 22

“Delete from ‘products’”

Question 23

What is a foreign key?

Answer 23

A column that specifically refers to values from another table - a shared attribute

Question 24

How do you join two SQL tables?

Answer 24

Using the join clause following the ‘from’ clause.Join tables ‘using’ the attribute to join them with.

Question 25

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

Answer 25

By using “as” after you select the table and column

Question 26

What are some examples of aggregate functions?

Answer 26

Max, sum, avg, count

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.