SQL Select, Insert, Update, Delete, Join, Aggregates

Question 1

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

Answer 1

Structured Query Language

Primary way of interacting with relational databases

Javascript is an imperative programming language where as SQL is a declarative programming language

Programmers describe the results they want and the programming environment comes up with its own plant or getting those results

Question 2

How do you retrieve specific columns from a database table?

Answer 2

select “column”,

from “row”

Question 3

How do you filter rows based on some specific criteria?

Answer 3

You use the ‘where’ clause

Question 4

What are the benefits of formatting your SQL?

Answer 4

Consistency and readability

Question 5

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

Answer 5

Equals =

Less than <

Greater than >

Not equal !=

Question 6

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

Answer 6

Using the ‘limit’ clause

Question 7

How do you retrieve all columns from a database table?

Answer 7

select *

Question 8

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

Answer 8

You use the ‘order by’ clause

Question 9

How do you add a row to a SQL table?

Answer 9

You use the ‘insert’ statement

insert into “row” (“column1”, “column2” etc..)
values (‘value1’, ‘value2’, etc..)

Question 10

What is a tuple?

Answer 10

A list of values

Question 11

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

Answer 11

You separate the values in parentheses with a comma ,

insert into “row” (“column1”, “column2” etc..
values (‘value1’, ‘value2’, etc..),
‘value1’, ‘value2’, etc..)

Question 12

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

Answer 12

returning *

Question 13

How do you update rows in a database table?

Answer 13

You use the update statement with a set clause

update “products”
set “price” = 100
where “productId” = 24;

Question 14

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

Answer 14

If you do not include a where clause, it will update all price values instead of your targeted value

Question 15

How do you delete rows from a database table?

Answer 15

delete from “row”

where “example” = ‘example’

Question 16

How do you accidentally delete all rows from a table?

Answer 16

delete from “row”

If you do not use a where clause, you will delete everything in the table

Question 17

What is a foreign key?

Answer 17

A foreign key is a column that refers to values from another row

Question 18

How do you join two SQL tables?

Answer 18

select *
from “table”
join “table” using (“foreign-key”);

Question 19

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

Answer 19

select “p”.”name” as “product”,
“s”.”name” as “supplier”
from “products” as “p”
join “suppliers” as “s” using (“supplierId”);

You use the ‘as’ keyword when selecting the column

Question 20

What are some examples of aggregate functions?

Answer 20

avg, count, max, min, every, sum

Question 21

What is the purpose of a group by clause?

Answer 21

It allows you to separate rows into groups and perform aggregate functions on those groups of rows