Homework Assignment 3

Homework Assignment 3

In this homework, you will practice ORDER By and LIMIT clauses. You also learn UNION (U) operator to combine two table expressions. MySQL does not support INTERSECT(∩) and EXCEPT (-) operators, but these operators can be implemented by using temporary tables. See examples shown in page5.

Sorting of NULL Values

NULLvalues introduce a problem with sorting, and the various SQL products handle the ordering ofNULLvalues in different ways. MySQL treatsNULLvalues as the lowest values in a column. Therefore, they are always placed at the bottom of the result if the order is descending and at the top if the order is ascending; see the following example and the accompanying result.

-- Get the different league numbers, and sort the result in descending order.

SELECT DISTINCT LEAGUENO

FROM PLAYERS

ORDER BY 1 DESC; #column number 1 == column name LEAGUENO

Limit Clause

The LMIT clause is the last clause of a select block with which a subset of the rows can be selected. No condition is specified with the LIMIT clause but an indication is given of how many of the first and last rows are selected.

-- Get the five lowest league numbers with the corresponding player

-- numbers and names from the PLAYERS table

SELECT LEAGUENO, PLAYERNO, NAME

FROM PLAYERS

ORDER BY LEAGUENO ASC

LIMIT 5;

-- Get the numbers of the top three best players. The best player is defined

-- as the person with the highest number of matches won

SELECT PLAYERNO, COUNT(*) AS NUMBER

FROM MATCHES

WHERE WON > LOST

GROUP BY PLAYERNO

ORDER BY NUMBER DESC

LIMIT 3;

-- get the average of the four lowest penalty amounts.

-- run the following two SQL statements, see why the first one has SQL syntax error

SELECT AVG(AMOUNT)

FROM (SELECT AMOUNT

FROM PENALTIES

ORDER BY AMOUNT

LIMIT 4);

-- get the average of the four lowest penalty amounts.

SELECT AVG(AMOUNT)

FROM (SELECT AMOUNT

FROM PENALTIES

ORDER BY AMOUNT

LIMIT 4) AS T;

-- get the numbers and names of the three players who

-- incurred the highest total aount of penalties

SELECT PLAYERNO, NAME

FROM PLAYERS

WHERE PLAYERNO IN

(SELECT PLAYERNO

FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL

FROM PENALTIES

GROUP BY PLAYERNO

ORDER BY TOTAL DESC

LIMIT 3) AS T);

Combining with UNION

If two table expressions are combined with theUNIONoperator, the end result consists of every row that appears in the result of one of the two table expressions or in both.UNIONis the equivalent of the operatorunionfrom set theory.

-- Get the player number and the town of each player from Inglewood and Plymouth.

SELECT PLAYERNO, TOWN

FROM PLAYERS

WHERE TOWN = 'Inglewood'

UNION

SELECT PLAYERNO, TOWN

FROM PLAYERS

WHERE TOWN = 'Plymouth'

;

SELECT PLAYERNO, TOWN

FROM PLAYERS

WHERE TOWN = 'Inglewood'

OR TOWN = 'Plymouth';

It is not always possible to replace theUNIONoperator with anORoperator. Here is an example.

SELECT BIRTH_DATE AS DATES

FROM PLAYERS

UNION

SELECT PAYMENT_DATE

FROM PENALTIES;

--Get a list of all the dates that appear in the PLAYERS and the PENALTIES table.

SELECT BIRTH_DATE AS DATES

FROM PLAYERS

UNION

SELECT PAYMENT_DATE

FROM PENALTIES;

This statement cannot be formulated withORbecause rows from different tables are combined and are not, as in the previous example, from the same table.A special property of theUNIONoperator is that all duplicate (or equal) rows are removed automatically from the end result.

Rules for Using UNION

The following rules for using theUNIONoperator must be observed:

TheSELECTclauses of all relevant table expressions must have the same number of expressions, and the expressions that will be placed under one another must have comparable data types. If this applies, the table expressions areunion compatible. Note that two data types are comparable if they are the same or if the expressions can be transformed into the same data type by an implicit case.
AnORDER BYclause can be specified only after the last table expression. The sorting is performed on the entire end result, after all intermediate results have been combined.
TheSELECTclauses should not containDISTINCTbecause MySQL automatically removes duplicate rows when usingUNION; thus, an additionalDISTINCTis superfluous but allowed.

See why the following two SQL statements have SQL errors

-- The following SELECT statements have not been not written according to these rules (work through them for yourself):

SELECT *

FROM PLAYERS

UNION

SELECT *

FROM PENALTIES;

SELECT PLAYERNO

FROM PLAYERS

WHERE TOWN = 'Stratford'

ORDER BY 1

UNION

SELECT PLAYERNO

FROM TEAMS

ORDER BY 1;

TheUNIONoperator in combination with theGROUP BYclause offers the possibility of calculating subtotals and totals.

-- For each combination of team number and player number, give the sum of all sets won and sets lost, and find for each team a subtotal and final total. CHAR( N ) causes the cast to use no more than N characters of the argument. We may need to use CAST function to make sure table expressions are UNION compatible

# with CAST

SELECT CAST(TEAMNO AS CHAR(4)) AS TEAMNO,

CAST(PLAYERNO AS CHAR(4)) AS PLAYERNO,

SUM(WON + LOST) AS TOTAL

FROM MATCHES

GROUP BY TEAMNO, PLAYERNO

UNION

SELECT CAST(TEAMNO AS CHAR(4)),

'subtotal',

SUM(WON + LOST)

FROM MATCHES

GROUP BY TEAMNO

UNION

SELECT 'total', 'total', SUM(WON + LOST)

FROM MATCHES

ORDER BY 1, 2;

#without CAST

SELECT TEAMNO, PLAYERNO,

SUM(WON + LOST) AS TOTAL

FROM MATCHES

GROUP BY TEAMNO, PLAYERNO

UNION

SELECT TEAMNO,

'subtotal',

SUM(WON + LOST)

FROM MATCHES

GROUP BY TEAMNO

UNION

SELECT 'total', 'total', SUM(WON + LOST)

FROM MATCHES

ORDER BY 1, 2;

MySQL provides an extension to the standard SQL that is used for summarizing data: with ROLLUP operator. You can use the WITH ROLLUP operator in the GROUP BY clause to add summary rows to the final result set. With ROLLUP operator you cannot use ORDER BY clause. To sort individual columns, you can code the ASC or DESC keyword after the column in the GROUP BY clause. Try the following SQL statement and fix any SQL errors.

SELECT TEAMNO, PLAYERNO,

SUM(WON + LOST) AS TOTAL

FROM MATCHES

GROUP BY TEAMNO, PLAYERNO with rollup;

ORDER BY 1;

MySQL does not support INTERSECT(∩) and EXCEPT (-) operators, but these operators can be implemented by using temporary tables. See the following examples.

select distinct playerno

from players;

select distinct playerno

from matches;

select playerno

from players

union

select playerno

from matches;

drop tables if exists playerset1, playerset2;

create temporary table playerset1 (pnumber integer);

create temporary table playerset2 (pnumber integer);

insert into playerset1(pnumber)

SELECT distinct playerno

FROM players;

select * from playerset1;

insert into playerset2(pnumber)

SELECT distinct playerno

FROM matches;

select * from playerset2;

select pnumber from playerset2

where pnumber in (select pnumber from playerset1);

select pnumber from playerset2

where pnumber not in (select pnumber from playerset1);