CSE 103

1
CSE 103

• 103 Students Please do not log in yet.
• Check-in with Brian in the back.Review Days 3
  and 4 in the book.
• Others Please save your work and logout.Class
  begins at 1020.

2
CSE 103

• Section 002 M/W 1020am-1210pm
• 216 Berkey Hall
• Section 004 T/R 1020am-1210pm
• 120 Anthony Hall

3
CSE 103 Introduction to Information Technology

• Section 002 (602)
• Lead Jo
• Assistant Erica
• Section 004 (604)
• Lead Carl
• Assistant Brian

4
Review Questions

• Answer these in your groups. Refer to the
  textbook for Days 1 and 2 if you dont know the
  answers.
• Whats an RDBMS? (What does it stand for and what
  does that mean?)
• Hint the R is for Relational (not in the text)
• Whats a record?
• Whats a field? An attribute?
• What does a primary key do, and why do we need
  one?

5
Introduction to Queries

• Answer these questions in your groups. Refer to
  the text for Day 3 if you dont know the answers.
• What is a query?
• Why would you want to display only some of the
  fields for a record?
• What is SQL?
• What does it stand for?
• What does it do?
• Why are we studying SQL?

6
Queries and SQL

• Fundamental to manipulating data
• Retrieval, calculations, updating, etc.
• SQL is the common query language of relational
  databases all systems use it!
• Learn it once, can use any database system later
• SQL is not a programming language, but is a
  descriptive language
• Dynamic Web pages make extensive use of SQL
• Some queries are ONLY expressible in SQL, not in
  Design View in Access (or similar tools)

7
Basics of SQL

• What is meant by the term keyword?
• What are the two SQL keywords that must appear in
  every query used to retrieve data?
• What is meant by the term syntax?
• What is an example of a syntax rule in SQL?

8
CSE 103 Database Monitor

• Click on the MySQL Interface link on the
  navigation bar of the 103 Student Web site
• Click on Show Tables
• Select the Table tbl_Movies and click on Show
  Table
• Notice field names and data types
• Browse the other tables. What do you think each
  is used for?

9
First Query SQL SELECT, FROM

• Click Run SQL at bottom of page
• This window accepts any valid SQL query
• Type the followingSELECT MovieID, MovieTitle,
  Year, TypeFROM tbl_Movies
• SQL keywords are not case-sensitive
• SQL field names are not case-sensitive
• SQL table names ARE CASE-SENSITIVE!
• Click Run Query

10
First Exercise on SQL

• Now by using Show Tables to look at the makeup of
  the appropriate tables and by using Run SQL to
  construct and execute queries, produce lists of
• Only the genre names of the genre types 22
• Only names (both first and last) of the actors
  and directors 833887
• You may optionally write the queries down or copy
  them to a text/Word file
• Old queries can be helpful when you take BTs

11
Filters in queries SQL WHERE

• Most queries used to find specific information
• Find certain fields for records with a specific
  property
• E.g., movies made in 1990 albums by They Might
  Be Giants, etc.
• Use SQL keyword WHERE after the FROM (table)
  clause with condition using lt, gt, , etc.
• If searching text (char/varchar field types),
  must use "quotes" around what youre searching
  for
• ExampleSELECT... FROMWHERE MovieTitle "Jaws"

12
Using Queries as Filters

• Write SQL queries to answer the following
  questions
• Which movies were produced as TV-M? (39285)
• Note This is not a rating. TV-M means TV-Movie
• What actors/directors are named (first name)
  Gwyneth? (15)
• Which movies were produced after 2001? (23734)
• The numbers in parentheses are the number of
  records returned by correct queries
• Use your judgment as to what fields should be
  included
• Always include something descriptive, like a name
  or title, plus additional information you think
  is useful

13
Boolean Operators

• By using info in the text on page 3-10 to 3-12,
  construct queries to
• Find all TV-M movies produced so far this year.
  (1)
• Find all actors/directors with Kwan as either
  their first or last name. (67)
• Find all genres other than drama. (21)

14
Searching for Text

• In the text on pages 3-13 and 3-14, read the
  section on the LIKE operator
• Write queries to
• Find all movies with titles that include tale
  anywhere in the title. (942)
• Find all actors/directors whose last names begin
  with Wil. (5117)
• Text searching is not case-sensitive

15
Sorting Data SQL ORDER BY

• Often we want to sort data by certain fields
  (e.g., alphabetically in a rolodex)
• SQL keyword ORDER BY (always last!)
• Ascending (A-Z, 0-9) assumed can specify DESC to
  reverse order
• Separate multiple fields with ,
• SELECT FirstName, LastNameFROM tbl_People WHERE
  LastName LIKE "Wyc"ORDER BY LastName, FirstName
  DESC
• Extra space in queries makes them easier to read
  but has no effect on how they function

16
Missing data and Ranges SQL IS (NOT) NULL,
BETWEENAND

• Construct the following queries (check the book
  for hints on unknown data and data ranges, e.g.,
  years 1990-2000)
• List only the movies with unknown year data.
  (2421)
• List people with known heights, shortest first.
  (11578)
• List only the names and heights for those between
  7 and 8 feet tall, tallest first. (54)
• Hint the database doesnt understand 7 feet

17
Homework

• Look at the Homework link on the Classwork page
  of the Web site
• You will be practicing more queries with SQL
• On Day 3, you will be using the Access Design
  View to construct queries