postgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

A relational database management system that uses SQL query language.

mySQL, Microsoft SQL Server, mariaDB, SQLite, Oracle….etc

Question 2

What are some advantages of learning a relational database?

Answer 2

1. Many problem domains can be modeled well using a relational database; flexibility, you can change the system design after database initially created
2. relational databases support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible
3. Sql databases are widely used

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

The ‘top’ command in the terminal to show all the processes currently running

The command ‘sudo service postgresql status’

Question 4

What is a database schema?

Answer 4

A collection of tables; acts as a description or blueprint of a database

Question 5

What is a table?

Answer 5

A list of rows each having the same set of attributes; Tables are used to hold information about the objects to be represented in the database.

Question 6

What is a row?

Answer 6

An individual record with a unique id

Question 7

What are some examples of aggregate functions?

Answer 7

avg(), sum(), max()/min(), count()…many others

Question 8

What is the purpose of a group by clause?

Answer 8

Aggregate functions need a group by clause so it knows with rows to aggregate

Question 9

What is a foreign key?

Answer 9

The id for a record from another table;

Question 10

How do you join two SQL tables?

Answer 10

SELECT ,
…
FROM
JOIN USING

Question 11

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

Answer 11

Alias the columns or table using AS keyword

. AS

Question 12

How do you delete rows from a database table?

Answer 12

DELETE FROM
WHERE = ;
RETURNING *;

RETURNING statement is optional

Question 13

How do you accidentally delete all rows from a table?

Answer 13

Use a delete statement without a WHERE clause

Question 14

How do you update rows in a database table?

Answer 14

UPDATE  
	SET  = ,
	SET  = ,
	. . .  
   WHERE  ;

Question 15

How do you add a row to a SQL table?

Answer 15

INSERT INTO  (, . . .)
    VALUES (,

Question 16

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

Answer 16

INSERT INTO (columnsTuple)
VALUES (valueTuple1),
VALUES (valueTuple2);

or

SET column1 = val1,
column2 = val2;

Question 17

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

Answer 17

RETURNING *;

at end of SELECT statement

Question 18

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

Answer 18

It’s declarative instead of imperative - you describe what you want instead of giving instructions for doing something

Question 19

How do you retrieve specific columns from a database table?

Answer 19

SELECT ,
,

FROM ;

Question 20

How do you filter rows based on some specific criteria?

Answer 20

SELECT . . .
FROM . . .
WHERE ;

Question 21

What are the benefits of formatting your SQL?

Answer 21

It’s easier to read

Question 22

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

Answer 22

= != < > (equal, not equal, less than, greater than)

Question 23

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

Answer 23

SELECT …
FROM …
WHERE …
LIMIT ;

Question 24

How do you retrieve all columns from a database table?

Answer 24

SELECT *

FROM ;

Question 25

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

Answer 25

SELECT …
FROM …
ORDER BY
LIMIT ;