SQL ppt

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

By: brahma.23389 (55 month(s) ago)

bhai yaar jaldi se download ka link do kal midsem hai

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