SQL

Question 1

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

Answer 1

a domain-specific language used in programming and designed for managing data held in a relational database management system for handling structured data
it’s a declarative language, declaring what you want to happen but not how

Question 2

How do you retrieve specific columns from a database table?

Answer 2

select “productId”,
“name”,
“price”
from “products

Question 3

How do you filter rows based on some specific criteria?

Answer 3

select “productId”,
“name”,
“price”
from “products”
where “category” = ‘cleaning’;

Question 4

What are the benefits of formatting your SQL?

Answer 4

readability

Question 5

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

Answer 5

=, <, >, and !=

Question 6

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

Answer 6

Limit 5;

Question 7

How do you retrieve all columns from a database table?

Answer 7

Select *
From “whatever”;

Question 8

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

Answer 8

Order by “price” will give lowest first or asc
Order by “price” desc gives highest first

Question 9

How do you add a row to a SQL table?

Answer 9

insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’)
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

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

Question 12

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

Answer 12

Returning *; after

Question 13

How do you update rows in a database table?

Answer 13

update “actors”
set “firstName” = ‘Baby’,
“lastName” = ‘Yoda’
where “actorId” = ‘test’;

Question 14

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

Answer 14

So that you only update one row in the table

Question 15

How do you delete rows from a database table?

Answer 15

delete
from “products”
where “productId” = 24
returning *;

Question 15

How do you delete rows from a database table?

Answer 15

delete
from “products”
where “productId” = 24
returning *;

Question 16

How do you accidentally delete all rows from a table?

Answer 16

delete
from “products”;
No where clause

Question 17

What is a foreign key?

Answer 17

The keyword that links 2 different tables together
A field in one table that references another table

Question 18

How do you join two SQL tables?

Answer 18

Join “2ndtable” using (“keyword”);

Question 19

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

Answer 19

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

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