positive flow

Proprietary and Confidential April 12, 2023 - 1 -

SQL

Name of the Presenter: Devi B Date: 17-06-2011


Introduction to SQL

• What is SQL?– SQL (Structured Query Language) is used to

modify and access data or information from a storage area called database.

– When a user wants to get some information from a database file, he can issue a query

– A query is a user–request to retrieve data or information with a certain condition.

– SQL is a query language that allows user to specify the conditions.

1


Introduction to SQL

Concept of SQL

– The user specifies a certain condition

– The program will go through all the records in the database file and select those records that satisfy the condition.(searching).

– The result of the query will then be stored in form of a table.

1


Basic structure of an SQL query2

General Structure

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

Logical Operators

AND, OR, NOT

Output INTO TABLE , TO FILE , TO SCREEN

Union UNION


The Situation: Student Particulars2

field type widthcontentsid numeric 4 student id numbername character 30 namedob date 8 date of birthsex enum 1 sex: M / Fclass character 2 class


General StructureI

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

SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;FROM tablename WHERE condition


General StructureISELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;FROM table name WHERE condition

– The query will select rows from the source table name 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.


General StructureI

– 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.

SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;FROM table name WHERE condition


General Structure

Before using SQL, open the student file: USE student

Eg. 1 List all the student records.

id name dob sex class9801 Peter 06/04/86 M 1A9802 Mary 01/10/86 F 1A9803 Johnny 03/16/86 M 1A9804 Wendy 07/09/86 F 1B9805 Tobe 10/17/86 M 1B

: : : : :

I

SELECT * FROM student


General StructureIeg. 2 List the names of 1A students.

SELECT name, class FROM student ;

WHERE class="1A"

Class

1A

1A

1A

1B

1B

:

Class

1A

1A

1A

1B

1B

:

class="1A"


General StructureIeg. 2 List the names of 1A students.

Result name class

Peter 1A

Mary 1A

Johnny 1A

Luke 1A

Bobby 1A

Aaron 1A

: :


General StructureI

eg. 3 List the names and ages of 1B girls.

1B Girls ?


General StructureI

Condition for "1B Girls":

1) class = "1B"

2) sex = "F"

3) Both ( AND operator)



General StructureI


What is "age"?


General StructureI

Functions:

# Diff b/w two dates in days :DATEDIFF(NOW() – dob)

# Returns Years , Month , Days : FROM_DAYS(DATEDIFF(NOW() ) – dob)



General StructureIeg. 3 List the names and ages of 1B girls.

SELECT DATE_FORMAT (FROM_DAYS(DATEDIFF(NOW(),dob)), '%Y') AS age FROM student WHERE class='1B' AND sex='F'

Resultname ageWendy 12.0Kitty 11.0Janet 12.0Sandy 12.0Mimi 12.0


ComparisonIIexpr IN ( value1, value2, value3)

expr BETWEEN value1 AND value2

expr LIKE "%_"


ComparisonIIeg. 4 List the students who were born on

Wednesday or Saturday.

SELECT name, class, DAYNAME(dob) AS bdate FROM student ;

WHERE weekday(dob) IN (2,5)

Result name class bdatePeter 1A WednesdayWendy 1B WednesdayKevin 1C SaturdayLuke 1A WednesdayAaron 1A Saturday: : :


ComparisonIIeg. 5 List the students who were not born in

January, March, June, September.

SELECT name, class, dob FROM student ;

WHERE MONTH(dob) NOT IN (1,3,6,9)Result name class dob

Wendy 1B 07/09/86Tobe 1B 10/17/86Eric 1C 05/05/87Patty 1C 08/13/87Kevin 1C 11/21/87Bobby 1A 02/16/86Aaron 1A 08/02/86: : :


ComparisonIIeg. 6 List the id students whose total score is

between 200 and 300 (incl.)

SELECT student_id, total_marks FROM student ;

WHERE total_marks BETWEEN 80 AND 90

ResultStudent_id total_marks9000 2109810 2509201 260


ComparisonII

eg. 7 List the students whose names start with "T".

SELECT name, class FROM student ;

WHERE name LIKE "T%"

Result name classTobe 1BTeddy 1BTim 2A


GroupingIIISELECT ...... FROM ...... WHERE condition ;GROUP BY groupexpr [HAVING requirement]

Group functions:

COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )

– groupexpr specifies the related rows to be grouped as one entry. Usually it is a column.

– WHERE condition specifies the condition of individual rows before the rows are group. HAVING requirement specifies the condition involving the whole group.


GroupingIII eg. 8 List the number of students of each class.


Student

class1A1A1A1B

1B1B1B1B1B1C1C1C

1A

1B

1C

COUNT( )

COUNT( )1B

COUNT( )1C


GroupingIII

SELECT class, COUNT(*) FROM student ;

GROUP BY class

eg. 8 List the number of students of each class.

Result class cnt1A 101B 91C 92A 82B 82C 6


GroupingIII eg. 9 List the average score of each class.


Student

class1A1A1A1B

1B1B1B1B1B1C1C1C

1A

1B

1C

AVG( )

AVG( )

AVG( )


GroupingIII eg. 9 List the average total score of each

class.SELECT class, AVG(total_marks) as Average FROM student GROUP BY class

Result class Average

1A 85.90

1B 70.33

1C 37.89

2A 89.38

2B 53.13

2C 32.67


GroupingIII eg. 10 List the max. and min. total of

students in each class.

SELECT MAX(total_marks) as max_total, MIN(total_marks) as min_total,class

FROM student WHERE GROUP BY class

Result max_total min_total Class

300 200 IA

289 195 IB

250 150 IC

225 125 2A

300 207 2B

289 100 2C


GroupingIII eg. 11 List the average score of the boys in each

class. The list should not contain class with less than 3 boys.

SELECT AVG(total_marks), class FROM student WHERE sex="M" GROUP BY class

HAVING COUNT(*) >= 3

Result avg_marks class

86.00 1A

77.75 1B

35.60 1C

86.50 2A

56.50 2B


Display OrderIVSELECT ...... FROM ...... WHERE ......

GROUP BY ..... ;

ORDER BY colname ASC / DESC


Display OrderIV

SELECT name, id FROM student ;

WHERE sex="M" AND class="1A" ORDER BY name

eg. 12 List the boys of class 1A, order by their names.

name idPeter 9801Johnny 9803Luke 9810Bobby 9811Aaron 9812Ron 9813

ORDER BY

name idAaron 9812Bobby 9811Johnny 9803Luke 9810Peter 9801Ron 9813

Result


OutputV

INTO TABLE tablename

the output table is saved as a database file in the disk.

TO FILE filename [ADDITIVE]

output to a text file. (additive = append)

TO SCREEN

display on screen.


Union, Intersection and Difference of Tables3

The union of A and B (AB)

A B

A table containing all the rows from A and B.



The intersection of A and B (AB)

A B

A table containing only rows tht appear in both A and B.



The difference of A and B (A–B)

A B

A table containing rows that appear in A but not in B.


The Situation:Bridge Club & Chess Club3Consider the members of the Bridge Club and the Chess Club. The two database files have the same structure:

field type width contentsid numeric 4 student id numbername character 10 namesex character 1 sex: M / Fclass character 2 class



Bridge [A] Chess [B]

id name sex class id name sex class

1 9812 Aaron M 1A 1 9802 Mary F 1A

2 9801 Peter M 1A 2 9801 Peter M 1A

3 9814 Kenny M 1B 3 9815 Eddy M 1B

4 9806 Kitty F 1B 4 9814 Kenny M 1B

5 9818 Edmond M 1C 5 9817 George M 1C: : : : : : : :

Before using SQL, open the two tables:

SELECT AUSE bridgeSELECT BUSE chess



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

UNION ;

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

eg. 13 The two clubs want to hold a joint party.Make a list of all students. (Union)

SELECT * FROM student ;

UNION ;

SELECT * FROM student_temp ;



SELECT ...... FROM table1 ;

WHERE col IN ( SELECT col FROM table2 )

eg. 14 List of students who are members of both clubs. (Intersection)

ResultSELECT * FROM bridge ;

WHERE id IN ( SELECT id FROM chess ) ;

TO PRINTER



SELECT ...... FROM table1 ;

WHERE col NOT IN ( SELECT col FROM table2 )

eg. 14 Make a list of students who are members of the Bridge Club but not Chess Club.

(Difference)

ResultSELECT * FROM bridge ;

WHERE id NOT IN ( SELECT id FROM chess ) ;


MultipleTables4• SQL provides a convenient operation to

retrieve information from multiple tables.

• This operation is called join.

• The join operation will combine the tables into one large table with all possible combinations and then it will filter the rows of this combined table to yield useful information.


MultipleTables4

field1

A

B

field21

2

3

field1 field2A

A

A

1

2

3B

B

B

1

2

3


The Situation Music Lesson4

Each student should learn a musical instrument.Two database files: student.dbf & music.dbfThe common field: student id

field type width contents id numeric 4 student id number type character 10 type of the music instrument

SELECT AUSE studentSELECT BUSE music


Natural Join4

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

SELECT a.comcol, a.col1, b.col2, expr1, expr2 ;

FROM table1 a, table2 b ;

WHERE a.comcol = b.comcol


Natural Join4 eg. 15 Make a list of students and the instruments

they learn. (Natural Join)

Student

9801

id name class

Same id

JoinMusic

id

9801

type

9801

Product

id name class type


Natural Join4 eg. 15 Make a list of students and the instruments

they learn. (Natural Join)

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


Natural Join4

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

Three Parts :(1) Natural Join.

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

(3) GROUP BY class


Natural Join4 eg. 16

Music

Student Join

Product

Conditionm.type= "Piano"

Group By class


Natural Join4 eg. 16 Find the number of students learning piano

in each class.SELECT s.class, COUNT(*) ;


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

GROUP BY class ORDER BY class

Result class cnt1A 41B 21C 1


Outer Join4

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


Outer Join4

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

Student

9801

id name class

No match

Music

id type


Outer Join4 eg. 17 List the students who have not yet chosen

an instrument. (No match)

SELECT class, name, id FROM student ;

WHERE id NOT IN ( SELECT id FROM music ) ;

ORDER BY class, name

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

Result


Outer Join4

eg. 18 Make a checking list of students and the instruments they learn. The list should

also contain the students without an instrument.

(Outer Join)


Outer Join4

eg. 18

Natural Join

No Match

Outer Join


Outer Join4

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


WHERE s.id=m.id ;

eg. 18

UNION ;

SELECT class, name, id, "" ;

FROM student ;

WHERE id NOT IN ( SELECT id FROM music ) ;

ORDER BY 1, 2


Outer Join4class 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 id1A Mandy 98211B Kenny 98141B Tobe 98051C Edmond 98181C George 9817: : :

No Match

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

empty


SQL CREATE TABLE Statement4

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like - Primary key - Unique key - Foreign key can be defined for the columns while creating the table.


Syntax for the CREATE TABLE Statement4

CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, ... column_nameN datatype ); table_name - is the name of the table. column_name1, column_name2.... - is the name of the columns datatype - is the datatype for the column like char, date, number etc.


SQL ALTER TABLE Statement4The SQL ALTER TABLE command is used to modify

the definition (structure) of a table by modifying

the definition of its columns.


Database Testing5

SQL – Part of DB testing


Queries

positive flow

Documents

b girls

general structureeg

student file

student records

condition distinct

use student

general structurebefore

certain condition