postgres/SQL Flashcards

1
Q

what is PostgreSQL and what are some alternative relational databases?

A

powerful, free open source Relational Database Management system (RDBMS)

  • relational database - a digital database based on the relational model of data
    • relational model - approach to managing data using a structure and language consistent with first-order predicate logic, where all data is represented in forms of tuples, grouped into relations
  • alternative relational databases
  • -SQL server
  • -oracle
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

support good guarantees about data integrity

store and modify data in a way that makes data corruption as unlikely as possible

works with SQL language

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, in the terminal

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 tables

- a schema defines how the data in a relational database should 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 a list of rows each having the same set of attributes

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

what is a row?

A

also called a tuple, represents a single, implicitly structured data item in a table

-a single instance of a record

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 langauges like JavaScript?

A

domain-specific language design for managing data held in relational database management systems

it’s a declarative programming languages

  • in declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results
  • JavaScript is an imperative language
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 “name”

From “table”

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 “category” = ‘cleaning’

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 code

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

what are four 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 retrieve all columns from a database table?

A

select *

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

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

A

order by

  • default ascending
  • desc for descending order
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

how do you add a row to a SQL table?

A

insert into “table” (“name”, “description”, “price”)

values (‘Ostrich Pillow’, ‘Feel Comfy and Cozy’, 99)

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

what is a tuple?

A

a list of values

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

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

A

use multiple tuples for values keyword, separated by a comma

17
Q

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

A

returning *

18
Q

How do you update rows in a database table?

A

update keyword,

set keyword

19
Q

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

A

if you don’t include a where clause, it would update every row in the table

20
Q

how do you delete rows from a database table?

A

delete keyword,

where keyword

21
Q

how do you accidentally delete all rows from a table?

A

not including a where statement

22
Q

what is a foreign key?

A

column value that is referenced in another table

23
Q

how do you join two SQL tables?

A

join “tableName” using (“columnName”)

24
Q

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

A

aliasing, using the “as” keyword

25
Q

what are some examples of aggregate functions?

A

count()
avg()
min()
sum()

26
Q

what is the purpose of a group by clause?

A

when you want to separate rows into groups and perform aggregate functions on those groups of rows