SQL

Question 1

What is SQL and how is it different from languages like JavaScript?

Answer 1

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.

Question 2

How do you retrieve specific columns from a database table?

Answer 2

• 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”;

Question 3

How do you filter rows based on some specific criteria?

Answer 3

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';

Question 4

What are the benefits of formatting your SQL?

Answer 4

• 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.

Question 5

What are four comparison operators that can be used in a where clause?

Answer 5

=, <, >, !=

Question 6

How do you limit the number of rows returned in a result set?

Answer 6

• 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;

Question 7

How do you retrieve all columns from a database table?

Answer 7

Use the * asterisk character instead of column names.
Good for seeing what type of data is inside the table.
ex.
select *
from “”;

Question 8

How do you control the sort order of a result set?

Answer 8

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;

Question 9

How do you add a row to a SQL table?

Answer 9

• 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’);

Question 10

What is a tuple?

Answer 10

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.

Question 11

How do you add multiple rows to a SQL table at once?

Answer 11

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.

Question 12

How do you get back the row being inserted into a table without a separate select statement?

Answer 12

• 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 *;

Question 13

How do you update rows in a database table?

Answer 13

• 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;

Question 14

Why is it important to include a where clause in your update statements?

Answer 14

A where clause is necessary in order to target only specific rows.
If it is excluded, it will update everything.

Question 15

How do you delete rows from a database table?

Answer 15

An SQL delete statement is how rows get removed from tables.

Question 16

How do you accidentally delete all rows from a table?

Answer 16

You can accidentally delete all rows by excluding a where clause.

Question 17

What is a foreign key?

Answer 17

It is a column in a table that has values constrained to match a column in another table.

Question 18

How do you join two SQL tables?

Answer 18

Use the join clause.
ex. 
select *
  from "products"
  join "suppliers" using ("supplierId");

Question 19

How do you temporarily rename columns or tables in a SQL statement?

Answer 19

You use the "as" keyword.
ex. 
select "products"."name" as "product",
       "suppliers"."name" as "supplier"
  from "products"
  join "suppliers" using ("supplierId");

Question 20

What are some examples of aggregate functions?

Answer 20

max(), min(), avg(), sum()

Question 21

What is the purpose of a group by clause?

Answer 21

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.

Question 22

What do aggregate functions do?

Answer 22

They aggregate a list or row into one value