SQL Flashcards
What is SQL and how is it different from languages like JavaScript?
SQL (Structured Query Language) is the primary way of interacting with relational databases.
SQL is a declarative programming language.
How do you retrieve specific columns from a database table?
A Select statement
select “name”, “price”
from “products”;
How do you filter rows based on some specific criteria?
where “column name” = ‘attribute name’;
What are the benefits of formatting your SQL?
It makes it look cleaner.
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
How do you retrieve all columns from a database table?
select *
from “column”;
How do you control the sort order of a result set?
desc keyword
How do you add a row to a SQL table?
Use the insert statement
What is a tuple?
A finite ordered list of elements
SQL: A set of sets, defining a container with order.
How do you add multiple rows to a SQL table at once?
specify more than one tuple of values separated by commas.
data, data, data),
(data, data, data
How do you get back the row being inserted into a table without a separate select statement?
Use the returning clause. If you want all columns you can use * otherwise use a comma-separated list instead.
How do you update rows in a database table?
update
Why is it important to include a where clause in your update statements?
If you don’t specify a specific row it will update all items in that column.
How do you delete rows from a database table?
delete from “table”
where “row” = ‘value’
Complex where/and:
where “row” = ‘value’
and “row” (, =, !=) ‘value’;
How do you accidentally delete all rows from a table?
delete from “table”;
What is a foreign key?
When a columns data references another tables data.
How do you join two SQL tables?
select “column”
from “table”
join “table” using (“column”)
How do you temporarily rename columns or tables in a SQL statement?
Aliasing
select “table”.”column” as “newLabel”
this can also be done when using from and join.
select “table”.”column” as “newLabel”
from “products” as “p”
join “suppliers” as “s” using (“supplierId”); (for example)
repeat as necessary by separating with commas.
What are some examples of aggregate functions?
max(): Finds max number avg(): Finds average count(*): Counts number of rows min() sum() every() look into JSON aggregate functions
What is the purpose of a group by clause?
Separates rows into groups to perform aggregate functions.
