postgres Flashcards
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is a relational database management system, other relational databases are MySQL, SQL Server, Oracle
What are some advantages of learning a relational database?
good fit for storing related data, support good guarantees about data integrity, arguably one of the most widely used kind of database
What is one way to see if PostgreSQL is running?
top command
What is a database schema?
a collection of tables
What is a table?
data stored in relations, a list of rows with information
What is a row?
a line in a table containing related information
What is SQL and how is it different from languages like JavaScript?
primary way of interacting with relational databases, unlike JavaScript it is declarative not imperative where we describe what we want and the programming environment comes up with its own plan for getting those results
How do you retrieve specific columns from a database table?
select “specificColumn”
from “databaseTable”
How do you filter rows based on some specific criteria?
select “specificColumn”
from “databaseTable”
where “x” = “y”
What are the benefits of formatting your SQL?
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?
select “specificColumn”
from “databaseTable”
limit x
How do you retrieve all columns from a database table?
select *
from “databaseTable”
How do you control the sort order of a result set?
select *
order by “x” desc (asc is default)
from “databaseTable”
How do you add a row to a SQL table?
insert into “SQLTableName” (“property”
values (“propertyValue”)
What is a tuple?
in SQL a tuple is a list of values
How do you add multiple rows to a SQL table at once?
insert into “SQLTableName” (“property”
values (“propertyValue1”),
(“propertyValue2”),
(“propertyValue3”)
How do you get back the row being inserted into a table without a separate select statement?
returning *;
or
returning “rowname”;
How do you update rows in a database table?
update “SQLTableName”
set “propertyName” = ‘newValue’
Why is it important to include a where clause in your update statements?
If there is no where clause, then every row will be updated
How do you delete rows from a database table?
delete from “SQLTableName”
where “propertyName” = ‘value’
How do you accidentally delete all rows from a table?
exclude the where clause
What is a foreign key?
a column referring to the same values in different tables
How do you join two SQL tables?
select *
from “x”
join “y” using (“xy”)
How do you temporarily rename columns or tables in a SQL statement?
“x” as “y”
What are some examples of aggregate functions?
sum, count, min, max,
What is the purpose of a group by clause?
perform aggregate functions on groups of rows
