PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
- Open source relational databases management system
2. SQLlite, MySQL, Microsoft SQL System
What are some advantages of learning a relational database?
- Everything in web development is based on databases
2. Will basically always be using relational databases in web development
What is one way to see if PostgreSQL is running?
Sudo service postgresql status command
What is a database schema?
A collection of tables that dictate how data should be organized
What is a table?
A collection of rows that stored data with the same attributes
What is a row?
A single record in a table
What is SQL and how is it different from languages like JavaScript?
SQL is declarative - the computer figures it out
JavaScript is imperative - the dev writes the machine instructions
How do you retrieve specific columns from a database table?
Select
“column”,
“column”
From “row”
How do you filter rows based on some specific criteria?
By using where “ “ = “ “
What are the benefits of formatting your SQL?
To lessen complexity
What are four comparison operators that can be used in a where clause?
Less than, greater than, equal
How do you limit the number of rows returned in a result set?
The limit clause
How do you retrieve all columns from a database table?
The universal * (star)
How do you control the sort order of a result set?
Order by (desc) or (asc) clause
How do you add a row to a SQL table?
The insert statement
What is a tuple?
A set of parenthesis with a list of data
How do you add multiple rows to a SQL table at once?
Value clause, followed by “value” with command and multiple rows would be closed tuples on top of eachother
How do you get back the row being inserted into a table without a separate select statement?
Returning clause
How do you update rows in a database table?
With an update statement
Update “table”
Set “column” = ‘new value’
Why is it important to include a where clause in your update statements?
So you don’t update all the rows
How do you delete rows from a database table?
Delete key word
Delete from “row”
Where “column” = ‘value’
How do you accidentally delete all rows from a table?
Delete from “row”
What is a foreign key?
A value in a column that has same key in another table
How do you join two SQL tables?
Join clause
Join “table”
Using (shared foreign key)
How do you temporarily rename columns or tables in a SQL statement?
Aliasing
Select “a”.”firstName”
From “actors” as “c”
What are some examples of aggregate functions?
Count(), sum(), min(), max()
What is the purpose of a group by clause?
So you can specify specific rows to order
