PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
powerful relational database management system
Redis MangoDB etc
What are some advantages of learning a relational database?
a lot of databases use SQL languages / popular
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
collection of tables -> it sets how the table should be structured
What is a table?
list of a row each having set of attribute
What is a row?
list of datas in table structured
What is SQL and how is it different from languages like JavaScript?
primary way of interacting with relational databases (retrieving, creating, and
manipulating data)
Different from javascript -> imperative langue where programmer tell what to do and how to do
sql -> declarative -> program figures out and describe the results
How do you retrieve specific columns from a database table?
select “column”,
if more than one column. “ “
from “table”
(optional)
where “column” = ‘value’
order by “column” (optional) desc
limit number;
How do you filter rows based on some specific criteria?
where “ “ =, >,
What are the benefits of formatting your SQL?
retrieve the data easily
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?
*
How do you control the sort order of a result set?
order by “column”
if descending order
order by “column” desc
How do you add a row to a SQL table?
sql = `insert into "table" ("column", "column")
values ($1, $2)
returning *;
`
values = [value for $1, value for $2]
db
.query(sql, values)
numbers area automatically generated by app due to avoid duplicate value
What is a tuple?
list of values
How do you add multiple rows to a SQL table at once?
insert into “table” (“column”, “column)
values (‘1st’, ‘1st’)
(‘2nd’, ‘2nd’);
How do you get back the row being inserted into a table without a separate select statement?
using returning*
How do you update rows in a database table?
all -> update “table”
set “column” = ‘new value’;
specific -> update “table”
set “column” = ‘new value’
where “column” = ‘existing value’;
multiple -> update “table”
set “column” = ‘new value’,
“column2” = ‘new value,
where “column” = ‘existing value’;
Why is it important to include a where clause in your update statements?
if not, it would update all row values in the table
How do you delete rows from a database table?
delete from “table”
where “column” = ‘value’;
delete from “table”
where “column” = ‘value’
and “column” , =, != ‘value’
How do you accidentally delete all rows from a table?
when you don’t put where
What is a foreign key?
just one column that links other table to the current table (mostly id)
How do you join two SQL tables?
select*
from “table”
join “table2” using (“mainlyid”)
How do you temporarily rename columns or tables in a SQL statement?
select “column” as “newname”
What are some examples of aggregate functions?
count, sum, max, min, avg
select \_\_\_("column")
from"table"
What is the purpose of a group by clause?
the selected data gets grouped based on group by “column”
