sql ii
Download
Skip this Video
Download Presentation
SQL-II

Loading in 2 Seconds...

play fullscreen
1 / 22

SQL-II - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

SQL-II. Reading: C&B, Chap 5. In this lecture you will learn. how to sort and group query results how to calculate aggregates and other derived data how to insert, modify & delete row data the role of NULL values in databases how to combine query results using set operations

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-II' - elaina


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
sql ii

SQL-II

Reading: C&B, Chap 5

in this lecture you will learn
In this lecture you will learn
  • how to sort and group query results
  • how to calculate aggregates and other derived data
  • how to insert, modify & delete row data
  • the role of NULL values in databases
  • how to combine query results using set operations
  • how to compose and use nested SELECT queries

Dept. of Computing Science, University of Aberdeen

more control over select
More Control Over SELECT
  • What have we achieved so far using SELECT?
    • Retrieve data from all the rows and columns (whole table)
    • Retrieve data from all the rows and select columns
    • Retrieve data from select rows and columns
  • Sometimes we want to re-format the output from SELECT:
    • E.g. for reports...
  • Examples of additional processing:
    • Eliminate duplicates
    • Sort and/or group the results
    • Rename column headings
    • Calculate totals, averages, etc. (often called aggregates)
    • Combine results from different tables

Dept. of Computing Science, University of Aberdeen

use of distinct
Use of DISTINCT

Viewing

  • List the property numbers of all properties that have been viewed
  • Query1:

SELECT propertyNo

FROM Viewing;

  • Query2:

SELECT DISTINCT propertyNo

FROM Viewing;

Query1

Query2

Dept. of Computing Science, University of Aberdeen

order by
ORDER BY

Staff

  • Produce a list of salaries for all staff, arranged in descending order of salary
  • Query1:

SELECT staffNo, fName, lName, salary

FROM Staff

ORDER BY salary DESC;

  • Query2:

SELECT staffNo, fName, lName, salary

FROM Staff

ORDER BY 4 ASC;

Query1

Query2

Dept. of Computing Science, University of Aberdeen

calculated fields

staffNo

fName

lName

Expr1003

SA9

Mary

Howe

750

SG14

David

Ford

1500

SG37

Ann

Beech

1000

SG5

Susan

Brand

2000

SL21

John

White

2500

SL41

Julie

Lee

7500

Calculated Fields

Staff

  • Produce a list of monthly salaries for all staff, showing the staff number, the first and last names, and the salalry details
  • Query1

SELECT staffNo, fName, lName, salary/12

FROM Staff;

Query1

Dept. of Computing Science, University of Aberdeen

renaming columns

staffNo

fName

lName

monthlySalary

SA9

Mary

Howe

750

SG14

David

Ford

1500

SG37

Ann

Beech

1000

SG5

Susan

Brand

2000

SL21

John

White

2500

SL41

Julie

Lee

7500

Renaming Columns

Staff

  • When new fields are calculated we can name them using ‘AS’
  • Query1:

SELECT staffNo, fName, lName, salary/12 AS monthlySalary

FROM Staff;

Query1

Dept. of Computing Science, University of Aberdeen

sql aggregate functions
SQL Aggregate Functions
  • We do not want to just retrieve data
  • We also want to summarise data
  • Aggregate functions compute summarization (or aggregation) of data
  • Aggregate functions
    • SUM
    • AVG
    • MIN
    • MAX
    • COUNT

Dept. of Computing Science, University of Aberdeen

sum min max avg

myMin

myMax

myAvg

mySum

9000

90000

30500

183000

SUM, MIN, MAX, AVG

Staff

  • Find the minimum, maximum, average and sum of staff salary
  • Query1

SELECT MIN(salary) AS myMin, MAX(salary) AS myMax,AVG(salary) AS myAvg, SUM(salary) AS mySum

FROM Staff;

Query1

Dept. of Computing Science, University of Aberdeen

count

WomenStaff

4

COUNT(*)

Staff

  • Counts the number of rows in a table
    • Including the rows that have duplicates and nulls

SELECT Count(*) as WomenStaff

FROM Staff

WHERE Sex=\'F\';

  • SELECT list (target list) cannot refer to any other column.

Query1

Dept. of Computing Science, University of Aberdeen

group by
GROUP BY
  • Aggregate functions help us to summarise the whole column(s) of data into one row.
  • Sometimes we want to group data before applying aggregate functions
    • This gives us ‘subtotals’ rather than ‘overall total’
  • GROUP BY is used to achieve that

Dept. of Computing Science, University of Aberdeen

group by example

branchNo

myCount

mySum

B003

3

54000

B005

2

120000

B007

1

9000

GROUP BY - Example

Staff

  • Find the number of staff working in each branch and the sum of their salaries
  • Query1:

SELECT branchNo, Count(staffNo) AS myCount, SUM(salary) AS mySum

FROM Staff

GROUP BY branchNo;

Query1

Dept. of Computing Science, University of Aberdeen

having

branchNo

myCount

mySum

B003

3

54000

B005

2

120000

HAVING

Staff

  • Query1:

SELECT branchNo, Count(staffNo) AS myCount, SUM(salary) AS mySum

FROM Staff

GROUP BY branchNo

HAVING COUNT(staffNo)>1;

Query1

Dept. of Computing Science, University of Aberdeen

adding data to a table using insert
Adding Data to a Table using INSERT
  • General format:

INSERT INTO TableName (colname1, colname2, ...)

VALUES (value1, value2, ...);

  • Example:

INSERT INTO Staff (StaffNo, Salary, Position, Lname)

VALUES (322, 15000, \'Assistant\', \'Smith\');

SELECT * FROM Staff;

Dept. of Computing Science, University of Aberdeen

why do db systems allow nulls
Why Do DB Systems Allow NULLs ?
  • A NULL value can be used to represent several situations:
  • Don\'t care; Don\'t know; Don\'t know yet; Used to know!
  • SQL has special rules and logic to handle NULLs:

SELECT * FROM Staff WHERE Fname IS NULL;

  • NB. WHERE Colname = \'NULL\' does not work !
  • Can also say: WHERE Colname IS NOT NULL
  • NULLs can be useful, difficult, or dangerous
  • Use NULLs wisely!

Dept. of Computing Science, University of Aberdeen

modifying deleting data
Modifying & Deleting Data
  • Changing specific values in a table:

UPDATE Staff SET Salary = 1.05 * Salary

WHERE Position = \'Director\';

  • Deleting specific rows:

DELETE FROM Staff WHERE Fname IS NULL;

  • Deleting all rows of a table:

DELETE FROM Staff;

  • Completely removing a table is a DDL operation:

DROP TABLE Staff;

Dept. of Computing Science, University of Aberdeen

combining results tables
Combining Results Tables
  • Sometimes its useful to be able to combine query results using set operations:

R - S

R ∩ S

R U S

R

R

R

S

S

S

(c) Difference

(a) Union

(b) Intersection

Dept. of Computing Science, University of Aberdeen

set operations in sql
Set Operations in SQL
  • Syntax:

(SELECT ...) UNION (SELECT ...)

(SELECT ...) INTERSECT (SELECT ...)

(SELECT ...) EXCEPT (SELECT ...)

  • Some DBMSs use MINUS instead of EXCEPT
  • For set operations, the tables must be union-compatible
    • i.e. have the same number and types of columns

Dept. of Computing Science, University of Aberdeen

set operation example
Set Operation Example
  • List the cities with both a branch office & a property for rent:

(SELECT City FROM Branch) INTERSECT

(SELECT City FROM PropertyForRent);

Cities with branch office OR a property for rent: UNION

Cities with branch office but NO props for rent: EXCEPT

Dept. of Computing Science, University of Aberdeen

using subqueries nested selects
Using Subqueries - Nested SELECTs
  • If we know that a SELECT statement will produce a single value,
  • we can use this value directly in a simple predicate.
  • Example: find all members of staff who earn more than Smith:

SELECT * FROM Staff WHERE

Salary > (SELECT Salary FROM Staff WHERE Lname =\'Smith\');

Dept. of Computing Science, University of Aberdeen

subqueries that give a list of values
Subqueries That Give a List of Values
  • If the SELECT statement is expected to produce a list of values, we can use the IN, ANY, ALL or EXISTS keywords to operate on the list.
  • Example: find those members of staff who are not managers but earn more than at least one manager:

SELECT * FROM Staff WHERE

Position <> \'Manager\' AND Salary > ANY (SELECT Salary

FROM Staff WHERE Position = \'Manager\');

  • ALL: Predicate is true if test is true for all list elements
  • IN: Predicate is true if the test element is in the list
  • EXISTS: Predicate is true if list is non-empty

Dept. of Computing Science, University of Aberdeen

summary so far
Summary So Far...
  • SQL is a powerful but “quirky" query language
  • SQL is not like other programming languages (no variables)
  • You can answer many kinds of question...
  • You can build up complex queries from simpler sub-queries
  • It often requires thought to compose complex queries...
  • Approach: Break problem into sub-parts, and then build up final query...
  • Next lecture: queries using multiple tables (joins)

Dept. of Computing Science, University of Aberdeen

ad