PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
open source object-relational database management system; mySQL, Oracle, Microsoft SQL server
What are some advantages of learning a relational database?
Relational databases are arguably the most widely used kind of database. Many web developers work with a relational database at least a little bit during their career
What is one way to see if PostgreSQL is running?
command sudo service postgresql status
What is a database schema?
a collection of tables that defines how the data in a relational database should be organized
What is a table?
a list of rows each having the same set of attributes;a database table is sort of like a spreadsheet where each row is a record in that spreadsheet
What is a row?
single structured data item in a table
What is SQL and how is it different from languages like JavaScript?
primary way of interacting with relational databases. It is a powerful way of retrieving, creating, and manipulating data in a relational database; JavaScript is imperative and SQL is declarative
How do you retrieve specific columns from a database table?
select “columnName”
from “tableName”;
How do you filter rows based on some specific criteria?
where “columnName” = ‘specific description’
What are the benefits of formatting your SQL?
consistent style helps with 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 number; limit clause always comes last
How do you retrieve all columns from a database table?
select *
from “tableName”
How do you control the sort order of a result set?
order by clause ; default it ascending order; if specified can use descending order; order by “whatever” desc
How do you add a row to a SQL table?
insert into “tableName”(“columnName”)
values (‘columnValue’);
What is a tuple?
the list of values being inserted in parenthesis
How do you add multiple rows to a SQL table at once?
insert into “tableName”(“columnName”)
values (‘this’),
(‘that’)
How do you get back the row being inserted into a table without a separate select statement?
use returning clause;
insert into “tableName”(“columnName”)
values (‘columnValue’)
returning *;
How do you update rows in a database table?
update “tableName”
set “columnName” = ‘new value’
where “columnName” = ‘value’
Why is it important to include a where clause in your update statements?
to update specific statments at target specified in where clause
How do you delete rows from a database table?
delete from “tableName”
where “columnName” = ‘specific’
How do you accidentally delete all rows from a table?
omitting the where clause
What is a foreign key?
when a column in a table specifically refers to values in a different table with the same column name
How do you join two SQL tables?
select “tableName”.”columnName”
“otherTableName”.”columnName”
from “tableName”
join “otherTableName” using (“columnNameInCommon”);
How do you temporarily rename columns or tables in a SQL statement?
select “tableName”.”columnName” as “newName”
What are some examples of aggregate functions?
count( ), sum ( ), avg ( ), max ( )
What is the purpose of a group by clause?
separate rows into groups and perform aggregate functions on those groups of rows
