Final Exam Practical

Question 1

TINYINT

Answer 1

Maximum value unsigned 255, signed -128, 128

Question 2

BOOLEAN

Answer 2

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

Question 3

ENUM

Answer 3

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

Question 4

AUTO_INCREMENT

Answer 4

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

Question 5

INSERT using value(s) from another table

Answer 5

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

Question 6

Foreign Key specifications

Answer 6

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

Question 7

Alter Table: Add/Drop Foreign Key Constraint

Answer 7

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

ALTER TABLE tablename
DROP FOREIGN KEY constraint_name_fk;

Question 8

SELECTION

Answer 8

performed through the WHERE clause

Question 9

PROJECTION

Answer 9

performed through the SELECT clause of SELECT statement

Question 10

UNION

Answer 10

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

Question 11

DIFFERENCE

Answer 11

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

Question 12

PRODUCT

Answer 12

SELECT *
FROM salesRep INNER JOIN techSalesRep;

Question 13

INTERSECTION

Answer 13

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

Question 14

EQUIJOIN

Answer 14

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

Question 15

NATURAL JOIN

Answer 15

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

Question 16

LEFT OUTER

Answer 16

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

Question 17

RIGHT OUTER

Answer 17

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

Question 18

mySQL ALT to FULL OUTER

Answer 18

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;

Question 19

When to appropriately use DISTINCT keyword

Answer 19

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 …

Question 20

Table Aliases

Answer 20

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

Question 21

Multi-table join

Answer 21

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;

Question 22

UNIQUE Constraint

Answer 22

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;

Question 23

LOWER/UPPER

Answer 23

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

Question 24

LENGTH

Answer 24

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

Question 25

INSTR

Answer 25

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

Question 26

SUBSTR

Answer 26

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

Question 27

CONCAT

Answer 27

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

Question 28

ROUND/TRUNCATE

Answer 28

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

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

Question 29

MOD

Answer 29

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

Question 30

Subquery

Answer 30

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

Question 31

AVG

Answer 31

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;

Question 32

COUNT

Answer 32

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

Question 33

MAX/MIN

Answer 33

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

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

Question 34

SUM

Answer 34

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

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

Question 35

GROUP BY

Answer 35

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

Question 36

HAVING

Answer 36

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

Question 37

ORDER BY

Answer 37

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