Module 3

What is SQL?

SQL (Structured Query Language) is a standard language used for managing and manipulating relational databases.

• Retrieve data from databases
• Insert, update, and delete records
• Create and manage databases and tables
• Execute queries efficiently

SQL is an ANSI standard language and is widely used in relational database systems.

What is a Schema?

A Schema is a logical collection of database objects such as:

• Tables
• Views
• Indexes
• Procedures

It acts like a namespace or container that organizes database objects. Each schema belongs to a single database.

Advantages of Schema

• Improves security by controlling access
• Organizes database objects logically
• Supports multiple objects with same name
• Allows ownership transfer
• Enables easier management and maintenance

CREATE Schema

Syntax

                CREATE SCHEMA schema_name
                [AUTHORIZATION owner_name];
            

Example

                CREATE SCHEMA student;
                GO
            

This creates a new schema named student.

ALTER Schema

Used to transfer objects from one schema to another.

Syntax

                ALTER SCHEMA target_schema
                TRANSFER old_schema.object_name;
            

Example

                ALTER SCHEMA lecturer
                TRANSFER student.marks;
            

DROP Schema

Syntax

                DROP SCHEMA [IF EXISTS] schema_name;
            

Example

                DROP SCHEMA student;
            

This deletes the schema and all its objects permanently.

SQL Data Types

• INT - Integer values
• VARCHAR - Variable-length string
• CHAR - Fixed-length string
• DATE - Date values
• FLOAT - Decimal numbers

Constraints in SQL

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK

Retrieval Queries

                SELECT * FROM students;

                SELECT name, marks FROM students WHERE marks > 80;
            

INSERT Statement

            INSERT INTO students (id, name)
            VALUES (1, 'Pruthviraj');
            

UPDATE Statement

                UPDATE students
                SET marks = 90
                WHERE id = 1;
            

DELETE Statement

                DELETE FROM students
                WHERE id = 1;
            

Additional Features of SQL

• Views for virtual tables
• Indexes for performance
• Stored procedures
• Triggers
• Transactions (ACID properties)