my sql: data manipulation

My SQL: Data ManipulationProf. Sin Min Lee

Deparment of Computer Science

San Jose State University

Introduction

Writing an SQL Command Retrieving Data Building SQL Statements Performing Database Updates

SQL SQL (Structured Query Language) is

the standard language for commercial DBMSs SEQUEL (Structured English QUEry Language)

was originally defined by IBM for System R standardization of SQL began in the 80s current standard is SQL-99 SQL is more than a query language it includes a DDL, DML and

administration commands SQL is an example of a transform-oriented language. A language designed to use relations to transform inputs into required

outputs.

04/19/23

Basic structure of an SQL Basic structure of an SQL queryquery22

GeneralStructure

SELECT, ALL / DISTINCT, *,AS, FROM, WHERE

Comparison IN, BETWEEN, LIKE "% _"

Grouping GROUP BY, HAVING,COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )

Display Order ORDER BY, ASC / DESC

LogicalOperators

AND, OR, NOT

Output INTO TABLE / CURSORTO FILE [ADDITIVE], TO PRINTER, TO SCREEN

Union UNION

04/19/23

fieldfield typetype widthwidth contentscontentsid numeric 4 student id numbername character 10 namedob date 8 date of birthsex character 1 sex: M / Fclass character 2 classhcode character 1 house code: R, Y, B, Gdcode character 3 district coderemission logical 1 fee remissionmtest numeric 2 Math test score

22 The Situation:Student ParticularsThe Situation:Student Particulars

04/19/23

General StructureGeneral StructureII

SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;

FROMFROM tablenametablename WHEREWHERE conditioncondition

SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......

04/19/23

General StructureGeneral StructureII The query will select rows from the source tablename and output the result in table form.

Expressions expr1, expr2 can be : (1) a column, or (2) an expression of functions and fields.



And col1, col2 are their corresponding column names in the output table.

04/19/23

General StructureGeneral StructureII DISTINCT will eliminate duplication in the output

while ALL will keep all duplicated rows.

condition can be : (1) an inequality, or (2) a string comparison using logical operators AND, OR, NOT.



04/19/23

General StructureGeneral StructureIIBefore using SQL, open the student file:

USE studentUSE student

eg. 1eg. 1 List all the student records.List all the student records.

SELECT * FROM student

id name dob sex class mtest hcode dcode remission9801 Peter 06/04/86 M 1A 70 R SSP .F.9802 Mary 01/10/86 F 1A 92 Y HHM .F.9803 Johnny 03/16/86 M 1A 91 G SSP .T.9804 Wendy 07/09/86 F 1B 84 B YMT .F.9805 Tobe 10/17/86 M 1B 88 R YMT .F.: : : : : : : : :

Result

04/19/23

General StructureGeneral StructureIIeg. 2eg. 2 List the names and house code of 1A students.List the names and house code of 1A students.

SELECT name, hcode, class FROM student ;

WHERE class="1A"

Class

11AA

11AA

11AA

11BB

11BB

::

Class

11AA

11AA

11AA

11BB

11BB

::

class="1A"

04/19/23

General StructureGeneral StructureII

name hcode classPeter R 1AMary Y 1AJohnny G 1ALuke G 1ABobby B 1AAaron R 1A: : :

Result

eg. 2eg. 2 List the names and house code of 1A students.List the names and house code of 1A students.

04/19/23

General StructureGeneral StructureIIeg. 3eg. 3 List the residential district of the Red House List the residential district of the Red House

members.members.

SELECT DISTINCT dcode FROM student ;

WHERE hcode="R"

dcodeHHMKWCMKKSSPTSTYMT

Result

What is SQL? ISO SQL has two major

components: Data Definition Language (DDL) Data Manipulation Language

(DML)

Source: Database Systems Connolly/Begg

Data Definition Language (DDL)

Defining the database structure Tables

Controlling access to the data What a user can legally access


Data Manipulation Language (DML)

Retrieving Data Query tables

Updating Data Populate tables


Writing SQL Commands

SQL statement consists of reserved words and user-defined words Reserved words are a fixed part

of the SQL language and have a fixed meaning

User-defined words are made up by the user (according to syntax rules)


Reserved Words

Are fixed part of the SQL language Have a fixed meaning Require exact spelling Kept on the same line


User-defined Words

Are made up by the user Governed by a set of syntax rules Represent names of database

objects such as: Tables Columns Views Indexes


Data Manipulation

Select: query data in the database Insert: insert data into a table Update: updates data in a table Delete: delete data from a table


Literals

Non-numeric data values must be enclosed in single quotes: ’16 Holland Drive’ ‘CS157B’

Numeric data values must NOT be enclosed in single quotes: 6 600.00


Writing SQL Commands

Most components of an SQL statement are case insensitive, but one exception is that literal character data must be typed exactly as it appears in the database.


Simple Query

Select specifies which columns are to appear in the output.

From specifies the table(s) to be used.

Where filters the rows subject to some condition(s).


Simple Query

Group By forms groups of rows with the same column value.

Having filters the groups subject to some condition.

Order By specifies the order of the output.


Retrieve all columns and all rows

SELECT firstColumn,…,lastColumn

FROM tableName;

SELECT *

FROM tableName;

Use of Distinct

SELECT DISTINCT columnName

FROM tableName;

columnName

A

B

C

D

columnName

A

A

B

B

C

D

Calculated fields

SELECT columnName/2

FROM tableName

price

5.00

3.00

6.00

price

10.00

6.00

12.00

Comparison Search Condition

= equals

< > is not equal to (ISO standard)

!= “ “ “ “ (allowed in some dialects)

< is less than

> is greater than

<= is less than or equal to

>= is greater than or equal to


Comparison Search Condition

An expression is evaluated left to right.

Subexpressions in brackets are evaluated first.

NOTs are evaluated before ANDs and ORs.

ANDs are evaluated before ORs.


Range Search ConditionSELECT columnNameFROM tableNameWHERE columnName BETWEEN 20

AND 30;

SELECT columnNameFROM tableNameWHERE columnName >= 20 AND columnName <= 30;

Set membership search condition

SELECT columnName

FROM tableName

WHERE columnName

IN (‘name1’, ‘name2’);

SELECT columnName

FROM tableName

WHERE columnName = ‘name1’

OR columnName = ‘name2’;

Pattern matching symbols

% represents any sequence of zero

or more characters (wildcard).

_ represents any single character


Pattern match search condition‘h%’ : begins with the character h .

‘h_ _ _’ : four character string beginning with the character h.

‘%e’ : any sequence of characters, of length at least 1, ending with the character e.

‘%CS157B%’ : any sequence of characters of any length containing CS157B


Pattern match search condition

LIKE ‘h%’

begins with the character h .

NOT LIKE ‘h%’

does not begin with the character h.


Pattern match search condition

To search a string that includes a

pattern-matching character

‘15%’

Use an escape character to represent

the pattern-matching character.

LIKE ‘15#%’ ESCAPE ‘#’


NULL search condition

DOES NOT WORK

comment = ‘ ’

comment != ‘ ’

DOES WORK

comment IS NULL

comment IS NOT NULL

Sorting

The ORDER BY clause consists of list of column

identifiers that the result is to be sorted on, separated by commas.

Allows the retrieved rows to be ordered by ascending (ASC) or descending (DESC) order


Sorting

Column identifier may be A column name A column number (deprecated)


Sorting

SELECT type, rent

FROM tableName

ORDER BY type, rent ASC;


type rent

Apt

Apt

Flat

Flat

450

500

600

650

type rent

Flat

Apt

Flat Apt

650 450

600 500

Aggregate Functions

COUNT returns the number … SUM returns the sum … AVG returns the average … MIN returns the smallest … MAX returns the largest …

value in a specified column.


Use of COUNT( * )

How many students in CS157B?

SELECT COUNT( * ) AS my count

FROM CS157B

my count

40

GROUP BY clause

When GROUP BY is used, each item in the SELECT list must be single-valued per group.

The SELECT clause may contain only Column names Aggregate functions Constants An expression involving combinations of the

above


GroupingSELECT dept, COUNT(staffNo) AS my count

SUM(salary)

FROM tableName

GROUP BY dept

ORDER BY dept

dept my count

Salary

A

B

C

2

2

1

300.00300.00 200.00

dept staffNo Salary

A

B

C

A

B

1

1

1

2

2

200.00

200.00

200.00

100.00

100.00

Restricting Grouping

HAVING clause is with the GROUP BY clause. filters groups into resulting table. includes at least one aggregate

function. WHERE clause

filters individual rows into resulting table.

Aggregate functions cannot be used.


SELECT dept, COUNT(staffNo) AS my count, SUM(salary) AS my sum

FROM StaffGROUP BY deptHAVING COUNT(staffNo) > 1ORDER BY dept;


dept my count

my sum

A

B

2

2

300.00 300.00

dept staffNo Salary

A

B

C

A

B

1

1

1

2

2

200.00

200.00

200.00

100.00

100.00

Subqueries

SELECT columnNameA

FROM tableName1

WHERE columnNameB = (SELECT columnNameB

FROM tableName2

WHERE condition);


result from inner SELECT applied as a condition for the outer SELECT

Subquery with Aggregate Function

SELECT fName, salary –

( SELECT AVG(salary)

FROM Staff ) AS salDiff

FROM Staff

WHERE salary > ( SELECT AVG(salary)

FROM Staff );


List all staff whose salary is greater than the average salary,show by how much their salary is greater than the average.

Nested Subqueries: Use of IN

SELECT propertyFROM PropertyForRentWHERE staff IN(

SELECT staffFROM StaffWHERE branch = (

SELECT branch FROM Branch WHERE street = ‘112 A St’));


Selects branch at 112 A St


SELECT property

FROM PropertyForRent

WHERE staff IN(

SELECT staff

FROM Staff

WHERE branch = ( branch ) );


Select staff members who works at branch.


SELECT property

FROM PropertyForRent

WHERE staff IN( staffs who works

at branch on ‘112 A St’);


Since there are more than one row selected, “=“ cannot be used.

Use of ANY/SOME

SELECT name, salary

FROM Staff

WHERE salary > SOME( SELECT salary

FROM Staff

WHERE branch = ‘A’ );


Result:{2000,3000,4000}

Result: {list of staff with salary greater than 2000.}

Use of ALL

SELECT name, salary

FROM Staff

WHERE salary > ALL( SELECT salary

FROM Staff

WHERE branch = ‘A’ );


Result:{2000,3000,4000}

Result: {list of staff with salary greater than 4000.}

Use of Any/Some and All

If the subquery is empty: ALL returns true ANY returns false

ISO standard allows SOME to be

used interchangeably with ANY.


04/19/23

Natural JoinNatural Join44A A Natural JoinNatural Join is a join operation that joins two is a join operation that joins two tables bytables by their common column. This their common column. This operation is similar to the setting relation of two operation is similar to the setting relation of two tables.tables.

SELECT a.comcol, a.SELECT a.comcol, a.col1col1, b., b.col2col2, , expr1expr1, , expr2expr2 ; ;

FROM FROM table1table1 a, a, table2table2 b ; b ;

WHERE a.WHERE a.comcolcomcol = b. = b.comcolcomcol

04/19/23

Natural JoinNatural Join44

MusicMusic

idid

98019801

typetype

StudentStudent

98019801

idid namename classclass

98019801

ProductProduct

idid namename classclass typetype

Same idSame id

JoinJoin

eg. 25eg. 25 Make a list of students and the instruments Make a list of students and the instruments they they learn. (Natural Join)learn. (Natural Join)

04/19/23

SELECT s.class, s.name, s.id, m.type ;

FROM student s, music m ;

WHERE s.id=m.id ORDER BY class, name


class name id type1A Aaron 9812 Piano1A Bobby 9811 Flute1A Gigi 9824 Recorder1A Jill 9820 Piano1A Johnny 9803 Violin1A Luke 9810 Piano1A Mary 9802 Flute: : : :

Result

eg. 25eg. 25 Make a list of students and the instruments they Make a list of students and the instruments they learn. (Natural Join)learn. (Natural Join)

04/19/23

eg. 26eg. 26 Find the number of students learning piano in Find the number of students learning piano in each class.each class.


Three Parts :Three Parts :

(1)(1) Natural Join.Natural Join.

(2)(2) Condition: Condition: m.type="Piano"m.type="Piano"

(3)(3) GROUP BY classGROUP BY class

04/19/23


MusicMusic

StudentStudent

ProductProduct

JoinJoin ConditionConditionm.type= "Piano"m.type= "Piano"

Group ByGroup By

classclass

eg. 26eg. 26

04/19/23

eg. 26eg. 26 Find the number of students learning piano in Find the number of students learning piano in each class.each class.

SELECT s.class, COUNT(*) ;


WHERE s.id=m.id AND m.type="Piano" ;

GROUP BY class ORDER BY class


class cnt1A 41B 21C 1

Result

04/19/23

An An Outer JoinOuter Join is a join operation that includes is a join operation that includes rows that have a match, plus rows that do not rows that have a match, plus rows that do not have a match in the other table.have a match in the other table.

Outer JoinOuter Join44

04/19/23

eg. 27eg. 27 List the students who have not yet chosen an List the students who have not yet chosen an instrument. (No match)instrument. (No match)


No matchNo match

MusicMusic

idid typetype

StudentStudent

98019801

idid namename classclass

04/19/23

eg. 27eg. 27 List the students who have not yet chosen an List the students who have not yet chosen an instrument. (No match)instrument. (No match)

SELECT class, name, id FROM student ;

WHERE id NOT IN ( SELECT id FROM music ) ;ORDER BY class, name


Resultclass name id1A Mandy 98211B Kenny 98141B Tobe 98051C Edmond 98181C George 9817: : :

04/19/23

eg. 28eg. 28 Make a checking list of students and the Make a checking list of students and the instruments they learn. The list should also instruments they learn. The list should also contain the students contain the students without an instrument.without an instrument.

(Outer Join)(Outer Join)


04/19/23

Outer JoinOuter Join44Natural JoinNatural Join

No MatchNo Match

Outer JoinOuter Join

eg. 28eg. 28

04/19/23

SELECT s.class, s.name, s.id, m.type ;


WHERE s.id=m.id ;

Outer JoinOuter Join44UNION ;

SELECT class, name, id, "" ;

FROM student ;

WHERE id NOT IN ( SELECT id FROM music ) ;

ORDER BY 1, 2

eg. 28eg. 28

04/19/23


emptyclass name id1A Mandy 98211B Kenny 98141B Tobe 98051C Edmond 98181C George 9817: : :

No Match

class name id type1A Aaron 9812 Piano1A Bobby 9811 Flute1A Gigi 9824 Recorder1A Jill 9820 Piano1A Johnny 9803 Violin1A Luke 9810 Piano1A Mary 9802 Flute: : : :

Natural Join

class name id type

1A Aaron 9812 Piano

1A Bobby 9811 Flute

1A Gigi 9824 Recorder

1A Jill 9820 Piano

1A Johnny 9803 Violin

1A Luke 9810 Piano

1A Mandy 9821

1A Mary 9802 Flute

1A Peter 9801 Piano

1A Ron 9813 Guitar

1B Eddy 9815 Piano

1B Janet 9822 Guitar

1B Kenny 9814

1B Kitty 9806 Recorder

: : : :

Outer Join

Multi-Table Queries

Join Inner Join Left Outer Join Right Outer Join Full Outer Join


JoinSELECT client

FROM Client c, View v

WHERE c.client = v.client;


FROM Client c JOIN View v ON c.client = v.client(creates two identical client columns)

FROM Client JOIN View USING clientFROM Client NATURAL JOIN View

ISO standard Alternatives

Join

The join operation combines data from two tables by forming pairs of related rows where the matching columns in each table have the same value.

If one row of a table is unmatched, the row is omitted from the resulting table.


my sql: data manipulation

Documents