Criteria for when you need another table you know the value of another field B

Criteria for when you need another table you know the

This preview shows page 30 - 41 out of 64 pages.

Criteria for when you need another table… you know the value of another field B without looking because B’s value is always the same for a given value of A just as, given Album ID, we knew Album Title, Group, etc eg in a Course Offerings table: Course Sec Teacher Office Phone Userid Machine cs100 3 cs100 4 cs100 6 cs100 13 cs100 14 cs100 15 cs100 16 cs100 21 cs100 22 cs100 23 cs100 24 cs100 25 cs100 26 cs100 31 cs100 32 cs200 22 cs200 25 cs200 26 JCBeatty DC 2109 4525 jcbeatty math cs200 34 JCBeatty DC 2109 4525 jcbeatty math cs230 1 JCBeatty DC 2109 4525 jcbeatty math • • • • • • • • • • • • • • • • • • • • • T Monday, November 5, 2012
Image of page 30
CS 200 Fall 2012 SQL Here’s a simple language that lets us describe matching between (database) tables select field_list from table_list where conditions EG select Title, Artist, Price from Albums 27 SQL – Structured Query Language Sybase’s Interactive SQL App Click to run the select Monday, November 5, 2012
Image of page 31
CS 200 Fall 2012 SQL select Album_ID, Mins, Secs, Title from Songs 28 Another Example of an SQL Select Statement Monday, November 5, 2012
Image of page 32
CS 200 Fall 2012 SQL select Title, Artist, Price from Albums where Price < 10.00 29 Asking for albums below a certain price Monday, November 5, 2012
Image of page 33
CS 200 Fall 2012 SQL select Title, Artist, Medium, Category, Price from Albums where Title = 'North Country' 30 Asking for albums with a particular album title Monday, November 5, 2012
Image of page 34
CS 200 Fall 2012 SQL select Title, Artist, Medium, Category, Price from Albums where Artist = 'The Rankin Family' 31 Asking for albums by a particular artist Monday, November 5, 2012
Image of page 35
CS 200 Fall 2012 SQL select Side, Track, Songs. Title from Albums, Songs where ( Albums. Title = 'North Country') and ( Albums. Album_ID = Songs. Album_ID) 32 Listing the songs on an album (“dot notation”) . . . Monday, November 5, 2012
Image of page 36
CS 200 Fall 2012 SQL select Side, Track, Songs. Title from Albums, Songs where ( Albums. Title = 'North Country') and ( Albums. Album_ID = Songs. Album_ID) 32 Listing the songs on an album (“dot notation”) . . . and matching songs to a particular album Monday, November 5, 2012
Image of page 37
CS 200 Fall 2012 SQL select Side, Track, Songs.Title from Albums, Songs where (Albums.Title = 'North Country') and (Albums.Album_ID = Songs.Album_ID) order by Songs.Title 33 Listing the songs in alphabetical order (“order by”) Monday, November 5, 2012
Image of page 38
CS 200 Fall 2012 SQL Here’s the precise syntax of a select statement select field_listA from table_list [ where conditions ] [ order by field_listB ] [ group by field_listC ] You must type each clause in the order shown just as “The red bounces ball.” is incorrect English [ • • • ] means that • • • is optional Fields in the various field_lists must exist in a table of table_list you can use “*” as field_listA to mean “all the fields” If two tables use the same field name, you must write TableName.FieldName to indicate which field you mean they aren’t necessarily a (foreignKey, primaryKey) matchup 34 More on Matching (1) Monday, November 5, 2012
Image of page 39
CS 200 Fall 2012 SQL Repeated from the previous slide... select field_listA from table_list [ where conditions] [ order by field_listB ] [ group by field_listC] The ordering specified by “ order by ” is first by the leftmost field in field_listB then by the second leftmost field in field_listB etc, from left to right The “ where ” clause can accomplish two things
Image of page 40
Image of page 41

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture