eee-474 internet database place : 3th floor # 305 ... · 2/1/2011 · eee-474 internet database...

3.04.2017

1

ADO.NET

EEE-474

Internet Database

Programming II

Assoc. Prof. Dr. Turgay İBRİKÇİ

Dept of Electrical-Electronics Eng.

ADO.NET

2

Lectures of the EEE-474

• Associate Prof. Dr. Turgay IBRIKCI

Place : 3th floor # 305

– Phone : (322) 338 68 68 / 139

– Email : [email protected]

• Please indicate your name and course number at the Subject field

– Office Hour

• Assistant ……

• The web address : http://eembdersler.wordpress.com

ADO.NET

3

Course Structure

• Textbook

No single textbook covers the material for the course.

“ADO.NET 4 Step by Step”, Tim Patrick , 2010,

The slices will be supplemented by material from publicly available sources on the web.

Internet Materials

Useful extension text book

• All kind of ADO with C# books are useful for this course

ADO.NET

4

Course Structure

• Lectures Monday 15:30-19:00 (Every week)

• Laboratory Monday 10:15-12:00 (Every week)

• HomeworksOne Chapt. to present with examples

• Final Project

• Presentation

– Mid Exam The department will setup it

– Final Exam The department will setup it

ADO.NET

5

Grading scale and policy

Labs (Homeworks) All weeks 25% of 40%

Midterm Exam 1 15% of 40%

Final Exam 1 15% of 60%

Project/Presentation 1 45% of 60%

Total

100%+10%

ADO.NET

Course Objectives

• Understand the basics of Relational Databases

• Write SQL code based on ANSI/ISO standards to build and

maintain database structures with C#

• Update database content with SQL and transaction handling

• Retrieve data from single or multiple tables

• Process data with row and aggregate functions

• Manipulate data with correlated and noncorrelated subqueries

• Apply views to break down problems and enhance security

3.04.2017

2

ADO.NET

Learning Objectives

• Databases

• Database Access

– Review some database theory

– Learn about the ADO.NET namespaces

and core classes

– Learn how to use ADO.NET classes in

an application

ADO.NET

What Is a Database System?

• Database: very large, integrated collection of data.

• Models a real-world enterprise

– Entities (e.g., teams, games)

– Relationships (e.g.,

The Beşiktaş is playing in the super league )

– More recently, also includes active components , often

called “business logic”. (e.g., the BCS ranking system)

• A Database Management System (DBMS) is a software system

designed to store, manage, and facilitate access to databases.

ADO.NET

What is database?

A database is a collection of information that

is organized so that it can easily be

accessed,

managed, and

updated.

A database is made up of files, records

and fields.

ADO.NET

Databases

• Virtually all interesting applications require a structured, persistent data store

– E-Commerce: placing an order, fulfilling an order

– HR: Personnel data

– Sales

– CRM: Customer data0.

– Games

• Database needs vary with the type of application

– Online Transaction Processing/OLTP

– Business Intelligence/Data Warehouse/OLAP “On-Line Analytical Processing ”

ADO.NET

Database Requirements

• Can store, view and modify data

• Can move, copy and transform data

• Can backup and restore data

• Has data integrity

• Is scale able and available

– High number of users

– Lots of data

– High throughput with low response time

• Is secure

• Is capable of application development

ADO.NET

What Is a DBMS?

• A very large, integrated collection of data.

• Models real-world enterprise.

– Entities (e.g., students, courses)

– Relationships (e.g., Ali is taking EEE-472

• A Database Management System (DBMS) is a

software package designed to store and

manage databases.

http://whatis.techtarget.com/definition/0,289893,sid9_gci212343,00.html

3.04.2017

3

ADO.NET

Why Study Databases??

• Shift from computation to information

– at the “low end”: scramble to webspace -a mess

– at the “high end”: scientific applications

• Datasets increasing in diversity and volume.

– Digital libraries, interactive video, Human Genome

project, EOS project

– ... need for DBMS exploding

• DBMS encompasses most of CS

– OS, languages, theory, “AI”, multimedia, logic

?

ADO.NET

Why Use a DBMS?

• Data independence and efficient access.

• Reduced application development time.

• Data integrity and security.

• Uniform data administration.

• Concurrent access, recovery from crashes.

ADO.NET

Data Models

• A data model is a collection of concepts for

describing data.

• A schema is a description of a particular collection of

data, using the given data model.

• The relational model of data is the most widely

used model today.

– Main concept: relation, basically a table with

rows and columns.

– Every relation has a schema, which describes the

columns, or fields. ADO.NET

Levels of Abstraction

• Views describe how users see the

data.

• Conceptual schema defines logical

structure

• Physical schema describes the files

and indexes used.

• (sometimes called the ANSI/SPARC

model)

Physical Schema

Conceptual Schema

View 1 View 2 View 3

DB

Users

ADO.NET

Example: University Database

• Conceptual schema: – Students(sid: string, name: string, login: string,

age: integer, gpa:real)

– Courses(cid: string, cname:string, credits:integer)

– Enrolled(sid:string, cid:string, grade:string)

• Physical schema: – Relations stored as unordered files.

– Index on first column of Students.

• External Schema (View): – Course_info(cid:string,enrollment:integer)

ADO.NET

Instance of Students Relation

Students( sid: string, name: string, login: string,

age: integer, gpa: real )

sid name login age gpa

53666 Jones jones@cs 18 3.4

53688 Smith smith@ee 18 3.2

53650 Smith smith@math 19 3.8

mailto:jones@cs

mailto:smith@ee

mailto:smith@math

3.04.2017

4

ADO.NET

Levels of Abstraction

• Many external schemata, single

conceptual(logical) schema and

physical schema.

– External schemata describe

how users see the data.

– Conceptual schema defines

logical structure

– Physical schema describes

the files and indexes used.

Schemas are defined using DDL;

data is modified/queried using DML.

Physical Schema

Conceptual Schema

External Schema 1

External Schema 3

External Schema 2

ADO.NET

Data Independence

• Applications insulated from how data is

structured and stored.

• Logical data independence: Protection

from changes in logical structure of data.

• Physical data independence: Protection

from changes in physical structure of data.

One of the most important benefits of using a DBMS!

ADO.NET

Structure of a DBMS

• A typical DBMS has a

layered architecture.

• This is one of several

possible architectures;

each system has its

own variations.

Query Optimization

and Execution

Relational Operators

Files and Access Methods

Buffer Management

Disk Space Management

DB

These layers must consider concurrency control and recovery

ADO.NET

Layered Approach to Using a DBMS

• Applications that work with a DBMS use

a layered approach

– C# application is topmost layer

– C# sends instructions to next layer,

the DBMS

– DBMS works directly with data

• Programmer need not understand the

physical structure of the data

– Just need to know how to interact

with the DBMS

C# Program

Database

Management

System

Data

ADO.NET

Relational Database Model

• Introduced in the 60’s and 70’s and is the most

common type of DBMS today

• Data elements stored in simple tables (related)

• General structure good for many problems

• Easy to understand, modify, maintain

Examples: MySQL, Access, Oracle, SQL Server

• We will focus on relational databases using

Microsoft SQL Server2016 Express Edition

ADO.NET

The Relational Model

• Views entities as two-dimensional tables

– Records are rows

– Attributes (fields) are columns

• Tables can be linked

• Supports one-to-many, many-to-many, and one-

to-one relationships

3.04.2017

5

ADO.NET

Terminology

• Database: a collection of interrelated tables

• Table: a logical grouping of related data

– A category of people, places, or things

– For example, employees or departments

– Organized into rows and columns

• Field: an individual piece of data pertaining to an item, an employee name for instance

• Record: the complete data about a single item such as all information about an employee

– A record is a row of a table

ADO.NET

Field

Record

Here is a sample record from a telephone directory data file made up of different fields:

Mr.

Title

Name

Postcode

Tel. No.

John

Smith

101 High Street

Ayr

KA7 3KL

01292 262626

Sample Fields

Address

Surname

Town

ADO.NET

Emp_Id First_Name Last_Name Department

001234 Ignacio Fleta Accounting

002000 Christian Martin Computer Support

002122 Orville Gibson Human Resources

003400 Ben Smith Accounting

003780 Allison Chong Computer Support

Database Table

Row (Record)

Column Field

• Each table has a primary key

– Uniquely identifies that row of the table

– Emp_Id is the primary key in this example

– Serves as an index to quickly retrieve the record

• Columns are also called fields or attributes

• Each column has a particular data type

ADO.NET

Relational Database : Books

Database

• Primary key uniquely identifies each row

– Rule of Entity Integrity

• Composite primary key

• Lines connecting tables

– Relationships

• One-to-many relationship

• Foreign key

– Join multiple tables

– Rule of Referential Integrity

ADO.NET


Database

Field Description authorID Author’s ID number in the database. In the Books database,

this Integer column is defined as auto-increment. For each new row inserted in this table, the database increments

the authorID value, ensuring that each row has a unique

authorID. This column represents the table’s primary key. firstName Author’s first name (a String). lastName Author’s last name (a String).

ADO.NET


Database authorID firstName lastName 1 Harvey Deitel

2 Paul Deitel

3 Tem Nieto

4 Kate Steinbuhler

5 Sean Santry

6 Ted Lin

7 Praveen Sadhu

8 David McPhie

9 Cheryl Yaeger

10 Marina Zlatkina

11 Ben Wiedermann

12 Jonathan Liperi

3.04.2017

6

ADO.NET


Database Field Description publisherID The publisher’s ID number in the database. This

auto-incremented Integer is the table’s primary key.

publisherName The name of the publisher (a String).

publisherID publisherName 1 Prentice Hall

2 Prentice Hall PTG

Field Description authorID The author’s ID number, which allows the database

to associate each book with a specific author. The integer ID number in this column must also appear

in the Authors table. isbn The ISBN number for a book (a String).

ADO.NET


Database authorID isbn authorID isbn 1 0130895725 2 0139163050

1 0132261197 2 013028419x

1 0130895717 2 0130161438

1 0135289106 2 0130856118

1 0139163050 2 0130125075

1 013028419x 2 0138993947

1 0130161438 2 0130852473

1 0130856118 2 0130829277

1 0130125075 2 0134569555

1 0138993947 2 0130829293

1 0130852473 2 0130284173

1 0130829277 2 0130284181

1 0134569555 2 0130895601

1 0130829293 3 013028419x

1 0130284173 3 0130161438

1 0130284181 3 0130856118

1 0130895601 3 0134569555

2 0130895725 3 0130829293

2 0132261197 3 0130284173

2 0130895717 3 0130284181

2 0135289106 4 0130895601

ADO.NET

Relational Database : Book

Database

Field Description isbn ISBN number of the book (a String). title Title of the book (a String). editionNumber Edition number of the book (a String). copyright Copyright year of the book (an Integer). description Description of the book (a String). publisherID Publisher’s ID number (an Integer). This value

must correspond to an ID number in the

Publishers table. imageFile Name of the file containing the book’s cover image

(a String). price Suggested retail price of the book (a real number).

[Note: The prices shown in this database are for

example purposes only.] ADO.NET


Database isbn title edition-

Number publisherID copy-

right price

0130923613 Python How to Program 1 1 2002 $69.95

0130622214 C# How to Program 1 1 2002 $69.95

0130341517 Java How to Program 4 1 2002 $69.95

0130649341 The Complete Java Training

Course

4 2 2002 $109.95

0130895601 Advanced Java 2 Platform

How to Program

1 1 2002 $69.95

0130308978 Internet and World Wide

Web How to Program

2 1 2002 $69.95

0130293636 Visual Basic .NET How to

Program

2 1 2002 $69.95

0130895636 The Complete C++ Training

Course

3 2 2001 $109.95

0130895512 The Complete e-Business &

e-Commerce Programming

Training Course

1 2 2001 $109.95

ADO.NET


Database

Table relationships in Books

ADO.NET

Components of Database

Programming

• SQL

• ADO .NET

– ADO .NET Classes

3.04.2017

7

ADO.NET

What Is ADO.NET?

• ADO .NET is a collection of classes, interfaces, structures,

and enumerated types that manage data access from

relational data stores within the .NET Framework

– These collections are organized into namespaces:

• System.Data, System.Data.OleDb,

System.Data.SqlClient, etc.

• ADO .NET is an evolution from ADO.

– Does not share the same object model, but shares many

of the same paradigms and functionality.

ADO.NET

ADO.Net Architecture

• ADO.Net provides an architecture for communicating between an

application and a data source.

• The “data source” can be anything that has the required API, but

usually it is a database server.

Data Source

Connection Object

(ADO.Net)

Data Adaptor

Dataset (Local)

Application

ADO.NET

Getting SqlServer

• You need a Sql Server .

• Download from

https://www.microsoft.com/en-us/sql-server/sql-server-

downloads

• Download Express Edition (1.2GB)

– Extract it.

– Install it

ADO.NET

What is SQL?

–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. (instead of algorithms)

ADO.NET

Concept of SQL

–The user specifies a certain condition.

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

ADO.NET

How to involve SQL in CSharp

–Before using SQL, you are able to be accessed the database

– the tables should be opened.

–The SQL command can be entered by CSharp

–To perform exact matching

https://www.microsoft.com/en-us/sql-server/sql-server-downloads









3.04.2017

8

ADO.NET

Basic structure of an SQL query

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 / CURSOR

TO FILE [ADDITIVE], TO PRINTER, TO SCREEN

Union UNION

ADO.NET

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

The Situation: Student Particulars

ADO.NET

General Structure

SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;

FROM tablename WHERE condition

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

ADO.NET

General Structure

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



– And col1, col2 are their corresponding column names in the output table.

ADO.NET

General Structure

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



ADO.NET

General Structure

Before using SQL, open the student file: USE student

eg. 1 List all the student records.

SELECT * FROM student id name dob sex class mtest hcode dcode remission

9801 Peter 06/04/86 M 1A 70 R SSP .F.

9802 Mary 01/10/86 F 1A 92 Y HHM .F.

9803 Johnny 03/16/86 M 1A 91 G SSP .T.

9804 Wendy 07/09/86 F 1B 84 B YMT .F.

9805 Tobe 10/17/86 M 1B 88 R YMT .F.

: : : : : : : : :

Result

3.04.2017

9

ADO.NET

General Structure

eg. 2 List the names and house code of 1A students. SELECT name, hcode, class FROM student

WHERE class="1A"

Class

1A

1A

1A

1B

1B

:

Class

1A

1A

1A

1B

1B

:

class="1A"

ADO.NET

General Structure

name hcode class

Peter R 1A

Mary Y 1A

Johnny G 1A

Luke G 1A

Bobby B 1A

Aaron R 1A

: : :

Result

eg. 2 List the names and house code of 1A students.

ADO.NET

General Structure

eg. 4 List the names and ages (1 d.p.) of 1B girls.

1B Girls ?

ADO.NET

Condition for "1B Girls":

1) class = "1B"

2) sex = "F"

3) Both ( AND operator)

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

ADO.NET

General Structure


What is "age"?

ADO.NET

Functions: # days : DATE( ) – dob # years :(DATE( ) – dob) / 365 1 d.p.: ROUND(__ , 1)

General Structure


3.04.2017

10

ADO.NET

General Structure


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

ADO.NET

General Structure

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

name id class

Peter 9801 1A

Mary 9802 1A

Luke 9810 1A

Bobby 9811 1A

Aaron 9812 1A

Ron 9813 1A

Gigi 9824 1A

: : :

Result

ADO.NET

Comparison

expr IN ( value1, value2, value3)

expr BETWEEN value1 AND value2

expr LIKE "%_"

ADO.NET

Comparison

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)

name class bdate

Peter 1A Wednesday

Wendy 1B Wednesday

Kevin 1C Saturday

Luke 1A Wednesday

Aaron 1A Saturday

: : :

Result

ADO.NET

Comparison

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) name class dob

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

ADO.NET

Comparison

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

name mtest

Luke 86

Aaron 83

Gigi 84

Result

3.04.2017

11

ADO.NET

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

ADO.NET

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" name class hcode

Aaron 1A R

Janet 1B R

Paula 2A R

Result

ADO.NET

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.

ADO.NET

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

class.

ADO.NET

COUNT( )

Group By Class

1A

COUNT( ) 1B

COUNT( ) 1C

1A

1B

1C Student

class 1A

1A

1A

1B

1B

1B

1B

1B

1B

1C

1C

1C ADO.NET

Grouping

SELECT class, COUNT(*) FROM student ;

GROUP BY class

class cnt

1A 10

1B 9

1C 9

2A 8

2B 8

2C 6

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

Result

3.04.2017

12

ADO.NET

Grouping

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

ADO.NET

Group By Class

AVG( )

AVG( )

AVG( )

1A

1B

1C Student

class 1A

1A

1A

1B

1B

1B

1B

1B

1B

1C

1C

1C

ADO.NET

Grouping

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

SELECT class, AVG(mtest) FROM student ; GROUP BY class

class avg_mtest

1A 85.90

1B 70.33

1C 37.89

2A 89.38

2B 53.13

2C 32.67

Result

ADO.NET

Grouping III eg. 13 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

ADO.NET

Grouping

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

max_mtest min_mtest dcode

92 36 HHM

91 19 MKK

91 31 SSP

92 36 TST

75 75 TSW

88 38 YMT

Result

ADO.NET

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

avg_mtest class

86.00 1A

77.75 1B

35.60 1C

86.50 2A

56.50 2B

Result

3.04.2017

13

ADO.NET

Display Order IV SELECT ...... FROM ...... WHERE ......

GROUP BY ..... ;

ORDER BY colname ASC / DESC

ADO.NET

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.

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

ADO.NET

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.

name id class dcode

Jimmy 9712 2A HHM

Tim 9713 2A HHM

Samual 9714 2A SHT

Rosa 9703 2A SSP

Helen 9702 2A TST

Joseph 9715 2A TSW

Paula 9701 2A YMT

Susan 9704 2A YMT

Result

ADO.NET

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

cnt docode

11 YMT

10 HHM

10 SSP

9 MKK

5 TST

2 TSW

1 KWC

1 MMK

1 SHT

Result

ADO.NET

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)

ADO.NET

Display Order IV name hcode class

Bobby B 1A

Teddy B 1B

Joseph B 2A

Zion B 2B

Leslie B 2C

Johnny G 1A

Luke G 1A

Kevin G 1C

George G 1C

: : :

Result

Order by

class

Blue House

Green House

: :

Order by

hcode

3.04.2017

14

ADO.NET

Output

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.

ADO.NET

Output

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

id name dob sex class mtest hcode dcode remission

9707 Zion 07/29/85 M 2B 51 B MKK .F.

9709 Yvonne 08/24/85 F 2C 10 R TST .F.

9804 Wendy 07/09/86 F 1B 84 B YMT .F.

9819 Vincent 03/15/85 M 1C 29 Y MKK .F.

9805 Tobe 10/17/86 M 1B 88 R YMT .F.

9713 Tim 06/19/85 M 2A 91 R HHM .T.

9816 Teddy 01/30/86 M 1B 64 B SSP .F.

: : : : : : : : :

Result

ADO.NET

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 class name sex

1A Aaron M

1A Peter M

1A Ron M

1B Tobe M

1B Janet F

1B Kitty F

1B Mimi F

: : :

Result

ADO.NET

Union, Intersection and Difference of Tables

A B

The union of A and B (AB)

A table containing all the rows from A and B.

ADO.NET


The intersection of A and B (AB)

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

A B

ADO.NET


The difference of A and B (A–B)

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

A B

3.04.2017

15

ADO.NET

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

ADO.NET


Before using SQL, open the two tables:

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

: : : : : : : :

SELECT A USE bridge SELECT B USE chess

ADO.NET


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

ADO.NET



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

ADO.NET



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

ADO.NET

Multiple Tables:

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

3.04.2017

16

ADO.NET

Multiple Tables:

field1

A

B

field2

1

2

3

field1 field2

A

A

A

1

2

3

B

B

B

1

2

3

ADO.NET

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

ADO.NET

Natural Join

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

ADO.NET

Natural Join

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)

ADO.NET

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

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 Mary 9802 Flute

: : : :

Result

eg. 25 Make a list of students and the instruments they learn. (Natural Join)

ADO.NET

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

Natural Join

Three Parts :

(1) Natural Join.

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

(3) GROUP BY class

3.04.2017

17

ADO.NET

Natural Join

Music

Student

Product

Join Condition m.type=

"Piano"

Group By class

eg. 26

ADO.NET

eg. 26 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

Natural Join

class cnt

1A 4

1B 2

1C 1

Result

ADO.NET

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

ADO.NET

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

Outer Join

No match

Music

id type

Student

9801

id name class

ADO.NET

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

Result

class name id

1A Mandy 9821

1B Kenny 9814

1B Tobe 9805

1C Edmond 9818

1C George 9817

: : :ADO.NET

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

3.04.2017

18

ADO.NET

Outer Join

Natural Join

No Match

Outer Join

eg. 28

ADO.NET

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


WHERE s.id=m.id ;

Outer Join

UNION ;

SELECT class, name, id, "" ;

FROM student ;

WHERE id NOT IN ( SELECT id FROM music ) ; ORDER BY 1, 2

eg. 28

ADO.NET

Outer Join

empty class name id

1A Mandy 9821

1B Kenny 9814

1B Tobe 9805

1C Edmond 9818

1C George 9817

: : :

No Match

class name id type

1A Aaron 9812 Piano

1A Bobby 9811 Flute


1A Jill 9820 Piano


1A Luke 9810 Piano

1A Mary 9802 Flute

: : : :

Natural Join

class name id type

1A Aaron 9812 Piano

1A Bobby 9811 Flute


1A Jill 9820 Piano


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

eee-474 internet database place : 3th floor # 305 ... · 2/1/2011 · eee-474 internet database...

Documents