SQL

1. SQL Credits: Drs. Liu and Raj, Silberschatz, et al.

2. Structured Query Language (SQL) • Developed by IBM (system R) in the 1970s • Standards (needed due to multiple vendors) • SQL-86 • SQL-89 (minor revision) • SQL-92, aka SQL2 (major revision) • Most DBMS in production support this • SQL:1999, aka SQL3 (major extensions/OO) • SQL:2003 (more extensions, some XML) • SQL:2006 (better integration with XML) Check standard supported by your DBMS version

3. Two Facets of SQL • Data Manipulation Language (DML) • C: insert statement • R: select statement • U: update statement • D: delete statement • Data Definition Language (DDL) • The schema for each relation. • The domain of values associated with each attribute. • Integrity constraints • The set of indices to be maintained for each relations. • Security and authorization information for each relation.

4. Integrity Constraints (ICs) • Checks that prevent manipulations (CUD) that violate constraints set up by the data expert • Guard against accidental damage • Prevent loss of consistency due to changes • Ensure application semantics • Constraints supported via SQL features • Domain constraints • Referential integrity constraints • Assertions • Triggers (really a general mechanism that can be used for constraint checking)

5. Domain (aka Type) Constraints • Most elementary form of integrity constraint • Ensures that values stored are type safe • Prevents bad data from being kept in database • Ensure only valid queries are used • Avoids nonsensical comparisons • My apple is bigger than your orange

6. Referential Integrity • If a table entry refers to another table, guarantee that the reference exists (no dangling pointers) • If Fido is a dog name mentioned in doghouse, then a row for Fido exists in dog • If passenger Smith is booked on Flight 100, then 100 is a flight mentioned in the flight table • Referential integrity in SQL • Specified by keys as part of create table statement • Primary keys, unique keys and foreign keys • Keys need not be single attributes, could be a set of attributes

7. Common Domain Types in SQL • Strings • char(n), varchar(n) (see more on next slide) • Numbers • int, smallint, numeric(p,d), real, double precision, float(n) • Date and time • date, time, datetime • User-defined domains • create domain SSNtype char(11) not null • Null values allowed for all domains • May be overridden by NOT NULL

8. Other Data Types • date: Dates, containing a (4 digit) year, month and date • Example: date‘2005-7-27’ • time: Time of day, in hours, minutes and seconds. • Example: time‘09:00:30’ time‘09:00:30.75’ • timestamp: date plus time of day • Example: timestamp‘2005-7-27 09:00:30.75’ • interval: period of time • Example: 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

9. User Defined Types • create typeconstruct in SQL creates user-defined type create type Dollars as numeric (12,2) final • create table department(dept_namevarchar(20),building varchar(15),budget Dollars);

10. blobs and clobs • Large objects (photos, videos, CAD files, etc.) are stored as a large object: • blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system) • clob: character large object -- object is a large collection of character data • When a query returns a large object, a pointer is returned rather than the large object itself.

11. char(n) and varchar(n) • Either used to define a character type • Use char(n) for a fixed-length string • Use varchar(n) for variable-length string • Note that storing “dog” in a char(6) string actually stores “dogXXX” where X is a space. In other words, it pads all strings to make the consistent length. varchar(n) does not pad the string. • Why did I mention this? • Recommendation is to use varchar

12. Create Table Construct • An SQL relation is defined using thecreate tablecommand: create table r(A1D1, A2D2, ..., AnDn,(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_namevarchar(15) not null,branch_cityvarchar(30),assets integer);

13. Primary Keys • Primary keys are denoted in relations by listing them first and underlining them: • STUDENT (studentID, name, major) • PRIMARY KEY (studentID) • For PKs made up of several attributes (composite keys), all of the attributes that are part of the key are underlined: • COURSE (departmentID, courseNumber, courseDescription) • PRIMARY KEY(departmentID, courseNumber) • A PK constraint means that the attribute must be: • Unique • Not Null

14. Create Table Construct & Key • Indicate the primary key in the create table command: create table branch (branch_namevarchar(15) not null,branch_cityvarchar(30),assets integer), primary key (branch_name)); • Remember that the primary key must be guaranteed to be non-null and unique

15. Foreign Keys • Foreign keys are added to a relation to show the relationship between two (or more) entities. • Example: • Department (DeptID,DeptName, Location); • Employee (EmpID, EmpName, DeptID); • FOREIGN KEY (DeptID) REFERENCES Department(DeptID) • or FOREIGN KEY (DeptID) REFERENCES Department Table name

16. 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, ..., Anfromr1, r2, ..., rmwhere P • Ai represents an attribute • Rirepresents a relation • P is a predicate. • The result of an SQL query is a relation.

17. The 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 • NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) • E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name

18. 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: selectloan_number, branch_name, amount  100from loan would return a relation that is the same as the loan relation, except that the value of the attribute amount is multiplied by 100.

19. Cartesian Product • For each tuple in each relation, concatenate into a single tuple

20. Cartesian Product • RRes Result is a relation consisting of each instructor concatenated with each of the “teaches” Instances on that relation Note that this example is provided in the text on page 69 – and the answer provided Is wrong!

21. The from Clause • The fromclause lists the relations involved in the query • Corresponds to the Cartesian product operation of the relational algebra. • Find the Cartesian product borrower X loan select from borrower, loan • The Cartesian product is generated if > 1 relation

22. Examples • Refer to Figure 2.1 from your text to answer the response to the queries (instructor relation)

23. Sample Query • select name from instructor;

24. Sample Query • select dept_name from instructor; (notice the duplicates)

25. Sample Query • Use select all dept_name from instructor; to explicitly indicate that duplicates should not be removed

26. Sample Query • select distinct dept_name from instructor; • Use of distinct to eliminate duplicates • Duplicateretention is the default

27. Sample Query • What will this query return? • select ID, name from instructor;

28. Sample Query • What will this query return? • select ID, name from instructor; • Returns a relation containing all instructor IDs and names

29. Sample Query • What will this query return? • select ID, name, salary * 1.1 from instructor;

30. Sample Query • What will this query return? • select ID, name, salary * 1.1 from instructor; • Returns a relation containing all instructor IDs, names, and salaries incremented by 10% • Note that it returns a separate relation; the original data in the table is unchanged

31. select/from/where • To reference a specific attribute, use the dot notation • Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch. • select customer_name, borrower.loan_number, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_numberand branch_name = 'Perryridge’;

32. The where Clause • The whereclause 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.

33. 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) selectloan_numberfrom loanwhere amountbetween 90000 and 100000 Check your implementation of SQL. “between X and Y” may include X and or Y, or exclude both

34. Sample Query • Back to our instructor relation: What will this query return? • Select name from instructor where dept_name = ‘Comp Sci’ and salary > 70000;

35. Sample Query • Back to our instructor relation: What will this query return? • Select name from instructor where dept_name = ‘CompSci’ and salary > 70000; • Trick question: Returns nothing because the dept_name doesn’t match.

36. Sample Query • Back to our instructor relation: What will this query return? • Select name from instructor where dept_name = ‘Comp. Sci.’ and salary > 70000; • Returns a relation containing Katz and Brandt

37. The Rename Operation • The 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_numberas loan_id, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number

38. String Operations • SQL standard is that strings are case sensitive • Some database systems do not distinguish case – you need to know the specifics for the database you are using • SQL supports functions for string concatenation, substrings, length, converting to lower/upper case, etc • Different operations supported on various databases

39. String Operations • Pattern Matching • % matches any substring • _ (underscore) matches any character • Patterns are case sensitive

40. Like • The operator “like” uses patterns that are described using two special characters: • percent (%). 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_streetlike '% Main%’ See the book if you want to use a % or _ in the actual pattern

41. Other String Operations • 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.

42. Tuple Variables • Tuple variables are defined in the from clause via the use of the as clause. • Find the customer names and their loan numbers 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

43. Tuple Variables Find the names of all branches that have greater assets than some branch located in Brooklyn. select distinct T.branch_namefrom branch as T, branch as S  rename the same relation to do 1-1 comparewhere T.assets > S.assetsand S.branch_city = 'Brooklyn‘

44. Ordering the Display of Tuples • List in alphabetic order the names of all customers having a loan in Perryridge branch select distinct customer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number and branch_name = 'Perryridge' order by customer_name • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. • Example: order bycustomer_namedesc

45. Modification of the Database – Deletion • Delete all account tuples at the Perryridge branch delete from accountwhere branch_name = 'Perryridge' • Delete all accounts at every branch located in the city ‘Needham’. delete from accountwhere branch_name in (select branch_namefrom branchwhere branch_city = 'Needham')

46. Modification of the Database – Insertion • Add a new tuple to account insert into accountvalues ('A-9732', 'Perryridge', 1200) or equivalentlyinsert into account (branch_name, balance, account_number)values ('Perryridge', 1200, 'A-9732') • Add a new tuple to account with balance set to null insert into accountvalues ('A-777','Perryridge', null )

47. Modification of the Database – Updates • Increase all accounts with balances over $10,000 by 6% update accountset balance = balance  1.06where balance > 10000

48. Case Statement for Conditional Updates • Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. updateaccountsetbalance = casewhenbalance <= 10000 thenbalance *1.05elsebalance * 1.06end