BA394 VBScript Lecture 5

1
BA394 VBScript Lecture 5

• Tim Faltemier

2
In the next few Lectures

• Begin to look at the SQL Language
• Show some examples of SQL and the various things
  it can do.
• Write some access routines of your own.

3
Structured Query Language (SQL)

• SQL allows the programmer an easy way to access
  data in a database.
• It is pretty much a universal database which
  means that even if you are programming in C you
  can still access the data.
• SQL is probably one of the easiest things to
  learn, but one of the most powerful when mastered.

4
SQL (II)

• Everything that you will be accessing in SQL
  comes in the form of Tables.
• Tables are the databases way of sorting the data
  into known groups (Person, Bid, Seller, etc..)
• Fields are the values that are the same
  throughout each record and usually everyone has
  one(PersonID, Lastname, etc.)
• Records are the entire values made up of many
  individual fields.

5
SQL (III)

• So the overall look of a SQL database is
  Database, Tables, Fields, Records, and Items.
• Now that your comfortable with the overall scheme
  of SQL, lets start looking at what kinds of thins
  you can do with it.
• In the next few slides, we will look at the
  various features of the language.

6
SQL - Select

• The Select statement is the most common in the
  SQL language.
• It allows the programmer to extract data from the
  database.
• The Syntax (that you will use most commonly) is
• SELECT FROM ltTablegt
• This code will return all records from all rows
  in the ltTablegt

7
SQL Select (Breakdown)

• What makes the Select statement so powerful, is
  the fact that you can alter any part of that
  statement and get a different assortment of
  values.
• If you were to call the SELECT statement with
  SELECT person FROM ltTablegt then you would get
  only the values from the person field.
• This is useful if you have LARGE amounts of data
  and only plan to use a certain section of it
  (Speeds up response times).

8
SQL Select Where

• Now that you understand how to get either All the
  data from a table or a few fields from the the
  table, now you have to learn how to only get
  certain records from the database.
• This is where the WHERE syntax comes into play.
  
• WHERE allows you to get individual records from
  the database by comparing values.

9
SQL Select Where (II)

• The WHERE syntax works as follows
• SELECT FROM ltTablegt WHERE ltFieldgt ltConditionalgt
  ltValuegt
• Ex. SELECT FROM Person WHERE CitySandnes
• You can see an example of what happens online.
• Very important Note When using Strings, use
  and . When using Numeric Values do not.

10
SQL Select Where Like

• Now that you can get certain records you may want
  to loosen up that range of values that you will
  select.
• This is where the LIKE command comes into play.
• When using the LIKE command in conjunction with
  the WHERE, you allow the record search to be much
  more loose.

11
SQL Select Where Like (II)

• Before I show you the syntax of the LIKE command,
  you have to know how to use one more thing. The
  character.
• The character is known as a wildcard and will
  match anything.
• So if I were to say LIKE a then that would
  match any string ending in an a.
• If I were to say LIKE az it would match any
  string starting with an a ending with a z.

12
SQL Select Where Like (III)

• The syntax for the LIKE command is as follows
• SELECT FROM ltTablegt WHERE ltFieldgt LIKE ltValuegt
• Ex. SELECT FROM Persons WHERE FirstName LIKE
  O
• The Final command we will learn today is the AND
  and OR commands

13
SQL AND / OR

• The AND and OR commands are used when you want to
  filter your search even more.
• AND is used when you want to test if two values
  are BOTH true.
• OR is used when you want to test if one or both
  are true.
• You can see a simple example on the next slide.

14
SQL AND / OR (II)

• If you were to say SELECT FROM Persons WHERE
  FirstNameTim AND LastNameFaltemier
• The following would ONLY match the name Tim
  Faltemier. It would NOT match Tim Smith or John
  Faltemier.
• Likewise if it was OR instead of AND then it
  WOULD match the above examples because at least
  one is true.

15
Questions or Comments
16
Final Notes

• For the next quiz, make sure you look at and
  understand all of the examples from my site and
  read the lecture notes.
• Also make SURE you check back on the website
  before the quiz next week as there may be changes
  noted etc..
• Finally if you have any other questions or
  problems dont hesitate to email me.