Chapter 3 Introduction to SQL n Overview of

  • Slides: 60
Download presentation
Chapter 3: Introduction to SQL n Overview of The SQL Query Language n Data

Chapter 3: Introduction to SQL n Overview of The SQL Query Language n Data Definition n Basic Query Structure n Additional Basic Operations n Set Operations n Null Values n Aggregate Functions n Nested Subqueries n Modification of the Database System Concepts - 6 th Edition 3. 1

History n IBM Sequel language developed as part of System R project at the

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

Data Definition Language Allows the specification of not only a set of relations but

Data Definition Language Allows the specification of not only a set of relations but also information about each relation, including: n The schema for each relation. n The domain of values associated with each attribute. n Integrity constraints n Security and authorization information for each relation. n The set of indices to be maintained for each relations. -> physical level n The physical storage structure of each relation on disk. -> physical level Database System Concepts - 6 th Edition 3. 3

Domain Types in SQL n char(n). Fixed length character string, with user-specified length n.

Domain Types in SQL n char(n). Fixed length character string, with user-specified length n. n varchar(n). Variable length character strings, with user-specified maximum n n n 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. For example, 34. 2 is numeric(3, 1). 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. Database System Concepts - 6 th Edition 3. 4

Create Table Construct n An SQL relation is defined using the create table command:

Create Table Construct n An SQL relation is defined using the create table command: create table r (A 1 D 1, A 2 D 2, . . . , An Dn, <integrity-constraint 1>, . . . , <integrity-constraintk>); l r is the name of the relation l each Ai is an attribute name in the schema of relation r l Di is the data type of values in the domain of attribute Ai n Example: create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8, 2)); n insert into instructor values (‘ 10211’, ’Smith’, ’Biology’, 66000); l 字串加單引號 n insert into instructor values (‘ 10211’, null, ’Biology’, 66000); ->wrong! n insert into instructor values (‘ 10211’, ’Smith’, ’Biology’, null); ->right! Database System Concepts - 6 th Edition 3. 5

Integrity Constraints in Create Table n not null n primary key (A 1, .

Integrity Constraints in Create Table n not null n primary key (A 1, . . . , An ) n foreign key (Am, . . . , An ) references r Example: Declare ID as the primary key for instructor. 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); primary key declaration on an attribute automatically ensures not null Database System Concepts - 6 th Edition 3. 6

And a Few More Relation Definitions n create table department ( dept_name building budget

And a Few More Relation Definitions n create table department ( dept_name building budget varchar(20), varchar(15), numeric(12, 2), primary key (dept_name)); n create table teaches ( ID course_id sec_id semester year varchar(5), varchar(8), varchar(6), numeric(4, 0), primary key (ID, course_id, sec_id, semester, year), foreign key (ID) references instructor, foreign key (course_id, sec_id, semester, year) references section); Note: 1. A primary key can consist of many attributes. 2. A table can have many foreign keys. Database System Concepts - 6 th Edition 3. 7

Drop and Alter Table Constructs n drop table command deletes all information about the

Drop and Alter Table Constructs n drop table command deletes all information about the dropped relation from the database. l 如果只刪除資料而不刪除定義的話,請使用delete語法. n alter table command is used to add attributes to an existing relation: l alter table r add A D; 4 where A is the name of the attribute to be added to relation r and D is the domain of A. 4 All tuples in the relation are assigned null as the value for the new attribute. l alter table r drop A; 4 where Database System Concepts - 6 th Edition A is the name of an attribute of relation r 3. 8

Relational Schemas n instructor (ID, name, dept_name, salary) n teaches (ID, course_id, sec_id, semester,

Relational Schemas n instructor (ID, name, dept_name, salary) n teaches (ID, course_id, sec_id, semester, year) n course (course_id, title, dept_name, credits) n section (course_id, sec_id, semester, year, building, room_number, time_slot_id) n student (ID, name, dept_name, tot_cred) n takes (ID, course_id, sec_id, semester, year, grade) n department (dept_name, building, budget) Database System Concepts - 6 th Edition 3. 9

Relational Instances n section n course Database System Concepts - 6 th Edition 3.

Relational Instances n section n course Database System Concepts - 6 th Edition 3. 10

More Relational Instances n department n teaches n instructor Database System Concepts - 6

More Relational Instances n department n teaches n instructor Database System Concepts - 6 th Edition 3. 11

Basic Query Structure n A typical SQL query has the form: select A 1,

Basic Query Structure n A typical SQL query has the form: select A 1, A 2, . . . , An from r 1, r 2, . . . , rm where P; l Ai represents an attribute l ri represents a relation l P is a predicate. n The result of an SQL query is a relation. n Note: An SQL query is usually ended with the semicolon “; ”, but it might vary in different software or different programming languages. (For example, 在ASP. net程式中要加. ) Database System Concepts - 6 th Edition 3. 12

The select Clause n The select clause list the attributes desired in the result

The select Clause n The select clause list the attributes desired in the result of a query l corresponds to the projection operation of the relational algebra n Example: find the names of all instructors: ( 參照instance) select name from instructor; n NOTE: SQL names are case insensitive (i. e. , you may use upper- or lower-case letters. ) l E. g. , Name ≡ NAME ≡ name l Some people use upper case wherever we use bold font. Database System Concepts - 6 th Edition 3. 13

The select Clause (Cont. ) n SQL allows duplicates in relations as well as

The select Clause (Cont. ) n SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. n Find the department names of all instructors, and remove duplicates select distinct dept_name from instructor; n An asterisk in the select clause denotes “all attributes” select * from instructor; n The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. n The following query would return a relation that is the same as the instructor relation, except that the value of the attribute salary is divided by 12. select ID, name, salary/12 from instructor; <- A query can have no “where”. Database System Concepts - 6 th Edition 3. 14

The where Clause n The where clause specifies conditions that the result must satisfy

The where Clause n The where clause specifies conditions that the result must satisfy l Corresponds to the selection predicate of the relational algebra. n To find all instructors in Comp. Sci. dept with salary > 70000 select name from instructor where dept_name = ‘Comp. Sci. ' and salary > 70000; n Comparison results can be combined using the logical connectives and, or, and not. n Comparisons can be applied to results of arithmetic expressions. n 注意: l 字串用單引號 l 不等於是用 <> Database System Concepts - 6 th Edition 3. 15

The from Clause n The from clause lists the relations involved in the query

The from Clause n The from clause lists the relations involved in the query l Corresponds to the Cartesian product operation of the relational algebra. n Find the Cartesian product instructor X teaches select from instructor, teaches; l generates every possible “instructor – teaches” pair, with all attributes from both relations. n Cartesian product not very useful directly, but useful combined with where -clause condition (selection operation in relational algebra). Database System Concepts - 6 th Edition 3. 16

Cartesian Product teaches instructor 95000 應該為 65000 Database System Concepts - 6 th Edition

Cartesian Product teaches instructor 95000 應該為 65000 Database System Concepts - 6 th Edition 3. 17

Joins n For all instructors who have taught courses, find their names and the

Joins n For all instructors who have taught courses, find their names and the course ID of the courses they taught. select name, course_id from instructor, teaches where instructor. ID = teaches. ID; n Find the course ID, semester, year and title of each course offered by the Comp. Sci. department (參照 instance) select section. course_id, semester, year, title from section, course where section. course_id = course_id and dept_name = ‘Comp. Sci. ‘; n Join: Cartesian product + 限制式 n PS. 相同屬性出現在兩個以上的表格,前面必須加註 來源表格 Database System Concepts - 6 th Edition 3. 18

Try Writing Some Queries in SQL n Find the titles of courses in the

Try Writing Some Queries in SQL n Find the titles of courses in the Comp. Sci. department that have 3 credits. Answer: n Find the course_ids and sec_ids which were offered by an instructor named “Einstein”; make sure there are no duplicates in the result. Answer: Database System Concepts - 6 th Edition 3. 19

Natural Join n Natural join matches tuples with the same values for all common

Natural Join n Natural join matches tuples with the same values for all common attributes, and retains only one copy of each common column n 注意:common attributes通常是primary key或foreign key n select * from instructor natural join teaches; <- 有些軟體不支援此語法! Database System Concepts - 6 th Edition 3. 20

Natural Join (Cont. ) n Danger in natural join: unrelated attributes with same name

Natural Join (Cont. ) n Danger in natural join: unrelated attributes with same name which get equated incorrectly n Example: List the names of instructors along with the titles of courses that they teach l Incorrect version (equates course. dept_name with instructor. dept_name) select name, title from instructor natural join teaches natural join course; <- 錯誤的原因在於instructor和course不用同一系! l Correct version select name, title from instructor natural join teaches, course where teaches. course_id= course_id; <-在from裡使用逗號”, ”,所以course是和前一個combined relation做 cartesian product. n 注意:更複雜的join語法會在第四章補充。 Database System Concepts - 6 th Edition 3. 21

The Rename Operation n The SQL allows renaming relations and attributes using the as

The Rename Operation n The SQL allows renaming relations and attributes using the as clause: old-name as new-name n “as”放在select clause: l select ID, name, salary/12 as monthly_salary from instructor; n Tuple variables: “as”放在from clause Usually used to compare tuples in the same relation. n Example: Find the names of all instructors who have a higher salary than some instructor in ‘Comp. Sci’. (see the next page) l select distinct T. name from instructor as T, instructor as S where T. salary > S. salary and S. dept_name = ‘Comp. Sci. ’; n Keyword as is optional and may be omitted instructor as T ≡ instructor T l Database System Concepts - 6 th Edition 3. 22

Example of Tuple Variables instructor S T Database System Concepts - 6 th Edition

Example of Tuple Variables instructor S T Database System Concepts - 6 th Edition 3. 23

String Operations n SQL includes a string-matching operator for comparisons on character strings. The

String Operations n SQL includes a string-matching operator for comparisons on character strings. The operator “like” uses patterns that are described using two special characters: l percent (%). The % character matches any substring. (* in OS) l underscore (_). The _ character matches any character. (? in OS) n Find the names of all instructors whose name includes the substring “dar”. select name from instructor where name like '%dar%'; n SQL supports a variety of string operations such as l concatenation (using “||”) l converting from upper to lower case (and vice versa) l finding string length, extracting substrings, etc. Database System Concepts - 6 th Edition 3. 24

Where Clause Predicates n SQL includes a between comparison operator n Example: Find the

Where Clause Predicates n SQL includes a between comparison operator n Example: Find the names of all instructors with salary between $90, 000 and $100, 000 (that is, $90, 000 and $100, 000) l select name from instructor where salary between 90000 and 100000; It is equivalent to select name from instructor where salary 90000 and salary 100000; Database System Concepts - 6 th Edition 3. 25

Ordering the Display of Tuples n List in alphabetic order the names of all

Ordering the Display of Tuples n List in alphabetic order the names of all instructors select distinct name from instructor order by name; name Brandt Califieri (c. f. page 3. 13) …… n We may specify desc for descending order or asc for ascending order. For each attribute, ascending order is the default. l Example: order by name desc name Wu Srinivasan n Can sort on multiple attributes l Example: order by dept_name, name Database System Concepts - 6 th Edition 3. 26 ………

Duplicates n Recall that the relational algebra operators support the set semantics. n Multiset

Duplicates n Recall that the relational algebra operators support the set semantics. n Multiset versions of some of the relational algebra operators: l 1. given multiset relations r 1 and r 2: (r 1): If there are c 1 copies of tuple t 1 in r 1, and t 1 satisfies selections , , then there are c 1 copies of t 1 in (r 1). 2. A (r ): For each copy of tuple t 1 in r 1, there is a copy of tuple A (t 1) in A (r 1) where A (t 1) denotes the projection of the single tuple t 1. 3. r 1 x r 2: If there are c 1 copies of tuple t 1 in r 1 and c 2 copies of tuple t 2 in r 2, there are c 1 x c 2 copies of the tuple t 1. t 2 in r 1 x r 2 n Example: Suppose multiset relations r 1 (A, B) and r 2 (C) are as follows: r 1 = {(1, a) (2, a)} Ø Then r 2 = {2, 3, 3} c>2(r 1)= {3, 3}, B(r 1)={a, a}, B(r 1) x r 2 = {(a, 2), (a, 3), (a, 3)} Database System Concepts - 6 th Edition 3. 27 C A B 2 1 a 3 2 a 3

Duplicates (Cont. ) n SQL duplicate semantics: select A 1, , A 2, .

Duplicates (Cont. ) n SQL duplicate semantics: select A 1, , A 2, . . . , An from r 1, r 2, . . . , rm where P is equivalent to the multiset version of the expression: n Example: l See page 3. 14 for example of projection Database System Concepts - 6 th Edition 3. 28

Set Operations n Find courses that ran in Fall 2009 or in Spring 2010

Set Operations n Find courses that ran in Fall 2009 or 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); n Find courses that ran in Fall 2009 and in Spring 2010 (select course_id from section where sem = ‘Fall’ and year = 2009) intersect (select course_id from section where sem = ‘Spring’ and year = 2010); n Find courses that ran in Fall 2009 but not in Spring 2010 (select course_id from section where sem = ‘Fall’ and year = 2009) except (select course_id from section where sem = ‘Spring’ and year = 2010); Database System Concepts - 6 th Edition 3. 29

Set Operation Example union intersect Database System Concepts - 6 th Edition 3. 30

Set Operation Example union intersect Database System Concepts - 6 th Edition 3. 30 except

※ Duplicates of Set Operations n Set operations union, intersect, and except Each of

※ Duplicates of Set Operations n Set operations union, intersect, and except Each of the above operations automatically eliminates duplicates. -> support the set semantics. n To retain all duplicates, use the corresponding multiset version syntax: n union all, intersect all and except all. l n Suppose a tuple occurs m times in r and n times in s, then, it occurs: m + n times in r union all s l min(m, n) times in r intersect all s l max(0, m – n) times in r except all s n Example: A = {p, p, q}, B = {p, r} l A union all B = {p, p, p, q, r}; A union B = {p, q, r} l A intersect all B = {p} l A except all B = {p, q}; B except all A = {r} l Database System Concepts - 6 th Edition 3. 31

Null Values n It is possible for tuples to have a null value, denoted

Null Values n It is possible for tuples to have a null value, denoted by null, for some of their attributes n null signifies an unknown value or that a value does not exist. n The result of any arithmetic expression involving null is null l Example: 5 + null returns null n The predicate is null can be used to check for null values. l Example: Find all instructors whose salary is null. select name from instructor where salary is null; Database System Concepts - 6 th Edition 3. 32

Null Values and Three Valued Logic n Any comparison with null returns unknown l

Null Values and Three Valued Logic n Any comparison with null returns unknown l Example: 5 < null or null <> null or null = null n Three-valued logic using the truth value unknown: l OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown l AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown l NOT: (not unknown) = unknown n Result of where clause predicate is treated as false if it evaluates to unknown A n Example: l Select C from T 1 where A = ‘ ’ and B = ‘ ’ -> {7} l Select C from T 1 where A = ‘ ’ or B = ‘ ’ -> {8, 7} Database System Concepts - 6 th Edition 3. 33 T 1 B C 8 7

Aggregate Functions n These functions operate on the multiset of values of a column

Aggregate Functions n These functions operate on the multiset of values of a column of a relation, and return a value avg: average value min: minimum value max: maximum value sum: sum of values count: number of values n Example: Database System Concepts - 6 th Edition A B C 7 sum(c ) 7 27 3 10 3. 34

Aggregate Functions (Cont. ) n Find the average salary of instructors in the Computer

Aggregate Functions (Cont. ) n Find the average salary of instructors in the Computer Science department (see page 3. 11) l select avg (salary) from instructor where dept_name= ’Comp. Sci. ’; n Find the number of tuples in the course relation l select count (*) from course; avg(salary) 77333 count (*) 13 n Find the total number of instructors who teach a course in the Spring 2010 semester l select count (distinct ID) from teaches where semester = ’Spring’ and year = 2010; Database System Concepts - 6 th Edition 3. 35 count (distinct ID) 6

Aggregate Functions – Group By n Find the average salary of instructors in each

Aggregate Functions – Group By n Find the average salary of instructors in each department l select dept_name, avg (salary) as avg_salary from instructor group by dept_name; avg_salary Database System Concepts - 6 th Edition 3. 36

Aggregation (Cont. ) n Note: Attributes in select clause outside of aggregate functions must

Aggregation (Cont. ) n Note: Attributes in select clause outside of aggregate functions must appear in group by list <-否則會出現multi-valued的狀況,違反 atomic的限制 l /* erroneous query */ select dept_name, ID, avg (salary) from instructor group by dept_name ID avg(salary) biology 76766 72000 Comp. Sci 45565 77333 10101 83821 …. . ………. n Find the number of instructors in each department who teach a course in the Spring 2010 semester. select dept_name, count (distinct ID) as instr_count from instructor natural join teaches where semester= ‘Spring’ and year = 2010 group by dept_name; (參照join結果) Database System Concepts - 6 th Edition 3. 37

Aggregate Functions – Having Clause n Find the names and average salaries of all

Aggregate Functions – Having Clause n Find the names and average salaries of all departments whose average salary is greater than 42000 select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000; (參照分群結果) Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups 注意: • aggregate function 不能直接使用在where clause裡. • 放在having和select clause裡的aggregate function 可不同 Database System Concepts - 6 th Edition 3. 38

Aggregate Functions – Example n For each course section offered in 2009, find the

Aggregate Functions – Example n For each course section offered in 2009, find the average total credits (tot_cred) of all students enrolled in the section, if the section had at least 2 students. select course_id, semester, year, sec_id, avg (tot_cred) from takes natural join student where year = 2009 group by course_id, sec_id, semester, year having count (ID) >= 2; Database System Concepts - 6 th Edition 3. 39

Practice n Find the number of instructors in each department. n Find the names

Practice n Find the number of instructors in each department. n Find the names of all departments whose instructors are more than 10. Database System Concepts - 6 th Edition 3. 40

Null Values and Aggregates n Total all salaries select sum (salary ) from instructor;

Null Values and Aggregates n Total all salaries select sum (salary ) from instructor; l Above statement ignores null amounts l Result is null if there is no non-null amount n All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes n What if collection has only null values? l count returns 0 l all other aggregates return null ID 10101 12121 15151 22222 32343 Database System Concepts - 6 th Edition 3. 41 name salary Srinivasan Wu Mozart Einstein El Said 40000 null 75000 null

Nested Subqueries n SQL provides a mechanism for the nesting of subqueries. n A

Nested Subqueries n SQL provides a mechanism for the nesting of subqueries. n A subquery is a select-from-where expression that is nested within another query. n A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality. Database System Concepts - 6 th Edition 3. 42

Example Query n Find courses offered in Fall 2009 and in Spring 2010 (另一種寫法)

Example Query n Find courses offered in Fall 2009 and in Spring 2010 (另一種寫法) 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); n Find courses offered in Fall 2009 but not in Spring 2010 select distinct course_id from section where semester = ’Fall’ and year= 2009 and course_id not in (select course_id from section where semester = ’Spring’ and year= 2010); n 練習題:Find courses offered in Fall 2009 or in Spring 2010 Database System Concepts - 6 th Edition 3. 43

Section and Result of Subquery Database System Concepts - 6 th Edition 3. 44

Section and Result of Subquery Database System Concepts - 6 th Edition 3. 44

Set Comparison n Find names of instructors with salary greater than that of some

Set Comparison n Find names of instructors with salary greater than that of some (at least one) instructor in the ‘Comp. Sci’ department. select distinct T. name from instructor as T, instructor as S where T. salary > S. salary and S. dept_name = ’Comp. Sci’ n Same query using > some clause select name from instructor where salary > some (select salary from instructor where dept_name = ’Comp. Sci’) n 確定sub-query只輸出一筆資料則可省略量詞some或all. Database System Concepts - 6 th Edition 3. 45

Definition of Some Clause n F <comp> some r t r such that (F

Definition of Some Clause n F <comp> some r t r such that (F <comp> t ) Where <comp> can be: <= <> (5 < some 0 5 6 ) = true (5 < some 0 5 ) = false (5 = some 0 5 ) = true (5 some 0 5 ) = true (since 0 5) (read: 5 < some tuple in the relation) (= some) in However, ( some) not in Database System Concepts - 6 th Edition 3. 46

Example Query n Find the names of all instructors whose salary is greater than

Example Query n Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department. select name from instructor where salary > all (select salary from instructor where dept_name = ’Biology’) n 練習題:Find the names of all instructors who earn the most salary in the Biology department. Answer: Database System Concepts - 6 th Edition 3. 47

Definition of all Clause n F <comp> all r t r (F <comp> t)

Definition of all Clause n F <comp> all r t r (F <comp> t) 0 5 6 ) = false (5 < all 6 10 ) = true (5 = all 4 5 ) = false (5 all 4 6 ) = true (since 5 4 and 5 6) (5 < all ( all) not in However, (= all) in Database System Concepts - 6 th Edition 3. 48

Test for Empty Relations n The exists construct returns the value true if the

Test for Empty Relations n The exists construct returns the value true if the argument subquery is nonempty. n exists r r Ø n not exists r r = Ø Database System Concepts - 6 th Edition 3. 49

Correlation Variables n Another way of specifying the query “Find all courses taught in

Correlation Variables n Another way of specifying the query “Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester” 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); n S: correlation variable, or correlation name n Correlated subquery: a subquery in the where clause that uses a correlation variable from an outer query n 注意:有的subquery需要額外宣告變數,有的不需要 (see page 3. 43). Database System Concepts - 6 th Edition 3. 50

Database System Concepts - 6 th Edition 3. 51

Database System Concepts - 6 th Edition 3. 51

Not Exists n Find all students who have taken all courses offered in the

Not Exists n Find all students who have taken all courses offered in the Biology department. 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)) X n Note that X – Y = Ø X Y Y X Y n Here: Y: the courses taken by the student S; X: the courses offered by the “Biology” department. n Note: Cannot write this query using = all and its variants Database System Concepts - 6 th Edition 3. 52 X Y Y X

Derived Relations n SQL allows a subquery expression to be used in the from

Derived Relations n SQL allows a subquery expression to be used in the from clause n Find the average instructors’ salaries of those departments where the average salary is greater than $42, 000. (see page 3. 36, c. f. page 3. 38) select dept_name, avg_salary from (select dept_name, avg (salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000; 更改屬性的名稱 n Note that we do not need to use the having clause n Another way to write above query 更改表格的名稱 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; <-只能用新定義的relation裡的屬性 n 注意: (1) 寫在from的subquery不能用到同層from的其他relation.    (2) 有的軟體要求一定要把derived relation改名 Database System Concepts - 6 th Edition 3. 53

With Clause n The with clause provides a way of defining a temporary view

With Clause n The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. (c. f. page 4. 11) n Find all departments with the maximum budget with max_budget (value) as (select max(budget) from department) select dept_name from department, max_budget where department. budget = max_budget. value Database System Concepts - 6 th Edition 3. 54 value 120000

Complex Queries using With Clause n The with clause makes the query logic clearer,

Complex Queries using With Clause n The with clause makes the query logic clearer, but it is not supported by all database systems. n Find all departments where the total salary is greater than the average of the total salary at all departments 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; n 注意定義的順序,後面的可用到前面的定義,整個query的輸出 為最後select-from-where所定義的。 Database System Concepts - 6 th Edition 3. 55

Modification of the Database – Deletion n Delete all instructors delete from instructor; n

Modification of the Database – Deletion n Delete all instructors delete from instructor; n Delete all instructors from the Finance department delete from instructor where dept_name= ’Finance’; n Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building. delete from instructor where dept_name in (select dept_name from department where building = ’Watson’); Database System Concepts - 6 th Edition 3. 56

Example Query n Delete all instructors whose salary is less than the average salary

Example Query n Delete all instructors whose salary is less than the average salary of instructors delete from instructor where salary< (select avg (salary) from instructor); l Problem: as we delete tuples from deposit, the average salary changes l Solution used in SQL: 1. First, compute avg salary and find all tuples to delete 2. Next, delete all tuples found above (without recomputing avg or retesting the tuples) Database System Concepts - 6 th Edition 3. 57

Modification of the Database – Insertion (one tuple) n Add a new tuple to

Modification of the Database – Insertion (one tuple) n Add a new tuple to course insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci. ’, 4); n or equivalently insert into course (course_id, title, dept_name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci. ’, 4); n Add a new tuple to student with tot_creds set to null insert into student values (’ 3003’, ’Green’, ’Finance’, null); Database System Concepts - 6 th Edition 3. 58

Modification of the Database – Insertion (a set of tuples) n Add all instructors

Modification of the Database – Insertion (a set of tuples) n Add all instructors to the student relation with tot_creds set to 0 insert into student select ID, name, dept_name, 0 from instructor n The select from where statement is evaluated fully before any of its results are inserted into the relation (otherwise queries like insert into table 1 select * from table 1 would cause problems) n 以上為文數字資料的範例,若不同型態則需要不同的輸入方法,且各軟體不 同. Database System Concepts - 6 th Edition 3. 59

Modification of the Database – Updates n Increase salaries of instructors whose salary is

Modification of the Database – Updates n Increase salaries of instructors whose salary is over $100, 000 by 3%, and all others receive a 5% raise l Write two update statements: 欲修改的欄位名 稱 新的欄位值 update instructor set salary = salary * 1. 03 where salary > 100000; update instructor set salary = salary * 1. 05 where salary <= 100000; l The order is important l Can be done better using the case statement update instructor set salary = case when salary <= 100000 then salary * 1. 05 else salary * 1. 03 end Database System Concepts - 6 th Edition 3. 60