SQL/Databases

Question 1

creating tables

Answer 1

• Use CREATE TABLE statement
• First column is usually the primary key

Syntax

CREATE TABLE table_name (

id INTEGER PRIMARY KEY,
col1 TYPE,
col2 TYPE

)

Question 2

primary key

Answer 2

• Unique identifier for a table record
• Added to column during creation of table

Syntax [2]:

• id INTEGER PRIMARY KEY
• id INTEGER PRIMARY KEY AUTOINCREMENT
  
  • This syntax will leave id creation to the database

Question 3

INSERT INTO

Answer 3

• Adds data to database

Syntax [2]:

INSERT INTO table_name
VALUES (datum1, datum2, datum3);

INSERT INTO table_name (col2, col3)
VALUES (datum2, datum3);

• With this syntax, can exclude certain values when inserting data, in particular the id
• Must have set PRIMARY KEY to AUTOINCREMENT during table creation

Question 4

SELECT

Answer 4

• Used to get data from table
• add DISTINCT to return only unique results

Syntax:

SELECT col1, col2, col3 FROM table_name

Question 5

clauses

Answer 5

• basically sql commands

4 clauses:

1. DISTINCT
   
   • Add to SELECT to remove duplicates
     
     • SELECT DISTINCT city FROM country
   • Add to COUNT clause to only count values once
     
     • SELECT COUNT (DISTICT city) FROM country
2. ORDER BY col_name
   
   • sorts results
   • sort in reverse order by adding negative
3. WHERE col_name ><= value
   
   • filters out results
4. HAVING agg_col ><= value
   
   • just like where, but used on alias of column created by aggregate function (agg_col)
   • ex: SELECT SUM(sales) as total_sales FROM store HAVING total_sales>1000

Question 6

aggregate functions

Answer 6

• functions that combine values from multiple rows
• Functions
  
  • SUM
  • MAX
  • MIN
  • COUNT
  • AVG

SELECT FUNC(col_name) FROM table_name

Question 7

GROUP BY

Answer 7

• used in conjuctino with aggregate functions
• basically, creates groups on which aggregate functions are performed
• added to the end of a SELECT statement

ex: SELECT SUM(quantity) FROM groceries GROUP BY aisle;
* instead of providing the sum over entire column, will generate sum for each aisle

Question 8

operators

Answer 8

• Use with WHERE to add additional conditions to filter by
• Can include parantheses to control how logic is evaluated

5 operations

1. AND
   
   • Returns rows if all conditions are met
   • WHERE col1>5 AND col2<5
2. OR
   
   • Returns rows if any of conditions are met
   • WHERE col1>5 OR col2=10
3. IN
   
   • Shorthand for multiple or operators
   • Returns rows if the column matches any of the values in the list
   • WHERE genre IN (“disco”, “jazz”, “funk”)
   • Values inside parentheses can also be a subquery
4. NOT IN
   
   • Like IN, but returns row if none of conditions are met
   • WHERE genre NOT IN (“electronic”, “new age”)
5. LIKE
   
   • Used for inexact matching
   • SELECT * FROM genre WHERE genre LIKE “%hip%”
   • Often used with percentage signs (which act as wildcards)

Question 9

subqueries

Answer 9

• using the result of on query inside another query
• the queries can be from different tables
• often used with IN operator or FROM
• ex: SELECT * FROM music WHERE artist IN (SELECT stars FROM billboards);

Question 10

AS

Answer 10

• Assigns an alias to a column, usually an aggregate function column
• Used to rename columns on the fly
• SELECT col1 AS new_name

Question 11

CASE

Answer 11

• Adds a label to a record based on some condition
• SQL’s way of handling if/then logic
• creates a new column, like an aggregate function

Syntax:

CASE
WHEN col1>‘val1’ THEN ‘resultA’
ELSE ‘resultB’
END AS col_output

• 4 parts: WHEN, THEN, ELSE, END AS
• WHEN: evaluate the row
• THEN: assigns a value if condition is met
• ELSE: assign as value if condition isn’t met
• END AS: assigns an alias to the new column

Question 12

cross join

Answer 12

• naively matches every row with every other row
• put both table names after FROM

SELECT * FROM table_1, table_2

Question 13

order of execution

Answer 13

• Order in which sql actions are performed

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY

Question 14

inner join

Answer 14

• finds records that have matching values in both tables
• and combines them in one row in output
• query will only return records from either table when match is found

2 approaches:

1. Implicit inner join

SELECT *
FROM table_1, table_2
WHERE table_1.col = table_2.col

• 2. Explicit inner join*

SELECT *
FROM table1
INNER JOIN table2
ON table_1.col = table2.col

• Explicit inner join is best practice

Question 15

outer join

Answer 15

• find records that have matching values in both tables
• and combines them in one row in output
• but keeps all rows from one table
• and populates missing field in the other table with null

2 flavors:

1. left outer join
   
   • returns left table, and matches in right table
2. right outer join
   
   • returns right table, and matches in left table

Syntax [very similar to inner join]:

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table_1.col = table2.col

Question 16

SQL query lifecycle

Answer 16

3 steps

1. Parse
   
   • makes sure that query is syntactically correct and that tables exist
   • turns query into algebraic expression
2. Optimize
   
   • optimizes command (ex, turns 5*10 into 50)
   • estimates cost (CPU and time) for each potential approach to fulfilling query
   • chooses optimal approach
3. Execute
   
   • takes plan and turns it into operations on database

Question 17

query tuning

Answer 17

• process of optimizing queries

Steps

1. Use an SQL profiler to identity which queries are taking the longest amount of time
2. Get execution plan for a specific command
   
   • ​syntax varies based on environment
   • ex: EXPLAIN QUERY PLAN select * from movies
3. Optimize query manually

Question 18

changing records

Answer 18

• Use UPDATE
• changes data in database
• tell databse what content to change (with SET) and which row to update (with WHERE)

Syntax

UPDATE table_name
SET co1_1 = val_1, col_2 = val_2,
WHERE condition;

Question 19

deleting records

Answer 19

• Use DELETE
• remove records from database
• if WHERE is omitted, will remove all records

Syntax:

DELETE FROM table_name
WHERE condition;

Question 20

changing schema

Answer 20

• ALTER TABLE
• changes the composition of the table after creation
• adds a new column

Syntax:

ALTER TABLE table_name
ADD col_name TYPE;

Question 21

deleteing table

Answer 21

• Use DROP TABLE

Syntax

DROP TABLE table_name

Question 22

get string length

Answer 22

• Use LENGTH function (LEN in some variations)

SELECT LENGTH(col_name) FROM table

Question 23

NoSQL

Answer 23

• a variety of technologies that don’t use sql or tables
• one type is a document database
• ex:
  
  • MongoDB

Question 24

Mongoose

Answer 24

• NPM package for connecting to a MongoDB database
• It’s a node thing (whereas MondoDB is just a database in general)

Question 25

ACID properties

Answer 25

• Atomicity
  
  • either all operatins in transactions occur, or none
  • no half transactions
• Consistency
  
  • correctness
  • any transaction will bring the db to a valid state
• Isolation
  
  • if a database is queried concurrently, you get the same result as if you’d quereid sequentially
• Durability
  
  • persistence
  • changes are permanent
  • once a record is created/updated, it will remain, even if there power loss, crash, etc.