Structured Query Language (SQL) - Study Notes

Chapter Summary

Structured Query Language (SQL) is the standard database programming language used to construct, query, manage, and modify relational database management systems (RDBMS). SQL serves as the primary tool to handle structural descriptions of schemas, perform record-level manipulations such as insertion, modification, and deletion, and coordinate transactions to ensure data consistency and accuracy.

Learning Objectives

• Understand the functional roles of SQL in a Relational Database Management System (RDBMS).
• Distinguish between different sub-languages of SQL, namely DDL, DML, DQL, DCL, and TCL.
• Learn how to design database tables by selecting appropriate data types and applying structural integrity constraints.
• Apply data manipulation queries to insert, modify, and delete table records.
• Utilize data query commands with conditional filtering, sorting, grouping, and aggregation clauses.

Key Concepts and Definitions

• Data Definition Language (DDL): A category of SQL statements used to define, alter, or drop the physical structures of database schemas. Typical commands include CREATE, ALTER, DROP, and TRUNCATE.
• Data Manipulation Language (DML): A set of statements used to retrieve, insert, delete, or update the actual data stored within database tables. These include INSERT, UPDATE, and DELETE.
• Data Query Language (DQL): Specifically comprised of the SELECT command, used to fetch subsets of data based on precise search criteria.
• Data Control Language (DCL): Used to handle permissions and access rights of database users via GRANT and REVOKE commands.
• Transaction Control Language (TCL): Commands that control and manage the changes made by DML statements to keep the database in a consistent state, such as COMMIT, ROLLBACK, and SAVEPOINT.
• Database Constraints: Rules applied on columns or tables to restrict the types of data entered. Examples include PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

Worked Methods

1. Table Creation with Column Constraints

Creating a structured table requires defining field names, their respective data types, sizes, and any appropriate constraints. For example, a student table with multiple constraints is created using the following method:

CREATE TABLE Student (Admno integer NOT NULL PRIMARY KEY, Name char(20) NOT NULL, Gender char(1), Age integer DEFAULT 17, Place char(10));

2. Filtering Data Using Select Clauses

Retrieving specific information requires combining the SELECT statement with conditional clauses such as WHERE, BETWEEN, and IN. For instance, selecting students from Chennai or Delhi whose age is between 18 and 19:

SELECT Admno, Name FROM Student WHERE Place IN ('Chennai', 'Delhi') AND Age BETWEEN 18 AND 19;

3. Data Aggregation and Grouping

Summary reports can be produced by utilizing the GROUP BY clause alongside aggregate functions like COUNT, MAX, MIN, SUM, or AVG. To filter grouped rows based on an aggregate condition, the HAVING clause is required:

SELECT Place, COUNT(*) FROM Student GROUP BY Place HAVING COUNT(*) > 1;

Common Exam Traps

• DELETE vs. TRUNCATE vs. DROP: Students often confuse these three structural removal tools. DELETE is a DML command that removes individual rows based on conditions, keeping the table structure and occupied memory space. TRUNCATE is a DDL command that deletes all rows in a table to free up the allocated space, but retains the schema. DROP is a DDL command that completely deletes both the data rows and the table structure from the database.
• WHERE vs. HAVING: The WHERE clause is used to filter individual rows before grouping, whereas the HAVING clause is used strictly with the GROUP BY clause to filter groups based on aggregate results. You cannot write aggregate functions (like SUM or COUNT) inside a WHERE clause.
• DISTINCT with Null Values: When the DISTINCT keyword is applied on a column, even if there are multiple NULL values present, only a single NULL value is returned in the result set.

Exam Tips

• Ensure every SQL command ends with a semicolon (;) as it is the standard statement terminator in RDBMS environments.
• When applying multiple constraints on a single field (such as NOT NULL and UNIQUE), list them separated by a space without any commas between them, placing the comma only at the end of the entire column definition.
• Remember that the ORDER BY clause sorts data in ascending order by default. To sort in descending order, the DESC keyword must be explicitly appended.
• When writing strings in SQL values, always enclose them in single quotes (e.g., 'Chennai') to prevent syntax processing errors.