Midterm Practical Flashcards
Create a database
CREATE DATABASE databaseName;
Open a database
USE databaseName;
Create a table with attributes
CREATE TABLE employee(
employeeID CHAR(11),
ssn CHAR(11),
name VARCHAR(35),
dob DATE,
CONSTRAINT employee_pk PRIMARY KEY(employeeID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Remove a table within the current database
DROP tableName;
Remove a database
DROP DATABASE databaseName;
DROP DATABASE IF EXISTS test;
View metadata commands
SHOW DATABASES; SHOW TABLES; DESCRIBE tableName;
Run a script
. filepath.sql
INSERT syntax
INSERT INTO tableName
(filedlist) VALUES (valuelist);
SELECT all values
SELECT *
FROM tableName;
Select some values
SELECT attributes_to_include
FROM tableName;
Select with a column alias
SELECT attribute as “longer attribute”
FROM tableName;
WHERE clause syntax
SELECT * | attributes_to_include
FROM tableName
WHERE condition(s);
Logical Operators
NOT, AND, OR
How to find NULL values
SELECT employeeID
FROM employee
WHERE name IS NULL;
INSERT NULL values
INSER INTO tableNAME (test, test2)
VALUES(‘3333’, NULL);
SMALLINT
-32768 to 32768 OR 0 to 65535
DECIMAL
DECIMAL(p,s)
§ Example: DECIMAL(5,2) -> ###.##
SIGNED VS UNSIGNED
UNSIGNED can store only zero and positive numbers in a column. SIGNED can hold zero, negative and positive
How to apply a not null constraint
title VARCHAR(255) NOT NULL,
Set default constraint
replacementCost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
Relational Operators
> Greater Than
= Greater Than or Equal To
< Less Than
<= Less Than or Equal To
= Equal To
<> Not Equal To
!= Not Equal To
IN (list) Contained in comma-separated list
LIKE string Matches string pattern
BETWEEN
will include values that are greater than, or equal to, the minimum
value and less than, or equal to, the maximum value specified
SELECT * | attribute(s)
FROM table
WHERE attribute BETWEEN min_value AND max_value;
USING LIKE with WILDCARDS
To specify our pattern we use characters and wildcards
§ Characters must be present
§ Wildcards are placeholders for characters
§ _ means exactly 1 character
§ % means 0 or more characters
Using select calculations
SELECT title, length, TRUNCATE(length/60, 0), length MOD 60
FROM film;
SELECT title, length AS “Total Minutes”,
TRUNCATE(length/60,0) AS “Hours”, length MOD 60 AS “Minutes”
FROM film;
UPDATE syntax
Modifies existing user data
General Syntax Format:
UPDATE tableName
SET field1 = value1, …, fieldN = valueN
WHERE condition;
DELETE syntax
Removes record(s) from a table
General Syntax Format:
DELETE
FROM tableName
WHERE condition(s);
ALTER table to add/drop primary key
Let’s get rid of the PRIMAY KEY constraint:
ALTER TABLE film
DROP PRIMARY KEY;
Then add it back:
ALTER TABLE film
ADD PRIMARY KEY film_pk (filmID);
add/drop columns
General Syntax Format:
ALTER TABLE tableName
{ADD|DROP} COLUMN columnspec;
changing defaults
Defaults are the only “property” that you can alter.
Let’s add a default value of “Not entered.” to the description column in the table:
ALTER TABLE film
ALTER description SET DEFAULT ‘Not entered.’;
And if we change our mind and don’t want it after all:
ALTER TABLE film
ALTER description DROP DEFAULT;
Modifying columns
ALTER TABLE tableName
MODIFY COLUMN attribute DATATYPE [NOT NULL] [DEFAULT];
1NF to BCNF breakdowns
1NF — no repeating groups, attr names are unique, 1NF - has primary key, no repeating attributes
2NF — partial dependencies (functional dependencies are only part of the primary key)
3NF— transitive dependencies on non-key elements to non-key elements.
BCNF — check that left hand side of functional dependencies are candidate keys.
