Basic Structure of SQL - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Basic Structure of SQL - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL

Basic Structure of SQL

• Select

List the attributes that you want to used in the result of the query.

• From

Where you look for the relations to be evaluation.

• Where

Consist of predicate involving attributes of the relations that appear in the from clause

Example

Movie (title, year, length, inColor, studioName, producerC#)

• Select title, length

From Movie Where studioName = ‘Disney’ AND year = 1990

title length

Selection in SQL

• The selection operator of relational algebra, and much more, is available through the WHERE clause of SQL. We may build expressions by comparing values using the six common comparison operators: =, <>, <, >, <=, and >=

SQL Queries and Relational Algebra

• SELECT L
• FROM R
• WHERE C

π L (σ C ( R ))

Dates and Times

• These values are often represent in a variety of format such as 5/14/1948 or 14 May 1948 Example: DATE ‘1948-05-14’ TIME ’15:00:02.5’

Subqueries

• In SQL, a query that is part of another query is call subquery. Subqueries can have subqueries, and so on, down as many level as we wish. There are a number of other ways that subqueries can be used:

• Subqueries can return a single constant, and this constant can be compared with another value in a WHERE clause
• Subqueries can return relations that can be used in various ways in WHERE clauses.
• Subqueries can have their relations appear in FROM clauses, just like any stored relation can.

Unions, Intersections, and Differences

• Uses unions to find the two relations that are not the same between the two queries.
• Uses intersections to find the two relations that are the same in both queries.
• Uses differences to find the relation in one queries but not in the other.

Example

• Unions = A U B
• Intersections = A ח B = C
• Differences = A - B

A (^) C B

Aggregate Functions

• These functions take a collection of values as input and return a single value. SQL uses five aggregation operators: 1) Average: avg 2) Minimum: min 3) Maximum: max 4) Total: sum 5) Count: count

Null Values

• Null value is uses to indicate absence information about the value of an attribute.
• Null value is uses to represent the value that are unknown, value that are inapplicable, and value that are withheld.