Example 4.1:
CREATE USER 'BOOKSQL'@'localhost' IDENTIFIED BY 'BOOKSQLPW'
Example 4.2:
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' WITH GRANT OPTION
Example 4.3:
CREATE DATABASE TENNIS
Example 4.4:
USE TENNIS
Example 4.5:
CREATE TABLE PLAYERS (PLAYERNO INTEGER NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET VARCHAR(30) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN VARCHAR(30) NOT NULL, PHONENO CHAR(13) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) ) ; CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO) ) ; CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON SMALLINT NOT NULL, LOST SMALLINT NOT NULL, PRIMARY KEY (MATCHNO) ) ; CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO) ) ; CREATE TABLE COMMITTEE_MEMBERS (PLAYERNO INTEGER NOT NULL, BEGIN_DATE DATE NOT NULL, END_DATE DATE , POSITION CHAR(20) , PRIMARY KEY (PLAYERNO, BEGIN_DATE))
Example 4.6:
INSERT INTO PLAYERS VALUES ( 2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road', '43', '3575NH', 'Stratford', '070-237893', '2411') ; INSERT INTO PLAYERS VALUES ( 6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane', '80', '1234KK', 'Stratford', '070-476537', '8467') ; INSERT INTO PLAYERS VALUES ( 7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way', '39', '9758VB', 'Stratford', '070-347689', NULL) ; INSERT INTO PLAYERS VALUES ( 8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road', '4', '6584WO', 'Inglewood', '070-458458', '2983') ; INSERT INTO PLAYERS VALUES ( 27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive', '804', '8457DK', 'Eltham', '079-234857', '2513') ; INSERT INTO PLAYERS VALUES ( 28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road', '10', '1294QK', 'Midhurst', '010-659599', NULL) ; INSERT INTO PLAYERS VALUES ( 39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square', '78', '9629CD', 'Stratford', '070-393435', NULL) ; INSERT INTO PLAYERS VALUES ( 44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street', '23', '4444LJ', 'Inglewood', '070-368753', '1124') ; INSERT INTO PLAYERS VALUES ( 57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way', '16', '4377CB', 'Stratford', '070-473458', '6409') ; INSERT INTO PLAYERS VALUES ( 83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road', '16A', '1812UP', 'Stratford', '070-353548', '1608') ; INSERT INTO PLAYERS VALUES ( 95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street', '33A', '5746OP', 'Douglas', '070-867564', NULL) ; INSERT INTO PLAYERS VALUES ( 100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane', '80', '6494SG', 'Stratford', '070-494593', '6524') ; INSERT INTO PLAYERS VALUES ( 104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street', '65', '9437AO', 'Eltham', '079-987571', '7060') ; INSERT INTO PLAYERS VALUES ( 112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road', '8', '6392LK', 'Plymouth', '010-548745', '1319') ;
INSERT INTO TEAMS VALUES (1, 6, 'first') ; INSERT INTO TEAMS VALUES (2, 27, 'second') ;
INSERT INTO MATCHES VALUES ( 1, 1, 6, 3, 1) ; INSERT INTO MATCHES VALUES ( 2, 1, 6, 2, 3) ; INSERT INTO MATCHES VALUES ( 3, 1, 6, 3, 0) ; INSERT INTO MATCHES VALUES ( 4, 1, 44, 3, 2) ; INSERT INTO MATCHES VALUES ( 5, 1, 83, 0, 3) ; INSERT INTO MATCHES VALUES ( 6, 1, 2, 1, 3) ; INSERT INTO MATCHES VALUES ( 7, 1, 57, 3, 0) ; INSERT INTO MATCHES VALUES ( 8, 1, 8, 0, 3) ; INSERT INTO MATCHES VALUES ( 9, 2, 27, 3, 2) ; INSERT INTO MATCHES VALUES (10, 2, 104, 3, 2) ; INSERT INTO MATCHES VALUES (11, 2, 112, 2, 3) ; INSERT INTO MATCHES VALUES (12, 2, 112, 1, 3) ; INSERT INTO MATCHES VALUES (13, 2, 8, 0, 3) ;
INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100) ; INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75) ; INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100) ; INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50) ; INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25) ; INSERT INTO PENALTIES VALUES (6, 8, '1980-12-08', 25) ; INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30) ; INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75) ;
INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1990-1-1', '1990-12-31', 'Secretary') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1991-1-1', '1992-12-31', 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1992-1-1', '1993-12-31', 'Treasurer') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1993-1-1', NULL, 'Chairman') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1990-1-1', '1992-12-31', 'Chairman') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1994-1-1', NULL, 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-1-1', '1992-12-31', 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-1-1', NULL, 'Secretary') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1990-1-1', '1990-12-31', 'Treasurer') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1991-1-1', '1991-12-31', 'Secretary') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1993-1-1', '1993-12-31', 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1994-1-1', NULL, 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-1-1', '1992-12-31', 'Secretary') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-1-1', '1990-12-31', 'Member') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-1-1', '1991-12-31', 'Treasurer') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-1-1', '1993-12-31', 'Treasurer') ; INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-1-1', NULL, 'Treasurer') ;
Example 4.7:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' ORDER BY NAME
Example 4.8:
SELECT PLAYERNO FROM PLAYERS WHERE JOINED > 1980 AND TOWN = 'Stratford' ORDER BY PLAYERNO
Example 4.9:
SELECT * FROM PENALTIES
Example 4.10:
SELECT 33 * 121
Example 4.11:
UPDATE PENALTIES SET AMOUNT = 200 WHERE PLAYERNO = 44 ; SELECT PLAYERNO, AMOUNT FROM PENALTIES WHERE PLAYERNO = 44
Example 4.12:
DELETE FROM PENALTIES WHERE AMOUNT > 100
Example 4.13:
CREATE INDEX PENALTIES_AMOUNT ON PENALTIES (AMOUNT)
Example 4.14:
CREATE VIEW NUMBER_SETS (MATCHNO, DIFFERENCE) AS SELECT MATCHNO, ABS(WON - LOST) FROM MATCHES ; SELECT * FROM NUMBER_SETS
Example 4.15:
GRANT SELECT ON PLAYERS TO DIANE ; GRANT SELECT, UPDATE ON PLAYERS TO PAUL ; GRANT SELECT, UPDATE ON TEAMS TO PAUL ; SELECT * FROM TEAMS
Example 4.16:
DROP TABLE MATCHES
Example 4.17:
DROP VIEW NUMBER_SETS
Example 4.18:
DROP INDEX PENALTIES_AMOUNT
Example 4.19:
DROP DATABASE TENNIS
Example 4.20:
SELECT @@VERSION SELECT @@DATADIR // @@返回系统变量的值，DATADIR返回数据文件的存储路径
Example 4.21:
SET SQL_MODE = 'ANSI'
Example 4.22:
CREATE OR REPLACE VIEW USERS (USER_NAME) AS SELECT DISTINCT UPPER(CONCAT('''',USER,'''@''',HOST,'''')) FROM MYSQL.USER ; CREATE OR REPLACE VIEW TABLES (TABLE_CREATOR, TABLE_NAME, CREATE_TIMESTAMP, COMMENT) AS SELECT UPPER(TABLE_SCHEMA), UPPER(TABLE_NAME), CREATE_TIME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE IN ('BASE TABLE','TEMPORARY') ; CREATE OR REPLACE VIEW COLUMNS (TABLE_CREATOR, TABLE_NAME, COLUMN_NAME, COLUMN_NO, DATA_TYPE, CHAR_LENGTH, `PRECISION`, SCALE, NULLABLE, COMMENT) AS SELECT UPPER(TABLE_SCHEMA), UPPER(TABLE_NAME), UPPER(COLUMN_NAME), ORDINAL_POSITION, UPPER(DATA_TYPE), CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS ; CREATE OR REPLACE VIEW VIEWS (VIEW_CREATOR, VIEW_NAME, CREATE_TIMESTAMP, WITHCHECKOPT, IS_UPDATABLE, VIEWFORMULA, COMMENT) AS SELECT UPPER(V.TABLE_SCHEMA), UPPER(V.TABLE_NAME), T.CREATE_TIME, CASE WHEN V.CHECK_OPTION = 'None' THEN 'NO' WHEN V.CHECK_OPTION = 'Cascaded' THEN 'CASCADED' WHEN V.CHECK_OPTION = 'Local' THEN 'LOCAL' ELSE 'Yes' END, V.IS_UPDATABLE, V.VIEW_DEFINITION, T.TABLE_COMMENT FROM INFORMATION_SCHEMA.VIEWS AS V, INFORMATION_SCHEMA.TABLES AS T WHERE V.TABLE_NAME = T.TABLE_NAME AND V.TABLE_SCHEMA = T.TABLE_SCHEMA ; CREATE OR REPLACE VIEW INDEXES (INDEX_CREATOR, INDEX_NAME, CREATE_TIMESTAMP, TABLE_CREATOR, TABLE_NAME, UNIQUE_ID, INDEX_TYPE) AS SELECT DISTINCT UPPER(I.INDEX_SCHEMA), UPPER(I.INDEX_NAME), T.CREATE_TIME, UPPER(I.TABLE_SCHEMA), UPPER(I.TABLE_NAME), CASE WHEN I.NON_UNIQUE = 0 THEN 'YES' ELSE 'NO' END, I.INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS AS I, INFORMATION_SCHEMA.TABLES AS T WHERE I.TABLE_NAME = T.TABLE_NAME AND I.TABLE_SCHEMA = T.TABLE_SCHEMA ; CREATE OR REPLACE VIEW COLUMNS_IN_INDEX (INDEX_CREATOR, INDEX_NAME, TABLE_CREATOR, TABLE_NAME, COLUMN_NAME, COLUMN_SEQ, ORDERING) AS SELECT UPPER(INDEX_SCHEMA), UPPER(INDEX_NAME), UPPER(TABLE_SCHEMA), UPPER(TABLE_NAME), UPPER(COLUMN_NAME), SEQ_IN_INDEX, CASE WHEN COLLATION = 'A' THEN 'ASCENDING' WHEN COLLATION = 'D' THEN 'DESCENDING' ELSE 'OTHER' END FROM INFORMATION_SCHEMA.STATISTICS ; CREATE OR REPLACE VIEW USER_AUTHS (GRANTOR, GRANTEE, PRIVILEGE, WITHGRANTOPT) AS SELECT 'UNKNOWN', UPPER(GRANTEE), PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES ; CREATE OR REPLACE VIEW DATABASE_AUTHS (GRANTOR, GRANTEE, DATABASENAME, PRIVILEGE, WITHGRANTOPT) AS SELECT 'UNKNOWN', UPPER(GRANTEE), UPPER(TABLE_SCHEMA), PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES ; CREATE OR REPLACE VIEW TABLE_AUTHS (GRANTOR, GRANTEE, TABLE_CREATOR, TABLE_NAME, PRIVILEGE, WITHGRANTOPT) AS SELECT 'UNKNOWN', UPPER(GRANTEE), UPPER(TABLE_SCHEMA), UPPER(TABLE_NAME), PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES ; CREATE OR REPLACE VIEW COLUMN_AUTHS (GRANTOR, GRANTEE, TABLE_CREATOR, TABLE_NAME, COLUMN_NAME, PRIVILEGE, WITHGRANTOPT) AS SELECT 'UNKNOWN', UPPER(GRANTEE), UPPER(TABLE_SCHEMA), UPPER(TABLE_NAME), UPPER(COLUMN_NAME), PRIVILEGE_TYPE, IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Example 4.23:
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_NO FROM COLUMNS WHERE TABLE_NAME = 'PLAYERS' AND TABLE_CREATOR = 'TENNIS' ORDER BY COLUMN_NO
Example 4.24:
SELECT INDEX_NAME FROM INDEXES WHERE TABLE_NAME = 'PENALTIES' AND TABLE_CREATOR = 'TENNIS'
Example 5.1:
SELECT TRUE, FALSE
Example 5.2:
SELECT MATCHNO, WON - LOST FROM MATCHES WHERE WON = LOST + 2
Example 5.3:
SELECT TEAMNO, DIVISION FROM TEAMS
Example 5.4:
SELECT TEAMNO AS TEAM_NUMBER, DIVISION AS DIVISION_OF_TEAM FROM TEAMS
Example 5.5:
SELECT PAYMENTNO, AMOUNT * 100 AS CENTS FROM PENALTIES
Example 5.6:
SELECT MATCHNO AS PRIMKEY, 80 AS EIGHTY, WON - LOST AS DIFFERENCE, TIME('23:59:59') AS ALMOST_MIDNIGHT, 'TEXT' AS TEXT FROM MATCHES WHERE MATCHNO <= 4
Example 5.7:
SELECT PAYMENTNO, AMOUNT * 100 AS CENTS FROM PENALTIES ORDER BY CENTS
Example 5.8:
SET @PLAYERNO = 7
Example 5.9:
SELECT NAME, TOWN, POSTCODE FROM PLAYERS WHERE PLAYERNO < @PLAYERNO
Example 5.10:
SELECT @PLAYERNO
Example 5.11:
SELECT * FROM USER_AUTHS WHERE GRANTEE = CURRENT_USER
Example 5.12:
SELECT * FROM PENALTIES WHERE PAYMENT_DATE = CURRENT_DATE
Example 5.13:
SELECT PLAYERNO, CASE SEX WHEN 'F' THEN 'Female' ELSE 'Male' END, NAME AS SEX FROM PLAYERS WHERE JOINED > 1980
Example 5.14:
SELECT PLAYERNO, TOWN, BIRTH_DATE, CASE TOWN WHEN 'Stratford' THEN 0 WHEN 'Plymouth' THEN 1 WHEN 'Inglewood' THEN 2 ELSE 3 END AS P, CASE TOWN WHEN 'Stratford' THEN CASE BIRTH_DATE WHEN '1948-09-01' THEN 'Old Stratforder' ELSE 'Young Stratforder' END WHEN 'Inglewood' THEN CASE BIRTH_DATE WHEN '1962-07-08' THEN 'Old Inglewooder' ELSE 'Young Inglewooder' END ELSE 'Rest' END AS TYPE FROM PLAYERS
Example 5.15:
SELECT PLAYERNO, JOINED, CASE WHEN JOINED < 1980 THEN 'Seniors' WHEN JOINED < 1983 THEN 'Juniors' ELSE 'Children' END AS AGE_GROUP FROM PLAYERS ORDER BY JOINED
Example 5.16:
SELECT PLAYERNO, JOINED, TOWN, CASE WHEN JOINED >= 1980 AND JOINED <= 1982 THEN 'Seniors' WHEN TOWN = 'Eltham' THEN 'Elthammers' WHEN PLAYERNO < 10 THEN 'First members' ELSE 'Rest' END FROM PLAYERS
Example 5.17:
SELECT (PLAYERNO), (((NAME))) FROM PLAYERS
Example 5.18:
SELECT PAYMENTNO, YEAR(PAYMENT_DATE) FROM PENALTIES WHERE YEAR(PAYMENT_DATE) > 1980
Example 5.19:
SELECT PLAYERNO, CONCAT(CONCAT(LEFT(INITIALS, 1), '. '), NAME) AS FULL_NAME FROM PLAYERS WHERE LEFT(NAME, 1) = 'B'
Example 5.20:
SELECT INITIALS, NAME, COALESCE(LEAGUENO, 1) FROM PLAYERS WHERE Town = 'Stratford'
Example 5.21:
SELECT PLAYERNO, DAYNAME(BIRTH_DATE), MONTHNAME(BIRTH_DATE), DAYOFYEAR(BIRTH_DATE) FROM PLAYERS WHERE PLAYERNO < 10
Example 5.22:
SELECT PLAYERNO, BIRTH_DATE, ADDDATE(BIRTH_DATE, 7) FROM PLAYERS WHERE DAYNAME(BIRTH_DATE) = 'Saturday'
Example 5.23:
SELECT PLAYERNO, BEGIN_DATE, END_DATE, DATEDIFF(END_DATE, BEGIN_DATE) FROM COMMITTEE_MEMBERS WHERE DATEDIFF(END_DATE, BEGIN_DATE) > 500 OR (END_DATE IS NULL AND DATEDIFF(CURRENT_DATE, BEGIN_DATE) > 500) ORDER BY PLAYERNO ; SELECT PLAYERNO, BEGIN_DATE, END_DATE, DATEDIFF(COALESCE(END_DATE, CURRENT_DATE), BEGIN_DATE) FROM COMMITTEE_MEMBERS WHERE DATEDIFF(COALESCE(END_DATE, CURRENT_DATE), BEGIN_DATE) > 500 ORDER BY 1
Example 5.24:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT > 50
Example 5.25:
SELECT CONCAT(RTRIM(NAME), CAST(BIRTH_DATE AS CHAR(10))) FROM PLAYERS WHERE TOWN = 'Inglewood'
Example 5.26:
UPDATE PLAYERS SET LEAGUENO = NULL WHERE PLAYERNO = 2
Example 5.27:
SELECT TEAMNO, CAST(NULL AS CHAR) FROM TEAMS
Example 5.28:
SELECT MATCHNO, WON, LOST FROM MATCHES WHERE WON >= LOST * 2
Example 5.29:
SELECT PLAYERNO, TOWN || ' ' || STREET || ' ' || HOUSENO FROM PLAYERS WHERE TOWN = 'Stratford'
Example 5.30:
SELECT PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE + INTERVAL 7 DAY FROM PENALTIES WHERE PAYMENTNO > 5
Example 5.31:
SELECT PAYMENTNO, PAYMENT_DATE FROM PENALTIES WHERE PAYMENT_DATE >= '1982-12-25' AND PAYMENT_DATE <= '1982-12-25' + INTERVAL 6 DAY
Example 5.34:
CREATE TABLE MATCHES_SPECIAL (MATCHNO INTEGER NOT NULL, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON SMALLINT NOT NULL, LOST SMALLINT NOT NULL, START_DATE DATE NOT NULL, START_TIME TIME NOT NULL, END_TIME TIME NOT NULL, PRIMARY KEY (MATCHNO)) ; INSERT INTO MATCHES_SPECIAL VALUES (1, 1, 6, 3, 1, '2004-10-25', '14:10:12', '16:50:09') ; INSERT INTO MATCHES_SPECIAL VALUES (2, 1, 44, 3, 2, '2004-10-25', '17:00:00', '17:55:48')
Example 5.35:
SELECT MATCHNO, START_TIME, ADDTIME(START_TIME, '08:00:00') FROM MATCHES_SPECIAL
Example 5.36:
SELECT MATCHNO , END_TIME FROM MATCHES_SPECIAL WHERE ADDTIME(END_TIME, '06:30:00') < '24:00:00'
Example 5.37:
CREATE TABLE TSTAMP (COL1 TIMESTAMP) ; SET @TIME = TIMESTAMP('1980-12-08 23:59:59.59') ; INSERT INTO TSTAMP VALUES (@TIME + INTERVAL 3 MICROSECOND) ; SELECT COL1, COL1 + INTERVAL 3 MICROSECOND FROM TSTAMP
Example 5.38: A
INSERT INTO COMMITTEE_MEMBERS VALUES (7 + 15, CURRENT_DATE, CURRENT_DATE + INTERVAL 17 DAY, 'Member')
Example 5.39:
SELECT PLAYERNO FROM PLAYERS WHERE (TOWN, STREET) = ('Stratford', 'Haseltine Lane')
Example 5.40:
UPDATE PLAYERS SET (TOWN, STREET) = ('Stratford', 'Haseltine Lane') WHERE PLAYERNO = 27
Example 5.41:
INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08', 100), (2, 44, '1981-05-05', 75), (3, 27, '1983-09-10', 100), (4, 104, '1984-12-08', 50), (5, 44, '1980-12-08', 25), (6, 8, '1980-12-08', 25), (7, 44, '1982-12-30', 30), (8, 27, '1984-11-12', 75)
Answer 5.4:
SELECT MATCHNO, WON - LOST AS DIFFERENCE FROM MATCHES
Answer 5.6:
SELECT PLAYERS.PLAYERNO, PLAYERS.NAME, PLAYERS.INITIALS FROM PLAYERS WHERE PLAYERS.PLAYERNO > 6 ORDER BY PLAYERS.NAME
Answer 5.9:
SELECT PLAYERNO FROM COMMITTEE_MEMBERS WHERE BEGIN_DATE = CURRENT_DATE
Answer 5.10:
SELECT TEAMNO, CASE DIVISION WHEN 'first' then 'first division' WHEN 'second' THEN 'second division' ELSE 'unknown' END AS DIVISION FROM TEAMS
Answer 5.11:
SELECT PAYMENTNO, AMOUNT, CASE WHEN AMOUNT >= 0 AND AMOUNT <= 40 THEN 'low' WHEN AMOUNT >= 41 AND AMOUNT <= 80 THEN 'moderate' WHEN AMOUNT >= 81 THEN 'high' ELSE 'incorrect' END AS CATEGORY FROM PENALTIES
Answer 5.12:
SELECT PAYMENTNO, AMOUNT FROM PENALTIES WHERE CASE WHEN AMOUNT >= 0 AND AMOUNT <= 40 THEN 'low' WHEN AMOUNT > 40 AND AMOUNT <= 80 THEN 'moderate' WHEN AMOUNT > 80 THEN 'high' ELSE 'incorrect' END = 'low'
Answer 5.14:
SELECT PAYMENTNO FROM PENALTIES WHERE DAYNAME(PAYMENT_DATE) = 'Monday'
Answer 5.15:
SELECT PAYMENTNO FROM PENALTIES WHERE YEAR(PAYMENT_DATE) = 1984
Answer 5.22:
SELECT PLAYERNO, SUBSTR(INITIALS,1,1) || '. ' || NAME FROM PLAYERS
Answer 5.23:
SELECT TEAMNO, RTRIM(DIVISION) || ' division' FROM TEAMS
Answer 5.25:
SELECT PLAYERNO, BEGIN_DATE, BEGIN_DATE + INTERVAL 2 MONTH + INTERVAL 3 DAY FROM COMMITTEE_MEMBERS
Answer 5.29:
SELECT PAYMENTNO, PAYMENT_DATE, PAYMENT_DATE + INTERVAL 3 HOUR + INTERVAL 50 SECOND + INTERVAL 99 MICROSECOND FROM PENALTIES
Answer 5.30:
SELECT PAYMENTNO FROM PENALTIES WHERE (AMOUNT, PLAYERNO, PAYMENT_DATE) = (25, 44, '1980-12-08')
Answer 5.31:
SELECT PLAYERNO FROM PLAYERS WHERE (NAME, INITIALS) = (TOWN, STREET)
Example 6.1:
SELECT PLAYERNO FROM PENALTIES WHERE AMOUNT > 25 GROUP BY PLAYERNO HAVING COUNT(*) > 1 ORDER BY PLAYERNO
Example 6.2:
SELECT PLAYERNO, LEAGUENO FROM PLAYERS WHERE TOWN = 'Stratford' ORDER BY LEAGUENO
Example 6.3:
SELECT 89 * 73
Example 6.4:
(SELECT * FROM TEAMS)
Example 6.5:
SELECT PLAYERNO FROM TEAMS UNION SELECT PLAYERNO FROM PENALTIES
Example 6.6:
SELECT PLAYERNO FROM (SELECT PLAYERNO, SEX FROM PLAYERS WHERE PLAYERNO < 10) AS PLAYERS10 WHERE SEX = 'M'
Example 6.7:
SELECT PLAYERNO FROM (SELECT PLAYERNO, SEX FROM (SELECT PLAYERNO, SEX, JOINED FROM (SELECT PLAYERNO, SEX, JOINED FROM PLAYERS WHERE PLAYERNO > 10) AS GREATER10 WHERE PLAYERNO < 100) AS LESS100 WHERE JOINED > 1980) AS JOINED1980 WHERE SEX = 'M'
Example 6.8:
SELECT PLAYERNO, JOINED - (SELECT JOINED FROM PLAYERS WHERE PLAYERNO = 100) FROM PLAYERS WHERE PLAYERNO < 60
Example 6.9:
SELECT PLAYERNO FROM PLAYERS WHERE YEAR(BIRTH_DATE) = (SELECT YEAR(BIRTH_DATE) FROM PLAYERS WHERE PLAYERNO = 27)
Example 6.10:
SELECT (SELECT BIRTH_DATE FROM PLAYERS WHERE PLAYERNO = 27), (SELECT BIRTH_DATE FROM PLAYERS WHERE PLAYERNO = 44), (SELECT BIRTH_DATE FROM PLAYERS WHERE PLAYERNO = 100)
Example 6.11:
SELECT PLAYERNO FROM PLAYERS WHERE (SEX, TOWN) = (SELECT SEX, TOWN FROM PLAYERS WHERE PLAYERNO = 100)
Answer 6.8:
SELECT PLAYERNO, BEGIN_DATE FROM COMMITTEE_MEMBERS UNION SELECT PLAYERNO, END_DATE FROM COMMITTEE_MEMBERS ORDER BY PLAYERNO
Answer 6.9:
SELECT PLAYERNO, BEGIN_DATE, 'Begin date' FROM COMMITTEE_MEMBERS UNION SELECT PLAYERNO, END_DATE, 'End date' FROM COMMITTEE_MEMBERS ORDER BY PLAYERNO
Answer 6.10:
SELECT PLAYERNO FROM (SELECT PLAYERNO FROM (SELECT PLAYERNO, END_DATE FROM (SELECT PLAYERNO, BEGIN_DATE, END_DATE FROM COMMITTEE_MEMBERS WHERE POSITION = 'Secretary') AS SECRETARIES WHERE BEGIN_DATE >= '1990-01-01') AS AFTER1989 WHERE END_DATE <= '1994-12-31') AS BEFORE1995
Answer 6.11:
SELECT TEAMNO FROM TEAMS WHERE PLAYERNO = (SELECT PLAYERNO FROM PLAYERS WHERE NAME = 'Parmenter' AND INITIALS = 'R')
Answer 6.12:
SELECT TEAMNO FROM TEAMS WHERE PLAYERNO = (SELECT PLAYERNO FROM PLAYERS WHERE NAME = (SELECT NAME FROM PLAYERS WHERE PLAYERNO = 6) AND PLAYERNO <> 6) ; SELECT NAME FROM PLAYERS WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO = (SELECT TEAMNO FROM MATCHES WHERE MATCHNO = 6))
Answer 6.13:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT > (SELECT AMOUNT FROM PENALTIES WHERE PAYMENTNO = 4)
Answer 6.14:
SELECT PLAYERNO FROM PLAYERS WHERE DAYNAME(BIRTH_DATE) = (SELECT DAYNAME(BIRTH_DATE) FROM PLAYERS WHERE PLAYERNO = 2)
Answer 6.15:
SELECT PLAYERNO FROM COMMITTEE_MEMBERS WHERE (BEGIN_DATE, END_DATE) = (SELECT BEGIN_DATE, END_DATE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 8 AND POSITION = 'Treasurer') AND PLAYERNO <> 8
Answer 6.16:
SELECT (SELECT DIVISION FROM TEAMS WHERE TEAMNO = 1), (SELECT DIVISION FROM TEAMS WHERE TEAMNO = 2)
Answer 6.17:
SELECT (SELECT AMOUNT FROM PENALTIES WHERE PAYMENTNO = 1) + (SELECT AMOUNT FROM PENALTIES WHERE PAYMENTNO = 2) + (SELECT AMOUNT FROM PENALTIES WHERE PAYMENTNO = 1)
Example 7.1:
CREATE DATABASE EXTRA ; USE EXTRA ; CREATE TABLE CITIES (CITYNO INTEGER NOT NULL PRIMARY KEY, CITYNAME CHAR(20) NOT NULL) ; INSERT INTO CITIES VALUES (1, 'Stratford') ; INSERT INTO CITIES VALUES (2, 'Inglewood')
Example 7.2:
SELECT * FROM EXTRA.CITIES
Example 7.3:
SELECT * FROM TENNIS.TEAMS
Example 7.4:
SELECT * FROM BOB.PENALTIES ; SELECT * FROM PENALTIES
Example 7.5:
SELECT TEAMNO FROM TEAMS ; SELECT TEAMS.TEAMNO FROM TEAMS ; SELECT TENNIS.TEAMS.TEAMNO FROM TENNIS.TEAMS
Example 7.6:
SELECT TEAMNO, NAME FROM TEAMS, PLAYERS WHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Example 7.7:
SELECT PAYMENTNO, PENALTIES.PLAYERNO, AMOUNT, NAME, INITIALS FROM PENALTIES, PLAYERS WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO ; SELECT PLAYERS.PLAYERNO FROM PLAYERS, TEAMS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO ; SELECT PLAYERS.PLAYERNO FROM TEAMS, PLAYERS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO
Example 7.8:
SELECT PAYMENTNO, PEN.PLAYERNO, AMOUNT, NAME, INITIALS FROM PENALTIES AS PEN, PLAYERS AS P WHERE PEN.PLAYERNO = P.PLAYERNO ; SELECT PAYMENTNO, PEN.PLAYERNO, AMOUNT, NAME, INITIALS FROM PENALTIES PEN, PLAYERS P WHERE PEN.PLAYERNO = P.PLAYERNO
Example 7.9:
SELECT T.PLAYERNO FROM TEAMS AS T, PENALTIES AS PEN WHERE T.PLAYERNO = PEN.PLAYERNO
Example 7.10:
SELECT DISTINCT T.PLAYERNO FROM TEAMS AS T, PENALTIES AS PEN WHERE T.PLAYERNO = PEN.PLAYERNO
Example 7.11:
SELECT DISTINCT P.NAME, P.INITIALS FROM PLAYERS AS P, MATCHES AS M WHERE P.PLAYERNO = M.PLAYERNO
Example 7.12:
SELECT M.MATCHNO, M.PLAYERNO, M.TEAMNO, P.NAME, T.DIVISION FROM MATCHES AS M, PLAYERS AS P, TEAMS AS T WHERE M.PLAYERNO = P.PLAYERNO AND M.TEAMNO = T.TEAMNO
Example 7.13:
SELECT PEN.PAYMENTNO, PEN.PLAYERNO, PEN.PAYMENT_DATE FROM PENALTIES AS PEN, PLAYERS AS P WHERE PEN.PLAYERNO = P.PLAYERNO AND YEAR(PEN.PAYMENT_DATE) = P.JOINED
Example 7.14:
SELECT P.PLAYERNO FROM PLAYERS AS P, PLAYERS AS PAR WHERE PAR.NAME = 'Parmenter' AND PAR.INITIALS = 'R' AND P.BIRTH_DATE < PAR.BIRTH_DATE ; SELECT P.PLAYERNO FROM PLAYERS AS P, PLAYERS WHERE PLAYERS.NAME = 'Parmenter' AND PLAYERS.INITIALS = 'R' AND P.BIRTH_DATE < PLAYERS.BIRTH_DATE
Example 7.15:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT FROM PLAYERS, PENALTIES WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO AND BIRTH_DATE > '1920-06-30'
Example 7.16:
SELECT TEAMNO, NAME FROM TEAMS, PLAYERS WHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO ; SELECT TEAMNO, NAME FROM TEAMS INNER JOIN PLAYERS ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO ; SELECT TEAMNO, NAME FROM TEAMS JOIN PLAYERS ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Example 7.17:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT FROM PLAYERS, PENALTIES WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO ORDER BY 1 ; SELECT PLAYERS.PLAYERNO, NAME, AMOUNT FROM PLAYERS LEFT OUTER JOIN PENALTIES ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO ORDER BY 1
Example 7.18:
SELECT PAYMENTNO, NAME FROM PENALTIES LEFT OUTER JOIN PLAYERS ON PENALTIES.PLAYERNO = PLAYERS.PLAYERNO ORDER BY 1
Example 7.19:
SELECT P.PLAYERNO, NAME, TEAMNO, DIVISION FROM PLAYERS AS P LEFT OUTER JOIN TEAMS AS T ON P.PLAYERNO = T.PLAYERNO ORDER BY P.PLAYERNO
Example 7.20:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT, TEAMNO FROM PLAYERS LEFT OUTER JOIN PENALTIES ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO LEFT OUTER JOIN MATCHES ON PLAYERS.PLAYERNO = MATCHES.PLAYERNO WHERE TOWN = 'Inglewood'
Example 7.21:
SELECT PLAYERS.PLAYERNO, NAME, TEAMNO FROM TEAMS RIGHT OUTER JOIN PLAYERS ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Example 7.22:
SELECT DISTINCT MATCHES.MATCHNO, MATCHES.PLAYERNO AS MATCH_PNO, COMMITTEE_MEMBERS.PLAYERNO AS COMMITTEE_PNO FROM MATCHES FULL OUTER JOIN COMMITTEE_MEMBERS ON MATCHES.PLAYERNO = COMMITTEE_MEMBERS.PLAYERNO ORDER BY 1, 2, 3
Example 7.23:
SELECT TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO FROM TEAMS LEFT OUTER JOIN PENALTIES ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO WHERE DIVISION = 'second' ; SELECT TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO FROM TEAMS LEFT OUTER JOIN PENALTIES ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO AND DIVISION = 'second'
Example 7.24:
SELECT TEAMS.PLAYERNO, TEAMS.TEAMNO, PENALTIES.PAYMENTNO FROM TEAMS FULL OUTER JOIN PENALTIES ON TEAMS.PLAYERNO = PENALTIES.PLAYERNO AND TEAMS.PLAYERNO > 1000
Example 7.25:
SELECT * FROM TEAMS UNION JOIN PENALTIES
Example 7.26:
SELECT PLAYERNO FROM (SELECT * FROM PLAYERS WHERE TOWN = 'Stratford') AS STRATFORDERS
Example 7.27:
SELECT SMALL_TEAMS.PLAYERNO FROM (SELECT PLAYERNO, DIVISION FROM TEAMS) AS SMALL_TEAMS WHERE SMALL_TEAMS.DIVISION = 'first'
Example 7.28:
SELECT MATCHNO, DIFFERENCE FROM (SELECT MATCHNO, ABS(WON ?LOST) AS DIFFERENCE FROM MATCHES) AS M WHERE DIFFERENCE > 2
Example 7.29:
SELECT * FROM (SELECT 'Stratford' AS TOWN, 4 AS NUMBER UNION SELECT 'Plymouth', 6 UNION SELECT 'Inglewood', 1 UNION SELECT 'Douglas', 2) AS TOWNS ORDER BY TOWN
Example 7.30:
SELECT PLAYERNO, NAME, PLAYERS.TOWN, NUMBER * 1000 FROM PLAYERS, (SELECT 'Stratford' AS TOWN, 4 AS NUMBER UNION SELECT 'Plymouth', 6 UNION SELECT 'Inglewood', 1 UNION SELECT 'Douglas', 2) AS TOWNS WHERE PLAYERS.TOWN = TOWNS.TOWN ORDER BY 1 ; SELECT PLAYERNO, NAME, PLAYERS.TOWN, NUMBER FROM PLAYERS LEFT OUTER JOIN (SELECT 'Stratford' AS TOWN, 4 AS NUMBER UNION SELECT 'Plymouth', 6 UNION SELECT 'Inglewood', 1 UNION SELECT 'Douglas', 2) AS TOWNS ON PLAYERS.TOWN = TOWNS.TOWN ORDER BY 1
Example 7.31:
SELECT PLAYERNO FROM PLAYERS LEFT OUTER JOIN (SELECT 'Stratford' AS TOWN, 4 AS NUMBER UNION SELECT 'Plymouth', 6 UNION SELECT 'Inglewood', 1 UNION SELECT 'Douglas', 2) AS TOWNS ON PLAYERS.TOWN = TOWNS.TOWN WHERE TOWNS.NUMBER > 2
Example 7.32:
SELECT * FROM (SELECT 'John' AS FIRST_NAME UNION SELECT 'Mark' UNION SELECT 'Arnold') AS FIRST_NAMES, (SELECT 'Berg' AS LAST_NAME UNION SELECT 'Johnson' UNION SELECT 'Williams') AS LAST_NAMES
Example 7.33:
SELECT NUMBER, POWER(NUMBER,3) FROM (SELECT 10 NUMBER UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19) AS NUMBERS WHERE POWER(NUMBER,3) <= 4000
Example 7.34:
SELECT NUMBER FROM (SELECT CAST(CONCAT(DIGIT1.DIGIT, CONCAT(DIGIT2.DIGIT, DIGIT3.DIGIT)) AS UNSIGNED INTEGER) AS NUMBER FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT1, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT2, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT3) AS NUMBERS ORDER BY 1
Example 7.35:
SELECT NUMBER AS SQUARE, ROUND(SQRT(NUMBER)) AS BASIS FROM (SELECT CAST(CONCAT(DIGIT1.DIGIT, CONCAT(DIGIT2.DIGIT, DIGIT3.DIGIT)) AS UNSIGNED INTEGER) AS NUMBER FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT1, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT2, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT3) AS NUMBERS WHERE SQRT(NUMBER) = ROUND(SQRT(NUMBER)) ORDER BY 1
Answer 7.3:
SELECT PAYMENTNO, AMOUNT, PLAYERS.PLAYERNO, NAME FROM PENALTIES, PLAYERS WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO
Answer 7.4:
SELECT PAYMENTNO, NAME FROM PENALTIES, PLAYERS, TEAMS WHERE PENALTIES.PLAYERNO = TEAMS.PLAYERNO AND TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Answer 7.5:
SELECT T.TEAMNO, P.NAME FROM TEAMS AS T, PLAYERS AS P WHERE T.PLAYERNO = P.PLAYERNO
Answer 7.6:
SELECT M.MATCHNO, P.NAME, T.DIVISION FROM MATCHES AS M, PLAYERS AS P, TEAMS AS T WHERE M.PLAYERNO = P.PLAYERNO AND M.TEAMNO = T.TEAMNO
Answer 7.7:
SELECT P.PLAYERNO, P.NAME FROM PLAYERS AS P, COMMITTEE_MEMBERS AS C WHERE P.PLAYERNO = C.PLAYERNO AND YEAR(C.BEGIN_DATE) >= 1990 AND YEAR(C.END_DATE) <= 1990
Answer 7.8:
SELECT DISTINCT CM.PLAYERNO FROM COMMITTEE_MEMBERS AS CM, PENALTIES AS PEN WHERE CM.PLAYERNO = PEN.PLAYERNO AND CM.BEGIN_DATE = PEN.PAYMENT_DATE
Answer 7.9:
SELECT P.PLAYERNO, P.NAME FROM PLAYERS AS P, PLAYERS AS P27 WHERE P.TOWN = P27.TOWN AND P27.PLAYERNO = 27 AND P.PLAYERNO <> 27
Answer 7.10:
SELECT DISTINCT P.PLAYERNO AS PLAYER_PLAYERNO, P.NAME AS PLAYER_NAME, CAP.PLAYERNO AS CAPTAIN_PLAYERNO, CAP.NAME AS CAPTAIN_NAME FROM PLAYERS AS P, PLAYERS AS CAP, MATCHES AS M, TEAMS AS T WHERE M.PLAYERNO = P.PLAYERNO AND T.TEAMNO = M.TEAMNO AND M.PLAYERNO <> T.PLAYERNO AND CAP.PLAYERNO = T.PLAYERNO
Answer 7.11:
SELECT PEN1.PAYMENTNO, PEN1.PLAYERNO FROM PENALTIES AS PEN1, PENALTIES AS PEN2 WHERE PEN1.AMOUNT = PEN2.AMOUNT AND PEN2.PLAYERNO = 44 AND PEN1.PLAYERNO <> 44
Answer 7.12:
SELECT T.TEAMNO, P.NAME FROM TEAMS AS T INNER JOIN PLAYERS AS P ON T.PLAYERNO = P.PLAYERNO
Answer 7.13:
SELECT P.PLAYERNO, P.NAME FROM PLAYERS AS P INNER JOIN PLAYERS AS P27 ON P.TOWN = P27.TOWN AND P27.PLAYERNO = 27 AND P.PLAYERNO <> 27
Answer 7.14:
SELECT M.MATCHNO, P.NAME, T.DIVISION FROM (MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO) INNER JOIN TEAMS AS T ON M.TEAMNO = T.TEAMNO
Answer 7.15:
SELECT PLAYERS.PLAYERNO, PENALTIES.AMOUNT FROM PLAYERS LEFT OUTER JOIN PENALTIES ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
Answer 7.16:
SELECT P.PLAYERNO, M.TEAMNO FROM PLAYERS AS P LEFT OUTER JOIN MATCHES AS M ON P.PLAYERNO = M.PLAYERNO
Answer 7.17:
SELECT P.PLAYERNO, M.TEAMNO FROM (PLAYERS AS P LEFT OUTER JOIN MATCHES AS M ON P.PLAYERNO = M.PLAYERNO) LEFT OUTER JOIN PENALTIES AS PEN ON P.PLAYERNO = PEN.PLAYERNO
Answer 7.21:
SELECT PLAYERNO, DIFFERENCE FROM (SELECT PLAYERNO, JOINED - YEAR(BIRTH_DATE) AS DIFFERENCE FROM PLAYERS) AS DIFFERENCES WHERE DIFFERENCE > 20
Answer 7.22:
SELECT LETTER1 || LETTER2 || LETTER3 FROM (SELECT 'a' AS LETTER1 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd') AS LETTERS1, (SELECT 'a' AS LETTER2 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd') AS LETTERS2, (SELECT 'a' AS LETTER3 UNION SELECT 'b' UNION SELECT 'c' UNION SELECT 'd') AS LETTERS3
Answer 7.23:
SELECT ROUND(RAND() * 1000) FROM (SELECT 0 NUMBER UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS NUMBERS
Example 8.1:
SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford'
Example 8.2:
SELECT PLAYERNO, BIRTH_DATE, JOINED FROM PLAYERS WHERE YEAR(BIRTH_DATE) + 17 = JOINED
Example 8.3:
SELECT PLAYERNO FROM PLAYERS WHERE LEAGUENO = '7060'
Example 8.4:
SELECT PLAYERNO, LEAGUENO FROM PLAYERS WHERE LEAGUENO = LEAGUENO
Examples 8.5:
SELECT MATCHNO FROM MATCHES WHERE (WON, LOST) = (2, 3)
Example 8.6:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO = 1)
Example 8.7:
SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE BIRTH_DATE < (SELECT BIRTH_DATE FROM PLAYERS WHERE LEAGUENO = '8467') ; SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE (SELECT BIRTH_DATE FROM PLAYERS WHERE LEAGUENO = '9999') IS NULL
Example 8.8:
SELECT MATCHNO FROM MATCHES WHERE TEAMNO = (SELECT TEAMNO FROM TEAMS WHERE PLAYERNO = 27)
Example 8.9:
SELECT PLAYERNO, TOWN, SEX FROM PLAYERS WHERE (TOWN, SEX) = ((SELECT TOWN FROM PLAYERS WHERE PLAYERNO = 7), (SELECT SEX FROM PLAYERS WHERE PLAYERNO = 2))
Example 8.10:
SELECT DISTINCT PLAYERNO FROM COMMITTEE_MEMBERS WHERE (BEGIN_DATE, END_DATE) = (SELECT BEGIN_DATE, END_DATE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 6 AND POSITION = 'Secretary' AND BEGIN_DATE = '1990-01-01')
Example 8.11:
SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE (NAME, INITIALS) < (SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = 6) ORDER BY NAME, INITIALS
Example 8.12:
SELECT MATCHNO FROM MATCHES_SPECIAL WHERE (START_DATE, START_TIME) > (SELECT START_DATE, START_TIME FROM MATCHES_SPECIAL WHERE MATCHNO = 1)
Example 8.13:
SELECT MATCHNO FROM MATCHES WHERE 'Inglewood' = (SELECT TOWN FROM PLAYERS WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO)
Example 8.14:
SELECT MATCHNO, PLAYERNO, TEAMNO FROM MATCHES WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMS.PLAYERNO = MATCHES.PLAYERNO)
Example 8.15:
SELECT MATCHNO FROM MATCHES WHERE SUBSTR((SELECT DIVISION FROM TEAMS WHERE TEAMS.TEAMNO = MATCHES.TEAMNO),3,1) = SUBSTR((SELECT NAME FROM PLAYERS WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO),3,1)
Example 8.16:
SELECT PLAYERNO, NAME, SEX, BIRTH_DATE FROM PLAYERS WHERE SEX = 'M' AND BIRTH_DATE > '1970-12-31'
Example 8.17:
SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE TOWN = 'Plymouth' OR TOWN = 'Eltham'
Example 8.18:
SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE TOWN <> 'Stratford' ; SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE NOT (TOWN = 'Stratford')
Example 8.19:
SELECT PLAYERNO, TOWN, BIRTH_DATE FROM PLAYERS WHERE (TOWN = 'Stratford' OR YEAR(BIRTH_DATE) = 1963) AND NOT (TOWN = 'Stratford' AND YEAR(BIRTH_DATE) = 1963)
Example 8.20:
SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE TOWN = 'Inglewood' OR TOWN = 'Plymouth' OR TOWN = 'Midhurst' OR TOWN = 'Douglas' ; SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE TOWN IN ('Inglewood', 'Plymouth', 'Midhurst', 'Douglas')
Example 8.21:
SELECT PLAYERNO, YEAR(BIRTH_DATE) FROM PLAYERS WHERE YEAR(BIRTH_DATE) IN (1962, 1963, 1970)
Example 8.22:
SELECT MATCHNO, WON, LOST FROM MATCHES WHERE 2 IN (WON, LOST)
Example 8.23:
SELECT PLAYERNO FROM PLAYERS WHERE PLAYERNO IN (100, (SELECT PLAYERNO FROM PENALTIES WHERE PAYMENTNO = 1), (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO = 2))
Example 8.24:
SELECT MATCHNO, WON, LOST FROM MATCHES WHERE WON IN (TRUNCATE(MATCHNO / 2,0), LOST, (SELECT LOST FROM MATCHES WHERE MATCHNO = 1))
Example 8.25:
SELECT MATCHNO FROM MATCHES WHERE (SELECT SUBSTR(NAME,1,1) FROM PLAYERS WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO) IN ('B','C','E')
Example 8.26:
SELECT MATCHNO, WON, LOST FROM MATCHES WHERE (WON, LOST) IN ((3,1),(3,2))
Example 8.27:
SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE (NAME, INITIALS) IN ((SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = 6), (SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = 27))
Example 8.28:
SELECT PLAYERNO FROM MATCHES ; SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES)
Example 8.29:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO = 1)
Example 8.30:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO NOT IN (SELECT TEAMNO FROM TEAMS WHERE PLAYERNO = 6))
Example 8.31:
SELECT * FROM COMMITTEE_MEMBERS WHERE (BEGIN_DATE, END_DATE) IN (SELECT BEGIN_DATE, END_DATE FROM COMMITTEE_MEMBERS WHERE POSITION = 'Secretary')
Example 8.32:
CREATE TABLE PLAYERS_NI (NAME CHAR(10) NOT NULL, INITIALS CHAR(3) NOT NULL, TOWN VARCHAR(30) NOT NULL, PRIMARY KEY (NAME, INITIALS)) ; INSERT INTO PLAYERS_NI VALUES ('Parmenter', 'R', 'Stratford') ; INSERT INTO PLAYERS_NI VALUES ('Parmenter', 'P', 'Stratford') ; INSERT INTO PLAYERS_NI VALUES ('Miller', 'P', 'Douglas') ; CREATE TABLE PENALTIES_NI (PAYMENTNO INTEGER NOT NULL, NAME CHAR(10) NOT NULL, INITIALS CHAR(3) NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO), FOREIGN KEY (NAME, INITIALS) REFERENCES PLAYERS_NI (NAME, INITIALS)) ; INSERT INTO PENALTIES_NI VALUES (1, 'Parmenter', 'R', 100.00) ; INSERT INTO PENALTIES_NI VALUES (2, 'Miller', 'P', 200.00)
Example 8.33:
SELECT NAME, INITIALS, TOWN FROM PLAYERS_NI WHERE NAME IN (SELECT NAME FROM PENALTIES_NI) AND INITIALS IN (SELECT INITIALS FROM PENALTIES_NI) ; SELECT NAME, INITIALS, TOWN FROM PLAYERS_NI WHERE (NAME, INITIALS) IN (SELECT NAME, INITIALS FROM PENALTIES_NI) ; SELECT NAME, INITIALS, TOWN FROM PLAYERS_NI WHERE NAME IN (SELECT NAME FROM PENALTIES_NI WHERE PLAYERS_NI.INITIALS = PENALTIES_NI.INITIALS)
Example 8.34:
SELECT NAME, INITIALS, TOWN FROM PLAYERS_NI WHERE (NAME, INITIALS) NOT IN (SELECT NAME, INITIALS FROM PENALTIES_NI)
Example 8.35:
SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE >= '1962-01-01' AND BIRTH_DATE <= '1964-12-31' ; SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE BETWEEN '1962-01-01' AND '1964-12-31'
Example 8.36:
SELECT MATCHNO, WON + LOST FROM MATCHES WHERE WON + LOST BETWEEN 2 AND 4
Example 8.37:
SELECT PLAYERNO, BIRTH_DATE, NAME, INITIALS FROM PLAYERS WHERE BIRTH_DATE BETWEEN (SELECT BIRTH_DATE FROM PLAYERS WHERE NAME = 'Newcastle' AND INITIALS = 'B') AND (SELECT BIRTH_DATE FROM PLAYERS WHERE NAME = 'Miller' AND INITIALS = 'P')
Example 8.38:
SELECT NAME, PLAYERNO FROM PLAYERS WHERE NAME LIKE 'B%'
Example 8.39:
SELECT NAME, PLAYERNO FROM PLAYERS WHERE NAME LIKE '%r'
Example 8.40:
SELECT NAME, PLAYERNO FROM PLAYERS WHERE NAME LIKE '%e_'
Example 8.41:
SELECT NAME, TOWN, PLAYERNO FROM PLAYERS WHERE NAME LIKE CONCAT('%', SUBSTR(TOWN,3,1))
Example 8.42:
SELECT NAME, PLAYERNO FROM PLAYERS WHERE NAME LIKE '%#_%' ESCAPE '#'
Example 8.43:
SELECT PLAYERNO, LEAGUENO FROM PLAYERS WHERE LEAGUENO IS NOT NULL
Example 8.44:
SELECT NAME, PLAYERNO, LEAGUENO FROM PLAYERS WHERE LEAGUENO <> '8467' OR LEAGUENO IS NULL
Example 8.45:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) ; SELECT NAME, INITIALS FROM PLAYERS WHERE EXISTS (SELECT * FROM PENALTIES WHERE PLAYERNO = PLAYERS.PLAYERNO)
Example 8.46:
SELECT NAME, INITIALS FROM PLAYERS WHERE NOT EXISTS (SELECT * FROM TEAMS WHERE PLAYERNO = PLAYERS.PLAYERNO) ; SELECT NAME, INITIALS FROM PLAYERS WHERE NOT EXISTS (SELECT 'nothing' FROM TEAMS WHERE PLAYERNO = PLAYERS.PLAYERNO)
Example 8.47:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE <= ALL (SELECT BIRTH_DATE FROM PLAYERS)
Example 8.48:
SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE < ALL (SELECT BIRTH_DATE FROM PLAYERS AS P INNER JOIN MATCHES AS M ON P.PLAYERNO = M.PLAYERNO WHERE M.TEAMNO = 2)
Example 8.49:
SELECT DISTINCT TEAMNO, PLAYERNO FROM MATCHES AS M1 WHERE WON <= ALL (SELECT WON FROM MATCHES AS M2 WHERE M1.TEAMNO = M2.TEAMNO)
Example 8.50:
SELECT LEAGUENO, PLAYERNO FROM PLAYERS WHERE LEAGUENO >= ALL (SELECT LEAGUENO FROM PLAYERS) ; SELECT LEAGUENO, PLAYERNO FROM PLAYERS WHERE LEAGUENO >= ALL (SELECT LEAGUENO FROM PLAYERS WHERE LEAGUENO IS NOT NULL)
Example 8.51:
SELECT PLAYERNO, TOWN, LEAGUENO FROM PLAYERS AS P1 WHERE LEAGUENO <= ALL (SELECT P2.LEAGUENO FROM PLAYERS AS P2 WHERE P1.TOWN = P2.TOWN) ; SELECT PLAYERNO, TOWN, LEAGUENO FROM PLAYERS AS P1 WHERE LEAGUENO <= ALL (SELECT P2.LEAGUENO FROM PLAYERS AS P2 WHERE P1.TOWN = P2.TOWN AND LEAGUENO IS NOT NULL) ; SELECT PLAYERNO, TOWN, LEAGUENO FROM PLAYERS AS P1 WHERE LEAGUENO <= ALL (SELECT P2.LEAGUENO FROM PLAYERS AS P2 WHERE P1.TOWN = P2.TOWN AND LEAGUENO IS NOT NULL) AND TOWN IN (SELECT TOWN FROM PLAYERS WHERE LEAGUENO IS NOT NULL)
Example 8.52:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE > ANY (SELECT BIRTH_DATE FROM PLAYERS)
Example 8.53:
SELECT DISTINCT PLAYERNO FROM PENALTIES WHERE PLAYERNO <> 27 AND AMOUNT > ANY (SELECT AMOUNT FROM PENALTIES WHERE PLAYERNO = 27)
Example 8.54:
SELECT PLAYERNO, BIRTH_DATE, TOWN FROM PLAYERS AS P1 WHERE BIRTH_DATE > ANY (SELECT BIRTH_DATE FROM PLAYERS AS P2 WHERE P1.TOWN = P2.TOWN)
Example 8.55:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE EXISTS (SELECT * FROM PENALTIES WHERE PLAYERS.PLAYERNO = PLAYERS.PLAYERNO) ; SELECT PLAYERNO, NAME FROM PLAYERS AS P WHERE EXISTS (SELECT * FROM PENALTIES AS PEN WHERE P.PLAYERNO = PEN.PLAYERNO)
Example 8.56:
SELECT TEAMNO, DIVISION FROM TEAMS WHERE EXISTS (SELECT * FROM MATCHES WHERE PLAYERNO = 44 AND TEAMNO = TEAMS.TEAMNO)
Example 8.57:
SELECT DISTINCT PLAYERNO FROM PENALTIES AS PEN WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES WHERE PAYMENTNO <> PEN.PAYMENTNO)
Example 8.58:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE 1 <> ALL (SELECT TEAMNO FROM MATCHES WHERE PLAYERNO = PLAYERS.PLAYERNO)
Example 8.59:
SELECT TEAMNO FROM TEAMS WHERE NOT EXISTS (SELECT * FROM MATCHES WHERE PLAYERNO = 57 AND TEAMNO = TEAMS.TEAMNO)
Example 8.60:
SELECT PLAYERNO FROM PLAYERS AS P WHERE NOT EXISTS (SELECT * FROM TEAMS AS T WHERE NOT EXISTS (SELECT * FROM MATCHES AS M WHERE T.TEAMNO = M.TEAMNO AND P.PLAYERNO = M.PLAYERNO))
Example 8.61:
SELECT PLAYERNO FROM PLAYERS WHERE NOT EXISTS (SELECT * FROM MATCHES AS M1 WHERE PLAYERNO = 57 AND NOT EXISTS (SELECT * FROM MATCHES AS M2 WHERE M1.TEAMNO = M2.TEAMNO AND PLAYERS.PLAYERNO = M2.PLAYERNO))
Example 8.62:
SELECT PLAYERNO FROM PLAYERS AS P WHERE NOT EXISTS (SELECT * FROM MATCHES AS M1 WHERE PLAYERNO = 57 AND NOT EXISTS (SELECT * FROM MATCHES AS M2 WHERE M1.TEAMNO = M2.TEAMNO AND P.PLAYERNO = M2.PLAYERNO)) AND PLAYERNO NOT IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE TEAMNO NOT IN (SELECT TEAMNO FROM MATCHES WHERE PLAYERNO = 57)))
Example 8.63:
SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford'
Example 8.64:
SELECT PLAYERNO FROM PENALTIES WHERE AMOUNT = 25 ; SELECT PLAYERNO FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES WHERE AMOUNT = 25)
Answer 8.1:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT > 60 ; SELECT PAYMENTNO FROM PENALTIES WHERE 60 < AMOUNT ; SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT - 60 > 0
Answer 8.2:
SELECT TEAMNO FROM TEAMS WHERE PLAYERNO <> 27
Answer 8.4:
SELECT DISTINCT PLAYERNO FROM MATCHES WHERE WON > LOST
Answer 8.5:
SELECT DISTINCT PLAYERNO FROM MATCHES WHERE WON + LOST = 5
Answer 8.6:
ELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = (SELECT PLAYERNO FROM PENALTIES WHERE PAYMENTNO = 4)
Answer 8.7:
SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO = (SELECT TEAMNO FROM MATCHES WHERE MATCHNO = 2))
Answer 8.8:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE BIRTH_DATE = (SELECT BIRTH_DATE FROM PLAYERS WHERE NAME = 'Parmenter' AND INITIALS = 'R') AND NOT (NAME = 'Parmenter' AND INITIALS = 'R')
Answer 8.9:
SELECT MATCHNO FROM MATCHES WHERE WON = (SELECT WON FROM MATCHES WHERE MATCHNO = 6) AND MATCHNO <> 6 AND TEAMNO = 2
Answer 8.10:
SELECT MATCHNO FROM MATCHES WHERE (WON, LOST) = ((SELECT WON FROM MATCHES WHERE MATCHNO = 2), (SELECT LOST FROM MATCHES WHERE MATCHNO = 8))
Answer 8.11:
SELECT PLAYERNO, TOWN, STREET, HOUSENO FROM PLAYERS WHERE (TOWN, STREET, HOUSENO) < (SELECT TOWN, STREET, HOUSENO FROM PLAYERS WHERE PLAYERNO = 100) ORDER BY TOWN, STREET, HOUSENO
Answer 8.12:
SELECT PAYMENTNO FROM PENALTIES WHERE 1965 < (SELECT YEAR(BIRTH_DATE) FROM PLAYERS WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)
Answer 8.13:
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMS.PLAYERNO = PENALTIES.PLAYERNO)
Answer 8.14:
SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE SEX = 'F' AND TOWN <> 'Stratford' ; SELECT PLAYERNO, NAME, TOWN FROM PLAYERS WHERE SEX = 'F' AND NOT (TOWN = 'Stratford')
Answer 8.15:
SELECT PLAYERNO FROM PLAYERS WHERE JOINED >= 1970 AND JOINED <= 1980 ; SELECT PLAYERNO FROM PLAYERS WHERE NOT (JOINED < 1970 OR JOINED > 1980)
Answer 8.16:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE MOD(YEAR(BIRTH_DATE), 400) = 0 OR (MOD(YEAR(BIRTH_DATE), 4) = 0 AND NOT(MOD(YEAR(BIRTH_DATE), 100) = 0))
Answer 8.17:
SELECT MATCHNO, NAME, INITIALS, DIVISION FROM MATCHES AS M, PLAYERS AS P, TEAMS AS T WHERE M.PLAYERNO = P.PLAYERNO AND M.TEAMNO = T.TEAMNO AND YEAR(BIRTH_DATE) > 1965 AND WON > LOST
Answer 8.18:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT IN (50, 75, 100)
Answer 8.19:
SELECT PLAYERNO FROM PLAYERS WHERE TOWN NOT IN ('Stratford', 'Douglas') ; SELECT PLAYERNO FROM PLAYERS WHERE NOT (TOWN IN ('Stratford', 'Douglas')) ; SELECT PLAYERNO FROM PLAYERS WHERE TOWN <> 'Stratford' AND TOWN <> 'Douglas'
Answer 8.20:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT IN (100, PAYMENTNO * 5, (SELECT AMOUNT FROM PENALTIES WHERE PAYMENTNO = 2))
Answer 8.21:
SELECT PLAYERNO, TOWN, STREET FROM PLAYERS WHERE (TOWN, STREET) IN (('Stratford','Haseltine Lane'), ('Stratford',' Edgecombe Way'))
Answer 8.22:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES)
Answer 8.23:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES WHERE AMOUNT > 50)
Answer 8.24:
SELECT TEAMNO, PLAYERNO FROM TEAMS WHERE DIVISION = 'first' AND PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford')
Answer 8.25:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) AND PLAYERNO NOT IN (SELECT PLAYERNO FROM TEAMS WHERE DIVISION = 'first') ; SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM TEAMS WHERE DIVISION = 'first'))
Answer 8.27:
SELECT MATCHNO, PLAYERNO FROM MATCHES WHERE (WON, LOST) IN (SELECT WON, LOST FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE DIVISION = 'second'))
Answer 8.28:
SELECT PLAYERNO, NAME FROM PLAYERS AS P1 WHERE (TOWN, STREET, HOUSENO, POSTCODE) IN (SELECT TOWN, STREET, HOUSENO, POSTCODE FROM PLAYERS AS P2 WHERE P1.PLAYERNO <> P2.PLAYERNO)
Answer 8.29:
SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT BETWEEN 50 AND 100
Answer 8.30:
SELECT PAYMENTNO FROM PENALTIES WHERE NOT (AMOUNT BETWEEN 50 AND 100) ; SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT NOT BETWEEN 50 AND 100 ; SELECT PAYMENTNO FROM PENALTIES WHERE AMOUNT < 50 OR AMOUNT > 100
Answer 8.31:
SELECT PLAYERNO FROM PLAYERS WHERE JOINED BETWEEN YEAR(BIRTH_DATE + INTERVAL 17 YEAR) AND YEAR(BIRTH_DATE + INTERVAL 39 YEAR)
Answer 8.32:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '%is%'
Answer 8.33:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '______'
Answer 8.34:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '______%' ; SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '%______' ; SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '%______%' ; SELECT PLAYERNO, NAME FROM PLAYERS WHERE LENGTH(RTRIM(NAME)) > 6
Answer 8.35:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE NAME LIKE '_r%r_'
Answer 8.36:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE TOWN LIKE '_@%%@%_' ESCAPE '@'
Answer 8.37:
SELECT PLAYERNO FROM PLAYERS WHERE LEAGUENO IS NULL
Answer 8.39:
SELECT NAME, INITIALS FROM PLAYERS WHERE EXISTS (SELECT * FROM TEAMS WHERE PLAYERNO = PLAYERS.PLAYERNO)
Answer 8.40:
SELECT NAME, INITIALS FROM PLAYERS AS P WHERE NOT EXISTS (SELECT * FROM TEAMS AS T WHERE T.PLAYERNO = P.PLAYERNO AND NOT EXISTS (SELECT * FROM MATCHES AS M WHERE M.TEAMNO = T.TEAMNO AND M.PLAYERNO = 112))
Answer 8.41:
SELECT PLAYERNO FROM PLAYERS WHERE BIRTH_DATE <= ALL (SELECT BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford') AND TOWN = 'Stratford'
Answer 8.42:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO = ANY (SELECT PLAYERNO FROM PENALTIES)
Answer 8.43:
SELECT PAYMENTNO, AMOUNT, PAYMENT_DATE FROM PENALTIES AS PEN1 WHERE AMOUNT >= ALL (SELECT AMOUNT FROM PENALTIES AS PEN2 WHERE YEAR(PEN1.PAYMENT_DATE) = YEAR(PEN2.PAYMENT_DATE))
Answer 8.44:
SELECT (SELECT PLAYERNO FROM PLAYERS WHERE PLAYERNO <= ALL (SELECT PLAYERNO FROM PLAYERS)), (SELECT PLAYERNO FROM PLAYERS WHERE PLAYERNO >= ALL (SELECT PLAYERNO FROM PLAYERS))
Answer 8.46:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE DIVISION = 'first')) AND PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE WON > LOST) AND PLAYERNO NOT IN (SELECT PLAYERNO FROM PENALTIES)
Answer 8.47:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO = 1) AND PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO = 2)
Answer 8.48:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE EXISTS (SELECT * FROM PENALTIES WHERE PLAYERNO = PLAYERS.PLAYERNO)
Answer 8.49:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES AS M1 WHERE WON > LOST AND EXISTS (SELECT * FROM MATCHES AS M2 WHERE M1.PLAYERNO = M2.PLAYERNO AND WON > LOST AND M1.MATCHNO <> M2.MATCHNO)) ; SELECT PLAYERNO, NAME FROM PLAYERS WHERE 1 < (SELECT COUNT(*) FROM MATCHES WHERE WON > LOST AND PLAYERS.PLAYERNO = PLAYERNO)
Answer 8.50:
SELECT P.PLAYERNO, P.NAME FROM PLAYERS AS P, MATCHES AS M1 WHERE P.PLAYERNO = M1.PLAYERNO GROUP BY P.PLAYERNO, P.NAME HAVING SUM(WON) > (SELECT SUM(LOST) FROM MATCHES AS M2 WHERE M2.PLAYERNO = P.PLAYERNO GROUP BY M2.PLAYERNO)
Answer 8.51:
SELECT NAME, INITIALS FROM PLAYERS WHERE NOT EXISTS (SELECT * FROM PENALTIES WHERE PLAYERS.PLAYERNO = PLAYERNO AND PAYMENT_DATE BETWEEN '1980-01-01' AND '1980-12-31')
Answer 8.52:
SELECT DISTINCT PLAYERNO FROM PENALTIES AS PEN1 WHERE EXISTS (SELECT * FROM PENALTIES AS PEN2 WHERE PEN1.AMOUNT = PEN2.AMOUNT AND PEN1.PAYMENTNO <> PEN2.PAYMENTNO)
Answer 8.53:
SELECT PLAYERNO FROM PLAYERS WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM MATCHES WHERE WON = 3)
Answer 8.54:
SELECT TEAMNO, DIVISION FROM TEAMS WHERE TEAMNO NOT IN (SELECT TEAMNO FROM MATCHES WHERE PLAYERNO = 6)
Answer 8.55:
SELECT DISTINCT PLAYERNO FROM MATCHES WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM MATCHES WHERE PLAYERNO = 57))
Example 9.1:
SELECT * FROM PENALTIES ; SELECT PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT FROM PENALTIES
Example 9.2:
SELECT PENALTIES.* FROM PENALTIES INNER JOIN TEAMS ON PENALTIES.PLAYERNO = TEAMS.PLAYERNO ; SELECT PENALTIES.PAYMENTNO, PENALTIES.PLAYERNO, PENALTIES.PAYMENT_DATE, PENALTIES.AMOUNT FROM PENALTIES INNER JOIN TEAMS ON PENALTIES.PLAYERNO = TEAMS.PLAYERNO ; SELECT PEN.* FROM PENALTIES AS PEN INNER JOIN TEAMS ON PEN.PLAYERNO = TEAMS.PLAYERNO
Example 9.3:
SELECT MATCHNO, 'Tally', WON - LOST, WON * 10 FROM MATCHES
Example 9.4:
SELECT TOWN FROM PLAYERS ; SELECT DISTINCT TOWN FROM PLAYERS
Example 9.5:
SELECT STREET, TOWN FROM PLAYERS ; SELECT DISTINCT STREET, TOWN FROM PLAYERS
Example 9.6:
SELECT DISTINCT LEAGUENO FROM PLAYERS
Example 9.7:
SELECT DISTINCT * FROM (SELECT 1 AS A, 'Hello' AS B, 4 AS C UNION SELECT 1, 'Hello', NULL UNION SELECT 1, 'Hello', NULL UNION SELECT 1, NULL, NULL) AS X
Example 9.8:
SELECT COUNT(*) FROM PLAYERS
Example 9.9:
SELECT COUNT(*) FROM PLAYERS WHERE TOWN = 'Stratford'
Example 9.10:
SELECT COUNT(LEAGUENO) FROM PLAYERS ; SELECT COUNT(ALL LEAGUENO) FROM PLAYERS
Example 9.11:
SELECT COUNT(DISTINCT TOWN) FROM PLAYERS
Example 9.12:
SELECT COUNT(DISTINCT SUBSTR(NAME, 1, 1)) FROM PLAYERS
Example 9.13:
SELECT COUNT(DISTINCT YEAR(PAYMENT_DATE)) FROM PENALTIES
Example 9.14:
SELECT COUNT(DISTINCT TOWN), COUNT(DISTINCT SEX) FROM PLAYERS
Example 9.15:
SELECT PLAYERNO, NAME FROM PLAYERS AS P WHERE (SELECT COUNT(*) FROM PENALTIES AS PEN WHERE P.PLAYERNO = PEN.PLAYERNO) > (SELECT COUNT(*) FROM MATCHES AS M WHERE P.PLAYERNO = M.PLAYERNO)
Example 9.16:
SELECT PLAYERNO, NAME, (SELECT COUNT(*) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) AS NUMBER FROM PLAYERS WHERE (SELECT COUNT(*) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) >= 2 ; SELECT PLAYERNO, NAME, NUMBER FROM (SELECT PLAYERNO, NAME, (SELECT COUNT(*) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) AS NUMBER FROM PLAYERS) AS PN WHERE NUMBER >= 2
Example 9.17:
SELECT (SELECT COUNT(*) FROM PENALTIES), (SELECT COUNT(*) FROM MATCHES)
Example 9.18:
SELECT MAX(AMOUNT) FROM PENALTIES
Example 9.19:
SELECT MIN(AMOUNT) FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford')
Example 9.20:
SELECT COUNT(*) FROM PENALTIES WHERE AMOUNT = (SELECT MIN(AMOUNT) FROM PENALTIES)
Example 9.21:
SELECT DISTINCT TEAMNO, PLAYERNO FROM MATCHES AS M1 WHERE WON = (SELECT MAX(WON) FROM MATCHES AS M2 WHERE M1.TEAMNO = M2.TEAMNO)
Example 9.22:
SELECT (MAX(AMOUNT) - MIN(AMOUNT)) * 100 FROM PENALTIES
Example 9.23:
SELECT SUBSTR(MAX(NAME), 1, 1) FROM PLAYERS
Example 9.24:
SELECT MAX(LEAGUENO) FROM PLAYERS WHERE TOWN = 'Midhurst'
Example 9.25:
SELECT CASE WHEN MIN(LEAGUENO) IS NULL THEN 'Unknown' ELSE MIN(LEAGUENO) END FROM PLAYERS WHERE TOWN = 'Amsterdam'
Example 9.26:
SELECT PLAYERNO, AMOUNT, PAYMENT_DATE FROM PENALTIES AS PEN1 WHERE AMOUNT = (SELECT MAX(AMOUNT) FROM PENALTIES AS PEN2 WHERE PEN2.PLAYERNO = PEN1.PLAYERNO)
Example 9.27:
SELECT PLAYERNO, (SELECT MAX(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) AS HIGHESTPENALTY, (SELECT MAX(WON) FROM MATCHES WHERE MATCHES.PLAYERNO = PLAYERS.PLAYERNO) AS NUMBEROFSETS FROM PLAYERS
Example 9.28:
SELECT PLAYERNO FROM PLAYERS WHERE (SELECT MIN(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) = (SELECT MAX(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)
Example 9.29:
SELECT SUM(AMOUNT) FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Inglewood') ; SELECT SUM(DISTINCT AMOUNT) FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Inglewood')
Example 9.30:
SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44
Example 9.31:
SELECT DISTINCT PLAYERNO FROM PENALTIES WHERE AMOUNT > (SELECT AVG(AMOUNT) FROM PENALTIES)
Example 9.32:
SELECT AVG(DISTINCT AMOUNT) FROM PENALTIES
Example 9.33:
SELECT AVG(LENGTH(RTRIM(NAME))), MAX(LENGTH(RTRIM(NAME))) FROM PLAYERS
Example 9.34:
SELECT PAYMENTNO, AMOUNT, ABS(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES)) AS DIFFERENCE FROM PENALTIES AS P
Example 9.35:
SELECT VARIANCE(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44 ; SELECT SUM(P) / (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERNO = 44) FROM (SELECT POWER(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44),2) AS P FROM PENALTIES WHERE PLAYERNO = 44) AS POWERS
Example 9.36:
SELECT STDDEV(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44
Answer 9.4:
SELECT COUNT(*), MAX(AMOUNT) FROM PENALTIES
Answer 9.5:
SELECT COUNT(DISTINCT POSITION) FROM COMMITTEE_MEMBERS
Answer 9.6:
SELECT COUNT(LEAGUENO) FROM PLAYERS WHERE TOWN = 'Inglewood'
Answer 9.7:
SELECT TEAMNO, DIVISION, (SELECT COUNT(*) FROM MATCHES WHERE TEAMS.TEAMNO = MATCHES.TEAMNO) FROM TEAMS
Answer 9.8:
SELECT PLAYERNO, NAME, (SELECT COUNT(*) FROM MATCHES WHERE MATCHES.PLAYERNO = PLAYERS.PLAYERNO AND WON > LOST) FROM PLAYERS
Answer 9.9:
SELECT 'Number of players' , (SELECT COUNT(*) FROM PLAYERS) UNION SELECT 'Number of teams', (SELECT COUNT(*) FROM TEAMS) UNION SELECT 'Number of matches', (SELECT COUNT(*) FROM MATCHES)
Answer 9.10:
SELECT MIN(WON) FROM MATCHES WHERE WON > LOST
Answer 9.11:
SELECT PLAYERNO, ABS((SELECT MIN(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) - (SELECT MAX(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)) FROM PLAYERS
Answer 9.12:
SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE YEAR(BIRTH_DATE) = (SELECT MAX(YEAR(BIRTH_DATE)) FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO = 1))
Answer 9.14:
SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO = 1)
Answer 9.15:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE (SELECT SUM(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) > 100
Answer 9.16:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE WON > (SELECT SUM(WON) FROM MATCHES WHERE PLAYERNO = 27))
Answer 9.17:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE (SELECT SUM(WON) FROM MATCHES WHERE MATCHES.PLAYERNO = PLAYERS.PLAYERNO) = 8
Answer 9.18:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE LENGTH(RTRIM(NAME)) > (SELECT AVG(LENGTH(RTRIM(NAME))) FROM PLAYERS)
Answer 9.19:
SELECT PLAYERNO, ABS((SELECT AVG(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) - (SELECT MAX(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)) FROM PLAYERS
Answer 9.20:
SELECT PLAYERNO, REPEAT('*', CAST((SELECT AVG(AMOUNT) FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)/10 AS SIGNED INTEGER)) FROM PLAYERS
Answer 9.21:
SELECT SQRT(SUM(P) / (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERNO = 44)) FROM (SELECT POWER(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES WHERE PLAYERNO = 44),2) AS P FROM PENALTIES WHERE PLAYERNO = 44) AS POWERS
Example 10.1:
SELECT TOWN FROM PLAYERS GROUP BY TOWN
Example 10.2:
SELECT TOWN, COUNT(*) FROM PLAYERS GROUP BY TOWN
Example 10.3:
SELECT TEAMNO, COUNT(*), SUM(WON) FROM MATCHES GROUP BY TEAMNO
Example 10.4:
SELECT TEAMNO, COUNT(*) FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS INNER JOIN PLAYERS ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO WHERE TOWN = 'Eltham') GROUP BY TEAMNO
Example 10.5:
SELECT AMOUNT, COUNT(*), SUM(AMOUNT) FROM PENALTIES GROUP BY AMOUNT
Example 10.6:
SELECT TEAMNO, PLAYERNO FROM MATCHES GROUP BY TEAMNO, PLAYERNO ; SELECT TEAMNO, PLAYERNO FROM MATCHES GROUP BY PLAYERNO, TEAMNO ; SELECT TEAMNO, PLAYERNO, SUM(WON), COUNT(*), MIN(LOST) FROM MATCHES GROUP BY TEAMNO, PLAYERNO
Example 10.7:
SELECT P.PLAYERNO, NAME, SUM(AMOUNT) FROM PLAYERS AS P INNER JOIN PENALTIES AS PEN ON P.PLAYERNO = PEN.PLAYERNO GROUP BY P.PLAYERNO, NAME
Example 10.8:
SELECT YEAR(PAYMENT_DATE), COUNT(*) FROM PENALTIES GROUP BY YEAR(PAYMENT_DATE)
Example 10.9:
SELECT TRUNCATE(PLAYERNO/25,0), COUNT(*), MAX(PLAYERNO) FROM PLAYERS GROUP BY TRUNCATE(PLAYERNO/25,0)
Example 10.10:
SELECT LEAGUENO FROM PLAYERS GROUP BY LEAGUENO
Example 10.11:
SELECT CAST(AMOUNT * 100 AS SIGNED INTEGER) AS AMOUNT_IN_CENTS FROM PENALTIES GROUP BY AMOUNT
Example 10.12:
SELECT AVG(TOTAL) FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL FROM PENALTIES GROUP BY PLAYERNO) AS TOTALS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford' OR TOWN = 'Inglewood')
Example 10.13:
SELECT PLAYERS.PLAYERNO, NAME, NUMBER_OF_PENALTIES, NUMBER_OF_TEAMS FROM PLAYERS, (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_PENALTIES FROM PENALTIES GROUP BY PLAYERNO) AS NUMBER_PENALTIES, (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_TEAMS FROM TEAMS GROUP BY PLAYERNO) AS NUMBER_TEAMS WHERE PLAYERS.PLAYERNO = NUMBER_PENALTIES.PLAYERNO AND PLAYERS.PLAYERNO = NUMBER_TEAMS.PLAYERNO ; SELECT PLAYERS.PLAYERNO, NAME, (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES, (SELECT COUNT(*) FROM TEAMS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS FROM PLAYERS
Example 10.14:
SELECT DISTINCT M.PLAYERNO, NUMBERP FROM MATCHES AS M LEFT OUTER JOIN (SELECT PLAYERNO, COUNT(*) AS NUMBERP FROM PENALTIES GROUP BY PLAYERNO) AS NP ON M.PLAYERNO = NP.PLAYERNO
Example 10.15:
SELECT GROUPS.PGROUP, SUM(P.AMOUNT) FROM PENALTIES AS P, (SELECT 1 AS PGROUP, '1980-01-01' AS START, '1981-06-30' AS END UNION SELECT 2, '1981-07-01', '1982-12-31' UNION SELECT 3, '1983-01-01', '1984-12-31') AS GROUPS WHERE P.PAYMENT_DATE BETWEEN START AND END GROUP BY GROUPS.PGROUP ORDER BY 1
Example 10.16:
SELECT P1.PAYMENTNO, P1.AMOUNT, SUM(P2.AMOUNT) FROM PENALTIES AS P1, PENALTIES AS P2 WHERE P1.PAYMENTNO >= P2. PAYMENTNO GROUP BY P1. PAYMENTNO, P1.AMOUNT ORDER BY P1. PAYMENTNO
Example 10.17:
SELECT P1.PAYMENTNO, P1.AMOUNT, (P1.AMOUNT * 100) / SUM(P2.AMOUNT) FROM PENALTIES AS P1, PENALTIES AS P2 GROUP BY P1.PAYMENTNO, P1.AMOUNT ORDER BY P1.PAYMENTNO
Example 10.18:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO UNION SELECT CAST(NULL AS SIGNED INTEGER), SUM(AMOUNT) FROM PENALTIES ; SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO WITH ROLLUP
Example 10.19:
SELECT SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY SEX, TOWN WITH ROLLUP
Example 10.20:
SELECT ROW_NUMBER() OVER () AS SEQNO, SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY SEX, TOWN WITH CUBE ORDER BY SEX, TOWN
Example 10.21:
SELECT TOWN, MIN(BIRTH_DATE) FROM PLAYERS GROUP BY TOWN ; SELECT TOWN, MIN(BIRTH_DATE) FROM PLAYERS GROUP BY GROUPING SETS ((TOWN))
Example 10.22:
SELECT CAST(NULL AS CHAR), TOWN, COUNT(*) FROM PLAYERS GROUP BY TOWN UNION SELECT SEX, CAST(NULL AS CHAR), COUNT(*) FROM PLAYERS GROUP BY SEX ORDER BY 2, 1 ; SELECT SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY GROUPING SETS ((TOWN), (SEX)) ORDER BY 2, 1
Example 10.23:
SELECT SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY GROUPING SETS ((SEX, TOWN), (SEX), ()) ORDER BY 1, 2
Example 10.24:
SELECT TEAMNO, PLAYERNO, COUNT(*) FROM MATCHES GROUP BY GROUPING SETS (TEAMNO, PLAYERNO) ORDER BY 1, 2
Example 10.25:
SELECT SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY ROLLUP (SEX, TOWN) ORDER BY 1, 2
Example 10.26:
SELECT ROW_NUMBER() OVER () AS SEQNO, SEX, TOWN, YEAR(BIRTH_DATE), COUNT(*) FROM PLAYERS GROUP BY ROLLUP (SEX, TOWN, YEAR(BIRTH_DATE)) ORDER BY 2, 3, 4
Example 10.27:
SELECT ROW_NUMBER() OVER () AS SEQNO, SEX, TOWN, YEAR(BIRTH_DATE), COUNT(*) FROM PLAYERS GROUP BY ROLLUP (SEX, (TOWN, YEAR(BIRTH_DATE))) ORDER BY 2, 3, 4
Example 10.28:
SELECT ROW_NUMBER() OVER () AS SEQNO, SEX, TOWN, COUNT(*) FROM PLAYERS GROUP BY CUBE (SEX, TOWN) ORDER BY 2, 3
Answer 10.1:
SELECT JOINED FROM PLAYERS GROUP BY JOINED
Answer 10.2:
SELECT JOINED, COUNT(*) FROM PLAYERS GROUP BY JOINED
Answer 10.3:
SELECT PLAYERNO, AVG(AMOUNT), COUNT(*) FROM PENALTIES GROUP BY PLAYERNO
Answer 10.4:
SELECT TEAMNO, COUNT(*), SUM(WON) FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE DIVISION = 'first') GROUP BY TEAMNO
Answer 10.5:
SELECT WON, LOST, COUNT(*) FROM MATCHES WHERE WON > LOST GROUP BY WON, LOST ORDER BY 1, 2
Answer 10.6:
SELECT YEAR(BEGIN_DATE), MONTH(BEGIN_DATE), COUNT(*) FROM COMMITTEE_MEMBERS GROUP BY YEAR(BEGIN_DATE), MONTH(BEGIN_DATE) ORDER BY 1, 2
Answer 10.7:
SELECT P.NAME, T.DIVISION, SUM(WON) FROM (MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO) INNER JOIN TEAMS AS T ON M.TEAMNO = T.TEAMNO GROUP BY P.NAME, T.DIVISION ORDER BY 1
Answer 10.8:
SELECT NAME, INITIALS, COUNT(*) FROM PLAYERS AS P INNER JOIN PENALTIES AS PEN ON P.PLAYERNO = PEN.PLAYERNO WHERE P.TOWN = 'Inglewood' GROUP BY P.PLAYERNO, NAME, INITIALS
Answer 10.9:
SELECT T.TEAMNO, DIVISION, SUM(WON) FROM TEAMS AS T, MATCHES AS M WHERE T.TEAMNO = M.TEAMNO GROUP BY T.TEAMNO, DIVISION
Answer 10.10:
SELECT LENGTH(RTRIM(NAME)), COUNT(*) FROM PLAYERS GROUP BY LENGTH(RTRIM(NAME))
Answer 10.11:
SELECT ABS(WON - LOST), COUNT(*) FROM MATCHES GROUP BY ABS(WON ?LOST)
Answer 10.14:
SELECT AVG(NUMBERS) FROM (SELECT COUNT(*) AS NUMBERS FROM PLAYERS GROUP BY TOWN) AS TOWNS
Answer 10.15:
SELECT TEAMS.TEAMNO, DIVISION, NUMBER_PLAYERS FROM TEAMS LEFT OUTER JOIN (SELECT TEAMNO, COUNT(*) AS NUMBER_PLAYERS FROM MATCHES GROUP BY TEAMNO) AS M ON (TEAMS.TEAMNO = M.TEAMNO)
Answer 10.16:
SELECT PLAYERS.PLAYERNO, NAME, SUM_AMOUNT, NUMBER_TEAMS FROM (PLAYERS LEFT OUTER JOIN (SELECT PLAYERNO, SUM(AMOUNT) AS SUM_AMOUNT FROM PENALTIES GROUP BY PLAYERNO) AS TOTALS ON (PLAYERS.PLAYERNO = TOTALS.PLAYERNO)) LEFT OUTER JOIN (SELECT PLAYERNO, COUNT(*) AS NUMBER_TEAMS FROM TEAMS WHERE DIVISION = 'first' GROUP BY PLAYERNO) AS NUMBERS ON (PLAYERS.PLAYERNO = NUMBERS.PLAYERNO)
Answer 10.17:
SELECT TEAMNO, COUNT(DISTINCT PLAYERNO) FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM PLAYERS AS P INNER JOIN TEAMS AS T ON P.PLAYERNO = T.PLAYERNO AND TOWN = 'Stratford') AND WON > LOST GROUP BY TEAMNO
Answer 10.18:
SELECT PLAYERNO, NAME, JOINED - AVERAGE FROM PLAYERS, (SELECT AVG(JOINED) AS AVERAGE FROM PLAYERS) AS T
Answer 10.19:
SELECT PLAYERNO, NAME, JOINED ?AVERAGE FROM PLAYERS, (SELECT TOWN, AVG(JOINED) AS AVERAGE FROM PLAYERS GROUP BY TOWN) AS TOWNS WHERE PLAYERS.TOWN = TOWNS.TOWN
Answer 10.20:
SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO WITH ROLLUP
Answer 10.21:
SELECT P.NAME, T.DIVISION, SUM(WON) FROM (MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO) INNER JOIN TEAMS AS T ON M.TEAMNO = T.TEAMNO GROUP BY P.NAME, T.DIVISION WITH ROLLUP
Answer 10.23:
SELECT ROW_NUMBER() OVER () AS SEQNO, TEAMNO, PLAYERNO, WON, COUNT(*) FROM MATCHES GROUP BY TEAMNO, PLAYERNO, WON WITH CUBE ORDER BY 2, 3
Answer 10.24:
SELECT COUNT(*) FROM MATCHES GROUP BY GROUPING SETS (())
Answer 10.25:
SELECT TEAMNO, PLAYERNO, COUNT(*) FROM MATCHES GROUP BY GROUPING SETS ((TEAMNO, PLAYERNO), (TEAMNO), ()) ORDER BY 1, 2
Answer 10.27:
SELECT TEAMNO, PLAYERNO, COUNT(*) FROM MATCHES WHERE WON > LOST GROUP BY ROLLUP (TEAMNO, PLAYERNO) ORDER BY 1, 2
Answer 10.28:
SELECT P.TOWN, P.SEX, M.TEAMNO, COUNT(*) FROM MATCHES AS M INNER JOIN PLAYERS AS P ON M.PLAYERNO = P.PLAYERNO GROUP BY CUBE (P.TOWN, P.SEX, M.TEAMNO) ORDER BY 1, 2, 3
Example 11.1:
SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING COUNT(*) > 1
Example 11.2:
SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING MAX(YEAR(PAYMENT_DATE)) = 1984
Example 11.3:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO HAVING SUM(AMOUNT) > 150
Example 11.4:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM TEAMS) GROUP BY PLAYERNO HAVING SUM(AMOUNT) > 80
Example 11.5:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO HAVING SUM(AMOUNT) >= ALL (SELECT SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO)
Answer 11.1:
SELECT TOWN FROM PLAYERS GROUP BY TOWN HAVING COUNT(*) > 4
Answer 11.2:
SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING SUM(AMOUNT) > 150
Answer 11.3:
SELECT NAME, INITIALS, COUNT(*) FROM PLAYERS INNER JOIN PENALTIES ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO GROUP BY PLAYERS.PLAYERNO, NAME, INITIALS HAVING COUNT(*) > 1
Answer 11.4:
SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM MATCHES GROUP BY TEAMNO)
Answer 11.5:
SELECT TEAMNO, DIVISION FROM TEAMS WHERE TEAMNO IN (SELECT TEAMNO FROM MATCHES GROUP BY TEAMNO HAVING COUNT(DISTINCT PLAYERNO) > 4)
Answer 11.6:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES WHERE AMOUNT > 40 GROUP BY PLAYERNO HAVING COUNT(*) >= 2)
Answer 11.7:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING SUM(AMOUNT) >= ALL (SELECT SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO))
Answer 11.8:
SELECT PLAYERNO FROM PENALTIES WHERE PLAYERNO <> 104 GROUP BY PLAYERNO HAVING SUM(AMOUNT) = (SELECT SUM(AMOUNT) * 2 FROM PENALTIES WHERE PLAYERNO = 104)
Answer 11.9:
SELECT PLAYERNO FROM PENALTIES WHERE PLAYERNO <> 6 GROUP BY PLAYERNO HAVING COUNT(*) = (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERNO = 6)
Example 12.1:
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES ORDER BY PLAYERNO
Example 12.2:
SELECT PLAYERNO, AMOUNT FROM PENALTIES ORDER BY PLAYERNO, AMOUNT
Example 12.3:
SELECT AMOUNT FROM PENALTIES ORDER BY PLAYERNO, AMOUNT
Example 12.4:
SELECT NAME, INITIALS, PLAYERNO FROM PLAYERS ORDER BY SUBSTR(NAME, 1, 1)
Example 12.5:
SELECT PLAYERNO, AMOUNT FROM PENALTIES ORDER BY ABS(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES))
Example 12.6:
SELECT PLAYERNO, AMOUNT FROM PENALTIES AS P1 ORDER BY (SELECT AVG(AMOUNT) FROM PENALTIES AS P2 WHERE P1.PLAYERNO = P2.PLAYERNO)
Example 12.7:
SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO ORDER BY 2
Example 12.8:
SELECT PLAYERNO, NAME, (SELECT SUM(AMOUNT) FROM PENALTIES AS PEN WHERE PEN.PLAYERNO=P.PLAYERNO) FROM PLAYERS AS P ORDER BY 3 ; SELECT PLAYERNO, NAME, (SELECT SUM(AMOUNT) FROM PENALTIES AS PEN WHERE PEN.PLAYERNO=P.PLAYERNO) AS TOTAL FROM PLAYERS AS P ORDER BY TOTAL
Example 12.9:
SELECT PLAYERNO, AMOUNT FROM PENALTIES ORDER BY PLAYERNO DESC, AMOUNT ASC
Example 12.10:
CREATE TABLE CODES (CODE CHAR(4) NOT NULL) ; INSERT INTO CODES VALUES ('abc') ; INSERT INTO CODES VALUES ('ABC') ; INSERT INTO CODES VALUES ('-abc') ; INSERT INTO CODES VALUES ('a bc') ; INSERT INTO CODES VALUES ('ab') ; INSERT INTO CODES VALUES ('9abc') ; SELECT * FROM CODES ORDER BY CODE
Example 12.11:
SELECT DISTINCT LEAGUENO FROM PLAYERS ORDER BY 1 DESC
Answer 12.3:
SELECT PLAYERNO, TEAMNO, WON - LOST FROM MATCHES ORDER BY 3 ASC
Example 13.1:
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'
Example 13.2:
SELECT BIRTH_DATE AS DATES FROM PLAYERS UNION SELECT PAYMENT_DATE FROM PENALTIES
Example 13.3:
SELECT PLAYERNO FROM PENALTIES UNION SELECT PLAYERNO FROM TEAMS
Example 13.4:
SELECT PLAYERNO FROM PENALTIES UNION SELECT PLAYERNO FROM TEAMS UNION SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford'
Example 13.5:
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
Example 13.6:
SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' INTERSECT SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE > '1960-12-31' ORDER BY 1 ; SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' AND BIRTH_DATE > '1960-12-31' ORDER BY 1
Example 13.7:
SELECT PLAYERNO FROM TEAMS INTERSECT SELECT PLAYERNO FROM PENALTIES
Example 13.8:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM TEAMS INTERSECT SELECT PLAYERNO FROM PENALTIES)
Example 13.9:
SELECT POWER(NUMBER,2) AS POWERS FROM (SELECT CAST(DIGIT1. DIGIT || DIGIT2. DIGIT AS UNSIGNED INTEGER) AS NUMBER FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT1, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT2) AS NUMBERS WHERE POWER(NUMBER,2) < 5000 INTERSECT SELECT POWER(NUMBER,3) AS POWERS FROM (SELECT CAST(DIGIT1.DIGIT || DIGIT2.DIGIT AS UNSIGNED INTEGER) AS NUMBER FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT1, (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGIT2) AS NUMBERS WHERE POWER(NUMBER,3) < 5000
Example 13.10:
SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' EXCEPT SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE > '1960-12-31' ORDER BY 1 ; SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' AND NOT(BIRTH_DATE > '1960-12-31') ORDER BY 1
Example 13.11:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES EXCEPT SELECT PLAYERNO FROM TEAMS) ; SELECT PLAYERNO FROM TEAMS INTERSECT SELECT PLAYERNO FROM PENALTIES ; SELECT PLAYERNO FROM TEAMS EXCEPT (SELECT PLAYERNO FROM TEAMS EXCEPT SELECT PLAYERNO FROM PENALTIES)
Example 13.12:
SELECT PLAYERNO FROM PENALTIES EXCEPT SELECT 6 UNION SELECT 27 UNION SELECT 58
Example 13.13:
SELECT PLAYERNO FROM PENALTIES UNION ALL SELECT PLAYERNO FROM TEAMS
Example 13.14:
SELECT PLAYERNO FROM PENALTIES EXCEPT ALL SELECT PLAYERNO FROM TEAMS
Example 13.15:
SELECT PLAYERNO FROM PENALTIES EXCEPT SELECT PLAYERNO FROM TEAMS UNION SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Eltham' ; SELECT PLAYERNO FROM PENALTIES EXCEPT ( SELECT PLAYERNO FROM TEAMS UNION SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Eltham' )
Answer 13.1:
SELECT PLAYERNO FROM COMMITTEE_MEMBERS UNION SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING COUNT(*) >= 2
Answer 13.2:
SELECT MAX(ADATE) FROM (SELECT MAX(BIRTH_DATE) AS ADATE FROM PLAYERS UNION SELECT MAX(PAYMENT_DATE) AS ADATE FROM PENALTIES) AS TWODATES
Answer 13.5:
SELECT PLAYERNO FROM COMMITTEE_MEMBERS INTERSECT SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING COUNT(*) >= 2
Answer 13.6:
SELECT COUNT(*) FROM (SELECT PLAYERNO FROM COMMITTEE_MEMBERS INTERSECT SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING COUNT(*) >= 2) AS PLAYERS
Answer 13.7:
SELECT PLAYERNO FROM COMMITTEE_MEMBERS EXCEPT SELECT PLAYERNO FROM PENALTIES
Answer 13.8:
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (11),(12),(13),(14),(15),(16),(17),(18),(19),(20) EXCEPT SELECT PAYMENTNO FROM PENALTIES
Answer 13.9:
SELECT SUM(NUMBER) FROM (SELECT COUNT(*) AS NUMBER FROM PLAYERS UNION ALL SELECT COUNT(*) AS NUMBER FROM TEAMS) AS NUMBERS
Answer 13.10:
SELECT POWER(DIGIT,2) FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGITS1 UNION ALL SELECT POWER(DIGIT,3) FROM (SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9') AS DIGITS2 ORDER BY 1
Example 14.1:
INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (3, 100, 'third') ; INSERT INTO TEAMS VALUES (3, 100, 'third') ; INSERT INTO TEAMS (PLAYERNO, DIVISION, TEAMNO) VALUES (100, 'third', 3) ; INSERT INTO TEAMS (TEAMNO, DIVISION) VALUES (3, 'third')
Example 14.2:
INSERT INTO PLAYERS (PLAYERNO, NAME, INITIALS, SEX, JOINED, STREET, TOWN) VALUES (611, 'Jones', 'GG', 'M', 1977, 'Green Way', 'Stratford') ; INSERT INTO PLAYERS (PLAYERNO, NAME, INITIALS, BIRTH_DATE, SEX, JOINED, STREET, HOUSENO, POSTCODE, TOWN, PHONENO, LEAGUENO) VALUES (611, 'Jones', 'GG', NULL, 'M', 1977, 'Green Way', NULL, NULL, 'Stratford', NULL, NULL)
Example 14.3:
INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (6, 100, 'third'), (7, 27, 'fourth'), (8, 39, 'fourth'), (9, 112, 'sixth')
Example 14.4:
CREATE TABLE TOTALS (NUMBERPLAYERS INTEGER NOT NULL, SUMPENALTIES DECIMAL(9,2) NOT NULL) ; INSERT INTO TOTALS (NUMBERPLAYERS, SUMPENALTIES) VALUES ((SELECT COUNT(*) FROM PLAYERS), (SELECT SUM(AMOUNT) FROM PENALTIES))
Example 14.5:
CREATE TABLE RECR_PLAYERS (PLAYERNO SMALLINT NOT NULL, NAME CHAR(15) NOT NULL, TOWN CHAR(10) NOT NULL, PHONENO CHAR(13), PRIMARY KEY (PLAYERNO)) ; INSERT INTO RECR_PLAYERS (PLAYERNO, NAME, TOWN, PHONENO) SELECT PLAYERNO, NAME, TOWN, PHONENO FROM PLAYERS WHERE LEAGUENO IS NULL ; INSERT INTO RECR_PLAYERS SELECT PLAYERNO, NAME, TOWN, PHONENO FROM PLAYERS WHERE LEAGUENO IS NULL ; INSERT INTO RECR_PLAYERS (TOWN, PHONENO, NAME, PLAYERNO) SELECT TOWN, PHONENO, NAME, PLAYERNO FROM PLAYERS WHERE LEAGUENO IS NULL
Example 14.6:
INSERT INTO RECR_PLAYERS (PLAYERNO, NAME, TOWN, PHONENO) SELECT PLAYERNO + 1000, NAME, TOWN, PHONENO FROM RECR_PLAYERS
Example 14.7:
INSERT INTO PENALTIES SELECT PAYMENTNO + 100, PLAYERNO, PAYMENT_DATE, AMOUNT FROM PENALTIES WHERE AMOUNT > (SELECT AVG(AMOUNT) FROM PENALTIES)
Example 14.8:
UPDATE PLAYERS SET LEAGUENO = '2000' WHERE PLAYERNO = 95 ; UPDATE PLAYERS AS P SET P.LEAGUENO = '2000' WHERE P.PLAYERNO = 95
Example 14.9:
UPDATE PENALTIES SET AMOUNT = AMOUNT * 1.05
Example 14.10:
UPDATE MATCHES SET WON = 0 WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford')
Example 14.11:
UPDATE PLAYERS SET STREET = 'Palmer Street', HOUSENO = '83', TOWN = 'Inglewood', POSTCODE = '1234UU', PHONENO = NULL WHERE NAME = 'Parmenter' ; UPDATE PLAYERS SET STREET = TOWN, TOWN = STREET WHERE PLAYERNO = 44 ; UPDATE PLAYERS SET STREET = TOWN WHERE PLAYERNO = 44 ; UPDATE PLAYERS SET TOWN = STREET WHERE PLAYERNO = 44
Example 14.12:
CREATE TABLE PLAYERS_DATA (PLAYERNO INTEGER NOT NULL PRIMARY KEY, NUMBER_MAT INTEGER, SUM_PENALTIES DECIMAL(7,2)) ; INSERT INTO PLAYERS_DATA (PLAYERNO) SELECT PLAYERNO FROM PLAYERS ; UPDATE PLAYERS_DATA AS PD SET NUMBER_MAT = (SELECT COUNT(*) FROM MATCHES AS M WHERE M.PLAYERNO = PD.PLAYERNO), SUM_PENALTIES = (SELECT SUM(AMOUNT) FROM PENALTIES AS PEN WHERE PEN.PLAYERNO = PD.PLAYERNO)
Example 14.13:
UPDATE PENALTIES SET AMOUNT = AMOUNT ?(SELECT AVG(AMOUNT) FROM PENALTIES)
Example 14.14:
DELETE FROM PENALTIES WHERE PLAYERNO = 44 ; DELETE FROM PENALTIES AS PEN WHERE PEN.PLAYERNO = 44
Example 14.15:
DELETE FROM PLAYERS WHERE JOINED > (SELECT AVG(JOINED) FROM PLAYERS WHERE TOWN = 'Stratford')
Answer 14.1:
INSERT INTO PENALTIES VALUES (15, 27, '1985-11-08', 75)
Answer 14.2:
INSERT INTO PENALTIES SELECT PAYMENTNO + 1000, PLAYERNO, PAYMENT_DATE, AMOUNT FROM PENALTIES WHERE AMOUNT > (SELECT AVG(AMOUNT) FROM PENALTIES) UNION SELECT PAYMENTNO + 2000, PLAYERNO, PAYMENT_DATE, AMOUNT FROM PENALTIES WHERE PLAYERNO = 27
Answer 14.3:
UPDATE PLAYERS SET SEX = 'W' WHERE SEX = 'F'
Answer 14.4:
UPDATE PLAYERS SET SEX = 'X' WHERE SEX = 'F' ; UPDATE PLAYERS SET SEX = 'F' WHERE SEX = 'M' ; UPDATE PLAYERS SET SEX = 'M' WHERE SEX = 'X' ; UPDATE PLAYERS SET SEX = CASE SEX WHEN 'F' THEN 'M' ELSE 'F' END
Answer 14.5:
UPDATE PENALTIES SET AMOUNT = AMOUNT * 1.2 WHERE AMOUNT > (SELECT AVG(AMOUNT) FROM PENALTIES)
Answer 14.6:
DELETE FROM PENALTIES WHERE PLAYERNO = 44 AND YEAR(PAYMENT_DATE) = 1980
Answer 14.7:
DELETE FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE TEAMNO IN (SELECT TEAMNO FROM TEAMS WHERE DIVISION = 'second'))
Answer 14.8:
DELETE FROM PLAYERS WHERE TOWN = (SELECT TOWN FROM PLAYERS WHERE PLAYERNO = 44) AND PLAYERNO <> 44
Example 15.1:
CREATE TABLE PLAYERS (PLAYERNO INTEGER NOT NULL PRIMARY KEY, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE, SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET VARCHAR(30) NOT NULL, HOUSENO CHAR(4), POSTCODE CHAR(6), TOWN VARCHAR(30) NOT NULL, PHONENO CHAR(13), LEAGUENO CHAR(4) UNIQUE)
Example 15.2:
CREATE TABLE TEST.PENALTIES (PAYMENTNO INTEGER NOT NULL PRIMARY KEY, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL)
Example 15.3:
CREATE TABLE MEASUREMENTS (NR INTEGER, MEASUREMENT_VALUE FLOAT(1)) ; INSERT INTO MEASUREMENTS VALUES (1, 99.99), (2, 99999.99), (3, 99999999.99), (4, 99999999999.99), (5, 99999999999999.99), (6, 0.999999), (7, 0.9999999), (8, 99999999.9999), (9, (1.0/3)) ; SELECT * FROM MEASUREMENTS
Example 15.4:
SELECT TRUE
Example 15.5:
CREATE TEMPORARY TABLE SUMPENALTIES (TOTAL DECIMAL(10,2)) ; INSERT INTO SUMPENALTIES SELECT SUM(AMOUNT) FROM PENALTIES
Example 15.6:
CREATE TABLE TESTTABLE (C1 INTEGER) ; INSERT INTO TESTTABLE VALUES (1) ; CREATE TEMPORARY TABLE TESTTABLE (C1 INTEGER, C2 INTEGER) ; INSERT INTO TESTTABLE VALUES (2, 3) ; SELECT * FROM TESTTABLE
Example 15.7:
CREATE TABLE TEAMS_COPY1 LIKE TEAMS
Example 15.8:
CREATE TABLE TEAMS_COPY2 AS (SELECT * FROM TEAMS)
Example 15.9:
CREATE TABLE TEAMS_COPY3 AS (SELECT TEAMNO AS TNO, PLAYERNO AS PNO, DIVISION FROM TEAMS)
SELECT * FROM TEAMS_COPY3
Example 15.10:
CREATE TABLE TEAMS_COPY4 AS (SELECT TEAMNO, PLAYERNO FROM TEAMS WHERE PLAYERNO = 27)
Example 15.11:
CREATE TEMPORARY TABLE TEAMS AS (SELECT * FROM TEAMS
Example 15.12:
CREATE TABLE TEAMS_COPY5 (TEAMNO INTEGER NOT NULL PRIMARY KEY, PLAYERNO INTEGER NULL, DIVISION CHAR(10) NOT NULL) AS (SELECT * FROM TEAMS) ; CREATE TABLE TEAMS_COPY5 (PLAYERNO INTEGER NULL, DIVISION CHAR(10) NOT NULL) AS (SELECT * FROM TEAMS)
Example 15.13:
CREATE TABLE TEAMS_COPY6 (PLAYERNO INTEGER NULL, COMMENT VARCHAR(100)) AS (SELECT * FROM TEAMS) ; SELECT * FROM TEAMS_COPY6
Example 15.14:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL PRIMARY KEY, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL DEFAULT '1990-01-01', AMOUNT DECIMAL(7,2) NOT NULL DEFAULT 50.00) ; INSERT INTO PENALTIES (PAYMENTNO, PLAYERNO) VALUES (15, 27) ; INSERT INTO PENALTIES (PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT) VALUES (15, 27, DEFAULT, DEFAULT)
Example 15.15:
UPDATE PENALTIES SET AMOUNT = DEFAULT
Example 15.16:
UPDATE PENALTIES SET AMOUNT = YEAR(DEFAULT(PAYMENT_DATE))*10
Example 15.17:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL PRIMARY KEY COMMENT 'Primary key of the table', PLAYERNO INTEGER NOT NULL COMMENT 'Player who has incurred the penalty', PAYMENT_DATE DATE NOT NULL COMMENT 'Date on which the penalty has been paid', AMOUNT DECIMAL(7,2) NOT NULL COMMENT 'Amount of the penalty in dollars') ; SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PENALTIES'
Example 15.18:
CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL PRIMARY KEY, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON SMALLINT NOT NULL, LOST SMALLINT NOT NULL, BALANCE AS ABS(WON ?LOST))
Example 15.19:
SELECT MATCHNO, BALANCE FROM MATCHES WHERE BALANCE > 1
Example 15.20:
SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, NULLABLE FROM COLUMNS WHERE TABLE_NAME = 'PLAYERS' AND TABLE_CREATOR = 'TENNIS' ORDER BY COLUMN_NO
Example 15.21:
SELECT 'PLAYERS' AS TABLE_NAME, COUNT(*) AS NUMBER_ROWS, (SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME = 'PLAYERS' AND TABLE_CREATOR = 'TENNIS') AS P FROM PLAYERS UNION SELECT 'TEAMS', COUNT(*), (SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME = 'TEAMS' AND TABLE_CREATOR = 'TENNIS') AS T FROM TEAMS UNION SELECT 'PENALTIES', COUNT(*), (SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME = 'PENALTIES' AND TABLE_CREATOR = 'TENNIS') AS PEN FROM PENALTIES UNION SELECT 'MATCHES', COUNT(*), (SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME = 'MATCHES' AND TABLE_CREATOR = 'TENNIS') AS M FROM MATCHES UNION SELECT 'COMMITTEE_MEMBERS', COUNT(*), (SELECT COUNT(*) FROM COLUMNS WHERE TABLE_NAME = 'COMMITTEE_MEMBERS' AND TABLE_CREATOR = 'TENNIS') AS CM FROM COMMITTEE_MEMBERS ORDER BY 1
Answer 15.6:
CREATE TABLE DEPARTMENT ( DEPNO CHAR(5) NOT NULL PRIMARY KEY, BUDGET DECIMAL(8,2), LOCATION VARCHAR(30))
Answer 15.7:
CREATE TABLE P_COPY LIKE PLAYERS
Answer 15.8:
CREATE TABLE P2_COPY AS (SELECT * FROM PLAYERS)
Answer 15.9:
CREATE TABLE NUMBERS AS (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford')
Example 16.1:
CREATE TABLE PLAYERS ( PLAYERNO INTEGER NOT NULL PRIMARY KEY, : : LEAGUENO CHAR(4)) ; CREATE TABLE PLAYERS ( PLAYERNO INTEGER NOT NULL, : : LEAGUENO CHAR(4), PRIMARY KEY (PLAYERNO))
Example 16.2:
CREATE TABLE DIPLOMAS (STUDENT INTEGER NOT NULL, COURSE INTEGER NOT NULL, DDATE DATE NOT NULL, SUCCESSFUL CHAR(1), LOCATION VARCHAR(5), PRIMARY KEY (STUDENT, COURSE, DDATE))
Example 16.3:
CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL UNIQUE, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO)) ; CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO), UNIQUE (PLAYERNO))
Example 16.4:
CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)) ; SELECT * FROM TEAMS WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM PLAYERS)
Example 16.5:
CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
Example 16.6:
CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON INTEGER NOT NULL, LOST INTEGER NOT NULL, PRIMARY KEY (MATCHNO), FOREIGN KEY (TEAMNO) REFERENCES TEAMS (TEAMNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
Example 16.7:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)) ; CREATE TABLE EMPLOYEES (EMPLOYEE_NO CHAR(10) NOT NULL, MANAGER_NO CHAR(10), PRIMARY KEY (EMPLOYEE_NO), FOREIGN KEY (MANAGER_NO) REFERENCES EMPLOYEES (EMPLOYEE_NO))
Example 16.8:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) ON UPDATE RESTRICT ON DELETE RESTRICT)
Example 16.9:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) ON DELETE CASCADE) ; DELETE FROM PLAYERS WHERE PLAYERNO = 127 ; DELETE FROM PENALTIES WHERE PLAYERNO = 127
Example 16.10:
CREATE TABLE PENALTIES (PAYMENTNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, PAYMENT_DATE DATE NOT NULL, AMOUNT DECIMAL(7,2) NOT NULL, PRIMARY KEY (PAYMENTNO), FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO) ON DELETE SET NULL)
Example 16.11:
CREATE TABLE PLAYERS_X (PLAYERNO INTEGER NOT NULL, SEX CHAR(1) NOT NULL CHECK(SEX IN ('M', 'F')))
Example 16.12:
CREATE TABLE PLAYERS_Y (PLAYERNO INTEGER NOT NULL, BIRTH_DATE DATE CHECK(BIRTH_DATE > '1920-01-01'))
Example 16.13:
CREATE TABLE PLAYERS_Z (PLAYERNO SMALLINT NOT NULL, BIRTH_DATE DATE, JOINED SMALLINT NOT NULL, CHECK(YEAR(BIRTH_DATE) < JOINED)) ; CREATE TABLE PLAYERS_W (PLAYERNO SMALLINT, BIRTH_DATE DATE NOT NULL, JOINED SMALLINT NOT NULL, CHECK(YEAR(BIRTH_DATE) < JOINED), CHECK(BIRTH_DATE > '1920-01-01'), CHECK(JOINED < 1880))
Example 16.14:
CREATE TABLE PLAYERS_V (PLAYERNO SMALLINT NOT NULL, SEX CHAR(1) NOT NULL CHECK(SEX IN (SELECT SEX FROM PLAYERS)))
Example 16.14:
CREATE TABLE PLAYERS_V (PLAYERNO SMALLINT NOT NULL, SEX CHAR(1) NOT NULL CHECK(SEX IN (SELECT SEX FROM PLAYERS)))
Answer 16.3:
CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON INTEGER NOT NULL, LOST INTEGER NOT NULL, PRIMARY KEY (MATCHNO)) ; CREATE TABLE MATCHES (MATCHNO INTEGER NOT NULL PRIMARY KEY, TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, WON INTEGER NOT NULL, LOST INTEGER NOT NULL)
Example 17.1:
SHOW CHARACTER SET ; SELECT CHARACTER_SET_NAME, DESCRIPTION, DEFAULT_COLLATE_NAME, MAXLEN FROM INFORMATION_SCHEMA.CHARACTER_SETS
Example 17.2:
SHOW COLLATION LIKE 'utf8%' ; SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME LIKE 'utf8%'
Example 17.3:
CREATE TABLE TABUCS2 (C1 CHAR(10) CHARACTER SET ucs2 NOT NULL PRIMARY KEY, C2 VARCHAR(10) CHARACTER SET ucs2)
Example 17.4:
CREATE TABLE TABDEFKARSET (C1 CHAR(10) NOT NULL, C2 VARCHAR(10)) ; SELECT COLUMN_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABDEFKARSET'
Example 17.5:
CREATE TABLE TABUTF8 (C1 CHAR(10) NOT NULL, C2 VARCHAR(10)) DEFAULT CHARACTER SET utf8 ; SELECT COLUMN_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABUTF8'
Example 17.6:
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('TABUCS2', 'KARSETHP8')
Example 17.7:
CREATE TABLE TABCOLLATE (C1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_romanian_ci NOT NULL, C2 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_spanish_ci) ; SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABCOLLATE'
Example 17.8:
CREATE TABLE TABDEFCOL (C1 CHAR(10) NOT NULL, C2 VARCHAR(10)) CHARACTER SET = utf8 COLLATE = utf8_romanian_ci ; SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABDEFCOL'
Example 17.9:
CREATE TABLE TWOCHARSETS (C1 CHAR(10) CHARACTER SET 'latin1' NOT NULL, C2 VARCHAR(10) CHARACTER SET 'utf8') ; INSERT INTO TWOCHARSETS VALUES ('A', 'A') ; SELECT * FROM TWOCHARSETS WHERE C1 = C2
Example 17.10:
CREATE TABLE TWOCOLL (C1 CHAR(10) COLLATE 'latin1_general_ci' NOT NULL, C2 VARCHAR(10) COLLATE 'latin1_danish_ci') ; INSERT INTO TWOCOLL VALUES ('A', 'A') ; SELECT * FROM TWOCOLL WHERE C1 = C2 ; SELECT * FROM TWOCOLL WHERE C1 COLLATE latin1_danish_ci = C2 ; SELECT * FROM TWOCOLL WHERE C1 COLLATE utf8_general_ci = C2
Example 17.11:
SELECT _utf8'database'
Example 17.12:
SELECT COLLATION(_utf8'database'), COLLATION(_utf8'database' COLLATE utf8_bin), COLLATION((SELECT MAX(NAME) FROM PLAYERS))
Example 17.13:
SELECT CHARSET(_utf8'database'), CHARSET((SELECT MAX(NAME) FROM PLAYERS))
Example 17.14:
SELECT _latin1'Muller' AS NAME UNION SELECT CONCAT('M', _latin1 x'FC', 'ller') ORDER BY NAME COLLATE latin1_swedish_ci
Example 17.15:
CREATE TABLE LETTERS (SEQNO INTEGER NOT NULL PRIMARY KEY, LETTER CHAR(1) CHARACTER SET UTF8 NOT NULL) ; INSERT INTO LETTERS VALUES (1, 'e'), (2, x'E9'),(3, x'EB') ; SELECT LETTER FROM (SELECT LETTER COLLATE utf8_general_ci AS LETTER FROM LETTERS) AS L GROUP BY LETTER ; SELECT LETTER FROM (SELECT LETTER COLLATE utf8_swedish_ci AS L FROM LETTERS) AS LETTER GROUP BY LETTER
Example 17.16:
SELECT LETTER FROM LETTERS ORDER BY LETTER
Example 17.17:
SELECT COERCIBILITY('Rick' COLLATE latin1_general_ci) AS C0, COERCIBILITY(TEAMNO) AS C2, COERCIBILITY(USER()) AS C3, COERCIBILITY('Rick') AS C4, COERCIBILITY(NULL) AS C5 FROM TEAMS WHERE TEAMNO = 1
Example 17.18:
SELECT @@COLLATION_DATABASE
Example 17.19:
SHOW VARIABLES LIKE 'CHARACTER_SET%'
Answer 17.2:
SELECT COLLATION_NAME, COUNT(*) FROM INFORMATION_SCHEMA.COLLATIONS GROUP BY COLLATION_NAME
Answer 17.4:
SELECT CHARSET((SELECT MAX(TOWN) FROM PLAYERS)), COLLATION((SELECT MAX(TOWN) FROM PLAYERS))
Answer 17.5:
SELECT TOWN FROM PLAYERS ORDER BY TOWN COLLATE latin1_danish_ci
Example 18.1:
DROP TABLE PLAYERS
Example 18.2:
RENAME TABLE PLAYERS TO TENNIS_PLAYERS
Example 18.3:
ALTER TABLE PLAYERS TO TENNIS_PLAYERS
Example 18.4:
ALTER TABLE PLAYERS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
Example 18.5:
ALTER TABLE TEAMS ADD TYPE CHAR(1)
Example 18.6:
ALTER TABLE TEAMS ADD TYPE CHAR(1) AFTER TEAMNO
Example 18.7:
ALTER TABLE TEAMS ADD (CATEGORY VARCHAR(20) NOT NULL, IMAGO INTEGER DEFAULT 10)
Example 18.8:
ALTER TABLE TEAMS DROP TYPE
Example 18.9:
ALTER TABLE PLAYERS CHANGE BIRTH_DATE DATE_OF_BIRTH DATE
Example 18.10:
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(40) NOT NULL
Example 18.11:
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(5) NOT NULL
Example 18.12:
ALTER TABLE PLAYERS CHANGE PLAYERNO PLAYERNO TINYINT
Example 18.13:
ALTER TABLE PLAYERS CHANGE TOWN TOWN VARCHAR(5) NOT NULL AFTER PLAYERNO
Example 18.14:
ALTER TABLE PLAYERS MODIFY TOWN VARCHAR(5) NOT NULL AFTER PLAYERNO
Example 18.15:
ALTER TABLE COMMITTEE_MEMBERS ALTER POSITION SET DEFAULT 'Member'
Example 18.16:
ALTER TABLE COMMITTEE_MEMBERS ALTER POSITION DROP DEFAULT
Example 18.17:
CREATE TABLE T1 (A INTEGER NOT NULL PRIMARY KEY, B INTEGER NOT NULL) ; CREATE TABLE T2 (A INTEGER NOT NULL PRIMARY KEY, B INTEGER NOT NULL CONSTRAINT C1 CHECK (B > 0), CONSTRAINT FK1 FOREIGN KEY (A) REFERENCES T1 (A)) ; ALTER TABLE T1 ADD CONSTRAINT FK2 FOREIGN KEY (A) REFERENCES T2 (A)
Example 18.18:
ALTER TABLE PLAYERS DROP PRIMARY KEY
Example 18.19:
ALTER TABLE T1 DROP CONSTRAINT FK2
Example 18.20:
ALTER TABLE T2 DROP CONSTRAINT C1
Answer 18.1:
ALTER TABLE COMMITTEE_MEMBERS CHANGE POSITION COMMITTEE_POSITION CHAR(20)
Answer 18.2:
ALTER TABLE COMMITTEE_MEMBERS MODIFY COMMITTEE_POSITION CHAR(30)
Answer 18.3:
ALTER TABLE PLAYERS ALTER TOWN SET DEFAULT 'Stratford'
Example 19.1:
SELECT PLAYERNO, 0 FROM CHILDREN WHERE CNAME1 IS NULL AND CNAME2 IS NULL AND CNAME3 IS NULL UNION SELECT PLAYERNO, 1 FROM CHILDREN WHERE CNAME1 IS NOT NULL AND CNAME2 IS NULL AND CNAME3 IS NULL UNION SELECT PLAYERNO, 2 FROM CHILDREN WHERE CNAME1 IS NOT NULL AND CNAME2 IS NOT NULL AND CNAME3 IS NULL UNION SELECT PLAYERNO, 3 FROM CHILDREN WHERE CNAME1 IS NOT NULL AND CNAME2 IS NOT NULL AND CNAME3 IS NOT NULL UNION SELECT PLAYERNO, 0 FROM PLAYERS WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM CHILDREN)
Example 19.2:
SELECT * FROM CHILDREN WHERE PLAYERNO = 6 ; UPDATE CHILDREN SET CNAME2 = 'Diane' WHERE PLAYERNO = 6 ; SELECT PLAYERNO, COUNT(*) FROM PLAYERS LEFT OUTER JOIN CHILDREN ON PLAYERS.PLAYERNO = CHILDREN.PLAYERNO GROUP BY PLAYERNO ; UPDATE CHILDREN SET CNAME = 'Diane' WHERE PLAYERNO = 6 AND CNAME = 'Diana'
Example 19.3:
SELECT ADDRESS FROM PLAYERS WHERE PLAYERNO = 44
Example 19.4:
SELECT NAME, AMOUNT FROM PENALTIES AS PEN, PLAYERS AS P WHERE PEN.PLAYERNO = P.PLAYERNO
Example 19.5:
SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL FROM PLAYERNO LEFT OUTER JOIN PENALTIES ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO GROUP BY PLAYERNO ORDER BY 1
Answer 19.3:
SELECT MATCHNO, TEAMNO, DIVISION FROM MATCHES
Answer 19.4:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE WON > AVERAGE
Answer 19.5:
CREATE TABLE PERFORMANCE (NAME_SHOW CHAR(20) NOT NULL, LOCATION CHAR(20) NOT NULL, PERF_DATE DATE NOT NULL, PRIMARY KEY (NAME_SHOW, LOCATION, PERF_DATE)) ; CREATE TABLE SHOWS (NAME_SHOW CHAR(20) NOT NULL, ARTIST CHAR(20) NOT NULL, PRIMARY KEY (NAME_SHOW)) ; CREATE TABLE SETTING (NAME_SHOW CHAR(20) NOT NULL, MUSICIAN CHAR(20) NOT NULL, INSTRUMENT CHAR(20) NOT NULL, PRIMARY KEY (NAME_SHOW, MUSICIAN, INSTRUMENT))
Example 20.4:
SELECT * FROM PLAYERS WHERE PLAYERNO = 44
Example 20.5:
SELECT PLAYERNO, TOWN FROM PLAYERS WHERE PLAYERNO < 10 AND TOWN = 'Stratford' ORDER BY PLAYERNO
Example 20.6:
SELECT NAME, INITIALS FROM PLAYERS WHERE TOWN = (SELECT TOWN FROM PLAYERS WHERE PLAYERNO = 44)
Example 20.7:
CREATE INDEX PLAY_PC ON PLAYERS (POSTCODE ASC)
Example 20.8:
CREATE INDEX MAT_WL ON MATCHES (WON, LOST)
Example 20.9:
CREATE UNIQUE INDEX NAMEINIT ON PLAYERS (NAME, INITIALS)
Example 20.10:
CREATE UNIQUE CLUSTERED INDEX PLAYERS_CLUSTERED ON PLAYERS (PLAYERNO)
Example 20.11:
ALTER TABLE TEAMS ADD INDEX TEAMS_DIVISION USING BTREE (DIVISION)
Example 20.12:
DROP INDEX PLAY_PC ; DROP INDEX MAT_WL ; DROP INDEX NAMEINIT
Example 20.13:
CREATE TABLE T1 (COL1 INTEGER NOT NULL, COL2 DATE NOT NULL UNIQUE, COL3 INTEGER NOT NULL, COL4 INTEGER NOT NULL, PRIMARY KEY (COL1, COL4), UNIQUE (COL3, COL4), UNIQUE (COL3, COL1) ) ; CREATE UNIQUE INDEX "PRIMARY" USING BTREE ON T1 (COL1, COL4) ; CREATE UNIQUE INDEX COL2 USING BTREE ON T1 (COL2) ; CREATE UNIQUE INDEX COL3 USING BTREE ON T1 (COL3, COL4) ; CREATE UNIQUE INDEX COL3_2 USING BTREE ON T1 (COL3, COL1)
Example 20.14:
CREATE TABLE PLAYERS_XXL (PLAYERNO INTEGER NOT NULL PRIMARY KEY, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE, SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET VARCHAR(30) NOT NULL, HOUSENO CHAR(4), POSTCODE CHAR(6), TOWN VARCHAR(30) NOT NULL, PHONENO CHAR(13), LEAGUENO CHAR(4))
Example 20.15:
CREATE PROCEDURE FILL_PLAYERS_XXL (IN NUMBER_PLAYERS INTEGER) BEGIN DECLARE COUNTER INTEGER; TRUNCATE TABLE PLAYERS_XXL; COMMIT WORK; SET COUNTER = 1; WHILE COUNTER <= NUMBER_PLAYERS DO INSERT INTO PLAYERS_XXL VALUES( COUNTER, CONCAT('name',CAST(COUNTER AS CHAR(10))), CASE MOD(COUNTER,2) WHEN 0 THEN 'vl1' ELSE 'vl2' END, DATE('1960-01-01') + INTERVAL (MOD(COUNTER,300)) MONTH, CASE MOD(COUNTER,20) WHEN 0 THEN 'F' ELSE 'M' END, 1980 + MOD(COUNTER,20), CONCAT('street',CAST(COUNTER /10 AS UNSIGNED INTEGER)), CAST(CAST(COUNTER /10 AS UNSIGNED INTEGER)+1 AS CHAR(4)), CONCAT('p',MOD(COUNTER,50)), CONCAT('town',MOD(COUNTER,10)), '070-6868689', CASE MOD(COUNTER,3) WHEN 0 THEN '0' ELSE COUNTER END); IF MOD(COUNTER,1000) = 0 THEN COMMIT WORK; END IF; SET COUNTER = COUNTER + 1; END WHILE; COMMIT WORK; UPDATE PLAYERS_XXL SET LEAGUENO = NULL WHERE LEAGUENO = '0'; COMMIT WORK; END
Example 20.16:
CALL FILL_PLAYERS_XXL(100000)
Example 20.17:
CREATE INDEX PLAYERS_XXL_INITIALS ON PLAYERS_XXL(INITIALS) ; CREATE INDEX PLAYERS_XXL_POSTCODE ON PLAYERS_XXL(POSTCODE) ; CREATE INDEX PLAYERS_XXL_STREET ON PLAYERS_XXL(STREET)
Example 20.18:
CREATE INDEX PLAY_MAT ON PLAYERS(PLAYERNO), MATCHES(PLAYERNO)
Example 20.19:
CREATE INDEX MAT_HALFBALANCE ON MATCHES((WON ?LOST)/2) ; SELECT * FROM MATCHES WHERE (WON ?LOST)/2 > 1
Example 20.20:
CREATE INDEX PEN_PAYMENT_DATE ON PENALTIES WHERE PAYMENT_DATE > '1996-12-31'
Example 20.21:
CREATE HASH INDEX PLAYERNO_HASH ON PLAYERS (PLAYERNO) WITH PAGES=100
Example 20.22:
CREATE BITMAP INDEX PLAYERS_SEX ON PLAYERS(SEX)
Example 20.23:
SELECT TABLE_CREATOR, TABLE_NAME, COUNT(*) FROM INDEXES GROUP BY TABLE_CREATOR, TABLE_NAME HAVING COUNT(*) > 1
Example 20.24:
SELECT TABLE_CREATOR, TABLE_NAME FROM TABLES AS TAB WHERE NOT EXISTS (SELECT * FROM INDEXES AS IDX WHERE TAB.TABLE_CREATOR = IDX.TABLE_CREATOR AND TAB.TABLE_NAME = TAB.TABLE_NAME AND IDX.UNIQUE_ID = 'YES')
Example 21.1:
CREATE VIEW TOWNS AS SELECT DISTINCT TOWN FROM PLAYERS ; SELECT * FROM TOWNS
Example 21.2:
CREATE VIEW CPLAYERS AS SELECT PLAYERNO, LEAGUENO FROM PLAYERS WHERE LEAGUENO IS NOT NULL ; SELECT * FROM CPLAYERS
Example 21.3:
SELECT * FROM CPLAYERS WHERE PLAYERNO BETWEEN 6 AND 44 ; SELECT PLAYERNO, LEAGUENO FROM PLAYERS WHERE LEAGUENO IS NOT NULL AND PLAYERNO BETWEEN 6 AND 44
Example 21.4:
DELETE FROM CPLAYERS WHERE LEAGUENO = '7060'
Example 21.5:
CREATE VIEW SEVERAL AS SELECT * FROM CPLAYERS WHERE PLAYERNO BETWEEN 6 AND 27 ; SELECT * FROM SEVERAL
Example 21.6:
CREATE VIEW DIGITS AS SELECT '0' DIGIT UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION SELECT '7' UNION SELECT '8' UNION SELECT '9' ; SELECT * FROM DIGITS
Example 21.7:
CREATE VIEW STRATFORDERS (PLAYERNO, NAME, INIT, BORN) AS SELECT PLAYERNO, NAME, INITIALS, BIRTH_DATE FROM PLAYERS WHERE TOWN = 'Stratford' ; SELECT * FROM STRATFORDERS WHERE PLAYERNO > 90
Example 21.8:
CREATE VIEW RESIDENTS (TOWN, NUMBER) AS SELECT TOWN, COUNT(*) FROM PLAYERS GROUP BY TOWN
Example 21.9:
CREATE VIEW VETERANS AS SELECT * FROM PLAYERS WHERE BIRTH_DATE < '1960-01-01' ; UPDATE VETERANS SET BIRTH_DATE = '1970-09-01' WHERE PLAYERNO = 2 ; CREATE VIEW VETERANS AS SELECT * FROM PLAYERS WHERE BIRTH_DATE < '1960-01-01' WITH CHECK OPTION
Example 21.10:
CREATE VIEW INGLEWOOD_VETERANS AS SELECT * FROM VETERANS WHERE TOWN = 'Inglewood' WITH CASCADED CHECK OPTION
Example 21.11:
DROP VIEW CPLAYERS
Example 21.12:
SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME = 'STOCK' AND TABLE_CREATOR = 'TENNIS' UNION SELECT VIEW_NAME FROM VIEWS WHERE VIEW_NAME = 'STOCK' AND VIEW_CREATOR = 'TENNIS'
Example 21.13:
CREATE VIEW COST_RAISERS AS SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES)
SELECT PLAYERNO FROM COST_RAISERS WHERE TOWN = 'Stratford' ; SELECT PLAYERNO FROM (SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES)) AS VIEWFORMULA WHERE TOWN = 'Stratford'
Example 21.14:
DELETE FROM STRATFORDERS WHERE BORN > '1965-12-31' ; DELETE FROM PLAYERS WHERE BIRTH_DATE > '1965-12-31' AND TOWN = 'Stratford' ; SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) ; SELECT PLAYERNO FROM <intermediate result> WHERE TOWN = 'Stratford'
Example 21.15:
SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) AND TOWN = 'Stratford' ; SELECT TOWN, COUNT(*) FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) GROUP BY TOWN ; CREATE VIEW PPLAYERS AS SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES) ; SELECT * FROM PPLAYERS WHERE TOWN = 'Stratford' ; SELECT TOWN, COUNT(*) FROM PPLAYERS GROUP BY TOWN
Example 21.17:
SELECT DISTINCT NAME, INITIALS, DIVISION FROM PLAYERS AS P, MATCHES AS M, TEAMS AS T WHERE P.PLAYERNO = M.PLAYERNO AND M.TEAMNO = T.TEAMNO ; CREATE VIEW TEAMS (TEAMNO, PLAYERNO, DIVISION) AS SELECT DISTINCT TEAMNO, CAPTAIN, DIVISION FROM RESULT ; CREATE VIEW MATCHES AS SELECT MATCHNO, TEAMNO, PLAYERNO, WON, LOST FROM RESULT
Example 21.18:
CREATE VIEW PLAYERSS AS SELECT * FROM PLAYERS WHERE SEX IN ('M', 'F') WITH CHECK OPTION
Answer 21.1:
CREATE VIEW NUMBERPLS (TEAMNO, NUMBER) AS SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO
Answer 21.2:
CREATE VIEW WINNERS AS SELECT PLAYERNO, NAME FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM MATCHES WHERE WON > LOST)
Answer 21.3:
CREATE VIEW TOTALS (PLAYERNO, SUM_PENALTIES) AS SELECT PLAYERNO, SUM(AMOUNT) FROM PENALTIES GROUP BY PLAYERNO
Answer 21.5.1:
SELECT YEAR(BORN) - 1900, COUNT(*) FROM (SELECT PLAYERNO, NAME, INITIALS, BIRTH_DATE AS BORN FROM PLAYERS WHERE TOWN = 'Stratford') AS STRATFORDERS GROUP BY BORN
Answer 21.5.2:
SELECT EXPENSIVE.PLAYERNO FROM (SELECT * FROM PLAYERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES)) AS EXPENSIVE, (SELECT PLAYERNO, NAME, INITIALS, BIRTH_DATE AS BORN FROM PLAYERS WHERE TOWN = 'Stratford') AS STRATFORDERS WHERE EXPENSIVE.PLAYERNO = STRATFORDERS.PLAYERNO
Answer 21.5.3:
UPDATE PLAYERS SET BIRTH_DATE = '1950-04-04' WHERE PLAYERNO = 7
Example 22.1:
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Example 22.2:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TENNIS' ORDER BY 1
Example 22.3:
CREATE DATABASE TENNIS2 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci
Example 22.4:
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Example 22.5:
ALTER DATABASE TENNIS2 DEFAULT CHARACTER SET sjis DEFAULT COLLATE sjis_japanese_ci
Example 22.6:
ALTER DATABASE TENNIS CHARACTER SET hp8 ; CREATE TABLE CHARSETHP8 (C1 CHAR(10) NOT NULL, C2 VARCHAR(10)) ; SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CHARSETHP8'
Example 22.7:
ALTER DATABASE TENNIS COLLATE hp8_bin
Example 22.8:
DROP DATABASE TENNIS2
Example 23.1:
CREATE USER CHRIS IDENTIFIED BY CHRISSEC ; CREATE USER PAUL IDENTIFIED BY LUAP ; CREATE USER 'CHRIS'@'%' IDENTIFIED BY 'CHRISSEC' ; CREATE USER 'SAM'@'TEST' IDENTIFIED BY 'CHRISSEC'
Example 23.2:
ALTER USER JIM IDENTIFIED BY JIM1
Example 23.3:
DROP USER JIM
Example 23.4:
GRANT SELECT ON PLAYERS TO JAMIE
Example 23.5:
GRANT INSERT, UPDATE ON TEAMS TO JAMIE, PIET
Example 23.6:
GRANT SELECT, INSERT ON PENALTIES TO PUBLIC
Example 23.7:
GRANT UPDATE (PLAYERNO, DIVISION) ON TEAMS TO PETE
Example 23.8:
GRANT SELECT ON TENNIS.* TO PETE
Example 23.9:
GRANT CREATE, ALTER, DROP, CREATE VIEW ON TENNIS.* TO JIM
Example 23.10:
GRANT SELECT ON INFORMATION_SCHEMA.* TO PETE
Example 23.11:
GRANT SELECT, INSERT ON * TO ALYSSA
Example 23.12:
GRANT CREATE, ALTER, DROP ON *.* TO MAX
Example 23.13:
GRANT CREATE USER ON *.* TO ALYSSA ; GRANT ALL PRIVILEGES ON *.* TO ROOT
Example 23.14:
GRANT REFERENCES ON TEAMS TO JOHN WITH GRANT OPTION ; GRANT REFERENCES ON TEAMS TO PETE
Example 23.15:
CREATE ROLE SALES ; GRANT SELECT, INSERT ON PENALTIES TO SALES ; GRANT SALES TO ILENE, KELLY, JIM, MARC
Example 23.16:
DROP ROLE SALES
Example 23.17:
SELECT GRANTEE FROM USER_AUTHS WHERE PRIVILEGE = 'SELECT' UNION SELECT GRANTEE FROM DATABASE_AUTHS WHERE DATABASENAME = 'TENNIS' AND PRIVILEGE = 'SELECT' UNION SELECT GRANTEE FROM TABLE_AUTHS WHERE TABLE_CREATOR = 'TENNIS' AND PRIVILEGE = 'SELECT'
Example 23.18:
REVOKE SELECT ON PLAYERS FROM JIM
Example 23.19:
REVOKE REFERENCES ON TEAMS FROM JIM
Example 23.20:
REVOKE SALES FROM ILENE
Example 23.21:
REVOKE SELECT ON PENALTIES FROM SALES
Example 23.22:
CREATE USER DIANE IDENTIFIED BY 'SECRET' ; CREATE VIEW NAME_ADDRESS AS SELECT NAME, INITIALS, STREET, HOUSENO, TOWN FROM PLAYERS WHERE LEAGUENO IS NULL ; GRANT SELECT ON NAME_ADDRESS TO DIANE
Example 23.23:
CREATE VIEW RESIDENTS (TOWN, NUMBER_OF) AS SELECT TOWN, COUNT(*) FROM PLAYERS GROUP BY TOWN ; GRANT SELECT ON RESIDENTS TO GERARD
Answer 23.1:
CREATE USER RONALDO IDENTIFIED BY 'NIKE'
Answer 23.2:
DROP USER RONALDO
Answer 23.3:
GRANT SELECT, INSERT ON PLAYERS TO RONALDO
Answer 23.4:
GRANT ALL ON COMMITTEE_MEMBERS TO PUBLIC
Answer 23.5:
GRANT UPDATE(STREET, HOUSENO, POSTCODE, TOWN) ON PLAYERS TO RONALDO
Answer 23.6:
GRANT INSERT ON TENNIS.* TO JACO, DIANE
Answer 23.7:
CREATE USER JOE IDENTIFIED BY 'JAZZ' ; CREATE USER JACO IDENTIFIED BY 'JAZZ' ; CREATE USER CHRIS IDENTIFIED BY 'JAZZ' ; CREATE ROLE ADMIN ; GRANT ALL ON COMMITTEE_MEMBERS TO ADMIN
Example 24.1:
CREATE TABLE COUNTRIES (COUNTRYNO INTEGER NOT NULL PRIMARY KEY, COUNTRYNAME VARCHAR(30) NOT NULL) ; CREATE SEQUENCE COUNTRYNUMBERS ; INSERT INTO COUNTRIES (COUNTRYNO, COUNTRYNAME) VALUES (NEXT VALUE FOR COUNTRYNUMBERS, 'China') ; UPDATE COUNTRIES SET COUNTRYNO = NEXT VALUE FOR COUNTRYNUMBERS WHERE COUNTRYNO = 1
Example 24.2:
SELECT PLAYERNO, NEXT VALUE FOR COUNTRYNUMBERS FROM PLAYERS
Example 24.3:
CREATE SEQUENCE HUNDRED START WITH 100 ; SELECT NEXT VALUE FOR HUNDRED AS NUMBERS FROM MATCHES WHERE MATCHES <= 5
Example 24.4:
CREATE SEQUENCE BIG_STEPS INCREMENT BY 100 ; SELECT NEXT VALUE FOR BIG_STEPS AS NUMBERS FROM MATCHES WHERE MATCHNO <= 5
Example 24.5:
CREATE SEQUENCE BACKWARDS INCREMENT BY -10 ; SELECT NEXT VALUE FOR BACKWARDS AS NUMBERS FROM MATCHES WHERE MATCHNO <= 5
Example 24.6:
CREATE SEQUENCE BIG_STEPS INCREMENT BY 100 ; SELECT NEXT VALUE FOR BIG_STEPS AS NUMBERS FROM MATCHES WHERE MATCHNO <= 5
Example 24.7:
CREATE SEQUENCE MIN98 START WITH 100 INCREMENT BY ? MINVALUE 98 ; SELECT NEXT VALUE FOR MIN98 AS NUMBERS FROM MATCHES WHERE MATCHNO <= 5
Example 24.8:
CREATE SEQUENCE CYCLE3 START WITH 1 INCREMENT BY 1 MAXVALUE 3 CYCLE ; SELECT NEXT VALUE FOR CYCLE3 AS NUMBERS FROM MATCHES
Example 24.9:
CREATE SEQUENCE STANDARD MINVALUE 1 MAXVALUE 2147483647 START WITH 1 INCREMENT BY 1 NOCYCLE CACHE 20 ORDER
Example 24.10:
CREATE SEQUENCE TEAMNUMBERS ; INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (NEXT VALUE FOR TEAMNUMBERS, 6, 'first') ; UPDATE MATCHES SET TEAMNO = PREVIOUS VALUE FOR TEAMNUMBERS WHERE MATCHNO = 10
Example 24.11:
ALTER SEQUENCE COUNTRYNUMBERS RESTART
Example 24.12:
ALTER SEQUENCE COUNTRYNUMBERS RESTART WITH 100
Example 24.13:
ALTER SEQUENCE FIVES MAXVALUE 800
Example 24.14:
DROP SEQUENCE DECREASE
Example 24.15:
GRANT ALTER, USAGE ON SEQUENCE COUNTRYNUMBERS TO BEN
Answer 24.1.1:
CREATE SEQUENCE EVEN_NUMBERS START WITH 2 INCREMENT BY 2
Answer 24.1.2:
CREATE SEQUENCE TENS START WITH 80 INCREMENT BY ?0
Answer 24.1.3:
CREATE SEQUENCE FROM_1_TO_4 START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 4 NOCACHE CYCLE
Answer 24.1.4:
CREATE SEQUENCE BIT START WITH 0 MINVALUE 0 MAXVALUE 1 NOCACHE CYCLE
Exaple 25.1:
CREATE TABLE BRADLEY.TEST (COLUMN1 INTEGER)
Example 25.2:
CREATE INDEX BRADLEY.INDEXA ON TEST (COLUMN1)
Example 25.3:
CREATE SCHEMA TENNIS_SCHEMA
Example 25.4:
CREATE VIEW TENNIS_SCHEMA.SEVERAL_MATCHES AS SELECT * FROM MATCHES WHERE MATCHENO < 5
Example 25.5:
CREATE SCHEMA TWO_TABLES CREATE TABLE TABLE1 (COLUMN1 INTEGER) CREATE TABLE TABLE2 (COLUMN1 INTEGER) CREATE INDEX INDEX1 ON TABLE1(COLUMN1)
Example 27.1:
DELETE FROM PENALTIES WHERE PLAYERNO = 44 ; SELECT * FROM PENALTIES
Example 27.1:
DELETE FROM PENALTIES WHERE PLAYERNO = 44 ; SELECT * FROM PENALTIES
Example 27.11:
LOCK TABLE PLAYERS IN SHARE MODE ; LOCK TABLE PLAYERS READ
Example 29.1:
SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO = 6 OR PLAYERNO = 83 OR PLAYERNO = 44 ; SELECT NAME, INITIALS FROM PLAYERS WHERE PLAYERNO IN (6, 83, 44)
Example 29.2:
SELECT * FROM PLAYERS WHERE JOINED = 1980 OR TOWN = 'Stratford' ; SELECT * FROM PLAYERS WHERE JOINED = 1980 UNION SELECT * FROM PLAYERS WHERE TOWN = 'Stratford'
Example 29.3:
UPDATE PENALTIES SET AMOUNT = 150 WHERE AMOUNT = 100 OR PAYMENT_DATE = '1980-12-01' ; UPDATE PENALTIES SET AMOUNT = 150 WHERE AMOUNT = 100 ; UPDATE PENALTIES SET AMOUNT = 150 WHERE PAYMENT_DATE = '1980-12-01' ; SELECT NAME FROM PLAYERS WHERE JOINED = 1980 OR TOWN = 'Stratford'
Example 29.4:
SELECT MATCHNO, WON - LOST FROM MATCHES WHERE WON >= LOST UNION SELECT MATCHNO, LOST - WON FROM MATCHES WHERE WON < LOST ; SELECT MATCHNO, ABS(WON - LOST) FROM MATCHES
Example 29.5:
SELECT * FROM PLAYERS WHERE NOT (JOINED > 1980)
Example 29.6:
SELECT * FROM PLAYERS WHERE NOT (SEX = 'M') ; SELECT * FROM PLAYERS WHERE SEX = 'F'
Example 29.7:
SELECT * FROM PLAYERS WHERE JOINED + 10 = 1990 ; SELECT * FROM PLAYERS WHERE JOINED = 1980
Example 29.8:
SELECT PLAYERNO FROM PLAYERS WHERE BIRTH_DATE >= '1962-01-01' AND BIRTH_DATE <= '1965-12-31' ; SELECT PLAYERNO FROM PLAYERS WHERE BIRTH_DATE BETWEEN '1962-01-01' AND '1965-12-31'
Example 29.9:
SELECT * FROM PLAYERS WHERE NAME LIKE '%n'
Example 29.10:
SELECT PAYMENTNO, NAME FROM PENALTIES AS PEN, PLAYERS AS P WHERE PEN.PLAYERNO = P.PLAYERNO AND PEN.PLAYERNO = 44 ; SELECT PAYMENTNO, NAME FROM PENALTIES AS PEN, PLAYERS AS P WHERE PEN.PLAYERNO = P.PLAYERNO AND PEN.PLAYERNO = 44 AND P.PLAYERNO = 44
Example 29.11:
SELECT PLAYERNO, COUNT(*) FROM PENALTIES GROUP BY PLAYERNO HAVING PLAYERNO >= 40 ; SELECT PLAYERNO, COUNT(*) FROM PENALTIES WHERE PLAYERNO >= 40 GROUP BY PLAYERNO
Example 29.12:
SELECT PLAYERNO, NAME FROM PLAYERS WHERE EXISTS (SELECT '1' FROM PENALTIES WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO)
Example 29.13:
SELECT DISTINCT MATCHNO, NAME FROM MATCHES, PLAYERS WHERE MATCHES.PLAYERNO = PLAYERS.PLAYERNO
Example 29.14:
SELECT NAME, INITIALS FROM PLAYERS WHERE TOWN = 'Stratford' UNION ALL SELECT NAME, INITIALS FROM PLAYERS WHERE TOWN = 'Douglas'
Example 29.15:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT FROM PLAYERS, PENALTIES WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO UNION SELECT PLAYERNO, NAME, NULL FROM PLAYERS WHERE PLAYERNO NOT IN (SELECT PLAYERNO FROM PENALTIES) ORDER BY 1
SELECT PLAYERNO, NAME, AMOUNT FROM PLAYERS LEFT OUTER JOIN PENALTIES USING (PLAYERNO) ORDER BY 1
Example 29.16:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE <= ALL (SELECT BIRTH_DATE FROM PLAYERS) ; SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE = (SELECT MIN(BIRTH_DATE) FROM PLAYERS)
Example 29.17:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE > ANY (SELECT BIRTH_DATE FROM PLAYERS) ; SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS WHERE BIRTH_DATE > (SELECT MIN(BIRTH_DATE) FROM PLAYERS)
Answer 29.1.1:
SELECT * FROM PLAYERS WHERE TOWN = 'Stratford' AND STREET = 'Edgecombe Way' UNION SELECT * FROM PLAYERS WHERE BIRTH_DATE < '1960-01-01'
Answer 29.1.2:
SELECT * FROM PLAYERS
Answer 29.1.3:
SELECT DISTINCT TEAMS.* FROM TEAMS, MATCHES WHERE TEAMS.TEAMNO = MATCHES.TEAMNO AND WON * LOST = WON * 4
Answer 29.1.4:
SELECT DISTINCT T.TEAMNO FROM TEAMS AS T, MATCHES AS M WHERE T.TEAMNO = M.TEAMNO AND DIVISION = 'second'
Answer 29.1.5:
SELECT PLAYERNO FROM PLAYERS
Answer 29.1.6:
SELECT PLAYERNO, CASE SEX WHEN 'F' THEN 'Female' ELSE 'Male' END FROM PLAYERS
Answer 29.1.7:
SELECT BIRTH_DATE, COUNT(*) FROM PLAYERS WHERE BIRTH_DATE >= '1970-01-01' GROUP BY BIRTH_DATE
Answer 29.9.1:
SELECT PLAYERNO, NAME, BIRTH_DATE FROM PLAYERS_XXL WHERE BIRTH_DATE = (SELECT MIN(BIRTH_DATE) FROM PLAYERS_XXL)
Answer 29.9.2:
SELECT * FROM PLAYERS_XXL
Answer 29.9.3:
SELECT PLAYERNO, CASE SEX WHEN 'F' THEN 'Female' ELSE 'Male' END FROM PLAYERS_XXL
Answer 29.9.4:
SELECT POSTCODE, COUNT(*) FROM PLAYERS_XXL WHERE POSTCODE >= 'Y' GROUP BY POSTCODE
Answer 29.9.5:
SELECT * FROM PLAYERS_XXL WHERE PLAYERNO <= 10
Example 30.1:
CREATE PROCEDURE DELETE_MATCHES (IN P_PLAYERNO INTEGER) BEGIN DELETE FROM MATCHES WHERE PLAYERNO = P_PLAYERNO; END
Example 30.2:
CALL DELETE_MATCHES (8)
Example 30.5:
CREATE PROCEDURE TEST (OUT NUMBER1 INTEGER) BEGIN DECLARE NUMBER2 INTEGER DEFAULT 100; SET NUMBER1 = NUMBER2; END # CALL TEST (@NUMBER) # SELECT @NUMBER
Example 30.6:
CREATE PROCEDURE TEST (OUT NUMBER1 INTEGER) BEGIN DECLARE NUMBER2 INTEGER DEFAULT (SELECT COUNT(*) FROM PLAYERS); SET NUMBER1 = NUMBER2; END
Example 30.7:
CREATE PROCEDURE DIFFERENCE (IN P1 INTEGER, IN P2 INTEGER, OUT P3 INTEGER) BEGIN IF P1 > P2 THEN SET P3 = 1; ELSEIF P1 = P2 THEN SET P3 = 2; ELSE SET P3 = 3; END IF; END # CREATE PROCEDURE FIBONNACI (INOUT NUMBER1 INTEGER, INOUT NUMBER2 INTEGER, INOUT NUMBER3 INTEGER) BEGIN SET NUMBER3 = NUMBER1 + NUMBER2; IF NUMBER3 > 10000 THEN SET NUMBER3 = NUMBER3 - 10000; END IF; SET NUMBER1 = NUMBER2; SET NUMBER2 = NUMBER3; END # SET @A=16, @B=27 # CALL FIBONNACI(@A,@B,@C) # SELECT @C # CALL FIBONNACI(@A,@B,@C) # SELECT @C # CALL FIBONNACI(@A,@B,@C) # SELECT @C
Example 30.9:
CREATE PROCEDURE LARGEST (OUT T CHAR(10)) BEGIN IF (SELECT COUNT(*) FROM PLAYERS) > (SELECT COUNT(*) FROM PENALTIES) THEN SET T = 'PLAYERS'; ELSEIF (SELECT COUNT(*) FROM PLAYERS) = (SELECT COUNT(*) FROM PENALTIES) THEN SET T = 'EQUAL'; ELSE SET T = 'PENALTIES'; END IF; END
Example 30.10:
CREATE PROCEDURE AGE (IN START_DATE DATE, IN END_DATE DATE, OUT YEARS INTEGER, OUT MONTHS INTEGER, OUT DAYS INTEGER) BEGIN DECLARE NEXT_DATE, PREVIOUS_DATE DATE;
SET YEARS = 0; SET PREVIOUS_DATE = START_DATE; SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR; WHILE NEXT_DATE < END_DATE DO SET YEARS = YEARS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR; END WHILE;
SET MONTHS = 0; SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH; WHILE NEXT_DATE < END_DATE DO SET MONTHS = MONTHS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH; END WHILE;
SET DAYS = 0; SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY; WHILE NEXT_DATE <= END_DATE DO SET DAYS = DAYS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY; END WHILE; END # SET @START = '1991-01-12' # SET @END = '1999-07-09' # CALL AGE (@START, @END, @YEAR, @MONTH, @DAY) # SELECT @START, @END, @YEAR, @MONTH, @DAY
Example 30.11:
CREATE PROCEDURE SMALL_EXIT (OUT P1 INTEGER, OUT P2 INTEGER) BEGIN SET P1 = 1; SET P2 = 1; BLOCK1 : BEGIN LEAVE BLOCK1; SET P2 = 3; END; SET P1 = 4; END
Example 30.12:
CREATE PROCEDURE WAIT (IN WAIT_SECONDS INTEGER) BEGIN DECLARE END_TIME INTEGER DEFAULT NOW() + INTERVAL WAIT_SECONDS SECOND; WAIT_LOOP: LOOP IF NOW() > END_TIME THEN LEAVE WAIT_LOOP; END IF; END LOOP WAIT_LOOP; END
Example 30.13:
CREATE PROCEDURE AGAIN (OUT RESULT INTEGER) BEGIN DECLARE COUNTER INTEGER DEFAULT 1; SET RESULT = 0; LOOP1: WHILE COUNTER <= 1000 DO SET COUNTER = COUNTER + 1; IF COUNTER > 100 THEN LEAVE LOOP1; ELSE ITERATE LOOP1; END IF; SET RESULT = COUNTER * 10; END WHILE LOOP1; END
Example 30.14:
CALL WAIT ((SELECT COUNT(*) FROM PENALTIES)) # CREATE TABLE PLAYERS_WITH_PARENTS (PLAYERNO INTEGER NOT NULL PRIMARY KEY, FATHER_PLAYERNO INTEGER, MOTHER_PLAYERNO INTEGER) # ALTER TABLE PLAYERS_WITH_PARENTS ADD FOREIGN KEY (FATHER_PLAYERNO) REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO) # ALTER TABLE PLAYERS_WITH_PARENTS ADD FOREIGN KEY (MOTHER_PLAYERNO) REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO) # INSERT INTO PLAYERS_WITH_PARENTS VALUES (9,NULL,NULL), (8,NULL,NULL), (7,NULL,NULL), (6,NULL,NULL), (5,NULL,NULL), (4,8,9), (3,6,7), (2,4,5), (1,2,3)
Example 30.15:
CREATE PROCEDURE TOTAL_NUMBER_OF_PARENTS (IN P_PLAYERNO INTEGER, INOUT NUMBER INTEGER) BEGIN DECLARE V_FATHER, V_MOTHER INTEGER; SET V_FATHER = (SELECT FATHER_PLAYERNO FROM PLAYERS_WITH_PARENTS WHERE PLAYERNO = P_PLAYERNO); SET V_MOTHER = (SELECT MOTHER_PLAYERNO FROM PLAYERS_WITH_PARENTS WHERE PLAYERNO = P_PLAYERNO);
IF V_FATHER IS NOT NULL THEN CALL TOTAL_NUMBER_OF_PARENTS (V_FATHER, NUMBER); SET NUMBER = NUMBER + 1; END IF;
IF V_MOTHER IS NOT NULL THEN CALL TOTAL_NUMBER_OF_PARENTS (V_MOTHER, NUMBER); SET NUMBER = NUMBER + 1; END IF; END # SET @NUMBER = 0 # CALL TOTAL_NUMBER_OF_PARENTS (1, @NUMBER) # SELECT @NUMBER
Example 30.16:
CREATE PROCEDURE TOTAL_PENALTIES_PLAYER (IN P_PLAYERNO INTEGER, OUT TOTAL_PENALTIES DECIMAL(8,2)) BEGIN SELECT SUM(AMOUNT) INTO TOTAL_PENALTIES FROM PENALTIES WHERE PLAYERNO = P_PLAYERNO; END # CALL TOTAL_PENALTIES_PLAYER (27, @TOTAL) # SELECT @TOTAL # SELECT FATHER_PLAYERNO, MOTHER_PLAYERNO INTO V_FATHER, V_MOTHER FROM PLAYERS_WITH_PARENTS WHERE PLAYERNO = P_PLAYERNO
Example 30.17:
CREATE PROCEDURE GIVE_ADDRESS (IN P_PLAYERNO SMALLINT, OUT P_STREET VARCHAR(30), OUT P_HOUSENO CHAR(4), OUT P_TOWN VARCHAR(30), OUT P_POSTCODE CHAR(6)) BEGIN SELECT TOWN, STREET, HOUSENO, POSTCODE INTO P_TOWN, P_STREET, P_HOUSENO, P_POSTCODE FROM PLAYERS WHERE PLAYERNO = P_PLAYERNO; END
Example 30.18:
CREATE TABLE FIBON (NUMBER1 INTEGER NOT NULL PRIMARY KEY, NUMBER2 INTEGER NOT NULL) # CREATE PROCEDURE FIBONNACI_START() BEGIN DELETE FROM FIBON; INSERT INTO FIBON (NUMBER, NUMBER2) VALUES (16, 27); END # CREATE PROCEDURE FIBONNACI_GIVE (INOUT NUMBER INTEGER) BEGIN DECLARE N1, N2 INTEGER; SELECT NUMBER1, NUMBER2 INTO N1, N2 FROM FIBON; SET NUMBER = N1 + N2; IF NUMBER > 10000 THEN SET NUMBER = NUMBER - 10000; END IF; SET N1 = N2; SET N2 = NUMBER; UPDATE FIBON SET NUMBER1 = N1, NUMBER2 = N2; END # CALL FIBONNACI_START() # CALL FIBONNACI_GIVE(@C) # SELECT @C # CALL FIBONNACI_GIVE(@C) # SELECT @C # CALL FIBONNACI_GIVE(@C) # SELECT @C
Example 30.19:
CREATE PROCEDURE DELETE_PLAYER (IN P_PLAYERNO INTEGER) BEGIN DECLARE NUMBER_OF_ PENALTIES INTEGER; DECLARE NUMBER_OF_TEAMS INTEGER; SELECT COUNT(*) INTO NUMBER_OF_PENALTIES FROM PENALTIES WHERE PLAYERNO = P_PLAYERNO;
SELECT COUNT(*) INTO NUMBER_OF_TEAMS FROM TEAMS WHERE PLAYERNO = P_PLAYERNO_;
IF NUMBER_OF_PENALTIES = 0 AND NUMBER_OF_TEAMS = 0 THEN CALL DELETE_MATCHES (P_PLAYERNO); DELETE FROM PLAYERS WHERE PLAYERNO = P_PLAYERNO; END IF; END
Example 30.20:
CREATE PROCEDURE DUPLICATE (OUT P_PROCESSED SMALLINT) BEGIN SET P_PROCESSED = 1; INSERT INTO TEAMS VALUES (2,27,'third'); SET P_PROCESSED = 2; END # CALL DUPLICATE(PROCESSED)
Example 30.21:
CREATE PROCEDURE SMALL_MISTAKE1 (OUT ERROR CHAR(5)) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET ERROR = '23000'; SET ERROR = '00000'; INSERT INTO TEAMS VALUES (2,27,'third'); END
Example 30.22:
CREATE PROCEDURE SMALL_MISTAKE2 (OUT ERROR CHAR(5)) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET ERROR = '23000'; DECLARE CONTINUE HANDLER FOR SQLSTATE '21S01' SET ERROR = '21S01'; SET ERROR = '00000'; INSERT INTO TEAMS VALUES (2,27,'third',5); END
Example 30.23:
CREATE PROCEDURE SMALL_MISTAKE3 (OUT ERROR CHAR(5)) BEGIN DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION SET ERROR = 'XXXXX'; SET ERROR = '00000'; INSERT INTO TEAMS VALUES (2,27,'third'); END
Example 30.24:
CREATE PROCEDURE SMALL_MISTAKE4 (OUT ERROR CHAR(5)) BEGIN DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000'; DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET ERROR = '23000'; SET ERROR = '00000'; INSERT INTO TEAMS VALUES (2,27,'third'); END
Example 30.25:
CREATE PROCEDURE SMALL_MISTAKE5 (OUT ERROR CHAR(5)) BEGIN DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000'; DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET ERROR = '23000'; BEGIN DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET ERROR = '23000'; END; BEGIN DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET ERROR = '00000'; INSERT INTO TEAMS VALUES (2,27,'third'); END; END # CREATE PROCEDURE SMALL_MISTAKE6 () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @PROCESSED = 100; BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @PROCESSED = 200; INSERT INTO TEAMS VALUES (2,27,'third'); END; END
Example 30.26:
CREATE PROCEDURE NUMBER_OF_PLAYERS (OUT NUMBER INTEGER) BEGIN DECLARE A_PLAYERNO INTEGER; DECLARE FOUND BOOLEAN DEFAULT TRUE; DECLARE C_PLAYERS CURSOR FOR SELECT PLAYERNO FROM PLAYERS; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND = FALSE; SET NUMBER = 0; OPEN C_PLAYERS; FETCH C_PLAYERS INTO A_PLAYERNO; WHILE FOUND DO SET NUMBER = NUMBER + 1; FETCH C_PLAYERS INTO A_PLAYERNO; END WHILE; CLOSE C_PLAYERS; END
Example 30.27:
CREATE PROCEDURE DELETE_OLDER_THAN_30() BEGIN DECLARE V_AGE, V_PLAYERNO,V_YEARS, V_MONTHS, V_DAYS INTEGER; DECLARE V_BIRTH_DATE DATE; DECLARE FOUND BOOLEAN DEFAULT TRUE; DECLARE C_PLAYERS CURSOR FOR SELECT PLAYERNO, BIRTH_DATE FROM PLAYERS; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND = FALSE; OPEN C_PLAYERS; FETCH C_PLAYERS INTO V_PLAYERNO, V_BIRTH_DATE; WHILE FOUND DO CALL AGE (V_BIRTH_DATE, NOW(), V_YEARS, V_MONTHS, V_DAYS); IF V_YEARS > 30 THEN DELETE FROM PENALTIES WHERE PLAYERNO = V_PLAYERNO; END IF; FETCH C_PLAYERS INTO V_PLAYERNO, V_BIRTH_DATE; END WHILE; CLOSE C_PLAYERS; END
Example 30.28:
CREATE PROCEDURE TOP_THREE (IN P_PLAYERNO INTEGER, OUT OK BOOLEAN) BEGIN DECLARE A_PLAYERNO, BALANCE, SEQNO INTEGER; DECLARE FOUND BOOLEAN; DECLARE BALANCE_PLAYERS CURSOR FOR SELECT PLAYERNO, SUM(WON) - SUM(LOST) FROM MATCHES GROUP BY PLAYERNO ORDER BY 2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND = FALSE; SET SEQNO = 0; SET FOUND = TRUE; SET OK = FALSE; OPEN BALANCE_PLAYERS; FETCH BALANCE_PLAYERS INTO A_PLAYERNO, BALANCE; WHILE FOUND AND SEQNO < 3 AND OK = FALSE DO SET SEQNO = SEQNO + 1; IF A_PLAYERNO = P_PLAYERNO THEN SET OK = TRUE; END IF; FETCH BALANCE_PLAYERS INTO A_PLAYERNO, BALANCE; END WHILE; CLOSE BALANCE_PLAYERS; END
Example 30.29:
CREATE PROCEDURE NEW_TEAM () BEGIN INSERT INTO TEAMS VALUES (100,27,'first'); END # SET AUTOCOMMIT = 1 # START TRANSACTION # INSERT INTO TEAMS VALUES (200,27,'first') # CALL NEW_TEAM() # ROLLBACK WORK
Example 30.30:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' AND TABLE_NAME = 'ROUTINES' ORDER BY ORDINAL_POSITION
Example 30.31:
SHOW PROCEDURE STATUS LIKE 'FIBONNACI'
Example 30.32:
SHOW CREATE PROCEDURE FIBONNACI
Example 30.33:
DROP PROCEDURE DELETE_PLAYER
Example 30.35:
ALTER PROCEDURE DELETE_MATCHES COMPILE
Example 30.36:
GRANT EXECUTE ON DELETE_MATCHES TO JOHN
Example 31.1:
CREATE FUNCTION DOLLARS(AMOUNT DECIMAL(7,2)) RETURNS DECIMAL(7,2) BEGIN RETURN AMOUNT * (1 / 0.8); END # SELECT PAYMENTNO, AMOUNT, DOLLARS(AMOUNT) FROM PENALTIES
Example 31.2:
CREATE FUNCTION NUMBER_OF_PLAYERS() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM PLAYERS); END # SELECT NUMBER_OF_PLAYERS()
Example 31.3:
CREATE FUNCTION NUMBER_OF_PENALTIES (P_PLAYERNO INTEGER) RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM PENALTIES WHERE PLAYERNO = P_PLAYERNO); END # CREATE FUNCTION NUMBER_OF_MATCHES (P_PLAYERNO INTEGER) RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM MATCHES WHERE PLAYERNO = P_PLAYERNO); END # SELECT PLAYERNO, NAME, INITIALS FROM PLAYERS WHERE NUMBER_OF_PENALTIES(PLAYERNO) > NUMBER_OF_MATCHES(PLAYERNO)
Example 31.4:
CREATE FUNCTION NUMBER_OF_DAYS (START_DATE DATE, END_DATE DATE) RETURNS INTEGER BEGIN DECLARE DAYS INTEGER; DECLARE NEXT_DATE, PREVIOUS_DATE DATE; SET DAYS = 0; SET NEXT_DATE = START_DATE + INTERVAL 1 DAY; WHILE NEXT_DATE <= END_DATE DO SET DAYS = DAYS + 1; SET PREVIOUS_DATE = NEXT_DATE; SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY; END WHILE; RETURN DAYS; END
Example 31.5:
CREATE FUNCTION DELETE_PLAYER (P_PLAYERNO INTEGER) RETURNS BOOLEAN BEGIN DECLARE NUMBER_OF_PENALTIES INTEGER; DECLARE NUMBER_OF_TEAMS INTEGER; DECLARE EXIT HANDLER FOR SQLWARNING RETURN FALSE; DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN FALSE;
SELECT COUNT(*) INTO NUMBER_OF_PENALTIES FROM PENALTIES WHERE PLAYERNO = P_PLAYERNO;
SELECT COUNT(*) INTO NUMBER_OF_TEAMS FROM TEAMS WHERE PLAYERNO = P_PLAYERNO;
IF NUMBER_OF_PENALTIES = 0 AND NUMBER_OF_TEAMS = 0 THEN DELETE FROM MATCHES WHERE PLAYERNO = P_PLAYERNO; DELETE FROM PLAYERS WHERE PLAYERNO = P_PLAYERNO; END IF; RETURN TRUE; END
Example 31.6:
CREATE FUNCTION NUMBER_OF_PLAYERS () RETURNS INTEGER BEGIN DECLARE NUMBER INTEGER; CALL NUMBER_OF_PLAYERS(NUMBER); RETURN NUMBER; END
Example 31.7:
DROP FUNCTION PLACE_IN_SET
Example 32.1:
CREATE TABLE CHANGES (USER CHAR(30) NOT NULL, CHA_TIME TIMESTAMP NOT NULL, CHA_PLAYERNO SMALLINT NOT NULL, CHA_TYPE CHAR(1) NOT NULL, CHA_PLAYERNO_NEW INTEGER, PRIMARY KEY (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE))
Example 32.1:
CREATE TRIGGER INSERT_PLAYERS AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_PLAYERNO_NEW) VALUES (USER, CURDATE(), NEW.PLAYERNO, 'I', NULL); END # CREATE PROCEDURE INSERT_CHANGE (IN CPNO INTEGER, IN CTYPE CHAR(1), IN CPNO_NEW INTEGER) BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_PLAYERNO_NEW) VALUES (USER, CURDATE(), CPNO, CTYPE, CPNO_NEW); END # CREATE TRIGGER INSERT_PLAYER AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE(NEW.PLAYERNO, 'I', NULL); END
Example 32.2:
CREATE TRIGGER DELETE_PLAYER AFTER DELETE ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE (OLD.PLAYERNO, 'D', NULL); END
Example 32.3:
CREATE TRIGGER UPDATE_PLAYER AFTER UPDATE ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGES (NEW.PLAYERNO, 'U', OLD.PLAYERNO); END
Example 32.4:
CREATE TRIGGER UPDATE_PLAYER2 AFTER UPDATE(LEAGUENO) ON PLAYERS FOR EACH ROW BEGIN CALL INSERT_CHANGE (NEW.PLAYERNO, 'U', OLD.PLAYERNO); END
Example 32.5:
CREATE TRIGGER UPDATE_PLAYER AFTER UPDATE OF PLAYERS FOR EACH ROW WHEN ( NEW.LEAGUENO <> OLD.LEAGUENO ) BEGIN INSERT INTO CHANGES (USER, CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_PLAYERNO_OLD) VALUES (USER, SYSDATE, NEW.PLAYERNO, 'U', OLD.PLAYERNO); END
Example 32.6:
CREATE TABLE PLAYERS_MAT (PLAYERNO INTEGER NOT NULL PRIMARY KEY, NUMBER_OF_MATCHES INTEGER NOT NULL) # INSERT INTO PLAYERS_MAT (PLAYERNO, NUMBER_OF_MATCHES) SELECT PLAYERNO, (SELECT COUNT(*) FROM MATCHES AS M WHERE P.PLAYERNO = M.PLAYERNO) FROM PLAYERS AS P
Example 32.7:
CREATE TRIGGER INSERT_PLAYERS AFTER INSERT ON PLAYERS FOR EACH ROW BEGIN INSERT INTO PLAYERS_MAT VALUES(NEW.PLAYERNO, 0); END
Example 32.8:
CREATE TRIGGER DELETE_PLAYERS AFTER DELETE ON PLAYERS FOR EACH ROW BEGIN DELETE FROM PLAYERS_MAT WHERE PLAYERNO = OLD.PLAYERNO; END
Example 32.9:
CREATE TRIGGER INSERT_MATCHES AFTER INSERT ON MATCHES FOR EACH ROW BEGIN UPDATE PLAYERS_MAT SET NUMBER_OF_MATCHES = NUMBER_OF_MATCHES + 1 WHERE PLAYERNO = NEW.PLAYERNO; END
Example 32.10:
CREATE TRIGGER DELETE_MATCHES AFTER DELETE ON MATCHES FOR EACH ROW BEGIN UPDATE PLAYERS_MAT SET NUMBER_OF_MATCHES = NUMBER_OF_MATCHES - 1 WHERE PLAYERNO = OLD.PLAYERNO; END
Example 32.11:
CREATE TRIGGER SUM_PENALTIES_INSERT AFTER INSERT ON PENALTIES FOR EACH ROW BEGIN DECLARE TOTAL DECIMAL(8,2);
SELECT SUM(AMOUNT) INTO TOTAL FROM PENALTIES WHERE PLAYERNO = NEW.PLAYERNO;
UPDATE PLAYERS SET SUM_PENALTIES = TOTAL WHERE PLAYERNO = NEW.PLAYERNO END # CREATE TRIGGER SUM_PENALTIES_DELETE AFTER DELETE, UPDATE ON PENALTIES FOR EACH ROW BEGIN DECLARE TOTAL DECIMAL(8,2);
SELECT SUM(AMOUNT) INTO TOTAL FROM PENALTIES WHERE PLAYERNO = OLD.PLAYERNO;
UPDATE PLAYERS SET SUM_PENALTIES = TOTAL WHERE PLAYERNO = OLD.PLAYERNO END # UPDATE PLAYERS SET SUM_PENALTIES = (SELECT SUM(AMOUNT) FROM PENALTIES WHERE PLAYERNO = NEW.PLAYERNO) WHERE PLAYERNO = NEW.PLAYERNO
Example 32.12:
CREATE TRIGGER BIRTHJOINED BEFORE INSERT, UPDATE(BIRTH_DATE, JOINED) OF PLAYERS FOR EACH ROW WHEN (YEAR(NEW.BIRTH_DATE) >= NEW.JOINED) BEGIN ROLLBACK WORK; END; # CREATE TRIGGER BIRTHJOINED BEFORE INSERT, UPDATE ON PLAYERS FOR EACH ROW BEGIN IF YEAR(NEW.BIRTH_DATE) >= NEW.JOINED) THEN ROLLBACK WORK; END IF; END;
Example 32.13:
CREATE TRIGGER FOREIGN_KEY1 BEFORE INSERT, UPDATE(PLAYERNO) OF PENALTIES FOR EACH ROW BEGIN DECLARE NUMBER INTEGER; SELECT COUNT(*) INTO NUMBER FROM PLAYERS WHERE PLAYERNO = NEW.PLAYERNO; IF NUMBER = 0 THEN ROLLBACK WORK; END IF; END # CREATE TRIGGER FOREIGN_KEY1 BEFORE INSERT, UPDATE ON PENALTIES FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM PLAYERS WHERE PLAYERNO = NEW.PLAYERNO) = 0 THEN ROLLBACK WORK; END IF; END # CREATE TRIGGER FOREIGN_KEY2 BEFORE DELETE, UPDATE(PLAYERNO) OF PLAYERS FOR EACH ROW BEGIN DELETE FROM PENALTIES WHERE PLAYERNO = OLD.PLAYERNO; END # CREATE TRIGGER FOREIGN_KEY2 BEFORE DELETE, UPDATE ON PLAYERS FOR EACH ROW BEGIN DELETE FROM PENALTIES WHERE PLAYERNO = OLD.PLAYERNO; END
Example 32.14:
DROP TRIGGER BIRTHJOINED
Answer 32.2:
CREATE TRIGGER MAX1 AFTER INSERT, UPDATE(POSITION) OF COMMITTEE_MEMBERS FOR EACH ROW BEGIN SELECT COUNT(*) INTO NUMBER_MEMBERS FROM COMMITTEE_MEMBERS WHERE PLAYERNO IN (SELECT PLAYERNO FROM COMMITTEE_MEMBERS WHERE CURRENT DATE BETWEEN BEGIN_DATE AND END_DATE GROUP BY POSITION HAVING COUNT(*) > 1) IF NUMBER_MEMBERS > 0 THEN ROLLBACK WORK; ENDIF; END;
Answer 32.3:
CREATE TRIGGER SUM_PENALTIES_250 AFTER INSERT, UPDATE(AMOUNT) OF PENALTIES FOR EACH ROW BEGIN SELECT COUNT(*) INTO NUMBER_PENALTIES FROM PENALTIES WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES GROUP BY PLAYERNO HAVING SUM(AMOUNT) > 250); IF NUMBER_PENALTIES > 0 THEN ROLLBACK WORK; ENDIF; END;
Answer 32.4:
CREATE TRIGGER NUMBER_MATCHES_INSERT AFTER INSERT OF MATCHES FOR EACH ROW BEGIN UPDATE TEAMS SET NUMBER_MATCHES = (SELECT COUNT(*) FROM MATCHES WHERE PLAYERNO = NEW.PLAYERNO) WHERE PLAYERNO = NEW.PLAYERNO END; # CREATE TRIGGER NUMBER_MATCHES_DELETE AFTER DELETE, UPDATE OF MATCHES FOR EACH ROW BEGIN UPDATE TEAMS SET NUMBER_MATCHES = (SELECT COUNT(*) FROM MATCHES WHERE PLAYERNO = OLD.PLAYERNO) WHERE PLAYERNO = OLD.PLAYERNO END;
