PostgreSQL

Question 1

What are some advantages of learning a relational database?

Answer 1

Relation databases are used to store related data and can store and modify that data in a way that makes data corruption as unlikely as possible. They are also widely used - the most popular databases used in most businesses.

Question 2

What is PostgreSQL and what are some alternative relational databases?

Answer 2

PostgreSQL is a free, open-source relational database management system (RDBMS). It is its own database server, its own program.
We are simply using the database.
It has a robust feature set, standards compliance, and reliability. Relation databases are used to store related data and can store and modify that data in a way that makes data corruption as unlikely as possible.

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

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

The command sudo service postgresql status.

Question 4

What is pgweb?

Answer 4

Pgweb is a web-based database browser for PostgreSQL, written in Go and works on OSX, Linux and Windows machines. Main idea behind using Go for backend development is to utilize ability of the compiler to produce zero-dependency binaries for multiple platforms. Pgweb was created as an attempt to build very simple and portable application to work with local or remote PostgreSQL databases.

Question 5

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

Answer 5

SQL is the primary way of interacting with relational databases. It is how we CRUD data.

It’s a declarative programming language, different from imperative languages like JS because rather than telling it what to do and how to do it, the developer describes or declares the sought after results, and the database server dynamically generates a plan of action to get those results.

Question 6

How do you retrieve specific columns from a database table?

Answer 6

Using the ‘select’ keyword followed by a comma delineated list of column names in double quotes.

Will also need to include the ‘from’ keyword and the table you’re retreiving from.
You can optionally add some filtering features with the ‘where’ clause or the ‘order by’ clause.

Question 7

How do you filter rows based on some specific criteria?

Answer 7

Using the ‘where’ keyword followed by the filter column name, a comparison operator, and the value used to filter in single quotes.

Question 8

What are the benefits of formatting your SQL?

Answer 8

Legibility and consistent style.

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

Using the ‘limit’ keyword and a numerical value, not in quotes.

Question 11

How do you retrieve all columns from a database table?

Answer 11

Using the ‘select’ keyword with an asterisk * (universal operator?)

Question 12

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

Answer 12

Using the ‘order by’ clause. You can optionally add ‘desc’ to the end for descending order (ascending is default).

Question 13

How do you add a row to a SQL table?

Answer 13

Using an insert statement followed by the name of the table and a comma delineated list of column names wrapped in parentheses, then below that, the values keyword followed by a comma delineated list of respective values in single quotes wrapped in parentheses. Optionally, you can add a returning keyword to the end to see the inserted data with the autogenerated column data.

Question 14

What is a tuple?

Answer 14

In SQL, a list of values is referred to as a tuple.

Question 15

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

Answer 15

Entering a comma delineated list of tuples, each wrapped in its own set of parentheses

Question 16

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

Answer 16

The returning keyword and * if you want all the column data, or a specified list of columns.

Question 17

How do you delete rows from a database table?

Answer 17

Using a ‘delete’ statement followed by the table name, and then a ‘where’ clause with an associated or identifying tuple.

Question 18

How do you accidentally delete all rows from a table?

Answer 18

By not including a ‘where’ clause.

Question 19

How do you delete rows from a database table?

Answer 19

Using a ‘delete’ statement followed by the table name, and then a ‘where’ clause with an associated or identifying tuple.

Question 20

How do you update rows in a database table?

Answer 20

Update keyword followed by the table name, then the ‘set’ keyword followed by the category or column and then the equal sign and then the values, then probably a ‘where’ clause indicating specificity.

Question 21

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

Answer 21

It gives you control over the rows you’re updating otherwise you will override the column in all of the rows.

Question 22

How do you accidentally delete all rows from a table?

Answer 22

By not including a where clause.

Question 23

What is a foreign key?

Answer 23

The reference Id/value in one table to data from another table, in which the foreign key is the primary key or primary ID.

Question 24

How do you join two SQL tables?

Answer 24

Using the ‘select’ keyword followed by the data you want using dot notation to join the table and column name where appropriate, then a ‘from’ keyword defining the starting point of your joined tables, then the ‘join’ clause including the second table name and the’ using’ keyword followed by related key or column.

Question 25

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

Answer 25

Using the as keyword in either the ‘select statements or in the from or join` clauses after the associated table names to assign aliases