PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
- an open source relational database management system
- others are MySQL, oracle, sql server, sqlite
What are some advantages of learning a relational database?
get to learn the widely used SQL language and relational database, also can store related data with data integrity
What is one way to see if PostgreSQL is running?
‘sudo service postgresql status’ in the terminal, then see if it says ‘online’
What is a database schema?
a collection of tables which defines how data in a relational database should be organized
What is a table?
a list of rows of stored data with the same set of attributes
What is a row?
a horizontal line of data or single instance of a record
What is SQL and how is it different from languages like JavaScript?
- a declarative programming language which interacts with relational databases
- SQL is declarative, so you tell it what you want to do, but not how to do it, while JavaScript is imperative
How do you retrieve specific columns from a database table?
use ‘select’ followed by column name in double quotes
ex. select “name”
How do you filter rows based on some specific criteria?
use ‘where’, followed by name of row, comparison operator, name of cell to filter by in single quotes
ex. where “category” = ‘cleaning’;
What are the benefits of formatting your SQL?
consistency and readability
What are four comparison operators that can be used in a where clause?
< > != =
How do you limit the number of rows returned in a result set?
‘limit’ followed by number of rows you want returned
ex. limit 1
How do you retrieve all columns from a database table?
select * (asterisk)
from “tableName”;
How do you control the sort order of a result set?
order by “columnName”
How do you add a row to a SQL table?
‘insert into “tableName” (“rowName”,)
values (‘data’,)
ex.
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
What is a tuple?
a list of values (wrapped in parenthesis)
How do you add multiple rows to a SQL table at once?
add another tuple in parenthesis after a comma
How do you get back the row being inserted into a table without a separate select statement?
returning *
How do you update rows in a database table?
update “tableName”
set “columnName” = ‘value’
where {expression} ex. “columnName” = ‘value’
Why use where clause in update statements?
so you don’t update every single row in the table
How do you delete rows from a database table?
delete from "tableName"
where {conditional}
ex. delete from "products"
where "productId" = 24
returning *;How do you accidentally delete all rows from a table?
delete from “tableName”
no where
begin, commit, rollback
begin - don’t do the transaction yet
(then do the update or delete)
commit- finish the transaction
rollback - don’t want to commit, but want to end transaction
What is a foreign key?
column which refers to a column in another table
How do you join two SQL tables?
join "tableName" using ("columnName")
ex.
select *
from "products"
join "suppliers" using ("supplierId");How do you temporarily rename columns or tables in a SQL statement?
rename column:
{keyword} “tableName”.”columnName” as “renamedColumn”
rename: table
{keyword} “tableName” as “renamedTable”
What are some examples of aggregate functions?
max() avg() count() min() sum() every()
What is the purpose of a ‘group by’ clause?
to group rows together into one row based on values of a specific column
