SQL Flashcards
What is SQL and how is it different from languages like JavaScript?
Structured Query Language (SQL) is the primary way of interacting with relational databases. JS is imperative programming language and SQL is a declarative programming language
How do you retrieve specific columns from a database table?
SELECT column name and FROM table name
How do you filter rows based on some specific criteria?
select, from, and where keyword
What are the benefits of formatting your SQL?
Helps us to make the queries more readable and easy to understand.
What are the benefits of formatting your SQL?
Helps us to make the queries more readable and easy to understand.
What are four comparison operators that can be used in a where clause?
=, <, >, and !=
How do you limit the number of rows returned in a result set?
use limit keyword and the number of rows
How do you retrieve all columns from a database table?
select * from tableName
How do you control the sort order of a result set?
order by “column name” desc/asc
How do you add a row to a SQL table?
Insert into “tableName” (“columnName”)
values (‘value’)
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
What is a tuple?
a list of values
How do you add multiple rows to a SQL table at once?
values (‘value’),
(‘value’)
insert into “products” (“name”)
values (‘Ostrich Pillow’),
(‘Tater Mitts’)
returning *;
How do you get back the row being inserted into a table without a separate select statement?
by using keyword returning *;
How do you update rows in a database table?
update “tableName”
set “column/attribute” = ‘value’
where “selection” = ‘value’;
update “products”
set “price” = 100
where “productId” = 24;
Why is it important to include a where clause in your update statements?
to target specific rows so it does not update every row in the table
How do you delete rows from a database table?
delete
from “table name”
where “columnName/attribute” = ‘value’
delete
from “products”
where “productId” = 24
returning *;
How do you accidentally delete all rows from a table?
delete
from “tableName”;
What is a foreign key?
Column that show up in multiple tables
How do you join two SQL tables?
select “tableName”.”columnName”
from “tableName”
join “tableName” using (“columnName”);
select “p”.”name” as “product”,
“p”.”category”,
“s”.”name” as “supplier”,
“s”.”state”
from “products” as “p”
join “suppliers” as “s” using (“supplierId”);
How do you temporarily rename columns or tables in a SQL statement?
join “tableName” as “c” using (“customerId”)
“tableName”.”currentColumnName” as “newColumnName”
What are some examples of aggregate functions?
An aggregate function performs a calculation on a set of values, and returns a single value. Ex: min(), sum(), and every(), max()
What is the purpose of a group by clause?
the group the results of aggregate functions . It basically allows us to create our own collection of data within a collection of data
