1 / 17

CSE 103

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 10:20. CSE 103. Section 002: M/W 10:20am-12:10pm 216 Berkey Hall Section 004: T/R 10:20am-12:10pm 120 Anthony Hall.

freym
Download Presentation

CSE 103

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. 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 10:20.

  2. CSE 103 Section 002: M/W 10:20am-12:10pm 216 Berkey Hall Section 004: T/R 10:20am-12:10pm 120 Anthony Hall

  3. CSE 103: Introduction to Information Technology • Section 002 (602): • Lead: Jo • Assistant: Erica • Section 004 (604): • Lead: Carl • Assistant: Brian http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  4. Review Questions Answer these in your groups. Refer to the textbook for Days 1 and 2 if you don’t know the answers. • What’s an RDBMS? (What does it stand for and what does that mean?) • Hint: the R is for Relational (not in the text) • What’s a record? • What’s a field? An attribute? • What does a primary key do, and why do we need one? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  5. Introduction to Queries Answer these questions in your groups. Refer to the text for Day 3 if you don’t 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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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? http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  9. First Query: SQL SELECT, FROM • Click Run SQL at bottom of page • This window accepts any valid SQL query • Type the following:SELECT 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! • ClickRun Query http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  10. First Exercise on SQL • Now by using Show Tablesto 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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 <, >, =, etc. • If searching text (char/varchar field types), must use "quotes" around what you’re searching for • Example:SELECT... FROM…WHERE MovieTitle = "Jaws" http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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) http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  16. Missing data and Ranges: SQL IS (NOT) NULL, BETWEEN…AND • 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 doesn’t understand ‘7 feet’ http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

  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 http://www.cse.msu.edu/~cse103 U:/msu/course/cse/103

More Related