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;