c# and databases - city university of new yorknatacha/teachspring_2013/csc... · way that is...
TRANSCRIPT
CSC 330 Object Oriented CSC 330 Object Oriented ProgrammingProgramming
C# and DatabasesC# and Databases
2
IntroductionIntroduction
Database:Integrated collection of dataDatabase management system (DBMS)
Provides mechanisms for storing and organizing data in a way that is consistent with database’s formatAllows storage and access to database without knowledge of internal representation
Relational Databases most popularUse Structured Query Language to perform queries (search) and manipulate dataProgramming languages need an interface to interact with relational databases
3
Introduction to Database Management SystemIntroduction to Database Management System
A database management system (DBMS) is software that manages large collections of data
It is designed to store, retrieve, and manipulate dataA C# application can interact with a DBMS through a 3-layer manipulation
Application: interacts with the user and sends instructions to DBMSDBMS: works directly with the data and sends the result back to the applicationData: the data stored in DBMS
Data
DBMS
Application
4
What are Databases?What are Databases?
Features of DBMSDefinition of data structure Maintenance facilityAllow access to and storage of data independently of the internal representation of data
Main job of the DBMS is to ensureensure datadata integrityintegrity
5
Relational Database Systems and SQLRelational Database Systems and SQL
Relational database systems are the most popular today (Microsoft Access is a relational database system)SQL is international standard language used with relational database systems to perform queries (i.e. to request information that satisfies given criteria) and to manipulate dataSome popular relational database management systems (RDBMS) are Microsoft SQL Server, Oracle, MySQL, Sybase
6
Relational Database ModelRelational Database Model
Logical representation of data:Relationships can be considered without concern for physical structure of data
Composed of tablesRows called recordsColumns called fieldsPrimary key: field that contains unique data
Each record can be identified by at least one distinct value
New sets made from queries called result sets
7
Tables, Rows, and ColumnsTables, Rows, and Columns
Data stored in a database are organized into tables, rows, and columnsEach table holds a collection of related data
A table is a two-dimensional container made of rows and columnsA row is a complete set of information about a single itemA column holds an individual piece of information about the item
Name Phone
Katie Allen 555-1234
Jill Ammons 555-5678
Kevin Brown 555-9012
Elisa Garcia 555-3456
Jeff Jenkins 555-7890
Each row contains data about one person
8
Column Data TypesColumn Data Types
When you create a database table, you must specify a data type for the column Acceptable data types are defined by DBMS, not C#
SQL Server Data Type
Description Corresponding C# or .NET Framework Data Type
bit True/false values bool
datetime A date and a time DateTime
decimal(t, d) A decimal value with t total digits and d digits appearing after the decimal point. decimal
float Real numbers double
int An integer number int
money Values that represent currency decimal
nchar(n) A fixed-length Unicode string with a maximum length of n characters. string
nvarchar(n) A variable-length Unicode string with a maximum length of n characters. string
9
Relational Database ModelRelational Database ModelRelational Database Model
All information is contained in tables (or relationsrelations)
TupleTuple or row (record)AttributeAttribute or column (field)Relationships [not why called relational]
10
Relation InstanceRelation InstanceThe current values (relation instance) of a relation are specified by a tableAn element t of r is a tuple, represented by a row in a table
JonesSmithCurryLindsay
customer_name
MainNorthNorthPark
customer_street
HarrisonRyeRyePittsfield
customer_city
customer
attributes(or columns)
tuples(or rows)
11
Relational Database ModelRelational Database Model
Relationships are logical representation of data:Not concerned with physical structure
PrimaryPrimary keykey: field that contains unique dataEach record can be identified by at least one distinct valuenumber name department salary location
23603 Jones 413 1100 New Jersey
24568 Kerwin 413 2000 New Jersey
34589 Larson 642 1800 Los Angeles
35761 Myers 611 1400 Orlando
47132 Neumann 413 9000 New Jersey
78321 Stephens 611 8500 Orlando
record/row
field/columnprimary key
12
Primary KeysPrimary Keys
Most database tables have a primary keyA primary key is a column that can be used to identify a specific rowThe column that is designated as the primary key must hold a unique value for each row. For example,
Employee ID, social security number, invoice number, sales order numberSometimes the data that you want to store in a table does not contain any unique items that can be used as a primary key
You need to create an identity column specifically to serve as the primary key. This column is known as an identify column.Identify columns typically contain integersEach time a new row is added to the table, the DBMS automatically assigns a unique value to the identify column
13
Primary KeysPrimary Keys
Relational DBMS enforces several constraints on dataRuleRule ofof EntityEntity IntegrityIntegrity: in most cases every record has a unique value in its primary-key fieldPrimary key does not have to be a single fieldCompoundCompound PrimaryPrimary KeyKey: when a record has a unique key based on a combination of two fields
14
Foreign KeysForeign Keys
A foreignforeign keykey is used to create relationships:Field for which every entry has a unique value in another table and where the field in the other table is the primary key for that table
RuleRule ofof ReferentialReferential IntegrityIntegrity: every foreign-key field value must appear in another table’s primary-key fieldOneOne toto manymany relationshiprelationship: A foreign key can appear many times in its own table, but only once as primary key in another table
15
Relational Database Overview: Relational Database Overview: Books DatabaseBooks Database
Rule of Entity Integrity: in most cases every record every record hasaunique value in its primary-key fieldCompound Primary key: when a record has a unique key based on a combination of two fieldsForeign key:
Field for which every entry has a unique value in another table and where the field in the other table is the primary key for that tableRule of Referential Integrity: every foreign-key field value must appear in another table’s primary-key fieldOne to many relationship: A foreign key can appear many times inits own table, but only once as primary key in another table
16
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
Field Desc rip tion authorID Author’s ID number in the database. In the Books database, this int field is
defined as an auto-incremented field. For each new record inserted in this table, the database increments the authorID value, ensuring that each record has a unique authorID. This field represents the table’s primary key.
firstName Author’s first name (a string). lastName Author’s last name (a string). Fig. 19.3 Authors tab le from Books.
17
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
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
13 Jeffrey Listfield
Fig. 19.4 Data from the Authors tab le of Books.
18
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
Field Description publisherID The publisher’s ID number in the database. This auto-incremented int
field is the table’s primary-key field.
publisherName The name of the publisher (a string). Fig. 19.5 Publishers table from Books.
publisherID publisherName 1 Prentice Hall
2 Prentice Hall PTG Fig. 19.6 Data from the Publishers tab le of Books.
Fie ld Desc rip tion authorID The author’s ID number, which allows the database to associate each
book with a specific author. The integer ID number in this field must also appear in the Authors table.
isbn The ISBN number for a book (a string). Fig. 19.7 AuthorISBN ta b le from Books.
19
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
a u t h o r I D i s b n a u t h o r I D i s b n 1 0 1 3 0 8 9 5 7 2 5 2 0 1 3 9 1 6 3 0 5 0
1 0 1 3 2 2 6 1 1 9 7 2 0 1 3 0 2 8 4 1 9 x 1 0 1 3 0 8 9 5 7 1 7 2 0 1 3 0 1 6 1 4 3 8 1 0 1 3 5 2 8 9 1 0 6 2 0 1 3 0 8 5 6 1 1 8 1 0 1 3 9 1 6 3 0 5 0 2 0 1 3 0 1 2 5 0 7 5 1 0 1 3 0 2 8 4 1 9 x 2 0 1 3 8 9 9 3 9 4 7 1 0 1 3 0 1 6 1 4 3 8 2 0 1 3 0 8 5 2 4 7 3 1 0 1 3 0 8 5 6 1 1 8 2 0 1 3 0 8 2 9 2 7 7 1 0 1 3 0 1 2 5 0 7 5 2 0 1 3 4 5 6 9 5 5 5 1 0 1 3 8 9 9 3 9 4 7 2 0 1 3 0 8 2 9 2 9 3 1 0 1 3 0 8 5 2 4 7 3 2 0 1 3 0 2 8 4 1 7 3 1 0 1 3 0 8 2 9 2 7 7 2 0 1 3 0 2 8 4 1 8 1 1 0 1 3 4 5 6 9 5 5 5 2 0 1 3 0 8 9 5 6 0 1 1 0 1 3 0 8 2 9 2 9 3 3 0 1 3 0 2 8 4 1 9 x 1 0 1 3 0 2 8 4 1 7 3 3 0 1 3 0 1 6 1 4 3 8 1 0 1 3 0 2 8 4 1 8 1 3 0 1 3 0 8 5 6 1 1 8 1 0 1 3 0 8 9 5 6 0 1 3 0 1 3 4 5 6 9 5 5 5 2 0 1 3 0 8 9 5 7 2 5 3 0 1 3 0 8 2 9 2 9 3 2 0 1 3 2 2 6 1 1 9 7 3 0 1 3 0 2 8 4 1 7 3 2 0 1 3 0 8 9 5 7 1 7 3 0 1 3 0 2 8 4 1 8 1 2 0 1 3 5 2 8 9 1 0 6 4 0 1 3 0 8 9 5 6 0 1
Fig . 1 9 .8 D a t a f ro m A u t h o r I S B N t a b le in B o o k s .
20
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
Fie ld Desc rip tion 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 int). publisherID Publisher’s ID number (an int). 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.]
Fig. 19.9 Titles ta b le from Books.
21
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
i s b n t i t l e e d i t i o n -N u m b e r
p u b l i s h - e r I D
c o p y -r i g h t
i m a g e F i l e p r i c e
0 1 3 0 9 2 3 6 1 3 P y t h o n H o w t o P r o g r a m
1 1 2 0 0 2 p y t h o n . j p g $ 6 9 . 9 5
0 1 3 0 6 2 2 2 1 4 C # H o w t o P r o g r a m
1 1 2 0 0 2 c s h t p . j p g $ 6 9 . 9 5
0 1 3 0 3 4 1 5 1 7 J a v a H o w t o P r o g r a m
4 1 2 0 0 2 j h t p 4 . j p g $ 6 9 . 9 5
0 1 3 0 6 4 9 3 4 1 T h e C o m p l e t e J a v a T r a i n i n g C o u r s e
4 2 2 0 0 2 j a v a c t c 4 . j p g $ 1 0 9 . 9 5
0 1 3 0 8 9 5 6 0 1 A d v a n c e d J a v a 2 P l a t f o r m H o w t o P r o g r a m
1 1 2 0 0 2 a d v j h t p 1 . j p g $ 6 9 . 9 5
0 1 3 0 3 0 8 9 7 8 I n t e r n e t a n d W o r l d W i d e W e b H o w t o P r o g r a m
2 1 2 0 0 2 i w 3 h t p 2 . j p g $ 6 9 . 9 5
0 1 3 0 2 9 3 6 3 6 V i s u a l B a s i c . N E T H o w t o P r o g r a m
2 1 2 0 0 2 v b n e t . j p g $ 6 9 . 9 5
0 1 3 0 8 9 5 6 3 6 T h e C o m p l e t e C + + T r a i n i n g C o u r s e
3 2 2 0 0 1 c p p c t c 3 . j p g $ 1 0 9 . 9 5
0 1 3 0 8 9 5 5 1 2 T h e C o m p l e t e e -B u s i n e s s & e -C o m m e r c e P r o g r a m m i n g T r a i n i n g C o u r s e
1 2 2 0 0 1 e b e c c t c . j p g $ 1 0 9 . 9 5
0 1 3 0 8 9 5 6 1 X T h e C o m p l e t e I n t e r n e t & W o r l d W i d e W e b P r o g r a m m i n g T r a i n i n g C o u r s e
2 2 2 0 0 1 i w 3 c t c 2 . j p g $ 1 0 9 . 9 5
0 1 3 0 8 9 5 5 4 7 T h e C o m p l e t e P e r l T r a i n i n g C o u r s e
1 2 2 0 0 1 p e r l . j p g $ 1 0 9 . 9 5
0 1 3 0 8 9 5 5 6 3 T h e C o m p l e t e X M L P r o g r a m m i n g T r a i n i n g C o u r s e
1 2 2 0 0 1 x m l c t c . j p g $ 1 0 9 . 9 5
Fig. 19.10 part 1
22
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
0 1 3 0 8 9 5 7 2 5 C H o w t o P r o g r a m 3 1 2 0 0 1 c h t p 3 . j p g $ 6 9 . 9 5
0 1 3 0 8 9 5 7 1 7 C + + H o w t o P r o g r a m
3 1 2 0 0 1 c p p h t p 3 . j p g $ 6 9 . 9 5
0 1 3 0 2 8 4 1 9 X e - B u s i n e s s a n d e -C o m m e r c e H o w t o P r o g r a m
1 1 2 0 0 1 e b e c h t p 1 . j p g $ 6 9 . 9 5
0 1 3 0 6 2 2 2 6 5 W i r e l e s s I n t e r n e t a n d M o b i l e B u s i n e s s H o w t o P r o g r a m
1 1 2 0 0 1 w i r e l e s s . j p g $ 6 9 . 9 5
0 1 3 0 2 8 4 1 8 1 P e r l H o w t o P r o g r a m
1 1 2 0 0 1 p e r l h t p 1 . j p g $ 6 9 . 9 5
0 1 3 0 2 8 4 1 7 3 X M L H o w t o P r o g r a m
1 1 2 0 0 1 x m l h t p 1 . j p g $ 6 9 . 9 5
0 1 3 0 8 5 6 1 1 8 T h e C o m p l e t e I n t e r n e t a n d W o r l d W i d e W e b P r o g r a m m i n g T r a i n i n g C o u r s e
1 2 2 0 0 0 i w 3 c t c 1 . j p g $ 1 0 9 . 9 5
0 1 3 0 1 2 5 0 7 5 J a v a H o w t o P r o g r a m ( J a v a 2 )
3 1 2 0 0 0 j h t p 3 . j p g $ 6 9 . 9 5
0 1 3 0 8 5 2 4 8 1 T h e C o m p l e t e J a v a 2 T r a i n i n g C o u r s e
3 2 2 0 0 0 j a v a c t c 3 . j p g $ 1 0 9 . 9 5
0 1 3 0 3 2 3 6 4 0 e - B u s i n e s s a n d e -C o m m e r c e f o r M a n a g e r s
1 1 2 0 0 0 e b e c m . j p g $ 6 9 . 9 5
0 1 3 0 1 6 1 4 3 8 I n t e r n e t a n d W o r l d W i d e W e b H o w t o P r o g r a m
1 1 2 0 0 0 i w 3 h t p 1 . j p g $ 6 9 . 9 5
0 1 3 0 1 3 2 4 9 7 G e t t i n g S t a r t e d w i t h V i s u a l C + + 6 w i t h a n I n t r o d u c t i o n t o M F C
1 1 1 9 9 9 g s v c . j p g $ 4 9 . 9 5
Fig. 19.10 part 2
23
Relational Database Overview: Relational Database Overview: BooksBooks DatabaseDatabase
0 1 3 0 8 2 9 2 9 3 T h e C o m p le t e V is u a l B a s ic 6 T ra in in g C o u rs e
1 2 1 9 9 9 v b c t c 1 . j p g $ 1 0 9 .9 5
0 1 3 4 5 6 9 5 5 5 V is u a l B a s ic 6 H o w to P r o g r a m
1 1 1 9 9 9 v b h t p 1 . j p g $ 6 9 .9 5
0 1 3 2 7 1 9 7 4 6 J a v a M u lt im e d ia C yb er C la s s r o o m
1 2 1 9 9 8 j a v a c t c . j p g $ 1 0 9 .9 5
0 1 3 6 3 2 5 8 9 0 J a v a H o w to P ro g ra m
1 1 1 9 9 8 j h t p 1 . j p g $ 6 9 .9 5
0 1 3 9 1 6 3 0 5 0 T h e C o m p le t e C + + T ra in in g C o u rs e
2 2 1 9 9 8 c p p c t c 2 . j p g $ 1 0 9 .9 5
0 1 3 5 2 8 9 1 0 6 C + + H o w t o P ro g ra m
2 1 1 9 9 8 c p p h t p 2 . j p g $ 4 9 .9 5
0 1 3 7 9 0 5 6 9 6 T h e C o m p le t e J a v a T ra in in g C o u rs e
2 2 1 9 9 8 j a v a c t c 2 . j p g $ 1 0 9 .9 5
0 1 3 0 8 2 9 2 7 7 T h e C o m p le t e J a v a T ra in in g C o u rs e (J a v a 1 .1 )
2 2 1 9 9 8 j a v a c t c 2 . j p g $ 9 9 .9 5
0 1 3 8 9 9 3 9 4 7 J a v a H o w to P ro g ra m (J a v a 1 .1 )
2 1 1 9 9 8 j h t p 2 . j p g $ 4 9 .9 5
0 1 3 1 1 7 3 3 4 0 C + + H o w t o P ro g ra m
1 1 1 9 9 4 c p p h t p 1 . j p g $ 6 9 .9 5
0 1 3 2 2 6 1 1 9 7 C H o w t o P r o g ra m 2 1 1 9 9 4 c h t p 2 . j p g $ 4 9 .9 5
0 1 3 1 1 8 0 4 3 6 C H o w t o P r o g ra m 1 1 1 9 9 2 c h t p . j p g $ 6 9 .9 5
Fig . 1 9 .1 0 D a t a f ro m t h e T i t l e s t a b le o f B o o k s .
Fig. 19.10 part 3
24
The Books DatabaseThe Books Database
Entity-relationship diagram for the Book database
AuthorISBN
authorID
isbn
Authors
authorID
firstName
lastName
Publishers
publisherID
publisherName
Titles
isbn
titleeditionNumber
copyright
publisherIDimageFile
price
1
¥
¥
¥11
25
Relational Database QueriesRelational Database QueriesRelational Database Queries
QueriesQueries : return a new result setBased upon relationalrelational algebraalgebra
Uses 3 operations to build a new relation (a result-set)ProjectionProjection, selectionselection, and joinjoin
26
What is SQL?What is SQL?
Structured Query Language (SQLSQL)Keywords and rules used to implement relational algebra operations
27
SQL KeywordsSQL Keywords
SQL keyword Description SELECT Selects (retrieves) fields from one or more tables.
FROM Specifies tables from which to get fields or delete records. Required in every SELECT and DELETE statement.
WHERE Specifies criteria that determine the rows to be retrieved. INNER JOIN Joins records from multiple tables to produce a single set of records. GROUP BY Specifies criteria for grouping records. ORDER BY Specifies criteria for ordering records. INSERT Inserts data into a specified table. UPDATE Updates data in a specified table. DELETE Deletes data from a specified table.
Fig. 19.12 SQL query keywords.
28
Basic Select QueryBasic Select Query
Extracts information from one or more tables in a databaseFormat:
Basic: select * from tableselect * from table--namenameExample: select * from authorsselect * from authors* means "extract all columns"
To get a projection (specific columns), use a list:select select firstnamefirstname, , lastnamelastname from authorsfrom authorsColumn, table names may be case sensitiveSQL keywords should not be case sensitive
29
Basic Basic SelectSelect QueryQuery
authorID lastName authorID lastName 1 Deitel 8 M cPhie
2 Deitel 9 Yaeger
3 Nieto 10 Zlatkina
4 Steinbuhler 12 W iederm ann
5 Santry 12 Liperi
6 Lin 13 Listfield 7 Sadhu
Fig . 19.13 authorID a n d lastName fro m th e Authors ta b le .
30
WhereWhere ClauseClause
Used to specify certain criteria in a queryBasic form:
SELECT * FROM tableName WHERE criteriaExample:
SELECT * FROM Titles WHERE copyright > 1999Can use LIKE clause
Used for pattern matchingUses wildcards
*: zero or more characters take its place?: exactly one character takes its place
31
PatternPattern--matching with Wherematching with Where
Can use likelike clause for string pattern matchingUses wildcards
%: zero or more characters take its place (*)_: exactly one character takes its place (?)
Examples:Select * from authors where Select * from authors where firstnamefirstname like '_e%'like '_e%'Select * from authors where Select * from authors where lastnamelastname like 'D%'like 'D%'
32
WHEREWHERE ClauseClauseT i t l e e d i t i o n N u m b e r c o p y r i g h t
I n t e r n e t a n d W o r l d W i d e W e b H o w to P r o g r a m 2 2 0 0 2
J a v a H o w t o P r o g r a m 4 2 0 0 2
T h e C o m p l e t e J a v a T r a i n i n g C o u r s e 4 2 0 0 2
T h e C o m p l e t e e - B u s i n e s s & e - C o m m e r c e P r o g r a m m i n g T r a i n i n g C o u r s e
1 2 0 0 1
T h e C o m p l e t e I n t e r n e t & W o r l d W i d e W e b P r o g r a m m i n g T r a i n in g C o u r s e
2 2 0 0 1
T h e C o m p l e t e P e r l T r a i n in g C o u r s e 1 2 0 0 1 T h e C o m p l e t e X M L P r o g r a m m i n g T r a i n i n g C o u r s e 1 2 0 0 1
C H o w t o P r o g r a m 3 2 0 0 1
C + + H o w to P r o g r a m 3 2 0 0 1
T h e C o m p l e t e C + + T r a i n i n g C o u r s e 3 2 0 0 1
e - B u s i n e s s a n d e - C o m m e r c e H o w t o P r o g r a m 1 2 0 0 1 I n t e r n e t a n d W o r l d W i d e W e b H o w to P r o g r a m 1 2 0 0 0
T h e C o m p l e t e I n t e r n e t a n d W o r l d W i d e W e b P r o g r a m m i n g T r a i n in g C o u r s e
1 2 0 0 0
J a v a H o w t o P r o g r a m ( J a v a 2 ) 3 2 0 0 0
T h e C o m p l e t e J a v a 2 T r a in i n g C o u r s e 3 2 0 0 0
X M L H o w t o P r o g r a m 1 2 0 0 1
P e r l H o w t o P r o g r a m 1 2 0 0 1
A d v a n c e d J a v a 2 P l a t f o r m H o w t o P r o g r a m 1 2 0 0 2 e - B u s i n e s s a n d e - C o m m e r c e f o r M a n a g e r s 1 2 0 0 0
W i r e l e s s I n t e r n e t a n d M o b i l e B u s i n e s s H o w t o P r o g r a m 1 2 0 0 1
C # H o w T o P r o g r a m 1 2 0 0 2
P y t h o n H o w t o P r o g r a m 1 2 0 0 2 V i s u a l B a s i c . N E T H o w to P r o g r a m 2 2 0 0 2
Fig . 1 9 . 1 4 Tit le s w i t h c o p y r ig h t s a f t e r 1 9 9 9 f r o m t a b le T i t l e s .
33
WHEREWHERE ClauseClause
authorID firstName lastName 1 Harvey Deitel
2 Paul Deitel
Fig. 19.15 Authors from the Authors table whose last names start with D.
authorID firstName lastName 3 Tem Nieto
6 Ted Lin
11 Ben Wiedermann
12 Jonathan Liperi
13 Jeffrey Listfield Fig. 19.16 Authors from table Authors whose last names contain i as the
second letter.
34
Sorting the ResultsSorting the Results
SQL queries return their data in indeterminate orderSame query may return rows in different order each time
Use the order by clause to arrange results of a queryCan be ascending (asc) or descending (desc) order
Examples:select * from authors order by select * from authors order by lastnamelastname descdescselect * from authors select * from authors
order by order by lastnamelastname, , firstnamefirstname ascasc
35
ORDER BYORDER BY ClauseClause
Used to arrange results of a queryCan be ascending or descending order
Uses ASC and DESC respectively
Example:SELECT authorID FROM Authors ORDER BY authorID ASC
Can be used to sort by multiple fields
36
ORDER BYORDER BY ClauseClauseauthorID firstName lastName 2 Paul Deitel
1 Harvey Deitel
6 Ted Lin
12 Jonathan Liperi
13 Jeffrey Listfield
8 David McPhie 3 Tem Nieto
7 Praveen Sadhu
5 Sean Santry
4 Kate Steinbuhler 11 Ben Wiedermann
9 Cheryl Yaeger
10 Marina Zlatkina Fig. 19.17 Authors from table Authors in ascending order by lastName.
37
ORDER BYORDER BY ClauseClauseauthorID firstName lastName 10 Marina Zlatkina
9 Cheryl Yaeger
11 Ben Wiedermann
4 Kate Steinbuhler
5 Sean Santry
7 Praveen Sadhu 3 Tem Nieto
8 David McPhie
13 Jeffrey Listfield
12 Jonathan Liperi
6 Ted Lin 2 Paul Deitel
1 Harvey Deitel Fig. 19.18 Authors from table Authors in descending order by lastName.
38
ORDER BYORDER BY ClauseClauseauthorID firstName lastName 1 Harvey Deitel
2 Paul Deitel
6 Ted Lin
12 Jonathan Liperi
13 Jeffrey Listfield
8 David McPhie 3 Tem Nieto
7 Praveen Sadhu
5 Sean Santry
4 Kate Steinbuhler
11 Ben Wiedermann 9 Cheryl Yaeger
10 Marina Zlatkina Fig. 19.19 Authors from table Authors in ascending order by lastName and
by firstName.
39
ORDER BYORDER BY ClauseClausei s b n t i t l e e d i t i o n -
N u m b e r c o p y -r i g h t
p r i c e
0 1 3 0 8 9 5 6 0 1 A d v a n c e d J a v a 2 P l a t f o r m H o w t o P r o g r a m
1 2 0 0 2 $ 6 9 . 9 5
0 1 3 1 1 8 0 4 3 6 C H o w t o P r o g r a m 1 1 9 9 2 $ 6 9 . 9 5
0 1 3 0 8 9 5 7 2 5 C H o w t o P r o g r a m 3 2 0 0 1 $ 6 9 . 9 5
0 1 3 2 2 6 1 1 9 7 C H o w t o P r o g r a m 2 1 9 9 4 $ 4 9 . 9 5
0 1 3 0 6 2 2 2 1 4 C # H o w T o P r o g r a m 1 2 0 0 2 $ 6 9 . 9 5
0 1 3 5 2 8 9 1 0 6 C + + H o w t o P r o g r a m 2 1 9 9 8 $ 4 9 . 9 5 0 1 3 1 1 7 3 3 4 0 C + + H o w t o P r o g r a m 1 1 9 9 4 $ 6 9 . 9 5
0 1 3 0 8 9 5 7 1 7 C + + H o w t o P r o g r a m 3 2 0 0 1 $ 6 9 . 9 5
0 1 3 0 2 8 4 1 9 X e - B u s i n e s s a n d e - C o m m e r c e H o w t o P r o g r a m
1 2 0 0 1 $ 6 9 . 9 5
0 1 3 0 3 0 8 9 7 8 I n t e r n e t a n d W o r l d W i d e W e b H o w t o P r o g r a m
2 2 0 0 2 $ 6 9 . 9 5
0 1 3 0 1 6 1 4 3 8 I n t e r n e t a n d W o r l d W i d e W e b H o w t o P r o g r a m
1 2 0 0 0 $ 6 9 . 9 5
0 1 3 0 3 4 1 5 1 7 J a v a H o w t o P r o g r a m 4 2 0 0 2 $ 6 9 . 9 5
0 1 3 6 3 2 5 8 9 0 J a v a H o w t o P r o g r a m 1 1 9 9 8 $ 4 9 . 9 5 0 1 3 0 2 8 4 1 8 1 P e r l H o w t o P r o g r a m 1 2 0 0 1 $ 6 9 . 9 5
0 1 3 0 9 2 3 6 1 3 P y t h o n H o w t o P r o g r a m 1 2 0 0 2 $ 6 9 . 9 5
0 1 3 0 2 9 3 6 3 6 V i s u a l B a s i c . N E T H o w t o P r o g r a m
2 2 0 0 2 $ 6 9 . 9 5
0 1 3 4 5 6 9 5 5 5 V i s u a l B a s i c 6 H o w t o P r o g r a m
1 1 9 9 9 $ 6 9 . 9 5
0 1 3 0 6 2 2 2 6 5 W i r e l e s s I n t e r n e t a n d M o b i l e B u s i n e s s H o w t o P r o g r a m
1 2 0 0 1 $ 6 9 . 9 5
0 1 3 0 2 8 4 1 7 3 X M L H o w t o P r o g r a m 1 2 0 0 1 $ 6 9 . 9 5
F ig . 1 9 . 2 0 B o o k s f r o m t a b le T i t l e s w h o se t i t le s e n d w it h H o w t o P r o g r a m i n a sc e n d in g o rd e r b y t i t l e .
40
Merging Data from Multiple Tables: Merging Data from Multiple Tables: INNER JOININNER JOIN
Merges records from multiple tables into a single recordTests for matching values in a common field
General Form:select * from table1 select * from table1 inner join table 2 on table1.field = table2.fieldinner join table 2 on table1.field = table2.field
Example:select select firstnamefirstname, , isbnisbn from from authors inner join authors inner join authorISBNauthorISBNon on authors.authorIDauthors.authorID = = authorsISBN.authorIDauthorsISBN.authorID
41
Merging Data from Multiple Tables: Merging Data from Multiple Tables: INNER JOININNER JOIN
fir s t N a m e l a s t N a m e i s b n f i r s t N a m e l a s t N a m e i s b n H a r v e y D e i te l 0 1 3 0 8 9 5 6 0 1 H a r v e y D e i te l 0 1 3 0 8 2 9 2 9 3
H a r v e y D e i te l 0 1 3 0 2 8 4 1 8 1 H a r v e y D e i te l 0 1 3 4 5 6 9 5 5 5
H a r v e y D e i te l 0 1 3 0 2 8 4 1 7 3 H a r v e y D e i te l 0 1 3 0 8 2 9 2 7 7
H a r v e y D e i te l 0 1 3 0 8 5 2 4 7 3 P a u l D e i te l 0 1 3 0 1 2 5 0 7 5
H a r v e y D e i te l 0 1 3 8 9 9 3 9 4 7 P a u l D e i te l 0 1 3 0 8 5 6 1 1 8
H a r v e y D e i te l 0 1 3 0 8 5 6 1 1 8 P a u l D e i te l 0 1 3 0 1 6 1 4 3 8 H a r v e y D e i te l 0 1 3 0 1 6 1 4 3 8 P a u l D e i te l 0 1 3 0 2 8 4 1 9 x
H a r v e y D e i te l 0 1 3 0 2 8 4 1 9 x P a u l D e i te l 0 1 3 9 1 6 3 0 5 0
H a r v e y D e i te l 0 1 3 9 1 6 3 0 5 0 P a u l D e i te l 0 1 3 0 8 9 5 6 0 1
H a r v e y D e i te l 0 1 3 5 2 8 9 1 0 6 P a u l D e i te l 0 1 3 5 2 8 9 1 0 6 H a r v e y D e i te l 0 1 3 0 8 9 5 7 1 7 P a u l D e i te l 0 1 3 0 8 9 5 7 1 7
H a r v e y D e i te l 0 1 3 2 2 6 1 1 9 7 P a u l D e i te l 0 1 3 2 2 6 1 1 9 7
H a r v e y D e i te l 0 1 3 0 8 9 5 7 2 5 P a u l D e i te l 0 1 3 0 8 9 5 7 2 5
H a r v e y D e i te l 0 1 3 0 1 2 5 0 7 5 T e m N ie to 0 1 3 0 2 8 4 1 8 1
P a u l D e i te l 0 1 3 0 2 8 4 1 8 1 T e m N ie to 0 1 3 0 2 8 4 1 7 3 P a u l D e i te l 0 1 3 0 2 8 4 1 7 3 T e m N ie to 0 1 3 0 8 2 9 2 9 3
P a u l D e i te l 0 1 3 0 8 2 9 2 9 3 T e m N ie to 0 1 3 4 5 6 9 5 5 5
P a u l D e i te l 0 1 3 4 5 6 9 5 5 5 T e m N ie to 0 1 3 0 8 5 6 1 1 8
P a u l D e i te l 0 1 3 0 8 2 9 2 7 7 T e m N ie to 0 1 3 0 1 6 1 4 3 8
P a u l D e i te l 0 1 3 0 8 5 2 4 7 3 T e m N ie to 0 1 3 0 2 8 4 1 9 x P a u l D e i te l 0 1 3 8 9 9 3 9 4 7
Fig . 1 9 .2 1 A u t h o rs f ro m t a b le A u t h o r s a n d ISBN n u m b e rs o f t h e a u t h o rs’ b o o k s, so rt e d in a sc e n d in g o rd e r b y l a s t N a m e a n d f i r s t N a m e .
42
Joining Data from Tables Authors, Joining Data from Tables Authors, AuthorISBNAuthorISBN, Titles and Publishers, Titles and Publishers
Tables produced by INNER JOIN can be used as arguments for another INNER JOIN
43
Joining Data from Tables Joining Data from Tables AuthorsAuthors, , AuthorISBNAuthorISBN, , TitlesTitles and and PublishersPublishers
1 SELECT Titles.title, Titles.isbn, Authors.firstName, 2 Authors.lastName, Titles.copyright, 3 Publishers.publisherName4 FROM5 ( Publishers INNER JOIN Titles 6 ON Publishers.publisherID = Titles.publisherID ) 7 INNER JOIN8 ( Authors INNER JOIN AuthorISBN 9 ON Authors.authorID = AuthorISBN.authorID ) 10 ON Titles.isbn = AuthorISBN.isbn11 ORDER BY Titles.title
Fig. 19.22 TitleAuthor query of Books database.
Join Publishers and Titles tables if the publisherID matches
Join Authors and AuthorISBN if authorID matches
Join two created tables if titlesISBN matches authorsISBN
Sort new table by title
44
Joining Data from Tables Joining Data from Tables AuthorsAuthors, , AuthorISBNAuthorISBN, , TitlesTitles and and PublishersPublishers
Title i s b n f i r s t -N a m e
l a s t -N a m e
c o p y -r i g h t
p u b l i s h e r -N a m e
A d v a n c e d Ja v a 2 P la tfo rm H o w to P ro g ra m
0 1 3 0 8 9 5 6 0 1 P a u l D e ite l 2 0 0 2 P re n tic e H a ll
A d v a n c e d Ja v a 2 P la tfo rm H o w to P ro g ra m
0 1 3 0 8 9 5 6 0 1 H a rv e y D e ite l 2 0 0 2 P re n tic e H a ll
A d v a n c e d Ja v a 2 P la tfo rm H o w to P ro g ra m
0 1 3 0 8 9 5 6 0 1 S e a n S a n tr y 2 0 0 2 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 1 1 8 0 4 3 6 H a rv e y D e ite l 1 9 9 2 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 1 1 8 0 4 3 6 P a u l D e ite l 1 9 9 2 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 2 2 6 1 1 9 7 H a rv e y D e ite l 1 9 9 4 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 2 2 6 1 1 9 7 P a u l D e ite l 1 9 9 4 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 0 8 9 5 7 2 5 H a rv e y D e ite l 2 0 0 1 P re n tic e H a ll
C H o w to P ro g ra m 0 1 3 0 8 9 5 7 2 5 P a u l D e ite l 2 0 0 1 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 T e m N ie to 2 0 0 2 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 P a u l D e ite l 2 0 0 2 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 J e ffre y L is t fie ld 2 0 0 2 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 C h e r yl Y a e g e r 2 0 0 2 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 M a r in a Z la tk in a 2 0 0 2 P re n tic e H a ll
C # H o w T o P ro g ra m 0 1 3 0 6 2 2 2 1 4 H a rv e y D e ite l 2 0 0 2 P re n tic e H a ll
C + + H o w to P ro g ra m 0 1 3 0 8 9 5 7 1 7 P a u l D e ite l 2 0 0 1 P re n tic e H a ll
Fig. 19.23 part 1
45
Joining Data from Tables Joining Data from Tables AuthorsAuthors, , AuthorISBNAuthorISBN, , TitlesTitles and and PublishersPublishers
C++ How to Program 0130895717 Harvey Deitel 2001 Prentice Hall
C++ How to Program 0131173340 Paul Deitel 1994 Prentice Hall
C++ How to Program 0131173340 Harvey Deitel 1994 Prentice Hall
C++ How to Program 0135289106 Harvey Deitel 1998 Prentice Hall
C++ How to Program 0135289106 Paul Deitel 1998 Prentice Hall
e-Business and e-Commerce for Managers
0130323640 Harvey Deitel 2000 Prentice Hall
e-Business and e-Commerce for Managers
0130323640 Kate Steinbuhler
2000 Prentice Hall
e-Business and e-Commerce for Managers
0130323640 Paul Deitel 2000 Prentice Hall
e-Business and e-Commerce How to Program
013028419X Harvey Deitel 2001 Prentice Hall
e-Business and e-Commerce How to Program
013028419X Paul Deitel 2001 Prentice Hall
e-Business and e-Commerce How to Program
013028419X Tem Nieto 2001 Prentice Hall
Fig . 19.23 Po rt io n o f the re su lt se t p ro d uc e d b y the q ue ry in Fig . 19.22.
Fig. 19.23 part 2
46
The Selection OperationThe Selection Operation
The selection operation is accomplished using wherewhereBasic form:
select * from tableselect * from table--name where criterianame where criteriaExample:
select * from titles where copyright > 1999select * from titles where copyright > 1999Make sure String criteria are inside single quotesCan use expected relational operators
select * from authors where select * from authors where firstnamefirstname > 'B'> 'B'
47
SQL and Relational Operators with the Where ClauseSQL and Relational Operators with the Where Clause
SQL supports several relational operators for writing conditional expressions
Operator Meaning
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
= Equal to
<> Not equal to
48
SQL String FunctionsSQL String Functions
SQL keywords and clauses are not case-sensitive. But, string comparison are.‘Denim Jeans’, ‘denim jeans’, and ‘Denim jeans’ are considered three different string literals The following three SQL statements will generate three different results:
SELECT * FROM Product WHERE Description = ‘Denim Jeans’SELECT * FROM Product WHERE Description = ‘denim jeans’SELECT * FROM Product WHERE Description = ‘Denim jeans’
You can use the Lower() or Upper() string function before performing the comparison
SELECT * FROM Product WHERE Lower(Description) = ‘denim jeans’SELECT * FROM Product WHERE Upper(Description) = ‘DENIM JEANS’
49
Using the LIKE OperatorUsing the LIKE Operator
The LIKE operator allows you to do a search based on a pattern rather than specifying exactly what is desired
“Oxford Cloth Shirt” and “Poplin Shirt” both contains the string “Shirt”Use the string “Shirt” as the pattern with the wildcard character %% represents any sequence of zero or more characters
SELECT * FROM Product WHERE Description LIKE ‘%Shirt%’SELECT * FROM Product WHERE Description LIKE ‘Chino%’SELECT * FROM Product WHERE Description LIKE ‘%jeans’
The underscore (_) wildcard character represents a single character. To search for all rows in which Product_Number begins with “2”, followed by any one character, followed by “-0”, followed by any one character, use
SELECT * FROM Product WHERE Product_Number LIKE ‘2_-0_’
50
Sorting the Results of a Select QuerySorting the Results of a Select Query
To sort the results of a SELECT query, use the ORDER BY clauseSELECT * FROM Product ORDER BY PriceSELECT * FROM Product WHERE Price > 20.00 ORDER BY Price
The results will be sorted in ascending orderTo sort in descending order, use the Desc operator
SELECT * FROM Product ORDER BY Price DescSELECT * FROM Product WHERE Price > 20.00 ORDER BY Price Desc
51
Insert StatementInsert Statement
Inserts a new record into a tableGeneral form:
insert into tableinsert into table--name(fieldname1, fieldname2,...) name(fieldname1, fieldname2,...) values(value1, value2)values(value1, value2)
Example:insert into insert into authors(authorIDauthors(authorID, , lastnamelastname, , firstnamefirstname))values(15, 'Gilbert', 'Steve')values(15, 'Gilbert', 'Steve')
Values must match field names in order and type
52
INSERTINSERT StatementStatementauthorID 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
13 Jeffrey Listfield
14 Sue Smith Fig. 19.24 Authors a fte r a n INSERT op era tion to a d d a rec ord .
53
The Update StatementThe Update Statement
Modifies data in a tableGeneral Form:
update tableupdate table--name set field1=value1 name set field1=value1 where criteriawhere criteria
Example:update authors set update authors set firstnamefirstname='Stephen' ='Stephen' where where lastnamelastname='Gilbert' and ='Gilbert' and firstnamefirstname='Steve'='Steve'
What happens if you violate an integrity rule?
54
UPDATEUPDATE StatementStatementauthorID 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
13 Jeffrey Listfield
14 Sue Jones Fig. 19.25 Ta b le Authors a fte r a n UPDATE op era tion to c ha nge a rec ord .
55
The Delete StatementThe Delete Statement
Removes data from a tableGeneral Form:
delete from tabledelete from table--name where criterianame where criteriaDon't forget the criteria!!!
Example:delete from authors where delete from authors where lastnamelastname='Gilbert'='Gilbert'
56
DELETEDELETE StatementStatementauthorID 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
13 Jeffrey Listfield Fig. 19.26 Table Authors after a DELETE operation to remove a record .
57
SQL Math FunctionsSQL Math Functions
SQL provides several functions for performing calculationsAvg(Column): calculates the average value in a particular column
SELECT Avg(Price) FROM Product
Sum(Column): calculates the sum of a column’s valuesSELECT Sum(Price) FROM Product
Min(Column): finds the minimum value of a column.SELECT Min(Price) FROM Product
Max(Column): finds the maximum value of a columnSELECT Max(Price) FROM Product
Count(Column): returns the number of values of the specified columnSELECT Count(Price) FROM Product
To determine the number of rows in a table, use:SELECT Count(*) FROM Product
58
Creating a Database in Visual StudioCreating a Database in Visual Studio
A .NET application uses several components, arranged in layers, to connect to a database
Data Source – a source of data with which the application can workTable Adapter – connects to a data source and retrieves data from a table in a data sourceDataset – gets a copy of a table from the table adapter and keeps the copy of the table in memoryBinding Source – a component that can connect user interface controls directly to a dataset
Application
Binding Source
Dataset
Table Adapter
Data Source
59
Creating A ServerCreating A Server--Based DatabaseBased Database
Visual Studio provides wizards that make it easy to create and configure the database
The Add New Item window provides a Service-based Database option for creating an empty SQL Server databaseThe default name of the SQL Server database is Database1.mdfThis book uses SQL Server database as examples
60
The Database File’s LocationThe Database File’s Location
When you use Visual Studio to create a SQL server database, the database file will be created in the project’s folder
The “project’s folder” is where the Form1.cs, Form1.Designer.cs, and Program.cs files are storedThe file extension is .mdf. For example, Phonelist.mdf.The server will also create a file that ends with the .LDF extension. For example, Phonelist_log.LDF.
This is a transaction log file used to keep a log of all the operations that you perform on the database