w2d6-7 (SQL Intro) Flashcards
What is an RDMBS?
A relational database management system.
What do the rows and columns of a database table represent?
Each row represents a single entity, and each column denotes a specific piece of data for that entity.
What is a primary key, and what is the most common one in SQL?
A primary key is the unique identifier for a row of data, and the most typical one is ‘id’
In Rails, what does each table usually hold?
One type of resource (users, people, houses, posts, etc.)
What is a database schema, and what must you decide to implement it?
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.
What is the difference between static and dynamic typing?
Static typing requires the data type of a variable be specified and immutable (SQL), whereas this is not a requirement in dynamic typing (Ruby)
What is a foreign key?
An entry in a table which points to a row (primary key) in a different table; used for associations between tables.
What is the convention for naming a foreign key?
[other_table_name_singularized]_id
What do the ‘SELECT’, ‘FROM’, and ‘WHERE’ clauses do?
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).
What are the 4 main data manipulation operations in SQL?
SELECT, INSERT, UPDATE, DELETE
What are the 3 operators to manipulate a database schema?
CREATE TABLE, ALTER TABLE, DROP TABLE
What does the ‘JOIN’ clause do?
It allows us to query across tables by associating a foreign key with a primary id.
What are the two components of the SQL language?
Data Definition Language (DDL), and Data Manipulation Language (DML)
Explain an INNER JOIN, FULL OUTER JOIN, and LEFT OUTER JOIN
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.
Write the SQL query to join TableA and TableB on their name column value and then exclude all matching results.
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null
What are good SQL naming conventions as they relate to Rails?
Name files in snake_case and pluralize them. Also, use the ‘id’ column as the primary key for each table.
What is a database connection?
A connection between the user/application and the SQL server. It is held until one of the two parties breaks the connection.
What is a query optimizer?
A part of the server which takes a properly formed query and determines the most efficient way to execute it.
Explain the SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses.
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
T/F: SQL queries are ternimated by a ‘;’
T
How do yo add a column alias to a SELECT clause?
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;
How do you remove duplicate returns from a SELECT clause?
Add the ‘DISTINCT’ keyword:
SELECT DISTINCT cust_id FROM account;
What is a subquery?
A query within a query, which can be used to further narrow down results.
What is the syntax for a subquery?
Enclose it in parentheses and reference it by an alias (‘e’ below):
SELECT emp_id FROM (SELECT fname FROM employee) e;
What is a view?
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;
How do you define a table alias?
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.
What are the primary logical operators for the WHERE clause? How can they be grouped?
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’);
How do you order across multiple columns?
Separate the orderings by parentheses:
mysql> SELECT open_emp_id, product_cd -> FROM account -> ORDER BY open_emp_id, product_cd;
What is the keyword to add to the end of an ORDER BY clause to specify ascending or descending order?
ASC (default) and DESC
How do you sort via an expression?
Add it after ORDER BY:
mysql> SELECT cust_id, cust_type_cd, city, state, fed_id -> FROM customer -> ORDER BY RIGHT(fed_id, 3);
