PostgreSQL Flashcards

1
Q

What is PostgreSQl and what are some alternative relational databases?

A

Open source object-relational database system

alternatives: mySQL, 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

You work with the SQL language, and a majority of databases use SQL

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

collection of tables in sql

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

What is a table?

A

list of rows each having the same set of attributes.

ex. student table can all have attributes: “firstName” , “lastName” , and “dateOfBirth

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

What is a row?

A

a record of the table with data

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 - it is declarative meaning programmers describe the results they want and the programming environments gets those results

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 “columnName” from “tableName”;

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 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 limit the number of rows returned in a result set?

A

limit

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

*

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

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 “tableName” (“columnName”) values (‘valueName’) returning *;

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

What is a “tuple”?

A

A tuple is simply a row contained in a table in the tablespace.

17
Q

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

A

Insert into “tableName” (“columnNames”, etc etc.) values (‘value’, etc etc) returning *;

18
Q

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

A

returning *

19
Q

How do you update rows in a database table?

A

returning *

20
Q

How do you update rows in a database table?

A

Update “table” set “columnName” = ‘value’ where “columnName = ‘newValue’;

21
Q

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

A

So you dont update everything inside of the table

22
Q

How do you delete rows from a database table?

A

Delete from “tableName” where “columnName” = value returning *;

23
Q

How do you accidentally delete all rows from a table?

A

Delete from “tableName” DONT DO THIS!!

24
Q

What is a foreign key?

A

Value in column used to link two tables

25
Q

How do you join two SQL tables?

A

join

26
Q

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

A

Aliasing use as

27
Q

What are some examples of aggregate functions?

A

min(), sum(), and every()

28
Q

What is the purpose of a group by clause?

A

Instead, you want to separate rows into groups and perform aggregate functions on those groups of rows. This is done with a group by clause.