SQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

PostgreSQL is a powerful, free, open source Relational Database Management System (RDBMS). Other popular 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

A quality of many relational databases is that they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible. This means that developers can set up their database to reject “bad” data and not worry about data being “half written”.

They are very widely used

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

Using the top command in one of your terminals to list the currently running processes

You can also check the status of the postgresql service:
sudo service postgresql status

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. In relational databases, you typically have to define your schema up front and the database server will make sure that any data being written to the database conforms to that schema.

Question 5

What is a table?

Answer 5

A table is a list of rows each having the same set of attributes. For example, all students in a “students” table could have “firstName”, “lastName”, and “dateOfBirth” attributes. Attributes are commonly referred to as columns. You might visualize a database table as a sort of spreadsheet where each row is a record in that spreadsheet.

Question 6

What is a row?

Answer 6

The row is where actual data is stored

Question 7

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

Answer 7

Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.

JavaScript is an imperative programming language, where you basically tell the JS runtime what to do and how to do it. Whereas, SQL is a declarative programming language. Programmers describe the results they want and the programming environment comes up with its own plan for getting those results (like HTML and CSS). The programmer simply “declares” their intent and the Web browser does its best to output the desired results according to a predefined set of rules.

Question 8

How do you retrieve specific columns from a database table?

Answer 8

Querying the data
- The query starts with the select keyword
- The select keyword is followed by a comma-separated list of column names, each surrounded by “ double quotes.
- The column names are followed by a from clause specifying which table to retrieve the data from.
- The query must end in a ; semicolon.
- SQL keywords such as select and from are not case-sensitive.
- SQL does not have to be indented, but you should do it anyway for consistent style and therefore readability.

i.e.

select “name”,
“price”
from “products”;

It is possible to select all of the columns in a table by replacing the list of column names with an * asterisk.

select *
from “products”;

• The order of the results is not predictable.
• The * asterisk is not in quotes.

Question 9

How do you filter rows based on some specific criteria?

Answer 9

This is done using a where clause.

select “productId”,
“name”,
“price”
from “products”
where “category” = ‘cleaning’;

Question 10

What are the benefits of formatting your SQL?

Answer 10

Database tables in large systems can contain millions of rows. As humans we cannot really understand that much information all at once. Plus, the types of problems that we are solving usually involve a few pieces of data at a time. So formatting allows us to interpret the data more efficiently. Easier to read, understand, and debug it.

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

SQL provides us with the ability to limit the number of rows included in a result set.

select “name”,
“description”
from “products”
order by “price” desc
limit 1;

Before we look at the result set, here are some things to note about the example select statement:

The limit clause comes last.
The limit clause includes a literal integer number with no quotes to specify the maximum number of rows that should be returned.
The sort order of the order by clause is switched to descending order with the desc keyword.

Question 13

How do you retrieve all columns from a database table?

Answer 13

It is possible to select all of the columns in a table by replacing the list of column names with an * asterisk.

select *
from “products”;

Question 14

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

Answer 14

The default sort order of the results is ascending order, but you can change it to descending

Question 15

How do you add a row to a SQL table?

Answer 15

An SQL insert statement is a means of adding rows to a table. Consider the following “products” table:

To add a row to this table, we would likely execute the following command:

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);

The statement begins with the insert keyword.
The table to insert into is specified in “ double quotes.
The list of columns being inserted is wrapped in ( ) parenthesis.

The values being inserted are also wrapped in ( ) in parenthesis in the same order as the columns they belong to. In SQL, a list of values is referred to as a tuple.

Text values are wrapped in ‘ single quotes.
Numeric values are represented with literal numbers (or decimals if applicable).

In this particular statement, the “productId” was left out. This is because tables are often configured to auto-generate identifier attributes to avoid accidental duplicates.

Question 16

What is a tuple?

Answer 16

The values being inserted are also wrapped in () in parenthesis in the same order as the columns they belong to. In SQL, a list of values is referred to as a tuple.

Question 17

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

Answer 17

Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas. Below we are inserting two new rows into the “products” table.

insert into “products” (“name”, “description”, “price”, “category”)

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

   ('Tater Mitts', 'Scrub some taters!', 6, 'cooking') returning *;

Question 18

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

Answer 18

returning *;

If you only want specific values back, you can use a comma-separated list of column names instead of an * asterisk.

returning “name”, “language”;

Question 19

How do you update rows in a database table?

Answer 19

An SQL update statement is a means of updating rows in a database table. Consider the following “products” table:

To make an update we might execute the following command:

update “products”
set “price” = 100;

HOWEVER there is a serious problem with this query as it would update every row in the table!

Therefore, great care must be taken to include a where clause in your update statements to only target specific rows:

update “products”
set “price” = 100
where “productId” = 24;

Question 20

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

Answer 20

To target only specific rows, so that it is not updating every row in the table

Question 21

How do you delete rows from a database table?

Answer 21

An SQL delete statement is how rows get removed from tables. Consider the following “products” table:

To delete from this table, we might execute the following command:

delete
from “products”;

💀💀💀💀 WE JUST DELETED EVERYTHING FROM THE TABLE! and there is no undo. 😢

You must be extremely careful when deleting things from a table as it cannot be undone. Let’s try that again. We only want to delete the ShakeWeight because, well, it’s ridiculous. We can target specific rows to delete by including a where clause. As with insert and update statements, we can include a returning clause if we want the affected row(s) returned to us. A returning clause is not mandatory though.

If we wanted to delete all “products” in the ‘cleaning’ category that are cheaper than 20, we would:

delete
from “products”
where “category” = ‘cleaning’
and “price” < 20

Question 22

How do you accidentally delete all rows from a table?

Answer 22

delete
from “products”;

If a where clause is not used

Question 23

What is a foreign key?

Answer 23

This is known as a foreign key. Instead of putting all of the supplier information for a product into the product row itself, there is instead just one column that links the “products” table to the “suppliers” table.

Question 24

How do you join two SQL tables?

Answer 24

An SQL join clause is a way of combining data from two different database tables into one result set.

select *
from “products”
join “suppliers” using (“supplierId”);

We are selecting all columns from both the “products” table and the “suppliers” table.
The join clause follows the from clause.
The join clause is instructing the database server to link the two tables by their “supplierId” column.

Question 25

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

Answer 25

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

We may want to know the highest price in the entire table. We’d use the max() aggregate function. It’s not strictly required that you alias an aggregate (with as), but it’s good practice because it communicates your intent more clearly.

select max(“price”) as “highestPrice”
from “products”;

If we wanted to know the average price, there’s an avg() aggregate function for that:

select avg(“price”) as “averagePrice”
from “products”;

Or maybe we just want to know the number of rows in the “products” table. We’d use the count() aggregate function:

select count(*) as “totalProducts”
from “products”;

There are many aggregate functions available to you in SQL, including min( ), sum( ), and every( ).

Question 27

What is the purpose of a group by clause?

Answer 27

The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions.