PostgreSQL Flashcards

(27 cards)

1
Q

What is PostgreSQL and what are some alternative relational databases?

A

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

Enforces rules that the data you are storing is useful and can be queried

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
  • sudo service postgresql status
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
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
5
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
6
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
7
Q

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

A

Structured Query Language is used for accessing database.

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

How do you retrieve specific columns from a database table?

A

select “thing1”

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

How do you filter rows based on some specific criteria?

A

where

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

What are the benefits of formatting your SQL?

A

Easier to read

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

What are 4 comparison operators that can be used in a ‘where’ clause?

A

=, <, >, !=

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

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

A

limit -insert number-

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
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
14
Q

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

A

order by, then direction (ascd (a-z) or desc(z-a))

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

How do you add a row to a SQL table?

A

insert into

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

What is a tuple?

A

A grouping of data values, usually comma separated and enclosed in parentheses

17
Q

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

A

Use multiple tuples in the VALUES clause, separated by commas

18
Q

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

A

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

19
Q

How do you update rows in a database table?

A

update clause

20
Q

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

A

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

21
Q

How do you delete rows from a database table?

A

delete statement

22
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

23
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

24
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

25
How do you temporarily rename columns or tables in a SQL statement?
Give them an alias using `AS` after the column in the `SELECT` clause or the table in the `FROM`/`JOIN`
26
What are some examples of aggregate functions?
`SUM`, `AVG`, `MIN`, `MAX`, `COUNT`, `STRING_AGG`, `JSON_AGG`
27
What is the purpose of a 'group by' clause?
To indicate which of the columns that are not being aggregated should be used as unique groups to aggregate values up to