PostgreSQL Flashcards

1
Q

What is PostgreSQL and what are some alternative relational databases?

A
  • PostgreSQL is an open source relational database system

- SQLite, MySQL, SQL Server, Oracle, Snowflake

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are some advantages of learning a relational database?

A
  • Nearly all applications utilize a database, many of them relational databases
    • SQL is a commonly used and therefore highly transferrable language to learn
    • Allow you to query data!
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is one way to see if PostgreSQL is running?

A
  • Use the top command in the terminal to see if there are any PostgreSQL processes running
    • sudo service postgresql status
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How do you start and stop a PostgreSQL server from the CLI?

A

Start: sudo service postgresql start
See UI: pgweb and then visit localhost address
Stop: sudo service postgresql stop

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is ACID?

A

Atomicity - Transactions are singular units, and these units get accepted or rejected entirely (not halfway)
Consistency - There are rules in place (e.g. table schema enforcement) that prevent invalid data from being written (data could be incorrect, but not invalid)
Isolation - Processes may be run in parallel, but they must affect the database as if they were done sequentially
Durability - Once a transaction has been committed, it stays committed (saved the non-volatile memory)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a database schema?

A

A collection of databases, it determines how data will be organized

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a table?

A

A table is made up of rows of data that all have the same attributes represented as columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a row?

A

A row is made up of data elements, one for each attribute

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

Structured Query Language, for accessing databases
SQL is a declarative language as opposed to an imperative language like JavaScript, meaning developers code the intent rather than the operations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How do you retrieve specific columns from a database table?

A

SELECT "col1", "col2"

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How do you filter rows based on some specific criteria?

A

... WHERE criteria

Called a “predicate”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What are the benefits of formatting your SQL?

A

Readability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

=, >, <, !=, and the “__ than or equal to” variations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

... LIMIT n at the end of the query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How do you retrieve all columns from a database table?

A

SELECT *

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

ORDER BY "col1" ASC/DESC or you can use the numbers to refer to which column as well

17
Q

How do you add a row to a SQL table?

A

Use the INSERT clause

INSERT INTO "table" ("col1", "col2", ...) VALUES ('col1Value', 23);

18
Q

What is a tuple?

A
  • A grouping of data values, usually comma separated and enclosed in parentheses
    • A finite ordered list of elements
19
Q

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

A

Use multiple tuples in the VALUES clause, separated by commas

20
Q

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

A

At the end of the INSERT statement, include the RETURNING clause followed by the columns you want returned

21
Q

How do you update rows in a database table?

A

Use the UPDATE clause WITH A WHERE CLAUSE!

UPDATE "table" SET "column" = 'new value' WHERE "id" = 123 RETURNING *;

22
Q

Why is it important to include awhereclause in yourupdatestatements?

A

If you don’t, all values in the specified columns in the table will be set to a single value

23
Q

How do you delete rows from a database table?

A

Use the DELETE clause WITH A WHERE CLAUSE!

DELETE FROM "table" WHERE "id" = 123 RETURNING *;

24
Q

How do you accidentally delete all rows from a table?

A

If you leave off a WHERE clause, the delete operation will happen for every row in the table

25
Q

What is a foreign key?

A

A column that contain values that refer to another column in another table, used to join the two tables together

26
Q

How do you join two SQL tables?

A

After the FROM, use the JOIN clause followed by the table and the join method
FROM "table1" JOIN "table2" USING ("commonId")
We can use ON as well

27
Q

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

A

Give them an alias using AS after the column in the SELECT clause or the table in the FROM/JOIN

28
Q

What are some examples of aggregate functions?

A

SUM, AVG, MIN, MAX, COUNT, STRING_AGG, JSON_AGG

29
Q

What is the purpose of agroup byclause?

A

To indicate which of the columns that are not being aggregated should be used as unique groups to aggregate values up to

30
Q

How do you prevent SQL injection attacks when sending a query with user input to a SQL database?

A

When using db.query(), pass in two arguments: sql and params

  • sql has the query with the user input placeholders as $1, $2, etc.
  • params is an array of the items to interpolate/substitute