1 / 18

12 – Databases: Structured Query Language

12 – Databases: Structured Query Language. 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. Example: People Database.

pascal
Download Presentation

12 – Databases: Structured Query Language

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 12 – Databases: Structured Query Language

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

  3. Example: People Database Person

  4. Example: People v1 <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set rs = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value & "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> • Display Surnameof all people:

  5. Example: People v2 <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set 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 Set rs = Nothing %> </body> </html> • Display Surnameof Male people:

  6. Example: People v3 SQL statement <html> <head> <title></title> </head> <body> <% Const cs = "…" Dim rs Set 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 Set rs = Nothing %> </body> </html> • Display Surnameof Male people:

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

  8. SQL: SELECT statement • SELECT statement • used to get data • can be embedded in VB, via rs.Open:rs.Open "Person", csrs.Open "SELECT * FROM [Person]", cs all fields

  9. 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];

  10. 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 textSELECT * FROM Person WHERE Surname = 'Smith'

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

  12. Example: Music Track

  13. Questions: SQL Track • Create an SQL statement to extract Track Title of records by Aerosmith SELECT [Track Title] FROM Track WHERE [Artist Name] = 'Aerosmith'; MS Access: Music.mdb

  14. Questions: SQL Track • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name SELECT * FROM Track WHERE [Artist Name] = 'Disturbed' ORDER BY [Track Title]; MS Access: Music.mdb

  15. Example: People v4 • User controls what is displayed:

  16. SQL: DISTINCT records SELECT [Artist Name]FROM [Track]; SELECT DISTINCT [Artist Name]FROM [Track];

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

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

More Related