Final Take 2 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