w2d6-7 (SQL Intro)

Question 1

What is an RDMBS?

Answer 1

A relational database management system.

Question 2

What do the rows and columns of a database table represent?

Answer 2

Each row represents a single entity, and each column denotes a specific piece of data for that entity.

Question 3

What is a primary key, and what is the most common one in SQL?

Answer 3

A primary key is the unique identifier for a row of data, and the most typical one is ‘id’

Question 4

In Rails, what does each table usually hold?

Answer 4

One type of resource (users, people, houses, posts, etc.)

Question 5

What is a database schema, and what must you decide to implement it?

Answer 5

A database schema is a description of the organization of your database into tables and columns. You must decide what tables to have, what columns each table will have, and the data type of each column.

Question 6

What is the difference between static and dynamic typing?

Answer 6

Static typing requires the data type of a variable be specified and immutable (SQL), whereas this is not a requirement in dynamic typing (Ruby)

Question 7

What is a foreign key?

Answer 7

An entry in a table which points to a row (primary key) in a different table; used for associations between tables.

Question 8

What is the convention for naming a foreign key?

Answer 8

[other_table_name_singularized]_id

Question 9

What do the ‘SELECT’, ‘FROM’, and ‘WHERE’ clauses do?

Answer 9

SELECT takes a list of comma separated column names, FROM specifies a table to query, and WHERE specifies which rows to select depending on certain values of their columns (i.e. filters the data).

Question 10

What are the 4 main data manipulation operations in SQL?

Answer 10

SELECT, INSERT, UPDATE, DELETE

Question 11

What are the 3 operators to manipulate a database schema?

Answer 11

CREATE TABLE, ALTER TABLE, DROP TABLE

Question 12

What does the ‘JOIN’ clause do?

Answer 12

It allows us to query across tables by associating a foreign key with a primary id.

Question 13

What are the two components of the SQL language?

Answer 13

Data Definition Language (DDL), and Data Manipulation Language (DML)

Question 14

Explain an INNER JOIN, FULL OUTER JOIN, and LEFT OUTER JOIN

Answer 14

An inner join produces a table consisting of rows where a certain column value matches.

A full outer join produces a set of all records where a certain column value matches. If there is no match, the record in the other table will contain null.

A left outer join does the samething, except all records are retained in the first table, and only matching records in the second table are retained.

Question 15

Write the SQL query to join TableA and TableB on their name column value and then exclude all matching results.

Answer 15

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

Question 16

What are good SQL naming conventions as they relate to Rails?

Answer 16

Name files in snake_case and pluralize them. Also, use the ‘id’ column as the primary key for each table.

Question 17

What is a database connection?

Answer 17

A connection between the user/application and the SQL server. It is held until one of the two parties breaks the connection.

Question 18

What is a query optimizer?

Answer 18

A part of the server which takes a properly formed query and determines the most efficient way to execute it.

Question 19

Explain the SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses.

Answer 19

Select Determines which columns to include in the query’s result set

From Identifies the tables from which to draw data and how the tables should be joined

Where Filters out unwanted data

Group by Used to group rows together by common column values

Having Filters out unwanted groups

Order by Sorts the rows of the final result set by one or more columns

Question 20

T/F: SQL queries are ternimated by a ‘;’

Answer 20

T

Question 21

How do yo add a column alias to a SELECT clause?

Answer 21

Add the alias after the corresponding element in the SELECT clause:

mysql> SELECT emp_id, -> ‘ACTIVE’ status, -> emp_id * 3.14159 empid_x_pi, -> UPPER(lname) last_name_upper -> FROM employee;

Question 22

How do you remove duplicate returns from a SELECT clause?

Answer 22

Add the ‘DISTINCT’ keyword:

SELECT DISTINCT cust_id FROM account;

Question 23

What is a subquery?

Answer 23

A query within a query, which can be used to further narrow down results.

Question 24

What is the syntax for a subquery?

Answer 24

Enclose it in parentheses and reference it by an alias (‘e’ below):

SELECT emp_id FROM (SELECT fname FROM employee) e;

Question 25

What is a view?

Answer 25

A table generated by a select statement that is temporarily stored as a variable and can be queried:

mysql> CREATE VIEW employee_vw AS -> SELECT emp_id, fname, lname, -> YEAR(start_date) start_year -> FROM employee;

Question 26

How do you define a table alias?

Answer 26

In the FROM clause:

SELECT e.emp_id, e.fname, e.lname, d.name dept_name FROM employee AS e INNER JOIN department AS d ON e.dept_id = d.dept_id;

Note that the alias is assigned in the FROM clause but used in the SELECT and WHERE clauses (FROM is evaluated first, then WHERE, then SELECT, which is why this works). Also, note that the ‘AS’ keyword is optional.

Question 27

What are the primary logical operators for the WHERE clause? How can they be grouped?

Answer 27

AND, OR, and NOT, and they can be grouped with parentheses:

mysql> SELECT emp_id, fname, lname, start_date, title -> FROM employee -> WHERE (title = ‘Head Teller’ AND start_date > ‘2006-01-01’) -> OR (title = ‘Teller’ AND start_date > ‘2007-01-01’);

Question 28

How do you order across multiple columns?

Answer 28

Separate the orderings by parentheses:

mysql> SELECT open_emp_id, product_cd -> FROM account -> ORDER BY open_emp_id, product_cd;

Question 29

What is the keyword to add to the end of an ORDER BY clause to specify ascending or descending order?

Answer 29

ASC (default) and DESC

Question 30

How do you sort via an expression?

Answer 30

Add it after ORDER BY:

mysql> SELECT cust_id, cust_type_cd, city, state, fed_id -> FROM customer -> ORDER BY RIGHT(fed_id, 3);