DOC PREVIEW
SJSU CS 157A - 26FCS157Neyha_Amar

This preview shows page 1-2-21-22 out of 22 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 22 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 22 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 22 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 22 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 22 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

SQLInsertingExample of InsertionUpdatingExample 2: UpdatingDeletionExample 2: DeletionSet OperationsUnion OperationContinue… Union OperationIntersect OperationExcept OperationAggregate FunctionsExample: Aggregate FunctionGroup By ClauseExample: GROUP BYExample GROUP BY…HAVING clauseExample: HAVINGNull ValuesExamples: Null ValuesReferencesSQLSQLNeyha AmarCS 157A, Fall 2006InsertingInsertingThe insert statement is used to add a row of data into a tableStrings should be enclosed in single quotes, and numbers should not.Consider the following table: EmployeeEmpID name DeptID1 John Hats2 David Shoesinsert into Employeevalues(3, ‘Steve’, ‘Glasses’) OR insert into Employee(EmpID, name, DeptID)values(3, ‘Steve’, ‘Glasses’) ORinsert into Employee(name, DeptID, EmpID)values(‘Steve’, ‘Glasses’, 3) EmpID name DeptID1 John Hats2 David Shoes3 Steve Glasses Row insertedExample of InsertionExample of InsertionUpdatingUpdatingThe update statement is used to change a value in a tuple that matches a specified criteria. General Form: update tablename set column = new value, nextcolumn = new value2, … where somecolumn [and | or othercolumn] OPERATOR valueExample1 (from textbook): update account set balance = balance * 1.05 where balance >= 1000  Without the WHERE all values under the specified column will be updatedExample 2: UpdatingExample 2: Updating update Employee set DeptID = ‘Shoes’ where name = ‘Steve’EmpID name DeptID1 John Hats2 David Shoes3 Steve Glasses EmployeeEmpID name DeptID1 John Hats2 David Shoes3 Steve Shoes updated row DeletionDeletionThe delete statement is used to delete tuples from the tableWe can only delete whole tuples, not values in only particular attributes General Form: delete from tablename where somecolumn [and | or othercolumn] OPERATOR valueExample 1: delete from account where branch_name = ‘Perryridge’w/o the WHERE all records will be deleted !!!Example 2: DeletionExample 2: DeletionEmpID name DeptID1 John Hats2 David Shoes3 Steve Glassesdelete from Employeewhere EmpID = 3EmpID name DeptID1 John Hats2 David ShoesEmployeeSet OperationsSet OperationsSQL operations Union, Intersect, and Except operate on relations and correspond to relational algebra operations union, intersection and set differenceRelations participating in operations must be compatible, that is they must have the same attributesUnion OperationUnion OperationExample: Find all the bank customers having a loan, an account, or both at the bank. (select customer_name from depositor) union (select customer_name from borrower)oUnion operation automatically eliminates duplicates If customer has several accounts or loans (or both) at the bank then he/she will appear only ONCE in the result.Continue… Union OperationContinue… Union OperationoIf you want to retain all duplicates, you must write UNION ALL in place of UNION (select customer_name from depositor) union all (select customer_name from borrower)Intersect OperationIntersect OperationExample: Find all the bank customers having a loan AND an account at the bank. (select distinct customer_name from depositor) intersect (select distinct customer_name from borrower)oautomatically eliminates duplicatesoto retain them use INTERSECT ALL in place of INTERSECTExcept OperationExcept OperationExample: Find all the bank customers having an account but NO loan at the bank. (select distinct customer_name from depositor) except (select distinct customer_name from borrower)oautomatically eliminates duplicates,oTo retain use EXCEPT ALL in place of EXCEPTAggregate FunctionsAggregate FunctionsFunctions that take a collection (a set or multiset) of values as input and return a single value.SQL has five built-in aggregate functions:- avg ( [distinct | all] n) – returns average value of n- min ( [distinct | all] expr) – returns minimum value of expr- max ( [distinct | all] expr) – returns maximum value of expr- sum ( [distinct | all] n) – returns total sum of values in expr- count (* | [distinct | all] expr) - returns # of rows * - return # of rows including NULL values from relation distinct - return # of rows eliminating duplicates and NULL values from expr all - return # of rows including duplicates but no NULL values from exprThe input to sum and avg must be a collection of numbersOthers can operate on collections of nonnumeric data types, such as strings, as well.Example: Aggregate FunctionExample: Aggregate FunctionExample: Find the average account balance at the Perryridge branch. select avg (balance) from account [as average] where branch_name = ‘Perryridge’oResult will be a relation with a single attribute, containing a single tuple that equals the average account balance at Perryridge oCan give a name to the attribute by using the AS clauseGroup By ClauseGroup By ClauseGROUP BY is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement.When that happens, we need to GROUP BY all the columns except the one(s) operated on by the arithmetic operatorExample: GROUP BYExample: GROUP BYConsider the following relation schema:Store_InformationStore_name Sales DateLos Angeles $1800 10-05-2006San Diego $250 10-07-2006Boston $700 10-08-2006Example GROUP BY…Example GROUP BY…Query: Find the total sales for each store.Answer: select Store_name, sum(Sales) from Store_Information group by store_nameResult:Store_name sum(Sales)Los Angeles $1800San Diego $250Boston $700HAVING clauseHAVING clauseHAVING serves as the WHERE clause for grouped dataThis condition does not apply to a single tuple; it applies to each group constructed by the group by clause.Example: HAVINGExample: HAVINGQuery: Find the total sales for each store that has a total sale greater than $1500.Answer: select Store_name, sum(Sales) from Store_Information group by store_name having sum(Sales) > 1500Result:Store_name sum(Sales)Los Angeles $1800Null ValuesNull ValuesSQL allows the use of null values to indicate absence of information about the value of an attribute.We can use the special keyword null in a predicate to test for a null valueExamples: Null ValuesExamples: Null ValuesQuery: Find all loan numbers that appear in the loan relation with null values for amount.Answer: select loan_number from loan where


View Full Document

SJSU CS 157A - 26FCS157Neyha_Amar

Documents in this Course
SQL

SQL

18 pages

Lecture

Lecture

44 pages

Chapter 1

Chapter 1

56 pages

E-R Model

E-R Model

16 pages

Lecture

Lecture

48 pages

SQL

SQL

15 pages

SQL

SQL

26 pages

Lossless

Lossless

26 pages

SQL

SQL

16 pages

Final 3

Final 3

90 pages

Lecture 3

Lecture 3

22 pages

SQL

SQL

25 pages

Load more
Download 26FCS157Neyha_Amar
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view 26FCS157Neyha_Amar and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view 26FCS157Neyha_Amar 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?