Final Exam Practical Flashcards
TINYINT
Maximum value unsigned 255, signed -128, 128
BOOLEAN
Not directly implemented in MySQL, instead TINYINT(1); 0=False; 1=True
ENUM
Allows a fixed number of pre-defined values to be specified
AUTO_INCREMENT
Can be used with whole number data types to automatically supply a value incremented by 1, for each record inserted.
To utilize, include in attribute specification, then don’t supply a value for the respective attribute when inserting new records
0 or NULL will also cause an automatic value to be used for the respective attribute
INSERT using value(s) from another table
nstead of looking up values needed to compose an INSERT statement, include a query that will find and use the
needed values
Foreign Key specifications
ON UPDATE and/or ON DELETE
§ CASCADE
§ SET NULL
§ SET DEFAULT
§ NO ACTION
Alter Table: Add/Drop Foreign Key Constraint
You can’t modify a FOREIGN KEY constraint; it must be dropped and added.
ALTER TABLE tablename
DROP FOREIGN KEY constraint_name_fk;
SELECTION
performed through the WHERE clause
PROJECTION
performed through the SELECT clause of SELECT statement
UNION
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
DIFFERENCE
SELECT *
FROM salesRep
WHERE empID NOT IN (
SELECT empNum
FROM techSalesRep);
PRODUCT
SELECT *
FROM salesRep INNER JOIN techSalesRep;
INTERSECTION
SELECT salesRep.firstName, salesRep.lastName, salesRep.city, salesRep.state
FROM salesRep
WHERE empID IN (
SELECT empNum
FROM techSalesRep);
EQUIJOIN
SELECT *
FROM territory INNER JOIN district
ON territory.distID = district.distID;
NATURAL JOIN
SELECT territory.terrID,
territory.terrName,
territory.salesRepID,
district.distID,
distName
FROM territory INNER JOIN district
ON territory.distID = district.distID;
LEFT OUTER
SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory LEFT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID;
RIGHT OUTER
SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory RIGHT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID;
mySQL ALT to FULL OUTER
SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory LEFT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID
UNION
SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory RIGHT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID;
When to appropriately use DISTINCT keyword
When attempting a relational algebra using SQL, the DBMS may not produce a relation as
duplicate rows can be in the result set.
§ Can use the DISTINCT key word as part of SELECT clause of SELECT statement to remove
duplicate rows from a result set.
§ Not needed for every SELECT statement written!
§ Only those where you are asked to produce a relational algebra operation using SQL.
§ Format: SELECT DISTINCT …
Table Aliases
SELECT art.artist, s.songTitle
FROM song AS s RIGHT JOIN artist art
ON s.artistID = art.artistID;
Multi-table join
SELECT artist.artist, cd.cdTitle
FROM ((artist INNER JOIN song
ON artist.artistID = song.artistID)
INNER JOIN cdMakeup
ON song.songID = cdMakeup.songID)
INNER JOIN cd
ON cdMakeup.cdID = cd.cdID;
UNIQUE Constraint
CREATE TABLE artist(
artistID INTEGER UNSIGNED,
artist VARCHAR(50) NOT NULL,
CONSTRAINT artist_pk PRIMARY KEY (artistID),
CONSTRAINT artist_artist_un UNIQUE(artist)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOWER/UPPER
LOWER(s) takes a string ‘s’ and returns a string with all lower-case letters
§ UPPER(s) takes a string ‘s’ and returns a string with all upper-case letters
§ Very useful when doing string comparisons in a case sensitive environment.
§ Although MySQL is NOT case sensitive, you should utilize one of these functions if a task
says, “ensure that case does not impact your results”.
SELECT *
FROM song
WHERE UPPER(songTitle) = ‘BLUE’;
songID songTitle length artistID
LENGTH
LENGTH(s) takes a string ‘s’ and returns the number of characters in the string
INSTR
INSTR(s1,s2) (or LOCATE(s2,s1) with MySQL) looks in string s1 for string s2 and returns the starting position of where they match up; returns 0 if s2 is not in s1
SUBSTR
SUBSTR(s, n, m) takes a string ‘s’ and returns the string that begins at position ‘n’ and goes
for ‘m’ characters
CONCAT
CONCAT(s1, s2) appends s2 to s1
|| is the SQL concatenation operator
ROUND/TRUNCATE
ROUND(n, m) rounds ‘n’ to ‘m’ decimal places
TRUNCATE(n, m) truncates ‘n’ after ‘m’ decimal places
MOD
MOD(n, m) returns the remainder when ‘n’ is divided by ‘m’.
Subquery
SELECT deptName
FROM department
WHERE collegeID IN (
SELECT collegeID
FROM college
WHERE collegeName = ‘Golisano College of Computing
and Information Sciences’);
IN will handle any number of resulting records from the subquery
Equal (=) will only handle one resulting record from the subquery
AVG
AVG(column) returns the average value in the column
Null values are not included; zeros are included
SELECT AVG(length) AS ‘Avg Length’
FROM song;
AVG(DISTINCT column) returns the average of the distinct values in the column
SELECT AVG(DISTINCT length) AS ‘Avg Length’
FROM song;
COUNT
COUNT(*) returns the number of rows in the table
COUNT(column) returns the number of rows with non-Null values in column
COUNT(DISTINCT column) returns the number of rows with non-Null, distinct values incolumn
MAX/MIN
MAX(column) returns the maximum non-null value in column
MIN(column) returns the minimum non-null value in column
SUM
SUM(column) returns the sum of all values in column
SUM(DISTINCT column) returns the sum of all distinct values in column
GROUP BY
The GROUP BY clause allows aggregate function results to be based on specified groupings
of records.
Should GROUP BY all attributes in SELECT clause that are NOT part of an aggregatefunction call
HAVING
In stead of using the WHERE clause, SQL provides us with the HAVING clause
Use the HAVING clause to place restrictions according to the results of aggregate functions
ORDER BY
Used to sort the rows in a result set
An attribute can be sorted in ascending (default) or descending order
Can have multiple levels of sorting
MUST be used on assignments when sorting is required
Other clauses may accomplish sorting, however for assessment purposes use only ORDER BY
