Basic SQL

1. CS157A Lecture 12 Basic SQL Prof. Sin-Min Lee Department of Computer Science Prof. Sin-Min Lee Department of Computer Science

2. In this chapter, you will learn: • The basic commands and functions of SQL • How SQL is used for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information • About more advanced SQL features such as updatable views, stored procedures, and triggers

3. Introduction to SQL • Ideal database language • Create database and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information • SQL is the ideal DB language • Data definition language • Data manipulation language

4. Background • IBM developed the original version of SQL at its San Jose Research Laboratory • Evolved as The Sequel language, its name has changed to SQL (Structured Query Language) • SQL has clearly established itself as the standard relational-database language

5. Good Reasons to Study SQL • ANSI standardization effort led to de facto query standard for relational database • Forms basis for present and future DBMS integration efforts • Becomes catalyst in development of distributed databases and database client/server architecture

6. Basic Structure of SQL • Consists of three clauses: • Select - Used to list the attributes desired in the result of a query. • From - Lists the relations to be scanned in the evaluation of the expression. • Where - Consists of a predicate involving attributes of the relations that appear in the from clause.

7. SQL a Short Introduction • SQL stands for Structured Query Language • Queries are used to extract information from the database. • An SQL expression/block consists of three main clauses: • select (projection operation) : Lists the attributes desired in the result of a query • from (Cartesian-product operation) : List the relation to be scanned in the evaluation of the expression. • where (selection predicate) : Consists of predicate involving attributes of the relations that appear in the from clause Example: select name from students where gpa = '4.0' attribute relation condition

8. A typical SQL query form • Select: A1, A2,….An • Ai represents an attribute. • From: r1, r2,….rm • ri is a relation • Where: P • P represents a predicate.

9. The Select Clause • Example of a Simple Query: • “Find the names of all branches in the loan relation” selectbranch-name fromloan

10. Creating Table Structure • Tables store end-user data • May be based on data dictionary entries CREATE TABLE <table name>(<attribute1 name and attribute1 characteristics,attribute2 name and attribute2 characteristics,attribute3 name and attribute3 characteristics,primary key designation,foreign key designation and foreign key requirement>);

11. Data Definition Commands • Create database structure • Holds all tables and is a collection of physical files stored on disk • DBMS automatically creates tables to store metadata • Database administrator creates structure or schema • Logical group of tables or logical database • Groups tables by owner • Enforces security • CREATE SCHEMA AUTHORIZATION <creator> • Example:CREATE SCHEMA AUTHORIZATION JONES

12. Using Domains • Domain is set of permissible values for a column • Definition requires: • Name • Data type • Default value • Domain constraint or condition CREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>] [CHECK (<condition>)]

13. Different parts of SQL • Data-definition language • Interactive data-manipulation language • View definition • Transaction Control • Embedded SQL and dynamic SQL • Integrity • Authorization

14. More examples continued • Inserting keyword distinct after select we can eliminate duplication • For instance: select distinctbranch-name fromloan • Inserting keyword all after select helps restoring duplication.

15. The where clause • Example: “Find all loan numbers for loans made at the Perryridge branch with loan amounts greater than $ 1200.” selectloan-number fromloan wherebranch-name = ‘Perryridge’ and amount > 1200

16. More examples of Where clause • Logical connectives like and, or, and not are used in the where clause • Example: • Loan number of those loans with loan amounts between $90,000 & $ 100,000 selectloan number fromloan whereamount between 90000 and 100000

17. The from Clause • Defines a Cartesian product of the relations in the clause. • Example: • “For all customers who have a loan from the bank, find their names, loan numbers and loan amount”

18. The from Clause (Con’d) selectcustomer-name, borrower.loan-number, amount fromborrower, loan whereborrower.loan-number = loan.loan-number

19. The Rename Operation • Uses asclause to rename both, relations and attributes • The as clause takes the form in SQL: old-name as new-name

20. The Rename Operation (Con’d) • Example: • To change attribute name loan-number to be replaced with name loan-id : select customer-name, borrower.loan-number as loan-id, amount fromborrower, loan whereborrower.loan-number = loan.loan-number

21. String Operations • SQL specifies by enclosing in single quotes, for example, ‘Perryridge’ • “%” character is use to match any substring. • “_” character is use to match any character • It expresses patterns by using the like comparison operator

22. String Operations (Con’d) • Example: • Find the names of all customers whose street address includes the substring ‘Main’ selectcustomer-name fromcustomer where customer-street like ‘%Main%’

23. Set Operations • Operations such as union, intersect, ad except operate on relations. • Corresponds to relational-algebra operations ,  and . • Relations participating in the operations must be compatible; i.e. must have same set of attributes.

24. Union Operation • Example: • To find all customers having a loan, an account, or both at bank: (selectcustomer-name fromdepositor) union (selectcustomer-name fromborrower)

25. Intersect Operation • Example: • To find all customers who have both a loan and an account at the bank: (select distinctcustomer-name fromdepositor) intersect (select distinctcustomer-name fromborrower)

26. Except Operation • Example: • To find all customers who have an account but no loan at the bank: (select distinctcustomer-name) fromdepositor) except (selectcustomer-name fromborrower)

27. Aggregate Functions • These functions take a collection of values as input and return a single value. • SQL offers five built-in aggregate functions: • Average: avg • Minimum: min • Maximum: max • Total: sum • Count: count

28. Aggregate Functions (Con’d) • Example: • Find the average account balance at the Perryridge branch.” select avg(balance) fromaccount wherebranch-name =‘Perryridge’

29. Null Values • Used to indicate absence of information about the value of an attribute. • Can use special keyword null in a predicate to test for a null value.

30. Null Values (Con’d) • Example: selectloan-number fromloan whereamountis null

31. Nested Subqueries • A subquery is a select-from-where expression that is nested within another query. • Common use includes: • Perform tests for set membership • Make set comparisons • Determine set cardinality

32. Nested Subqueries (Con’d) • Example: • Find those customers who are borrowers from the bank and who appear in the list of account holders obtained in the subquery selectdistinctcustomer-name fromborrower wherecustomer-name in (select customer- name from depositor)

33. Views • We define a view in SQL by using the create view command. • To define a view, we must give the view a name and must state the query that computes the view.

34. Views (Con’d) • Example: • Using view all-customer, we can find all customers of the Perryridge branch: selectcustomer-name fromall-customer wherebranch-name = ‘Perryridge’

35. Complex Queries • What are complex queries? Queries that are hard to write as a single SQL block. • Way to compose multiple SQL blocks: • Derived Relations: • Subquery expression to be used in the from clause. • The result relation must be given a name and the attributes can be renamed. Example: To find the average account balance of those branches where the avg acct balance is > 1200 select branch-name, avg-balance from (select branch-name, avg(balance) from account group by branch-name) as branch-avg (branch-name, avg-balance) where avg-balance > 1200 result relation renamed attribute ( Note: balance is an attribute in the relation. Since we're calculating the average balance it's more meaningful to rename balance to avg-balance )

36. Complex Queries cont'd • With clause: • Makes the query logic clearer by providing ways to define temporary views • view, like procedures ( in structure programming ), can be broken up into smaller views for clarity and reusability. • Permits view definition to be used in multiple places within a query.

37. Modification of the Database • Add, Remove, and Change information. • Insertion ( add ) : Insert data ( tuple or set of tuples ) into a relation Ex: insertintoStudentsvalues ( 'Jane Doe', '4.0', 'honor'); inserts the student Jane Doe into Students whose GPA 4.0 and who is an honor student. • Deletion ( remove ) : Deletes the entire tuple from a relation. Ex: delete fromStudentswherename="Jane Doe"; • Update ( change ): Changes a value in a tuple without changing all values in the tuple. Ex: 1. updateStudentssetgpa = 3.5wherename = 'Jane Doe' 2. updateStudentssetdean_list =case whengpa < 4.0then'regular' else 'honor' end

38. Joined Relations • SQL provides mechanisms for joining relations, including condition joins and natural joins. • Ways to join relations: • Inner join - Combines two relations which contain a common field and eliminating tuples that don't match. • left outer join - Combines two relations which contain a common field that results in tuples in left relation to be preserved and the unmatched tuples in the right relation filled with null values. • right outer join - Combines two relations which contain a common field that results in tuples in right relation to be preserved and the unmatched tuples in the left relation filled with null values. • natural join – Similar to inner join, however, the common attributes of the relations will appear only once.

39. Examples of joined relations: loan-num branch-name amount cust-name loan-num L-170 Downtown 3000 Jones L-170 • Suppose we have two relations loan and borrower: Inner join relation: loan inner join borrower on loan.loan-num = borrower.loan-num Natural inner join: loan naturalinner join borrower L-230 Redwood L-230 4000 Smith ` L-260 L-155 PerryRidge 1700 Hayes Loan Borrower loan-num loan-num branch-name branch-name amount amount cust-name Downtown Downtown L-170 L-170 3000 3000 Jones L-170 L-230 L-230 Redwood Redwood L-230 4000 4000 Smith loan-num branch-name amount amount cust-name cust-name Downtown L-170 L-170 3000 3000 Jones Jones L-230 L-230 Redwood 4000 4000 Smith Smith

40. Joined relations cont’d • left outer join: Syntax:loanleft outer joinborroweronloan.loan-num = borrower.loan-num • Right outer join: Syntax:loanright outer joinborroweronloan.loan-num = borrower.loan-num loan-num loan-num branch-name branch-name amount amount cust-name Downtown Downtown L-170 L-170 3000 3000 Jones L-170 L-230 L-230 Redwood Redwood L-230 4000 4000 Smith 1700 L-260 Perryridge null null loan-num loan-num branch-name branch-name amount amount cust-name Downtown Downtown L-170 L-170 3000 3000 Jones L-170 L-230 L-230 L-230 Redwood Redwood 4000 4000 Smith null null null Hayes L-155

41. SQL Integrity Constraints • Adherence to entity integrity and referential integrity rules is crucial • Entity integrity enforced automatically if primary key specified in CREATE TABLE command sequence • Referential integrity can be enforced in specification of FOREIGN KEY • Other specifications to ensure conditions met: • ON DELETE RESTRICT • ON UPDATE CASCADE

42. Data Manipulation Commands Common SQL Commands Table 5.3

43. Data Entry and Saving • Enters data into a table • Saves changes to disk INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); COMMIT <table names> ;

44. Listing Table Contents and Other Commands • Allows table contents to be listed • UPDATE command makes data entry corrections • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used • DELETE command removes table row SELECT <attribute names> FROM <table names>;

45. Queries SELECT <column(s)>FROM <table name>WHERE <conditions>; • Creating partial listings of table contents Table 5.4 Mathematical Operators

46. Examples SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; • Mathematical operators • Mathematical operators on character attributes • Mathematical operators on dates SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’; SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘01/20/2002’;

47. Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT;

48. Operators SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288; • Logical: AND, OR, NOT • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra • Special • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL

49. Advanced Data ManagementCommands • ALTER - changes table structure • ADD - adds column • MODIFY - changes column characteristics • Entering data into new column ALTER TABLE <table name>ADD (<column name> <new column characteristics>); ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’;

50. Advanced Data Management Commands (con’t.) • Dropping a column • Arithmetic operators and rules of precedence ALTER TABLE VENDORDROP COLUMN V_ORDER; Table 5.5