SQL

Question 1

What are some differences between SQL and NoSQL?

Answer 1

NoSQL(document-oriented storage) stores data in graphs, kv pairs, or documents (similar to JSON). Documents are stored in collections that are similar to SQL’s tables to hold related entries. No schema or restrictions to particular columns or fields.

Question 2

What is normalization?

Answer 2

A technique of not storing duplicate information but instead storing a reference to it, like SQL storing photos in a separate table

Question 3

What is denormalization?

Answer 3

NoSQL where a single query can retrieve all the information we need however data is duplicated so if we want to update we need to change the same thing in multiple places

Question 4

What is a transaction?

Answer 4

• A unit of work performed against a database. All work in the transaction must succeed or it’s rolled back. “All or nothing”
• We can trust our data is consistent across tables because operations are in a transaction

Question 5

What is a RDBMS?

Answer 5

Relational database management system (Oracle)

Question 6

What is a schema?

Answer 6

The description of the organization of a database table, columns, and data types

Question 7

What is the difference between static typing and dynamic typing?

Answer 7

Static requires you to specify the type of data upfront

Question 8

List some common SQL datatypes

Answer 8

• Boolean
• INT
• FLOAT
• VARCHAR (255)
• TEXT (unlimited length)
• DATE
• DATETIME
• TIME
• BLOB (binary data; eg an image)

Question 9

What is a foreign key?

Answer 9

A value in a database table whose responsibility is to point to a row in a different table.

Question 10

What does SQL stand for?

Answer 10

Structured query language. It’s a domain-specific language to query data out of relational databases.

Question 11

What are the comparision and equality operators in SQL?

Answer 11

(, >=, <=, =, !=)

Question 12

What are the boolean operators in SQL?

Answer 12

(AND, OR, NOT)

Question 13

Provide an example of a SQL Select

Answer 13

SELECT
  *
FROM
  users
WHERE
  name = 'Ned';

Question 14

Provide an example of a SQL Insert

Answer 14

INSERT INTO
users (name, age, height_in_inches)
VALUES
(‘Santa Claus’, 876, 34);

Question 15

Provide an example of a SQL Update

Answer 15

UPDATE
  users
SET
  name = 'Eddard Stark', house = 'Winterfell'
WHERE
  name = 'Ned Stark';

Question 16

Provide an example of a SQL Delete

Answer 16

DELETE FROM
  users
WHERE
  (name = 'Eddard Stark' AND house = 'Winterfell');
---
DELETE FROM
  accounts
WHERE
  customer_id = 666;

Question 17

Provide an example of creating a users table

Answer 17

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  birth_date DATE,
  house VARCHAR(255),
  favorite_food VARCHAR(20)
);

Question 18

What considerations do we need on a self-join?

Answer 18

We should use aliases to make it clear what is being joined

SELECT
team_member.first_name, team_member.last_name,
manager.first_name, manager.last_name
FROM
employee AS team_member
JOIN
employee AS manager ON manager.id = team_member.manager_id

Question 19

What formatting would we use on a subquery table?

Answer 19

SELECT
  bands.*
FROM
  bands
JOIN (
  SELECT
    albums.*
  FROM
    albums
  WHERE
    album.type = "POP"
  GROUP BY
    album.band_id
  HAVING
    COUNT(*) > 3
  ) AS pop_group_albums ON bands.id = pop_group_albums.band_id
WHERE
  band.leader_id IN (
    SELECT
      musicians.id
    FROM
      musicians
    WHERE
      musicians.birth_yr > 1940
  )

Question 20

How should we handle NULL comparisons in SQL?

Answer 20

We want to use IS NULL or IS NOT NULL instead of == or !=