What is SQL and how is it different from languages like JavaScript?
Structured Query Language(SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
SQL is a declarative programming language. In declarative languages, programmers describe the results they want and the programming environment comes up with its own plan for getting those results.
How do you retrieve specific columns from a database table?
- The query starts with the select keyword.
- The select keyword is followed by a comma-separated list of column names, each surrounded by “ double quotes.
- The column names are followed by a from clause specifying which table to retrieve the data from.
- The query must end in a ; semicolon.
ex.
select “name”,
“price”
from “products”;
How do you filter rows based on some specific criteria?
Use the WHERE statement when filtering for specific values or the SELECT statement when filtering for specific attributes
ex.
select "productId",
"name",
"price"
from "products"
where "category" = 'cleaning';What are the benefits of formatting your SQL?
- Helps us to make the queries more readable and easy to understand.
- When the queries are formatted and laid out with proper spacing it comes easy to find the errors in the query and fix the error.
- Formatted queries are easy to be passed on to the fellow developers.
- Helps in increasing the efficiency of the database transaction system.
- Makes it easy to scale and deploy the database system.
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?
- Use the limit clause.
- The limit clause always comes last.
- The limit clause includes a literal integer number with no quotes to specify the maximum number of rows that should be returned.
ex.
select “name”,
“description”
from “products”
order by “price” desc
limit 1;
How do you retrieve all columns from a database table?
Use the * asterisk character instead of column names.
Good for seeing what type of data is inside the table.
ex.
select *
from “”;
How do you control the sort order of a result set?
Use the “order by” clause in the select statement to control the order of the result set.
- The order by clause comes after the from clause.
- The order by clause is followed by a column name in “ double quotes.
- The default sort order of the results is ascending order.
- The sort order of the order by clause is switched to descending order with the desc keyword.
ex.
select *
from “products”
order by “price”;
OR
select “name”,
“description”
from “products”
order by “price” desc
limit 1;
How do you add a row to a SQL table?
- The statement begins with the insert keyword.
- The table to insert into is specified in “ double quotes.
- The list of columns being inserted is wrapped in () parenthesis.
- The values being inserted are also wrapped in () in parenthesis in the same order as the columns they belong to. In SQL, a list of values is referred to as a tuple.
- Text values are wrapped in ‘ single quotes.
- Numeric values are represented with literal numbers (or decimals if applicable).
- In this particular statement, the “productId” was left out. This is because tables are often configured to auto-generate identifier attributes to avoid accidental duplicates.
Ex.
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
What is a tuple?
ex.
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’);
- The values being inserted are also wrapped in () in parenthesis in the same order as the columns they belong to. In SQL, a list of values is referred to as a tuple.
How do you add multiple rows to a SQL table at once?
ex.
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 *;
- Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas. Below we are inserting two new rows into the “products” table.
How do you get back the row being inserted into a table without a separate select statement?
- In PostgreSQL it’s possible to get the full inserted row back from the database, including it’s auto-generated attribute(s), like a productId. This is done with a returning clause.
ex.
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’)
returning *;
How do you update rows in a database table?
- An SQL update statement is a means of updating rows in a database table.
- Great care must be taken to include a where clause in your update statements to only target specific rows
ex.
update “products”
set
“price” = 100
“colName” = ‘value’ <=== (can update multiple columns)
where “productId” = 24;
Why is it important to include a where clause in your update statements?
A where clause is necessary in order to target only specific rows.
If it is excluded, it will update everything.
How do you delete rows from a database table?
An SQL delete statement is how rows get removed from tables.
How do you accidentally delete all rows from a table?
You can accidentally delete all rows by excluding a where clause.
What is a foreign key?
It is a column in a table that has values constrained to match a column in another table.
How do you join two SQL tables?
Use the join clause.
ex.
select *
from "products"
join "suppliers" using ("supplierId");How do you temporarily rename columns or tables in a SQL statement?
You use the "as" keyword.
ex.
select "products"."name" as "product",
"suppliers"."name" as "supplier"
from "products"
join "suppliers" using ("supplierId");What are some examples of aggregate functions?
max(), min(), avg(), sum()
What is the purpose of a group by clause?
The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions.
What do aggregate functions do?
They aggregate a list or row into one value
