1 / 17

18 – Structured Query Language

18 – 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. Person.

clara
Download Presentation

18 – 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. 18 – 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 • Display Surname of all people in list box: Option Explicit Const cs = "Provider … " Private Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "Person", cs lstPeople.Clear Do Until rs.EOF lstPeople.AddItem rs.Fields("Surname").Value rs.MoveNext Loop rs.Close Set rs = Nothing End Sub

  5. Example: People v2 • Display Surname of Male people in list box: Option Explicit Const cs = "Provider …" Private Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "Person", cs Me.lstPeople.Clear Do Until rs.EOF If rs.Fields("Gender").Value = True Then lstPeople.AddItem rs.Fields("Surname").Value End If rs.MoveNext Loop rs.Close Set rs = Nothing End Sub

  6. Example: People v3 SQL statement • Display Surname of Male people in list box: Option Explicit Const cs = "Provider …" Private Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "SELECT * FROM Person WHERE Gender = True", cs Me.lstPeople.Clear Do Until rs.EOF lstPeople.AddItem rs.Fields("Surname").Value rs.MoveNext Loop rs.Close Set rs = Nothing End Sub

  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. People Database (with Hobbies) Person Hobby

  13. SQL: Joining tables ID Surname Forenames Phone email HobbyID Description PersonID 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 1 Archery 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 2 Herpetology 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 3 Music 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 6 Hitting people with swords 1 2 Smith John 01752 111111 john.smith@john.smith.ac.uk 4 Football 2 2 Smith John 01752 111111 john.smith@john.smith.ac.uk 5 Rugby 2 SELECT *FROM [Person], [Hobby]WHERE [Person].[ID] = [Hobby].[PersonID]; Two tables Matching records

  14. SQL: Joining tables ID Surname 1 Dixon 1 Dixon 1 Dixon 1 Dixon 2 Smith 2 Smith SELECT [ID], [Surname]FROM [Person], [Hobby]WHERE [Person].[ID] = [Hobby].[PersonID];

  15. SQL: DISTINCT records ID Surname 1 Dixon 2 Smith SELECT DISTINCT [ID], [Surname]FROM [Person], [Hobby]WHERE [Person].[ID] = [Hobby].[PersonID];

  16. Example: People v4 • User controls what is displayed: Option Explicit Const cs = "Provider …" Private Sub optAll_Click() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open "Person", cs Me.lstPeople.Clear Do Until rs.EOF Me.lstPeople.AddItem rs.Fields("Surname").Value rs.MoveNext Loop rs.Close Set rs = Nothing End Sub Private Sub optMale_Click() ‘ You fill in this code. End Sub Private Sub optFemale_Click() ‘ You fill in this code. End Sub

  17. Example: People v5 • User controls what is displayed: • V4 has 38 lines • do same with 23 Option Explicit Const cs = "Provider …" Private Sub optAll_Click() ‘ You fill in this code. End Sub Private Sub optMale_Click() ‘ You fill in this code. End Sub Private Sub optFemale_Click() ‘ You fill in this code. End Sub

More Related