PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

• Relational Database Management System

- MySQL, SQLite

Question 2

What are some advantages of learning a relational database?

Answer 2

• Support good guarantees about data integrity
• Store and modify data in a way that makes data corruption as unlikely as possible
• Most widely used kind of database

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

• Running the top command and checking to see postgres processes
• sudo service postgresql status

Question 4

What is a database schema?

Answer 4

-Collection of tables

Question 5

What is a table?

Answer 5

• Data stored in relations in relational database

- List of rows each having the same set of attributes

Question 6

What is a row?

Answer 6

-A row represents a complete record of a specific data

Question 7

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

Answer 7

• SQL is declarative programming language and JavaScript is imperative programming language
• Programmers describe the result

Question 8

How do you retrieve specific columns from a database table?

Answer 8

-select statement

Question 9

How do you filter rows based on some specific criteria?

Answer 9

-where clause

Question 10

What are the benefits of formatting your SQL?

Answer 10

-Easy to find

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 clause

Question 13

How do you retrieve all columns from a database table?

Answer 13

-asterisk

Question 14

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

Answer 14

-order by clause

Question 15

How do you add a row to a SQL table?

Answer 15

-insert keyword

Question 16

What is a tuple?

Answer 16

-One row

Question 17

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

Answer 17

-Specifying more than one tuple of values, separated by commas

Question 18

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

Answer 18

-return *

Question 19

How do you update rows in a database table?

Answer 19

• update keyword and set keyword

- include a where clause

Question 20

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

Answer 20

-If a where clause is missing, it would update every row in the table

Question 21

How do you delete rows from a database table?

Answer 21

• delete from keyword

- where clause

Question 22

How do you accidentally delete all rows from a table?

Answer 22

-Not including the where clause

Question 23

What is a foreign key?

Answer 23

-An attribute in a table that refers to the primary key of another table

Question 24

How do you join two SQL tables?

Answer 24

-join clause

Question 25

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

Answer 25

-Alias column names by using as keyword

Question 26

What are some examples of aggregate functions?

Answer 26

• max()
• avg()
• sum()

Question 27

What is the purpose of a group by clause?

Answer 27

-To separate rows into groups and perform aggregate functions on those groups