SQLOutlineBackgroundParts of SQLBasic Domain TypesBasic Schema Definition in SQLExamples create tableExample 2Basic SQL Query StructureExample select ClauseWhere ClauseFrom ClauseRenameSet OperationExamples Set OperationsSQLSangeeta DevadigaCS157A, Fall 2006OutlineBackgroundData DefinitionBasic StructureSet OperationBackgroundIBM developed the original version named sequel in early 1970’sSequel language has evolved into SQLSQL (Structured Query Language) Versions of SQL SQL 92 SQL 99 SQL 2003 (latest version)Parts of SQLData Definition Language (DDL)Data manipulation Language (DML)IntegrityView DefinitionTransaction controlEmbedded SQL and Dynamic SQLAuthorizationBasic Domain Typeschar(n): A fixed length character string with user specifed length n. character can be used instead.varchar(n): A variable length character string with user specified max length n. character varying is equivalent.int: An Integer, the full form integer is equivalent.smallint: A small integer, a subset of integer domain typenumeric(p,d): A fixed point number with user specified precision. E.g: numeric(3,1) allows 31.5 to be defined preciselyreal, double precision: Floating-point and double precision floating-point numbers with machine-dependent precision.float(n): A floating point number, with precision of at least n digits.Basic Schema Definition in SQL We create SQL relation using the create table commandcreate table r( A1D1 , A2D2 , ….. , AnDn , (integrity constraint1), ………. , (integrity constraintk))r is the name of relationA1….An are the names of attributesD1…Dn are the types of values in the domainExamples create tableExample 1:create table customer (customer_name char(20), customer_street char(30), customer_city char(30), primary key (customer_name))Example 2create table account (account_number char(10), branch_name char(15), balance numeric (12, 2), primary key (account_number))Basic SQL Query StructureSQL is based on set and relational operations with some modification and enhancement.SQL query has the form select A1,A2, … ,An from r1, r2, …. ,rm where PA1 is a attributer1 represents a relation P is a predicate Equivalent Query: A1, A2 , … , An(P (r1 X r2 X …… X rm))The result of a SQL query is a relationExample select Clauseselect branch_name from loanIn relational Algebra, the query would be branch_name(loan)SQL allows duplicates in query resultuse distinct if no duplicates in resultuse all if duplicates required in resultselect distinct branch_name from loan (result has distinct branch names)select all branch_name from loan (result may have duplicates)Where ClauseCorresponds to the selection predicate of relational algebraTo find loan numbers for loans made at San Jose branch with loan amounts greater than $500 select loan_number from loan where branch_name = “San Jose” and amount > $500Comparison result can be combined with logical connectives and, or, and notSQL includes between comparison operatorTo find loan numbers between amt. 900 and 10,000 select loan_number from loan where amount between 900 and 10000From ClauseCorresponds to Cartesian product operation of relational algebraExample: To find name, loan number, amount of all customers having loan at San Jose branch. select customer_name, loan_number, amount from borrower , loan where borrower.loan_number = loan.loan_number and branch_name = “San Jose”RenameSQL allows renaming relations and attributes using as clauseExample: To find name, loan_number, amount of all customers and rename column loan_number as loan_id. select customer_name, loan_number as loan_id, amount from borrower , loan where borrower.loan_number = loan. loan_numberSet OperationThe set operations union, intersect and except corresponds to U, , - respectively of relational algebra.Each of the above operation automatically eliminates duplicatesTo retain all duplicates use union all, intersect all, except allExamples Set OperationsFind all customers who have a loan, a account or both: select customer_name from depositor union select customer_name from borrowerFind all customers who have both loan and an account: select customer_name from depositor intersect select customer_name from borrowerFind all customers who have an account, but no loan: select customer_name from depositor except select customer_name from
View Full Document
Unlocking...