SQL Flashcards
What is SQL and how is it different from languages like JavaScript?
It’s a declarative language you only need to tell it what to do
How do you retrieve specific columns from a database table?
by using select “column name”
How do you filter rows based on some specific criteria?
by using where + condition
What are the benefits of formatting your SQL?
To make it more readable
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?
with limit (must be at the end)
How do you retrieve all columns from a database table?
with *
How do you control the sort order of a result set ?
with order by (in ascending)
else place desc for descending
How do you skip lines of code?
With offset
How do you add a row to a SQL table?
insert into “table” (“property”, …)
values (‘value’, …)
What is a tuple?
A list of values inside ( )
How do you add multiple rows to a SQL table at once?
insert into “table” (“property”, …)
values (‘value’, …),
(‘value’, …)
How do you get back the row being inserted into a table without a separate select statement?
insert into “table” (“property”, …)
values (‘value’, …),
(‘value’, …)
return *
How do you update rows in a database table?
update “table”
set “target” = value
where “condition” = comparison;
Why is it important to include a where clause in your update statements?
To pin point the specific target
How do you delete rows from a database table?
delete
from “table”
where “column” = value
returning *;
How do you accidentally delete all rows from a table?
delete
from “table”;
(you don’t add where)
What is a foreign key?
an id/key from another table
How do you join two SQL tables?
select *
from “table”
join “table2” using (“tableId”);
How do you temporarily rename columns or tables in a SQL statement?
select “p”.”name” as “product”,
“p”.”category”,
“s”.”name” as “supplier”,
“s”.”state”
from “products” as “p”
join “suppliers” as “s” using (“supplierId”);
What are some examples of aggregate functions?
count( )
sum( )
total( )
avg( )
min( )
max( )
What is the purpose of a group by clause?
To separate rows from groups
