PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
relational DB mgmt system
RDBMS
MySQL
SQL Server for MS
Oracle
What are some advantages of learning a relational database?
short answer: widely used and very powerful
long answer:
once you learn one, you’ve learned them all except for nuances
they support good guarantees about data integrity; minimize data corruption
What is one way to see if PostgreSQL is running?
$ sudo service postgresql status
shortcut: can just run start command again and it won’t do anythin
sudo = (super user do) keyword in terminal that elevates your privileges
service = control a bg service
pgweb
a TOOL to VISUALIZE our DB
**this is not your server
How to access:
In the terminal that was running $top, launch the pgweb PostgreSQL database client by typing $pgweb and pressing enter. You can stop pgweb at any time by pressing $Ctrl + C.
Visit http://localhost:8081 in your web browser. The upper left corner of the UI should say dev.
**MUST STOP SERVER CTRL + C
psql -c ‘\l’
psql = logs into DB (can be used alone)
-c = command
’ \l ‘ = command in a string, [backslash l ] means list
why PostgreSQL?
works will with Node and has built in functionality, esp for array and object
a lot of ppl with Node backend use postgreSQL
What is a database schema?
collection of tables
the instructions to build the tables
What is a table?
data stored in relations
list of rows w/ same set of attributes (columns)
What is a row?
a record
What is SQL and how is it different from languages like JavaScript?
how to interact with DB’s
declarative like HTML and CSS - you tell it what you want and it will give it back to you (order may change) via its own interpretation
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 “condition” = ‘keyword’
use logical comparison operators
What are the benefits of formatting your SQL?
cleaner, minimize mistakes, easy to maintain
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 #ofrows
How do you retrieve all columns from a database table?
select * (wildcard selector)
How do you control the sort order of a result set?
order by
How do you add a row to a SQL table?
insert into “tableName” (“columnName”, “columnName”)
values
How do you add a row to a SQL table?
insert into “tableName” (“columnName”, “columnName”)
values. (‘value’, ‘value’)
What is a tuple?
a list of values
How do you add multiple rows to a SQL table at once?
use commas between value rows
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’),
(‘Tater Mitts’, ‘Scrub some taters!’, 6, ‘cooking’)
returning *;
How do you get back the row being inserted into a table without a separate select statement?
returning *;
^^ use this as last line
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?
otherwise you could update all rows the whole table and cannot undo it
How do you delete rows from a database table?
delete from “tableName”
where “columnName” = ‘value’
returning *;
How do you accidentally delete all rows from a table?
if you don’t add where
What is a foreign key?
common key btwn tables
How do you join two SQL tables?
join “tableName” using (“columnName”)
How do you temporarily rename columns or tables in a SQL statement?
alias
“as”
What are some examples of aggregate functions?
max
min
count
sum
What is the purpose of a group by clause?
separate rows into groups and perform aggregate functions on them (instead of each row)
