SQL

Question 1

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

Answer 1

A declarative programming language. Programmers describe the desired results, and the programming environment finds a way to get that done. In JavaScript, the programmer tells the runtime how to get the results.

Question 2

How do you retrieve specific columns from a database table?

Answer 2

select “column” from “database table”

Question 3

How do you filter rows based on some specific criteria?

Answer 3

Using the “where” keyword and some kind of condition (<, >, =, !=)

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

<, >, =, !=

Question 6

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

Answer 6

“limit” keyword followed by a number will return only the specified number of rows

Question 7

How do you retrieve all columns from a database table?

Answer 7

select * from “database table”

Question 8

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

Answer 8

default sort order is ascending order. Including “desc” on the order by line at the end changes the order to descending order

Question 9

How do you add a row to a SQL table?

Answer 9

insert into “table name” (“column-a”, “column-b”, “column-c”, “column-d”)
values (‘value-a’, ‘value-b’, ‘value-c’, ‘value-d’);
returning *

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

after the values keyword, use comma-separated tuples of values to insert more than one row

Question 12

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

Answer 12

add a line at the end of the request
returning “columnName”
(* can also be used to return all)

Question 13

How do you update rows in a database table?

Answer 13

update “table”
set “column” = ‘value’
where “column” = ‘value’;

Question 14

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

Answer 14

To make sure that the intended row is being updated, otherwise all rows will get updated to the given value

Question 15

How do you delete rows from a database table?

Answer 15

delete from “table”
where “column” = ‘value’
and “column” (<, >, !=) ‘value’
returning *;

Question 16

How do you accidentally delete all rows from a table?

Answer 16

delete from “table”;

Question 17

What is a foreign key?

Answer 17

A column that references primary key values of a column in another table

Question 18

How do you join two SQL tables?

Answer 18

select “column” or *
from “table”
join “table” using (“common column”) [this column must be a shared column]

Question 19

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

Answer 19

aliasing
select “table”.”column:before” as “column:after”,
“table-2”.”column:before” as “column:after”
from “table”
join “table-2” using (“common column”);

Question 20

What are some examples of aggregate functions?

Answer 20

max(), avg(), count(), min(), sum(), every()

Question 21

What is the purpose of a group by clause?

Answer 21

grouping together values of a column based on a common value in the group by column