Introduction to Basic SQL

 

Introduction to Basic SQL

SQL (Structured Query Language) is the standard language for relational database management systems (RDBMSs) and is one of the main reasons for the widespread commercial success of relational databases. Because SQL is standardized, users can migrate database applications between different relational DBMS products with relatively low cost and effort, provided they adhere to standard SQL features.

SQL enables portability, interoperability, and vendor independence, allowing applications to access data stored in multiple relational DBMSs using the same language.

SQL vs Formal Relational Languages

The formal relational model is based on:

  • Relational Algebra (procedural)

  • Relational Calculus (declarative)

Relational algebra requires users to specify how to retrieve data (step-by-step operations), which is too low-level for most users. SQL, on the other hand, is a high-level declarative language, where users specify what data they want, and the DBMS decides how to execute and optimize the query.

Although SQL borrows ideas from relational algebra, it is more closely based on tuple relational calculus and provides a more user-friendly syntax.

History and Standardization of SQL

  • Originally developed at IBM Research as SEQUEL for the System R project

  • Renamed SQL (Structured Query Language)

  • Standardized jointly by:

    • ANSI (American National Standards Institute)

    • ISO (International Standards Organization)

Major SQL Standards:

  • SQL-86 (SQL1)

  • SQL-92 (SQL2) – major expansion

  • SQL:1999 (SQL3) – object-oriented features

  • SQL:2003 & SQL:2006 – XML support

  • SQL:2008, SQL:2011 – additional object and advanced features

New features are added gradually, and commercial DBMSs may take time to fully support them.

Scope and Capabilities of SQL

SQL is a comprehensive database language, supporting:

  • DDL (Data Definition Language) – schema and table creation

  • DML (Data Manipulation Language) – queries and updates

  • View definition

  • Integrity constraints

  • Authorization and security

  • Transaction control

  • Embedded SQL in programming languages (Java, C/C++)

Later standards divide SQL into:

  • Core SQL – must be implemented by all compliant DBMSs

  • Optional extensions – for applications like OLAP, data mining, spatial data, multimedia, etc.

Organization of SQL 

  • Basic SQL

    • Schema and table creation

    • Constraints

    • Simple queries

    • Insert, delete, update

  • Advanced SQL

    • Complex queries

    • Views

    • Assertions

    • Triggers

  • Other SQL features  (XML, transactions, security, OLAP, etc.)

SQL Terminology vs Relational Model

Relational Model    SQL Term
Relation            Table
Tuple        Row
Attribute        Column

These terms are used interchangeably.

SQL Schemas and Catalogs

SQL Schema

  • Introduced in SQL-92

  • Groups related database objects

  • Identified by:

    • Schema name

    • Authorization identifier (owner)

Schema elements include:

  • Tables

  • Views

  • Domains

  • Types

  • Constraints

  • Authorization rules

Example:

CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

Only authorized users can create schemas and schema elements.

Catalog

  • A collection of schemas

  • Contains a special schema called INFORMATION_SCHEMA, which stores metadata

  • Referential integrity constraints can be defined only within the same catalog

CREATE TABLE Command

The CREATE TABLE statement defines a base relation by:

  • Specifying table name

  • Declaring attributes and data types

  • Defining constraints:

    • PRIMARY KEY

    • UNIQUE

    • FOREIGN KEY

    • NOT NULL

Tables created using CREATE TABLE are called base tables and are physically stored by the DBMS.

Attributes are ordered, but rows are unordered.

Foreign key constraints involving:

  • Circular references

  • Forward references
    may need to be added later using ALTER TABLE.

SQL Data Types

Numeric Data Types

  • INTEGER, SMALLINT

  • FLOAT, REAL, DOUBLE PRECISION

  • DECIMAL(i, j) / NUMERIC(i, j) for fixed precision

Character String Types

  • Fixed-length: CHAR(n)

  • Variable-length: VARCHAR(n)

  • Large text: CLOB

String literals:

  • Enclosed in single quotes

  • Case-sensitive

  • Can be concatenated using ||

Bit String Types

  • BIT(n), BIT VARYING(n)

  • Large binary data: BLOB

  • Bit literals prefixed with B or X (hexadecimal)

Boolean Type

  • TRUE, FALSE

  • UNKNOWN (due to NULL values)

  • Uses three-valued logic

Date and Time Types

  • DATE: YYYY-MM-DD

  • TIME: HH:MM:SS

  • TIMESTAMP: DATE + TIME + fractional seconds

  • TIME WITH TIME ZONE

Comparisons are allowed based on chronological order.

INTERVAL Type

  • Represents a relative time period

  • Used to add or subtract from dates/timestamps

  • Types:

    • YEAR–MONTH

    • DAY–TIME

Domains and User-Defined Types

Domains

  • Named reusable data types

  • Improve readability and maintainability

Example:

CREATE DOMAIN SSN_TYPE AS CHAR(9);

Domains can include default values and constraints.

User-Defined Types (UDTs)

  • Created using CREATE TYPE

  • Used for object-oriented features

  • Discussed in later chapters

Key Takeaway 

SQL is a standardized, declarative, and comprehensive language for relational databases that supports data definition, manipulation, constraints, views, transactions, and advanced database features, making it the foundation of modern relational DBMSs.

 

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more