PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

• Relational database management system

- MySQL, Oracle, SQL Server by Microsoft

Question 2

What are some advantages of learning a relational database?

Answer 2

Widely adapted database, so can be a highly transferrable language

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

Question 4

What is a database schema?

Answer 4

• A collection of tables
• Defines how the data in a relational database should be organized
• Define a schema, and the database will make sure the data 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

Question 6

What is a row?

Answer 6

• Records that are contained inside the table

- All the data that describes one item inside a table

Question 7

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

Answer 7

SQL is a declarative language, so we declare or query for data, and the SQL language finds a path to get us the results

JavaScript is imperative, we write functions by hand, so we tell JS how do handle the action as well.

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “column”

If multiple columns, add comma then next column name.

Question 9

How do you filter rows based on some specific criteria?

Answer 9

Using the where keyword with comparison operator between “column” and data (string, number, boolean)

where “column” = data

Question 10

What are the benefits of formatting your SQL?

Answer 10

Data is easier to read and comprehend when organized

Question 11

What are four comparison operators that can be used in awhereclause?

Answer 11

Comparison operators: =, !=, >,

Question 12

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

Answer 12

• Using the limit keyword with a number

- limit 10

Question 13

How do you retrieve all columns from a database table?

Answer 13

select *

Question 14

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

Answer 14

• Ascending order is default

- order by “column” desc

Question 15

How do you add a row to a SQL table?

Answer 15

Using insert into keyword followed by the “table”, and the column names in double quotes and parenthesis, separated by commas.

insert into actors (firstName, lastName)
values (‘Ken’ ,’Kieu’)

Question 16

What is a tuple?

Answer 16

A list of values in SQL

Question 17

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

Answer 17

• values keyword once
• Each value should be a new line in parenthesis and single quote

insert into programming (languages)
values (‘HTML’),
(‘CSS’),
(‘JavaScript’);

Question 18

How do you get back the row being inserted into a table without a separateselectstatement?

Answer 18

returning *;

or returning specific “column”

Question 19

How do you update rows in a database table?

Answer 19

• update keyword “table”
• set keyword “column” = value
• where keyword “column” = value

update store
set price = 100
where item = ‘electronics’;

Question 20

Why is it important to include awhereclause in yourupdatestatements?

Answer 20

If where is omitted, it will update all instances of that category

Question 21

How do you delete rows from a database table?

Answer 21

• delete from table
• where column = value

delete from store
where item = ‘electronics’

Question 22

How do you accidentally delete all rows from a table?

Answer 22

If where is omitted, it will delete everything

Question 23

What is a foreign key?

Answer 23

Shared attribute that links two tables together

Question 24

How do you join two SQL tables?

Answer 24

SELECTorders.orderID, customers.customerName
FROMOrders
JOINcustomersONorders.customerID=customers.customerID;

Question 25

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

Answer 25

Using the as keyword will allow you to temporarily rename columns or tables

“actors” as “a”

Question 26

What are some examples of aggregate functions?

Answer 26

• max()
• min()
• avg()
• sum()
• count()
• every()

Question 27

What is the purpose of agroup byclause?

Answer 27

Group rows that you want to perform aggregate functions on