Final Take 2 Flashcards

1
Q

TINYINT

A

Maximum value unsigned 255, signed -128, 128

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

BOOLEAN

A

Not directly implemented in MySQL, instead TINYINT(1); 0=False; 1=True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

ENUM

A

Allows a fixed number of pre-defined values to be specified

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

AUTO_INCREMENT

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

INSERT using value(s) from another table

A

nstead of looking up values needed to compose an INSERT statement, include a query that will find and use the
needed values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Foreign Key specifications

A

ON UPDATE and/or ON DELETE
§ CASCADE
§ SET NULL
§ SET DEFAULT
§ NO ACTION

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Alter Table: Add/Drop Foreign Key Constraint

A

You can’t modify a FOREIGN KEY constraint; it must be dropped and added.

ALTER TABLE tablename
DROP FOREIGN KEY constraint_name_fk;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SELECTION

A

performed through the WHERE clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

PROJECTION

A

performed through the SELECT clause of SELECT statement

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

UNION

A

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

DIFFERENCE

A

SELECT *
FROM salesRep
WHERE empID NOT IN (
SELECT empNum
FROM techSalesRep);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

PRODUCT

A

SELECT *
FROM salesRep INNER JOIN techSalesRep;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

INTERSECTION

A

SELECT salesRep.firstName, salesRep.lastName, salesRep.city, salesRep.state
FROM salesRep
WHERE empID IN (
SELECT empNum
FROM techSalesRep);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

EQUIJOIN

A

SELECT *
FROM territory INNER JOIN district
ON territory.distID = district.distID;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

NATURAL JOIN

A

SELECT territory.terrID,
territory.terrName,
territory.salesRepID,
district.distID,
distName
FROM territory INNER JOIN district
ON territory.distID = district.distID;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

LEFT OUTER

A

SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory LEFT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID;

17
Q

RIGHT OUTER

A

SELECT territory.terrName, salesRep.firstName, salesRep.lastName
FROM territory RIGHT OUTER JOIN salesRep
ON territory.salesRepID = salesRep.empID;

18
Q

mySQL ALT to FULL OUTER

A

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;

19
Q

When to appropriately use DISTINCT keyword

A

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 …

20
Q

Table Aliases

A

SELECT art.artist, s.songTitle
FROM song AS s RIGHT JOIN artist art
ON s.artistID = art.artistID;

21
Q

Multi-table join

A

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;

22
Q

UNIQUE Constraint

A

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;

23
Q

LOWER/UPPER

A

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

24
Q

LENGTH

A

LENGTH(s) takes a string ‘s’ and returns the number of characters in the string

25
Q

INSTR

A

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

26
Q

SUBSTR

A

SUBSTR(s, n, m) takes a string ‘s’ and returns the string that begins at position ‘n’ and goes
for ‘m’ characters

27
Q

CONCAT

A

CONCAT(s1, s2) appends s2 to s1
|| is the SQL concatenation operator

28
Q

ROUND/TRUNCATE

A

ROUND(n, m) rounds ‘n’ to ‘m’ decimal places

TRUNCATE(n, m) truncates ‘n’ after ‘m’ decimal places

29
Q

MOD

A

MOD(n, m) returns the remainder when ‘n’ is divided by ‘m’.

30
Q

Subquery

A

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

31
Q

AVG

A

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;

32
Q

COUNT

A

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

33
Q

MAX/MIN

A

MAX(column) returns the maximum non-null value in column

MIN(column) returns the minimum non-null value in column

34
Q

SUM

A

SUM(column) returns the sum of all values in column

SUM(DISTINCT column) returns the sum of all distinct values in column

35
Q

GROUP BY

A

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

36
Q

HAVING

A

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

37
Q

ORDER BY

A

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