postgreSQL, SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
a powerful, free, open source Relational Database Management System (RDBMS). It is often cited as the most advanced open source database of its kind.
alternative relational db: MySQL, SQL Server by Microsoft, and Oracle by Oracle Corporation.
What are some advantages of learning a relational database?
Many problem domains can be modeled well using a relational database.
they support good guarantees about data integrity. They can store and modify data in a way that makes data corruption as unlikely as possible.
the most widely used kind of database
satisfies ACID properties
doesnt require a complex structure
What is one way to see if PostgreSQL is running?
‘sudo service postgresql status’
top command
pgweb
is just a web interface for interacting w/ postgresql (so its easier)
there are many web interfaces like pgweb, this is not the only one
psql
what you write in terminal to give commands w/ postgresql
What is a database schema?
defines how the data in a relational database should be organized.
collection of tables
the structure of the whole database, the “blueprint”
What is a table?
list of rows each having the same set of attributes.
defines what attribures(column) each row should have
What is a row?
a single record of data in a table
What is SQL and how is it different from languages like JavaScript?
primary way of interacting with relational databases.
It is a powerful way of retrieving, creating, and manipulating data in a relational database.
SQL is a declarative programming language while JS is a imperative programming language
(declarative: programmers describe the results they want and the programming environment comes up with its own plan for getting those results)
(imperative: you basically tell the JavaScript runtime what to do and how to do it)
How do you retrieve specific columns from a database table?
select “column name”,
“column name”,
etc.
from “name of table”
How do you filter rows based on some specific criteria?
where “column name” = or > or < or != ‘criteria’
What are the benefits of formatting your SQL?
cleaner, easier to read, easier to tell what is going on
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
give num of rows you want at most
How do you retrieve all columns from a database table?
- (an asterisk) instead of column names
How do you control the sort order of a result set?
order by “column name”
*will be ascending order by default
can use order by “column name” desc for it to be in descending order
How do you add a row to a SQL table?
insert into “table name” (column1 name, column2 name, etc)
values (value for column1, value for column2, etc)
What is a tuple?
a list of values (in SQL)
a row/a record
How do you add multiple rows to a SQL table at once?
list tuples separated by commas
How do you get back the row being inserted into a table without a separate select statement?
returning keyword *;
or
returning keyword specific column names;
(if you don’t want entire row returned)
How do you update rows in a database table?
update "table name"
set "column name" = 'new value',
"column name" = 'new value',
etc.
where "column name" = < > != 'value/condition';Why is it important to include a where clause in your update statements?
if there is no where clause, it will update every row of table
How do you delete rows from a database table?
delete from “table name”
where “column name” = > < != ‘value/condition’
How do you accidentally delete all rows from a table?
by not adding a where clause!!
What is a foreign key?
a column in a table where its values are constrained to match that of a column of another table
can be used to join tables together
constraint that one column has to match the values of another column in another table
How do you join two SQL tables?
join keyword
select "column1",
"column2",
etc.
from "table1 name"
join "table2 name" using ("foreign key")
How do you temporarily rename columns or tables in a SQL statement?
as keyword
select “columns1”
from “columns” as “all columns”
What are some examples of aggregate functions?
max(), sum(), count(), min(), average() etc
What is the purpose of a group by clause?
useful when you want to separate rows into groups and perform aggregate functions on those groups of rows
arrange multiple rows into one row based on a set of conditions or a value of a single row
groups row into a single row based on criteria (one or more columns to group by)
*all rows with same value in the stated columns will collapse into one row
what do aggregate functions do?
convert a bunch of values into a single value
performs a calculation on a set of values, and returns a single value.
similar to array.reduce()?
