Database System Concepts, 5th Ed.©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Lecture 6: SQLLecture 6: SQLChapter 3Chapter 3Database System Concepts, 5th Ed.©Silberschatz, Korth and SudarshanSee www.db-book.com for conditions on re-use Administrative issuesAdministrative issues■SQL assignment■SQL*plus documentationhttp://www.oracle.com/technology/docs/tech/sql_plus/index.html■SQL documentationSee SQL reference in:http://www.oracle.com/pls/db102/homepage■Note – for drawing ER diagramsDiaCze (Windows)http://www.cze.cz/downloads.phpKivio (Linux, Mac)http://www.koffice.org/download/Dia (Linux, Windows)http://live.gnome.org/Dia/Download©Silberschatz, Korth and Sudarshan3.3Database System Concepts, 5th Edition, Oct 5, 2006Domain Types in SQLDomain 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 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 n 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.©Silberschatz, Korth and Sudarshan3.4Database System Concepts, 5th Edition, Oct 5, 2006Create Table ConstructCreate Table Construct■An SQL relation is defined using the create table command:create table r (A1 D1, A2 D2, ..., An Dn,(integrity-constraint1),...,(integrity-constraintk))●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 branch(branch_name char(15) not null,branch_city char(30),assets integer)©Silberschatz, Korth and Sudarshan3.5Database System Concepts, 5th Edition, Oct 5, 2006Integrity Constraints in Create TableIntegrity Constraints in Create Table■not null■primary key (A1, ..., An )Example: Declare branch_name as the primary key for branch.create table branch (branch_name char(15), branch_city char(30), assets integer, primary key (branch_name))primary key declaration on an attribute automatically ensures not null in SQL-92 onwards, needs to be explicitly stated in SQL-89©Silberschatz, Korth and Sudarshan3.6Database System Concepts, 5th Edition, Oct 5, 2006Drop and Alter Table ConstructsDrop and Alter Table Constructs■The drop table command deletes all information about the dropped relation from the database.■The alter table command is used to add attributes to an existing relation: alter table r add A D where A is the name of the attribute to be added to relation r and D is the domain of A.●All tuples in the relation are assigned null as the value for the new attribute. ■The alter table command can also be used to drop attributes of a relation:alter table r drop A where A is the name of an attribute of relation r●Dropping of attributes not supported by many databases©Silberschatz, Korth and Sudarshan3.7Database System Concepts, 5th Edition, Oct 5, 2006Basic Query Structure Basic Query Structure ■SQL is based on set and relational operations with certain modifications and enhancements■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.■This query is equivalent to the relational algebra expression.■The result of an SQL query is a relation.∏A1, A2,, An σP r1×r2××rm©Silberschatz, Korth and Sudarshan3.8Database System Concepts, 5th Edition, Oct 5, 2006The select ClauseThe select Clause■The select clause list the attributes desired in the result of a query●corresponds to the projection operation of the relational algebra■Example: find the names of all branches in the loan relation:select branch_namefrom loan■In the relational algebra, the query would be: ∏branch_name (loan)■NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) ●E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name●Some people use upper case wherever we use bold font.©Silberschatz, Korth and Sudarshan3.9Database System Concepts, 5th Edition, Oct 5, 2006The select Clause (Cont.)The select Clause (Cont.)■SQL allows duplicates in relations as well as in query results.■To force the elimination of duplicates, insert the keyword distinct after select.■Find the names of all branches in the loan relations, and remove duplicatesselect distinct branch_namefrom loan■The keyword all specifies that duplicates not be removed.select all branch_namefrom loan©Silberschatz, Korth and Sudarshan3.10Database System Concepts, 5th Edition, Oct 5, 2006The select Clause (Cont.)The select Clause (Cont.)■An asterisk in the select clause denotes “all attributes”select *from loan■The select clause can contain arithmetic expressions involving the operation, +, –, ∗, and /, and operating on constants or attributes of tuples.■The query: select loan_number, branch_name, amount ∗ 100 from loanwould return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100.©Silberschatz, Korth and Sudarshan3.11Database System Concepts, 5th Edition, Oct 5, 2006The where ClauseThe where Clause■The where clause specifies conditions that the result must satisfy●Corresponds to the selection predicate of the relational algebra. ■To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200.select loan_numberfrom loanwhere branch_name = 'Perryridge' and amount > 1200■Comparison results can be combined using the logical connectives and, or, and not. ■Comparisons can be applied to results of arithmetic expressions.©Silberschatz, Korth and Sudarshan3.12Database System Concepts, 5th Edition, Oct 5, 2006The where Clause (Cont.)The where Clause (Cont.)■SQL includes a between comparison operator■Example: Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, ≥ $90,000 and ≤ $100,000) select loan_numberfrom loanwhere amount between 90000 and
View Full Document
Unlocking...