1/36
Week 2
Who proposed the relational database model, and when?
Dr. Edgar Codd, in 1970 at IBM
In relational terminology, what are columns and rows also called?
Columns = attributes/fields
Rows = records/tuples
What does each table, row, columns represent?
TABLE: one entity type such as customer or product
ROW: represent instance of that type of entity
COLUMNS: represent values attributed to that instance
What is a Row?
a dataset representing a single item
tuple or record
Column
a labeled element of a tuple e.g. address or DOB
Attribute/field
Table
a set of tuples sharing the same attributes; a set of cols and rows
relation or base relvar
View or Result set
Any set of tuples: a data report from the RDBMS in response to a query
derived relvar
Tuple
unique and unordered
Field properties: named, typed, single-valued
What are the required properties of a good Primary key?
Uniquely identifies one row, is constant over time, and is never NULL
What is a Foreign key?
One or more fields in a child table that identify a single row in another (parent) table
In table design, when a relationship is many-to-many, what should you create?
A junction (bridge) table
SQL is described as a declarative (nonprocedural language. What does that mean?
You specify WHAT result you want, not HOW to get it
What are the types of SQL statements?
Schema (DDL)
Data (DML)
Transaction Control (TCL)
Data Control (DCL)
Explain all the purposes of these SQL statements: DDL, DML, TCL, DCL
DDL - define structures (create, alter, drop)
DML - manipulate data (select, insert, update, delete)
TCL - transactions (commit, rollback)
DCL - permissions (grant, revoke)
List the clauses of a SELECT statement in their written order.
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
What terms specify a column or expression using shorthand?
SELECT t1.*, t2*
FROM t1 INNER JOIN t2
OR it can be a built in function call:
SELECT AVG(score), t1.* FROM t1 ..,
Can a select expr be given a column alias?
Yes used as the expression’s columns name
SELECT CONCAT(last_name,’,’,first_name) AS full_name FROM mytable ORDER BY full_name
Why can’t you refer to a column alias inside a WHERE clause?
Bc the column’s value/alias might not yet be determined when WHERE is evaluated
What happens when you name more than one table?
you are performing a join
for each table specified you can also specify a table alias
How do you use SELECT with columns from a Single Table
SELECT SKU, SKU_description, Department, Buyer
FROM SKU_DATA;
What do the SQL wildcard characters and % represent in a LIKE pattern?
_ = exactly one unspecified character;
% = any sequence of characters (including none)
What is the difference between the WHERE and HAVING clauses?
WHERE determines which rows form the groups; HAVING determines which groups appear in the final result
In MySQL, when you CREATE TABLE enrollments with PRIMARY KEY (student_id, course_id, semester), what doest his create?
A composite primary key made from all three columns together
What happens if you try to INSERT a row with a student_id that already exists as a Primary Key value?
MySQL rejects it with a ‘Duplicate entry’ error
Does every relational table need to have a Primary key?
No - MySQL allows tables without a PK, though it is generally undesirable for entity tables
What is the difference between DROP and TABLE and DELETE FROM (with no WHERE)?
DROP TABLE removes the table structure entirely; DELETE removes rows but keeps the table
GROUP BY
groups data by column values
HAVING
What does it do and how is it typically formed?
specifies conditions on groups, formed by the GROUP BY clause
ORDER BY
sorts the result set using either raw column data or expressions of them; ASC, DESC
How can columns selected for output be referred to in ORDER BY and GROUP BY clauses
using column names, column aliases, or column positions (in the select_expr)
What is the general structure of RDBMS
Underlying storage devices holding the actual data
Set of executing processes providing access to the data
Schema (DDL) and SQL code (DML)
Known as the DB instance
All of it referred to as the database server
MySQL
databases are used everywhere
simple and easy to use
open-source software
implements the relational model
designed for storing structured data
feature-rich SQL support
part of the LAMP stack
What are the MySQL column data types?
Columns store data, so they have data types associated
Character Types
Numeric
Date (temporal) - several types and formats
Other: BLOB - Binary Large Object and TEXT
Character types
CHAR - fixed length
VARCHAR - variable length
ENUM - finite set of values
Numeric types
integer - several ranges
floating-point - two ranges, may specify precision and scale
What is the MySQL Client?
The MySQL command-line interface (cli) is called mysql
It is a simple SQL tool with input line editing capabilities
What are some cli options?
database - the database to use
execute - execute the statement and quit
user - MySQL username to use when connecting to server
password - password to use when connecting to the server