postgresSL / SQL Flashcards
Why do we use databases in Web development?
To store data to be quickly retrieved, esp when it’s complex data and needs to be organized
They can also be used by many users and it provides a central location
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is a relational database.
Other popular relational databases include MySQL (also free), SQL Server by Microsoft, and Oracle by Oracle Corporation.
What are some advantages of learning a relational database?
Good data integrity (rejects “bad” data) (see below)
A quality of many relational databases is that they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible. This means that developers can set up their database to reject “bad” data and not worry about data being “half written”.
What is one way to see if PostgreSQL is running?
also, using the “sudo service postgresql status” command
using the “top” command and watching for it in the list of processes
“sudo” stands for “super user do”, aka an admin LOL
what command starts the postgres database?
sudo service postgresql start
what command stops the postgres database?
sudo service postgresql stop
what command checks postgrest status?
sudo service postgresql status
what does the pgweb command do?
it runs a server and lets you access from the browser, it queries your databases and gives you a GUI - graphical user interface, or webpage, to work in
What is a database schema?
a collection of relations (relations are like tables with rows & columns)
or, definition of all that describes your database
What is a table?
it has rows and columns for data, and in coding is called a relations
What is a row?
horizontal grid section with attributes in each column
like an object
a row is like an instance of a class
What is an attribute and what other names are used to describe them?
a column
what is a “primary key”
like an ID, basically
it uniquely identifies the row
What is SQL and how is it different from languages like JavaScript?
How do you retrieve specific columns from a database table?
How do you filter rows based on some specific criteria?
What are the benefits of formatting your SQL?
readability, consistency
code is read many more times than it is written!
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?
use the limit keyword
How do you retrieve all columns from a database table?
use “*”
How do you control the sort order of a result set?
type “order by”
How do you add a row to a SQL table?
What is a tuple?
a list of values in parentheses
How do you add multiple rows to a SQL table at once?
use a comma and add another tuple, e.g.,
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?
How do you update rows in a database table?
update “products”
set “price” = 100
where “productId” = 24;
Why is it important to include a where clause in your update statements?
so you don’t delete ALL!
How do you delete rows from a database table?
delete
from “products”
where “productId” = 24
returning *;
How do you accidentally delete all rows from a table?
by not adding “where”
What is a foreign key?
A key that is given by joining another table using the other table’s primary key
How do you join two SQL tables?
using “join …. using … “
How do you temporarily rename columns or tables in a SQL statement?
use “as”
how do you find the “key”
find the column that uniquely identifies each item
if you can’t find it just look in the “constraints” tab in the gui
What are some examples of aggregate functions?
count() , sum(), total() …
What is the purpose of a group by clause?
to group sth by a certain parameter
(can do it manually using other ways as leanred from previous exercises)
What are JavaScript classes?
When would you want to use a class?
How do you declare a class?
How do you inherit from another class?
Why would you want to inherit from another class?
