Concepts of sql

Outline

 Overview of The SQL Query Language
 SQL Data Definition
 Basic Query Structure of SQL Queries
 Additional Basic Operations
 Set Operations
 Null Values
Chapter 3: Introduction to SQL  Aggregate Functions
 Nested Subqueries
 Modification of the Database




Database System Concepts, 7th Ed.


LICET, CSE 3.2 ©Silberschatz, Korth, Sudarshan and GK




History SQL Parts

 IBM Sequel language developed as part of System R project at the IBM  DML -- provides the ability to query information from the database and to
San Jose Research Laboratory insert tuples into, delete tuples from, and modify tuples in the database.
 Renamed Structured Query Language (SQL)  integrity – the DDL includes commands for specifying integrity
 ANSI and ISO standard SQL: constraints.

• SQL-86  View definition -- The DDL includes commands for defining views.
• SQL-89  Transaction control –includes commands for specifying the beginning and
ending of transactions.
• SQL-92
 Embedded SQL and dynamic SQL -- define how SQL statements can
• SQL:1999 (language name became Y2K compliant!) be embedded within general-purpose programming languages.
• SQL:2003  Authorization – includes commands for specifying access rights to
 Commercial systems offer most, if not all, SQL-92 features, plus varying relations and views.
feature sets from later standards and special proprietary features.
• Not all examples here may work on your particular system.




LICET, CSE 3.3 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 3.4 ©Silberschatz, Korth, Sudarshan and GK

, Data Definition Language Domain Types in SQL

The SQL data-definition language (DDL) allows the specification of  char(n). Fixed length character string, with user-specified length n.
information about relations, including:  varchar(n). Variable length character strings, with user-specified
maximum length n.
 The schema for each relation.
 int. Integer (a finite subset of the integers that is machine-dependent).
 The type of values associated with each attribute.
 smallint. Small integer (a machine-dependent subset of the integer
 The Integrity constraints domain type).
 The set of indices to be maintained for each relation.  numeric(p,d). Fixed point number, with user-specified precision of p
 Security and authorization information for each relation. digits, with d digits to the right of decimal point. (ex., numeric(3,1), allows
44.5 to be stores exactly, but not 444.5 or 0.32)
 The physical storage structure of each relation on disk.
 real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at least n
digits.
 More are covered in Chapter 4.




LICET, CSE 3.5 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 3.6 ©Silberschatz, Korth, Sudarshan and GK




Create Table Construct Integrity Constraints in Create Table

 An SQL relation is defined using the create table command:  Types of integrity constraints
create table r • primary key (A1, ..., An )
(A1 D1, A2 D2, ..., An Dn, • foreign key (Am, ..., An ) references r
(integrity-constraint1), • not null
...,
(integrity-constraintk))  SQL prevents any update to the database that violates an integrity
constraint.
• r is the name of the relation
 Example:
• each Ai is an attribute name in the schema of relation r
create table instructor (
• Di is the data type of values in the domain of attribute Ai ID char(5),
 Example: name varchar(20) not null,
dept_name varchar(20),
create table instructor (
salary numeric(8,2),
ID char(5),
name varchar(20), primary key (ID),
foreign key (dept_name) references department);
dept_name varchar(20),
salary numeric(8,2))




LICET, CSE 3.7 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 3.8 ©Silberschatz, Korth, Sudarshan and GK

, And a Few More Relation Definitions And more still

 create table student (  create table course (
ID varchar(5), course_id varchar(8),
name varchar(20) not null,
title varchar(50),
dept_name varchar(20),
tot_cred numeric(3,0), dept_name varchar(20),
primary key (ID), credits numeric(2,0),
foreign key (dept_name) references department); primary key (course_id),
foreign key (dept_name) references department);
 create table takes (
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primary key (ID, course_id, sec_id, semester, year) ,
foreign key (ID) references student,
foreign key (course_id, sec_id, semester, year) references section);




LICET, CSE 3.9 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 3.10 ©Silberschatz, Korth, Sudarshan and GK




Updates to tables Basic Query Structure

 Insert  A typical SQL query has the form:
• insert into instructor values ('10211', 'Smith', 'Biology', 66000);
 Delete select A1, A2, ..., An
from r1, r2, ..., rm
• Remove all tuples from the student relation where P
 delete from student
 Drop Table • Ai represents an attribute
• drop table r • Ri represents a relation
 Alter
• P is a predicate.
• alter table r add A D
 The result of an SQL query is a relation.
 where A is the name of the attribute to be added to relation r and
D is the domain of A.
 All exiting tuples in the relation are assigned null as the value for
the new attribute.
• alter table r drop A
 where A is the name of an attribute of relation r
 Dropping of attributes not supported by many databases.


LICET, CSE 3.11 ©Silberschatz, Korth, Sudarshan and GK LICET, CSE 3.12 ©Silberschatz, Korth, Sudarshan and GK