SQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
open source relational database management system
What are some advantages of learning a relational database?
widely used.
data is also unlikely to be corrupted
What is one way to see if PostgreSQL is running?
top
sudo service postgresql status
What is a database schema?
A collection of tables is called a schema. the rules for how a data is sorted
What is a table?
Relational databases store data in relations, commonly referred to as tables. A table is a list of rows each having the same set of attributes
What is a row?
A table is a list of rows each having the same set of attributes
each row has a list of attributes
each row has the same list of attributes but they probably will have different values
How do you add a row to a SQL table?
insert into
What is a tuple?
a list of values (for columns) is referred to as a tuple.
example: values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’)
How do you add multiple rows to a SQL table at once?
example:
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 *
How do you update rows in a database table?
update clause
Why is it important to include a where clause in your update statements?
because without that, it will update every row
How do you delete rows from a database table?
delete clause
How do you accidentally delete all rows from a table?
using delete without a where clause
What is a foreign key?
a column that is also represented in another table. these columns match each other and can be used to join other tables together
How do you join two SQL tables?
join and using clause
How do you temporarily rename columns or tables in a SQL statement?
as clause
select "products"."name" as "product",
"suppliers"."name" as "supplier"
from "products"
join "suppliers" using ("supplierId");What is SQL and how is it different from languages like JavaScript?
SQL is declarative
Javascript is imperative
How do you retrieve specific columns from a database table?
select “thing”
How do you filter rows based on some specific criteria?
where clause just has to evaluated true or false
What are the benefits of formatting your SQL?
easier to read
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?
*
How do you control the sort order of a result set?
order by
What are some examples of aggregate functions?
sum() average() count() max() min()
What is the purpose of a group by clause?
arrange
