PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

relational database management system that often use some variation of a SQL language (Structured Query Language)

Alternatives: SQLite, mySQL, SQL server, oracle

Question 2

What are some advantages of learning a relational database?

Answer 2

It’s widely used,
Support good guarantees about data integrity

Uses SQL language (widely used)

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status

top

Question 4

What is a database schema?

Answer 4

A collection of tables

Defines how the data in a relational database should be organized

Question 5

What is a table?

Answer 5

list of rows each having the same set of attributes (columns)

Question 6

What is a row?

Answer 6

Refers to a data in a table that has the same set of attributes/columns as the other rows

Question 7

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

Answer 7

It is a declarative language (declare things and program makes it happen from its predetermined rules

Languages like JS are imperative

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “columnName”,

“columnName2”, etc

Question 9

How do you filter rows based on some specific criteria?

Answer 9

Using the ‘where’ clause

e.g.
where “column” != ‘value’

Question 10

What are the benefits of formatting your SQL?

Answer 10

It helps with 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?

Answer 12

‘limit’ keyword

e.g.
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

order by “column”

default order is ascending

Question 15

How do you add a row to a SQL table?

Answer 15

insert into “table” (…columns)

value (…values)

Question 16

What is a tuple?

Answer 16

A list of values

Question 17

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

Answer 17

have multiple tuples of values

Question 18

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

Answer 18

returning *;

Question 19

How do you update rows in a database table?

Answer 19

update “table”
set “column” = ‘value’
where condition(s)

*where clause is important!

Question 20

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

Answer 20

If there is no where clause, it will update the entire table

Question 21

How do you delete rows from a database table?

Answer 21

delete from "table"
where condition(s)

Question 22

How do you accidentally delete all rows from a table?

Answer 22

Using delete from without having a where clause

Question 23

What is a foreign key?

Answer 23

A shared column/attribute between tables that links/relates one set of data in a table to another

Question 24

How do you join two SQL tables?

Answer 24

join “table” using (“column”)

Question 25

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

Answer 25

using as

join “table” using (“column”) as “newName”

select “column” as “newName”

Question 26

What are some examples of aggregate functions?

Answer 26

max( )
sum( )
count( )
avg( )

Question 27

What is the purpose of a group by clause?

Answer 27

Collapse/group data into a single row