sql

Slide 1: 

Structured Query Language SQL

Introduction to SQL: 

Introduction to SQL What is SQL? When a user wants to get some information from a database file, he can issue a query . 1 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 SQL: 

Introduction to SQL Concept of SQL The user specifies a certain condition. 1 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 SQL: 

Introduction to SQL How to involve SQL in FoxPro Before using SQL, the tables should be opened. 1 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 query: 

Basic structure of an SQL query 2

Slide 6: 

field type width contents id numeric 4 student id number name character 10 name dob date 8 date of birth sex character 1 sex: M / F class character 2 class hcode character 1 house code: R, Y, B, G dcode character 3 district code remission logical 1 fee remission mtest numeric 2 Math test score 2 The Situation: Student Particulars

General Structure: 

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

General Structure: 

General Structure I 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. SELECT [ ALL / DISTINCT ] expr1 [ AS col1 ], expr2 [ AS col2 ] ; FROM tablename WHERE condition And col1 , col2 are their corresponding column names in the output table.

General Structure: 

General Structure I 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 tablename WHERE condition

General Structure: 

General Structure I Before using SQL, open the student file: USE student eg. 1 List all the student records. SELECT * FROM student Result

General Structure: 

General Structure I eg. 2 List the names and house code of 1A students. SELECT name, hcode, class FROM student ; WHERE class="1A" Class 1 A 1 A 1 A 1 B 1 B :  Class 1 A 1 A 1 A 1 B 1 B :     class="1A"

General Structure: 

General Structure I Result eg. 2 List the names and house code of 1A students.

General Structure: 

General Structure I eg. 3 List the residential district of the Red House members. SELECT DISTINCT dcode FROM student ; WHERE hcode="R" Result

General Structure: 

General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls. 1 B Girls ?

General Structure: 

Condition for "1B Girls": 1) class = "1B" 2) sex = "F" 3) Both ( AND operator) General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls.

General Structure: 

General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls. What is "age"?

General Structure: 

Functions: # days : DATE( ) – dob # years :(DATE( ) – dob) / 365 1 d.p.: ROUND(__ , 1) General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls.

General Structure: 

General Structure I eg. 4 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" Result

General Structure: 

General Structure I eg. 5 List the names, id of 1A students with no fee remission. SELECT name, id, class FROM student ; WHERE class="1A" AND NOT remission Result

Comparison: 

Comparison II expr IN ( value1 , value2 , value3 ) expr BETWEEN value1 AND value2 expr LIKE "%_"

Comparison: 

Comparison II eg. 6 List the students who were born on Wednesday or Saturdays. SELECT name, class, CDOW(dob) AS bdate ; FROM student ; WHERE DOW(dob) IN (4,7) Result

Comparison: 

Comparison II eg. 7 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

Comparison: 

Comparison II eg. 8 List the 1A students whose Math test score is between 80 and 90 (incl.) SELECT name, mtest FROM student ; WHERE class="1A" AND ; mtest BETWEEN 80 AND 90 Result

Comparison: 

Comparison II eg. 9 List the students whose names start with "T". SELECT name, class FROM student ; WHERE name LIKE "T%" Result

Comparison: 

Comparison II eg. 10 List the Red house members whose names contain "a" as the 2nd letter. SELECT name, class, hcode FROM student ; WHERE name LIKE "_a%" AND hcode="R" Result

Grouping: 

Grouping III SELECT ...... 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.

Grouping: 

Grouping III eg. 11 List the number of students of each class.

Slide 28: 

COUNT( ) Group By Class 1 A COUNT( ) 1 B COUNT( ) 1 C 1 A 1 B 1 C Student class 1 A 1 A 1 A 1 B 1 B 1 B 1 B 1 B 1 B 1 C 1 C 1 C

Grouping: 

Grouping III SELECT class, COUNT(*) FROM student ; GROUP BY class eg. 11 List the number of students of each class. Result

Grouping: 

Grouping III eg. 12 List the average Math test score of each class.

Slide 31: 

Group By Class AVG( ) AVG( ) AVG( ) 1 A 1 B 1 C Student class 1 A 1 A 1 A 1 B 1 B 1 B 1 B 1 B 1 B 1 C 1 C 1 C

Grouping: 

Grouping III eg. 12 List the average Math test score of each class. SELECT class, AVG(mtest) FROM student ; GROUP BY class Result

Grouping: 

Grouping III eg. 13 List the number of girls of each district. SELECT dcode, COUNT(*) FROM student ; WHERE sex="F" GROUP BY dcode Result

Grouping: 

Grouping III eg. 14 List the max. and min. test score of Form 1 students of each district. SELECT MAX(mtest), MIN(mtest), dcode ; FROM student ; WHERE class LIKE "1_" GROUP BY dcode Result

Grouping: 

Grouping III eg. 15 List the average Math test score of the boys in each class. The list should not contain class with less than 3 boys. SELECT AVG(mtest), class FROM student ; WHERE sex="M" GROUP BY class ; HAVING COUNT(*) >= 3 Result

Display Order: 

Display Order IV SELECT ...... FROM ...... WHERE ...... GROUP BY ..... ; ORDER BY colname ASC / DESC

Display Order: 

Display Order IV SELECT name, id FROM student ; WHERE sex="M" AND class="1A" ORDER BY name eg. 16 List the boys of class 1A, order by their names. ORDER BY dcode Result

Display Order: 

Display Order IV SELECT name, id, class, dcode FROM student ; WHERE class="2A" ORDER BY dcode eg. 17 List the 2A students by their residential district. Result

Display Order: 

Display Order IV SELECT COUNT(*) AS cnt, dcode FROM student ; GROUP BY dcode ORDER BY cnt DESC eg. 18 List the number of students of each district (in desc. order). Result

Display Order: 

Display Order IV SELECT name, class, hcode FROM student ; WHERE sex= " M" ORDER BY hcode, class eg. 19 List the boys of each house order by the classes. (2-level ordering)

Display Order: 

Display Order IV Result Order by class Blue House Green House : : Order by hcode

Output: 

Output V

Output: 

Output V eg. 20 List the students in desc. order of their names and save the result as a database file name.dbf. SELECT * FROM student ; ORDER BY name DESC INTO TABLE name.dbf Result

Output: 

Output V eg. 21 Print the Red House members by their classes, sex and name. SELECT class, name, sex FROM student ; WHERE hcode="R" ; ORDER BY class, sex DESC, name TO PRINTER Result

Union, Intersection and Difference of Tables: 

Union, Intersection and Difference of Tables 3 A B The union of A and B (A  B) A table containing all the rows from A and B .

Union, Intersection and Difference of Tables: 

Union, Intersection and Difference of Tables 3 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 Difference of Tables: 

Union, Intersection and Difference of Tables 3 The difference of A and B (A–B) A table containing rows that appear in A but not in B . A B

Slide 48: 

3 Consider the members of the Bridge Club and the Chess Club. The two database files have 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 Difference of Tables: 

Union, Intersection and Difference of Tables 3 Before using SQL, open the two tables: SELECT A USE bridge SELECT B USE chess

Union, Intersection and Difference of Tables: 

Union, Intersection and Difference of Tables 3 SELECT * FROM bridge ; UNION ; SELECT * FROM chess ; ORDER BY class, name INTO TABLE party eg. 22 The two clubs want to hold a joint party. Make a list of all students. (Union) SELECT ...... FROM ...... WHERE ...... ; UNION ; SELECT ...... FROM ...... WHERE ...... Result

Union, Intersection and Difference of Tables: 

Union, Intersection and Difference of Tables 3 SELECT * FROM bridge ; WHERE id IN ( SELECT id FROM chess ) ; TO PRINTER eg. 23 Print a list of students who are members of both clubs. (Intersection) SELECT ...... FROM table1 ; WHERE col IN ( SELECT col FROM table2 ) Result

Union, Intersection and Difference of Tables: 

Union, Intersection and Difference of Tables 3 SELECT * FROM bridge ; WHERE id NOT IN ( SELECT id FROM chess ) ; INTO TABLE diff eg. 24 Make a list of students who are members of the Bridge Club but not Chess Club. (Difference) SELECT ...... FROM table1 ; WHERE col NOT IN ( SELECT col FROM table2 ) Result

Multiple Tables:: 

Multiple Tables: 4 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 (Math: Cartesian Product), and then it will filter the rows of this combined table to yield useful information.

Multiple Tables:: 

Multiple Tables: 4 field1 A B field2 1 2 3 field1 field2 A A A 1 2 3 B B B 1 2 3

Slide 55: 

4 Each student should learn a musical instrument. Two database files: student.dbf & music.dbf The common field: student id field type width contents id numeric 4 student id number type character 10 type of the music instrument The Situation: Music Lesson SELECT A USE student SELECT B USE music

Natural Join: 

Natural Join 4 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 Join: 

Natural Join 4 Music id 9801 type Student 9801 id name class 9801 Product id name class type Same id Join eg. 25 Make a list of students and the instruments they learn. (Natural Join)

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 Join 4 Result eg. 25 Make a list of students and the instruments they learn. (Natural Join)

Natural Join: 

eg. 26 Find the number of students learning piano in each class. Natural Join 4 Three Parts : (1) Natural Join. (2) Condition: m.type="Piano" (3) GROUP BY class

Natural Join: 

Natural Join 4 Music Student Product Join Condition m.type= "Piano" Group By class eg. 26

Natural Join: 

eg. 26 Find the number of students learning piano in each 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 Join 4 Result

Outer Join: 

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 Join 4

Outer Join: 

eg. 27 List the students who have not yet chosen an instrument. (No match) Outer Join 4 No match Music id type Student 9801 id name class

Outer Join: 

eg. 27 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 Outer Join 4 Result

Outer Join: 

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

Outer Join: 

Outer Join 4 Natural Join No Match Outer Join eg. 28

Outer Join: 

SELECT s.class, s.name, s.id, m.type ; FROM student s, music m ; WHERE s.id=m.id ; Outer Join 4 UNION ; SELECT class, name, id, "" ; FROM student ; WHERE id NOT IN ( SELECT id FROM music ) ; ORDER BY 1, 2 eg. 28

Outer Join: 

Outer Join 4 empty No Match Natural Join Outer Join