logging in or signing up SQL ppt aSGuest14049 Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINT lite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: Embed: Flash iPad Copy Does not support media & animations WordPress Embed Customize Embed URL: Copy Thumbnail: Copy The presentation is successfully added In Your Favorites. Views: 8710 Category: Entertainment License: All Rights Reserved Like it (11) Dislike it (0) Added: March 03, 2009 This Presentation is Public Favorites: 6 Presentation Description No description available. Comments Posting comment... By: brahma.23389 (44 month(s) ago) bhai yaar jaldi se download ka link do kal midsem hai Saving..... Post Reply Close Saving..... Edit Comment Close Premium member Presentation Transcript 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 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" General Structure : General Structure I 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" General Structure : General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls. 1B 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" 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 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) 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) 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 Comparison : Comparison II eg. 9 List the students whose names start with "T". SELECT name, class FROM student ; WHERE name LIKE "T%" 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" 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. Grouping : Grouping III SELECT class, COUNT(*) FROM student ; GROUP BY class eg. 11 List the number of students of each class. Grouping : Grouping III eg. 12 List the average Math test score of each class. Grouping : Grouping III eg. 12 List the average Math test score of each class. SELECT class, AVG(mtest) FROM student ; GROUP BY class Grouping : Grouping III eg. 13 List the number of girls of each district. SELECT dcode, COUNT(*) FROM student ; WHERE sex="F" GROUP BY dcode 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 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 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. 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. 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). 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 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 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 Union, Intersection and Difference of Tables : Union, Intersection and Difference of Tables 3 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. 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. 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 ...... 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 ) 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 ) 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 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 SELECT a.comcol, a.col1, b.col2, expr1, expr2 ; FROM table1 a, table2 b ; WHERE a.comcol = b.comcol Natural Join : Natural Join 4 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 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 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 Outer Join : Outer Join 4 Outer Join : eg. 27 List the students who have not yet chosen an instrument. (No match) Outer Join 4 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 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 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 You do not have the permission to view this presentation. In order to view it, please contact the author of the presentation.
SQL ppt aSGuest14049 Download Post to : URL : Related Presentations : Share Add to Flag Embed Email Send to Blogs and Networks Add to Channel Uploaded from authorPOINT lite Insert YouTube videos in PowerPont slides with aS Desktop Copy embed code: Embed: Flash iPad Copy Does not support media & animations WordPress Embed Customize Embed URL: Copy Thumbnail: Copy The presentation is successfully added In Your Favorites. Views: 8710 Category: Entertainment License: All Rights Reserved Like it (11) Dislike it (0) Added: March 03, 2009 This Presentation is Public Favorites: 6 Presentation Description No description available. Comments Posting comment... By: brahma.23389 (44 month(s) ago) bhai yaar jaldi se download ka link do kal midsem hai Saving..... Post Reply Close Saving..... Edit Comment Close Premium member Presentation Transcript 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 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" General Structure : General Structure I 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" General Structure : General Structure I eg. 4 List the names and ages (1 d.p.) of 1B girls. 1B 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" 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 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) 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) 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 Comparison : Comparison II eg. 9 List the students whose names start with "T". SELECT name, class FROM student ; WHERE name LIKE "T%" 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" 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. Grouping : Grouping III SELECT class, COUNT(*) FROM student ; GROUP BY class eg. 11 List the number of students of each class. Grouping : Grouping III eg. 12 List the average Math test score of each class. Grouping : Grouping III eg. 12 List the average Math test score of each class. SELECT class, AVG(mtest) FROM student ; GROUP BY class Grouping : Grouping III eg. 13 List the number of girls of each district. SELECT dcode, COUNT(*) FROM student ; WHERE sex="F" GROUP BY dcode 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 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 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. 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. 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). 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 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 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 Union, Intersection and Difference of Tables : Union, Intersection and Difference of Tables 3 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. 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. 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 ...... 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 ) 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 ) 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 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 SELECT a.comcol, a.col1, b.col2, expr1, expr2 ; FROM table1 a, table2 b ; WHERE a.comcol = b.comcol Natural Join : Natural Join 4 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 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 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 Outer Join : Outer Join 4 Outer Join : eg. 27 List the students who have not yet chosen an instrument. (No match) Outer Join 4 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 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 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