Structured Query Language (SQL): Complete Guide for Class 12 NCERT Students

What is Structured Query Language (SQL)?

Structured Query Language (SQL) is a standard programming language used to manage and manipulate relational databases. It allows users to create, retrieve, update, and delete data efficiently. In Class 12 NCERT Computer Science, SQL forms the foundation for understanding how databases work and how data is organized.

SQL is divided into several sublanguages, including:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

This blog focuses primarily on DDL commands that define the structure of database objects.

Understanding Data Definition Language (DDL) in SQL

Data Definition Language (DDL) is a subset of SQL commands used to define and modify the structure of database objects such as tables, indexes, and schemas. These commands are vital for setting up the database schema before inserting or manipulating data.

Key DDL commands include:

• CREATE: Used to create new tables or database objects by specifying columns, data types, and constraints.
• ALTER: Modifies the structure of existing tables, such as adding or deleting columns or changing data types.
• DROP: Permanently deletes tables or other database objects from the database.

DDL commands directly affect the database metadata, and any changes made are auto-committed, meaning they are saved immediately without needing a separate commit command.

How to Use the CREATE Command: Defining Tables

The CREATE command is used to create a new table in the database. It defines the table name, columns, data types, and constraints.

Syntax:

``sql CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... ); ``

Example:

Create a table named Employee to store employee details: ``sql CREATE TABLE Employee ( EmpID INT PRIMARY KEY, Name VARCHAR(50), Age INT, Department VARCHAR(30) ); ``

This command creates an Employee table with four columns: EmpID, Name, Age, and Department. EmpID is set as the primary key to uniquely identify each employee.

Modifying Tables with the ALTER Command

The ALTER command allows you to change the structure of an existing table without deleting it.

Common ALTER operations:

• Add a new column
• Modify an existing column's datatype
• Drop (delete) a column

Syntax:

``sql ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name MODIFY column_name new_datatype; ALTER TABLE table_name DROP COLUMN column_name; ``

Example:

Add a new column Salary to the Employee table: ``sql ALTER TABLE Employee ADD Salary INT; ``

This command adds a new Salary column to the existing Employee table.

Removing Database Objects Using the DROP Command

The DROP command is used to delete entire tables or other database objects permanently. Use this command with caution, as it removes all data and structure related to the object.

Syntax:

``sql DROP TABLE table_name; ``

Example:

To delete the Employee table: ``sql DROP TABLE Employee; ``

After executing this command, the Employee table and all its data will be permanently removed from the database.

Comparison of DDL Commands in SQL

Here is a comparison table summarizing the main DDL commands:

Understanding these commands helps in designing and maintaining databases effectively.

Worked Example: Creating and Modifying an Employee Table

Let's work through an example to understand how to create and modify a table using SQL DDL commands.

1. Create the table: ``sql CREATE TABLE Employee ( EmpID INT PRIMARY KEY, Name VARCHAR(50), Age INT ); ` 2. Add a new column for Department: `sql ALTER TABLE Employee ADD Department VARCHAR(30); ` 3. Modify the Age column to allow NULL values: `sql ALTER TABLE Employee MODIFY Age INT NULL; ` 4. Drop the Department column if no longer needed: `sql ALTER TABLE Employee DROP COLUMN Department; ``

This sequence shows how to define a table and update its structure as requirements change.