PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

PostgreSQL is a relational database
Alternatives: Redis, MongoDB, Neo4j

Question 2

What are some advantages of learning a relational database?

Answer 2

SQL (structured query language) language/jargon is used in many relational databases, so skills are transferable

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

Sudo service postgresql status
Sudo elevates you to administrative privileges
Echo $(whoami) shows the current user name
**usually asks for password
Service
Managing a background process
Postgresql status
Status tells you if it’s running or not
Start will start the program
Stop will stop the program

Question 4

What is a database schema?

Answer 4

Collection of tables
Rules of how the data can be stored
The structure of the database
If there’s no schema, usually in non-relational databases

Question 5

What is a table?

Answer 5

List of rows with the same of attributes(columns)

Question 6

What is a row?

Answer 6

Collection of attributes for a single row in a table

Question 7

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

Answer 7

“Structured Query Language” is a declarative programming language, where programmers describe the results they want and the programming environment comes up with its own plan for getting said results. (like HTML and CSS)
JavaScript is imperative programming language and we have to tell it exactly what to do, step by step

Question 8

How do you retrieve specific columns from a database table?

Answer 8

Select “insert-column-name-here” (multiple, separated by commas)
From “insert-database-table-name-here”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

Specify value from column
Where “insert-column-name” = ‘insert-value-name’;

Question 10

What are the benefits of formatting your SQL?

Answer 10

Easier readability

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

Limit how many results you want
Limit [insert-number]

Question 13

How do you retrieve all columns from a database table?

Answer 13

Use * [asterisk for all]
Select *

Question 14

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

Answer 14

Order by “insert-name-of-what-to-order-by” desc
Ascending by default
Desc for greatest to least

Question 15

How do you add a row to a SQL table?

Answer 15

Insert keyword
Insert into “table-data-name” (“column-name”, “more-column-names”)
Values (‘value of first column-name’, ‘value of second column-name’)
Returning * [optional, but shows immediately what you inserted]

Question 16

What is a tuple?

Answer 16

A list of values (the ones written inside the parenthesis with single quotes, separated by commas)

Question 17

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

Answer 17

Write multiple () separated by comma after Values
Values (‘content’, ‘content’),
(‘more-content’, ‘more-content’)

Question 18

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

Answer 18

Returning * [optional, but shows immediately what you inserted]

Question 19

How do you update rows in a database table?

Answer 19

Update keyword
Update “table-database-name”
Set “column-name” = ‘new-value’
Where “which-column” = ‘which-value-to-change’

Question 20

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

Answer 20

If not, it’ll update the entire column that was selected/chosen/written

Question 21

How do you delete rows from a database table?

Answer 21

Delete from “table-database-name”
Where “column-name” = ‘value to be deleted’
returning *;

Question 22

How do you accidentally delete all rows from a table?

Answer 22

Delete from “table-database-name”;
Accidentally deletes ALL rows!

Question 23

What is a foreign key?

Answer 23

It’s a shared data value that is a placeholder for its fullest amount of detail, which is located elsewhere
Ie. the supplierId in the products table is the number to look for in the suppliers table to find the rest of the information about that supplier

Question 24

How do you join two SQL tables?

Answer 24

Join keyword
Join “table-database-name” using (“column-to-combine-with”);
Can use Join “table-database-name”

Question 25

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

Answer 25

When selecting, you can write as “whatever-rename-name”
Select “table-database-name”.”column-name-in-database” as “new-temp-name-in-new-table”
Doesn’t rename in the original table, but only renames the column name in the result set

Question 26

What are some examples of aggregate functions?

Answer 26

Min, sum, count, max,

Question 27

What is the purpose of a group by clause?

Answer 27

Partition by the part we don’t want duplicated
After getting large table with duplicate info, the group by clause will collapse all the duplicates so the results will only show 1x each
Perform aggregate functions on the groups of rows