SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
it’s an open source relational database, alts are mySQL, SQLite, SnowFlake
What are some advantages of learning a relational database?
they’re everywhere so if you learn one, you learn a lot of them
What is one way to see if PostgreSQL is running?
check top or sudo service postgresql status
What is a database schema?
a collection of tables in a database
What is a table?
a list of rows all having the same attributes
What is a row?
one entry in a table that has an attribute value
What is SQL and how is it different from languages like JavaScript?
its a declarative language vs JS is imperative
How do you retrieve specific columns from a database table?
select “column name”
“additional columns. . .”
from “table name”;
How do you filter rows based on some specific criteria?
where clause and an expression that evaluates to a boolean
What are the benefits of formatting your SQL?
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 clause
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by clause with asc and desc
How do you add a row to a SQL table?
insert into statement
What is a tuple?
a list of values that may or may not correspond to a row in a table
How do you add multiple rows to a SQL table at once?
insert into with one list of columns followed with multiple tuples
How do you get back the row being inserted into a table without a separate select statement?
returning *
How do you update rows in a database table?
update keyword(update “tableName” set “column” = ‘value’)
Why is it important to include a where clause in your update statements?
if you don’t it will update every row
How do you delete rows from a database table?
delete keyword (delete from “table” where “something” = something else)
How do you accidentally delete all rows from a table?
by not including a where clause
What is a foreign key?
rows between tables with shared values
How do you join two SQL tables?
join “table” using (“key”)
How do you temporarily rename columns or tables in a SQL statement?
using an alias with keyword “as”
What are some examples of aggregate functions?
sum(), avg(), count(), min(), max(), every()
What is the purpose of a group by clause?
