PostreSQL

Question 1

Why do we use databases in Web development?

Answer 1

It is quicker to query, organize, get access to data from a database. Store a lot of information. Data less likely to be stolen or corrupted, can be accessed by many computers/phones by many users.

Question 2

What is PostgreSQL and what are some alternative relational databases?

Answer 2

An open source relational database. microsoft SQL server, MySQL, SQLite.

Question 3

What are some advantages of learning a relational database?

Question 4

What is one way to see if PostgreSQL is running?

Answer 4

using the top command.
sudo service postgresql status.
sudo = super user do.

Question 5

pgweb

Answer 5

pgweb queries your postgres database and visualizing it for you.

Question 6

What is a database schema?

Answer 6

A collection of tables

Question 7

What is a table?

Answer 7

A table is a list of rows each having the same set of attributes.
every row in a table should have the same columns.

Question 8

What is a row?

Answer 8

an object, an instance of the data, a data point,
A row is a single set of data with all columns that describe pieces of the data.

Question 9

What is an attribute and what other names are used to describe them?

Answer 9

Attributes are commonly referred to as columns. Keys, properties.

Question 10

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

Answer 10

Structured Query Language, SQL is a declarative programming language which means you tell it the results you want, not what to do, as opposed to JavaScript which is an imperative programming language.

Question 11

How do you retrieve specific columns from a database table?

Answer 11

by using the select keyword followed by the column names in double quotes separated by commas.

Question 12

How do you filter rows based on some specific criteria?

Answer 12

by using the where keyword followed by the column name in double quotes, and then a comparison to the value in single quotes. an expression.

Question 13

What are the benefits of formatting your SQL?

Answer 13

SQL does not have to be indented, but you should do it anyway for consistent style and therefore readability.

Question 14

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

Answer 14

=, <, >, !=

Question 15

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

Answer 15

using the limit keyword followed by a number where the number is the max number of rows to be returned.

Question 16

How do you retrieve all columns from a database table?

Answer 16

the select keyword followed by *. the * is not surrounded by any type of quotes

Question 17

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

Answer 17

using the order by keyword. This will sort it in ascending order, use the desc keyword to sort by descending order.

Question 18

How do you add a row to a SQL table?

Answer 18

using the insert keyword followed by into followed by the table you want to insert into in double quotes, followed by the parenthesis with the column inside quotes inside the parenthesis. then on the next line the keyword values followed by parenthesis and the values inside with single quotes if a string.
insert into “actors” (“firstName”, “lastName”)
values (‘Henry’, ‘Ding’)
returning *;

Question 19

What is a tuple?

Answer 19

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

Question 20

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

Answer 20

Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.

Question 21

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

Answer 21

In PostgreSQL it’s possible to get the full inserted row back from the database, including its auto-generated attribute(s), like a productId. This is done with a returning clause. Otherwise, it would be necessary to make a separate query to the database just to get the auto-generated attributes.

Question 22

How do you update rows in a database table?

Answer 22

update “tablename”
set “columnname” = ‘new value’
where “columnname” = ‘value’

Question 23

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

Answer 23

To make sure you only update the specific row you want and not everything.

Question 24

How do you delete rows from a database table?

Answer 24

delete
from “tablename”
where “columnname” = ‘value’

Question 25

How do you accidentally delete all rows from a table?

Answer 25

delete
from “tablename”

Question 26

What is a foreign key?

Answer 26

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. A foreign key references other tables’s primary keys.

Question 27

How do you join two SQL tables?

Answer 27

using the join keyword and the using keyword

Question 28

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

Answer 28

select “addresses”.”line1” as “address”
will rename line1 to address.

Question 29

What are some examples of aggregate functions?

Answer 29

count(), sum(), min(), max(), every()

Question 30

What is the purpose of a group by clause?

Answer 30

the GROUP BY clause divides a table into sets.