Informatics Practices: Class 11 — Chapter 8: Introduction to Structured Query Language (SQL)

8.1 & 8.2 Introduction to SQL

Relational Database Management Systems (RDBMS) like MySQL , Microsoft SQL Server , PostgreSQL , and Oracle allow the creation, linkage, and manipulation of relational databases. Structured Query Language (SQL) is the standard language used to interact with these databases.

Technical Profile: MySQL Database Engine

Architecture Layer / Tier: Relational Database Management System (RDBMS) Client-Server Architecture.
Operational Mechanics: SQL statements are sent from the client (e.g., Command Line Client) to the server, executed, and results are returned in tabular format.
System Impact & Execution Outputs: Case-insensitive execution of standard SQL commands, mandatory semicolon ( ; ) termination, and multiline statement buffering indicated by the -> prompt.

Rules for Writing SQL Statements

SQL is case-insensitive (e.g., SELECT and select are identical).
Statements must end with a semicolon ( ; ).
Multiline statements are supported; pressing Enter without a semicolon transitions the prompt from mysql> to -> .

8.3 Data Types and Constraints in MySQL

Every attribute in a relation must have a defined data type and can optionally have constraints to enforce data integrity.

Commonly Used Data Types

Data Type	Description
CHAR(n)	Fixed-length character string of size n (0 to 255). Right-padded with spaces if the input is shorter than n .
VARCHAR(n)	Variable-length character string of maximum size n (0 to 65535). Allocates bytes dynamically based on actual string length.
INT	Integer value occupying 4 bytes. Range: -2,147,483,648 to 2,147,483,647. For larger values, BIGINT (8 bytes) is used.
FLOAT	Floating-point numbers with decimal points. Occupies 4 bytes.
DATE	Date values in 'YYYY-MM-DD' format. Supported range: '1000-01-01' to '9999-12-31'.

Commonly Used SQL Constraints

Constraint	Description
NOT NULL	Ensures that a column cannot store NULL values.
UNIQUE	Ensures all values in a column are distinct.
DEFAULT	Provides a default value for a column when none is specified.
PRIMARY KEY	Uniquely identifies each row in a table. Implicitly NOT NULL and UNIQUE .
FOREIGN KEY	Referential constraint linking a column to the primary key of another table.

8.4 SQL for Data Definition (DDL)

Data Definition Language (DDL) commands are used to define, modify, and delete database structures such as databases and tables.

Database Operations

To create, select, and view databases:

  
    mysql> CREATE DATABASE StudentAttendance;
    Query OK, 1 row affected (0.02 sec)
    mysql> USE StudentAttendance;
    Database changed
    mysql> SHOW TABLES;
    Empty set (0.06 sec)

Table Creation and Schema Inspection

To create a table and view its structure:

  
    mysql> CREATE TABLE STUDENT(
    -> RollNumber INT,
    -> SName VARCHAR(20),
    -> SDateofBirth DATE,
    -> GUID CHAR(12),
    -> PRIMARY KEY (RollNumber));
    Query OK, 0 rows affected (0.91 sec)
    mysql> DESCRIBE STUDENT;
    +--------------+-------------+------+-----+---------+-------+
    | Field        | Type        | Null | Key | Default | Extra |
    +--------------+-------------+------+-----+---------+-------+
    | RollNumber   | int         | NO   | PRI | NULL    |       |
    | SName        | varchar(20) | YES  |     | NULL    |       |
    | SDateofBirth | date        | YES  |     | NULL    |       |
    | GUID         | char(12)    | YES  |     | NULL    |       |
    +--------------+-------------+------+-----+---------+-------+

Modifying Table Structure (ALTER TABLE)

Add Primary Key:

    
    mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);

Add Composite Primary Key:

  
  mysql> ALTER TABLE ATTENDANCE ADD PRIMARY KEY(AttendanceDate, RollNumber);

Add Foreign Key:

  
  mysql> ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

Add UNIQUE Constraint:

  
  mysql> ALTER TABLE GUARDIAN ADD UNIQUE(GPhone);

Add Attribute:

  
  mysql> ALTER TABLE GUARDIAN ADD income INT;

Modify Datatype:

  
  mysql> ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);

Modify Constraint (NOT NULL):

  
  mysql> ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;

Add Default Value:

  
  mysql> ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';

Remove Attribute:

  
  mysql> ALTER TABLE GUARDIAN DROP income;

Remove Primary Key:

  
  mysql> ALTER TABLE GUARDIAN DROP PRIMARY KEY;

Removing Tables and Databases (DROP)

  
    mysql> DROP TABLE table_name;
    mysql> DROP DATABASE database_name;

Caution: The DROP statement permanently deletes the structure and all associated data. It cannot be undone.

8.5 SQL for Data Manipulation (DML)

Data Manipulation Language (DML) commands are used to insert, modify, and delete records within tables.

Inserting Records (INSERT INTO)

To insert complete records matching the table schema order:

  
    mysql> INSERT INTO GUARDIAN
    -> VALUES (444444444444, 'Amit Ahuja', 5711492685, 'G-35, Ashok vihar, Delhi');

To insert partial records or specify custom column ordering:

  
    mysql> INSERT INTO GUARDIAN(GUID, GName, GAddress)
    -> VALUES (333333333333, 'Danny Dsouza', 'S -13, Ashok Village, Daman');

8.6 SQL for Data Query (DQL)

The SELECT statement is used to retrieve data from one or more tables.

Basic Selection and Column Aliasing

Retrieve specific columns and rename them in the output using AS :

  
    mysql> SELECT EName AS Name, Salary*12 AS 'Annual Salary'
    -> FROM EMPLOYEE;

Eliminating Duplicates (DISTINCT)

  
  mysql> SELECT DISTINCT DeptId FROM EMPLOYEE;

Filtering Records (WHERE Clause)

Supports relational operators ( = , < , > , <= , >= , != ) and logical operators ( AND , OR , NOT ).

  
    mysql> SELECT * FROM EMPLOYEE
    -> WHERE Salary > 5000 AND DeptId = 'D04';

Range and Set Operators (BETWEEN, IN)

BETWEEN (Inclusive Range):

    
      mysql> SELECT Ename, DeptId FROM EMPLOYEE
      -> WHERE Salary BETWEEN 20000 AND 50000;

IN (Set Membership):

  
    mysql> SELECT * FROM EMPLOYEE
    -> WHERE DeptId IN ('D01', 'D02', 'D04');

Sorting Results (ORDER BY)

Sorts records in ascending ( ASC , default) or descending ( DESC ) order:

  
    mysql> SELECT * FROM EMPLOYEE
    -> ORDER BY Salary DESC;

Handling NULL Values

Use IS NULL or IS NOT NULL to check for missing values:

  
    mysql> SELECT * FROM EMPLOYEE
    -> WHERE Bonus IS NULL;

Pattern Matching (LIKE Operator)

Uses wildcards for substring matching:

% : Represents zero, one, or multiple characters.
_ : Represents exactly one character.

  
    -- Starts with 'K'
    mysql> SELECT * FROM EMPLOYEE WHERE Ename LIKE 'K%';
    -- Exactly 5 letters ending with 'ANYA'
    mysql> SELECT * FROM EMPLOYEE WHERE Ename LIKE '_ANYA';

8.7 Data Updation and Deletion

DML operations to modify or remove existing records in a table.

Updating Records (UPDATE)

  
    mysql> UPDATE STUDENT
    -> SET GUID = 101010101010
    -> WHERE RollNumber = 3;

Caution: Omitting the WHERE clause will update all records in the table.

Deleting Records (DELETE)

  
    mysql> DELETE FROM STUDENT
    -> WHERE RollNumber = 2;

Caution: Omitting the WHERE clause will delete all records from the table, leaving an empty table structure.

Informatics Practices: Class 11 — Chapter 8: Introduction to Structured Query Language (SQL)

🧭 Navigation Assistant