12 Databases Structured Query Language Mark Dixon So
12 – Databases: Structured Query Language Mark Dixon, So. CCE 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, So. CCE SOFT 131 Page 2
Example: People Database Person. ID Surname Forenames Gender Phone e. Mail 1 Dixon Mark Yes 01752 232556 mark. dixon@plymouth. ac. uk 2 Smith John Yes 01752 111111 john. smith@john. smith. ac. uk 3 Jones Sally No 01752 888888 sally. jones@sally. jones. com 4 Bloggs Fred Yes 01752 123123 5 Anderson Genny 6 Smith Bob Mark Dixon, So. CCE No 01752 987987 Yes 01752 898898 SOFT 131 fred. bloggs@aaaaaa. com genny@bbbb. cccc. com bob. smith@bob-smith. com Page 3
Example: People v 1 • Display Surname of all people: <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set rs = Create. Object("ADODB. Recordset") rs. Open "Person", cs Do Until rs. EOF Response. Write rs. Fields("Surname"). Value & rs. Move. Next Loop rs. Close Set rs = Nothing %> </body> </html> Mark Dixon, So. CCE SOFT 131 Page 4
Example: People v 2 • Display Surname of Male people: <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set rs = Create. Object("ADODB. Recordset") rs. Open "Person", cs Do Until rs. EOF If rs. Fields("Gender"). Value = True Then Response. Write rs. Fields("Surname"). Value & End If rs. Move. Next Loop rs. Close Set rs = Nothing %> Mark Dixon, So. CCE </body> </html> SOFT 131 Page 5
Example: People v 3 • Display Surname of Male people: <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set rs = Create. Object("ADODB. Recordset") rs. Open "SELECT * FROM Person WHERE Gender = Do Until rs. EOF Response. Write rs. Fields("Surname"). Value & " " rs. Move. Next Loop rs. Close Set rs = Nothing %> SQL statement </body> </html> Mark Dixon, So. CCE SOFT 131 Page 6
SQL: Queries • main purpose of databases: – get information back out: searching • Structured Query Language – dedicated to interacting with databases • 3 rd Generation Language (such as VB, C++) – code describes how to do task • 4 th Generation Language (such as SQL) – code describes what to do (not how to do it) Mark Dixon, So. CCE 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, So. CCE 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, So. CCE 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, So. CCE 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, So. CCE SOFT 131 Page 11
Example: Music Track Mark Dixon, So. CCE 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 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, So. CCE SOFT 131 Page 13
Questions: SQL • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name 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, So. CCE SOFT 131 Page 14
Example: People v 4 • User controls what is displayed: Mark Dixon, So. CCE SOFT 131 Page 15
![SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien](http://slidetodoc.com/presentation_image_h2/ec86b4781ab0aeb48510160d2a9ab826/image-16.jpg "SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien")
SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien Ant Farm Disturbed SELECT DISTINCT [Artist Name] FROM [Track]; Artist Name Black Sabbath Aerosmith Alien Ant Farm Disturbed Mark Dixon, So. CCE 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, So. CCE 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, So. CCE SOFT 131 Page 18
- Slides: 18
