ASP SQL - Mark Dixon`s web site

Transcript ASP SQL - Mark Dixon`s web site

18 – Databases: Structured
Query Language
Mark Dixon, SoCCE
SOFT 131
Page 1
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
using SQL
• Objectives,
by end of this week’s sessions, you should be able to:
– Use SQL in your programs to create more
complex record-sets
Mark Dixon, SoCCE
SOFT 131
Page 2
Example: People Database
Person
PersonID Surname
Forenames Gender Phone
eMail
1 Dixon
Mark
Yes 01752 232556 [email protected]
2 Smith
John
Yes 01752 111111 [email protected]
3 Jones
Sally
No 01752 888888 [email protected]
4 Bloggs
Fred
5 Anderson Genny
6 Smith
Mark Dixon, SoCCE
Bob
Yes 01752 123123 [email protected]
No 01752 987987 [email protected]
Yes 01752 898898 [email protected]
SOFT 131
Page 3
Example: People v1
• Display Surname
of all people:
<html>
<head>
<title></title>
</head>
<body>
<%
Const cs = "…"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value & "<br>")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
</body>
</html>
Mark Dixon, SoCCE
SOFT 131
Page 4
Example: People v2
• Display Surname
of Male people:
<html>
<head>
<title></title>
</head>
<body>
<%
Const cs = "…"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
If rs.Fields("Gender").Value = True Then
Response.Write(rs.Fields("Surname").Value & "<br>")
End If
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
Mark Dixon, SoCCE
</body>
</html>
SOFT 131
Page 5
Example: People v3
• Display Surname
of Male people:
<html>
<head>
<title></title>
</head>
<body>
<%
Const cs = "…"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("SELECT * FROM Person WHERE Gender = True", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value & "<br>")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
SQL statement
</body>
</html>
Mark Dixon, SoCCE
SOFT 131
Page 6
SQL: Queries
• main purpose of databases:
– get information back out: searching
• Structured Query Language
– dedicated to interacting with databases
• 3rd Generation Language (such as VB, C++)
– code describes how to do task
• 4th Generation Language (such as SQL)
– code describes what to do (not how to do it)
Mark Dixon, SoCCE
SOFT 131
Page 7
SQL: SELECT statement
• SELECT statement
– used to get data
– can be embedded in VB, via rs.Open:
rs.Open("Person", cs)
rs.Open("SELECT * FROM [Person]", cs)
all fields
Mark Dixon, SoCCE
SOFT 131
Page 8
SQL: WHERE & ORDER BY
• WHERE clause
– used to restrict data
SELECT * FROM [People] WHERE [age]>=18;
• ORDER BY clause
– used to change order of data
SELECT * FROM [People] ORDER BY [Surname];
Mark Dixon, SoCCE
SOFT 131
Page 9
SQL: strings (text data)
• Possible confusion:
SELECT * FROM Person WHERE Surname = Smith
this will look for field called Smith - gives error
need single (SQL) quotes to signify literal text
SELECT * FROM Person WHERE Surname = 'Smith'
Mark Dixon, SoCCE
SOFT 131
Page 10
SQL & MS access queries
• MS Access
– Queries: select data from database
– really SQL select statements
– can use queries to test SQL code
MS Access: People.mdb
Mark Dixon, SoCCE
SOFT 131
Page 11
Example: Music
Track
Mark Dixon, SoCCE
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
SOFT 131
Page 12
Questions: SQL
• Create an SQL
statement to extract
Track Title of
records by
Aerosmith
Track
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
SELECT [Track Title] FROM Track
WHERE [Artist Name] = 'Aerosmith';
MS Access: Music.mdb
Mark Dixon, SoCCE
SOFT 131
Page 13
Questions: SQL
• Create an SQL
statement to extract
all fields of songs by
Disturbed, ordered
by track name
Track
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
SELECT * FROM Track WHERE [Artist Name] = 'Disturbed'
ORDER BY [Track Title];
MS Access: Music.mdb
Mark Dixon, SoCCE
SOFT 131
Page 14
Example: People v4
• User controls what
is displayed:
Mark Dixon, SoCCE
SOFT 131
Page 15
SQL: DISTINCT records
Artist Name
SELECT [Artist Name]
FROM [Track];
Black Sabbath
Aerosmith
Aerosmith
Aerosmith
Alien Ant Farm
Disturbed
Disturbed
Disturbed
Disturbed
SELECT DISTINCT [Artist Name]
FROM [Track];
Artist Name
Black Sabbath
Aerosmith
Alien Ant Farm
Disturbed
Mark Dixon, SoCCE
SOFT 131
Page 16
Tutorial Exercise: People
• Task 1: Get the People (versions 1, 2, & 3)
example (from the lecture) working.
– Use the database you created last week.
• Task 2: Modify your code to include the phone
number and email address.
• Task 3: Get the People version 4 working. You will
need to:
– Add a form to the page, and 3 submit buttons
– In your asp code, detect when a button has been
pressed (have a look at previous weeks)
• Task 4: Modify your code so that the user can
order the data by surname, or email address.
– You may want to use a Query String
Mark Dixon, SoCCE
SOFT 131
Page 17
Tutorial Exercise: Music
• Task 1: Create a web page to display the
music database details.
• Task 2: Modify your code so that the user is
presented with a list of artists, each of which
is a link. When the user clicks an artist a list
of tracks by that artist is displayed.
(the list of artists must be generated
dynamically from the database)
Mark Dixon, SoCCE
SOFT 131
Page 18

ASP SQL - Mark Dixon`s web site

Transcript ASP SQL - Mark Dixon`s web site

Directory