slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL PowerPoint Presentation
Download Presentation
SQL

Loading in 2 Seconds...

play fullscreen
1 / 22

SQL - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

SQL. Neyha Amar CS 157A, Fall 2006. Inserting. 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: Employee. Example of Insertion. insert into Employee

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL' - cachet


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQL

Neyha Amar

CS 157A, Fall 2006

inserting
Inserting
  • 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:

Employee

example of insertion
Example of Insertion

insert into Employee

values(3, ‘Steve’, ‘Glasses’) OR

insert into Employee(EmpID, name, DeptID)

values(3, ‘Steve’, ‘Glasses’) OR

insert into Employee(name, DeptID, EmpID)

values(‘Steve’, ‘Glasses’, 3)

 Row inserted

updating
Updating
  • 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 value

Example1 (from textbook):

update account

set balance = balance * 1.05

where balance >= 1000

  • Without the WHERE all values under the specified column will be updated
example 2 updating
Example 2: Updating

Employee

updateEmployee

set DeptID = ‘Shoes’

wherename = ‘Steve’

updated row 

deletion
Deletion
  • 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 value

Example 1:

delete fromaccount

wherebranch_name = ‘Perryridge’

  • w/o the WHERE all records will be deleted !!!
example 2 deletion
Example 2: Deletion

Employee

delete fromEmployee

where EmpID = 3

set operations
Set Operations
  • SQL operations Union,Intersect,andExcept 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 attributes
union operation
Union Operation

Example: Find all the bank customers having a loan, an account, or both at the bank.

(selectcustomer_name

fromdepositor)

union

(selectcustomer_name

fromborrower)

  • Union 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 operation
Continue… Union Operation
  • If you want to retain all duplicates, you must write UNION ALL in place of UNION

(selectcustomer_name

fromdepositor)

union all

(selectcustomer_name

fromborrower)

intersect operation
Intersect Operation

Example: Find all the bank customers having a loan AND an account at the bank.

(select distinctcustomer_name

fromdepositor)

intersect

(selectdistinct customer_name

fromborrower)

  • automatically eliminates duplicates
  • to retain them use INTERSECT ALLin place of INTERSECT
except operation
Except Operation

Example: Find all the bank customers having an account but NO loan at the bank.

(selectdistinct customer_name

fromdepositor)

except

(selectdistinct customer_name

fromborrower)

  • automatically eliminates duplicates,
  • To retain use EXCEPT ALLin place of EXCEPT
aggregate functions
Aggregate 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 function
Example: Aggregate Function

Example: Find the average account balance at the Perryridge branch.

selectavg (balance)

from account [as average]

wherebranch_name = ‘Perryridge’

  • Result will be a relation with a single attribute, containing a single tuple that equals the average account balance at Perryridge
  • Can give a name to the attribute by using the AS clause
group by clause
Group 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 operator
example group by
Example: GROUP BY

Consider the following relation schema:

Store_Information

example group by17
Example GROUP BY…

Query: Find the total sales for each store.

Answer:

select Store_name, sum(Sales)

from Store_Information

group by store_name

Result:

having clause
HAVING 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 having
Example: HAVING

Query: 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) > 1500

Result:

null values
Null 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 nullin a predicate to test for a null value
examples null values
Examples: Null Values

Query: Find all loan numbers that appear in the loan relation with null values for amount.

Answer:

select loan_number

from loan

where amountis null

Query: Find all loan numbers that appear in the loan relation that do not have null values for amount.

Answer:

select loan_number

from loan

where amountis not null

references
References
  • Database System Concepts, 5th

edition, Silberschatz, Korth, Sudarshan

  • http://www.nd.edu/~pmiller/capp_sql/sql_group_functions.ppt
  • http://databases.about.com/od/sql/l/aaaggregate2.htm