postgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

a powerful, free, open source Relational Database Management System (RDBMS)
MySQL, SQL Server, Oracle

Question 2

What are some advantages of learning a relational database?

Answer 2

they support good guarantees about data integrity, store and modify data in a way that makes data corruption as unlikely as possible

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

sudo service postgresql status, or top

Question 4

What is a database schema?

Answer 4

A collection of tables, how the data in a relational database should be organized

Question 5

What is a table?

Answer 5

relations - where relational databases are store data in

a set of rows

Question 6

What is a row?

Answer 6

row with the same set of attributes

Question 7

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

Answer 7

Structured Query Language (SQL) - primary way of interacting with relational databases
- a declarative programming language

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “column1”,
“column2”
from “table

Question 9

How do you filter rows based on some specific criteria?

Answer 9

where “column1” = ‘data’,

limit

Question 10

What are the benefits of formatting your SQL?

Answer 10

for consistent style and 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 #

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 “columnName” asc/desc

Question 15

How do you add a row to a SQL table?

Answer 15

insert into “tableName” (“column1”, “column2”)

values (‘value1’, ‘value2’)

Question 16

What is a tuple?

Answer 16

list of values

Question 17

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

Answer 17

values (‘value1’, ‘value2’),
(‘value1’, ‘value2’),
(‘value1’, ‘value2’)

Question 18

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

Answer 18

returning *

at the end of the statement

Question 19

How do you update rows in a database table?

Answer 19

update “tableName”
set “columnName” = ‘value’
where “columnName” = ‘value’

Question 20

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

Answer 20

you will update everything otherwise

Question 21

How do you delete rows from a database table?

Answer 21

delete from “tableName”

where “columnName” = ‘value’

Question 22

How do you accidentally delete all rows from a table?

Answer 22

not specifying where

Question 23

What is a foreign key?

Answer 23

one column linking all the attributes to a different table

Question 24

How do you join two SQL tables?

Answer 24

join “tableName” using (“columnName”)

Question 25

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

Answer 25

from “columnName” as “aka”

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

used to group rows that have the same values to produce summary reports from the database