Basic SQL
Fundamentals of Structured Query Language for Relational Databases
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic SQL
Mastering the language that powers modern relational database systems
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
SQl Basic
01 02 03
SQL Data Definition and Data Specifying Constraints in SQL Basic Retrieval Queries in SQL
Types
Understanding the foundation of Enforcing data integrity rules Extracting meaningful information
database structure
Data Definition Language (DDL)
04 05
INSERT, DELETE, and UPDATE Statements Additional Features of SQL
Modifying database content Advanced capabilities and extensions
Data Manipulation Language (DML)
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Basic SQL
The Language of Data
SQL is considered one of the major reasons for
the commercial success of relational databases
worldwide.
The origin of SQL traces back to relational
predicate calculus called tuple calculus, initially
proposed as the language SQUARE.
SQL actually comes from the word "SEQUEL"
which was the original term used in the paper
"SEQUEL TO SQUARE" by Chamberlin and Boyce.
IBM could not copyright that term, so they
abbreviated it to SQL and successfully
copyrighted the term.
Now popularly known as Structured Query
Language, SQL is an informal or practical
rendering of relational calculus with syntax
designed for ease of use.
SQL Data Definition, Data Types, Standards
Terminology CREATE Statement Language Features
Table, row, and column used for Main SQL command for data definition • Data definition and manipulation
relational model terms relation, tuple, operations • Transaction control (Transact-SQL)
and attribute respectively • Indexing capabilities
• Security specification (Grant and
Revoke)
• Active databases and multimedia
support
• Distributed databases
Each statement in SQL ends with a semicolon (;) to mark the end of the command.
SQL Standards Evolution
SQL-86 / SQL-1 1
Initial standard established
American National Standards Institute (ANSI)
2 SQL-92 (SQL-2)
Expanded capabilities and features
SQL-1999 (SQL-3) 3
Core specification with specialized extensions for different applications
4 SQL-2006
Added XML features for data integration
SQL-2008 5
Included object-oriented features for enhanced functionality
Current Status: SQL-3 (starting with SQL-1999) is the current standard, though it is not fully implemented in any RDBMS system.
Schema and Catalog Concepts in SQL
Key Concepts Schema Elements Include:
We cover the basic standard SQL syntax, Tables
though there are variations in existing RDBMS
Base data structures
systems.
SQL Schema Constraints
• Identified by a schema name Integrity rules
• Includes an authorization identifier
• Contains descriptors for each element
Views
Virtual tables
Domains
Data type definitions
Schema and Catalog Concepts (continued)
CREATE SCHEMA Catalog Catalog Clusters
Statement
Named collection of schemas in an SQL also supports the concept of a
SQL environment, providing cluster of catalogs for large-scale
CREATE SCHEMA COMPANY organizational structure database organization
AUTHORIZATION 'Jsmith';
Creates a new schema with
specified name and authorization
identifier
The CREATE TABLE Command
Specifying a New Relation
Table Name
Provide the name of the table to be created
Attributes
Specify attributes and their data types
Constraints
Define initial constraints for data integrity
Schema Specification (Optional)
CREATE TABLE [Link] CREATE TABLE EMPLOYEE ...
...
Creates table in default schema
Includes schema qualifier in the command
COMPANY relational database schema
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 10
One possible database state for the COMPANY relational
database schema
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 11
One possible database state for the COMPANY
relational database schema – continued
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 12
SQL CREATE TABLE data definition statements for
defining the COMPANY schema
continued on next slide
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 13
SQL CREATE TABLE data definition statements for
defining the COMPANY schema continued
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 14
Types of Relations in SQL
Base Tables (Base Relations)
Relation and its tuples are actually created and stored as a
physical file by the DBMS. These tables persist in storage and
consume disk space, forming the foundation of database
storage.
Virtual Relations (Views)
Created through the CREATE VIEW statement. Do not
correspond to any physical file. Views are dynamically
generated when queried and represent virtual tables based
on queries.