this is a SQL ppt you can find the basic sql queries
deepak266689
58 views
68 slides
Oct 06, 2024
Slide 1 of 68
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
About This Presentation
this is a SQL ppt you can find the basic sql queries
Slide Content
Structured Query Language
Introduction to SQLIntroduction to SQL
What is SQL?
–When a user wants to get some information
from a database file, he can issue a query.
11
– 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. (instead of algorithms)
What is SQL?
–When a user wants to get some information
from a database file, he can issue a query.
11
– 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. (instead of algorithms)
Introduction to SQLIntroduction to SQL
Concept of SQL
–The user specifies a certain condition.
11
–The result of the query will then be stored in
form of a table.
–Statistical information of the data.
–The program will go through all the records
in the database file and select those records
that satisfy the condition.(searching).
Concept of SQL
–The user specifies a certain condition.
11
–The result of the query will then be stored in
form of a table.
–Statistical information of the data.
–The program will go through all the records
in the database file and select those records
that satisfy the condition.(searching).
Introduction to SQLIntroduction to SQL
How to involve SQL in FoxPro
–Before using SQL, the tables should be
opened.
11
–The SQL command can be entered directly
in the Command Window
–To perform exact matching, we should
SET ANSI ON
How to involve SQL in FoxPro
–Before using SQL, the tables should be
opened.
11
–The SQL command can be entered directly
in the Command Window
–To perform exact matching, we should
SET ANSI ON
Basic structure of an SQL Basic structure of an SQL
queryquery22
General
Structure
SELECT, ALL / DISTINCT, *,
AS, FROM, WHERE
Comparison IN, BETWEEN, LIKE "% _"
Grouping GROUP BY, HAVING,
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
Display OrderORDER BY, ASC / DESC
Logical
Operators
AND, OR, NOT
Output INTO TABLE / CURSOR
TO FILE [ADDITIVE], TO PRINTER, TO SCREEN
Union UNION
queryquery22
General
Structure
SELECT, ALL / DISTINCT, *,
AS, FROM, WHERE
Comparison IN, BETWEEN, LIKE "% _"
Grouping GROUP BY, HAVING,
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
Display OrderORDER BY, ASC / DESC
Logical
Operators
AND, OR, NOT
Output INTO TABLE / CURSOR
TO FILE [ADDITIVE], TO PRINTER, TO SCREEN
Union UNION
fieldfieldtypetype widthwidthcontentscontents
id numeric 4student id number
name character 10name
dob date 8date of birth
sex character 1sex: M / F
classcharacter 2class
hcode character 1house code: R, Y, B,
G
dcode character 3district code
remissionlogical 1fee remission
mtestnumeric 2Math test score
22
The Situation:
Student Particulars
id numeric 4student id number
name character 10name
dob date 8date of birth
sex character 1sex: M / F
classcharacter 2class
hcode character 1house code: R, Y, B,
G
dcode character 3district code
remissionlogical 1fee remission
mtestnumeric 2Math test score
22
The Situation:
Student Particulars
General StructureGeneral Structure
II
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......
II
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......
General StructureGeneral Structure
II
–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.
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
–And col1, col2 are their corresponding column names in the
output table.
II
–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.
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
–And col1, col2 are their corresponding column names in the
output table.
General StructureGeneral Structure
II
–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.
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
II
–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.
SELECTSELECT [[ALL / DISTINCTALL / DISTINCT] ] expr1expr1 [ [ASAS col1col1], ], expr2expr2 [ [ASAS col2col2] ] ;;
FROMFROM tablenametablename WHEREWHERE conditioncondition
General StructureGeneral Structure
II
Before 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
idnamedob sexclassmtesthcodedcoderemission
9801Peter06/04/86M 1A 70 R SSP.F.
9802Mary01/10/86F 1A 92 Y HHM.F.
9803Johnny03/16/86M 1A 91 G SSP.T.
9804Wendy07/09/86F 1B 84 B YMT.F.
9805Tobe10/17/86M 1B 88 R YMT.F.
:: : : : : : : :
Result
II
Before 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
idnamedob sexclassmtesthcodedcoderemission
9801Peter06/04/86M 1A 70 R SSP.F.
9802Mary01/10/86F 1A 92 Y HHM.F.
9803Johnny03/16/86M 1A 91 G SSP.T.
9804Wendy07/09/86F 1B 84 B YMT.F.
9805Tobe10/17/86M 1B 88 R YMT.F.
:: : : : : : : :
Result
General StructureGeneral Structure
II
eg. 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"
II
eg. 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"
General StructureGeneral Structure
II
namehcodeclass
PeterR 1A
MaryY 1A
JohnnyG 1A
LukeG 1A
BobbyB 1A
AaronR 1A
: : :
Result
eg. 2eg. 2 List the names and house code of 1A students.List the names and house code of 1A students.
II
namehcodeclass
PeterR 1A
MaryY 1A
JohnnyG 1A
LukeG 1A
BobbyB 1A
AaronR 1A
: : :
Result
eg. 2eg. 2 List the names and house code of 1A students.List the names and house code of 1A students.
General StructureGeneral Structure
II
eg. 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"
dcode
HHM
KWC
MKK
SSP
TST
YMT
Result
II
eg. 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"
dcode
HHM
KWC
MKK
SSP
TST
YMT
Result
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
11B Girls ?B Girls ?
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
11B Girls ?B Girls ?
Condition for "1B Girls":Condition for "1B Girls":
1)1)class = class = "1B""1B"
2)2)sex = sex = "F""F"
3)3)Both ( AND operator)Both ( AND operator)
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
1)1)class = class = "1B""1B"
2)2)sex = sex = "F""F"
3)3)Both ( AND operator)Both ( AND operator)
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
What is "age"?What is "age"?
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
What is "age"?What is "age"?
Functions:Functions:
# days :# days :DATE( ) – dobDATE( ) – dob
# years :(DATE( ) – dob) / 365# years :(DATE( ) – dob) / 365
1 d.p.:1 d.p.:ROUND(__ , 1)ROUND(__ , 1)
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
# days :# days :DATE( ) – dobDATE( ) – dob
# years :(DATE( ) – dob) / 365# years :(DATE( ) – dob) / 365
1 d.p.:1 d.p.:ROUND(__ , 1)ROUND(__ , 1)
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
General StructureGeneral Structure
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
SELECT name, ROUND((DATE( )-dob)/365,1) AS age ;
FROM student WHERE class="1B" AND sex="F"
name age
Wendy 12.1
Kitty 11.5
Janet 12.4
Sandy 12.3
Mimi 12.2
Result
II
eg. 4eg. 4 List the names and ages (1 d.p.) of 1B girls.List the names and ages (1 d.p.) of 1B girls.
SELECT name, ROUND((DATE( )-dob)/365,1) AS age ;
FROM student WHERE class="1B" AND sex="F"
name age
Wendy 12.1
Kitty 11.5
Janet 12.4
Sandy 12.3
Mimi 12.2
Result
General StructureGeneral Structure
II
eg. 5eg. 5 List the names, id of 1A students with no fee List the names, id of 1A students with no fee
remission.remission.
SELECT name, id, class FROM student ;
WHERE class="1A" AND NOT remission
name id class
Peter 98011A
Mary 98021A
Luke 98101A
Bobby 98111A
Aaron 98121A
Ron 98131A
Gigi 98241A
: : :
Result
II
eg. 5eg. 5 List the names, id of 1A students with no fee List the names, id of 1A students with no fee
remission.remission.
SELECT name, id, class FROM student ;
WHERE class="1A" AND NOT remission
name id class
Peter 98011A
Mary 98021A
Luke 98101A
Bobby 98111A
Aaron 98121A
Ron 98131A
Gigi 98241A
: : :
Result
ComparisonComparison
IIII
exprexpr IN ( IN ( value1value1, , value2value2, , value3value3))
exprexpr BETWEEN BETWEEN value1value1 AND AND value2value2
exprexpr LIKE "%_" LIKE "%_"
IIII
exprexpr IN ( IN ( value1value1, , value2value2, , value3value3))
exprexpr BETWEEN BETWEEN value1value1 AND AND value2value2
exprexpr LIKE "%_" LIKE "%_"
ComparisonComparison
IIII
eg. 6eg. 6 List the students who were born on Wednesday List the students who were born on Wednesday or or
Saturdays.Saturdays.
SELECT name, class, CDOW(dob) AS
bdate ;FROM student ;
WHERE DOW(dob) IN (4,7)
name classbdate
Peter 1A Wednesday
Wendy 1B Wednesday
Kevin 1C Saturday
Luke 1A Wednesday
Aaron 1A Saturday
: : :
Result
IIII
eg. 6eg. 6 List the students who were born on Wednesday List the students who were born on Wednesday or or
Saturdays.Saturdays.
SELECT name, class, CDOW(dob) AS
bdate ;FROM student ;
WHERE DOW(dob) IN (4,7)
name classbdate
Peter 1A Wednesday
Wendy 1B Wednesday
Kevin 1C Saturday
Luke 1A Wednesday
Aaron 1A Saturday
: : :
Result
ComparisonComparison
IIII
eg. 7eg. 7 List the students who were not born in January, List the students who were not born in January, March, March,
June, September.June, September.
SELECT name, class, dob FROM student ;
WHERE MONTH(dob) NOT IN (1,3,6,9)
name classdob
Wendy 1B 07/09/86
Tobe 1B 10/17/86
Eric 1C 05/05/87
Patty 1C 08/13/87
Kevin 1C 11/21/87
Bobby 1A 02/16/86
Aaron 1A 08/02/86
: : :
Result
IIII
eg. 7eg. 7 List the students who were not born in January, List the students who were not born in January, March, March,
June, September.June, September.
SELECT name, class, dob FROM student ;
WHERE MONTH(dob) NOT IN (1,3,6,9)
name classdob
Wendy 1B 07/09/86
Tobe 1B 10/17/86
Eric 1C 05/05/87
Patty 1C 08/13/87
Kevin 1C 11/21/87
Bobby 1A 02/16/86
Aaron 1A 08/02/86
: : :
Result
ComparisonComparison
IIII
eg. 8eg. 8 List the 1A students whose Math test score is List the 1A students whose Math test score is between between
80 and 90 (incl.)80 and 90 (incl.)
SELECT name, mtest FROM student ;
WHERE class="1A" AND ;
mtest BETWEEN 80 AND 90
name mtest
Luke 86
Aaron 83
Gigi 84
Result
IIII
eg. 8eg. 8 List the 1A students whose Math test score is List the 1A students whose Math test score is between between
80 and 90 (incl.)80 and 90 (incl.)
SELECT name, mtest FROM student ;
WHERE class="1A" AND ;
mtest BETWEEN 80 AND 90
name mtest
Luke 86
Aaron 83
Gigi 84
Result
ComparisonComparison
IIII
eg. 9eg. 9 List the students whose names start with "T".List the students whose names start with "T".
SELECT name, class FROM student ;
WHERE name LIKE "T%"
name class
Tobe 1B
Teddy 1B
Tim 2A
Result
IIII
eg. 9eg. 9 List the students whose names start with "T".List the students whose names start with "T".
SELECT name, class FROM student ;
WHERE name LIKE "T%"
name class
Tobe 1B
Teddy 1B
Tim 2A
Result
ComparisonComparison
IIII
eg. 10eg. 10 List the Red house members whose names contain List the Red house members whose names contain "a" as "a" as
the 2nd letter.the 2nd letter.
SELECT name, class, hcode FROM student ;
WHERE name LIKE "_a%" AND hcode="R"
name classhcode
Aaron 1A R
Janet 1B R
Paula 2A R
Result
IIII
eg. 10eg. 10 List the Red house members whose names contain List the Red house members whose names contain "a" as "a" as
the 2nd letter.the 2nd letter.
SELECT name, class, hcode FROM student ;
WHERE name LIKE "_a%" AND hcode="R"
name classhcode
Aaron 1A R
Janet 1B R
Paula 2A R
Result
GroupingGroupingIIIIII
SELECT ...... FROM ...... WHERE SELECT ...... FROM ...... WHERE conditioncondition ; ;
GROUP BY GROUP BY groupexprgroupexpr [HAVING [HAVING requirementrequirement]]
Group functions:Group functions:
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )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.
SELECT ...... FROM ...... WHERE SELECT ...... FROM ...... WHERE conditioncondition ; ;
GROUP BY GROUP BY groupexprgroupexpr [HAVING [HAVING requirementrequirement]]
Group functions:Group functions:
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )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.
GroupingGroupingIIIIII
eg. 11eg. 11 List the number of students of each class.List the number of students of each class.
eg. 11eg. 11 List the number of students of each class.List the number of students of each class.
COUNT( )COUNT( )
Group By ClassGroup By Class
11AA
COUNT( )COUNT( )
11BB
COUNT( )COUNT( )
11CC
11AA
11BB
11CC
StudentStudent
class
11AA
11AA
11AA
11BB
11BB
11BB
11BB
11BB
11BB
11CC
11CC
11CC
Group By ClassGroup By Class
11AA
COUNT( )COUNT( )
11BB
COUNT( )COUNT( )
11CC
11AA
11BB
11CC
StudentStudent
class
11AA
11AA
11AA
11BB
11BB
11BB
11BB
11BB
11BB
11CC
11CC
11CC
GroupingGroupingIIIIII
SELECT class, COUNT(*) FROM student ;
GROUP BY class
classcnt
1A 10
1B 9
1C 9
2A 8
2B 8
2C 6
eg. 11eg. 11 List the number of students of each class.List the number of students of each class.
Result
SELECT class, COUNT(*) FROM student ;
GROUP BY class
classcnt
1A 10
1B 9
1C 9
2A 8
2B 8
2C 6
eg. 11eg. 11 List the number of students of each class.List the number of students of each class.
Result
GroupingGroupingIIIIII
eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.
eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.
Group By ClassGroup By Class
AVG( )AVG( )
AVG( )AVG( )
AVG( )AVG( )
11AA
11BB
11CC
StudentStudent
class
11AA
11AA
11AA
11BB
11BB
11BB
11BB
11BB
11BB
11CC
11CC
11CC
AVG( )AVG( )
AVG( )AVG( )
AVG( )AVG( )
11AA
11BB
11CC
StudentStudent
class
11AA
11AA
11AA
11BB
11BB
11BB
11BB
11BB
11BB
11CC
11CC
11CC
GroupingGroupingIIIIII
eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.
SELECT class, AVG(mtest) FROM student ;
GROUP BY class
classavg_mtest
1A 85.90
1B 70.33
1C 37.89
2A 89.38
2B 53.13
2C 32.67
Result
eg. 12eg. 12 List the average Math test score of each class.List the average Math test score of each class.
SELECT class, AVG(mtest) FROM student ;
GROUP BY class
classavg_mtest
1A 85.90
1B 70.33
1C 37.89
2A 89.38
2B 53.13
2C 32.67
Result
GroupingGroupingIIIIII
eg. 13eg. 13 List the number of girls of each district.List the number of girls of each district.
SELECT dcode, COUNT(*) FROM student ;
WHERE sex="F" GROUP BY dcode
dcode cnt
HHM 6
KWC 1
MKK 1
SSP 5
TST 4
YMT 8
Result
eg. 13eg. 13 List the number of girls of each district.List the number of girls of each district.
SELECT dcode, COUNT(*) FROM student ;
WHERE sex="F" GROUP BY dcode
dcode cnt
HHM 6
KWC 1
MKK 1
SSP 5
TST 4
YMT 8
Result
GroupingGroupingIIIIII
eg. 14eg. 14 List the max. and min. test score of Form 1 List the max. and min. test score of Form 1 students students
of each district.of each district.
SELECT MAX(mtest), MIN(mtest), dcode ;
FROM student ;
WHERE class LIKE "1_" GROUP BY dcode
max_mtestmin_mtestdcode
92 36 HHM
91 19 MKK
91 31 SSP
92 36 TST
75 75 TSW
88 38 YMT
Result
eg. 14eg. 14 List the max. and min. test score of Form 1 List the max. and min. test score of Form 1 students students
of each district.of each district.
SELECT MAX(mtest), MIN(mtest), dcode ;
FROM student ;
WHERE class LIKE "1_" GROUP BY dcode
max_mtestmin_mtestdcode
92 36 HHM
91 19 MKK
91 31 SSP
92 36 TST
75 75 TSW
88 38 YMT
Result
GroupingGroupingIIIIII
eg. 15eg. 15 List the average Math test score of the boys in List the average Math test score of the boys in each each
class. The list should not contain class with class. The list should not contain class with less than 3 boys.less than 3 boys.
SELECT AVG(mtest), class FROM student ;
WHERE sex="M" GROUP BY class ;
HAVING COUNT(*) >= 3
avg_mtestclass
86.001A
77.751B
35.601C
86.502A
56.502B
Result
eg. 15eg. 15 List the average Math test score of the boys in List the average Math test score of the boys in each each
class. The list should not contain class with class. The list should not contain class with less than 3 boys.less than 3 boys.
SELECT AVG(mtest), class FROM student ;
WHERE sex="M" GROUP BY class ;
HAVING COUNT(*) >= 3
avg_mtestclass
86.001A
77.751B
35.601C
86.502A
56.502B
Result
Display OrderDisplay OrderIVIV
SELECT ...... FROM ...... WHERE ...... SELECT ...... FROM ...... WHERE ......
GROUP BY ..... ;GROUP BY ..... ;
ORDER BY ORDER BY colnamecolname ASC / DESC ASC / DESC
SELECT ...... FROM ...... WHERE ...... SELECT ...... FROM ...... WHERE ......
GROUP BY ..... ;GROUP BY ..... ;
ORDER BY ORDER BY colnamecolname ASC / DESC ASC / DESC
Display OrderDisplay OrderIVIV
SELECT name, id FROM student ;
WHERE sex="M" AND class="1A" ORDER BY name
eg. 16eg. 16 List the boys of class 1A, order by their names.List the boys of class 1A, order by their names.
name id
Peter 9801
Johnny 9803
Luke 9810
Bobby 9811
Aaron 9812
Ron 9813
ORDER BY
dcode
name id
Aaron 9812
Bobby 9811
Johnny 9803
Luke 9810
Peter 9801
Ron 9813
Result
SELECT name, id FROM student ;
WHERE sex="M" AND class="1A" ORDER BY name
eg. 16eg. 16 List the boys of class 1A, order by their names.List the boys of class 1A, order by their names.
name id
Peter 9801
Johnny 9803
Luke 9810
Bobby 9811
Aaron 9812
Ron 9813
ORDER BY
dcode
name id
Aaron 9812
Bobby 9811
Johnny 9803
Luke 9810
Peter 9801
Ron 9813
Result
Display OrderDisplay OrderIVIV
SELECT name, id, class, dcode FROM
student ;
WHERE class="2A" ORDER BY dcode
eg. 17eg. 17 List the 2A students by their residential district.List the 2A students by their residential district.
name idclassdcode
Jimmy 97122A HHM
Tim 97132A HHM
Samual 97142A SHT
Rosa 97032A SSP
Helen 97022A TST
Joseph 97152A TSW
Paula 97012A YMT
Susan 97042A YMT
Result
SELECT name, id, class, dcode FROM
student ;
WHERE class="2A" ORDER BY dcode
eg. 17eg. 17 List the 2A students by their residential district.List the 2A students by their residential district.
name idclassdcode
Jimmy 97122A HHM
Tim 97132A HHM
Samual 97142A SHT
Rosa 97032A SSP
Helen 97022A TST
Joseph 97152A TSW
Paula 97012A YMT
Susan 97042A YMT
Result
Display OrderDisplay OrderIVIV
SELECT COUNT(*) AS cnt, dcode FROM
student ;
GROUP BY dcode ORDER BY cnt DESC
eg. 18eg. 18 List the number of students of each districtList the number of students of each district
(in desc. order).(in desc. order).
cnt docode
11YMT
10HHM
10SSP
9MKK
5TST
2TSW
1KWC
1MMK
1SHT
Result
SELECT COUNT(*) AS cnt, dcode FROM
student ;
GROUP BY dcode ORDER BY cnt DESC
eg. 18eg. 18 List the number of students of each districtList the number of students of each district
(in desc. order).(in desc. order).
cnt docode
11YMT
10HHM
10SSP
9MKK
5TST
2TSW
1KWC
1MMK
1SHT
Result
Display OrderDisplay OrderIVIV
SELECT name, class, hcode FROM student ;
WHERE sex="M" ORDER BY hcode, class
eg. 19eg. 19 List the boys of each house order by theList the boys of each house order by the classes. classes.
(2-level ordering)(2-level ordering)
SELECT name, class, hcode FROM student ;
WHERE sex="M" ORDER BY hcode, class
eg. 19eg. 19 List the boys of each house order by theList the boys of each house order by the classes. classes.
(2-level ordering)(2-level ordering)
Display OrderDisplay OrderIVIV
name hcodeclass
Bobby B 1A
Teddy B 1B
JosephB 2A
Zion B 2B
LeslieB 2C
JohnnyG 1A
Luke G 1A
Kevin G 1C
GeorgeG 1C
: : :
Result
Order
by
class
Blue
House
Green
House
:
:
Order
by
hcode
name hcodeclass
Bobby B 1A
Teddy B 1B
JosephB 2A
Zion B 2B
LeslieB 2C
JohnnyG 1A
Luke G 1A
Kevin G 1C
GeorgeG 1C
: : :
Result
Order
by
class
Blue
House
Green
House
:
:
Order
by
hcode
OutputOutput
VV
INTO TABLE tablename the output table is saved as a
database file in the disk.
INTO CURSOR temp the output is stored in the
working memory temporarily.
TO FILE filename [ADDITIVE] output to a text file.
(additive = append)
TO PRINTER send to printer.
TO SCREEN display on screen.
VV
INTO TABLE tablename the output table is saved as a
database file in the disk.
INTO CURSOR temp the output is stored in the
working memory temporarily.
TO FILE filename [ADDITIVE] output to a text file.
(additive = append)
TO PRINTER send to printer.
TO SCREEN display on screen.
OutputOutput
VV
eg. 20eg. 20 List the students in desc. order of their names and List the students in desc. order of their names and save the save the
result as a database file name.dbf.result as a database file name.dbf.
SELECT * FROM student ;
ORDER BY name DESC INTO TABLE name.dbf
idname dob sexclassmtesthcodedcoderemission
9707Zion 07/29/85M2B51B MKK.F.
9709Yvonne08/24/85F2C10R TST.F.
9804Wendy07/09/86F1B84B YMT.F.
9819Vincent03/15/85M1C29Y MKK.F.
9805Tobe 10/17/86M1B88R YMT.F.
9713Tim 06/19/85M2A91R HHM.T.
9816Teddy01/30/86M1B64B SSP.F.
: : : :: : : : :
Result
VV
eg. 20eg. 20 List the students in desc. order of their names and List the students in desc. order of their names and save the save the
result as a database file name.dbf.result as a database file name.dbf.
SELECT * FROM student ;
ORDER BY name DESC INTO TABLE name.dbf
idname dob sexclassmtesthcodedcoderemission
9707Zion 07/29/85M2B51B MKK.F.
9709Yvonne08/24/85F2C10R TST.F.
9804Wendy07/09/86F1B84B YMT.F.
9819Vincent03/15/85M1C29Y MKK.F.
9805Tobe 10/17/86M1B88R YMT.F.
9713Tim 06/19/85M2A91R HHM.T.
9816Teddy01/30/86M1B64B SSP.F.
: : : :: : : : :
Result
OutputOutput
VV
eg. 21eg. 21 Print the Red House members by their classes, sex Print the Red House members by their classes, sex and and
name.name.
SELECT class, name, sex FROM student ;
WHERE hcode="R" ;
ORDER BY class, sex DESC, name TO PRINTER
classname sex
1AAaron M
1APeter M
1ARon M
1BTobe M
1BJanet F
1BKitty F
1BMimi F
: : :
Result
VV
eg. 21eg. 21 Print the Red House members by their classes, sex Print the Red House members by their classes, sex and and
name.name.
SELECT class, name, sex FROM student ;
WHERE hcode="R" ;
ORDER BY class, sex DESC, name TO PRINTER
classname sex
1AAaron M
1APeter M
1ARon M
1BTobe M
1BJanet F
1BKitty F
1BMimi F
: : :
Result
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
A B
The union of A and B (AB)
A table containing all the rows from A and B.
Difference of TablesDifference of Tables33
A B
The union of A and B (AB)
A table containing all the rows from A and B.
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
The intersection of A and B (AB)
A table containing only rows that appear in both A and B.
A B
Difference of TablesDifference of Tables33
The intersection of A and B (AB)
A table containing only rows that appear in both A and B.
A B
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
The difference of A and B (A–B)
A table containing rows that appear in A but not in B.
A B
Difference of TablesDifference of Tables33
The difference of A and B (A–B)
A table containing rows that appear in A but not in B.
A B
33
Consider the members of the Bridge Club and Consider the members of the Bridge Club and
the Chess Club. The two database files have the Chess Club. The two database files have
the same structure:the same structure:
The Situation:
Bridge Club & Chess Club
field type width contents
id numeric 4 student id number
name character 10 name
sex character 1 sex: M / F
class character 2 class
Consider the members of the Bridge Club and Consider the members of the Bridge Club and
the Chess Club. The two database files have the Chess Club. The two database files have
the same structure:the same structure:
The Situation:
Bridge Club & Chess Club
field type width contents
id numeric 4 student id number
name character 10 name
sex character 1 sex: M / F
class character 2 class
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
Before using SQL, open the two tables:Before using SQL, open the two tables:
Bridge [A] Chess [B]
id namesexclass id namesexclass
19812AaronM 1A 19802Mary F 1A
29801PeterM 1A 29801PeterM 1A
39814KennyM 1B 39815Eddy M 1B
49806KittyF 1B 49814KennyM 1B
59818EdmondM 1C 59817GeorgeM 1C
: : : : : : : :
SELECT ASELECT A
USE bridgeUSE bridge
SELECT BSELECT B
USE chessUSE chess
Difference of TablesDifference of Tables33
Before using SQL, open the two tables:Before using SQL, open the two tables:
Bridge [A] Chess [B]
id namesexclass id namesexclass
19812AaronM 1A 19802Mary F 1A
29801PeterM 1A 29801PeterM 1A
39814KennyM 1B 39815Eddy M 1B
49806KittyF 1B 49814KennyM 1B
59818EdmondM 1C 59817GeorgeM 1C
: : : : : : : :
SELECT ASELECT A
USE bridgeUSE bridge
SELECT BSELECT B
USE chessUSE chess
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
UNION ;
SELECT * FROM chess ;
ORDER BY class, name INTO TABLE party
eg. 22eg. 22 The two clubs want to hold a joint party.The two clubs want to hold a joint party.Make a list of all Make a list of all
students. (Union)students. (Union)
SELECT ...... FROM ...... WHERE ...... ;SELECT ...... FROM ...... WHERE ...... ;
UNION ;UNION ;
SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......
Result
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
UNION ;
SELECT * FROM chess ;
ORDER BY class, name INTO TABLE party
eg. 22eg. 22 The two clubs want to hold a joint party.The two clubs want to hold a joint party.Make a list of all Make a list of all
students. (Union)students. (Union)
SELECT ...... FROM ...... WHERE ...... ;SELECT ...... FROM ...... WHERE ...... ;
UNION ;UNION ;
SELECT ...... FROM ...... WHERE ......SELECT ...... FROM ...... WHERE ......
Result
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
WHERE id IN ( SELECT id FROM chess ) ;
TO PRINTER
eg. 23eg. 23 Print a list of students who are members of both Print a list of students who are members of both clubs. clubs.
(Intersection)(Intersection)
SELECT ...... FROM SELECT ...... FROM table1table1 ; ;
WHERE WHERE colcol IN ( SELECT IN ( SELECT colcol FROM FROM table2table2 ) )
Result
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
WHERE id IN ( SELECT id FROM chess ) ;
TO PRINTER
eg. 23eg. 23 Print a list of students who are members of both Print a list of students who are members of both clubs. clubs.
(Intersection)(Intersection)
SELECT ...... FROM SELECT ...... FROM table1table1 ; ;
WHERE WHERE colcol IN ( SELECT IN ( SELECT colcol FROM FROM table2table2 ) )
Result
Union, Intersection and Union, Intersection and
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
WHERE id NOT IN ( SELECT id FROM chess ) ;
INTO TABLE diff
eg. 24eg. 24 Make a list of students who are members of the Make a list of students who are members of the Bridge Bridge
Club but not Chess Club. (Difference)Club but not Chess Club. (Difference)
SELECT ...... FROM SELECT ...... FROM table1table1 ; ;
WHERE WHERE colcol NOT IN ( SELECT NOT IN ( SELECT colcol FROM FROM table2table2 ) )
Result
Difference of TablesDifference of Tables33
SELECT * FROM bridge ;
WHERE id NOT IN ( SELECT id FROM chess ) ;
INTO TABLE diff
eg. 24eg. 24 Make a list of students who are members of the Make a list of students who are members of the Bridge Bridge
Club but not Chess Club. (Difference)Club but not Chess Club. (Difference)
SELECT ...... FROM SELECT ...... FROM table1table1 ; ;
WHERE WHERE colcol NOT IN ( SELECT NOT IN ( SELECT colcol FROM FROM table2table2 ) )
Result
Multiple Tables:Multiple Tables:44
• SQL provides a convenient operation to SQL provides a convenient operation to
retrieve information from multiple retrieve information from multiple
tables. tables.
• This operation is called This operation is called joinjoin. .
• The join operation will The join operation will combinecombine the tables the tables
into into one large table with all possible one large table with all possible
combinations combinations (Math: Cartesian Product), and then (Math: Cartesian Product), and then
it will filter it will filter the rows of this combined table to the rows of this combined table to
yield useful yield useful information.information.
• SQL provides a convenient operation to SQL provides a convenient operation to
retrieve information from multiple retrieve information from multiple
tables. tables.
• This operation is called This operation is called joinjoin. .
• The join operation will The join operation will combinecombine the tables the tables
into into one large table with all possible one large table with all possible
combinations combinations (Math: Cartesian Product), and then (Math: Cartesian Product), and then
it will filter it will filter the rows of this combined table to the rows of this combined table to
yield useful yield useful information.information.
Multiple Tables:Multiple Tables:44
field1
A
B
field2
1
2
3
field1field2
A
A
A
1
2
3
B
B
B
1
2
3
field1
A
B
field2
1
2
3
field1field2
A
A
A
1
2
3
B
B
B
1
2
3
44
Each student should learn a musical instrument.Each student should learn a musical instrument.
Two database files:Two database files: student.dbfstudent.dbf && music.dbfmusic.dbf
The common field: The common field: student idstudent id
fieldfield typetype widthwidth contentscontents
idid numeric numeric 4 4 student id number student id number
typetype character character 10 10 type of the music instrument type of the music instrument
The Situation:
Music Lesson
SELECT ASELECT A
USE studentUSE student
SELECT BSELECT B
USE musicUSE music
Each student should learn a musical instrument.Each student should learn a musical instrument.
Two database files:Two database files: student.dbfstudent.dbf && music.dbfmusic.dbf
The common field: The common field: student idstudent id
fieldfield typetype widthwidth contentscontents
idid numeric numeric 4 4 student id number student id number
typetype character character 10 10 type of the music instrument type of the music instrument
The Situation:
Music Lesson
SELECT ASELECT A
USE studentUSE student
SELECT BSELECT B
USE musicUSE music
Natural JoinNatural Join44
A A Natural JoinNatural Join is a join operation that joins two is a join operation that joins two
tables bytables bytheir 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
A A Natural JoinNatural Join is a join operation that joins two is a join operation that joins two
tables bytables bytheir 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
Natural JoinNatural Join44
MusicMusic
idid
98019801
typetype
StudentStudent
98019801
idid namenameclassclass
98019801
ProductProduct
idid namenameclassclasstypetype
Same idSame id
JoinJoin
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)
MusicMusic
idid
98019801
typetype
StudentStudent
98019801
idid namenameclassclass
98019801
ProductProduct
idid namenameclassclasstypetype
Same idSame id
JoinJoin
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)
SELECT s.class, s.name, s.id, m.type ;
FROM student s, music m ;
WHERE s.id=m.id ORDER BY class, name
Natural JoinNatural Join44
classname idtype
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny9803Violin
1A Luke 9810Piano
1A Mary 9802Flute
: : : :
Result
eg. 25eg. 25 Make a list of students and the instruments they Make a list of students and the instruments they learn. learn.
(Natural Join)(Natural Join)
FROM student s, music m ;
WHERE s.id=m.id ORDER BY class, name
Natural JoinNatural Join44
classname idtype
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny9803Violin
1A Luke 9810Piano
1A Mary 9802Flute
: : : :
Result
eg. 25eg. 25 Make a list of students and the instruments they Make a list of students and the instruments they learn. learn.
(Natural Join)(Natural Join)
eg. 26eg. 26 Find the number of students learning piano in Find the number of students learning piano in each each
class.class.
Natural JoinNatural Join44
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
class.class.
Natural JoinNatural Join44
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
Natural JoinNatural Join44
MusicMusic
StudentStudent
ProductProduct
JoinJoin
ConditionCondition
m.type= "Piano"m.type= "Piano"
Group ByGroup By
classclass
eg. 26eg. 26
MusicMusic
StudentStudent
ProductProduct
JoinJoin
ConditionCondition
m.type= "Piano"m.type= "Piano"
Group ByGroup By
classclass
eg. 26eg. 26
eg. 26eg. 26 Find the number of students learning piano in Find the number of students learning piano in each each
class.class.
SELECT s.class, COUNT(*) ;
FROM student s, music m ;
WHERE s.id=m.id AND m.type="Piano" ;
GROUP BY class ORDER BY class
Natural JoinNatural Join44
class cnt
1A 4
1B 2
1C 1
Result
class.class.
SELECT s.class, COUNT(*) ;
FROM student s, music m ;
WHERE s.id=m.id AND m.type="Piano" ;
GROUP BY class ORDER BY class
Natural JoinNatural Join44
class cnt
1A 4
1B 2
1C 1
Result
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
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
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)
Outer JoinOuter Join44
No matchNo match
MusicMusic
idid typetype
StudentStudent
98019801
idid namenameclassclass
instrument. (No match)instrument. (No match)
Outer JoinOuter Join44
No matchNo match
MusicMusic
idid typetype
StudentStudent
98019801
idid namenameclassclass
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
Outer JoinOuter Join44
Result
classname id
1A Mandy 9821
1B Kenny 9814
1B Tobe 9805
1C Edmond9818
1C George9817
: : :
instrument. (No match)instrument. (No match)
SELECT class, name, id FROM student ;
WHERE id NOT IN ( SELECT id FROM music ) ;
ORDER BY class, name
Outer JoinOuter Join44
Result
classname id
1A Mandy 9821
1B Kenny 9814
1B Tobe 9805
1C Edmond9818
1C George9817
: : :
eg. 28eg. 28 Make a checking list of students and the Make a checking list of students and the instruments they learn. instruments they learn.
The list should also The list should also contain the students without an instrument.contain the students without an instrument.
(Outer Join)(Outer Join)
Outer JoinOuter Join44
The list should also The list should also contain the students without an instrument.contain the students without an instrument.
(Outer Join)(Outer Join)
Outer JoinOuter Join44
Outer JoinOuter Join44
Natural JoinNatural Join
No MatchNo Match
Outer JoinOuter Join
eg. 28eg. 28
Natural JoinNatural Join
No MatchNo Match
Outer JoinOuter Join
eg. 28eg. 28
SELECT s.class, s.name, s.id, m.type ;
FROM student s, music m ;
WHERE s.id=m.id ;
Outer JoinOuter Join44
UNION ;
SELECT class, name, id, "" ;
FROM student ;
WHERE id NOT IN ( SELECT id FROM music ) ;
ORDER BY 1, 2
eg. 28eg. 28
FROM student s, music m ;
WHERE s.id=m.id ;
Outer JoinOuter Join44
UNION ;
SELECT class, name, id, "" ;
FROM student ;
WHERE id NOT IN ( SELECT id FROM music ) ;
ORDER BY 1, 2
eg. 28eg. 28
Outer JoinOuter Join44
empty
classname id
1A Mandy 9821
1B Kenny 9814
1B Tobe 9805
1C Edmond9818
1C George9817
: : :
No Match
classname idtype
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny9803Violin
1A Luke 9810Piano
1A Mary 9802Flute
: : : :
Natural Join
classname id type
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny 9803Violin
1A Luke 9810Piano
1A Mandy 9821
1A Mary 9802Flute
1A Peter 9801Piano
1A Ron 9813Guitar
1B Eddy 9815Piano
1B Janet 9822Guitar
1B Kenny 9814
1B Kitty 9806Recorder
: : : :
Outer Join
empty
classname id
1A Mandy 9821
1B Kenny 9814
1B Tobe 9805
1C Edmond9818
1C George9817
: : :
No Match
classname idtype
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny9803Violin
1A Luke 9810Piano
1A Mary 9802Flute
: : : :
Natural Join
classname id type
1A Aaron 9812Piano
1A Bobby 9811Flute
1A Gigi 9824Recorder
1A Jill 9820Piano
1A Johnny 9803Violin
1A Luke 9810Piano
1A Mandy 9821
1A Mary 9802Flute
1A Peter 9801Piano
1A Ron 9813Guitar
1B Eddy 9815Piano
1B Janet 9822Guitar
1B Kenny 9814
1B Kitty 9806Recorder
: : : :
Outer Join
Tags
Categories
GeneralDownload
Download Slideshow Get the original presentation fileQuick Actions
Statistics
- Views 58
- Slides 68
- Age 424 days
Related Slideshows
22
Pray For The Peace Of Jerusalem and You Will Prosper
RodolfoMoralesMarcuc
32 views
26
Don_t_Waste_Your_Life_God.....powerpoint
chalobrido8
35 views
31
VILLASUR_FACTORS_TO_CONSIDER_IN_PLATING_SALAD_10-13.pdf
JaiJai148317
32 views
14
Fertility awareness methods for women in the society
Isaiah47
30 views
35
Chapter 5 Arithmetic Functions Computer Organisation and Architecture
RitikSharma297999
29 views
5
syakira bhasa inggris (1) (1).pptx.......
ourcommunity56
30 views