MODULE 3: SQL DML (Data Manipulation Language), Physical ...

[Pages:238]MODULE 3: SQL DML (Data Manipulation Language), Physical Data Organization

PREPARED BY SHARIKA T R, SNGCE

PREPARED BY SHARIKA T R, SNGCE

SYLLABUS

? SQL DML (Data Manipulation Language) SQL queries on single and multiple tables, Nested queries (correlated and non-correlated), Aggregation and grouping, Views, assertions, Triggers, SQL data types.

? Physical Data Organization Review of terms: physical and logical records, blocking factor, pinned and unpinned organization. Heap files, Indexing, Singe level indices, numerical examples, Multi-level-indices, numerical examples, B-Trees & B+-Trees (structure only, algorithms not required), Extendible Hashing, Indexing on multiple keys ? grid files

PREPARED BY SHARIKA T R, SNGCE

Data-manipulation language(DML)

? The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.

Integrity

The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.

View definition

The SQL DDL includes commands for defining views.

Transaction control

SQL includes commands for specifying the beginning and ending of transactions.

PREPARED BY SHARIKA T R, SNGCE

Basic Retrieval Queries in SQL

? SQL has one basic statement for retrieving information from a database; the SELECT statement

? This is not the same as the SELECT operation of the relational algebra

? Important distinction between SQL and the formal relational model;

SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values

Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples;

it is not a set of tuples SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a query

PREPARED BY SHARIKA T R, SNGCE

SELECT

FROM

WHERE ?

is a list of attribute names whose values are to be retrieved by the query

?

is a list of the relation names required to process the query

?

is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query

PREPARED BY SHARIKA T R, SNGCE

PREPARED BY SHARIKA T R, SNGCE

PREPARED BY SHARIKA T R,

QO. Retrieve the birth date and addSNrGCEess of the employee(s) whose name is `John B. Smith'.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download