Transcription

Practice Exercise 2.6Database System Concepts - 6th Edition3.1 Silberschatz, Korth and Sudarshan

Practice Exercise 2.7Employee(person name, street, city)Works(person name, company name, salary)Company(company name, city)Write relational algebra expressions to:Database System Concepts - 6th Edition3.2 Silberschatz, Korth and Sudarshan

Chapter 3: Introduction to SQLDatabase System Concepts, 6th Ed. Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use

Link to A History andEvaluation of System Ron our webpageHistory IBM Sequel language developed as part of System Rproject 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, SQL:2003, SQL:2008 Commercial systems offer most, if not all, SQL-92features, plus varying feature sets from later standardsand special proprietary features. Not all examples here may work on our particularsystem.Database System Concepts - 6th Edition3.4 Silberschatz, Korth and Sudarshan

Data Definition Language (DDL)Allows specification of information about relations: The schema for each relation. The domain of values associated with eachattribute. Integrity constraints (e.g. NOT NULL) Other information such as The set of indices to be maintained for each relations. Security and authorization information for each relation. The physical storage structure of each relation on disk.Database System Concepts - 6th Edition3.5 Silberschatz, Korth and Sudarshan

Domain Types in SQL 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 machinedependent).smallint. Small integer (a machine-dependent subset of the integerdomain type).numeric(p,d). Fixed point number, with user-specified precision ofp digits, with n digits to the right of decimal point.real, double precision. Floating point and double-precision floatingpoint numbers, with machine-dependent precision.float(n). Floating point number, with user-specified precision of atleast n digits.More are covered in Ch.4.Database System Concepts - 6th Edition3.6 Silberschatz, Korth and Sudarshan

Domain Types in SQL char(n). Fixed length character string, with user-specified length n. varchar(n). Variable length character strings, with user-specifiedmaximum length n.How does SQL compare strings of different lengths?Standard SQL says: Spaces are added to the shorter oneuntil strings have same length. Not always implemented correctly!How about Unicode?nvarchar But many implementations allow UTF-8 in varcharDatabase System Concepts - 6th Edition3.7 Silberschatz, Korth and Sudarshan

Domain Types in SQL char(n). Fixed length character string, with user-specified length n. varchar(n). Variable length character strings, with user-specifiedmaximum length n. int. Integer (a finite subset of the integers that is machinedependent). smallint. Small integer (a machine-dependent subset of the integerdomain type). numeric(p,d). Fixed point number, with user-specifiedprecision of p digits, with n digits to the right of decimalpoint.P does not include thesign, nor the decimalpoint!Database System Concepts - 6th Edition3.8 Silberschatz, Korth and Sudarshan

Domain Types in SQL 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 machinedependent).smallint. Small integer (a machine-dependent subset of the integerdomain type).numeric(p,d). Fixed point number, with user-specified precision ofp digits, with n digits to the right of decimal point.real, double precision. Floating point and double-precision floatingpoint numbers, with machine-dependent precision.float(n). Floating point number, with user-specified precision of atleast n digits.Each of the above also supports the NULL value Sometimes NULL is prohibited!Database System Concepts - 6th Edition3.9 Silberschatz, Korth and Sudarshan

Remember the university DBDatabase System Concepts - 6th Edition3.10 Silberschatz, Korth and Sudarshan

CREATE TABLE Syntax:CREATE TABLE r (A1 D1, A2 D2, ., An k)) r is the name of the relation each Ai is an attribute name in the schema of relation r Di is the data type of values in the domain of attribute Ai Example:CREATE TABLE instructor (IDchar(5),namevarchar(20) not null,dept name varchar(20),salarynumeric(8,2))insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);insert into instructor values (‘10211’, null, ’Biology’, 66000);Database System Concepts - 6th Edition3.11 Silberschatz, Korth and Sudarshan

Integrity Constraints in Create Table not null primary key (A1, ., An ) foreign key (Am, ., An ) references rExample: Declare dept name as the primary key for department.CREATE TABLE instructor (IDchar(5),namevarchar(20) not null,dept name varchar(20),salarynumeric(8,2),primary key (ID),foreign key (dept name) references department)primary key declaration on an attribute automatically ensures not nullDatabase System Concepts - 6th Edition3.12 Silberschatz, Korth and Sudarshan

QUIZ: Integrity ConstraintsBased on this Example, write the CREATE TABLE for advisorCREATE TABLE instructor (IDchar(5),namevarchar(20) not null,dept name varchar(20),salarynumeric(8,2),primary key (ID),foreign key (dept name) references department)Database System Concepts - 6th Edition3.13 Silberschatz, Korth and Sudarshan

QUIZ: Integrity ConstraintsWrite CREATE TABLEs for student and takesCREATE TABLE instructor (IDchar(5),namevarchar(20) not null,dept name varchar(20),salarynumeric(8,2),primary key (ID),foreign key (dept name) references department)Database System Concepts - 6th Edition3.14 Silberschatz, Korth and Sudarshan

CREATE TABLE student (IDvarchar(5),namevarchar(20) not null,dept namevarchar(20),tot crednumeric(3,0),primary key (ID),foreign key (dept name) references department) ); CREATE TABLE takes (IDvarchar(5),course idvarchar(8),sec radevarchar(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 );Note: sec id can be dropped from primary key above, to ensure astudent cannot be registered for two sections of the same course in thesame semester.Database System Concepts - 6th Edition3.15 Silberschatz, Korth and Sudarshan

QUIZ: Integrity ConstraintsWrite the CREATE TABLE for courseCREATE TABLE instructor (IDchar(5),namevarchar(20) not null,dept name varchar(20),salarynumeric(8,2),primary key (ID),foreign key (dept name) references department)Database System Concepts - 6th Edition3.16 Silberschatz, Korth and Sudarshan

CREATE TABLE course (course idvarchar(8) primary key,titlevarchar(50),dept namevarchar(20),creditsnumeric(2,0),foreign key (dept name) references department) );Note: Primary key declaration can be combined with attributedeclaration as shown.Database System Concepts - 6th Edition3.17 Silberschatz, Korth and Sudarshan

DROP TABLE and ALTER TABLE drop table studentDeletes the table and its contents delete from student Deletes all contents of table, but retains table alter table alter table r add A D where A is the name of the attribute to be added torelation r and D is the domain of A. All tuples in the relation are assigned null as the valuefor the new attribute. alter table r drop A whereA is the name of an attribute of relation r Droppingof attributes not supported by manydatabasesDatabase System Concepts - 6th Edition3.18 Silberschatz, Korth and Sudarshan

3.3 Basic Query Structure The SQL data-manipulation language (DML) provides theability to query information, and insert, delete and updatetuples A typical SQL query has the form:select A1, A2, ., Anfrom r1, r2, ., rmwhere P Ai represents an attribute Ri represents a relation P is a predicate. The result of an SQL query is a relation.Database System Concepts - 6th Edition3.19 Silberschatz, Korth and Sudarshan

The SELECT clause It lists the attributes desired in the result of a query corresponds to the projection operation of therelational algebra Example: find the names of all instructors:select namefrom instructor NOTE: SQL names are case insensitive (i.e., you mayuse upper- or lower-case letters.) E.g. Name NAME name Some people use upper case wherever we use boldfont.Database System Concepts - 6th Edition3.20 Silberschatz, Korth and Sudarshan

The SELECT clause SQL allows duplicates in relations as well as in queryresults. To force the elimination of duplicates, insert the keyworddistinct after select. Find the names of all departments with instructor, andremove duplicates:SELECT distinct dept nameFROM instructor The keyword all specifies that duplicates not be removed(seldom used, since it’s the default):SELECT all dept nameFROM instructorDatabase System Concepts - 6th Edition3.21 Silberschatz, Korth and Sudarshan

The SELECT clause An asterisk in the select clause denotes “all attributes”SELECT *FROM instructor The select clause can contain arithmetic expressionsinvolving the operation, , –, , and /, and operating onconstants or attributes of tuples. The query:SELECT ID, name, salary/12FROM instructorwould return a relation that is the same as the instructorrelation, except that the value of the attribute salary isdivided by 12.Database System Concepts - 6th Edition3.22 Silberschatz, Korth and Sudarshan

The WHERE clause Specifies conditions that the result must satisfy Corresponds to the selection predicate of the relationalalgebra. Find all instructors in Comp. Sci. dept with salary 80000select namefrom instructorwheredept name ‘Comp. Sci.' andsalary 80000 Comparison results can be combined using the logicalconnectives and, or, and not. Comparisons can be applied to results of arithmeticexpressions.Database System Concepts - 6th Edition3.23 Silberschatz, Korth and Sudarshan

The FROM clause The from clause lists the relations involved in the query Corresponds to the Cartesian product operation of therelational algebra. Find the Cartesian product instructor X teachesselect from instructor, teaches generates every possible instructor – teaches pair, with allattributes from both relations Cartesian product not very useful directly, but useful combinedwith where-clause condition (selection operation in relationalalgebra)Database System Concepts - 6th Edition3.24 Silberschatz, Korth and Sudarshan

Cartesian Product: instructor X teachesinstructorDatabase System Concepts - 6th Editionteaches3.25 Silberschatz, Korth and Sudarshan

Joins For all instructors who have taught some course, find their namesand the course ID of the courses they taught.select name, course idfrom instructor, teacheswhere instructor.ID teaches.ID Find the course ID, semester, year and title of each course offeredby the Comp. Sci. departmentselect section.course id, semester, year, titlefrom section, coursewhere section.course id course.course id anddept name ‘Comp. Sci.'Database System Concepts - 6th Edition3.26 Silberschatz, Korth and Sudarshan

QUIZ: Queries Find the names of all students who are taking a class this semester inthe SCIENCE buildingEoL1Database System Concepts -6thEdition3.27 Silberschatz, Korth and Sudarshan

QUIZWhat are the three integrity constraints we havecovered? Give examples of how each is used.Database System Concepts - 6th Edition3.28 Silberschatz, Korth and Sudarshan

QUIZWhat are the three integrity constraints we havecovered? Give examples of how each is used. NOT NULL PRIMARY KEY FOREIGN KEYDatabase System Concepts - 6th Edition3.29 Silberschatz, Korth and Sudarshan

QUIZTrue or false:By default, in SQL a table may contain duplicate tuples.Database System Concepts - 6th Edition3.30 Silberschatz, Korth and Sudarshan

QUIZTrue or false:By default, in SQL a table may contain duplicate tuples.Database System Concepts - 6th Edition3.31 Silberschatz, Korth and Sudarshan

QUIZHow can we control duplicates in SQL queries?Database System Concepts - 6th Edition3.32 Silberschatz, Korth and Sudarshan

QUIZHow can we control duplicates in SQL queries?Database System Concepts - 6th Edition3.33 Silberschatz, Korth and Sudarshan

Natural Join Natural join matches tuples with the same values for allcommon attributes, and retains only one copy of each commoncolumn select *from instructor natural join teaches;Database System Concepts - 6th Edition3.34 Silberschatz, Korth and Sudarshan

Database System Concepts - 6th Edition3.35 Silberschatz, Korth and Sudarshan

Natural Join ExampleList the names of instructors along with the course ID ofthe courses that they taught. select name, course idfrom instructor, teacheswhere instructor.ID teaches.ID; select name, course idfrom instructor natural join teaches;Database System Concepts - 6th Edition3.36 Silberschatz, Korth and Sudarshan

Beware of unrelated attributes with same name which get equatedincorrectlyExample: List the names of instructors along with the the titles ofcourses that they teach Incorrect version (makes course.dept name instructor.dept name) select name, titlefrom instructor natural join teaches natural join course; Correct version select name, titlefrom instructor natural join teaches, coursewhere teaches.course id course.course id; Another correct version select name, titlefrom (instructor natural join teaches)join course using(course id);Database System Concepts - 6th Edition3.37 Silberschatz, Korth and Sudarshan

Database System Concepts - 6th Edition3.38 Silberschatz, Korth and Sudarshan

QUIZ: Natural JoinUse the natural join to write queries for the following: Find the names of customers who have (at least) an account Find the names of customers who have an account at the “Uptown” branch Find the names of customers who have a loan at a branch with assets greaterthan 1,000,000Database System Concepts - 6th Edition3.39 Silberschatz, Korth and Sudarshan

The RENAME OperationSQL allows renaming relations and attributes using the AS clause:old-name AS new-name E.g. SELECTID, name, salary/12 AS monthly salaryFROM instructor Find the names of all instructors who have a higher salary thansome instructor in ‘Comp. Sci’. SELECT DISTINCT T. nameFROM instructor AS T, instructor AS SWHERE T.salary S.salary AND S.dept name ‘Comp. Sci.’ The keyword AS is optional and may be omittedinstructor as T instructor TDatabase System Concepts - 6th Edition3.40 Silberschatz, Korth and Sudarshan

QUIZUse the relational-algebra operations defined to find all the accountswith a balance smaller than the balance of at least one other account.Hint: join the table with itself!Database System Concepts - 6th Edition3.41 Silberschatz, Korth and Sudarshan

String OperationsSQL includes a string-matching operator for comparisons on characterstrings. The operator LIKE uses patterns that are described usingtwo special characters: percent (%). The % character matches any substring. underscore ( ). The character matches any character. Find the names of all instructors whose name includes the substring“dar”:select namefrom instructorwhere name like '%dar%' Match the string “100 %”:like ‘100 \%'Database System Concepts - 6th Edition3.42escape'\' Silberschatz, Korth and Sudarshan

String Operations Patters are case sensitive. Pattern matching examples: ‘Intro%’ matches any string beginning with “Intro”. ‘%Comp%’ matches any string containing “Comp” as asubstring. ‘ ’ matches any string of exactly three characters. ‘ %’ matches any string of at least three characters. 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.Database System Concepts - 6th Edition3.43 Silberschatz, Korth and Sudarshan

QUIZ: String OperationsFind all course names that: Begin with “Computer” Have the substring “Computer” anywhere in them Have the substring “Computer” or “computer” anywherein them Have the substring “Comp” or “comp” anywhere in them Have the substring “comp% ” anywhere in themDatabase System Concepts - 6th Edition3.44 Silberschatz, Korth and Sudarshan

QUIZ: Find the loan number of all loansmade in cities that start with ‘S’ and end in‘ville’:Hint: Use join and string operations.Database System Concepts - 6th Edition3.45 Silberschatz, Korth and Sudarshan

Ordering the Display of Tuples List in alphabetic order the names of all instructors:SELECT DISTINCT nameFROM instructorORDER BY name We may specify desc for descending order or asc forascending order, for each attribute; ascending order isthe default. Example: order by name desc Can sort on multiple attributes Example: order by dept name, nameDatabase System Concepts - 6th Edition3.46 Silberschatz, Korth and Sudarshan

WHERE Clause Predicates SQL includes a between comparison operator Example: Find the names of all instructors withsalary between 90,000 and 100,000 (inclusive)select namefrom instructorwhere salary between 90000 and 100000 Tuple comparisonselect name, course idfrom instructor AS I, teaches AS Twhere (I.ID, dept name) (T.ID, ’Biology’)Database System Concepts - 6th Edition3.47 Silberschatz, Korth and Sudarshan

WHERE Clause Predicates SQL includes an IN operator Example: Find the names and salaries of allinstructors named ‘Jones’, ‘James’ or ‘Julian’SELECT nameFROM instructorWHERE salary IN (‘Jones’, ‘James’, ‘Julian’)Database System Concepts - 6th Edition3.48 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesList all the WHERE predicates we learned:Database System Concepts - 6th Edition3.49 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesList all the WHERE predicates we learned: BETWEEN Tuplecomparison INEOL2Database System Concepts - 6th Edition3.50 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesWrite a query to return the names of allstudents who have exactly 50, 60, 70, or 80credit hours total Without a WHERE predicate With a WHERE predicateDatabase System Concepts - 6th Edition3.51 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesWrite a query to return the names of allstudents who have anywhere between 50and 80 credit hours total Without a WHERE predicate With a WHERE predicateDatabase System Concepts - 6th Edition3.52 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesWrite a query that uses tuple comparison toreturn the names of all students who havetaken a class in 2014.Use the previous example:select name, course idfrom instructor AS I, teaches AS Twhere (I.ID, dept name) (T.ID, ’Biology’)Database System Concepts - 6th Edition3.53 Silberschatz, Korth and Sudarshan

Quiz: WHERE Clause PredicatesWrite a query that uses tuple comparison toreturn the names of all students who havetaken a class in 2014.SELECT S.nameFROM student AS S, takes AS TWHERE (T.ID, T.year) (S.ID, 2014)EOL2Database System Concepts -6thEdition3.54 Silberschatz, Korth and Sudarshan

Set Operations 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) 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) 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 - 6th Edition3.55 Silberschatz, Korth and Sudarshan

Set Operations Set operations union, intersect, and except Each of the above operations automaticallyeliminates duplicates To retain all duplicates use the correspondingmultiset versions union all, intersect all and exceptall.Suppose a tuple occurs m times in r and n times ins, then, it occurs: m n times in r union all s min(m,n) times in r intersect all s max(0, m – n) times in r except all sDatabase System Concepts - 6th Edition3.56 Silberschatz, Korth and Sudarshan

QUIZ: Set operationsFind the names of customers who have an account at the “Uptown”branch or a loan from the “Downtown” branchDatabase System Concepts - 6th Edition3.57 Silberschatz, Korth and Sudarshan

QUIZFind the account with the maximum balance without usingaggregation functions, i.e. max().Now we can finally write the complete solution!Hint: Use EXCEPTDatabase System Concepts - 6th Edition3.58 Silberschatz, Korth and Sudarshan

Null Values It is possible for tuples to have a null value, denoted by null,for some of their attributes null signifies an unknown value or that a value does notexist. The result of any arithmetic expression involving null is null Example: 5 null returns null The predicate is null can be used to check for null values. Example: Find all instructors whose salary is null.select namefrom instructorwhere salary is nullDatabase System Concepts - 6th Edition3.59 Silberschatz, Korth and Sudarshan

Null Values and Three Valued Logic Any comparison with null returns unknown Example: 5 null or null nullornull null Three-valued logic using the truth value unknown: OR: (unknown or true) true,(unknown or false) unknown(unknown or unknown) unknown AND: (true and unknown) unknown,(false and unknown) false,(unknown and unknown) unknown NOT: (not unknown) unknown “P is unknown” evaluates to true if predicate Pevaluates to unknownDatabase System Concepts - 6th Edition3.60 Silberschatz, Korth and Sudarshan

Null Values and Three Valued Logic Three-valued logic using the truth value unknown: OR: (unknown or true) true,(unknown or false) unknown(unknown or unknown) unknown AND: (true and unknown) unknown,(false and unknown) false,(unknown and unknown) unknown NOT: (not unknown) unknownWrite the truth table for three-valued OR!Database System Concepts - 6th Edition3.61 Silberschatz, Korth and Sudarshan

Database System Concepts - 6th Edition3.62 Silberschatz, Korth and Sudarshan

Null Values and Three Valued LogicThe result of where clause predicate is treated as falseif it evaluates to unknownThis means that, in SQL, we prefer to err on the side ofcaution, retaining only those tuples that surely make thepredicate true.EOL 3Database System Concepts -6thEdition3.63 Silberschatz, Korth and Sudarshan

QUIZWrite the truth table for three-valued AND!Database System Concepts - 6th Edition3.64 Silberschatz, Korth and Sudarshan

Database System Concepts - 6th Edition3.65 Silberschatz, Korth and Sudarshan

3.7 Aggregate FunctionsThese functions operate on the multiset of valuesof a column of a relation, and return a valueavg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of valuesDatabase System Concepts - 6th Edition3.66 Silberschatz, Korth and Sudarshan

Aggregate Functions Examples Find the average salary of instructors in the Computer Sciencedepartment select avg (salary)from instructorwhere dept name ’Comp. Sci.’; Find the total number of instructors who teach a course in theSpring 2010 semester select count (distinct ID)from teacheswhere semester ’Spring’ and year 2010; Find the number of tuples in the course relation select count (*)from course;Database System Concepts - 6th Edition3.67 Silberschatz, Korth and Sudarshan

Aggregate Functions – GROUP BYFind the average salary of instructors in each departmentselect dept name, avg (salary)from instructorgroup by dept name;Database System Concepts - 6th Edition3.68 Silberschatz, Korth and Sudarshan

Aggregate Functions – GROUP BYAttributes in select clause outside of aggregatefunctions must appear in group by listselect dept name, avg (salary)from instructorgroup by dept name;/* incorrect */select dept name, ID, avg (salary)from instructorgroup by dept name;Database System Concepts - 6th Edition3.69 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite queries to find the following: The highest budget of all departments The lowest number of credit hours of any student The highest budget of a department in each building The lowest number of credit hours of any student ineach departmentDatabase System Concepts - 6th Edition3.70 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite a query to find the lowest number of credit hoursof any student in the “CS” departmentDatabase System Concepts - 6th Edition3.71 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite a query to find the the highest budget of anydepartment.Database System Concepts - 6th Edition3.72 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite a query to find the name of the departmentwith the highest budget.Database System Concepts - 6th Edition3.73 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite a query to find the name of the departmentwith the highest budget. Does this work? Why not?SELECT dept name, MAX(budget)FROM departmentDatabase System Concepts - 6th Edition3.74 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite a query to find the name of the departmentwith the highest budget. Does this work? Why not?SELECT dept name, MAX(budget)FROM departmentDatabase System Concepts - 6th Edition3.75Attributes in selectclause outside ofaggregate functionsmust appear ingroup by list Silberschatz, Korth and Sudarshan

Aggregate Functions – Having ClauseFind the names and average salaries of alldepartments whose average salary is greater than42000select dept name, avg (salary)from instructorgroup by dept namehaving avg (salary) 42000;Note: predicates in the having clause are applied after theformation of groups whereas predicates in the whereclause are applied before forming groupsDatabase System Concepts - 6th Edition3.76 Silberschatz, Korth and Sudarshan

QUIZ: Aggregate FunctionsWrite queries to find the following: The names of the department whose students havean average total credit of at least 6 credit hoursDatabase System Concepts - 6th Edition3.77 Silberschatz, Korth and Sudarshan

Null Values and Aggregates Total all salariesselect sum (salary )from instructor Above statement ignores null amounts Result is null if all amounts are null All aggregate operations except count(*) ignoretuples with null values on the aggregated attributes What if the attribute has only null values? count returns 0 all other aggregates return nullDatabase System Concepts - 6th Edition3.78 Silberschatz, Korth and Sudarshan

3.8 Nested Subqueries SQL provides a mechanism for the nesting ofsubqueries. A subquery is a select-from-where expression thatis nested within another query. A common use of subqueries is to perform tests forset membership, set comparisons, and set cardinality.Database System Concepts - 6th Edition3.79 Silberschatz, Korth and Sudarshan

Nested Example: IN and NOT IN Find courses offered in Fall 2009 and in Spring 2010select distinct course idfrom sectionwhere semester ’Fall’ and year 2009 andcourse id in (select course idfrom sectionwhere semester ’Spring’ and year 2010); Find courses offered in Fall 2009 but not in Spring 2010select distinct course idfrom sectionwhere semester ’Fall’ and year 2009 andcourse id not in (select course idfrom sectionwhere semester ’Spring’ and year 2010);Database System Concepts - 6th Edition3.80 Silberschatz, Korth and Sudarshan

QUIZ: Now we can solve this!Write a query to find the name of the department withthe highest budget.(Hint: Start with the inner query!)Database System Concepts - 6th Edition3.81 Silberschatz, Korth and Sudarshan

Extra-credit QUIZWrite a nested query to find the total number of(distinct) students who have taken course sectionstaught by the instructor with ID 10101Hint: Use the schema diagram of the University DB (handout).Database System Concepts - 6th Edition3.82 Silberschatz, Korth and Sudarshan

QUIZ: NestedWrite a nested query to find the total number of (distinct) studentswho have taken course sections taught by the instructor with ID10101select count (distinct ID)from takeswhere (course id, sec id, semester, year) in(select course id, sec id, semester, yearfrom teacheswhere teaches.ID 10101);Database System Concepts - 6th Edition3.83 Silberschatz, Korth and Sudarshan

QUIZ: NestedWrite a nested query to find the total number of (distinct) studentswho have taken course sections taught by the instructor with ID10101What would the query returnif we only used course idhere?select count (distinct ID)from takeswhere (course id, sec id, semester, year) in(select course id, sec id, semester, yearfrom teacheswhere teaches.ID 10101); Note: Above query can be written in a much simpler manner. Thecode above is simply to illustrate SQL features.Database System Concepts - 6th Edition3.84EoL3 Silberschatz, Korth and Sudarshan

3.8 Review: Nested SubqueriesAre used for: Determining membership (or lack thereof) in a set: IN NOT INOuterquerySELECT COUNT (DISTINCT ID)FROM takesWHERE (course id, sec id, semester, year) IN(select course id, sec id, semester, yearfrom teacheswhere teaches.ID 10101);InnerqueryDatabase System Concepts - 6th Edition3.85 Silberschatz, Korth and Sudarshan

Nested SubqueriesAre used for: Determining membership (or lack thereof) in a set: IN NOT IN Comparing tupl

Database System Concepts - 6th Edition 3.4 Silberschatz, Korth and Sudarshan History IBM Sequel language developed as part of 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, SQL:2003, SQL