Example 4.2:
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.3:
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.4:
SELECT PLAYERNO, NAME, BIRTH_DATE
FROM PLAYERS
WHERE TOWN = 'Stratford'
ORDER BY NAME
Example 4.5:
SELECT PLAYERNO
FROM PLAYERS
WHERE JOINED > 1980
AND TOWN = 'Stratford'
ORDER BY PLAYERNO
Example 4.6:
SELECT *
FROM PENALTIES
Example 4.7:
SELECT 33 * 121
Example 4.8:
UPDATE PENALTIES
SET AMOUNT = 200
WHERE PLAYERNO = 44
SELECT PLAYERNO, AMOUNT
FROM PENALTIES
WHERE PLAYERNO = 44
Example 4.9:
DELETE
FROM PENALTIES
WHERE AMOUNT > 100
Example 4.10:
CREATE INDEX PENALTIES_AMOUNT
ON PENALTIES (AMOUNT)
Example 4.11:
CREATE VIEW NUMBER_SETS AS
SELECT MATCHNO, ABS(WON - LOST) AS DIFFERENCE
FROM MATCHES
SELECT *
FROM NUMBER_SETS
Example 4.12:
CREATE TABLE TOWNS (
TOWNNO INTEGER NOT NULL PRIMARY KEY,
TOWNNAME CHAR(20) NOT NULL)
INSERT INTO TOWNS VALUES (1, 'Stratford')
INSERT INTO TOWNS VALUES (2, 'Inglewood')
Example 4.13:
ATTACH DATABASE EXTRADB AS EXTRA
Example 4.14:
SELECT *
FROM EXTRA.TOWNS
Example 4.15:
DETACH DATABASE EXTRA
Example 4.16:
DROP TABLE COMMITTEE_MEMBERS
Example 4.17:
DROP VIEW NUMBER_SETS
Example 4.18:
DROP INDEX PENALTIES_AMOUNT
Example 4.19:
SELECT NAME
FROM SQLITE_MASTER
WHERE TYPE = 'index'
AND TBL_NAME = 'PENALTIES'
Example 4.20:
.database
Example 4.21:
.tables
Example 4.22:
.schema TEAMS
Example 4.23:
.dump TEAMS
Example 4.24:
.indices PENALTIES
Example 4.25:
PRAGMA DATABASE_LIST
Example 4.26:
PRAGMA TABLE_INFO(PENALTIES)
Example 4.27:
PRAGMA INDEX_LIST(PENALTIES)
Example 4.28:
PRAGMA INDEX_INFO(PENALTIES)
Example 4.29:
PRAGMA FOREIGN_KEY_LIST(MATCHES)
Example 6.1:
SELECT TYPEOF(1) AS INT,
TYPEOF(1.1) AS DEC,
TYPEOF(1E1) AS FLOAT,
TYPEOF('1') AS CHAR,
TYPEOF('2007-02-02') AS DATE,
TYPEOF('SQLite') AS CHAR,
TYPEOF(NULL) AS NOHTING
Example 6.2:
SELECT MATCHNO, WON - LOST
FROM MATCHES
WHERE WON = LOST + 2
Example 6.3:
SELECT TEAMNO, DIVISION
FROM TEAMS
PRAGMA FULL_COLUMN_NAMES = 1
Example 6.4:
SELECT TEAMNO AS TEAM_NUMBER, DIVISION AS DIVISION_OF_TEAM
FROM TEAMS
Example 6.5:
SELECT PAYMENTNO, AMOUNT * 100 AS CENTS
FROM PENALTIES
Example 6.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 6.7:
SELECT AMOUNT * 100 AS CENTS
FROM PENALTIES
GROUP BY CENTS
ORDER BY CENTS
Example 6.8:
SELECT *
FROM PENALTIES
WHERE PAYMENT_DATE = CURRENT_DATE
Example 6.9:
SELECT PLAYERNO,
CASE SEX
WHEN 'F' THEN 'Female'
ELSE 'Male'
END AS SEX,
NAME
FROM PLAYERS
WHERE JOINED > 1980
SELECT PLAYERNO,
CASE SEX
WHEN 'F' THEN 'Female'
END AS FEMALES, NAME
FROM PLAYERS
WHERE JOINED > 1980
Example 6.10:
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 6.11:
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 6.12:
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 6.13:
SELECT (PLAYERNO), (((NAME)))
FROM PLAYERS
Example 6.14:
SELECT PAYMENTNO, STRFTIME('%Y', PAYMENT_DATE) AS YEAR
FROM PENALTIES
WHERE STRFTIME('%Y', PAYMENT_DATE) * 1 > 1980
Example 6.15:
SELECT PLAYERNO, INITIALS || '. ' || NAME AS FULL_NAME
FROM PLAYERS
WHERE SUBSTR(NAME, 1, 1) = 'B'
Example 6.16:
SELECT INITIALS, NAME, COALESCE(LEAGUENO, '1')
FROM PLAYERS
WHERE Town = 'Stratford'
Example 6.17:
SELECT PLAYERNO, STRFTIME('%w', BIRTH_DATE) AS DAYNUMBER,
STRFTIME('%m', BIRTH_DATE) AS MONTHNUMBER,
STRFTIME('%j', BIRTH_DATE) AS DAYINYEARNUMBER
FROM PLAYERS
WHERE PLAYERNO < 10
Example 6.18:
SELECT PLAYERNO, BIRTH_DATE, DATE(BIRTH_DATE, '7 days') AS BIRTH_DATE_PLUS_7
FROM PLAYERS
WHERE STRFTIME('%w',BIRTH_DATE) = '6'
Example 6.19:
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT > 50
Example 6.20:
SELECT NAME || ' ' || CAST(PLAYERNO AS TEXT) AS NAME_PLUS_PNO
FROM PLAYERS
WHERE TOWN = 'Inglewood'
Example 6.21:
UPDATE PLAYERS
SET LEAGUENO = NULL
WHERE PLAYERNO = 2
Example 6.22:
SELECT NULL
Example 6.23:
SELECT MATCHNO, WON, LOST
FROM MATCHES
WHERE WON >= LOST * 2
Example 6.24:
SELECT 50 << 2
Example 6.25:
SELECT PLAYERNO
FROM PLAYERS
WHERE PLAYERNO & 1
Example 6.26:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO = (PLAYERNO >> 1) << 1
Example 6.27:
SELECT MATCHNO, TEAMNO, MATCHNO | TEAMNO, MATCHNO & TEAMNO
FROM MATCHES
Example 6.28:
SELECT PLAYERNO, TOWN || ' ' || STREET || ' ' || HOUSENO AS ADDRESS
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 6.29:
SELECT PAYMENTNO , PAYMENT_DATE, DATE(PAYMENT_DATE, '7 DAYS') AS DATE_PLUS_7
FROM PENALTIES
WHERE PAYMENTNO > 5
Example 6.30:
SELECT PAYMENTNO, PAYMENT_DATE
FROM PENALTIES
WHERE PAYMENT_DATE >= '1982-12-25'
AND PAYMENT_DATE <= DATE('1982-12-25', '6 DAYS')
Example 6.31:
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 6.32:
SELECT MATCHNO, START_TIME, TIME(START_TIME, '8 HOURS') AS START_TIME_PLUS_8
FROM MATCHES_SPECIAL
Example 6.33:
SELECT MATCHNO, END_TIME
FROM MATCHES_SPECIAL
WHERE END_TIME >= '00:00:00'
AND END_TIME <= TIME('24:00:00', '-6 HOURS', '-30 MINUTES')
Example 6.34:
SELECT PAYMENTNO, PAYMENTNO > 4
FROM PENALTIES
Example 6.35:
SELECT PLAYERNO, JOINED, PLAYERNO < 15, JOINED > 1979
FROM PLAYERS
WHERE (PLAYERNO < 15) = (JOINED > 1979)
Example 6.36:
INSERT INTO COMMITTEE_MEMBERS
VALUES (2 + 4, CURRENT_DATE, DATE(CURRENT_DATE, '17 DAYS'), 'Member')
Answer 6.6
:
SELECT PLAYERS.PLAYERNO, PLAYERS.NAME, PLAYERS.INITIALS
FROM PLAYERS
WHERE PLAYERS.PLAYERNO > 6
ORDER BY PLAYERS.NAME
Answer 6.8:
SELECT PLAYERNO
FROM COMMITTEE_MEMBERS
WHERE BEGIN_DATE = CURRENT_DATE
Answer 6.9:
SELECT TEAMNO,
CASE DIVISION
WHEN 'first' then 'first division'
WHEN 'second' THEN 'second division'
ELSE 'unknown'
END AS DIVISION
FROM TEAMS
Answer 6.10:
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 6.11:
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 6.12:
SELECT PAYMENTNO
FROM PENALTIES
WHERE STRFTIME('%w', PAYMENT_DATE) = '1'
Answer 6.13:
SELECT PAYMENTNO
FROM PENALTIES
WHERE STRFTIME('%Y', PAYMENT_DATE) = '1984'
Answer 6.20:
SELECT PLAYERNO, SUBSTR(INITIALS,1,1) || '. ' || NAME
FROM PLAYERS
Answer 6.21:
SELECT TEAMNO, DIVISION || ' division'
FROM TEAMS
Answer 6.23:
SELECT PLAYERNO, BEGIN_DATE, DATE(BEGIN_DATE, '2 MONTHS', '3 DAYS')
FROM COMMITTEE_MEMBERS
Answer 6.27:
SELECT PAYMENTNO, PAYMENT_DATE,
DATETIME(PAYMENT_DATE, '3 HOURS', '50 SECONDS', '99 MICROSECONDS')
FROM PENALTIES
Answer 6.28:
SELECT PLAYERNO, CASE TON = 'Inglewood'
WHEN 1 THEN 'Yes'
ELSE 'No'
END
FROM PLAYERS
Answer 6.29:
SELECT *
FROM PENALTIES
WHERE (AMOUNT = 25) = (PLAYERNO = 44)
Example 7.1:
SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT > 25
GROUP BY PLAYERNO
HAVING COUNT(*) > 1
ORDER BY PLAYERNO
Example 7.2:
SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE TOWN = 'Stratford'
ORDER BY LEAGUENO
Example 7.3:
SELECT 89 * 73
Example 7.4:
SELECT PLAYERNO
FROM TEAMS
UNION
SELECT PLAYERNO
FROM PENALTIES
SELECT PLAYERNO
FROM TEAMS
ORDER BY PLAYERNO
UNION
SELECT PLAYERNO
FROM PENALTIES
SELECT PLAYERNO
FROM TEAMS
UNION
SELECT PLAYERNO
FROM PENALTIES
ORDER BY PLAYERNO
Example 7.5:
SELECT PLAYERNO
FROM (SELECT PLAYERNO, SEX
FROM PLAYERS
WHERE PLAYERNO < 10) AS PLAYERS10
WHERE SEX = 'M'
Example 7.6:
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 7.7:
SELECT PLAYERNO, JOINED -
(SELECT JOINED
FROM PLAYERS
WHERE PLAYERNO = 100)
FROM PLAYERS
WHERE PLAYERNO < 60
SELECT PLAYERNO, JOINED - 1979
FROM PLAYERS
WHERE PLAYERNO < 60
SELECT TEAMNO
FROM TEAMS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM PLAYERS)
Example 7.8:
SELECT PLAYERNO
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) =
(SELECT STRFTIME('%Y', BIRTH_DATE)
FROM PLAYERS
WHERE PLAYERNO = 27)
SELECT PLAYERNO
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) = '1964'
Example 7.9:
SELECT (SELECT BIRTH_DATE
FROM PLAYERS
WHERE PLAYERNO = 27) AS BD27,
(SELECT BIRTH_DATE
FROM PLAYERS
WHERE PLAYERNO = 44) AS BD44,
(SELECT BIRTH_DATE
FROM PLAYERS
WHERE PLAYERNO = 100) AS BD100
Answer 7.8:
SELECT PLAYERNO, BEGIN_DATE
FROM COMMITTEE_MEMBERS
UNION
SELECT PLAYERNO, END_DATE
FROM COMMITTEE_MEMBERS
ORDER BY PLAYERNO
Answer 7.9:
SELECT PLAYERNO, BEGIN_DATE, 'Begin date'
FROM COMMITTEE_MEMBERS
UNION
SELECT PLAYERNO, END_DATE, 'End date'
FROM COMMITTEE_MEMBERS
ORDER BY PLAYERNO
Answer 7.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 7.11:
SELECT TEAMNO
FROM TEAMS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM PLAYERS
WHERE NAME = 'Parmenter' AND INITIALS = 'R')
Answer 7.12:
SELECT NAME
FROM PLAYERS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMNO =
(SELECT TEAMNO
FROM MATCHES
WHERE MATCHNO = 6))
Answer 7.13:
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT >
(SELECT AMOUNT
FROM PENALTIES
WHERE PAYMENTNO = 4)
Answer 7.14:
SELECT PLAYERNO
FROM PLAYERS
WHERE STRFTIME('%w', BIRTH_DATE) =
(SELECT STRFTIME('%w', BIRTH_DATE)
FROM PLAYERS
WHERE PLAYERNO = 2)
Answer 7.15:
SELECT (SELECT DIVISION
FROM TEAMS
WHERE TEAMNO = 1),
(SELECT DIVISION
FROM TEAMS
WHERE TEAMNO = 2)
Answer 7.16:
SELECT (SELECT AMOUNT
FROM PENALTIES
WHERE PAYMENTNO = 1) +
(SELECT AMOUNT
FROM PENALTIES
WHERE PAYMENTNO = 2) +
(SELECT AMOUNT
FROM PENALTIES
WHERE PAYMENTNO = 3)
Example 8.1:
SELECT *
FROM EXTRA.TOWNS
Example 8.2:
SELECT TEAMNO
FROM TEAMS
SELECT TEAMS.TEAMNO
FROM TEAMS
SELECT TENNIS.TEAMS.TEAMNO
FROM TENNIS.TEAMS
Example 8.3:
SELECT TEAMNO, NAME
FROM TEAMS, PLAYERS
WHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Example 8.4:
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 8.5:
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 8.6:
SELECT T.PLAYERNO
FROM TEAMS AS T, PENALTIES AS PEN
WHERE T.PLAYERNO = PEN.PLAYERNO
Example 8.7:
SELECT DISTINCT T.PLAYERNO
FROM TEAMS AS T, PENALTIES AS PEN
WHERE T.PLAYERNO = PEN.PLAYERNO
Example 8.8:
SELECT DISTINCT P.NAME, P.INITIALS
FROM PLAYERS AS P, MATCHES AS M
WHERE P.PLAYERNO = M.PLAYERNO
Example 8.9:
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 8.10:
SELECT PEN.PAYMENTNO, PEN.PLAYERNO, PEN.PAYMENT_DATE
FROM PENALTIES AS PEN, PLAYERS AS P
WHERE PEN.PLAYERNO = P.PLAYERNO
AND STRFTIME('%Y', PEN.PAYMENT_DATE) = P.JOINED
Example 8.11:
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 8.12:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS, PENALTIES
WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
AND BIRTH_DATE > '1920-06-30'
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS INNER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
WHERE BIRTH_DATE > '1920-06-30'
Example 8.13:
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 8.14:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS, PENALTIES
WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
ORDER BY PLAYERS.PLAYERNO
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS LEFT OUTER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
ORDER BY PLAYERS.PLAYERNO
Example 8.15:
SELECT PAYMENTNO, NAME
FROM PENALTIES LEFT OUTER JOIN PLAYERS
ON PENALTIES.PLAYERNO = PLAYERS.PLAYERNO
ORDER BY PAYMENTNO
Example 8.16:
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 8.17:
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 8.18:
SELECT PLAYERS.PLAYERNO
FROM PLAYERS INNER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
LEFT OUTER JOIN TEAMS
ON PLAYERS.PLAYERNO = TEAMS.PLAYERNO
SELECT PLAYERS.PLAYERNO
FROM (PLAYERS INNER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)
LEFT OUTER JOIN TEAMS
ON PLAYERS.PLAYERNO = TEAMS.PLAYERNO
Example 8.19:
SELECT PLAYERS.PLAYERNO
FROM (PLAYERS INNER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)
Example 8.20:
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS INNER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
WHERE BIRTH_DATE > '1920-06-30'
SELECT PLAYERS.PLAYERNO, NAME, AMOUNT
FROM PLAYERS NATURAL JOIN PENALTIES
WHERE BIRTH_DATE > '1920-06-30'
Example 8.21:
SELECT *
FROM TEAMS NATURAL JOIN PENALTIES
Example 8.22:
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 8.23:
SELECT *
FROM PENALTIES LEFT OUTER JOIN TEAMS
USING (PLAYERNO)
Example 8.24:
SELECT PLAYERNO
FROM (SELECT *
FROM PLAYERS
WHERE TOWN = 'Stratford') AS STRATFORDERS
Example 8.25:
SELECT SMALL_TEAMS.PLAYERNO
FROM (SELECT PLAYERNO, DIVISION
FROM TEAMS) AS SMALL_TEAMS
WHERE SMALL_TEAMS.DIVISION = 'first'
Example 8.26:
SELECT MATCHNO, DIFFERENCE
FROM (SELECT MATCHNO,
ABS(WON - LOST) AS DIFFERENCE
FROM MATCHES) AS M
WHERE DIFFERENCE > 2
Example 8.27:
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 8.28:
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 PLAYERNO
SELECT PLAYERNO, NAME, PLAYERS.TOWN, NUMBER * 1000
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 PLAYERNO
Example 8.29:
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 8.30:
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 8.31:
SELECT NUMBER, NUMBER*NUMBER*NUMBER AS POWER3
FROM (SELECT 10 AS 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 NUMBER*NUMBER*NUMBER <= 4000
Example 8.32:
SELECT NUMBER
FROM (SELECT CAST(DIGIT1.DIGIT || DIGIT2.DIGIT ||
DIGIT3.DIGIT AS INTEGER)
AS NUMBER
FROM (SELECT '0' AS 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' AS 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' AS 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 NUMBER
Example 8.33:
SELECT DIGIT*DIGIT AS SQUARE, DIGIT AS BASIS
FROM (SELECT CAST(DIGIT1.DIGIT || DIGIT2.DIGIT ||
DIGIT3.DIGIT AS INTEGER)
AS DIGIT
FROM (SELECT '0' AS 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' AS 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' AS 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 DIGIT * DIGIT IN
(SELECT CAST(DIGIT1.DIGIT || DIGIT2.DIGIT ||
DIGIT3.DIGIT AS INTEGER)
AS DIGIT
FROM (SELECT '0' AS 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' AS 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' AS 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)
ORDER BY DIGIT
Answer 8.3:
SELECT PAYMENTNO, AMOUNT, PLAYERS.PLAYERNO, NAME
FROM PENALTIES, PLAYERS
WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO
Answer 8.4:
SELECT PAYMENTNO, NAME
FROM PENALTIES, PLAYERS, TEAMS
WHERE PENALTIES.PLAYERNO = TEAMS.PLAYERNO
AND TEAMS.PLAYERNO = PLAYERS.PLAYERNO
Answer 8.5:
SELECT T.TEAMNO, P.NAME
FROM TEAMS AS T, PLAYERS AS P
WHERE T.PLAYERNO = P.PLAYERNO
Answer 8.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 8.7:
SELECT P.PLAYERNO, P.NAME
FROM PLAYERS AS P, COMMITTEE_MEMBERS AS C
WHERE P.PLAYERNO = C.PLAYERNO
AND C.POSITION = 'Chairman'
Answer 8.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 8.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 8.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 8.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 8.12:
SELECT T.TEAMNO, P.NAME
FROM TEAMS AS T INNER JOIN PLAYERS AS P
ON T.PLAYERNO = P.PLAYERNO
Answer 8.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 8.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 8.15:
SELECT PLAYERS.PLAYERNO, PENALTIES.AMOUNT
FROM PLAYERS LEFT OUTER JOIN PENALTIES
ON PLAYERS.PLAYERNO = PENALTIES.PLAYERNO
Answer 8.16:
SELECT P.PLAYERNO, M.TEAMNO
FROM PLAYERS AS P LEFT OUTER JOIN MATCHES AS M
ON P.PLAYERNO = M.PLAYERNO
Answer 8.17:
SELECT P.PLAYERNO, PEN.AMOUNT, 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 8.21:
SELECT PLAYERNO, DIFFERENCE
FROM (SELECT PLAYERNO,
JOINED - STRFTIME('%Y', BIRTH_DATE)
AS DIFFERENCE
FROM PLAYERS) AS DIFFERENCES
WHERE DIFFERENCE > 20
Answer 8.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 8.23:
SELECT ABS(RANDOM()/10000000000000000)
FROM (SELECT 0 AS 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 9.1:
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 9.2:
SELECT PLAYERNO, BIRTH_DATE, JOINED
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) + 17 = JOINED
Example 9.3:
SELECT PLAYERNO
FROM PLAYERS
WHERE LEAGUENO = '7060'
Example 9.4:
SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE LEAGUENO = LEAGUENO
Example 9.5:
SELECT 1 AS SEQNO, CAST(1 AS INTEGER) AS VALUE UNION
SELECT 2, CAST('1' AS TEXT) UNION
SELECT 3, CAST('1' AS REAL) UNION
SELECT 4, CAST(X'41' AS BLOB) UNION
SELECT 5, NULL
ORDER BY VALUE
Example 9.6:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMNO = 1)
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO = 6
SELECT *
FROM PLAYERS
WHERE BIRTH_DATE <
(SELECT BIRTH_DATE
FROM PLAYERS)
Example 9.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 BIRTH_DATE <
(SELECT BIRTH_DATE
FROM PLAYERS
WHERE LEAGUENO = '9999')
Example 9.8:
SELECT MATCHNO
FROM MATCHES
WHERE TEAMNO =
(SELECT TEAMNO
FROM TEAMS
WHERE PLAYERNO = 27)
Example 9.9:
SELECT MATCHNO
FROM MATCHES
WHERE 'Inglewood' =
(SELECT TOWN
FROM PLAYERS
WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO)
SELECT TOWN
FROM PLAYERS
WHERE PLAYERS.PLAYERNO = 6
SELECT TOWN
FROM PLAYERS
WHERE PLAYERS.PLAYERNO = 44
Example 9.10:
SELECT MATCHNO, PLAYERNO, TEAMNO
FROM MATCHES
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMS.PLAYERNO = MATCHES.PLAYERNO)
Example 9.11:
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 9.12:
SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE LEAGUENO
Example 9.13:
SELECT TEAMNO
FROM TEAMS
WHERE TEAMNO - 1
SELECT * FROM PLAYERS WHERE 18
SELECT * FROM PLAYERS WHERE NULL
SELECT * FROM PLAYERS WHERE PLAYERNO & 3
SELECT * FROM PLAYERS WHERE STRFTIME('%Y' , BIRTH_DATE)
Example 9.14:
SELECT PLAYERNO, NAME, SEX, BIRTH_DATE
FROM PLAYERS
WHERE SEX = 'M'
AND BIRTH_DATE > '1970-12-31'
Example 9.15:
SELECT PLAYERNO, NAME, TOWN
FROM PLAYERS
WHERE TOWN = 'Plymouth'
OR TOWN = 'Eltham'
Example 9.16:
SELECT PLAYERNO, NAME, TOWN
FROM PLAYERS
WHERE TOWN <> 'Stratford'
SELECT PLAYERNO, NAME, TOWN
FROM PLAYERS
WHERE NOT (TOWN = 'Stratford')
Example 9.17:
SELECT PLAYERNO, TOWN, BIRTH_DATE
FROM PLAYERS
WHERE (TOWN = 'Stratford' OR STRFTIME('%Y', BIRTH_DATE) = '1963')
AND NOT (TOWN = 'Stratford' AND STRFTIME('%Y', BIRTH_DATE) = '1963')
Example 9.18:
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 9.19:
SELECT PLAYERNO, STRFTIME('%Y', BIRTH_DATE) AS BIRTH_YEAR
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) IN ('1962', '1963', '1970')
Example 9.20:
SELECT MATCHNO, WON, LOST
FROM MATCHES
WHERE 2 IN (WON, LOST)
Example 9.21:
SELECT PLAYERNO
FROM PLAYERS
WHERE PLAYERNO IN
(100,
(SELECT PLAYERNO
FROM PENALTIES
WHERE PAYMENTNO = 1),
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMNO = 2))
Example 9.22:
SELECT MATCHNO, WON, LOST
FROM MATCHES
WHERE WON IN
(ROUND(MATCHNO / 2), LOST,
(SELECT LOST
FROM MATCHES
WHERE MATCHNO = 1))
Example 9.23:
SELECT MATCHNO
FROM MATCHES
WHERE (SELECT SUBSTR(NAME,1,1)
FROM PLAYERS
WHERE PLAYERS.PLAYERNO = MATCHES.PLAYERNO) IN ('B','C','E')
Example 9.24:
SELECT PLAYERNO
FROM MATCHES
SELECT PLAYERNO, NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO IN (6, 6, 6, 44, 83, 2, 57, 8, 27, 104, 112, 112, 8)
SELECT PLAYERNO, NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES)
SELECT PLAYERNO, NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO IN (6, 6, 6, 44, 83, 2, 57, 8, 27, 104, 112, 112, 8)
Example 9.25:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO = 1)
Example 9.26:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO NOT IN
(SELECT TEAMNO
FROM TEAMS
WHERE PLAYERNO = 6))
Example 9.27:
SELECT *
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 95
AND END_DATE IN
(SELECT END_DATE
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 27)
Example 9.28:
SELECT *
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 57
AND END_DATE IN
(SELECT END_DATE
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 7)
Example 9.29:
SELECT *
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 95
AND END_DATE NOT IN
(SELECT END_DATE
FROM COMMITTEE_MEMBERS
WHERE PLAYERNO = 7)
Example 9.30:
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 9.31:
SELECT MATCHNO, WON + LOST
FROM MATCHES
WHERE WON + LOST BETWEEN 2 AND 4
Example 9.32:
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 9.33:
SELECT NAME, PLAYERNO
FROM PLAYERS
WHERE NAME LIKE 'B%'
PRAGMA CASE_SENSITIVE_LIKE = 1
Example 9.34:
SELECT NAME, PLAYERNO
FROM PLAYERS
WHERE NAME LIKE '%r'
Example 9.35:
SELECT NAME, PLAYERNO
FROM PLAYERS
WHERE NAME LIKE '%e_'
Example 9.36:
SELECT NAME, TOWN, PLAYERNO
FROM PLAYERS
WHERE NAME LIKE '%' || SUBSTR(TOWN,3,1)
Example 9.37:
SELECT NAME, PLAYERNO
FROM PLAYERS
WHERE NAME LIKE '%#_%' ESCAPE '#'
Example 9.38:
SELECT NAME, PLAYERNO
FROM PLAYERS
WHERE NAME GLOB 'B*'
Example 9.39:
SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE LEAGUENO IS NOT NULL
Example 9.40:
SELECT NAME, PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE LEAGUENO <> '8467' OR LEAGUENO IS NULL
Example 9.41:
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)
SELECT *
FROM PENALTIES
WHERE PLAYERNO = 6
Example 9.42:
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 9.43:
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 9.44:
SELECT TEAMNO, DIVISION
FROM TEAMS
WHERE EXISTS
(SELECT *
FROM MATCHES
WHERE PLAYERNO = 44
AND TEAMNO = TEAMS.TEAMNO)
Example 9.45:
SELECT DISTINCT PLAYERNO
FROM PENALTIES AS PEN
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE PAYMENTNO <> PEN.PAYMENTNO)
Example 9.46:
SELECT TEAMNO
FROM TEAMS
WHERE NOT EXISTS
(SELECT *
FROM MATCHES
WHERE PLAYERNO = 57
AND TEAMNO = TEAMS.TEAMNO)
Example 9.47:
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))
SELECT *
FROM TEAMS AS T
WHERE NOT EXISTS
(SELECT *
FROM MATCHES AS M
WHERE T.TEAMNO = M.TEAMNO AND M.PLAYERNO = 27)
Example 9.48:
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 9.49:
SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO IN
(SELECT TEAMNO
FROM TEAMS
WHERE TEAMNO NOT IN
(SELECT TEAMNO
FROM MATCHES
WHERE PLAYERNO = 57))
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 9.50:
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
SELECT PLAYERNO
FROM PLAYERS
WHERE NOT (TOWN = 'Stratford')
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN <> 'Stratford'
Example 9.51:
SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT = 25
SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT <> 25
SELECT PLAYERNO
FROM PLAYERS
WHERE PLAYERNO NOT IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT = 25)
SELECT PLAYERNO
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT = 25)
Answer 9.1:
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT > 60
SELECT PAYMENTNO
FROM PENALTIES
WHERE 60 < AMOUNT
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT - 60 > 0
Answer 9.2:
SELECT TEAMNO
FROM TEAMS
WHERE PLAYERNO <> 27
Answer 9.4:
SELECT DISTINCT PLAYERNO
FROM MATCHES
WHERE WON > LOST
Answer 9.5:
SELECT DISTINCT PLAYERNO
FROM MATCHES
WHERE WON + LOST = 5
Answer 9.6:
SELECT PLAYERNO, NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM PENALTIES
WHERE PAYMENTNO = 4)
Answer 9.7:
SELECT PLAYERNO, NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMNO =
(SELECT TEAMNO
FROM MATCHES
WHERE MATCHNO = 2))
Answer 9.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 9.9:
SELECT MATCHNO
FROM MATCHES
WHERE WON =
(SELECT WON
FROM MATCHES
WHERE MATCHNO = 6)
AND MATCHNO <> 6
AND TEAMNO = 2
Answer 9.10:
SELECT MATCHNO
FROM MATCHES
WHERE WON =
(SELECT WON
FROM MATCHES
WHERE MATCHNO = 2)
AND LOST =
(SELECT LOST
FROM MATCHES
WHERE MATCHNO = 8)
Answer 9.11:
SELECT PAYMENTNO
FROM PENALTIES
WHERE 1965 <
(SELECT STRFTIME('%Y', BIRTH_DATE)
FROM PLAYERS
WHERE PLAYERS.PLAYERNO = PENALTIES.PLAYERNO)
Answer 9.12:
SELECT PAYMENTNO, PLAYERNO
FROM PENALTIES
WHERE PLAYERNO =
(SELECT PLAYERNO
FROM TEAMS
WHERE TEAMS.PLAYERNO = PENALTIES.PLAYERNO)
Answer 9.13:
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 9.14:
SELECT PLAYERNO
FROM PLAYERS
WHERE JOINED >= 1970
AND JOINED <= 1980
SELECT PLAYERNO
FROM PLAYERS
WHERE NOT (JOINED < 1970 OR JOINED > 1980)
Answer 9.15:
SELECT PLAYERNO, NAME, BIRTH_DATE
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) % 400 = 0
OR ((STRFTIME('%Y', BIRTH_DATE) % 4) = 0
AND NOT(STRFTIME('%Y', BIRTH_DATE) % 100) = 0)
Answer 9.16:
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 STRFTIME('%Y', BIRTH_DATE) > 1965
AND WON > LOST
Answer 9.17:
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT IN (50, 75, 100)
Answer 9.18:
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 9.19:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES)
Answer 9.20:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT > 50)
Answer 9.21
SELECT TEAMNO, PLAYERNO
FROM TEAMS
WHERE DIVISION = 'first'
AND PLAYERNO IN
(SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford')
Answer 9.22:
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 9.24:
SELECT PAYMENTNO
FROM PENALTIES
WHERE AMOUNT BETWEEN 50 AND 100
Answer 9.25:
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 9.26:
SELECT PLAYERNO
FROM PLAYERS
WHERE JOINED BETWEEN STRFTIME('%Y', BIRTH_DATE, '16 YEARS', '1 DAYS')
AND STRFTIME('%Y', BIRTH_DATE, '40 YEARS', '1 DAYS')
Answer 9.27:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE NAME LIKE '%is%'
Answer 9.28:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE NAME LIKE '______'
Answer 9.29:
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(NAME) > 6
Answer 9.30:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE NAME LIKE '__l%l___'
Answer 9.31
SELECT COL1
FROM (SELECT 'A%B%C' AS COL1
UNION
SELECT '%ABC%'
UNION
SELECT 'ABC%D') AS T1
WHERE COL1 LIKE '_@%%@%_' ESCAPE '@'
Answer 9.32:
SELECT PLAYERNO
FROM PLAYERS
WHERE LEAGUENO IS NULL
Answer 9.34:
SELECT NAME, INITIALS
FROM PLAYERS
WHERE EXISTS
(SELECT *
FROM TEAMS
WHERE PLAYERNO = PLAYERS.PLAYERNO)
Answer 9.35:
SELECT NAME, INITIALS
FROM PLAYERS AS P
WHERE NOT EXISTS
(SELECT *
FROM TEAMS AS T
WHERE T.PLAYERNO = P.PLAYERNO
AND EXISTS
(SELECT *
FROM MATCHES AS M
WHERE M.TEAMNO = T.TEAMNO
AND M.PLAYERNO = 112))
Answer 9.37:
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 9.38:
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 9.39:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE EXISTS
(SELECT *
FROM PENALTIES
WHERE PLAYERNO = PLAYERS.PLAYERNO)
Answer 9.40:
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 9.41:
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 9.42:
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 9.43:
SELECT PLAYERNO
FROM PLAYERS
WHERE PLAYERNO NOT IN
(SELECT PLAYERNO
FROM MATCHES WHERE WON = 3)
Answer 9.44:
SELECT TEAMNO, DIVISION
FROM TEAMS
WHERE TEAMNO NOT IN
(SELECT TEAMNO
FROM MATCHES
WHERE PLAYERNO = 6)
Answer 9.45:
SELECT DISTINCT PLAYERNO
FROM MATCHES
WHERE PLAYERNO NOT IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO IN
(SELECT TEAMNO
FROM MATCHES
WHERE PLAYERNO = 57))
Example 10.1:
SELECT *
FROM PENALTIES
SELECT PAYMENTNO, PLAYERNO, PAYMENT_DATE, AMOUNT
FROM PENALTIES
SELECT PENALTIES.*
FROM PENALTIES
Example 10.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 10.3:
SELECT *, *
FROM TEAMS
Example 10.4:
SELECT MATCHNO, 'Tally', WON - LOST, WON * 10
FROM MATCHES
Example 10.5:
SELECT TOWN
FROM PLAYERS
SELECT DISTINCT TOWN
FROM PLAYERS
Example 10.6:
SELECT STREET, TOWN
FROM PLAYERS
SELECT DISTINCT STREET, TOWN
FROM PLAYERS
SELECT TOWN
FROM PLAYERS
SELECT ALL TOWN
FROM PLAYERS
Example 10.7:
SELECT DISTINCT LEAGUENO
FROM PLAYERS
Example 10.8:
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 10.9:
SELECT COUNT(*)
FROM PLAYERS
Example 10.10:
SELECT COUNT(*)
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 10.11:
SELECT COUNT(*), PLAYERNO
FROM PLAYERS
SELECT 'The number of players is', COUNT(*)
FROM PLAYERS
Example 10.12:
SELECT COUNT(LEAGUENO)
FROM PLAYERS
SELECT COUNT(ALL LEAGUENO)
FROM PLAYERS
Example 10.13:
SELECT COUNT(DISTINCT TOWN)
FROM PLAYERS
Example 10.14:
SELECT COUNT(DISTINCT SUBSTR(NAME, 1, 1)) AS CHARS
FROM PLAYERS
Example 10.15:
SELECT COUNT(DISTINCT STRFTIME('%Y', PAYMENT_DATE)) AS YEARS
FROM PENALTIES
Example 10.16:
SELECT COUNT(DISTINCT TOWN), COUNT(DISTINCT SEX)
FROM PLAYERS
Example 10.17:
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 10.18:
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 10.19:
SELECT (SELECT COUNT(*)
FROM PENALTIES) AS NUMBER_PENALTIES,
(SELECT COUNT(*)
FROM MATCHES) AS NUMBER_MATCHES
Example 10.20:
SELECT MAX(AMOUNT)
FROM PENALTIES
Example 10.21:
SELECT MIN(AMOUNT)
FROM PENALTIES
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford')
Example 10.22:
SELECT COUNT(*)
FROM PENALTIES
WHERE AMOUNT =
(SELECT MIN(AMOUNT)
FROM PENALTIES)
Example 10.23:
SELECT DISTINCT TEAMNO, PLAYERNO
FROM MATCHES AS M1
WHERE WON =
(SELECT MAX(WON)
FROM MATCHES AS M2
WHERE M1.TEAMNO = M2.TEAMNO)
Example 10.24:
SELECT (MAX(AMOUNT) - MIN(AMOUNT)) * 100
FROM PENALTIES
Example 10.25:
SELECT SUBSTR(MAX(NAME), 1, 1)
FROM PLAYERS
Example 10.26:
SELECT MAX(LEAGUENO)
FROM PLAYERS
WHERE TOWN = 'Midhurst'
Example 10.27:
SELECT CASE WHEN MIN(LEAGUENO) IS NULL
THEN 'Unknown'
ELSE MIN(LEAGUENO)
END
FROM PLAYERS
WHERE TOWN = 'Amsterdam'
Example 10.28:
SELECT PLAYERNO, AMOUNT, PAYMENT_DATE
FROM PENALTIES AS PEN1
WHERE AMOUNT =
(SELECT MAX(AMOUNT)
FROM PENALTIES AS PEN2
WHERE PEN2.PLAYERNO = PEN1.PLAYERNO)
Example 10.29:
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 10.30:
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 10.31:
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 10.32:
SELECT AVG(AMOUNT)
FROM PENALTIES
WHERE PLAYERNO = 44
Example 10.33:
SELECT DISTINCT PLAYERNO
FROM PENALTIES
WHERE AMOUNT >
(SELECT AVG(AMOUNT)
FROM PENALTIES)
Example 10.34:
SELECT AVG(DISTINCT AMOUNT)
FROM PENALTIES
Example 10.35:
SELECT AVG(LENGTH(NAME)), MAX(LENGTH(NAME))
FROM PLAYERS
Example 10.36:
SELECT PAYMENTNO, AMOUNT,
ABS(AMOUNT - (SELECT AVG(AMOUNT)
FROM PENALTIES)) AS DIFFERENCE
FROM PENALTIES AS P
Answer 10.4:
SELECT COUNT(*), MAX(AMOUNT)
FROM PENALTIES
Answer 10.5:
SELECT COUNT(DISTINCT POSITION)
FROM COMMITTEE_MEMBERS
Answer 10.6:
SELECT COUNT(LEAGUENO)
FROM PLAYERS
WHERE TOWN = 'Inglewood'
Answer 10.7:
SELECT TEAMNO, DIVISION,
(SELECT COUNT(*)
FROM MATCHES
WHERE TEAMS.TEAMNO = MATCHES.TEAMNO)
FROM TEAMS
Answer 10.8:
SELECT PLAYERNO, NAME,
(SELECT COUNT(*)
FROM MATCHES
WHERE MATCHES.PLAYERNO = PLAYERS.PLAYERNO
AND WON > LOST)
FROM PLAYERS
Answer 10.9:
SELECT 'Number of players' AS TABLES,
(SELECT COUNT(*) FROM PLAYERS) AS NUMBERS
UNION
SELECT 'Number of teams',
(SELECT COUNT(*) FROM TEAMS)
UNION
SELECT 'Number of matches',
(SELECT COUNT(*) FROM MATCHES)
Answer 10.10:
SELECT MIN(WON)
FROM MATCHES
WHERE WON > LOST
Answer 10.11:
SELECT PLAYERNO,
(SELECT MAX(AMOUNT)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO =
PLAYERS.PLAYERNO) -
(SELECT MIN(AMOUNT)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO =
PLAYERS.PLAYERNO)
FROM PLAYERS
Answer 10.12:
SELECT PLAYERNO, BIRTH_DATE
FROM PLAYERS
WHERE STRFTIME('%Y', BIRTH_DATE) =
(SELECT MAX(STRFTIME('%Y', BIRTH_DATE))
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO = 1))
Answer 10.14:
SELECT AVG(AMOUNT)
FROM PENALTIES
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO = 1)
Answer 10.15:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE (SELECT SUM(AMOUNT)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO) > 100
Answer 10.16:
SELECT NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE WON >
(SELECT SUM(WON)
FROM MATCHES
WHERE PLAYERNO = 27))
Answer 10.17:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE (SELECT SUM(WON)
FROM MATCHES
WHERE MATCHES.PLAYERNO =
PLAYERS.PLAYERNO) = 8
Answer 10.18:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE LENGTH(NAME) >
(SELECT AVG(LENGTH(NAME))
FROM PLAYERS)
Answer 10.19:
SELECT PLAYERNO,
(SELECT MAX(AMOUNT)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO =
PLAYERS.PLAYERNO) -
(SELECT AVG(AMOUNT)
FROM PENALTIES
WHERE PENALTIES.PLAYERNO =
PLAYERS.PLAYERNO)
FROM PLAYERS
Example 11.1:
SELECT TOWN
FROM PLAYERS
GROUP BY TOWN
Example 11.2:
SELECT TOWN, COUNT(*)
FROM PLAYERS
GROUP BY TOWN
Example 11.3:
SELECT TEAMNO, COUNT(*), SUM(WON)
FROM MATCHES
GROUP BY TEAMNO
Example 11.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 11.5:
SELECT AMOUNT, COUNT(*), SUM(AMOUNT)
FROM PENALTIES
GROUP BY AMOUNT
Example 11.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 11.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 11.8:
SELECT STRFTIME('%Y', PAYMENT_DATE) AS YEAR, COUNT(*)
FROM PENALTIES
GROUP BY STRFTIME('%Y', PAYMENT_DATE)
Example 11.9:
SELECT ROUND(PLAYERNO/25) AS PLAYERS_GROUP, COUNT(*), MAX(PLAYERNO)
FROM PLAYERS
GROUP BY ROUND(PLAYERNO/25)
Example 11.10:
SELECT LEAGUENO
FROM PLAYERS
GROUP BY LEAGUENO
Example 11.11:
SELECT CAST(AMOUNT * 100 AS INTEGER) AS AMOUNT_IN_CENTS
FROM PENALTIES
GROUP BY AMOUNT
Example 11.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 11.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 11.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 11.15:
SELECT GROUPS.PGROUP, SUM(P.AMOUNT)
FROM PENALTIES AS P,
(SELECT 1 AS PGROUP, '1980-01-01' AS STARTDATE, '1981-06-30' AS ENDDATE
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 STARTDATE AND ENDDATE
GROUP BY GROUPS.PGROUP
ORDER BY GROUPS.PGROUP
Example 11.16:
SELECT CASE
WHEN AMOUNT > 70.00 THEN 'SERIOUS'
WHEN AMOUNT > 35.00 THEN 'REGRETTABLE'
ELSE 'PETTY'
END AS SERIOUSNESS,
SUM(AMOUNT) AS TOTAL_PENALTY_AMOUNT
FROM PENALTIES
GROUP BY CASE
WHEN AMOUNT > 70.00 THEN 'SERIOUS'
WHEN AMOUNT > 35.00 THEN 'REGRETTABLE'
ELSE 'PETTY'
END
ORDER BY TOTAL_PENALTY_AMOUNT
Example 11.17:
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 11.18:
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
Answer 11.1:
SELECT JOINED
FROM PLAYERS
GROUP BY JOINED
Answer 11.2:
SELECT JOINED, COUNT(*)
FROM PLAYERS
GROUP BY JOINED
Answer 11.3:
SELECT PLAYERNO, AVG(AMOUNT), COUNT(*)
FROM PENALTIES
GROUP BY PLAYERNO
Answer 11.4:
SELECT TEAMNO, COUNT(*), SUM(WON)
FROM MATCHES
WHERE TEAMNO IN
(SELECT TEAMNO
FROM TEAMS
WHERE DIVISION = 'first')
GROUP BY TEAMNO
Answer 11.5:
SELECT WON, LOST, COUNT(*)
FROM MATCHES
WHERE WON > LOST
GROUP BY WON, LOST
ORDER BY WON, LOST
Answer 11.6:
SELECT P.TOWN, 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.TOWN, T.DIVISION
ORDER BY P.TOWN
Answer 11.7:
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 11.8:
SELECT T.TEAMNO, DIVISION, SUM(WON)
FROM TEAMS AS T, MATCHES AS M
WHERE T.TEAMNO = M.TEAMNO
GROUP BY T.TEAMNO, DIVISION
Answer 11.9:
SELECT LENGTH(NAME), COUNT(*)
FROM PLAYERS
GROUP BY LENGTH(NAME)
Answer 11.10:
SELECT ABS(WON - LOST), COUNT(*)
FROM MATCHES
GROUP BY ABS(WON - LOST)
Answer 11.11:
SELECT STRFTIME('%Y', BEGIN_DATE), STRFTIME('%m', BEGIN_DATE), COUNT(*)
FROM COMMITTEE_MEMBERS
GROUP BY STRFTIME('%Y', BEGIN_DATE), STRFTIME('%m', BEGIN_DATE)
ORDER BY STRFTIME('%Y', BEGIN_DATE), STRFTIME('%m', BEGIN_DATE)
Answer 11.14:
SELECT AVG(NUMBERS)
FROM (SELECT COUNT(*) AS NUMBERS
FROM PLAYERS
GROUP BY TOWN) AS TOWNS
Answer 11.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 11.16:
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 11.17:
SELECT PLAYERNO, NAME, JOINED - AVERAGE
FROM PLAYERS,
(SELECT AVG(JOINED) AS AVERAGE
FROM PLAYERS) AS T
Answer 11.18:
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
Example 12.1:
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING COUNT(*) > 1
Example 12.2:
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING MAX(STRFTIME('%Y', PAYMENT_DATE)) = '1984'
Example 12.3:
SELECT PLAYERNO, SUM(AMOUNT)
FROM PENALTIES
GROUP BY PLAYERNO
HAVING SUM(AMOUNT) > 150
Example 12.4:
SELECT PLAYERNO, SUM(AMOUNT)
FROM PENALTIES
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM TEAMS)
GROUP BY PLAYERNO
HAVING SUM(AMOUNT) > 80
Answer 12.1:
SELECT TOWN
FROM PLAYERS
GROUP BY TOWN
HAVING COUNT(*) > 4
Answer 12.2:
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING SUM(AMOUNT) > 150
Answer 12.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 12.4:
SELECT TEAMNO, DIVISION
FROM TEAMS
WHERE TEAMNO IN
(SELECT TEAMNO
FROM MATCHES
GROUP BY TEAMNO
HAVING COUNT(DISTINCT PLAYERNO) > 4)
Answer 12.5:
SELECT NAME, INITIALS
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE AMOUNT > 40
GROUP BY PLAYERNO
HAVING COUNT(*) >= 2)
Answer 12.6:
SELECT PLAYERNO
FROM PENALTIES
WHERE PLAYERNO <> 104
GROUP BY PLAYERNO
HAVING SUM(AMOUNT) =
(SELECT SUM(AMOUNT) * 2
FROM PENALTIES
WHERE PLAYERNO = 104)
Answer 12.7:
SELECT PLAYERNO
FROM PENALTIES
WHERE PLAYERNO <> 6
GROUP BY PLAYERNO
HAVING COUNT(*) =
(SELECT COUNT(*)
FROM PENALTIES
WHERE PLAYERNO = 6)
Example 13.1:
SELECT PAYMENTNO, PLAYERNO
FROM PENALTIES
ORDER BY PLAYERNO
Example 13.2:
SELECT PLAYERNO, AMOUNT
FROM PENALTIES
ORDER BY PLAYERNO, AMOUNT
Example 13.3:
SELECT AMOUNT
FROM PENALTIES
ORDER BY PLAYERNO, AMOUNT
Example 13.4:
SELECT NAME, INITIALS, PLAYERNO
FROM PLAYERS
ORDER BY SUBSTR(NAME, 1, 1)
Example 13.5:
SELECT PLAYERNO, AMOUNT
FROM PENALTIES
ORDER BY ABS(AMOUNT - (SELECT AVG(AMOUNT) FROM PENALTIES))
Example 13.6:
SELECT PLAYERNO, AMOUNT
FROM PENALTIES AS P1
ORDER BY (SELECT AVG(AMOUNT)
FROM PENALTIES AS P2
WHERE P1.PLAYERNO = P2.PLAYERNO)
Example 13.7:
SELECT PLAYERNO, SUM(AMOUNT)
FROM PENALTIES
GROUP BY PLAYERNO
ORDER BY 2
Example 13.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 13.9:
SELECT PLAYERNO, AMOUNT
FROM PENALTIES
ORDER BY PLAYERNO DESC, AMOUNT ASC
Example 13.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 13.11:
SELECT DISTINCT LEAGUENO
FROM PLAYERS
ORDER BY LEAGUENO DESC
Answer 13.3
:
SELECT PLAYERNO, TEAMNO, WON - LOST
FROM MATCHES
ORDER BY 3 ASC
Example 14.1:
SELECT MAX(PLAYERNO)
FROM PLAYERS
SELECT PLAYERNO, NAME
FROM PLAYERS AS P1
WHERE 4 >
(SELECT COUNT(*)
FROM PLAYERS AS P2
WHERE P1.PLAYERNO < P2.PLAYERNO)
ORDER BY PLAYERNO DESC
SELECT PLAYERNO, NAME
FROM PLAYERS
ORDER BY PLAYERNO DESC
LIMIT 4
Example 14.2:
SELECT LEAGUENO, PLAYERNO, NAME
FROM PLAYERS
ORDER BY LEAGUENO ASC
LIMIT 5
Example 14.3:
SELECT PLAYERNO, COUNT(*) AS NUMBER
FROM MATCHES
WHERE WON > LOST
GROUP BY PLAYERNO
ORDER BY NUMBER DESC
LIMIT 3
Example 14.4:
SELECT PLAYERNO, COUNT(*) AS NUMBER
FROM MATCHES
WHERE WON > LOST
GROUP BY PLAYERNO
ORDER BY NUMBER DESC, PLAYERNO DESC
LIMIT 3
Example 14.5:
SELECT *
FROM (SELECT PLAYERNO, COUNT(*) AS NUMBER
FROM MATCHES
WHERE WON > LOST
GROUP BY PLAYERNO
ORDER BY NUMBER DESC, PLAYERNO DESC
LIMIT 3) AS T
ORDER BY 1
Example 14.6:
SELECT AVG(AMOUNT)
FROM (SELECT AMOUNT
FROM PENALTIES
ORDER BY AMOUNT
LIMIT 4) AS T
Example 14.7:
SELECT MIN(AMOUNT)
FROM (SELECT AMOUNT
FROM PENALTIES
ORDER BY AMOUNT DESC
LIMIT 3) AS T
Example 14.8:
SELECT DISTINCT AMOUNT
FROM PENALTIES
ORDER BY AMOUNT DESC
LIMIT 3
Example 14.9:
SELECT PLAYERNO
FROM (SELECT PLAYERNO
FROM PLAYERS
WHERE LEAGUENO IS NOT NULL
ORDER BY LEAGUENO DESC
LIMIT 6) AS T
ORDER BY PLAYERNO
LIMIT 3
Example 14.10:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL
FROM PENALTIES
GROUP BY PLAYERNO
ORDER BY TOTAL DESC
LIMIT 3) AS T)
Example 14.11:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES)
AND PLAYERNO NOT IN
(SELECT PLAYERNO
FROM PENALTIES
ORDER BY AMOUNT DESC
LIMIT 2)
AND PLAYERNO NOT IN
(SELECT PLAYERNO
FROM PENALTIES
ORDER BY AMOUNT ASC
LIMIT 2)
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
WHERE PLAYERNO NOT IN
(SELECT PLAYERNO
FROM PENALTIES
ORDER BY AMOUNT DESC
LIMIT 2)
AND PLAYERNO NOT IN
(SELECT PLAYERNO
FROM PENALTIES
ORDER BY AMOUNT ASC
LIMIT 2))
Example 14.12:
SELECT PLAYERNO, NAME
FROM PLAYERS
ORDER BY PLAYERNO ASC
LIMIT 5 OFFSET 3
Answer 14.2:
SELECT MATCHNO
FROM MATCHES
WHERE MATCHNO IN
(SELECT MATCHNO
FROM MATCHES
ORDER BY MATCHNO ASC
LIMIT 2)
OR MATCHNO IN
(SELECT MATCHNO
FROM MATCHES
ORDER BY MATCHNO DESC
LIMIT 2)
Answer 14.3:
SELECT PLAYERNO, NAME
FROM (SELECT PLAYERNO, NAME
FROM PLAYERS
ORDER BY PLAYERNO ASC
LIMIT 10) AS S10
ORDER BY NAME DESC
LIMIT 5
Answer 14.4:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM (SELECT PLAYERNO, COUNT(*) AS NUMBER
FROM MATCHES
WHERE WON > LOST
GROUP BY PLAYERNO) AS WINNERS
ORDER BY NUMBER DESC, PLAYERNO ASC
LIMIT 2)
Answer 14.5:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PENALTIES.PLAYERNO
FROM PENALTIES INNER JOIN PLAYERS
ON PENALTIES.PLAYERNO = PLAYERS.PLAYERNO
ORDER BY AMOUNT DESC, NAME ASC
LIMIT 4)
Answer 14.6:
SELECT PAYMENTNO, AMOUNT
FROM PENALTIES
ORDER BY AMOUNT DESC
LIMIT 1 OFFSET 2
Example 15.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 15.2:
SELECT BIRTH_DATE AS DATES
FROM PLAYERS
UNION
SELECT PAYMENT_DATE
FROM PENALTIES
Example 15.3:
SELECT PLAYERNO
FROM PENALTIES
UNION
SELECT PLAYERNO
FROM TEAMS
Example 15.4:
SELECT PLAYERNO
FROM PENALTIES
UNION
SELECT PLAYERNO
FROM TEAMS
UNION
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 15.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 TEAMNO, PLAYERNO
Example 15.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 PLAYERNO
SELECT PLAYERNO, BIRTH_DATE
FROM PLAYERS
WHERE TOWN = 'Stratford'
AND BIRTH_DATE > '1960-12-31'
ORDER BY PLAYERNO
Example 15.7:
SELECT PLAYERNO
FROM TEAMS
INTERSECT
SELECT PLAYERNO
FROM PENALTIES
Example 15.8:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM TEAMS
INTERSECT
SELECT PLAYERNO
FROM PENALTIES)
Example 15.9:
SELECT NUMBER*NUMBER AS POWERS
FROM (SELECT CAST(DIGIT1. DIGIT || DIGIT2. DIGIT AS INTEGER) AS NUMBER
FROM (SELECT '0' AS 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' AS 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 NUMBER*NUMBER < 5000
INTERSECT
SELECT NUMBER*NUMBER*NUMBER AS POWERS
FROM (SELECT CAST(DIGIT1.DIGIT || DIGIT2.DIGIT AS INTEGER) AS NUMBER
FROM (SELECT '0' AS 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' AS 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 NUMBER*NUMBER*NUMBER < 5000
Example 15.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 PLAYERNO
SELECT PLAYERNO, BIRTH_DATE
FROM PLAYERS
WHERE TOWN = 'Stratford'
AND NOT(BIRTH_DATE > '1960-12-31')
ORDER BY PLAYERNO
Example 15.11:
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PENALTIES
EXCEPT
SELECT PLAYERNO
FROM TEAMS)
Example 15.12:
SELECT PLAYERNO
FROM PENALTIES
UNION ALL
SELECT PLAYERNO
FROM TEAMS
Answer 15.1:
SELECT PLAYERNO
FROM COMMITTEE_MEMBERS
UNION
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING COUNT(*) >= 2
Answer 15.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 15.5:
SELECT PLAYERNO
FROM COMMITTEE_MEMBERS
INTERSECT
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING COUNT(*) >= 2
Answer 15.6:
SELECT COUNT(*)
FROM (SELECT PLAYERNO
FROM COMMITTEE_MEMBERS
INTERSECT
SELECT PLAYERNO
FROM PENALTIES
GROUP BY PLAYERNO
HAVING COUNT(*) >= 2) AS PLAYERS
Answer 15.7:
SELECT PLAYERNO
FROM COMMITTEE_MEMBERS
EXCEPT
SELECT PLAYERNO
FROM PENALTIES
Answer 15.8:
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 UNION SELECT 10
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
UNION SELECT 20
EXCEPT
SELECT PAYMENTNO
FROM PENALTIES
Answer 15.9:
SELECT SUM(NUMBER)
FROM (SELECT COUNT(*) AS NUMBER
FROM PLAYERS
UNION ALL
SELECT COUNT(*) AS NUMBER
FROM TEAMS) AS NUMBERS
Answer 15.10:
SELECT DIGIT*DIGIT
FROM (SELECT 0 AS 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 DIGIT*DIGIT*DIGIT
FROM (SELECT 0 AS 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 16.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 16.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 16.3:
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 16.4:
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 16.5:
INSERT INTO RECR_PLAYERS
(PLAYERNO, NAME, TOWN, PHONENO)
SELECT PLAYERNO + 1000, NAME, TOWN, PHONENO
FROM RECR_PLAYERS
Example 16.6:
INSERT INTO PENALTIES
SELECT PAYMENTNO + 100, PLAYERNO, PAYMENT_DATE, AMOUNT
FROM PENALTIES
WHERE AMOUNT >
(SELECT AVG(AMOUNT)
FROM PENALTIES)
Example 16.7:
CREATE TABLE PENALTIES_EXTRA (
PAYMENTNO INTEGER PRIMARY KEY,
PLAYERNO INTEGER)
;
INSERT INTO PENALTIES_EXTRA VALUES (1, 2)
;
INSERT INTO PENALTIES_EXTRA VALUES (1, 4)
;
SELECT * FROM PENALTIES_EXTRA
Example 16.8:
DELETE FROM PENALTIES_EXTRA
;
INSERT INTO PENALTIES_EXTRA VALUES (5, 1000)
;
INSERT OR FAIL INTO PENALTIES_EXTRA
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES
;
SELECT * FROM PENALTIES_EXTRA
Example 16.9:
DELETE FROM PENALTIES_EXTRA
;
INSERT INTO PENALTIES_EXTRA VALUES (5, 1000)
;
INSERT OR FAIL INTO PENALTIES_EXTRA
SELECT PAYMENTNO, PLAYERNO
FROM PENALTIES
ORDER BY PAYMENTNO DESC
;
SELECT * FROM PENALTIES_EXTRA
Example 16.10:
DELETE FROM PENALTIES_EXTRA
;
INSERT INTO PENALTIES_EXTRA VALUES (5, 1000)
;
INSERT OR IGNORE INTO PENALTIES_EXTRA
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES
;
SELECT * FROM PENALTIES_EXTRA
Example 16.11:
DELETE FROM PENALTIES_EXTRA
;
INSERT INTO PENALTIES_EXTRA VALUES (5, 1000)
;
INSERT OR REPLACE INTO PENALTIES_EXTRA
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES
;
SELECT * FROM PENALTIES_EXTRA
Example 16.12:
DELETE FROM PENALTIES_EXTRA
;
INSERT INTO PENALTIES_EXTRA VALUES (5, 1000)
;
INSERT OR REPLACE INTO PENALTIES_EXTRA
SELECT 5, 100 UNION
SELECT 5, 10 UNION
SELECT 5, 1
ORDER BY 1,2 ASC
;
SELECT * FROM PENALTIES_EXTRA
Example 16.13:
UPDATE PLAYERS
SET LEAGUENO = '2000'
WHERE PLAYERNO = 95
Example 16.14:
UPDATE PENALTIES
SET AMOUNT = AMOUNT * 1.05
Example 16.15:
UPDATE MATCHES
SET WON = 0
WHERE PLAYERNO IN (SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford')
Example 16.16:
UPDATE PLAYERS
SET STREET = 'Palmer Street',
HOUSENO = '83',
TOWN = 'Inglewood',
POSTCODE = '1234UU',
PHONENO = NULL
WHERE NAME = 'Parmenter'
Example 16.17:
UPDATE PLAYERS
SET STREET = TOWN,
TOWN = STREET
WHERE PLAYERNO = 44
Example 16.18:
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
SET NUMBER_MAT =
(SELECT COUNT(*)
FROM MATCHES AS M
WHERE M.PLAYERNO = PLAYERS_DATA.PLAYERNO),
SUM_PENALTIES =
(SELECT SUM(AMOUNT)
FROM PENALTIES AS PEN
WHERE PEN.PLAYERNO = PLAYERS_DATA.PLAYERNO)
Example 16.19:
UPDATE PENALTIES
SET AMOUNT = AMOUNT + (SELECT AVG(AMOUNT)
FROM PENALTIES)
Example 16.20:
UPDATE OR CONFLICT ROLLBACK PLAYERS
SET PLAYERNO = PLAYERNO + 1
Example 16.21:
DELETE
FROM PENALTIES
WHERE PLAYERNO = 44
Example 16.22:
DELETE
FROM MATCHES
WHERE WON >
(SELECT AVG(WON)
FROM MATCHES
WHERE PLAYERNO = 83)
Example 16.23:
DELETE
FROM PLAYERS
WHERE TOWN = 'Inglewood'
LIMIT 2
Example 16.24:
DELETE
FROM PLAYERS
WHERE TOWN = 'Inglewood'
ORDER BY PLAYERNO DESC
LIMIT 2
Example 16.25:
REPLACE INTO PENALTIES_EXTRA
SELECT PAYMENTNO, PLAYERNO FROM PENALTIES
Answer 16.1:
INSERT INTO PENALTIES
VALUES (15, 27, '1985-11-08', 75)
Answer 16.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 16.3:
UPDATE PLAYERS
SET SEX = 'W'
WHERE SEX = 'F'
Answer 16.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 16.5:
UPDATE PENALTIES
SET AMOUNT = AMOUNT * 1.2
WHERE AMOUNT >
(SELECT AVG(AMOUNT)
FROM PENALTIES)
Answer 16.6:
DELETE
FROM PENALTIES
WHERE PLAYERNO = 44
AND STRFTIME('%Y', PAYMENT_DATE) = 1980
Answer 16.7:
DELETE
FROM PENALTIES
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO IN
(SELECT TEAMNO
FROM TEAMS
WHERE DIVISION = 'second'))
Answer 16.8:
DELETE
FROM PLAYERS
WHERE TOWN =
(SELECT TOWN
FROM PLAYERS
WHERE PLAYERNO = 28)
AND PLAYERNO <> 28
Example 17.1:
CREATE TABLE PLAYERS (
PLAYERNO INTEGER NOT NULL PRIMARY KEY,
NAME CHAR(15) NOT NULL,
INITIALS CHAR(3) NOT NULL,
BIRTH_DATE DATE NULL,
SEX CHAR(1) NOT NULL,
JOINED SMALLINT NOT NULL,
STREET VARCHAR(30) NOT NULL,
HOUSENO CHAR(4) NULL,
POSTCODE CHAR(6) NULL,
TOWN VARCHAR(30) NOT NULL,
PHONENO CHAR(13) NULL,
LEAGUENO CHAR(4) UNIQUE)
Example 17.2:
ATTACH DATABASE TEST AS TEST
;
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 17.3:
CREATE TABLE ODD_DATATYPES (
COL1 XYZ,
COL2 NEWTYPE,
COL3 TINYBOOLEAN)
;
PRAGMA TABLE_INFO(ODD_DATATYPES)
Example 17.4:
CREATE TABLE T1 (
COL1,
COL2)
Example 17.5:
CREATE TABLE T_ONE (MIX INTEGER)
;
INSERT INTO T_ONE VALUES (1)
;
INSERT INTO T_ONE VALUES (STRFTIME('%Y-%m-%d','2007-01-01'))
;
INSERT INTO T_ONE VALUES ('sql')
;
INSERT INTO T_ONE VALUES (123.45)
;
SELECT * FROM T_ONE
Example 17.6:
SELECT MIX, TYPEOF(MIX)
FROM T_ONE
Example 17.7:
CREATE TABLE T_TWO (COL1 CHARACTER(10))
;
INSERT INTO T_TWO VALUES (1)
;
INSERT INTO T_TWO VALUES ('1')
;
SELECT COL1, TYPEOF(COL1) FROM T_TWO
Example 17.8:
CREATE TABLE T_THREE (COL1 SMALL_NUMBERS)
;
INSERT INTO T_THREE VALUES (1)
;
INSERT INTO T_THREE VALUES (1.2)
;
INSERT INTO T_THREE VALUES ('1')
;
INSERT INTO T_THREE VALUES ('1.2')
;
INSERT INTO T_THREE VALUES ('SQLite')
;
SELECT COL1, TYPEOF(COL1) FROM T_THREE
Example 17.9:
CREATE TABLE T_FOUR (COL1 INTEGERS)
;
INSERT INTO T_FOUR VALUES (1)
;
INSERT INTO T_FOUR VALUES (1.2)
;
INSERT INTO T_FOUR VALUES (CAST(1 AS REAL))
;
INSERT INTO T_FOUR VALUES ('SQLite')
;
SELECT COL1, TYPEOF(COL1) FROM T_FOUR
Example 17.10:
CREATE TABLE T_FIVE (COL1 REALLY)
;
INSERT INTO T_FIVE VALUES (1)
;
INSERT INTO T_FIVE VALUES (1.2)
;
INSERT INTO T_FIVE VALUES ('1')
;
INSERT INTO T_FIVE VALUES ('SQLite')
;
SELECT COL1, TYPEOF(COL1) FROM T_FIVE
Example 17.11:
CREATE TABLE T_SIX (COL1)
;
INSERT INTO T_SIX VALUES (1)
;
INSERT INTO T_SIX VALUES (1.2)
;
INSERT INTO T_SIX VALUES ('1')
;
INSERT INTO T_SIX VALUES ('SQLite')
;
SELECT COL1, TYPEOF(COL1) FROM T_SIX
Example 17.12:
CREATE TEMPORARY TABLE SUMPENALTIES (
TOTAL DECIMAL(10,2))
;
INSERT INTO SUMPENALTIES
SELECT SUM(AMOUNT)
FROM PENALTIES
Example 17.13:
CREATE TABLE TESTTABLE (
COL1 INTEGER)
;
INSERT INTO TESTTABLE VALUES (1)
;
CREATE TEMPORARY TABLE TESTTABLE (
COL1 INTEGER,
COL2 INTEGER)
;
INSERT INTO TESTTABLE VALUES (2, 3)
;
SELECT * FROM TESTTABLE
SELECT * FROM MAIN.TESTTABLE
Example 17.14:
CREATE TABLE IF NOT EXISTS TEAMS (
TEAMNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
DIVISION CHAR(6) NOT NULL)
Example 17.15:
CREATE TABLE TEAMS_COPY1 AS
SELECT *
FROM TEAMS
Example 17.16:
CREATE TABLE TEAMS_COPY2 AS
SELECT TEAMNO AS TNO, PLAYERNO AS PNO, DIVISION
FROM TEAMS
;
SELECT *
FROM TEAMS_COPY2
Example 17.17:
CREATE TABLE TEAMS_COPY3 AS
SELECT TEAMNO, PLAYERNO
FROM TEAMS
WHERE PLAYERNO = 27
Example 17.18:
CREATE TEMPORARY TABLE TEAMS AS
SELECT *
FROM TEAMS
Example 17.19:
CREATE TABLE PENALTIES (
PAYMENTNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL DEFAULT '2007-01-01',
AMOUNT DECIMAL(7,2) NOT NULL DEFAULT 50.00)
;
INSERT INTO PENALTIES
(PAYMENTNO, PLAYERNO)
VALUES (15, 27)
Example 17.20:
CREATE TABLE PENALTIES (
PAYMENTNO INTEGER NOT NULL PRIMARY KEY,
PLAYERNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL DEFAULT CURRENT_DATE,
AMOUNT DECIMAL(7,2) NOT NULL DEFAULT 50.00)
Answer 17.3
:
CREATE TABLE P_COPY AS
SELECT *
FROM PLAYERS
WHERE PLAYERNO IS NULL
Answer 17.4:
CREATE TABLE P2_COPY AS SELECT * FROM PLAYERS
Answer 17.5
:
CREATE TABLE NUMBERS AS
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 18.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))
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))
Example 18.2:
CREATE TABLE DIPLOMAS (
STUDENT INTEGER NOT NULL,
COURSE INTEGER NOT NULL,
DIP_DATE DATE NOT NULL,
SUCCESSFUL CHAR(1),
LOCATION VARCHAR(50),
PRIMARY KEY (STUDENT, COURSE, DIP_DATE))
Example 18.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 18.4:
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) UNIQUE,
PRIMARY KEY (PLAYERNO))
Example 18.5:
CREATE TABLE T_ROWID (
COL1 CHARACTER NOT NULL,
COL2 CHARACTER NOT NULL)
;
INSERT INTO T_ROWID VALUES ('a', 'b')
;
INSERT INTO T_ROWID VALUES ('c', 'd')
;
SELECT ROWID, * FROM T_ROWID
Example 18.6:
INSERT INTO T_ROWID (ROWID, COL1, COL2) VALUES (NULL, 'e', 'f')
;
SELECT ROWID, * FROM T_ROWID
Example 18.7:
INSERT INTO T_ROWID (ROWID, COL1, COL2) VALUES (50, 'g', 'h')
;
SELECT ROWID, * FROM T_ROWID
Example 18.8:
UPDATE T_ROWID SET ROWID = ROWID + 100
;
INSERT INTO T_ROWID VALUES ('e', 'f')
;
SELECT ROWID, * FROM T_ROWID
INSERT INTO T_ROWID (ROWID, COL1, COL2) VALUES (ROWID, 'i', 'j')
Example 18.9:
CREATE TABLE T_ROWID2 (
COL1 INTEGER PRIMARY KEY,
COL2 CHARACTER NOT NULL,
COL3 CHARACTER NOT NULL)
;
INSERT INTO T_ROWID2 VALUES (1, 'a', 'b')
;
INSERT INTO T_ROWID2 (COL2, COL3) VALUES ('c', 'd')
;
INSERT INTO T_ROWID2 (ROWID, COL2, COL3) VALUES (3, 'e', 'f')
;
SELECT ROWID, * FROM T_ROWID2
Example 18.10:
CREATE TABLE TMAX (
COL1 INTEGER PRIMARY KEY,
COL2 CHARACTER NOT NULL)
;
INSERT INTO TMAX VALUES (1, 'a')
;
INSERT INTO TMAX VALUES (9223372036854775807, 'b')
;
INSERT INTO TMAX (COL2) VALUES ('c')
;
SELECT ROWID, COL2 FROM TMAX
Example 18.11:
CREATE TABLE TENS (COL1 INTEGER PRIMARY KEY)
;
INSERT INTO TENS (COL1) VALUES (10)
;
INSERT INTO TENS (COL1) VALUES ((SELECT MAX(COL1)+10 FROM TENS))
;
INSERT INTO TENS (COL1) VALUES ((SELECT MAX(COL1)+10 FROM TENS))
;
INSERT INTO TENS (COL1) VALUES ((SELECT MAX(COL1)+10 FROM TENS))
;
SELECT COL1 FROM TENS
Examples 18.12:
CREATE TABLE T_AUTO (
COL1 INTEGER PRIMARY KEY AUTOINCREMENT,
COL2 CHARACTER NOT NULL,
COL3 CHARACTER NOT NULL)
;
INSERT INTO T_AUTO (COL2, COL3) VALUES ('a', 'b')
;
INSERT INTO T_AUTO (COL2, COL3) VALUES ('c', 'd')
;
INSERT INTO T_AUTO (ROWID, COL2, COL3) VALUES (50, 'e', 'f')
;
DELETE FROM T_AUTO WHERE ROWID = 50;
;
INSERT INTO T_AUTO (COL2, COL3) VALUES ('g', 'h')
;
SELECT COL1, COL2, COL3 FROM T_AUTO
Example 18.13:
CREATE TABLE T_CONFLICT (
COL1 INTEGER PRIMARY KEY ON CONFLICT REPLACE,
COL2 TEXT)
;
INSERT INTO T_CONFLICT VALUES (1, 'a')
;
INSERT INTO T_CONFLICT VALUES (1, 'b')
;
SELECT * FROM T_CONFLICT
Example 18.14:
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 18.15:
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 18.16:
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 18.17:
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 18.18:
CREATE TABLE PLAYERS_WITH_PASSPORT (
PLAYERNO INTEGER NOT NULL PRIMARY KEY,
PASSPORTNO CHAR(10) NOT NULL UNIQUE,
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))
CREATE TABLE PASSPORT_VISA (
PASSPORTNO CHAR(10) NOT NULL,
VISA_CODE CHAR(10) NOT NULL,
EXPIRATION_DATE DATE NOT NULL,
PRIMARY KEY (PASSPORTNO, VISA_CODE),
FOREIGN KEY (PASSPORTNO) REFERENCES PLAYERS_WITH_PASSPORT (PASSPORTNO))
Example 18.19:
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 18.20:
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 18.21:
CREATE TABLE PENALTIES (
PAYMENTNO INTEGER NOT NULL,
PLAYERNO INTEGER,
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 18.22:
CREATE TABLE PLAYERS_X (
PLAYERNO INTEGER NOT NULL,
SEX CHAR(1) NOT NULL CHECK(SEX IN ('M', 'F')))
CREATE TABLE PLAYERS_WITH_NULL (
PLAYERNO INTEGER NOT NULL,
SEX CHAR(1) CHECK(SEX IN ('M', 'F')))
Example 18.23:
CREATE TABLE PLAYERS_Y (
PLAYERNO INTEGER NOT NULL,
BIRTH_DATE DATE NOT NULL CHECK(BIRTH_DATE > '1920-01-01'))
Example 18.24:
CREATE TABLE PLAYERS_Z (
PLAYERNO INTEGER NOT NULL,
BIRTH_DATE DATE,
JOINED INTEGER NOT NULL,
CHECK(STRFTIME('%Y', BIRTH_DATE) < JOINED))
CREATE TABLE PLAYERS_W (
PLAYERNO INTEGER,
BIRTH_DATE DATE NOT NULL,
JOINED INTEGER NOT NULL,
CHECK(STRFTIME('%Y', BIRTH_DATE) < JOINED),
CHECK(BIRTH_DATE > '1920-01-01'),
CHECK(JOINED < 1880))
Example 18.25:
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),
HOUSENO CHAR(4),
POSTCODE CHAR(6),
TOWN VARCHAR(30),
PHONE CHAR(13),
LEAGUENO CHAR(4),
PRIMARY KEY (PLAYERNO),
CHECK (((STREET IS NULL) AND (HOUSENO IS NULL) AND (POSTCODE IS NULL) AND
(TOWN IS NULL)) OR
((STREET IS NOT NULL) AND (HOUSENO IS NOT NULL) AND
(POSTCODE IS NOT NULL) AND (TOWN IS NOT NULL))) )
Example 18.26:
CREATE TABLE T_INTEGERS (
COL1 INTEGER CHECK(TYPEOF(COL1) = 'integer'))
Example 18.27:
CREATE TABLE T_NUMERICS (
COL1 REAL CHECK(TYPEOF(COL1) IN ('integer','real')))
Example 18.28:
CREATE TABLE T_ALPHAS (
COL1 TEXT CHECK(TYPEOF(COL1) = 'text'))
Example 18.29:
CREATE TABLE T_DATES (
COL1 DATE CHECK(
TYPEOF(CAST(SUBSTR(COL1,1,4) AS INTEGER))='integer' AND
SUBSTR(COL1,5,1)='-' AND
TYPEOF(CAST(SUBSTR(COL1,6,2) AS INTEGER))='integer' AND
SUBSTR(COL1,8,1)='-' AND
TYPEOF(CAST(SUBSTR(COL1,9,2) AS INTEGER))='integer' AND
LENGTH(COL1)=10))
Example 18.30:
CREATE TABLE DIPLOMAS (
STUDENT INTEGER NOT NULL,
COURSE INTEGER NOT NULL,
DDATE DATE NOT NULL,
SUCCESSFUL CHAR(1),
LOCATION VARCHAR(50),
CONSTRAINT PRIMARY_KEY_DIPLOMAS PRIMARY KEY (STUDENT, COURSE, DDATE))
Example 18.31:
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,
PHONE CHAR(13),
LEAGUENO CHAR(4),
CONSTRAINT PRIMARY_KEY_PLAYERS PRIMARY KEY(PLAYERNO),
CONSTRAINT JOINED CHECK(JOINED > 1969),
CONSTRAINT POSTCODE_SIX_CHARACTERS_LONG CHECK(POSTCODE LIKE '______'),
CONSTRAINT ALLOWED_VALUES_SEX CHECK(SEX IN ('M', 'F')))
Answer 18.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 19.2:
CREATE TABLE T1 (
COL1 TEXT,
COL2 TEXT COLLATION NOCASE)
;
INSERT INTO T1 VALUES ('a', 'a')
;
INSERT INTO T1 VALUES ('A', 'A')
Example 20.1:
DROP TABLE PLAYERS
Example 20.2:
ALTER TABLE PLAYERS RENAME TO TENNIS_PLAYERS
Example 20.3:
ALTER TABLE TEAMS
ADD COLUMN TYPE CHAR(1)
Example 21.4:
SELECT *
FROM PLAYERS
WHERE PLAYERNO = 44
Example 21.5:
SELECT PLAYERNO, TOWN
FROM PLAYERS
WHERE PLAYERNO < 10
AND TOWN = 'Stratford'
ORDER BY PLAYERNO
Example 21.6:
SELECT NAME, INITIALS
FROM PLAYERS
WHERE TOWN =
(SELECT TOWN
FROM PLAYERS
WHERE PLAYERNO = 44)
Example 21.7:
CREATE INDEX PLAY_PC
ON PLAYERS (POSTCODE ASC)
Example 21.8:
CREATE INDEX MAT_WL
ON MATCHES (WON, LOST)
Example 21.9:
CREATE UNIQUE INDEX NAMEINIT
ON PLAYERS (NAME, INITIALS)
Example 21.10:
ATTACH DATABASE 'EXTRA.DB' AS EXTRA
;
CREATE UNIQUE INDEX EXTRA.COL1_INDEX
ON TEST (COL1)
Example 21.11:
DROP INDEX PLAY_PC
DROP INDEX MAT_WL
DROP INDEX NAMEINIT
Example 21.12:
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))
Example 21.13:
CREATE TABLE FOURNUMBERS (
COL1 INTEGER NOT NULL,
COL2 INTEGER NOT NULL,
COL3 INTEGER NOT NULL,
COL4 INTEGER NOT NULL)
;
CREATE INDEX INDEXCOL1 ON FOURNUMBERS (COL1)
;
CREATE INDEX INDEXCOL2 ON FOURNUMBERS (COL2)
;
INSERT INTO FOURNUMBERS VALUES (1,1,1,1)
;
INSERT INTO FOURNUMBERS VALUES (2,2,2,2)
;
INSERT INTO FOURNUMBERS VALUES (3,3,3,3)
;
INSERT INTO FOURNUMBERS VALUES (4,4,4,4)
;
SELECT *
FROM FOURNUMBERS INDEXED BY INDEXCOL2
WHERE COL2 > 2
SELECT *
FROM FOURNUMBERS INDEXED BY INDEXCOL2
SELECT *
FROM FOURNUMBERS NOT INDEXED
SELECT *
FROM FOURNUMBERS NOT INDEXED
WHERE COL2 > 2
Example 21.14:
REINDEX PLAY_PC
Example 21.15:
ANALYZE MATCHES
SELECT *
FROM SQLITE_STAT1
WHERE TBL = 'MATCHES'
Example 22.1:
CREATE VIEW TOWNS AS
SELECT DISTINCT TOWN
FROM PLAYERS
Example 22.2:
SELECT *
FROM TOWNS
Example 22.3:
CREATE VIEW CPLAYERS AS
SELECT PLAYERNO, LEAGUENO
FROM PLAYERS
WHERE LEAGUENO IS NOT NULL
;
SELECT *
FROM CPLAYERS
Example 22.4:
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 22.5:
CREATE VIEW SEVERAL AS
SELECT *
FROM CPLAYERS
WHERE PLAYERNO BETWEEN 6 AND 27
;
SELECT *
FROM SEVERAL
Example 22.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 22.7:
DROP VIEW CPLAYERS
Example 22.8:
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 22.10:
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 AS
SELECT DISTINCT TEAMNO, CAPTAIN AS PLAYERNO, DIVISION
FROM RESULT
CREATE VIEW MATCHES AS
SELECT MATCHNO, TEAMNO, PLAYERNO, WON, LOST
FROM RESULT
CREATE VIEW GREATER AS
SELECT DISTINCT PLAYERNO
FROM PENALTIES
WHERE AMOUNT >
(SELECT AVG(AMOUNT)
FROM PENALTIES
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE TEAMNO = 2))
CREATE VIEW FIRST AS
SELECT DISTINCT PLAYERNO
FROM MATCHES
WHERE TEAMNO IN
(SELECT TEAMNO
FROM TEAMS
WHERE DIVISION = 'first')
SELECT NAME, INITIALS
FROM PLAYERS
WHERE TOWN = 'Stratford'
AND PLAYERNO IN
(SELECT PLAYERNO
FROM GREATER)
AND PLAYERNO IN
(SELECT PLAYERNO
FROM FIRST)
Example 22.11:
CREATE VIEW PLAYERSS AS
SELECT *
FROM PLAYERS
WHERE SEX IN ('M', 'F')
WITH CHECK OPTION
Answer 22.1:
CREATE VIEW NUMBERPLS AS
SELECT TEAMNO, COUNT(*) AS NUMBER
FROM MATCHES
GROUP BY TEAMNO
Answer 22.2:
CREATE VIEW WINNERS AS
SELECT PLAYERNO, NAME
FROM PLAYERS
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM MATCHES
WHERE WON > LOST)
Answer 22.3:
CREATE VIEW TOTALS AS
SELECT PLAYERNO, SUM(AMOUNT) AS SUM_PENALTIES
FROM PENALTIES
GROUP BY PLAYERNO
Example 23.1:
CREATE TABLE CHANGES (
CHA_SEQNO INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
CHA_TIME DATETIME NOT NULL,
CHA_PLAYERNO INTEGER NOT NULL,
CHA_TYPE CHAR(1) NOT NULL,
CHA_LEAGUENO_OLD CHAR(4),
CHA_LEAGUENO_NEW CHAR(4))
Example 23.2:
CREATE TRIGGER INSERT_PLAYERS
AFTER
INSERT ON PLAYERS FOR EACH ROW
BEGIN
INSERT INTO CHANGES
(CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_OLD, CHA_LEAGUENO_NEW)
VALUES (CURRENT_TIMESTAMP, NEW.PLAYERNO, 'I', NULL, NEW.LEAGUENO);
END
INSERT INTO PLAYERS VALUES
(2000, 'Brown', 'C', '1959-06-25', 'M', 1978, 'Moscow Avenue', '80', '5674BF',
'Stratford', '070-346734', '6377')
SELECT CHA_SEQNO AS SEQNO, CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,
CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD
FROM CHANGES
WHERE CHA_PLAYERNO = 2000
AND CHA_TIME =
(SELECT MAX(CHA_TIME)
FROM CHANGES
WHERE CHA_PLAYERNO = 2000)
Example 23.3:
CREATE TRIGGER DELETE_PLAYERS
AFTER DELETE ON PLAYERS FOR EACH ROW
BEGIN
INSERT INTO CHANGES
(CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)
VALUES (CURRENT_TIMESTAMP, OLD.PLAYERNO, 'D', NULL, OLD.LEAGUENO);
END
Example 23.4:
DELETE
FROM PLAYERS
WHERE PLAYERNO = 2000
SELECT CHA_SEQNO AS SEQNO, CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,
CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD
FROM CHANGES
WHERE CHA_PLAYERNO = 2000
AND CHA_TIME =
(SELECT MAX(CHA_TIME)
FROM CHANGES
WHERE CHA_PLAYERNO = 2000)
Example 23.5:
CREATE TRIGGER UPDATE_PLAYERS
AFTER UPDATE ON PLAYERS FOR EACH ROW
BEGIN
INSERT INTO CHANGES
(CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)
VALUES (CURRENT_TIMESTAMP, NEW.PLAYERNO, 'U', NEW.LEAGUENO, OLD.LEAGUENO);
END
Example 23.6:
UPDATE PLAYERS
SET LEAGUENO = '4444'
WHERE PLAYERNO = 6
;
SELECT CHA_SEQNO AS SEQNO, CHA_PLAYERNO AS PLAYERNO, CHA_TYPE AS TYPE,
CHA_LEAGUENO_NEW AS LNEW, CHA_LEAGUENO_OLD AS LOLD
FROM CHANGES
WHERE CHA_PLAYERNO = 6
AND CHA_TIME =
(SELECT MAX(CHA_TIME)
FROM CHANGES
WHERE CHA_PLAYERNO = 6)
Example 23.7:
CREATE TRIGGER UPDATE_PLAYERS2
AFTER UPDATE OF LEAGUENO ON PLAYERS FOR EACH ROW
BEGIN
INSERT INTO CHANGES
(CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)
VALUES (CURRENT_TIMESTAMP, NEW.PLAYERNO, 'U', NEW.LEAGUENO, OLD.LEAGUENO);
END
Example 23.8:
CREATE TRIGGER UPDATE_PLAYERS3
AFTER UPDATE OF LEAGUENO ON PLAYERS FOR EACH ROW
WHEN ( OLD.PLAYERNO > 100 )
BEGIN
INSERT INTO CHANGES
(CHA_TIME, CHA_PLAYERNO, CHA_TYPE, CHA_LEAGUENO_NEW, CHA_LEAGUENO_OLD)
VALUES (CURRENT_TIMESTAMP, NEW.PLAYERNO, 'U', NEW.LEAGUENO, OLD.LEAGUENO);
END
Example 23.9:
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 23.10:
CREATE TRIGGER INSERT_PLAYERS_MAT
AFTER INSERT ON PLAYERS FOR EACH ROW
BEGIN
INSERT INTO PLAYERS_MAT
VALUES(NEW.PLAYERNO, 0);
END
Example 23.11:
INSERT INTO PLAYERS VALUES
(16, 'Parmenter', 'S', '1964-06-25', 'F', 1977, 'Haseltine Lane',
'80', '1234KK', 'Stratford', '070-476537', '8467')
;
SELECT *
FROM PLAYERS_MAT
WHERE PLAYERNO = 16
Example 23.12:
CREATE TRIGGER DELETE_PLAYERS
AFTER DELETE ON PLAYERS FOR EACH ROW
BEGIN
DELETE FROM PLAYERS_MAT
WHERE PLAYERNO = OLD.PLAYERNO;
END
Example 23.13:
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 23.14:
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 23.15:
CREATE TABLE TEAMS2 (
TEAMNO INTEGER PRIMARY KEY)
CREATE TRIGGER INSERT_TEAMS2
AFTER INSERT ON TEAMS FOR EACH ROW
BEGIN
SELECT RAISE(ABORT, 'More than 5 rows not allowed.')
FROM (SELECT 1)
WHERE (SELECT COUNT(*) FROM TEAMS2) >= 5;
INSERT INTO TEAMS2 VALUES (NEW.TEAMNO);
END
Example 23.16:
CREATE TRIGGER INSERT_TEAMS2
AFTER INSERT ON TEAMS2 FOR EACH ROW
BEGIN
INSERT INTO TEAMS2 VALUES (NEW.TEAMNO);
SELECT RAISE(ROLLBACK, 'More than 5 rows not allowed.')
FROM (SELECT 1)
WHERE (SELECT COUNT(*) FROM TEAMS2) > 5;
END
Example 23.17:
CREATE TRIGGER BORN_VS_JOINED
BEFORE INSERT ON PLAYERS FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'The birth year should be less than the year joined.')
FROM (SELECT 1)
WHERE STRFTIME('%Y', NEW.BIRTH_DATE) >= NEW.JOINED;
END
Example 23.18:
CREATE TRIGGER FOREIGN_KEY1
BEFORE INSERT ON PENALTIES FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK,
'The player in the PENALTIES table must appear in the PLAYERS table.')
FROM (SELECT 1)
WHERE (SELECT COUNT(*)
FROM PLAYERS
WHERE PLAYERNO = NEW.PLAYERNO) = 0;
END
CREATE TRIGGER FOREIGN_KEY2
BEFORE DELETE ON PLAYERS FOR EACH ROW
BEGIN
DELETE
FROM PENALTIES
WHERE PLAYERNO = OLD.PLAYERNO;
END
Example 23.19:
CREATE TABLE TAB_PRIMKEY1 (
COL1 INTEGER NOT NULL PRIMARY KEY)
;
CREATE TABLE TAB_FORKEY1 (
COL1 INTEGER NOT NULL PRIMARY KEY,
COL2 INTEGER REFERENCES TAB_PRIMKEY1(COL1))
;
.genfkey
Example 23.20:
CREATE TABLE TAB_PRIMKEY2 (
COL1 INTEGER NOT NULL PRIMARY KEY)
;
CREATE TABLE TAB_FORKEY2 (
COL1 INTEGER NOT NULL PRIMARY KEY,
COL2 INTEGER REFERENCES TAB_PRIMKEY2(COL1)
ON UPDATE CASCADE
ON DELETE CASCADE)
;
.genfkey
Example 23.21:
CREATE TABLE TAB_PRIMKEY3 (
COL1 INTEGER NOT NULL PRIMARY KEY)
;
CREATE TABLE TAB_FORKEY3 (
COL1 INTEGER NOT NULL PRIMARY KEY,
COL2 INTEGER REFERENCES TAB_PRIMKEY3(COL1)
ON UPDATE SET NULL
ON DELETE SET NULL)
;
.genfkey
CREATE TABLE TAB_PRIMKEY4 (
COL1 INTEGER NOT NULL PRIMARY KEY)
;
CREATE TABLE TAB_FORKEY4 (
COL1 INTEGER NOT NULL PRIMARY KEY,
COL2 INTEGER REFERENCES TAB_PRIMKEY4(COL1)
ON UPDATE SET DEFAULT
ON DELETE SET DEFAULT)
;
.genfkey
Example 23.22:
DROP TRIGGER BORN_VS_JOINED
Example 24.1:
Logging on
Logging on has succeeded.\n";
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.2:
Two connections
Logging on to tennis2.db has succeeded.\n";
if ($conn2 = sqlite_open('tennis3.db', 0666, $sqliteerror)) {
echo "Logging on to tennis3.db has succeeded.
\n";
sqlite_close($conn2);
} else {
echo "Logging on to tennis3.db has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
sqlite_close($conn1);
} else {
echo "Logging on to tennis2.db has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.3:
Create Index
Logging on has succeeded.\n";
$sqlins = sqlite_exec($conn, "CREATE UNIQUE INDEX PLAY ON PLAYERS (PLAYERNO)");
if (!$sqlins) {
echo "Index PLAY is not created!
\n";
} else {
echo "Index PLAY is created!
\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not successed.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.4:
Error messages
Logging on has succeeded.\n";
$sqlins = sqlite_exec($conn, "CREATE UNIQUE INDEX PLAY ON PLAYERS (PLAYERNO)");
if (!$sqlins) {
echo "Index PLAY is not created!
\n";
echo "
Error code = ".sqlite_last_error($conn)."\n";
echo "
Error text = ".sqlite_error_string(sqlite_last_error($conn))."\n";
} else {
echo "Index PLAY is created!
\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.5:
Parameters
Logging on has succeeded.\n";
$pno = 12;
$sqlins = sqlite_exec($conn,
"UPDATE MATCHES
SET WON = WON + 1
WHERE MATCHNO = $mno");
if (!$sqlins) {
echo "
Update is not executed!\n";
echo "
Error code = ".sqlite_last_error($conn)."\n";
echo "
Error text = ".sqlite_error_string(sqlite_last_error($conn))."\n";
} else {
echo "
WON column is increased for match $mno.\n";
echo "
Number of rows has changed: ".sqlite_changes($conn)."\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.6:
Develop a PHP program that presents the number of players in the PLAYERS table.
Query with a row
Logging on has succeeded.\n";
$query = "SELECT COUNT(*) AS NUMBER FROM PLAYERS";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
$rij = sqlite_fetch_array($sqlins) or die ("
Query had no result.\n");
echo "
The number of players is ".$row['NUMBER'].".\n";
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}?>
Example 24.7:
Query with multiple rows
Logging on has succeeded.\n";
$query = "SELECT PLAYERNO FROM PLAYERS ORDER BY 1 DESC";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
if (sqlite_num_rows($sqlins) > 0) {
echo "
The total number of players is ".sqlite_num_rows($sqlins)."\n";
while ($row = sqlite_fetch_array($sqlins)) {
echo "
Player number ".$row['PLAYERNO'].".\n";
}
} else {
echo "
No players found.\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.8:
Query all rows together
Logging on has succeeded.\n";
$query = "SELECT PLAYERNO FROM PLAYERS ORDER BY 1 DESC";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
$rows = sqlite_fetch_all($sqlins, SQLITE_ASSOC);
foreach ($rows as $row) {
echo "
Player number ".$row['PLAYERNO].".\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.9:
SEEK function
Logging on has succeeded.\n";
$query = "SELECT PLAYERNO FROM PLAYERS ORDER BY 1 DESC";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
sqlite_seek($sqlins,3);
$row = sqlite_current($sqlins);
echo "
Player number ".$row['PLAYERNO'].".\n";
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.10:
Null values
Logging on has succeeded.\n";
$query = "SELECT NULL AS NOTHING";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
$row = sqlite_fetch_array($sqlins) or die ("
Query had no result.\n");
if ($row['NOTHING'] === NULL) {
echo "
The result is NULL.\n";
} else {
echo "
The result is not NULL.\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 24.11:
Develop a PHP program with a scalar function that squares a number and use that function in a SELECT statement.
UDF
Logging on has succeeded.\n";
sqlite_create_function($conn, 'power', 'power', 1);
echo "The functiin is ready.
\n";
$query = "SELECT power(PLAYERNO) AS SQUARE FROM PLAYERS ORDER BY 1 DESC";
$sqlins = sqlite_query($conn, $query) or die ("
Query is incorrect.\n");
if (sqlite_num_rows($sqlins) > 0) {
echo "
The total number of players is ".sqlite_num_rows($sqlins)."\n";
while ($row = sqlite_fetch_array($sqlins)) {
echo "
Squares ".$row['SQUARE'].".\n";
}
} else {
echo "
No player found.\n";
}
sqlite_close($conn);
} else {
echo "Logging on has not succeeded.\n
";
echo "
Error code: ".$sqliteerror;
}
?>
Example 25.1:
BEGIN TRANSACTION
DELETE
FROM PENALTIES
WHERE PLAYERNO = 44
SELECT *
FROM PENALTIES
ROLLBACK
SELECT *
FROM PENALTIES
COMMIT
Example 25.3:
DELETE FROM PLAYERS WHERE PLAYERNO = 6
DELETE FROM PENALTIES WHERE PLAYERNO = 6
DELETE FROM MATCHES WHERE PLAYERNO = 6
DELETE FROM COMMITTEE_MEMBERS WHERE PLAYERNO = 6
UPDATE TEAMS SET PLAYERNO = 83 WHERE PLAYERNO = 6
Example 25.4:
UPDATE PENALTIES
SET AMOUNT = AMOUNT + 25
WHERE PAYMENTNO = 4
SELECT *
FROM PENALTIES
WHERE PAYMENTNO = 4
Example 25.5:
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
UPDATE PLAYERS
SET TOWN = 'Eltham'
WHERE PLAYERNO = 7
SELECT PLAYERNO, NAME, INITIALS, STREET, HOUSENO, POSTCODE, TOWN
FROM PLAYERS
WHERE PLAYERNO IN (6, 83, 2, 7, 57, 39, 100)
Example 25.6:
SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford'
Example 25.7:
UPDATE PENALTIES
SET AMOUNT = AMOUNT + 25
WHERE PAYMENTNO = 4
UPDATE PENALTIES
SET AMOUNT = AMOUNT + 30
WHERE PAYMENTNO = 4