1 / 99

Structured/System Query Language -- SQL

Structured/System Query Language -- SQL. September 20, 2004. Lecture Outline. History Basics of SQL Data Definition Language Query Language Data Manipulation Language Miscellany. History. Originally it is was called SEQUEL for S tructured E nglish QUE ry L anguage

Download Presentation

Structured/System Query Language -- SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Structured/System Query Language -- SQL September 20,2004

  2. Lecture Outline • History • Basics of SQL • Data Definition Language • Query Language • Data Manipulation Language • Miscellany

  3. History • Originally it is was called SEQUEL for Structured English QUEry Language • Designed and implemented at IBM Research • San Jose Research (now IBM Almaden Research Center) • as the interface for an experimental relational database system called SYSTEM R. • The language for IBM’s DB2

  4. A number of variations of SQL have been implemented by most commercial DBMS vendors • A joint effort by ANSI (American National Standards Institute) and ISO (International Standards Organization) led the first standard version of SQL (ANSI 1986) called SQL-1

  5. Revised and expanded in SQL-92 (SQL-2) and SQL-1999 (SQL-3) (newer?)

  6. Basics • Language developed specifically to query databases • It is a comprehensive database language (data definition, query and update) • Based on set and relational operations with certain modifications and enhancements

  7. A typical SQL query has the form: (PROJECTION) select A1, A2, ..., An (CART. PROD.) fromr1, r2, ..., rm (SELECTION) where P • Ais represent attributes • ris represent relations • P is a predicate. • The result of an SQL query is a relation

  8. Data Definition Language DDL

  9. Introduction • Allows the specification of a set of relations along with information about each relation, including • The schema for the relations. • The domain of values associated with each attribute in every relation. • Integrity constraints • The set of indices to be maintained for each relation. • Security and authorization information for each relation. • The physical storage structure of each relation on disk.

  10. SQL uses the terms schema,table, row and column for database, relation, tuple and attribute, respectively • The main DDL commands are • create • alter • drop

  11. Schema and Catalog Concepts • Prior to SQL-2, there was no concept of a relational database schema • All tables (relations) were considered part of the same schema • In SQL-2, the schema concept was incorporated in order to group together tables and other constructs (e.g. views) that belong to the same database application

  12. Formally, an SQL schema is identified by a schema name and authorization as well as descriptions for each element in the schema • Examples of elements include tables and views • Two ways to create a schema using create schema • Including all schema element definitions (rare)

  13. Assign a name and an authorization identifier but the elements are defined later • create schema Bank authorization xyz • A collection of schemas is known as a catalogue

  14. create table keyword • An SQL relation is defined using the create table command: create table r (A1D1, A2D2, ..., 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),branch-city char(30),assets integer)

  15. Data 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 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. • Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute. • create domain construct in SQL-92 creates user-defined domain types create domain person-name aschar(20) not null

  16. date. Dates, containing a (4 digit) year, month and date • E.g. date ‘2001-7-27’(on MySQL ‘YYYY-MM-DD’) • time. Time of day, in hours, minutes and seconds. • E.g. time ’09:00:30’ • timestamp: date plus time of day • E.g. timestamp ‘2001-7-27 09:00:30’ • Interval: period of time • E.g. Interval ‘1’ day • Subtracting a date/time/timestamp value from another gives an interval value • Interval values can be added to date/time/timestamp values • Can extract values of individual fields from date/time/timestamp • E.g. extract (year from r.starttime) • Can cast string types to date/time/timestamp • E.g. cast <string-valued-expression> as date

  17. Integrity Constraints in Create Table • not null (after domain) • Unique (after domain) • primary key (A1, ..., An) • foreign Key • Create table Table_YYY(A1 char(3), A2 int, A3 int UNIQUE, A4 float NOT NULL, PRIMARY KEY(A1,A2), FOREIGN KEY (A1) REFERENCES TABLE_XXX(A1) ON DELETE CASCADE ON UPDATE CASCADE);

  18. check (P), where P is a predicate • Example: Declare branch-name as the primary key for branch and ensure that the values of assets are non-negative. • create table branch (branch-name char(15),branch-city char(30),assets integer,primary key (branch-name),check (assets >= 0))

  19. The droptable and alter table keywords • 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 relationalter table r drop Awhere A is the name of an attribute of relation r

  20. Query Language SQL

  21. Schema Used in Examples

  22. The select clause • The select clause list the attributes desired in the result of a query • corresponds to the projection operator • E.g. find the names of all branches in the loan relationselect branch-namefrom loan • SQL names are case insensitive, i.e. you can use capital or small letters.

  23. SQL allows duplicates in query results hence in general an SQL table is not a set of tuples but rather a multiset(a.k.a bag) of tuples • To force the elimination of duplicates, insert the keyword distinct after select. • Find the names of all branches in the loan relations, and remove duplicates select distinct branch-namefrom loan • An asterisk in the select clause denotes “all attributes” select *from loan

  24. The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. selectloan-number, branch-name, amount 100from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by a 100.

  25. The from clause • The from clause lists the relations involved in the query • corresponds to the Cartesian product operator • Find the Cartesian product borrower x loan • select from borrower, loan • select *from borrower, loanwhere borrower.loan-number = loan.loan-number

  26. The where clause • The where clause specifies conditions that the result must satisfy • corresponds to the selection operator • To find all loan numbers for loans made at the Fargo branch with loan amounts greater than $1200.select loan-numberfrom loanwhere branch-name = “Fargo” and amount > 1200 • Comparison results can be combined using the logical connectives and, or, and not.

  27. SQL includes a between comparison operator • E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, $90,000 and $100,000) (i.e. inclusive)select loan-numberfrom loanwhere amountbetween 90000 and 100000

  28. SQL also includes an in operator used for set inclusion testing • E.g. Find the loan number of those loans with loan amounts equal to $90000, $100000, $ 110000 or $120000 • select loan-numberfrom loanwhere amountin (90000, 100000, 110000, 120000)

  29. Ambiguous Names and Aliasing • SQL allows renaming relations and attributes using the as clause:old-name as new-name • Find the name, loan number and loan amount of all customers; rename the column name loan-number as loan-id.select customer-name, borrower.loan-number as loan-id, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-number

  30. Find the customer names, their loan numbers and loan amounts for all customers having a loan at some branch. select customer-name, T.loan-number, S.amountfrom borrower as T, loan as Swhere T.loan-number = S.loan-number

  31. Find the names of all branches that have greater assets than some branch located in Fargo. select distinct T.branch-namefrom branch as T, branch as Swhere S.branch-city =‘”Fargo” and T.assets > S.assets

  32. String Operations • SQL includes a string-matching operator for comparisons on character strings. (LIKE) • Patterns are described using two special characters: • percentage (%). The % character matches any substring. • underscore (_). The _ character matches any character. • Find the names of all customers whose street includes the substring “Main”. select customer-namefrom customerwherecustomer-street like ‘%Main%’

  33. Find the names of all customers whose street includes the substring “Main” with length 5 (don’t use %) select customer-namefrom customerwherecustomer-street like ‘Main_’ or like ‘_Main’ • 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.

  34. Soundex operation • Database developers have long struggled with the problem of matching words that might not look alike, but actually sound alike…Useful for finding strings for which the sound is known but the precise spelling is not. • Soundex (argument) • Returns a 4 character code representing the sound of the words in the argument. • This result can be used to compare with the sound of other strings. • The argument can be a character string that is either a CHAR or VARCHAR not exceeding 4,000 bytes.

  35. The result of the function is CHAR(4). The result is null if the argument is null • How does it work: • A,E,I,O,U,Y,W,H are ignored along with double letters • Ignore everything after the 4th character • The first letter of the soundex code corresponds to the first letter of the argument • Every subsequent character in the name is looked up according to the scheme presented on the next slide and encoded as a digit between 1 and 6

  36. Select Soundex ('Smith'), Soundex ('Smythe')  S530 S530 • if there are insufficient characters remaining in the name, the coding is padded with zeros. • Two adjacent identically coded letters • Nonalphabetic characters terminate the soundex evaluation • 1 = B,P,F,V • 2 = C,S,G,J,K,Q,X,Z • 3 = D,T • 4 = L • 5 = M,N • 6 = R • All other letters (A,E,I,O,U,Y,W,H) ignored

  37. There is also a related function called DIFFERENCE function • Used to compare strings based upon their Soundex values.  • Difference(Argument1,Argument2) • Usually returns an integer result ranging in value from 0 (least similar) to 4 (most similar).

  38. Let's suppose you overheard a male employee talking in the hallway but didn't quite catch his name.  • You might overhear a name that sounded like "Ann" but you're positive it was a male.  • The DIFFERENCE function is ideal for this type of situation.  • We'd probably begin by specifying a threshold value of 4 to limit the number of results returned.

  39. Select firstname, difference(firstname,'ann') as 'difference' from employees wheredifference(firstname,'ann')=4 • FirstName Difference ---------- ----------- Ann 4 Anne 4 • Unfortunately, none of the results were male names. So we try to lower the threshold of 2

  40. Select firstname, difference(firstname,'ann') as 'difference' from employees wheredifference(firstname,'ann')>=2 • FirstName Difference ---------- ----------- Ann 4 Andrew 2Janet 2 Laura 2 Anne 4 • looks like Andrew might be our man!

  41. The order by clause • List in alphabetic order the names of all customers having a loan in Fargo branch select distinct customer-namefrom borrower, loanwhere borrower loan-number - loan.loan-number and branch-name = “Fargo”order by customer-name • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. • E.g. order bycustomer-namedesc

  42. Set Operations • The set operations union, intersect, and except operate on relations and correspond to the relational algebra operations and  • Each of the above operations automatically eliminates duplicates; to retain all duplicates use the corresponding versions union all, intersect all and except allSuppose a tuple occurs m times in r and n times in s, then, it occurs: • m + n times in r union all s • min(m,n) times in rintersect all s • max(0, m – n) times in rexcept all s

  43. Find all customers who have a loan, an account, or both: (selectcustomer-name from depositor)union (selectcustomer-name from borrower) • Find all customers who have both a loan and an account. (selectcustomer-name from depositor)intersect (selectcustomer-name from borrower)

  44. Find all customers who have an account but no loan (selectcustomer-name from depositor)except (selectcustomer-name from borrower)

  45. Divide operator • No SQL implementation • For instance, if you need to find all students who are taking the three courses {7,6,5} • the answer would be E/C

  46. Aggregate Functions • These functions avg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values • Produce numbers

  47. Find the average account balance at the Fargo branch. select avg (balance)from accountwhere branch-name = “Fargo” • Find the number of tuples in the customer relation. select count (*)from customer • Find the distinct number of depositors in the bank. select count (distinct customer-name)from depositor

  48. The group by clause • Find the number of depositors for each branch. select branch-name, count (customer-name)from depositor, accountwhere depositor.account-number = account.account-numbergroup by branch-name Note: Attributes in select clause outside of aggregate functions must appear in group by list

  49. The having clause • Find the names of all branches where the average account balance is more than $1,200. select branch-name, avg (balance)from accountgroup by branch-namehaving avg (balance) > 1200 Note: predicates in the having clause are applied after the formation of the aggregation groups whereas predicates in the where clause are applied before forming groups

More Related