الدرس · مقدمة في لغة SQL · IT1220

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

SQL history and data definition: domain types, create table, integrity constraints, drop and alter.
Basic query structure: select, from, where; rename, string matching, ordering, between, duplicates.
Set operations, null values and three-valued logic, aggregate functions with group by and having.
Nested subqueries: in / not in, some, all, exists, unique, subqueries in from, with, scalar subqueries.
Modifying the database: delete, insert, update and case.

أقسام الفصل

1SQL history and the data-definition languageتاريخ SQL ولغة تعريف البيانات
2Domain types in SQLأنواع النطاقات في SQL
3Create table, integrity constraints, drop and alterإنشاء الجداول والقيود والحذف والتعديل
4Basic query structure: select, from, whereبنية الاستعلام: select وfrom وwhere
5Rename, string operations, ordering and betweenإعادة التسمية والنصوص والترتيب وbetween
6Duplicates and set operationsالتكرار وعمليات المجموعات
7Null values and three-valued logicالقيم null والمنطق الثلاثي
8Aggregate functions, group by and havingدوال التجميع وgroup by وhaving
9Nested subqueries: membership, some, allالاستعلامات الفرعية: الانتماء وsome وall
10exists, not exists and uniqueexists وnot exists وunique
11Subqueries in from, the with clause and scalar subqueriesالاستعلام الفرعي في from وwith والاستعلام القياسي
12Modification of the database: delete, insert, updateتعديل القاعدة: الحذف والإدراج والتحديث

تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.

SQL and its data-definition languageSQL ولغة التعريف

SQL history and the data-definition language

History (slide 3)

IBM Sequel language developed as part of the System R project at the IBM San Jose Research Laboratory.
Renamed Structured Query Language (SQL).
ANSI and ISO standard SQL: SQL-86, SQL-89, SQL-92, SQL:1999 (language name became Y2K compliant!), SQL:2003.
Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. Not all examples here may work on your particular system.

The SQL DDL specifies (slide 4)

The schema for each relation.
The domain of values associated with each attribute.
Integrity constraints.
Also: the set of indices, security and authorization information, and the physical storage structure of each relation on disk.

Domain typesأنواع البيانات

Domain types in SQL (slide 5)

Typeالنوع	Meaningالمعنى
char(n)	Fixed length character string, with user-specified length n.
varchar(n)	Variable length character strings, with user-specified maximum length n.
int	Integer (a finite subset of the integers that is machine-dependent).
smallint	Small integer (a machine-dependent subset of the integer domain type).
numeric(p,d)	Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point.
real, double precision	Floating point and double-precision floating point numbers, with machine-dependent precision.
float(n)	Floating point number, with user-specified precision of at least n digits.

More are covered in Chapter 4.

المسألة

Which values can numeric(3,1) store exactly: 44.5, 444.5, 0.32? (slide 5)

Attribute data types and domains in SQLأنواع البيانات

Domain types in SQL

Attribute data types and domains in SQL (slides 6–8). The last line of each "Used" cell is the slide's example declaration.

Typeالفئة	SQL Data Typeنوع SQL	Usedالاستخدام
Numeric	INTEGER or INT and SMALLINT	Integer numbers of various sizes. NoRoom SMALLINT
Numeric	FLOAT or REAL and DOUBLE PRECISION	Floating-point (real) numbers of various precision
Numeric	NUMERIC (i, j) or DECIMAL (i, j)	Formatted numbers, where i the precision and j the scale. salary DECIMAL(7,2)
Character-string	CHAR(n) or CHARACTER(n)	Fixed length, where n is the number of characters. Bno CHAR(4)
Character-string	VARCHAR2(n) or CHAR VARYING(n) or CHARACTER VARYING(n)	Varying length, where n is the maximum number of characters. Name VERCHAR2(15)
Bit-string	BIT(n)	Fixed length, where n is the number of bits (0 or 1). BitSt BIT(4)
Bit-string	BIT VARYING (n)	Varying length, where n is the maximum number of bits. BitSt BIT VARYING(4)
Boolean	BOOLEAN	There are three values (TRUE, FALSE and UNKNOWN)
Date_Time	DATE	Has ten positions. Made up of year-month-day in the format yyyy-mm-dd. ViewDate DATE
Date_Time	TIME	Made up of hour:minute:second in the format hh:mm:ss. ViewTime TIME
Date_Time	TIME(i)	Made up of hour:minute:second plus i additional digits specifying fractions of a second. Format is hh:mm:ss:i

create table and integrity constraintsإنشاء الجداول

Create table, integrity constraints, drop and alter

create table

create table r (A1 D1, A2 D2, …, An Dn, (integrity-constraint1), …, (integrity-constraintk)) — r is the relation name, each Ai an attribute name, Di the data type of the domain of Ai.

Integrity constraints (slide 10)

not null
primary key (A1, …, An)
foreign key (Am, …, An) references r

من الشرائح

create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2));
create table instructor (ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department (dept_name));

A primary key declaration on an attribute automatically ensures not null.

More definitions, drop and alterdrop وalter

Create table, integrity constraints, drop and alter

من الشرائح

create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department (dept_name));
create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year), foreign key (ID) references student, foreign key (course_id, sec_id, semester, year) references section);
create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department (dept_name));

takes (slide 11)

primary key (ID, course_id, sec_id, semester, year); foreign key (ID) references student; foreign key (course_id, sec_id, semester, year) references section.
Note: sec_id can be dropped from the primary key to ensure a student cannot be registered for two sections of the same course in the same semester.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

drop table rحذف الجدول

Removes the relation r.

alter table r add A Dإضافة صفة

Adds attribute A with domain D; all existing tuples are assigned null as the value for the new attribute.

alter table r drop Aحذف صفة

Removes attribute A; dropping of attributes is not supported by many databases.

من الشرائح

ALTER TABLE student ADD Email varchar(255);
ALTER TABLE student MODIFY Email varchar(100);

select, from, whereselect وfrom وwhere

Basic query structure

select A1, …, An from r1, …, rm where P — Ai attributes, ri relations, P a predicate. The result of an SQL query is a relation.

Each clause and its relational-algebra counterpart (slides 15, 20, 21)

Clauseالجملة	Roleدورها	Relational algebraالمقابل في الجبر العلائقي
select	Lists the attributes desired in the result	Projection (Π)
from	Lists the relations involved in the query	Cartesian product (×)
where	Specifies conditions the result must satisfy	Selection predicate (σ)

SQL names are case insensitive (Name ≡ NAME ≡ name).
Conditions combine with and, or, not: where dept_name = 'Comp. Sci.' and salary > 80000.

من الشرائح

select name from instructor where dept_name = 'Comp. Sci.';
select name from instructor where dept_name = 'Comp. Sci.' and salary > 80000;
Comparisons can be applied to results of arithmetic expressions.

Inside the select clauseجملة select

Basic query structure: select, from, where

SQL allows duplicates in relations and query results; select distinct dept_name removes duplicates; select all keeps them.
An asterisk select * denotes "all attributes".
A literal: select '437' returns one column and a single row; select '437' as FOO names the column; select 'A' from instructor returns N rows (one per instructor tuple), each "A".
Arithmetic: select ID, name, salary/12 as monthly_salary from instructor.

instructor (slide 17)

ID	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	65000
12121	Wu	Finance	90000
15151	Mozart	Music	40000
22222	Einstein	Physics	95000
32343	El Said	History	60000
33456	Gold	Physics	87000
45565	Katz	Comp. Sci.	75000
58583	Califieri	History	62000
76543	Singh	Finance	80000
76766	Crick	Biology	72000
83821	Brandt	Comp. Sci.	92000
98345	Kim	Elec. Eng.	80000

Figure 3.3 (slide 17): result of "select dept_name from instructor" — duplicates kept

dept_name
Comp. Sci.
Finance
Music
Physics
History
Physics
Comp. Sci.
History
Finance
Biology
Comp. Sci.
Elec. Eng.

Combining relations in fromالربط في from

Basic query structure: select, from, where

select * from instructor, teaches generates every possible instructor–teaches pair; common attributes are renamed with the relation name (instructor.ID). The Cartesian product is useful combined with a where condition.

teaches (slide 22)

ID	course_id	sec_id	semester	year
10101	CS-101	1	Fall	2009
10101	CS-315	1	Spring	2010
10101	CS-347	1	Fall	2009
12121	FIN-201	1	Spring	2010
15151	MU-199	1	Spring	2010
22222	PHY-101	1	Fall	2009

instructor × teaches (slide 22) — the slide shows only these rows; "…" marks rows it leaves out

Inst.ID	name	dept_name	salary	teaches.ID	course_id	sec_id	semester	year
10101	Srinivasan	Comp. Sci.	65000	10101	CS-101	1	Fall	2009
10101	Srinivasan	Comp. Sci.	65000	10101	CS-315	1	Spring	2010
10101	Srinivasan	Comp. Sci.	65000	10101	CS-347	1	Fall	2009
10101	Srinivasan	Comp. Sci.	65000	12121	FIN-201	1	Spring	2010
10101	Srinivasan	Comp. Sci.	65000	15151	MU-199	1	Spring	2010
10101	Srinivasan	Comp. Sci.	65000	22222	PHY-101	1	Fall	2009
…	…	…	…	…	…	…	…	…
…	…	…	…	…	…	…	…	…
12121	Wu	Finance	90000	10101	CS-101	1	Fall	2009
12121	Wu	Finance	90000	10101	CS-315	1	Spring	2010
12121	Wu	Finance	90000	10101	CS-347	1	Fall	2009
12121	Wu	Finance	90000	12121	FIN-201	1	Spring	2010
12121	Wu	Finance	90000	15151	MU-199	1	Spring	2010
12121	Wu	Finance	90000	22222	PHY-101	1	Fall	2009
…	…	…	…	…	…	…	…	…
…	…	…	…	…	…	…	…	…

من الشرائح

select name, course_id from instructor, teaches where instructor.ID = teaches.ID;
select name, course_id from instructor, teaches where instructor.ID = teaches.ID and instructor.dept_name = 'Art';

Names with department and building (Figure 3.5)الشكل 3.5

Basic query structure: select, from, where

Figure 3.5: the result of "Retrieve the names of all instructors, along with their department names and department building name." It combines the instructor table with the department table.

(a) The instructor table (slide 24)

ID	name	dept_name	salary
22222	Einstein	Physics	95000
12121	Wu	Finance	90000
32343	El Said	History	60000
45565	Katz	Comp. Sci.	75000
98345	Kim	Elec. Eng.	80000
76766	Crick	Biology	72000
10101	Srinivasan	Comp. Sci.	65000
58583	Califieri	History	62000
83821	Brandt	Comp. Sci.	92000
15151	Mozart	Music	40000
33456	Gold	Physics	87000
76543	Singh	Finance	80000

(b) The department table (slide 24)

dept_name	building	budget
Comp. Sci.	Taylor	100000
Biology	Watson	90000
Elec. Eng.	Taylor	85000
Music	Packard	80000
Finance	Painter	120000
History	Painter	50000
Physics	Watson	70000

Figure 3.5: the result (slide 24)

name	dept_name	building
Srinivasan	Comp. Sci.	Taylor
Wu	Finance	Painter
Mozart	Music	Packard
Einstein	Physics	Watson
El Said	History	Painter
Gold	Physics	Watson
Katz	Comp. Sci.	Taylor
Califieri	History	Painter
Singh	Finance	Painter
Crick	Biology	Watson
Brandt	Comp. Sci.	Taylor
Kim	Elec. Eng.	Taylor

Rename and the self-joinإعادة التسمية

Rename, string operations, ordering and between

as

old-name as new-name renames relations and attributes; the keyword as is optional (instructor as T ≡ instructor T).

من الشرائح

select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Comp. Sci.';

emp_super (slide 26)

person	supervisor
Bob	Alice
Mary	Susan
Alice	David
David	Mary

Questions on emp_super (slide 26)

Find the supervisor of "Bob".
Find the supervisor of the supervisor of "Bob".
Find ALL the supervisors (direct and indirect) of "Bob".

emp_super S × emp_super T (slide 27): 4 × 4 = 16 rows

S.person	S.supervisor	T.person	T.supervisor
Bob	Alice	Bob	Alice
Bob	Alice	Mary	Susan
Bob	Alice	Alice	David
Bob	Alice	David	Mary
Mary	Susan	Bob	Alice
Mary	Susan	Mary	Susan
Mary	Susan	Alice	David
Mary	Susan	David	Mary
Alice	David	Bob	Alice
Alice	David	Mary	Susan
Alice	David	Alice	David
Alice	David	David	Mary
David	Mary	Bob	Alice
David	Mary	Mary	Susan
David	Mary	Alice	David
David	Mary	David	Mary

المسألة

select distinct S.supervisor, T.supervisor from emp_super S, emp_super T where S.supervisor = T.person; (slide 27)

String matching with likelike والأنماط

Rename, string operations, ordering and between

% (percent)علامة النسبة

Matches any substring.

_ (underscore)الشرطة السفلية

Matches any (one) character.

Pattern matching examples (slide 29)

Patternالنمط	Matchesما يطابقه
'Intro%'	Any string beginning with "Intro"
'%Comp%'	Any string containing "Comp" as a substring
'_ _ _'	Any string of exactly three characters
'_ _ _ %'	Any string of at least three characters

Patterns are case sensitive.
Find the names of all instructors whose name includes the substring "dar": select name from instructor where name like '%dar%'
Match the string "100%": like '100 \%%' escape '\' — in that example backslash (\) is the escape character. The slide also gives: like '1_ _$%%' escape '$'.
SQL supports a variety of string operations such as concatenation (using "||"), converting from upper to lower case (and vice versa), finding string length, extracting substrings, etc.

order by and betweenالترتيب وbetween

Rename, string operations, ordering and between

order by (slide 30)

select distinct name from instructor order by name
desc for descending, asc for ascending — ascending is the default.
Sort on multiple attributes: order by dept_name, name.

Where-clause predicates (slide 31)

between: where salary between 90000 and 100000 — that is, ≥ 90000 and ≤ 100000.
Tuple comparison: where (instructor.ID, dept_name) = (teaches.ID, 'Biology').

Set operations and duplicatesعمليات المجموعات

Duplicates and set operations

Set operations (slide 34)

Operationالعملية	Meaning in the exampleالمعنى في المثال
union	Courses that ran in Fall 2009 or in Spring 2010
intersect	Courses that ran in Fall 2009 and in Spring 2010
except	Courses that ran in Fall 2009 but not in Spring 2010

من الشرائح

(select course_id from section where sem = 'Fall' and year = 2009) union (select course_id from section where sem = 'Spring' and year = 2010)
(select course_id from section where sem = 'Fall' and year = 2009) intersect (select course_id from section where sem = 'Spring' and year = 2010)
(select course_id from section where sem = 'Fall' and year = 2009) except (select course_id from section where sem = 'Spring' and year = 2010)

union, intersect and except automatically eliminate duplicates; the multiset versions union all, intersect all, except all retain them.

A tuple occurs m times in r and n times in s (slide 37)

Expressionالتعبير	Occurrences of the tupleعدد مرات الظهور
r union all s	m + n
r intersect all s	min(m, n)
r except all s	max(0, m − n)

Set operations on c1 and c2 (Figures 3.9–3.13)مثال c1 وc2

Duplicates and set operations

Figure 3.9: the c1 relation, courses taught in Fall 2009 (slide 35)

course_id
CS-101
CS-347
PHY-101

Figure 3.10: the c2 relation, courses taught in Spring 2010 (slide 35)

course_id
CS-101
CS-315
CS-319
CS-319
FIN-201
HIS-351
MU-199

Figure 3.11: c1 union c2 (slide 35)

course_id
CS-101
CS-315
CS-319
CS-347
FIN-201
HIS-351
MU-199
PHY-101

Figure 3.12: c1 intersect c2 (slide 35)

course_id
CS-101

Figure 3.13: c1 except c2 (slide 35)

course_id
CS-347
PHY-101

Multiset semanticsدلالة التكرار

Duplicates and set operations

σθ(r1): all c1 copies of a tuple that satisfies θ are kept.
ΠA(r1): one projected copy for each copy of t1.
r1 × r2: c1 × c2 copies of t1.t2.

المسألة

r1(A, B) = {(1, a), (2, a)}, r2(C) = {(2), (3), (3)} (slide 33). What are ΠB(r1) and ΠB(r1) × r2?

SQL duplicate semantics (slide 33)

select A1, A2, …, An from r1, r2, …, rm where P is equivalent to the multiset version of the expression Π A1, A2, …, An (σP (r1 × r2 × … × rm)).

The largest salary with except (slide 36)

(select distinct salary from instructor) except (select distinct T.salary from instructor as T, instructor as S where T.salary < S.salary)

Null values and three-valued logicnull والمنطق الثلاثي

null signifies an unknown value or that a value does not exist.
Any arithmetic expression involving null is null: 5 + null returns null.
Check with is null: select name from instructor where salary is null.
Any comparison with null returns unknown: 5 < null, null <> null, null = null.

Three-valued logic (slide 39)

Expressionالتعبير	Resultالناتج
unknown or true	true
unknown or false	unknown
unknown or unknown	unknown
true and unknown	unknown
false and unknown	false
unknown and unknown	unknown
not unknown	unknown

A where-clause predicate that evaluates to unknown is treated as false.

It is possible for tuples to have a null value, denoted by null, for some of their attributes (slide 38).
"P is unknown" evaluates to true if predicate P evaluates to unknown (slide 39).

Aggregate functionsدوال التجميع

Aggregate functions (slide 40)

avg
min
max
sum
count

من الشرائح

select avg (salary) from instructor where dept_name = 'Comp. Sci.';
select count (distinct ID) from teaches where semester = 'Spring' and year = 2010;
select count (*) from course;

Null values and aggregates (slide 46)

sum(salary) ignores null amounts; the result is null if there is no non-null amount.
All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes.
Only null values: count returns 0; all other aggregates return null.

Only null values in the column (slide 46; the slide prints "-" for null)

SUM(SALARY)	AVG(SALARY)	MAX(SALARY)	MIN(SALARY)	COUNT(SALARY)
null	null	null	null	0

group by: average salary per departmentمثال group by

Aggregate functions, group by and having

من الشرائح

select dept_name, avg (salary) as avg_salary from instructor group by dept_name;

instructor, grouped by dept_name (slide 42)

ID	name	dept_name	salary
76766	Crick	Biology	72000
45565	Katz	Comp. Sci.	75000
10101	Srinivasan	Comp. Sci.	65000
83821	Brandt	Comp. Sci.	92000
98345	Kim	Elec. Eng.	80000
12121	Wu	Finance	90000
76543	Singh	Finance	80000
32343	El Said	History	60000
58583	Califieri	History	62000
15151	Mozart	Music	40000
33456	Gold	Physics	87000
22222	Einstein	Physics	95000

Result: avg_salary (slide 42)

dept_name	salary
Biology	72000
Comp. Sci.	77333
Elec. Eng.	80000
Finance	85000
History	61000
Music	40000
Physics	91000

group by and havinggroup by وhaving

من الشرائح

select dept_name, avg (salary) as avg_salary from instructor group by dept_name;
select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000;

Attributes in the select clause outside of aggregate functions must appear in the group by list: select dept_name, ID, avg(salary) … group by dept_name is erroneous.
having predicates are applied after the formation of groups; where predicates are applied before forming groups.

Course_attend (slide 45)

course	semester	student
IS320	1	Norah
IS320	1	Haya
IS320	2	Mona
IS320	2	Maha
IS320	2	Rana
IS200	1	Nada
IS200	1	Arwa

المسألة

SELECT course, semester, COUNT(student) AS count FROM course_attend GROUP BY course, semester;

Subqueries and set membershipالاستعلام الفرعي وin

Nested subqueries: membership, some, all

Subquery

A select-from-where expression nested within another query. In select A1, …, An from r1, …, rm where P: Ai can be a subquery that generates a single value, ri any valid subquery, and P an expression B <operation> (subquery).

Tests in the where clause (slide 49)

set membership: in, not in
set comparison: any, some, all
set cardinality: count(*)

من الشرائح

select distinct course_id from section where semester = 'Fall' and year = 2009 and course_id in (select course_id from section where semester = 'Spring' and year = 2010);
… course_id not in (select course_id from section where semester = 'Spring' and year = 2010);

من الشرائح

select count (distinct ID) from takes where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year from teaches where teaches.ID = 10101);
Note (slide 51): the query above can be written in a much simpler manner; the formulation is simply to illustrate SQL features.

some and allsome وall

Nested subqueries: membership, some, all

F <comp> some rsome

⇔ ∃ t ∈ r such that (F <comp> t) — true for at least one t.
(5 < some {0, 5, 6}) = true; (5 < some {0, 5}) = false.
(5 = some {0, 5}) = true; (5 ≠ some {0, 5}) = true.
(= some) ≡ in, but (≠ some) ≢ not in.

F <comp> all rall

⇔ ∀ t ∈ r (F <comp> t) — true for every t.
(5 < all {0, 5, 6}) = false; (5 < all {6, 10}) = true.
(5 = all {4, 5}) = false; (5 ≠ all {4, 6}) = true.
(≠ all) ≡ not in, but (= all) ≢ in.

من الشرائح

select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Biology';
select name from instructor where salary > some (select salary from instructor where dept_name = 'Biology');
select name from instructor where salary > all (select salary from instructor where dept_name = 'Biology');
select dept_name from instructor group by dept_name having avg (salary) >= all (select avg (salary) from instructor group by dept_name);

exists, not exists and uniqueexists وunique

existsيوجد

exists r ⇔ r ≠ Ø — true if the argument subquery is nonempty.

not existsلا يوجد

not exists r ⇔ r = Ø.

uniqueفريد

Tests whether a subquery has any duplicate tuples; true if the subquery contains no duplicates — e.g., courses offered at most once in 2009.

من الشرائح

select course_id from section as S where semester = 'Fall' and year = 2009 and exists (select * from section as T where semester = 'Spring' and year = 2010 and S.course_id = T.course_id);

S is a correlation name (a variable of the outer query); the inner query is a correlated subquery.
Students who took all Biology courses: not exists ((Biology courses) except (courses the student took)) — because X − Y = Ø ⇔ X ⊆ Y. This query cannot be written using = all and its variants.

not exists, unique and not unique in queriesاستعلامات unique

exists, not exists and unique

من الشرائح

Students who have taken all courses offered in the Biology department (slide 59): select distinct S.ID, S.name from student as S where not exists ((select course_id from course where dept_name = 'Biology') except (select T.course_id from takes as T where S.ID = T.ID));
The first nested query lists all courses offered in Biology; the second lists all courses a particular student took.
Courses offered at most once in 2009 (slide 60): select T.course_id from course as T where unique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009);
The same with count (slide 61): select T.course_id from course as T where 1 <= (select count(R.course_id) from section as R where T.course_id = R.course_id and R.year = 2009);
Courses offered at least twice in 2009, with not unique (slide 61): select T.course_id from course as T where not unique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009);

We can test for the existence of duplicate tuples in a subquery by using the not unique construct (slide 61).

Subqueries in from and the with clausefrom الفرعي وwith

Subqueries in from, the with clause and scalar subqueries

من الشرائح

select dept_name, avg_salary from (select dept_name, avg (salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000;
Another way (slide 63): select dept_name, avg_salary from (select dept_name, avg (salary) from instructor group by dept_name) as dept_avg (dept_name, avg_salary) where avg_salary > 42000;

With a subquery in from, the having clause is not needed — the outer where filters the averages.

with clause

Defines a temporary relation whose definition is available only to the query in which the with clause occurs.

من الشرائح

with max_budget (value) as (select max(budget) from department) select department.dept_name from department, max_budget where department.budget = max_budget.value;

من الشرائح

Departments where the total salary is greater than the average of the total salary at all departments (slide 65): with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.value > dept_total_avg.value;

Scalar subqueriesالاستعلام القياسي

Scalar subquery

A subquery used where a single value is expected.

من الشرائح

select dept_name, (select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors from department;

Runtime error if the subquery returns more than one result tuple.

Deletionالحذف

Modification of the database: delete, insert, update

Modification of the database (slide 68)

Deletion of tuples from a given relation.
Insertion of new tuples into a given relation.
Updating of values in some tuples in a given relation.

من الشرائح

delete from instructor;
delete from instructor where dept_name = 'Finance';
delete from instructor where dept_name in (select dept_name from department where building = 'Watson');

المسألة

delete from instructor where salary < (select avg (salary) from instructor); — the average changes as tuples are deleted. What does SQL do? (slide 70)

Insertionالإدراج

Modification of the database: delete, insert, update

من الشرائح

insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
insert into course (course_id, title, dept_name, credits) values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
insert into student values ('3003', 'Green', 'Finance', null);
insert into student select ID, name, dept_name, 0 from instructor;
Make each student in the Music department who has earned more than 144 credit hours an instructor in the Music department, with a salary of $18,000: insert into instructor select ID, name, dept_name, 18000 from student where dept_name = 'Music' and tot_cred > 144;

The select-from-where statement is evaluated fully before any of its results are inserted into the relation; otherwise queries like insert into table1 select * from table1 would cause problems.

Updates and caseالتحديث وcase

Modification of the database: delete, insert, update

من الشرائح

update instructor set salary = salary * 1.03 where salary > 100000;
update instructor set salary = salary * 1.05 where salary <= 100000;

The order is important — running the 5% update first could push a salary over 100,000 and then raise it again by 3%.
Done better with case: update instructor set salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03 end

Update with a scalar subquery (slide 75)

update student S set tot_cred = (select sum(credits) from takes, course where takes.course_id = course.course_id and S.ID = takes.ID and takes.grade <> 'F' and takes.grade is not null);
This sets tot_cred to null for students who have not taken any course; use case when sum(credits) is not null then sum(credits) else 0 end instead.

الخلاصة

أنهيت الدرس

الخطوة التالية: ثبّت المعلومات بالبطاقات، ثم اختبر نفسك بالأسئلة.

البطاقات التدريب

ما راجعته في هذا الدرس

SQL history and the data-definition languageتاريخ SQL ولغة تعريف البيانات
Domain types in SQLأنواع النطاقات في SQL
Create table, integrity constraints, drop and alterإنشاء الجداول والقيود والحذف والتعديل
Basic query structure: select, from, whereبنية الاستعلام: select وfrom وwhere
Rename, string operations, ordering and betweenإعادة التسمية والنصوص والترتيب وbetween
Duplicates and set operationsالتكرار وعمليات المجموعات
Null values and three-valued logicالقيم null والمنطق الثلاثي
Aggregate functions, group by and havingدوال التجميع وgroup by وhaving
Nested subqueries: membership, some, allالاستعلامات الفرعية: الانتماء وsome وall
exists, not exists and uniqueexists وnot exists وunique
Subqueries in from, the with clause and scalar subqueriesالاستعلام الفرعي في from وwith والاستعلام القياسي
Modification of the database: delete, insert, updateتعديل القاعدة: الحذف والإدراج والتحديث

Introduction to SQLمقدمة في لغة SQL

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

أقسام الفصل

SQL and its data-definition languageSQL ولغة التعريف

Domain typesأنواع البيانات

Attribute data types and domains in SQLأنواع البيانات

create table and integrity constraintsإنشاء الجداول

More definitions, drop and alterdrop وalter

select, from, whereselect وfrom وwhere

Inside the select clauseجملة select

Combining relations in fromالربط في from

Names with department and building (Figure 3.5)الشكل 3.5

Rename and the self-joinإعادة التسمية

String matching with likelike والأنماط

order by and betweenالترتيب وbetween

Set operations and duplicatesعمليات المجموعات

Set operations on c1 and c2 (Figures 3.9–3.13)مثال c1 وc2

Multiset semanticsدلالة التكرار

Null values and three-valued logicnull والمنطق الثلاثي

Aggregate functionsدوال التجميع

group by: average salary per departmentمثال group by

group by and havinggroup by وhaving

Subqueries and set membershipالاستعلام الفرعي وin

some and allsome وall

exists, not exists and uniqueexists وunique

not exists, unique and not unique in queriesاستعلامات unique

Subqueries in from and the with clausefrom الفرعي وwith

Scalar subqueriesالاستعلام القياسي

Deletionالحذف

Insertionالإدراج

Updates and caseالتحديث وcase

الخلاصة

أنهيت الدرس

ما راجعته في هذا الدرس

خريطة الفصل