sql1 - Introduction to SQL Select-From-Where Statements...

Info icon This preview shows pages 1–19. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Introduction to SQL Select-From-Where Statements Multirelation Queries Subqueries
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2 Why SQL? SQL is a very-high-level language. Say “what to do” rather than “how to do it.” Avoid a lot of data-manipulation details needed in procedural languages like C++ or Java. Database management system figures out “best” way to execute query. Called “query optimization.”
Image of page 2
3 Select-From-Where Statements SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
4 Our Running Example All our SQL queries will be based on the following database schema. Underline indicates key attributes. Beers(name , manf) Bars(name , addr, license) Drinkers(name , addr, phone) Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar )
Image of page 4
5 Example Using Beers(name, manf) , what beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ’Anheuser-Busch’;
Image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
6 Result of Query name Bud Bud Lite Michelob . . . The answer is a relation with a single attribu name, and tuples with the name of each bee by Anheuser-Busch, such as Bud.
Image of page 6
7 Meaning of Single-Relation Query Begin with the relation in the FROM clause. Apply the selection indicated by the WHERE clause. Apply the extended projection indicated by the SELECT clause.
Image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
8 Operational Semantics Check if Anheuser-Busch name manf Bud Anheuser-Busch Include t.name in the result, if so Tuple-variable t loops over all tuples
Image of page 8
9 Operational Semantics --- General Think of a tuple variable visiting each tuple of the relation mentioned in FROM. Check if the “current” tuple satisfies the WHERE clause. If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.
Image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
10 * In SELECT clauses When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.” Example : Using Beers(name, manf) : SELECT * FROM Beers WHERE manf = ’Anheuser-Busch’;
Image of page 10
11 Result of Query: name manf Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch . . . . . . Now, the result has each of the attributes of Beers.
Image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
12 Renaming Attributes If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. Example : Using Beers(name, manf) : SELECT name AS beer, manf FROM Beers WHERE manf = ’Anheuser-Busch’
Image of page 12
13 Result of Query: beer manf Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch . . . . . .
Image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
14 Expressions in SELECT Clauses Any expression that makes sense can appear as an element of a SELECT clause. Example : Using Sells(bar, beer, price) : SELECT bar, beer, price*114 AS priceInYen FROM Sells;
Image of page 14
15 Result of Query bar beer priceInYen Joe’s Bud 285 Sue’s Miller 342
Image of page 15

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
16 Example : Constants as Expressions Using Likes(drinker, beer) : SELECT drinker, ’likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ’Bud’;
Image of page 16
17 Result of Query drinker whoLikesBud Sally likes Bud Fred likes Bud
Image of page 17

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
18 Example : Information Integration We often build “data warehouses” from the data at many “sources.”
Image of page 18
Image of page 19
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern