postgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
a powerful, free, open source Relational Database Management System (RDBMS)
MySQL, SQL Server, Oracle
What are some advantages of learning a relational database?
they support good guarantees about data integrity, store and modify data in a way that makes data corruption as unlikely as possible
What is one way to see if PostgreSQL is running?
sudo service postgresql status, or top
What is a database schema?
A collection of tables, how the data in a relational database should be organized
What is a table?
relations - where relational databases are store data in
a set of rows
What is a row?
row with the same set of attributes
What is SQL and how is it different from languages like JavaScript?
Structured Query Language (SQL) - primary way of interacting with relational databases
- a declarative programming language
How do you retrieve specific columns from a database table?
select “column1”,
“column2”
from “table
How do you filter rows based on some specific criteria?
where “column1” = ‘data’,
limit
What are the benefits of formatting your SQL?
for consistent style 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 #
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by “columnName” asc/desc
How do you add a row to a SQL table?
insert into “tableName” (“column1”, “column2”)
values (‘value1’, ‘value2’)
What is a tuple?
list of values
How do you add multiple rows to a SQL table at once?
values (‘value1’, ‘value2’),
(‘value1’, ‘value2’),
(‘value1’, ‘value2’)
How do you get back the row being inserted into a table without a separate select statement?
returning *
at the end of the statement
How do you update rows in a database table?
update “tableName”
set “columnName” = ‘value’
where “columnName” = ‘value’
Why is it important to include a where clause in your update statements?
you will update everything otherwise
How do you delete rows from a database table?
delete from “tableName”
where “columnName” = ‘value’
How do you accidentally delete all rows from a table?
not specifying where
What is a foreign key?
one column linking all the attributes to a different table
How do you join two SQL tables?
join “tableName” using (“columnName”)
How do you temporarily rename columns or tables in a SQL statement?
from “columnName” as “aka”
What are some examples of aggregate functions?
max, sum, count, avg
What is the purpose of a group by clause?
used to group rows that have the same values to produce summary reports from the database
