ضمائم راهنمای SQL

ضمائم شامل پایگاه دادهٔ تمرین، پاسخ تمرین‌ها، خلاصهٔ نحو، نقدی کوتاه، مطالعهٔ پیشنهادی و نمایه است.

پایگاه دادهٔ تمرین

جدول‌های زیر پایگاه دادهٔ منطقیِ همهٔ مثال‌ها و تمرین‌ها را تشکیل می‌دهند. توجه کنید جدول‌های واقعی نصب‌شده در کلاس شما ردیف‌های بسیار بیشتری خواهند داشت.

PERSONS
PERSON	NAME	BDATE	GENDER	COUNTRY	JOB
1	Einstein	1879-03-20	Male	Germany	S
2	Dickinson	1812-07-22	Male	England	W
3	Dickinson	1830-07-15	Female	USA	W

COUNTRIES
COUNTRY	POP	AREA	GNP	LANGUAGE	LITERACY
Germany	81337541	137823	1331000	German	100
USA	263814032	3679192	6380000	English	95
England	58295119	94251	980200	English	99

ARMIES
COUNTRY	BUDGET	TROOPS	TANKS	SHIPS	PLANES
Germany	35200	367300	2855	32	300
USA	280600	1650500	14524	230	10600
England	41700	254300	921	52	340

JOBS
JOB	TITLE
S	Scientist
E	Entertainer
W	Writer

RELIGIONS
COUNTRY	RELIGION	PERCENT
Germany	Protestant	45
Germany	Catholic	37
England	Catholic	30

POP بر حسب نفر، AREA بر حسب مایل مربع و GNP بر حسب میلیون دلار است. LANGUAGE زبان اصلی کشور و LITERACY درصد باسوادی است. BUDGET بر حسب میلیون دلار و TROOPS فقط نیروهای فعال است. این جدول‌ها فقط برای مقاصد تصویری/آموزشی هستند؛ در تولید این پایگاه داده هیچ حیوانی آسیب ندیده است.

پاسخ تمرین‌ها

صفحهٔ ۱۰

مهربان‌ترین و بزرگوارترین مدرس، آیا ممکن است آموزش کوتاهی دربارهٔ استفاده از پایگاه دادهٔ کلاس به ما بدهید؟

SELECT *
FROM PERSONS

SELECT *
FROM COUNTRIES

SELECT *
FROM ERRORS

این پرس‌وجو یک خطای نحوی ایجاد می‌کند.

SELECT *
FROM ARMIES

SELECT *
FROM MYCOLUMNS

SELECT *
FROM HOW-WOULD-WE-KNOW-WHAT-YOU-PICKED

صفحهٔ ۱۲

SELECT JOB
FROM JOBS

SELECT NAME, BDATE
FROM PERSONS

SELECT PERCENT, RELIGION, COUNTRY
FROM RELIGIONS

SELECT COUNTRY, POP, AREA, GNP, LANGUAGE, LITERACY, COUNTRY
FROM COUNTRIES

SELECT *, COUNTRY
FROM COUNTRIES

صفحهٔ ۱۶

SELECT *
FROM COUNTRIES

SELECT *
FROM COUNTRIES
WHERE AREA < 30

SELECT NAME, COUNTRY
FROM PERSONS

SELECT NAME, COUNTRY
FROM PERSONS
WHERE COUNTRY = 'CANADA'

SELECT NAME, BDATE
FROM PERSONS
WHERE BDATE > '1964-01-01'

SELECT BDATE
FROM PERSONS
WHERE NAME = 'O''TOOLE'

به دو کوتیشن متوالی در رشته توجه کنید. تاریخ تولد O'Toole برابر 1932-08-12 است.

صفحهٔ ۱۹

SELECT *
FROM PERSONS
WHERE COUNTRY = 'IRELAND'

SELECT *
FROM PERSONS
WHERE COUNTRY = 'IRELAND'
ORDER BY NAME

SELECT *
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'

SELECT *
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'
ORDER BY GNP DESC

SELECT COUNTRY, JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
ORDER BY NAME

SELECT *
FROM ARMIES
ORDER BY BUDGET DESC

ایالات متحده بزرگ‌ترین بودجه را دارد.

SELECT *
FROM ARMIES
ORDER BY BUDGET

ویتنام کوچک‌ترین بودجه را دارد. چین بیشترین نیرو، روسیه بیشترین تانک و کشتی، و ایالات متحده بیشترین هواپیما را دارد.

صفحهٔ ۲۱

SELECT JOB
FROM PERSONS

SELECT DISTINCT JOB
FROM PERSONS

SELECT LANGUAGE
FROM COUNTRIES
WHERE LITERACY < 30
ORDER BY LANGUAGE

SELECT DISTINCT LANGUAGE
FROM COUNTRIES
WHERE LITERACY < 30
ORDER BY LANGUAGE

SELECT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
ORDER BY COUNTRY

SELECT DISTINCT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
ORDER BY COUNTRY

SELECT RELIGION
FROM RELIGIONS
WHERE PERCENT < 5
ORDER BY RELIGION

SELECT DISTINCT RELIGION
FROM RELIGIONS
WHERE PERCENT < 5
ORDER BY RELIGION

صفحهٔ ۲۴

SELECT COUNTRY
FROM COUNTRIES
WHERE COUNTRY LIKE '%GUINEA%'

SELECT *
FROM PERSONS
WHERE NAME LIKE '_Z%'

SELECT *
FROM PERSONS
WHERE BDATE LIKE '%07-15'

SELECT *
FROM PERSONS
WHERE NAME LIKE '%''%'

SELECT DISTINCT RELIGION
FROM RELIGIONS
WHERE RELIGION LIKE '%ORTHODOX%'
ORDER BY RELIGION

صفحهٔ ۲۶

SELECT *
FROM PERSONS
WHERE JOB = 'S'
AND COUNTRY = 'GERMANY'

SELECT *
FROM COUNTRIES
WHERE GNP < 3000
AND LITERACY < 40
ORDER BY GNP

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY >= 55
AND LITERACY <= 60

SELECT *
FROM PERSONS
WHERE COUNTRY = 'ENGLAND'
AND BDATE LIKE '15%'
ORDER BY NAME

SELECT *
FROM ARMIES
WHERE TROOPS > 100000
AND TANKS < 1000
AND SHIPS < 1000
AND PLANES < 1000

صفحهٔ ۲۸

SELECT *
FROM COUNTRIES
WHERE AREA BETWEEN 5 AND 75

SELECT *
FROM COUNTRIES
WHERE POP BETWEEN 100000 AND 200000

SELECT *
FROM ARMIES
WHERE PLANES BETWEEN 301 AND 399

SELECT *
FROM ARMIES
WHERE TROOPS >= 300000
AND BUDGET BETWEEN 10000 AND 100000

صفحهٔ ۳۰

SELECT *
FROM ARMIES
WHERE COUNTRY = 'ISRAEL'
OR COUNTRY = 'IRAQ'

SELECT *
FROM PERSONS
WHERE BDATE = '1835-10-19'
OR BDATE = '1917-02-06'

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'POE'
OR NAME = 'HUGO'
OR NAME = 'DAHL'

SELECT *
FROM PERSONS
WHERE BDATE LIKE '12%'
OR BDATE LIKE '14%'

پنج نفر در این دوره متولد شده‌اند.

صفحهٔ ۳۲

SELECT *
FROM PERSONS
WHERE NAME IN ('EINSTEIN','GALILEI','NEWTON')

SELECT *
FROM COUNTRIES
WHERE LITERACY IN (20,40,60)

SELECT NAME, COUNTRY
FROM PERSONS
WHERE JOB = 'S'
AND COUNTRY IN ('GERMANY','AUSTRIA','ITALY')

SELECT *
FROM PERSONS
WHERE JOB IN ('W','E','B')
AND COUNTRY IN ('GERMANY','AUSTRIA','ITALY')

صفحهٔ ۳۴

SELECT *
FROM COUNTRIES
WHERE GNP IS NULL

SELECT *
FROM PERSONS
WHERE JOB = 'E'
AND GENDER IS NULL

SELECT *
FROM PERSONS
WHERE COUNTRY = 'ISRAEL'
AND BDATE IS NULL

SELECT *
FROM COUNTRIES
WHERE AREA < 10
AND GNP >= 250

یک کشور چنین وضعی دارد.

SELECT *
FROM COUNTRIES
WHERE AREA < 10
AND GNP <= 250

دو کشور چنین وضعی دارند.

SELECT *
FROM COUNTRIES
WHERE AREA < 10

ممکن است پاسخ را ۳ حدس بزنید، اما تعداد کل ردیف‌ها ۴ است. یک کشور GNP برابر NULL دارد و بنابراین در هیچ‌یک از دو پرس‌وجوی نخست ظاهر نمی‌شود.

صفحهٔ ۳۶

SELECT *
FROM PERSONS
WHERE COUNTRY = 'GERMANY'
AND ( JOB = 'T' OR JOB = 'B' )
ORDER BY NAME

SELECT *
FROM PERSONS
WHERE COUNTRY = 'GERMANY'
AND NOT ( JOB = 'T' OR JOB = 'B' )
ORDER BY NAME

SELECT *
FROM ARMIES
WHERE COUNTRY NOT IN ('USA','RUSSIA')
AND BUDGET > 30000

سه ردیف در نتیجه وجود دارد.

صفحهٔ ۴۰

SELECT POP * .2
FROM COUNTRIES
WHERE COUNTRY = 'CANADA'

۵٬۶۸۶٬۹۰۹ کانادایی-آمریکایی.

SELECT BUDGET * 1000000 / TROOPS
FROM ARMIES
WHERE COUNTRY = 'USA'

در ایالات متحده ۱۷۰٬۰۰۰٫۰۹ دلار به ازای هر سرباز هزینه می‌شود.

SELECT BUDGET * 1000000 / TROOPS
FROM ARMIES
WHERE COUNTRY = 'CHINA'

در چین ۹۲۱٫۵۰ دلار به ازای هر سرباز هزینه می‌شود.

SELECT TANKS + SHIPS + PLANES
FROM ARMIES
WHERE COUNTRY = 'USA'

مجموع ۲۵٬۳۵۴ وسیلهٔ نظامی.

SELECT BUDGET * 1000000 * .45 / 125
FROM ARMIES
WHERE COUNTRY = 'USA'

۱٬۰۱۰٬۱۶۰٬۰۰۰ چکش!

صفحهٔ ۴۲

SELECT COUNTRY, POP, AREA, POP / AREA
FROM COUNTRIES
WHERE POP / AREA < 7
ORDER BY POP / AREA DESC

SELECT COUNTRY, POP, LITERACY, LITERACY / 100 * POP
FROM COUNTRIES
WHERE LITERACY / 100 * POP > 100000000

SELECT COUNTRY, BUDGET * 1000000 * .3 / 650 / TROOPS
FROM ARMIES
WHERE BUDGET * 1000000 * .3 / 650 / TROOPS > 10
ORDER BY BUDGET * 1000000 * .3 / 650 / TROOPS DESC

صفحهٔ ۴۴

SELECT COUNTRY, POP, LITERACY,
       LITERACY / 100 * POP AS READERS
FROM COUNTRIES
WHERE READERS > 100000000

SELECT COUNTRY, POP, GNP,
       GNP * 1000000 / POP AS GPP
FROM COUNTRIES
WHERE GPP > 20000
ORDER BY GPP DESC

SELECT COUNTRY,
       TROOPS * .20 * 20000 AS FISSION_FUND
FROM ARMIES
WHERE FISSION_FUND >= 2000000000
ORDER BY FISSION_FUND DESC

۱۱ کشور مشارکت‌کننده وجود دارد.

صفحهٔ ۴۸

SELECT SUM(TROOPS)
FROM ARMIES

SELECT MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
WHERE LANGUAGE = 'FRENCH'

SELECT COUNT(COUNTRY), COUNT(DISTINCT LANGUAGE)
FROM COUNTRIES

صفحهٔ ۵۰

SELECT LANGUAGE, SUM(POP)
FROM COUNTRIES
WHERE LANGUAGE IN ('HEBREW','SPANISH','ENGLISH','FRENCH')
GROUP BY LANGUAGE

SELECT LANGUAGE, MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
WHERE LANGUAGE IN ('HEBREW','SPANISH','ENGLISH','FRENCH')
GROUP BY LANGUAGE

SELECT COUNTRY, GENDER, COUNT(PERSON)
FROM PERSONS
WHERE COUNTRY IN ('CANADA','FRANCE')
GROUP BY COUNTRY, GENDER
ORDER BY COUNTRY, GENDER

SELECT JOB, GENDER, COUNT(PERSON)
FROM PERSONS
WHERE NOT GENDER IS NULL
GROUP BY JOB, GENDER
ORDER BY JOB, GENDER

صفحهٔ ۵۳

SELECT COUNTRY, COUNT(PERSON)
FROM PERSONS
WHERE JOB = 'W'
GROUP BY COUNTRY
ORDER BY COUNT(PERSON)

SELECT LANGUAGE, SUM(POP)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING SUM(POP) < 1000000

SELECT LANGUAGE, MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING MIN(LITERACY) <> MAX(LITERACY)

SELECT LANGUAGE, SUM(AREA), SUM(POP)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY SUM(AREA) DESC

صفحهٔ ۵۸

SELECT NAME, LANGUAGE
FROM PERSONS, COUNTRIES
WHERE PERSONS.COUNTRY = COUNTRIES.COUNTRY
AND JOB = 'S'
AND BDATE LIKE '19%'

SELECT COUNTRIES.COUNTRY, GNP, BUDGET
FROM COUNTRIES, ARMIES
WHERE COUNTRIES.COUNTRY = ARMIES.COUNTRY
AND POP > 100000000

SELECT COUNTRIES.COUNTRY, GNP, BUDGET, BUDGET / GNP * 100
FROM COUNTRIES, ARMIES
WHERE COUNTRIES.COUNTRY = ARMIES.COUNTRY
AND POP > 100000000

SELECT TROOPS * PERCENT / 100
FROM ARMIES, RELIGIONS
WHERE ARMIES.COUNTRY = RELIGIONS.COUNTRY
AND RELIGIONS.COUNTRY = 'GERMANY'
AND RELIGION = 'PROTESTANT'

با فرض اینکه نیروهای ارتش برشی از کل جمعیت باشند، حدود ۱۶۵٬۲۸۵ نفر از آن‌ها پروتستان‌اند.

صفحهٔ ۶۰

SELECT C.COUNTRY, C.POP, TROOPS
FROM COUNTRIES AS C, ARMIES AS A
WHERE C.COUNTRY = A.COUNTRY
AND POP > 100000000

SELECT DISTINCT LANGUAGE
FROM COUNTRIES AS C, RELIGIONS AS R
WHERE C.COUNTRY = R.COUNTRY
AND RELIGION = 'MUSLIM'
AND PERCENT > 90

SELECT NAME, TITLE, LANGUAGE
FROM PERSONS AS P, JOBS AS J, COUNTRIES AS C
WHERE P.JOB = J.JOB
AND P.COUNTRY = C.COUNTRY
AND BDATE < '1400-01-01'

صفحهٔ ۶۲

SELECT COUNTRY
FROM RELIGIONS
WHERE RELIGION = 'PROTESTANT'
AND PERCENT > 40
UNION
SELECT COUNTRY
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'

۱۰ ردیف در نتیجه وجود دارد.

SELECT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
UNION
SELECT COUNTRY
FROM ARMIES
WHERE BUDGET > 10000
ORDER BY 1

SELECT NAME
FROM PERSONS
WHERE NAME LIKE '_Z%'
UNION
SELECT COUNTRY
FROM COUNTRIES
WHERE COUNTRY LIKE '_Z%'
UNION
SELECT LANGUAGE
FROM COUNTRIES
WHERE LANGUAGE LIKE '_Z%'

۹ ردیف در نتیجه وجود دارد.

صفحهٔ ۶۵

SELECT MAX(POP)
FROM COUNTRIES

SELECT *
FROM COUNTRIES
WHERE POP = ( SELECT MAX(POP) FROM COUNTRIES )

چین برنده است.

SELECT *
FROM ARMIES
WHERE BUDGET > ( SELECT AVG(BUDGET) FROM ARMIES )

SELECT LANGUAGE, NAME, TITLE, BDATE
FROM PERSONS AS P, JOBS AS J, COUNTRIES AS C
WHERE P.JOB = J.JOB
AND P.COUNTRY = C.COUNTRY
AND P.JOB = ( SELECT JOB FROM PERSONS WHERE NAME = 'LUTHER' )
ORDER BY LANGUAGE, NAME

صفحهٔ ۶۷

SELECT COUNTRY
FROM COUNTRIES
WHERE LITERACY < 50

SELECT *
FROM PERSONS
WHERE JOB = 'W'
AND COUNTRY IN ( SELECT COUNTRY FROM COUNTRIES WHERE LITERACY < 50 )

SELECT *
FROM PERSONS
WHERE COUNTRY IN ( SELECT COUNTRY FROM RELIGIONS
                   WHERE RELIGION = 'CATHOLIC' AND PERCENT > 95 )

SELECT *
FROM PERSONS
WHERE JOB = 'M'
AND COUNTRY IN ( SELECT COUNTRY FROM COUNTRIES WHERE LANGUAGE = 'ENGLISH' )
AND COUNTRY IN ( SELECT COUNTRY FROM ARMIES
                 WHERE TROOPS < ( SELECT AVG(TROOPS) FROM ARMIES ) )

صفحهٔ ۶۹

SELECT JOB, NAME
FROM PERSONS AS P
WHERE BDATE = ( SELECT MAX(BDATE)
                FROM PERSONS
                WHERE JOB = P.JOB )

SELECT *
FROM PERSONS AS P
WHERE BDATE = ( SELECT MAX(BDATE)
                FROM PERSONS
                WHERE GENDER = P.GENDER )

هر دو نفر اهل Liechtenstein و عضو خانوادهٔ سلطنتی‌اند، هر دو در نیمهٔ نخست سال متولد شده‌اند و نام‌هایی تقریباً تلفظ‌ناپذیر دارند.

SELECT COUNTRY, RELIGION
FROM RELIGIONS AS R
WHERE PERCENT = ( SELECT MAX(PERCENT)
                  FROM RELIGIONS
                  WHERE COUNTRY = R.COUNTRY )
AND COUNTRY IN ( SELECT COUNTRY FROM COUNTRIES WHERE LANGUAGE = 'GERMAN' )

صفحهٔ ۷۲

SELECT *
FROM JOBS

INSERT
INTO JOBS ( JOB, TITLE )
VALUES ( 'A', 'Author' )

SELECT *
FROM PERSONS
WHERE PERSON >= 500

INSERT
INTO PERSONS ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
VALUES ( 500, 'name', 'bdate', 'gen', 'country', 'job' )

INSERT
INTO PERSONS ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
VALUES ( 501, 'name', 'bdate', 'gen', 'country', 'job' )

صفحهٔ ۷۴

UPDATE PERSONS
SET JOB = 'A'

SELECT *
FROM PERSONS
ORDER BY JOB

UPDATE PERSONS
SET BDATE = 'bdate', JOB = 'E'
WHERE PERSON = 500

SELECT *
FROM PERSONS
WHERE PERSON = 500

UPDATE PERSONS
SET JOB = 'W'
WHERE JOB = 'A'

SELECT *
FROM PERSONS
ORDER BY JOB

صفحهٔ ۷۶

DELETE
FROM PERSONS
WHERE PERSON >= 501

DELETE
FROM PERSONS
WHERE PERSON = 500

SELECT *
FROM PERSONS
WHERE PERSON >= 500

هیچ ردیفی.

SELECT COUNT(*)
FROM PERSONS
WHERE BDATE LIKE '17%'

DELETE
FROM PERSONS
WHERE BDATE LIKE '17%'

SELECT COUNT(*)
FROM PERSONS
WHERE BDATE LIKE '17%'

SELECT COUNT(*)
FROM RELIGIONS

DELETE
FROM RELIGIONS
WHERE RELIGION LIKE '%ORTHODOX%'

SELECT COUNT(*)
FROM RELIGIONS

باید ۳۶۹ ردیف باقی بماند.

صفحهٔ ۷۸

ROLLBACK

SELECT * FROM JOBS
-- 'A' should no longer be in JOBS
SELECT COUNT(*) FROM RELIGIONS

DELETE FROM PERSONS
DELETE FROM ARMIES
SELECT * FROM PERSONS
SELECT * FROM ARMIES
-- They're gone!
ROLLBACK
SELECT * FROM PERSONS
SELECT * FROM ARMIES
-- They're back!

INSERT
INTO PERSONS ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
VALUES ( 500, 'name', 'bdate', 'gen', 'country', 'job' )
COMMIT
ROLLBACK
SELECT * FROM PERSONS WHERE PERSON = 500
-- You're still there!
DELETE FROM PERSONS WHERE PERSON = 500
COMMIT

صفحهٔ ۸۱

CREATE TABLE SCIENTISTS
(
  NAME CHAR(20),
  BDATE DATE,
  GENDER CHAR(6),
  COUNTRY CHAR(20)
)

INSERT
INTO SCIENTISTS ( NAME, BDATE, GENDER, COUNTRY )
VALUES ( 'name', 'bdate', 'gen', 'country' )

SELECT *
FROM SCIENTISTS

DROP TABLE SCIENTISTS

SELECT *
FROM SCIENTISTS

صفحهٔ ۸۳

CREATE TABLE SCIENTISTS
( NAME CHAR(20), BDATE DATE, GENDER CHAR(6), COUNTRY CHAR(20) )

INSERT
INTO SCIENTISTS ( NAME, BDATE, GENDER, COUNTRY )
( SELECT NAME, BDATE, GENDER, COUNTRY
  FROM PERSONS
  WHERE JOB = 'S' AND GENDER = 'FEMALE' )

SELECT COUNT(*) FROM SCIENTISTS

۱ دانشمند زن وجود دارد.

INSERT
INTO SCIENTISTS ( NAME, BDATE, GENDER, COUNTRY )
( SELECT NAME, BDATE, GENDER, COUNTRY
  FROM PERSONS
  WHERE JOB = 'S' AND GENDER = 'MALE' )
SELECT COUNT(*) FROM SCIENTISTS

اکنون ۲۹ دانشمند وجود دارد.

INSERT
INTO SCIENTISTS ( NAME, BDATE, GENDER, COUNTRY )
( SELECT NAME, BDATE, GENDER, COUNTRY
  FROM PERSONS
  WHERE JOB = 'T' )
SELECT COUNT(*) FROM SCIENTISTS

در مجموع ۴۲ دانشمند وجود دارد.

DROP TABLE SCIENTISTS

صفحهٔ ۸۷

CREATE TABLE THEOLOGIANS
(
  NAME CHAR(20),
  BDATE DATE,
  GENDER CHAR(6) NOT NULL DEFAULT 'Male',
  COUNTRY CHAR(20),
  CHECK ( GENDER IN ('Male','Female') ),
  PRIMARY KEY ( NAME ),
  FOREIGN KEY ( COUNTRY ) REFERENCES COUNTRIES ( COUNTRY )
)

INSERT
INTO THEOLOGIANS ( NAME, BDATE, GENDER, COUNTRY )
( SELECT NAME, BDATE, GENDER, COUNTRY
  FROM PERSONS
  WHERE JOB = 'T' )

INSERT
INTO THEOLOGIANS ( NAME, BDATE, GENDER, COUNTRY )
VALUES ( 'name', 'bdate', 'gen', 'Prussia' )

INSERT
INTO THEOLOGIANS ( NAME, BDATE, GENDER, COUNTRY )
VALUES ( 'name', 'bdate', NULL, 'country' )

DROP TABLE THEOLOGIANS

صفحهٔ ۸۹

CREATE TABLE REGIONS ( NAME CHAR(10) )
INSERT INTO REGIONS ( NAME ) VALUES ( 'North' )
INSERT INTO REGIONS ( NAME ) VALUES ( 'South' )
INSERT INTO REGIONS ( NAME ) VALUES ( 'East' )
INSERT INTO REGIONS ( NAME ) VALUES ( 'West' )

-- Execute the following statement 14 times:
INSERT INTO REGIONS SELECT * FROM REGIONS

SELECT NAME, COUNT(*)
FROM REGIONS
GROUP BY NAME

در متن، اجرای این پرس‌وجو بدون ایندکس تقریباً ۴۰ ثانیه طول می‌کشد؛ بسته به رایانه ممکن است زمان متفاوت باشد.

CREATE INDEX RX
ON REGIONS ( NAME )

اکنون پرس‌وجو باید بسیار سریع‌تر اجرا شود، زیرا ایندکس برای هر گروه ردیف‌ها را سریع‌تر پیدا می‌کند.

DROP INDEX RX
DROP TABLE REGIONS

صفحهٔ ۹۱

CREATE VIEW IV ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
AS
SELECT *
FROM PERSONS
WHERE COUNTRY = 'ISRAEL'

SELECT * FROM IV

INSERT
INTO IV ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
VALUES ( 502, 'name', 'bdate', 'gen', 'Israel', 'job' )

INSERT
INTO IV ( PERSON, NAME, BDATE, GENDER, COUNTRY, JOB )
VALUES ( 503, 'name', 'bdate', 'gen', 'USA', 'job' )

دوست «گمشده» در جدول زیرین PERSONS درج شده است، اما چون شرط تعریف View را برآورده نمی‌کند در View دیده نمی‌شود.

CREATE VIEW IJV ( NAME, BDATE, COUNTRY, TITLE )
AS
SELECT NAME, BDATE, COUNTRY, TITLE
FROM IV, JOBS
WHERE IV.JOB = JOBS.JOB

DROP VIEW IJV
DROP VIEW IV

خداحافظ. Farewell. Aufwiedersein. Shalom.

امتیاز اضافه — پاسخ پرسش‌های فراتر از متن

این ستون شامل پاسخ به پرسش‌های مهمی است که فراتر از دامنهٔ این متن قرار دارند.

بله، انسان‌ها بد هستند. به‌گفتهٔ متن، این موضوع را می‌توان از سه راه دید: الف) کتاب مقدس، ب) استدلال، و ج) تجربه. در بخش «کتاب مقدس» نویسنده به نقل‌قولی از ارمیا اشاره می‌کند که قلب انسان را فریبکار و شدیداً شرور توصیف می‌کند. در بخش «استدلال» می‌پرسد اگر انسان‌ها اساساً خوب‌اند، چرا وسایل خود را قفل می‌کنیم؟ و در بخش «تجربه» به این نکته اشاره می‌کند که کودکان بدون آموزش، دروغ‌گفتن، خودخواهی و تنبلی را کشف می‌کنند.
خیر؛ به‌گفتهٔ متن، انسان‌های اساساً بد مناسب بهشت نیستند، زیرا آن مکان را خراب خواهند کرد.
به‌گفتهٔ متن، «خود قدیمی» باید بمیرد و «خود جدید» جای آن نصب شود. این کار کاملاً عمل خدا توصیف می‌شود و فرد آن را مانند نوزادی که دست‌ها و پاهایش را کشف می‌کند، کشف می‌کند.
اصطلاح الهیاتی این فرایند «regeneration» است و معمولاً «دوباره متولد شدن» نامیده می‌شود.
خیر؛ متن می‌گوید افراد بسیار کمی این تولد جدید را تجربه می‌کنند و برای مثال به نجات هشت نفر در زمان نوح و نجات لوط و دو دخترش اشاره می‌کند.
خیر؛ متن می‌گوید «پایان جهان» هنوز زمان زیادی فاصله دارد، اما عصر کنونیِ فیض به‌سرعت رو به پایان است و تأخیر توصیه نمی‌شود.
کل طرح در کتاب مقدس شرح داده شده است. متن اضافه می‌کند که کتاب The Truth نیز با درخواست در دسترس است.

خلاصهٔ نحو

دستورهای SQL بر اساس دسته
CATEGORY	STATEMENT	PURPOSE
Query	SELECT	Display rows of one or more tables
Maintenance	INSERT	Add rows to a table
Maintenance	UPDATE	Change rows in a table
Maintenance	DELETE	Remove rows from a table
Definition	CREATE	Add tables, indices, views
Definition	DROP	Remove tables, indices, views

مقادیر بر اساس دسته
CATEGORY	DESCRIPTION	EXAMPLES
NUMERIC	positive values	3, +12
NUMERIC	negative values	-7, -1024000
NUMERIC	decimal values	3.141519, -.96
NON-NUMERIC	single words	'Chamberlin', 'SELECT'
NON-NUMERIC	multiple words	'We love SQL', 'The LORD is good to me'
NON-NUMERIC	single quotes	'10 O''Clock', 'I don''t know'
DATE	'yyyy-mm-dd' format	'1996-01-01', '1996-12-31'

عملگرهای مقایسه
OPERATOR	MEANING	EXAMPLE
=	Equal to	NAME = 'EINSTEIN'
<>	Not equal to	BDATE <> '1944-05-02'
<	Less than	POP < 100000
<=	Less than or equal to	NAME <= 'O''Grady'
>	Greater than	AREA > 999
>=	Greater than or equal to	BDATE >= '1962-06-19'

عملگرهای حسابی
OPERATOR	MEANING	EXAMPLE
+	Add	2 + 2
-	Subtract	BDATE - 365
*	Multiply	POP * 1.25
/	Divide	PERCENT / 100
( )	Precedence	2 + ( 4 / 2 )

توابع آماری
FUNCTION	MEANING	EXAMPLE
COUNT( )	Count all rows	COUNT(*)
COUNT( )	Count non-null rows	COUNT(JOB)
COUNT( )	Count unique rows	COUNT(DISTINCT JOB)
SUM( )	Total value	SUM(POP)
MIN( )	Smallest value	MIN(POP)
MAX( )	Largest value	MAX(POP)
AVG( )	Average value	AVG(POP / AREA)

خلاصهٔ کامل دستور SELECT
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
[ DISTINCT ]	col [ AS alias ] list	NAME, PERSONS.JOB, TITLE AS POS
	expr [ AS alias ] list	POP / AREA AS DENSITY
	func [ AS alias ] list	MIN( POP ) AS LOWEST
FROM	table [ AS alias ] list	PERSONS, JOBS AS J
WHERE	col oper value	AREA > 3000000
[ NOT ]	col LIKE pattern	NAME LIKE '%ST__N%'
	cmpr AND cmpr	NAME LIKE '%ST__N%' AND JOB = 'S'
	col BETWEEN i AND j	LITERACY BETWEEN 55 AND 60
	cmpr OR cmpr	NAME = 'LUTHER' OR NAME = 'CALVIN'
	col IN ( value list )	NAME IN ('POE','HUGO','DAHL')
	col IS NULL	POP IS NULL
	( compound cmpr )	( JOB = 'S' OR JOB = 'W' )
	expr oper value	POP / AREA > 300
	col oper ( subquery )	POP > ( SELECT AVG ( POP ) ... )
	col IN ( subquery )	JOB IN ( SELECT DISTINCT JOB ... )
GROUP BY	col list	JOB, COUNTRY
HAVING	comparisons with funcs	COUNT(*) > 30
UNION	query	SELECT ...
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
	pos [ DESC ] list	1 DESC, 3
	expr [ DESC ] list	POP / AREA DESC
	func [ DESC ] list	COUNT(*) DESC

دستور INSERT
CLAUSE	PARAMETERS	EXAMPLE
INSERT
INTO	table ( col list )	COUNTRIES ( COUNTRY, POP )
VALUES	( value list )	( 'Beulah', 144000 )
	( query )	( SELECT ... )

دستور UPDATE
CLAUSE	PARAMETERS	EXAMPLE
UPDATE	table	COUNTRIES
SET	new value list	AREA = 7992, LANGUAGE = 'Hebrew'
WHERE	comparisons	COUNTRY = 'Beulah'

دستور DELETE
CLAUSE	PARAMETERS	EXAMPLE
DELETE
FROM	table	COUNTRIES
WHERE	comparisons	COUNTRY = 'Yugoslavia'

دستور ROLLBACK
CLAUSE	PARAMETERS	EXAMPLE
ROLLBACK

دستور COMMIT
CLAUSE	PARAMETERS	EXAMPLE
COMMIT

دستور CREATE TABLE با قیود
CLAUSE	PARAMETERS	EXAMPLE
CREATE	TABLE table	TABLE WRITERS
(	col type ( size ) list	NAME CHAR(20), BDATE DATE, SALES NUMERIC(9,2)
	NOT NULL	NOT NULL
	DEFAULT value	DEFAULT 0
UNIQUE	( col list )	( NAME )
CHECK	( comparison )	( GENDER IN ('Male','Female') )
PRIMARY KEY	( col list )	( NAME )
FOREIGN KEY	( col list ) REFERENCES table ( col list )	( COUNTRY ) REFERENCES COUNTRIES ( COUNTRY )
)

دستور CREATE INDEX
CLAUSE	PARAMETERS	EXAMPLE
CREATE	INDEX index	INDEX GJX
ON	table ( col list )	PERSONS ( GENDER, JOB )

دستور CREATE VIEW
CLAUSE	PARAMETERS	EXAMPLE
CREATE	VIEW view ( col list )	VIEW PTV ( PERSON, NAME, TITLE )
AS	query	SELECT ...

دستورهای DROP
CLAUSE	PARAMETERS	EXAMPLE
DROP	TABLE table	TABLE WRITERS
DROP	INDEX index	INDEX GJX
DROP	VIEW view	VIEW PTV

نقدی کوتاه

با وجود محبوبیت SQL و پذیرش آن به‌عنوان یک استاندارد رسمی، متن معتقد است این زبان تعریف نسبتاً ضعیفی دارد و از چند ناسازگاری داخلی رنج می‌برد، از جمله:

بند SELECT پیش از بند FROM می‌آید؛ در حالی‌که از دید منطقی ابتدا جدول‌ها انتخاب می‌شوند و سپس ستون‌هایی که باید نمایش داده شوند.
کلیدواژهٔ DISTINCT روی ردیف‌ها عمل می‌کند، اما باید در بند SELECT ــ جایی که ستون‌ها و نه ردیف‌ها فهرست می‌شوند ــ نوشته شود.
ستاره برای چند هدف استفاده می‌شود و نحو SQL را بی‌جهت پیچیده می‌کند؛ برای نمونه SELECT *, POP*1.1, COUNT(*).
توابع را نمی‌توان آزادانه تو در تو کرد؛ برای نمونه AVG(POP/AREA) مجاز است، اما AVG(COUNT(RELIGION)) نیست.
SQL هنگام اعمال توابع آماری مانند AVG، مقادیر Null را نادیده می‌گیرد، به‌جای آن‌که گزینه‌ای برای نادیده‌گرفتن آن‌ها یا واردکردنشان در شمارش فراهم کند.
متن پیشنهاد می‌کند GROUP BY اختیاری باشد، چون اطلاعات لازم معمولاً از بند SELECT قابل استنباط است.
بند HAVING گیج‌کننده توصیف می‌شود؛ متن پیشنهاد می‌کند توابع آماری در WHERE مجاز شوند و HAVING به‌کلی حذف شود.
نحو انواع مختلف دستورهای SQL ناسازگار است؛ برای نمونه شکل SELECT، INSERT، UPDATE و DELETE از الگوی واحدی پیروی نمی‌کند.
تعریف View نمی‌تواند بند ORDER BY داشته باشد.

نتیجهٔ متن این است که اگر برخی گویش‌های SQL بعضی از این محدودیت‌ها را رفع کنند، خودِ وجود گویش‌ها مسئله را نشان می‌دهد؛ باید یک استاندارد خوب و واحد وجود داشته باشد.

مطالعهٔ پیشنهادی

An Introduction to Database Systems

C. J. Date — به‌گفتهٔ متن، احتمالاً بهترین کتاب همه‌جانبه دربارهٔ پایگاه‌های دادهٔ رابطه‌ای؛ به‌عنوان مرور مقدماتی مفید و همچنین به‌عنوان مرجع بسیار خوب است. نویسنده این کتاب را به هر کسی که به فناوری پایگاه دادهٔ رابطه‌ای علاقه‌مند است توصیه می‌کند.

A Guide to the SQL Standard

C. J. Date — اثر دیگری از Date که موجز، دقیق و نسبتاً آسان برای خواندن توصیف شده است. متن یادآوری می‌کند که او چند کتاب دیگر دربارهٔ نسخه‌های مشخص SQL نیز نوشته یا در نگارش آن‌ها مشارکت داشته و ممکن است برای مسائل خاص محصول مفیدتر باشند.

ERA and LOGIC Workshop Manuals

Relational Systems Corporation — دو روش طراحی پایگاه داده که طبق متن برای طراحی سریع و دقیق یک پایگاه دادهٔ رابطه‌ای و پیاده‌سازی آن روی هر سیستم SQL مناسب‌اند. ERA برای سیستم‌های عملیاتی ترجیح داده شده و LOGIC برای کاربردهای انبار داده.

The Holy Bible

God — متن با لحنی طنزآمیز آن را «راهنمای کاربر برای همهٔ فرزندان آدم» می‌نامد؛ اثری آزموده، مناسب مطالعهٔ روزانه، با داستان‌های خوب و حتی کمی سرگرم‌کننده، و در پایان به آیهٔ Genesis 11 اشاره می‌کند.

نمایه

نمایهٔ زیر مدخل‌های منبع و شماره‌صفحه‌های چاپی کتاب را حفظ می‌کند.

alias, 43, 51, 59
AND, 25
ANSI, 5
arithmetic operator, 38
AS, 43, 51, 59, 90
asterisk (*), 9, 46
AVG, 46

BETWEEN, 27

calculated column, 38
Chamberlin, D.D., 5
CHECK, 85
clause, 7
column, 4
column alias, 43
column list, 11, 71
column position, 17, 61
COMMIT, 77
comparison, 14, 85
comparison operator, 14
compound, 25, 29
constraint, 84
correlated subquery, 68
COUNT, 46
course objectives, 3
CREATE, 6, 80, 88, 90
critique, 116

Date, C.J., 117
date value, 13
defining, 6
definition, 6
DEFAULT, 84
default value, 71
DELETE, 75
DESC, 17
DISTINCT, 20, 46
DROP, 6, 80, 88, 90
duplicate row, 20, 61

ERA, 117
expression, 38, 39

FOREIGN KEY, 86
FROM, 9, 57, 59
full column name, 55
function, statistical, 46

God, 117
grand total, 46
GROUP BY, 49

HAVING, 52

IN, 31, 66
index, 88
inner query, 68
INSERT, 6, 71, 82
integrity constraint, 84
INTO, 71
IS NULL, 33
ISO, 5

join, 55

keyword, 7

LIKE, 23
loading tables, 82
LOGIC, 117

maintaining, 3
maintenance, 6
MAX, 46
MIN, 46
multi-valued subquery, 66

negation, 35
non-numeric value, 13
NOT, 35
NOT NULL, 84
null, 33
numeric value, 13

operand, 38
operator, 14, 38
OR, 29
ORDER BY, 17, 41, 51, 61
outer query, 68

parameter, 7, 48
parentheses, 31, 35, 38
pattern, 23
percent (%), 23
precedence, 35
PRIMARY KEY, 86, 87

query, 6
querying, 3
quote ('), 13

range, 27
REFERENCES, 86
regeneration, 110
relational database, 4
ROLLBACK, 77
row, 4

save, 77
SELECT, 6, 9-69
SET, 73
single-quote ('), 13
single-valued subquery, 64
sorting, 17
SQL, 5
statistical function, 46
Structured Query Language, 5
subquery, correlated, 68
subquery, multi-valued, 66
subquery, single-valued, 64
subtotal, 49
SUM, 46
syntax summary, 111

table, 4, 80
table alias, 59, 68
table display, 110
total, grand, 46
transaction, 77

underscore (_), 23
UNION, 61
UNIQUE, 85
UPDATE, 6, 73

value list, 31, 71
value range, 27
VALUES, 71
view, 90
view, non-numeric, 13
value, numeric, 13
view, numeric, 13
view, 90

WHERE, 15, 41, 57

ضمائم SQL؛ پایگاه داده تمرین، پاسخ تمرین‌ها، خلاصه نحو، نقد و منابع