PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
- PostgreSQL is an open source relational database system
- SQLite, MySQL, SQL Server, Oracle, Snowflake
What are some advantages of learning a relational database?
- 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!
What is one way to see if PostgreSQL is running?
- Use the
top
command in the terminal to see if there are any PostgreSQL processes runningsudo service postgresql status
How do you start and stop a PostgreSQL server from the CLI?
Start: sudo service postgresql start
See UI: pgweb
and then visit localhost address
Stop: sudo service postgresql stop
What is ACID?
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)
What is a database schema?
A collection of databases, it determines how data will be organized
What is a table?
A table is made up of rows of data that all have the same attributes represented as columns
What is a row?
A row is made up of data elements, one for each attribute
What is SQL and how is it different from languages like JavaScript?
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 do you retrieve specific columns from a database table?
SELECT "col1", "col2"
How do you filter rows based on some specific criteria?
... WHERE criteria
Called a “predicate”
What are the benefits of formatting your SQL?
Readability
What are four comparison operators that can be used in awhere
clause?
=
, >
, <
, !=
, and the “__ than or equal to” variations
How do you limit the number of rows returned in a result set?
... LIMIT n
at the end of the query
How do you retrieve all columns from a database table?
SELECT *