Queries
Download
1 / 36

Queries - PowerPoint PPT Presentation


  • 68 Views
  • Uploaded on

Queries. How do we ask questions of the data? What is SELECT? What is FROM? What is WHERE? What is a calculated field?. What Is a Query?. A query is a question you ask of your database. You can: display data from multiple tables control which fields display

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Queries' - kaye-carrillo


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Queries

Queries

How do we ask questions of the data?

What is SELECT?

What is FROM?

What is WHERE?

What is a calculated field?


What is a query
What Is a Query?

  • A query is a question you ask of your database. You can:

    • display data from multiple tables

    • control which fields display

    • perform calculations on field values

    • save a query automatically

CS 105 Fall 2007


Sample query
Sample query

Note we have to capitalize the table names

CS 105 Fall 2007


Select query also called a result set

The results of a queryare displayed as aresult set

(NOT SAVED AUTOMATICALLY, COULD CHANGE THE NEXT TIME IT IS RUN)—called recordset in some programming situations.

Select Query also called a Result Set

CS 105 Fall 2007


Query types descriptions
Query Types & Descriptions

  • Select query-- most common query Retrieves and displays specific data requested from one or more tables; can specify display order—let’s try some:

  • Action queries do mass record updates in one operation. Types:

Update Query: alter the data in a Table

Append Query: adds records from one table to another table

Delete Query: deletes certain records, for example <1980

Make Table Query: creates a new Table from a query’s results.

CS 105 Fall 2007


Reserved or key words
Reserved or Key Words

  • Select * fromCustomers

  • Select, *, and from are reserved (key) words and symbols that have special meaning in SQL

  • SQLyog displays key words and symbols in blue.

  • After the word from, if SQL sees the word Customers, it assumes it's a name of a table

  • (see appendix E for a complete list of reserved or key words).

CS 105 Fall 2007


Strings
Strings

wherecust_name= “Village Toys”

  • After the key wordwhere,SQL assumesthat the wordcust_nameis the name of a field (column)

    “Village Toys” is a string

  • A string is a sequenceof ASCII symbols surrounded by singleor doublequotes that denotes a value (piece of information)

CS 105 Fall 2007


Logical operators
Logical operators

AND condition (happy only with both)

OR condition (happy with either one)

NOT condition (happy with anything but this one)

For NOT, must be NOT (name = ‘smith’)

Select *

from Pets

where not( name = "Hortence“ )

CS 105 Fall 2007


Relational criteria as in where lastname smith
Relational Criteria, as inWhereLastName =“Smith”

See Lesson 4 in SAMS book

= > < (equal to, greater than, less than)

<= >=

< > Not equal to

LIKE Pattern matching operator

BETWEEN

IS NULL

CS 105 Fall 2007


Examples of relational query criteria
Examples of Relational Query Criteria

  • LIKE‘Smith’

    Age between 21 and 65

    (Age >=21 And Age <=65 )

    <=98000returns values of less than or equal to 98000

    Not (firstname= ‘Smith’)

    Select records with values other than Smith

CS 105 Fall 2007


Example do not do this
Example: Do not do this!!!!

Select *

from Customers

where firstname ="Harry“ or “Fay”

  • Note: Repeatfield firstnameeach timebecause—this is the worst part—the Query will RUN but the results will be WRONG!

CS 105 Fall 2007


Order of precedence
Order of precedence:

  • When more than one logical operator is used in a query, NOT is evaluated first, then AND, and finally OR

  • To make sure the statement is read the way you want it to be, use parentheses, because any statement within parentheses is evaluated first!

CS 105 Fall 2007


Example
Example:

select * from Movies

where Rating < 5 or Rating > 8.5 and year > 2000

select * from Movies

where (Rating < 5 or Rating > 8.5) and year > 2000

CS 105 Fall 2007


Multiple tables
Multiple Tables

  • Two tables can be linked by a common field

  • Why would we use several tables rather than one big one?

  • How can you link one table to another?

Customers

Billing

CS 105 Fall 2007


Tables relating to other tables via fields
Tables relating to other tables via fields

CS 105 Fall 2007


Join using where lesson 13 in sams
Join using Where -- Lesson 13, in SAMs

  • When you are looking for data from two tables, you want to limit your “hits” to records that match

  • You relate one table to another by a common field

  • You want a single set of output is returned, and the join associates the correct rows in each table on the fly

  • Oops—what does “on the fly” mean?

    (not a permanent relationship)

CS 105 Fall 2007


A simple where join
A simple WHERE join

  • You do not need to specify Products.prod_name because Vendors does not have a field named prod_name

CS 105 Fall 2007


What happens if you don t use the where join
What happens if you don’t use the Where join?

Case Sensitive

For

Table Names

  • Every vendor is listed with every product, so total: 9 * 6 = 54 records (!!!)

CS 105 Fall 2007


You get too many hits
You get too many hits!!

CS 105 Fall 2007


Order of statements in a select query see lesson 5 in sams
Order of statements in a Select Query See Lesson 5 in Sams

  • The Where clause comes after the From clause

  • The Order By clause must be last, or you will get an error message

CS 105 Fall 2007


Fields that you show aren t necessarily the ones that you use for the join or filter
Fields that you show aren’t necessarily the ones that you use for the join or filter…

CS 105 Fall 2007


Note: if field names are duplicated in various tables, refer to fields specifically (note the table that they are from)

CS 105 Fall 2007


In ascending order null fields are at the top
In ascending order, NULL fields are at the top to fields specifically (note the table that they are from)

CS 105 Fall 2007


Wildcards
Wildcards to fields specifically (note the table that they are from)

The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. In another database, the wildcard might be different (such as*)

select FirstName, Lastname, City

from Employee

where FirstName LIKE “ Mi% “;

This SQL statement will match any first names that start with ‘Mi'.

.

CS 105 Fall 2007


More wildcard characters lesson 6 in sams
More Wildcard Characters Lesson 6 in Sams to fields specifically (note the table that they are from)

Underscore:

_ means that a character must be present

Like ‘_oat’

returns boat

but not oat

Note: From

or from

both work

fine

CS 105 Fall 2007


Case sensitivity
Case sensitivity to fields specifically (note the table that they are from)

  • MySQL is running on UNIX, therefore it is case sensitive

  • However, inside a search string, so far we are finding that case doesn’t matter—both these work:

CS 105 Fall 2007


Calculated field lesson 7 in sams
Calculated field – Lesson 7 in Sams to fields specifically (note the table that they are from)

ASsets up analias for a calculated value

CS 105 Fall 2007


Another calculated field
Another calculated field: to fields specifically (note the table that they are from)

CS 105 Fall 2007


Another alias field before
Another Alias field …before: to fields specifically (note the table that they are from)

CS 105 Fall 2007


How do we make it look attractive
How do we make it look attractive? to fields specifically (note the table that they are from)

Instead of

  • Detroit MI 44444

    Why not

  • Detroit, MI 44444

CS 105 Fall 2007


Joining words together concatenation
Joining Words Together: to fields specifically (note the table that they are from) Concatenation

  • Concatenation isputting two words together

  • Concatenation can be done with a function that takes two or more arguments separated by commas

    In SQL it works like this:

    Concat(field one,field two)

CS 105 Fall 2007


However it could look ugly
However, it could look ugly to fields specifically (note the table that they are from)

concat(cust_city,cust_state,cust_zip)

DetroitMI44444

How do we add a space? How do we insert a comma?

CS 105 Fall 2007


After
After: to fields specifically (note the table that they are from)

  • SQLyog uses the CONCAT function – creates an Alias field by putting 3 columns together

use Concat function rather than material shown in SAMs

CS 105 Fall 2007


Commenting your sql code
Commenting your SQL code to fields specifically (note the table that they are from)

  • You start comment lines with /* and end with */

CS 105 Fall 2007


Remark or comment statement in sql excel vba
Remark or Comment Statement in SQL, Excel, VBA to fields specifically (note the table that they are from)

  • Used for documentation.

  • Not “executable”

  • SQLyog uses green font

  • Most of the work you do out in the workplace is maintaining code--code without comments is unusable, and you have to start from scratch (!)

CS 105 Fall 2007


To summarize
To Summarize: to fields specifically (note the table that they are from)

  • How do we ask questions of the data?

  • What is SELECT?

  • What is FROM?

  • What is WHERE?

  • What is a calculated field?

  • Your moment of Zen

    http://www.youtube.com/watch?v=KERwnA8VfFM

CS 105 Fall 2007


ad