Postgres DBMS Flashcards
What is PostgreSQL and what are some alternative relational databases?
Opensource database. MySQL, SQLite, Sql Server, Oracle
What are some advantages of learning a relational database?
Commonly used
What is one way to see if PostgreSQL is running?
top, psql
What is a database schema?
Definition of database tables and relations
What is a table?
List of tabular data with columns defining the data
What is a row?
One set of data in the table
What is SQL and how is it different from languages like JavaScript?
SQL is a language to communicate with a relational database server. It’s a declarative language where you state your intent and the server figures out what you want.
How do you retrieve specific columns from a database table?
select
How do you filter rows based on some specific criteria?
where clause
What are the benefits of formatting your SQL?
easier to read
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 clause
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by
How do you add a row to a SQL table?
insert
What is a tuple?
a finite ordered list of elements
How do you add multiple rows to a SQL table at once?
add multiple values in parentheses separated by commas
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 (header) (tuples) where clause
Why is it important to include a where clause in your update statements?
to select specific rows
How do you delete rows from a database table?
delete from
How do you accidentally delete all rows from a table?
if you don’t set a where clause
What is a foreign key?
key in a different table
How do you join two SQL tables?
join
How do you temporarily rename columns or tables in a SQL statement?
Use an alias as
What are some examples of aggregate functions?
sum, avg, min, max, count
What is the purpose of a group by clause?
group results by a values of a column
