SQL Querying: Understanding SELECT Statements and Related Concepts, Slides of Database Management Systems (DBMS)

Download SQL Querying: Understanding SELECT Statements and Related Concepts and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Introduction to SQL

Select-From-Where Statements

Subqueries Grouping and Aggregation

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.”

Our Running Example

• All our SQL queries will be based on the following database schema. - Underline indicates key attributes. Candies(name, manf) Stores(name, addr, license) Consumers(name, addr, phone) Likes(consumer, candy) Sells(store, candy, price) Frequents(consumer, store)

Example

• Using Candies(name, manf), what candies are made by Hershey? SELECT name FROM Candies WHERE manf = ’Hershey’;

5

Notice SQL uses single-quotes for strings. SQL is case-insensitive, except inside strings.

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.

Operational Semantics

• To implement this algorithm think of a tuple variable (tv) ranging over 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.

* 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 Candies(name, manf):

SELECT * FROM Candies WHERE manf = ’Hershey’;

Result of Query:

name manf Twizzler Hershey Kitkat Hershey AlmondJoyHershey

......

11

Now, the result has each of the attributes of Candies.

Result of Query:

candy manf

Twizzler Hershey

Kitkat Hershey

AlmondJoyHershey

......

Expressions in SELECT Clauses

• Any expression that makes sense can appear as an element of a SELECT clause.
• Example: from Sells(store, candy, price):

SELECT store, candy, price * 114 AS priceInYen FROM Sells;

Another Example: Constant

Expressions

• From Likes(consumer, candy) :

SELECT consumer,’likes Kitkats’ AS whoLikesKitkats FROM Likes WHERE candy = ’Kitkat’;

Result of Query

consumer whoLikesKitkats

Sally likes Kitkats

Fred likes Kitkats

… …

Patterns

• WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.
• General form: LIKE or NOT LIKE
• Pattern is a quoted string with % = “any string”; _ = “any character.”

Example

• From Consumers(name, addr, phone) find the consumers with exchange 555:

SELECT name

FROM Consumers

WHERE phone LIKE ’%555-_ _ _ _’;