SQL Table Commands (CBSE Class 12 Computer Science - 083)

A table is the primary structure used to store data in a relational database. SQL provides several commands to create, display, modify, and delete tables. These commands are part of the Data Definition Language (DDL).

Learning Objectives

• Create tables using SQL.
• Display available tables.
• View the structure of a table.
• Modify a table using ALTER TABLE.
• Delete a table using DROP TABLE.

---

What is a Table?

A table is a collection of related data arranged in rows and columns.

• Columns represent attributes.
• Rows represent records.

Example

StudentID	Name	Class	Marks
101	Riya	XII-A	95
102	Aman	XII-B	91

---

SHOW TABLES

The SHOW TABLES command displays all tables present in the currently selected database.

Syntax

SHOW TABLES;

Example

USE School;

SHOW TABLES;

Sample Output

+----------------+
| Tables_in_school |
+----------------+
| Student        |
| Teacher        |
| Fee            |
+----------------+

---

CREATE TABLE

The CREATE TABLE command creates a new table in the selected database.

Syntax

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    ...
);

Example 1

CREATE TABLE Student
(
    StudentID INT,
    Name VARCHAR(30),
    Class CHAR(5),
    Marks INT
);

---

CREATE TABLE with Constraints

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    AdmissionNo INT UNIQUE,
    Marks INT
);

---

DESCRIBE TABLE (DESC)

The DESCRIBE (or DESC) command displays the structure of a table, including column names, data types, and constraints.

Syntax

DESCRIBE table_name;

or

DESC table_name;

Example

DESC Student;

Sample Output

+------------+-------------+------+-----+
| Field      | Type        | Null | Key |
+------------+-------------+------+-----+
| StudentID  | int         | NO   | PRI |
| Name       | varchar(30) | NO   |     |
| AdmissionNo| int         | YES  | UNI |
| Marks      | int         | YES  |     |
+------------+-------------+------+-----+

---

ALTER TABLE

The ALTER TABLE command is used to modify the structure of an existing table.

According to the CBSE syllabus, you should know how to:

• Add a column.
• Remove a column.
• Add a Primary Key.
• Remove a Primary Key.

---

Adding a New Column

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE Student
ADD City VARCHAR(20);

Updated Table Structure

StudentID	Name	Class	Marks	City

---

Removing a Column

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE Student
DROP COLUMN City;

---

Adding a Primary Key

If a table was created without a Primary Key, it can be added later using ALTER TABLE.

Syntax

ALTER TABLE table_name
ADD PRIMARY KEY(column_name);

Example

ALTER TABLE Student
ADD PRIMARY KEY(StudentID);

---

Removing a Primary Key

Syntax (MySQL)

ALTER TABLE table_name
DROP PRIMARY KEY;

Example

ALTER TABLE Student
DROP PRIMARY KEY;

---

DROP TABLE

The DROP TABLE command permanently deletes a table and all the data stored in it.

Syntax

DROP TABLE table_name;

Example

DROP TABLE Student;

---

Complete Example

CREATE DATABASE School;

USE School;

CREATE TABLE Student
(
    StudentID INT,
    Name VARCHAR(30),
    Marks INT
);

SHOW TABLES;

DESC Student;

ALTER TABLE Student
ADD Class CHAR(5);

ALTER TABLE Student
ADD PRIMARY KEY(StudentID);

ALTER TABLE Student
DROP COLUMN Class;

DROP TABLE Student;

---

Flow of Table Commands

CREATE TABLE
      │
      ▼
SHOW TABLES
      │
      ▼
DESCRIBE TABLE
      │
      ▼
ALTER TABLE
      │
      ▼
DROP TABLE

---

Summary of Table Commands

Command	Purpose
CREATE TABLE	Creates a new table.
SHOW TABLES	Displays all tables.
DESCRIBE (DESC)	Displays table structure.
ALTER TABLE ADD	Adds a new column.
ALTER TABLE DROP COLUMN	Removes a column.
ALTER TABLE ADD PRIMARY KEY	Adds a Primary Key.
ALTER TABLE DROP PRIMARY KEY	Removes the Primary Key.
DROP TABLE	Deletes the table permanently.

---

Common Errors

• Creating a table before selecting a database using USE.
• Adding a Primary Key to a column containing duplicate values.
• Dropping an important column accidentally.
• Dropping a table without taking a backup.
• Misspelling SQL keywords or forgetting semicolons.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Which SQL command is used to create a table?

The CREATE TABLE command is used to create a new table.

2. Which command displays the structure of a table?

The DESCRIBE or DESC command displays the table structure.

3. Which command is used to add a new column to an existing table?

The ALTER TABLE ... ADD command is used to add a new column.

4. Which command permanently deletes a table?

The DROP TABLE command permanently deletes a table and all its data.

5. Which command lists all tables in the selected database?

The SHOW TABLES command displays all tables present in the current database.

---

Summary

• A table stores related data in rows and columns.
• CREATE TABLE creates a new table.
• SHOW TABLES lists all tables in the selected database.
• DESCRIBE displays the structure of a table.
• ALTER TABLE modifies an existing table by adding or removing columns or primary keys.
• DROP TABLE permanently deletes a table and all its contents.
• These commands are part of the Data Definition Language (DDL).