.NET Web Forms - PowerPoint PPT Presentation

Net web forms l.jpg
Download
1 / 64

  • 290 Views
  • Updated On :
  • Presentation posted in: Pets / Animals

.NET Web Forms. Database Queries © 2002 by Jerry Post. Why do we Need Queries. Natural languages (English) are too vague With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

.NET Web Forms

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Net web forms l.jpg

.NET Web Forms

Database Queries

© 2002 by Jerry Post


Why do we need queries l.jpg

Why do we Need Queries

  • Natural languages (English) are too vague

    • With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct.

    • Consider the question: Who are our best customers?

  • We need a query system with more structure

  • We need a standardized system so users and developers can learn one method that works on any (most) systems.

    • Query By Example (QBE)

    • SQL


Four questions to create a query l.jpg

Four Questions to Create a Query

  • What output do you want to see?

  • What do you already know (or what constraints are given)?

  • What tables are involved?

  • How are the tables joined together?


Tables l.jpg

Tables


Organization l.jpg

Organization

  • Single table

  • Constraints

  • Computations

  • Groups/Subtotals

  • Multiple Tables


Sample questions l.jpg

List all animals with yellow in their color.

List all dogs with yellow in their color born after 6/1/01.

List all merchandise for cats with a list price greater than $10.

List all dogs who are male and registered or who were born before 6/1/01 and have white in their color.

What is the average sale price of all animals?

What is the total cost we paid for all animals?

List the top 10 customers and total amount they spent.

How many cats are in the animal list?

Count the number of animals in each category.

List the CustomerID of everyone who bought something between 4/1/01 and 5/31/01.

List the first name and phone of every customer who bought something between 4/1/01 and 5/31/01.

List the last name and phone of anyone who bought a registered white cat between 6/1/01 and 12/31/01.

Which employee has sold the most items?

Sample Questions


Query by example sql l.jpg

List all animals with yellow in their color.

Query04_01

Query By Example & SQL

What tables?

What

conditions?

SELECTAnimalID, Category, Breed, Color

FROMAnimal

WHERE(Color LIKE “*yellow*”);

What to see?


Basic sql select l.jpg

Basic SQL SELECT

SELECTcolumnsWhat do you want to see?

FROMtablesWhat tables are involved?

JOINconditionsHow are the tables joined?

WHEREcriteriaWhat are the constraints?


Order by l.jpg

ORDER BY

SELECTcolumns

FROMtables

JOINjoin columns

WHEREconditions

ORDER BYcolumns (ASC DESC)

NameCategoryBreed

CathyBirdAfrican Grey

BirdCanary

DebbieBirdCockatiel

BirdCockatiel

TerryBirdLovebird

BirdOther

CharlesBirdParakeet

CurtisBirdParakeet

RubyBirdParakeet

SandyBirdParrot

HoytBirdParrot

BirdParrot

SELECT Name, Category, Breed

FROM Animal

ORDER BY Category, Breed;


Distinct l.jpg

DISTINCT

SELECT Category

FROM Animal;

SELECT DISTINCT Category

FROM Animal;

Category

Fish

Dog

Fish

Cat

Cat

Dog

Fish

Dog

Dog

Dog

Fish

Cat

Dog

. . .

Category

Bird

Cat

Dog

Fish

Mammal

Reptile

Spider


Constraints and l.jpg

List all dogs with yellow in their color born after 6/1/01.

Query04_02

Constraints: And

SELECTAnimalID, Category, DateBorn

FROMAnimal

WHERE((Category="Dog") AND (Color Like "*Yellow*")

AND (DateBorn>#6/1/2001#));


Conditions and or l.jpg

List all dogs who are male and registered or who were born before 6/1/2001 and have white in their color.

Query04_03

Conditions: AND, OR

SELECT AnimalID, Category, Gender, Registered, DateBorn, Color

FROM Animal

WHERE (( Category="Dog") AND

( ( (Gender="Male") AND (Registered Is Not Null) ) OR

( (DateBorn<#6/1/2001#) AND (Color Like "*White*") ) ) );


Useful where conditions l.jpg

Useful Where Conditions


Oracle views l.jpg

Oracle Views


Oracle views and sql l.jpg

Oracle Views and SQL

CREATE VIEW Pets.Example AS

SELECTPets.Animal.AnimalID,

Pets.Animal.Breed,

Pets.Animal.Category,

Pets.Animal.Color

FROM Pets.Animal

WHERE (Pets.Animal.Color LIKE ‘%Yellow%’)

SQL version is created by the Oracle View Wizard.

The CREATE VIEW command saves it with the specified name.


Oracle view wizard l.jpg

Oracle View Wizard


Oracle schema manager views l.jpg

Oracle Schema Manager: Views


Oracle content viewer l.jpg

Oracle Content Viewer


Sql server views l.jpg

SQL Server Views


Simple computations l.jpg

Simple Computations

OrderItem(OrderID, ItemID, Price, Quantity)

Select OrderID, ItemID, Price, Quantity, Price*Quantity As Extended

From OrderItem;

OrderIDItemIDPriceQuantityExtended

151976419.50239.00

1517653 8.35325.05

1518673 6.89213.78

Basic computations (+ - * /) can be performed on numeric data.

The new display column should be given a meaningful name.


Computations aggregation avg l.jpg

What is the average sale price of all animals?

Sum

Avg

Min

Max

Count

StDev

Var

Query04_04

Computations: Aggregation--Avg

SELECT Avg(SalePrice) AS AvgOfSalePrice

FROM SaleAnimal;


Computations math operators l.jpg

What is the total value of the order for PONumber 22?

Use any common math operators on numeric data.

Operate on data in one row at a time.

OrderTotal

1798.28

Query04_05

Computations (Math Operators)

SELECT Sum([Quantity]*[Cost]) AS OrderTotal

FROM OrderItem

WHERE (PONumber=22);


Sql differences l.jpg

SQL Differences


Subtotals where l.jpg

How many cats are in the Animal list?

Query04_06

Subtotals (Where)

SELECTCount(AnimalID) AS CountOfAnimalID

FROMAnimal

WHERE(Category = “Cat”);


Groups and subtotals l.jpg

Count the number of animals in each category.

You could type in each WHERE clause, but that is slow.

And you would have to know all of the Category values.

CategoryCountOfAnimalID

Dog100

Cat47

Bird15

Fish14

Reptile6

Mammal6

Spider3

Query04_07

Groups and Subtotals

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

GROUP BYCategory

ORDER BYCount(AnimalID) DESC;


Conditions on totals having l.jpg

Count number of Animals in each Category, but only list them if more than 10.

CategoryCountOfAnimal

Dog100

Cat47

Bird15

Fish14

Query04_08

Conditions on Totals (Having)

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

GROUP BYCategory

HAVINGCount(AnimalID) > 10

ORDER BYCount(AnimalID) DESC;


Where detail v having group l.jpg

Count Animals born after 6/1/2001 in each Category, but only list Category if more than 10.

CategoryCountOfAnimalID

Dog30

Cat18

Query04_09

Where (Detail) v Having (Group)

SELECTCategory, Count(AnimalID) AS CountOfAnimalID

FROMAnimal

WHEREDateBorn > #6/1/2001#

GROUP BYCategory

HAVINGCount(AnimalID) > 10

ORDER BYCount(AnimalID) DESC;


Multiple tables intro distinct l.jpg

List the CustomerID of everyone who bought something between 4/1/01 and 5/31/01.

CustomerID

6

8

14

19

22

24

28

36

37

38

39

42

50

57

58

63

74

80

90

Query04_10

Multiple Tables (Intro & Distinct)

Avoid Duplicates

with DISTINCT

SELECT DISTINCT CustomerID

FROM Sale

WHERE (SaleDate Between #4/1/01# And #5/31/01#)

ORDER BY CustomerID;


Joining tables l.jpg

List LastNames of Customers who bought between 4/1/2001 and 5/31/2001.

CustomerIDLastName

22Adkins

57Carter

38Franklin

42Froedge

63Grimes

74Hinton

36Holland

6Hopkins

50Lee

58McCain

37McPherson

90Nichols

14Patterson

8Reid

28Samuels

80Vance

39Williams

24Young

19Zhang

Query04_11

Joining Tables

SELECT DISTINCT Sale.CustomerID, Customer.LastName

FROM Customer

INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID

WHERE (Sale.SaleDate Between #4/1/2001# And #5/31/2001#)

ORDER BY Customer.LastName;


Sql join l.jpg

SQL JOIN

FROM table1

INNER JOIN table2

ON table1.column = table2.column

SQL 92 syntax (Access and SQL Server)

FROM table1, table2

WHERE table1.column = table2.column

SQL 89 syntax (Oracle)

FROM table1, table2

JOIN table1.column = table2.column

Informal syntax


Syntax for three tables l.jpg

Syntax for Three Tables

SQL ‘92 syntax to join three tables

FROM Table1

INNER JOIN (Table2 INNER JOIN Table3

ON Table2.ColA = Table3.ColA)

ON Table1.ColB = Table2.ColB

Easier notation, but not correct syntax

FROM Table1, Table2, Table3

JOINTable1.ColB = Table2.ColB

Table2.ColA = Table3.ColA


Multiple tables many l.jpg

List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2001 and 12/31/2001.

Query04_12

Multiple Tables (Many)

SELECT DISTINCTROW Customer.LastName, Customer.Phone

FROM Customer INNER JOIN (Sale INNER JOIN (Animal INNER JOIN SaleAnimal

ON Animal.AnimalID = SaleAnimal.AnimalID) ON Sale.SaleID = SaleAnimal.SaleID)

ON Customer.CustomerID = Sale.CustomerID

WHERE ((Animal.Category="Cat") AND (Animal.Registered Is Not Null)

AND (Color Like "*White*") AND (SaleDate Between #6/1/2001# And #12/31/2001#));


Building a query l.jpg

List the Last Name and Phone of anyone who bought a registered White cat between 6/1/01 and 12/31/ 01.

Identify the tables involved.

Look at the columns you want to see.

LastName, Phone: Customer

Look at the columns used in the constraints.

Registered, Color, Category: Animal

Sale Date: Sale

Find connector tables.

To connect Animal to Sale: SaleAnimal

Select the desired columns and test the query.

Enter the constraints.

Set Order By columns.

Add Group By columns.

Add summary computations to the SELECT statement.

Building a Query


Joining tables hints l.jpg

Build Relationships First

Drag and drop

From one side to many side

Avoid multiple ties between tables

SQL

FROM Table1

INNER JOIN Table2

ON Table1.ColA = Table2.ColB

Join columns are often keys, but they can be any columns--as long as the domains (types of data) match.

Multiple Tables

FROM (Table1

INNER JOIN Table2

ON T1.ColA = T2.ColB )

INNER JOIN Table3

ON T3.ColC = T3.ColD

Shorter Notation

FROM T1, T2, T3

JOIN T1.ColA = T2.ColB

T1.ColC = T3.ColD

Shorter Notation is not correct syntax, but it is easier to write.

Joining Tables (Hints)


Tables with multiple joins l.jpg

Potential problem with three or more tables.

Access uses predefined relationships to automatically determine JOINs.

JOINS might loop.

Most queries will not work with loops.

Tables with Multiple Joins

A query with these four tables with four JOINS would only return rows where the Employee had the same ZipCode as the Supplier. If you only need the Supplier city, just delete the JOIN between Employee and ZipCode. If you want both cities, add the ZipCode table again as a fifth table.


Table alias l.jpg

Table Alias

SELECT Supplier.SID, Supplier.CityID, City.City, Employee.EID, Employee.LastName, Employee.CityID, City2.City

FROM (City INNER JOIN Supplier ON City.CityID = Supplier.CityID) INNER JOIN ((City AS City2 INNER JOIN Employee ON City2.CityID = Employee.CityID) INNER JOIN AnimalOrder ON Employee.EmployeeID = AnimalOrder.EmployeeID) ON Supplier.SupplierID = AnimalOrder.SupplierID;

SIDSupplier.CityIDCity.CityEIDLastNameEmployee.CityIDCity2.City

47972Middlesboro5James7083Orlando

210896Springfield1Reeves9201Lincoln

47972Middlesboro3Reasoner8313Springfield

910740Columbia8Carpenter10592Philadelphia

510893Smyrna3Reasoner8313Springfield


Saved query create view l.jpg

Save a query

Faster: only enter once

Faster: only analyze once

Any SELECT statement

Can use the View within other SQL queries.

Saved Query: Create View

CREATE VIEW Kittens AS

SELECT *

FROM Animal

WHERE (Category = ‘cat’) AND (Today - DateBorn < 180);

SELECT Avg(ListPrice)

FROM Kittens

WHERE (Color LIKE “*Black*”);


Updateable views l.jpg

To be updateable, a view must focus on one primary table. (OrderItem)

Goal is to change data in only one table. (OrderItem)

Data can be displayed from other tables. (Item)

Never include or attempt to change primary keys from more than one table. (Item.ItemID)

Updateable Views

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

OrderLine(OrderID, ItemID, Description, Quantity)


Non updateable view l.jpg

Non Updateable View

OrderItem(OrderID, ItemID, Quantity)

Item(ItemID, Description)

121573

121822

122571

57Cat food

58Dog food

59Bird food

OrderLine(OrderID, Item.ItemID, Description, Quantity)

12157Cat food3

12182Bird feeder2

12257Cat food1

32

If you attempt to change the Item.ItemID in the OrderLineView:

You will simply change the primary key value in the Item table.

It will not add a new row to the OrderItem table.


No views are updateable in net l.jpg

No Views are Updateable in .NET

  • Most databases do not really support updateable views very well, and Oracle had lots of problems. So, in .NET, you can only update underlying tables, not the views.

    • This approach complicates your applications.

    • But it is a realistic approach.


Primary portability hint l.jpg

Primary Portability Hint

  • The DBMSs use different variations on SQL, particularly Oracle, which uses an older version.

  • To reduce application maintenance and make your application more portable:

    • Save all relatively complex queries in the database (as views).

    • Then use only simple Select queries in your application.

    • That includes all queries with:

      • JOIN

      • Concatenation

      • Most computations

    • In SQL Server, go a step further and save all of your queries as parameterized views.


Sql data definition l.jpg

SQL Data Definition

  • Create Schema AuthorizationdbNamepassword

  • Create TableTableName (Column Type, . . .)

  • Alter TableTable {Add, Column, Constraint, Drop}

  • Drop {Table Table | Index Index On table}

  • Create IndexIndexName ON Table (Column {ASC|DESC})


Syntax examples l.jpg

Syntax Examples

CREATE TABLE Customer

(CustomerID INTEGER NOT NULL,

LastName CHAR (10),

more columns

);

ALTER TABLE Customer

DROP COLUMN ZipCode;

ALTER TABLE Customer

ADD COLUMN CellPhone CHAR(15);


Sql data manipulation commands l.jpg

SQL Data Manipulation Commands

Insert Intotarget (column1 . . .) VALUES (value1 . . .)

Insert Intotarget (column1 . . .) SELECT . . . FROM. . .

Delete Fromtable WHERE condition

Updatetable SET Column=Value,. . . Where condition

Note the use of the Select and Where conditions.

Synatx is the same--only learn it once.

You can also use subqueries.


Copy old animal data l.jpg

Copy Old Animal Data

INSERT INTO OldAnimals

SELECT *

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );


Delete old animal data l.jpg

Delete Old Animal Data

DELETE

FROM Animals

WHERE AnimalID IN

(SELECT AnimalOrderItem.AnimalID

FROM AnimalOrder INNER JOIN AnimalOrderItem

ON AnimalOrder.OrderID = AnimalOrderItem.OrderId

WHERE (AnimalOrder.OrderDate<#1/1/01#) );


Update example l.jpg

Change the ListPrice of Animals at the PetStore.

For cats, increase the ListPrice by 10%.

For dogs, increase the ListPrice by 20%.

Typically use two similar UPDATE statements.

With the CASE function, the statements can be combined.

Update Example

UPDATE Animal

SET ListPrice = ListPrice*1.10

WHERE Category = “Cat” ;

UPDATE Animal

SET ListPrice = ListPrice*1.20

WHERE Category = “Dog” ;


Parameterized queries l.jpg

Parameterized Queries

  • Parameterized queries often run faster and are somewhat easier to use in .NET.

  • Support for parameterized queries:

    • SQL ServerThe best of them all.

    • AccessOK, available in most cases.

    • OracleOK for action queries but hard with Select.

    • DB2Unknown.

  • SQL Server:

    CREATE PROCEDURE GetOrder (@OrderID int) AS

    SELECT OrderID, OrderDate, CustomerID

    FROM Order WHERE OrderID = @OrderID

    RETURN

  • Even if you cannot (or do not want to) create parameterized queries in the DBMS, we will create them within the code:

    cmdMine.CommandText = “SELECT OrderID, OrderDate, CustomerID FROM Order WHERE OrderID = ?


Importance of parameterized queries l.jpg

Importance of Parameterized Queries

  • Consider a typical SQL Update command:

    UPDATE Customer SET LastName = ‘O’Malley’ WHERE CustomerID=222

  • Note the problem with the name—it includes an apostrophe, which also happens to be the string delimiter for SQL. So, this statement will crash.

  • The statement will still fail even if you create a variable (sLastName = “O’Malley”)

  • strSQL = “UPDATE Customer SET LastName = “ & sLastName …

  • But, if you create a parameterized query, it will work!

    strSQL = “UPDATE Customer SET LastName=? WHERE CustomerID=?”

    Parameter 0 is “O’Malley”

    Parameter 1 is 222

  • You must always use parameterized queries for UPDATE and INSERT, so you might as well use them all of the time.


Sql syntax list l.jpg

SQL Syntax List

  • ALTER TABLE

  • COMMIT

  • CREATE INDEX

  • CREATE TABLE

  • CREATE VIEW

  • DELETE

  • DROP

  • INSERT

  • GRANT

  • REVOKE

  • ROLLBACK

  • SELECT

  • SELECT INTO

  • UPDATE


Sql syntax alter table l.jpg

SQL Syntax: ALTER TABLE

ALTER TABLE table

ADD COLUMN column datatype (size)

DROP COLUMN column

CREATE TABLE

See also:

DROP TABLE

Return to List


Sql syntax commit l.jpg

SQL Syntax: COMMIT

COMMIT WORK

ROLLBACK

See also:

Return to List


Sql syntax create index l.jpg

SQL Syntax: CREATE INDEX

CREATE [UNIQUE] INDEX index

ON table (column1, column2, … )

WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}

CREATE TABLE

See also:

Return to List


Sql syntax create table l.jpg

SQL Syntax: CREATE TABLE

CREATE TABLE table

(

column1datatype (size) [NOT NULL] [index1] ,

column2datatype (size) [NOT NULL] [index2],

… ,

CONSTRAINT pkname PRIMARY KEY (column, …),

CONSTRAINT fkname FOREIGN KEY (column)

REFERENCES existing_table (key_column),

)

ALTER TABLE

See also:

DROP TABLE

Return to List


Sql syntax create view l.jpg

SQL Syntax: CREATE VIEW

CREATE VIEW viewname AS

SELECT …

SELECT

See also:

Return to List


Sql syntax delete l.jpg

SQL Syntax: DELETE

DELETE

FROM table

WHERE condition

DROP

See also:

Return to List


Sql syntax drop l.jpg

SQL Syntax: DROP

DROP INDEX index ON table

DROP TABLE

DROP VIEW

DELETE

See also:

Return to List


Sql syntax grant l.jpg

SQL Syntax: GRANT

GRANT privilegeprivileges

ON objectALL, ALTER, DELETE, INDEX,

TO user | PUBLICINSERT, SELECT, UPDATE

REVOKE

See also:

Return to List


Sql syntax insert l.jpg

SQL Syntax: INSERT

INSERT INTO table (column1, column2, …)

VALUES (value1, value2, … )

INSERT INTO newtable (column1, column2, …)

SELECT …

SELECT

See also:

Return to List


Sql syntax revoke l.jpg

SQL Syntax: REVOKE

REVOKE privilegeprivileges

ON object ALL, ALTER, DELETE, INDEX,

FROM user | PUBLICINSERT, SELECT, UPDATE

GRANT

See also:

Return to List


Sql syntax rollback l.jpg

SQL Syntax: ROLLBACK

SAVEPOINT savepoint{optional}

ROLLBACK WORK

TO savepoint

COMMIT

See also:

Return to List


Sql syntax select l.jpg

SQL Syntax: SELECT

  • SELECT DISTINCT table.column {AS alias} , . . .

  • FROM table/query

  • INNER JOIN table/query ON T1.ColA = T2.ColB

  • WHERE (condition)

  • GROUP BY column

  • HAVING (group condition)

  • ORDER BY table.column

  • { UNION, INTERSECT, EXCEPT … }

  • GROUP BY CUBE (dimension1, dimension2, …)

  • TRANSFORM aggfunction{Crosstab values}

    • SELECT . . . FROM . . . GROUP BY{Crosstab rows}

    • PIVOT pivot column {Crosstab columns}

Return to List


Sql syntax select into l.jpg

SQL Syntax: SELECT INTO

SELECT column1, column2, …

INTO newtable

FROM tables

WHERE condition

SELECT

See also:

Return to List


Sql syntax update l.jpg

SQL Syntax: UPDATE

UPDATE TABLE table

SET column1 = value1, column2 = value2, …

WHERE condition

DELETE

See also:

Return to List


  • Login