DBMS Queries Module-3

SQL (Structured Query Language) is the standard language for interacting with databases. Here are the basic query examples you need to know, grouped by their function, assuming we have a table called Students:

1. Data Definition Language (DDL)

DDL commands are used to define the database schema (structure).

CREATE TABLE (To make a new table) This command defines the columns and their data types.

Query	Purpose
CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), Major VARCHAR(50), EnrollmentYear INT  );	Creates the Students table with four columns. INT is for whole numbers, VARCHAR is for text (of max size 50), and PRIMARY KEY uniquely identifies each row.

2. Data Manipulation Language (DML)

DML commands are used to manage data within the defined schema.

INSERT (To add new data) This adds a new row of data into the table.

Query	Purpose
INSERT INTO Students ( StudentID, FirstName, Major, EnrollmentYear ) VALUES ( 101, 'Alice', 'Computer Science', 2023 );	Adds a record for Alice, with her ID, major, and enrollment year.

SELECT (To retrieve data) The most common and important command.

Query	Purpose
SELECT * FROM Students;	Retrieves all columns (*) and all rows from the Students table.
SELECT FirstName, Major FROM Students;	Retrieves only the FirstName and Major columns for all students.
SELECT * FROM Students WHERE Major = 'Computer Science';	Retrieves all columns for students where the Major is exactly 'Computer Science'. WHERE is used for filtering.
SELECT FirstName FROM Students WHERE EnrollmentYear >= 2022;	Retrieves the names of students enrolled in 2022 or later.

UPDATE (To change existing data) This modifies data in one or more rows. Always use a WHERE clause to specify which row(s) to update, or you'll update the entire table!

Query	Purpose
UPDATE Students SET Major = 'Data Science' WHERE StudentID = 101;	Changes the Major to 'Data Science' only for the student whose StudentID is 101.

DELETE (To remove existing data)This removes entire rows from the table. Always use a WHERE clause to specify which row(s) to delete, or you'll empty the entire table!

Query	Purpose
DELETE FROM Students WHERE StudentID = 101;	Removes the row only for the student whose StudentID is 101.

3. Basic Clause Examples

These are used to organize or summarize data.

ORDER BY (To sort results)Used to sort the result set based on a column.

Query	Purpose
SELECT FirstName, EnrollmentYear FROM Students ORDER BY EnrollmentYear DESC;	Retrieves names and years, sorted by EnrollmentYear from highest to lowest (DESC for Descending). Use ASC (or nothing) for Ascending.

GROUP BY and Aggregate Functions (To summarize data) Used to group rows that have the same values in specified columns into a summary row.

Query	Purpose
SELECT Major, COUNT(StudentID) AS TotalStudents FROM Students GROUP BY Major;	Counts the total number of students (COUNT) for each unique major. AS gives the new column a readable name.

DISTINCT (To show unique values) Used to return only the unique (different) values in a column.

Query	Purpose
SELECT DISTINCT Major FROM Students;	Shows a list of every unique major present in the Students table.

---

Here are the basic tables and the queries to populate them, which you can use in any SQL environment (like MySQL, PostgreSQL, SQLite, etc.).

Table 1: Students_India (General Data)

This table will store basic student records with typical Indian names.

CREATE TABLE Statement

SQL

CREATE TABLE Students_India (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    City VARCHAR(50),
    DateOfBirth DATE
);

INSERT Query (Single and Multiple Rows)

SQL

-- Single entry insertion
INSERT INTO Students_India (StudentID, FirstName, LastName, City, DateOfBirth)
VALUES (101, 'Ananya', 'Sharma', 'Mumbai', '2005-08-15');

-- Multiple entry insertion
INSERT INTO Students_India (StudentID, FirstName, LastName, City, DateOfBirth)
VALUES 
    (102, 'Rohan', 'Verma', 'Delhi', '2004-03-22'),
    (103, 'Priya', 'Singh', 'Bangalore', '2006-11-10'),
    (104, 'Kabir', 'Reddy', 'Hyderabad', '2003-05-01'),
    (105, 'Deepika', 'Menon', 'Chennai', '2005-01-28');

Table 2: Employee_Salaries (For Numerical/Grouping Examples)

This table helps demonstrate aggregation and departmental grouping, common in SQL queries.

CREATE TABLE Statement

SQL

CREATE TABLE Employee_Salaries (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT Query (Multiple Rows)

SQL

INSERT INTO Employee_Salaries (EmployeeID, FullName, Department, Salary)
VALUES 
    (201, 'Vikram Kumar', 'IT', 75000.00),
    (202, 'Shreya Patel', 'HR', 55000.00),
    (203, 'Aakash Rao', 'Finance', 80000.00),
    (204, 'Nandini Das', 'IT', 92000.00),
    (205, 'Rajesh Gupta', 'Finance', 68000.00),
    (206, 'Kavita Iyer', 'HR', 60000.00);

---

ALTER TABLE Query

The ALTER TABLE query is used to modify the structure of an existing table. You can use it to add, delete, or modify columns, or to add and drop constraints.

A. Add a Column

Aspect	Query	Explanation
Example	ALTER TABLE Departments ADD HeadName VARCHAR(100);	Adds a new column named HeadName to the Departments table.

B. Drop a Column

Aspect	Query	Explanation
Example	ALTER TABLE Departments DROP COLUMN Location;	Permanently removes the Location column from the Departments table.

PRIMARY KEY Constraint

A Primary Key is a column (or a set of columns) that uniquely identifies each record in a table. It ensures two things:

1. Uniqueness: No two rows can have the same Primary Key value.

2. Not Null: The Primary Key column cannot contain null (empty) values.

Aspect	Query	Explanation
Example (Inline)	sql CREATE TABLE Employees ( EmpID INT PRIMARY KEY, -- EmpID is guaranteed to be unique and never empty EmpName VARCHAR(100) );	The EmpID column is set as the Primary Key. No two employees can share the same ID.
Example (External)	sql CREATE TABLE Projects ( ProjectID INT, Name VARCHAR(100), CONSTRAINT PK_Project PRIMARY KEY (ProjectID) -- Named constraint );	Defines the Primary Key outside the column definition, often preferred for clarity.

FOREIGN KEY Constraint (Referential Integrity)

A Foreign Key is a column (or set of columns) in one table that refers to the Primary Key in another table. It establishes a link between tables and enforces referential integrity, ensuring relationships are consistent.

Aspect	Query	Explanation
Example	sql CREATE TABLE Employees ( EmpID INT PRIMARY KEY, EmpName VARCHAR(100), DeptID INT, -- This is the Foreign Key CONSTRAINT FK_EmpDept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID) -- Links to the DeptID in the Departments table );	The DeptID in the Employees table must always match an existing DeptID in the Departments table. You cannot assign an employee to a non-existent department.
Example (Using ALTER)	ALTER TABLE Employees ADD CONSTRAINT FK_EmpDept_2 FOREIGN KEY (ManagerID) REFERENCES Employees(EmpID);	Adds a Foreign Key after the table is created. This specific example is a self-referencing Foreign Key, used for hierarchies (e.g., an employee's ManagerID refers to another employee's EmpID).

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL (empty) values. Every row must have a value entered for that column.

Aspect	Query	Explanation
Example (Inline)	sql CREATE TABLE UserProfiles ( UserID INT PRIMARY KEY, Username VARCHAR(50) NOT NULL, -- Must always be filled out Email VARCHAR(100) );	The Username column must always have a value when a new row is inserted. Leaving it blank will cause the query to fail.
Example (Using ALTER)	ALTER TABLE UserProfiles ALTER COLUMN Email VARCHAR(100) NOT NULL;	Modifies an existing column to enforce the NOT NULL constraint. (Note: The exact syntax for ALTER COLUMN varies slightly across database systems like MySQL, SQL Server, etc.).

---

SELECT Queries on the Data

You can now practice your SELECT queries on these tables:

1. Find all IT employees earning over ₹80,000:

   SQL

   SELECT FullName, Salary FROM Employee_Salaries WHERE Department = 'IT' AND Salary > 80000.00;

2. Count students from Mumbai:

   SQL

   SELECT COUNT(*) FROM Students_India WHERE City = 'Mumbai';

3. Find the average salary per department:

   SQL

   SELECT Department, AVG(Salary) AS AverageSalary FROM Employee_Salaries GROUP BY Department;

We'll use the two tables with Indian name data:

1. Students_India: Basic student details.

2. Employee_Salaries: Employee details, department, and salary.

1. Selecting All Columns (SELECT *)

The most basic retrieval uses the asterisk (*) wildcard to select every column in the table.

Aspect	Query	Explanation
Retrieving Everything	SELECT * FROM Students_India;	Displays all five columns (StudentID, FirstName, LastName, City, DateOfBirth) and all rows from the Students_India table.
Retrieving All Employee Data	SELECT * FROM Employee_Salaries;	Displays all four columns (EmployeeID, FullName, Department, Salary) and all rows from the Employee_Salaries table.

2. Selecting Specific Columns

This is best practice in production environments, as it avoids fetching unnecessary data and makes the result set easier to read.

Aspect	Query	Example Result (Partial)	Explanation
Targeted Retrieval	SELECT FirstName, LastName, City FROM Students_India;	Ananya Sharma, Mumbai; Rohan Verma, Delhi; etc.	Only returns the three specified columns for all students.
Checking Specific Details	SELECT FullName, Salary FROM Employee_Salaries;	Vikram Kumar, 75000.00; Shreya Patel, 55000.00; etc.	Only returns the name and salary, hiding the Department and EmployeeID.

3. Selecting with Aliases (AS)

Aliases temporarily rename a column or an expression in the result set. This is particularly useful for making output headers cleaner.

Aspect	Query	Example Result (Header)	Explanation
Column Renaming	SELECT DateOfBirth AS DOB, City AS Location FROM Students_India;	DOB | Location	Renames DateOfBirth to DOB and City to Location in the output.
Renaming for Clarity	SELECT FullName, Department AS DeptName FROM Employee_Salaries;	FullName | DeptName	Renames Department to the more descriptive DeptName.

4. Selecting Unique Values (DISTINCT)

The DISTINCT keyword is placed immediately after SELECT to eliminate duplicate rows from the final result set.

Aspect	Query	Example Result	Explanation
Finding Unique Locations	SELECT DISTINCT City FROM Students_India;	Mumbai, Delhi, Bangalore, Hyderabad, Chennai.	If two students were from Mumbai, Mumbai would only appear once.
Finding Unique Categories	SELECT DISTINCT Department FROM Employee_Salaries;	IT, HR, Finance.	Lists every unique department currently in the table, ignoring duplicates.
Compound Distinct	SELECT DISTINCT FirstName, LastName FROM Students_India;	Ananya Sharma, Rohan Verma, etc.	Returns unique combinations of first and last names.

5. Filtering Methods (WHERE Clause)

These methods use logical operators to specify which rows to include.

Method/Aspect	Query	Explanation
Range (BETWEEN)	SELECT * FROM Employee_Salaries WHERE Salary BETWEEN 70000 AND 80000;	Selects employees whose salary is inclusively between the two values (Aakash and Sunil).
Set Membership (IN)	SELECT * FROM Employee_Salaries WHERE Department IN ('IT', 'Marketing');	Selects employees where the department matches any value in the provided list (Vikram, Nandini, Sunil).
Pattern Matching (LIKE)	SELECT FullName FROM Employee_Salaries WHERE FullName LIKE '%a%';	Selects employees whose name contains the letter 'a' anywhere in the name (Vikram, Shreya, Aakash, Nandini, Rajesh, Kavita). The % acts as a wildcard.
Negative Condition (NOT)	SELECT * FROM Employee_Salaries WHERE Department NOT IN ('HR', 'IT');	Selects employees who are not in HR or IT (Aakash, Rajesh, Sunil).
Checking Nulls	SELECT * FROM Departments WHERE Manager IS NULL;	Selects the row where the Manager column has no value defined (the Marketing department).

The LIKE operator in SQL is used within the WHERE clause to search for a specified pattern in a character column. It's essential for flexible text searches where you don't know the exact value.

The power of LIKE comes from its two main wildcard characters:

1. Percent Sign (%): Represents zero, one, or multiple characters.

2. Underscore (_): Represents a single, arbitrary character.

Here are the various patterns and their uses, using an assumed table called Products with a ProductName column.

1. % Wildcard Patterns (Matching Multiple Characters)

The percent sign is the most common wildcard for partial text matches.

Pattern	Example Query	Matches	Doesn't Match	Explanation
Starts With	LIKE 'P%'	'Phone', 'Pen', 'Printer'	'Apple', 'ePen'	Finds any string that begins with the letter 'P'.
Ends With	LIKE '%e'	'Mouse', 'Cable', 'Orange'	'Pencil', 'Red'	Finds any string that ends with the letter 'e'.
Contains	LIKE '%ap%'	'Laptop', 'Adapter', 'Apple'	'Orange', 'Cable'	Finds any string that contains the sequence 'ap' anywhere.
Specific Length, Starts With	LIKE 'C%e'	'Cable', 'Cube'	'Car', 'Core'	Finds any string that starts with 'C' and ends with 'e', regardless of what's in between.

Example Query: To find all products whose names contain the word "Pro":

SQL

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%Pro%';

2._ Wildcard Patterns (Matching Single Characters)

The underscore is used when you know the number of characters but not what they are.

Pattern	Example Query	Matches	Doesn't Match	Explanation
Fixed Length	LIKE '____'	'Book', 'Case', 'Disk'	'Pen', 'Cable'	Finds any string that is exactly four characters long.
Specific Position	LIKE 'C_rd'	'Cord', 'Card'	'Cold', 'Car'	Finds a four-character string that starts with 'C', ends with 'rd', and has any single character in the second position.
Starts with, 2nd is Vowel	LIKE 'D_sk'	'Disk', 'Dusk'	'Desk'	Finds any four-character string starting with 'D' and ending with 'sk', with any single character in between.

Example Query: To find all products that are three letters long:

SQL

SELECT ProductName
FROM Products
WHERE ProductName LIKE '___';

3.Combined Wildcard Patterns (Complex Matches) You can mix both wildcards for sophisticated filtering.

Pattern	Example Query	Matches	Doesn't Match	Explanation
Starts & Ends	LIKE 'S%e_t'	'Software Set', 'Shirt'	'Sleet', 'Seat'	Starts with 'S', ends with 't', and has at least two characters in between (the first character is zero or more, the last is one).
Specific Prefix & Minimum Length	LIKE 'M____%'	'Monitor', 'Memory Card'	'Mouse', 'Mac'	Starts with 'M', is at least five characters long (M + four specified underscores), and can have any length after that.

Example Query: To find products starting with 'A' and having at least five characters:

SQL

SELECT ProductName
FROM Products
WHERE ProductName LIKE 'A____%';

4. Negation (NOT LIKE) You can use NOT to select rows that do not match the specified pattern.

Pattern	Example Query	Explanation
Does Not Contain	WHERE ProductName NOT LIKE '%USB%';	Selects all products whose names do not contain the sequence 'USB'.
Does Not Start With	WHERE ProductName NOT LIKE 'C%';	Selects all products whose names do not start with the letter 'C'.

6. Summarization Methods (GROUP BY & Aggregates)

These methods involve calculating summaries using Aggregate Functions (like SUM, AVG, COUNT, MAX, MIN).

Method/Aspect	Query	Explanation
Simple Aggregate	SELECT SUM(Salary) AS TotalBudget FROM Employee_Salaries;	Calculates the total salary paid to all employees.
Grouping with AVG	SELECT Department, AVG(Salary) AS AvgSalary FROM Employee_Salaries GROUP BY Department;	Finds the average salary for each department.
Filtering Groups (HAVING)	SELECT Department, COUNT(*) FROM Employee_Salaries GROUP BY Department HAVING COUNT(*) > 1;	First groups the data, then filters out groups with only one employee (Marketing is excluded).

7. Combining Data Methods (JOIN Clause)

These methods are used to combine columns from two or more tables based on a related column (the join condition). We use aliases E and D for brevity.

Method/Aspect	Query	Explanation
INNER JOIN	SELECT E.FullName, D.Location FROM Employee_Salaries E INNER JOIN Departments D ON E.Department = D.DeptName;	Returns only the rows where there is a match in both tables (all employees are returned, as all their departments exist in the Departments table).
LEFT JOIN	SELECT D.DeptName, D.Manager, E.FullName FROM Departments D LEFT JOIN Employee_Salaries E ON D.DeptName = E.Department;	Returns all rows from the left table (Departments), plus any matched employees from the right table. This would show 'Marketing' even though it has no matching employees yet.
UNION	SELECT FullName FROM Employee_Salaries UNION SELECT Manager FROM Departments;	Combines the result sets of two separate queries into one list, automatically removing duplicates. (Requires columns to have the same number and data type).

---

The UPDATE Syntax Structure

The UPDATE query is a fundamental SQL command used to modify existing records in a table. It lets you change the values in one or more columns for rows that match a specified condition.

It is one of the most powerful and potentially dangerous commands because, if used incorrectly, it can alter every single row in your table.

The UPDATE query has three main components:

1. UPDATE TableName: Specifies the table you want to modify.

2. SET column1 = value1, column2 = value2, ...: Specifies the columns to be changed and their new values.

3. WHERE condition: (CRITICAL!) Specifies which rows will be affected. If you omit the WHERE clause, all rows in the table will be updated!

General Syntax:

SQL

UPDATE TableName
SET Column1 = NewValue1, Column2 = NewValue2, ...
WHERE Condition;

Examples Using Student Data

We will use the Students_India and Employee_Salaries tables you provided earlier.

Table: Students_India (Before Updates)

StudentID	FirstName	LastName	City	DateOfBirth
101	Ananya	Sharma	Mumbai	2005-08-15
102	Rohan	Verma	Delhi	2004-03-22
103	Priya	Singh	Bangalore	2006-11-10
104	Kabir	Reddy	Hyderabad	2003-05-01
105	Deepika	Menon	Chennai	2005-01-28

Example A: Updating a Single Column for a Single Row

Goal: Change Ananya Sharma's city from 'Mumbai' to 'Pune'.

Query	Explanation
UPDATE Students_India SET City = 'Pune' WHERE StudentID = 101;	WHERE StudentID = 101 isolates the single row (Ananya). The SET clause changes only the City column value for that specific row.

Result for StudentID 101: City is now Pune.

Example B: Updating Multiple Columns for a Single Row

Goal: Correct Priya Singh's last name from 'Singh' to 'Jain' and update her City to 'Kolkata'.

Query	Explanation
UPDATE Students_India SET LastName = 'Jain', City = 'Kolkata' WHERE StudentID = 103;	The SET clause uses a comma (,) to separate the column-value pairs, applying both changes to the row where StudentID is 103.

Result for StudentID 103: LastName is now Jain, and City is Kolkata.

Examples Using Employee Data

Table: Employee_Salaries (Before Updates)

EmployeeID	FullName	Department	Salary
201	Vikram Kumar	IT	75000.00
202	Shreya Patel	HR	55000.00
203	Aakash Rao	Finance	80000.00
204	Nandini Das	IT	92000.00
205	Rajesh Gupta	Finance	68000.00
206	Kavita Iyer	HR	60000.00

Example C: Updating Multiple Rows Based on a Category

Goal: Give a 10% raise to everyone in the 'IT' department.

Query	Explanation
UPDATE Employee_Salaries SET Salary = Salary * 1.10 WHERE Department = 'IT';	WHERE Department = 'IT' selects multiple rows (201 and 204). The SET clause uses an expression to calculate the new salary based on the old one.

Result for IT Employees:

• Vikram Kumar's Salary: $75000 * 1.10 = 82500.00

• Nandini Das's Salary: $92000 * 1.10 = 101200.00

Example D: The Dangerous Update (No WHERE Clause)

Goal (Hypothetical): Attempt to update only one employee, but forget the WHERE clause.

Query	Explanation
UPDATE Employee_Salaries SET Department = 'Unknown';	DANGER! Because there is no WHERE clause, the Department column for every single employee (Vikram, Shreya, Aakash, etc.) will be changed to 'Unknown'.

Result: Every employee's department would be set to 'Unknown'. Always double-check the WHERE clause!

---

The DELETE Syntax Structure

The DELETE query is a fundamental SQL command used to remove existing rows (records) from a table. Like the UPDATE command, it is powerful and dangerous because, if the proper constraints are missing, it can instantly erase all your data.

The DELETE query has a very simple structure, but the inclusion of the WHERE clause is critically important:

1. DELETE FROM TableName: Specifies the table you want to remove rows from.

2. WHERE condition: (CRITICAL!) Specifies which rows will be affected. If you omit the WHERE clause, all rows in the table will be permanently deleted!

General Syntax:

SQL

DELETE FROM TableName
WHERE Condition;

Note: This removes the rows, but the table structure (schema) remains intact.

Examples Using Student Data

We'll use the Students_India and Employee_Salaries tables to illustrate different scenarios.

Table: Students_India

StudentID	FirstName	LastName	City	DateOfBirth
101	Ananya	Sharma	Mumbai	2005-08-15
102	Rohan	Verma	Delhi	2004-03-22
103	Priya	Singh	Bangalore	2006-11-10
104	Kabir	Reddy	Hyderabad	2003-05-01
105	Deepika	Menon	Chennai	2005-01-28

Example A: Deleting a Single Row

Goal: Remove the record for the student with ID 104 (Kabir Reddy).

Query	Explanation
DELETE FROM Students_India WHERE StudentID = 104;	WHERE StudentID = 104 isolates the single row using the Primary Key. This is the safest way to delete a specific record.

Result: The row for Kabir Reddy is permanently gone. The table now has 4 rows.

Example B: Deleting Multiple Rows Based on a Condition

Goal: Delete all student records for those born before the year 2005.

Query	Explanation
DELETE FROM Students_India WHERE DateOfBirth < '2005-01-01';	WHERE DateOfBirth < '2005-01-01' selects all rows where the birth year is 2004 or earlier (Rohan and Kabir—assuming Kabir was not deleted in Example A). Both matching rows would be deleted.

Result: All students born before 2005 are removed.

Examples Using Employee Data

Table: Employee_Salaries

EmployeeID	FullName	Department	Salary
201	Vikram Kumar	IT	75000.00
202	Shreya Patel	HR	55000.00
203	Aakash Rao	Finance	80000.00
204	Nandini Das	IT	92000.00
205	Rajesh Gupta	Finance	68000.00
206	Kavita Iyer	HR	60000.00

Example C: Deleting Rows Based on a Category and Value

Goal: Remove all HR employees whose salary is less than ₹60,000.

Query	Explanation
DELETE FROM Employee_Salaries WHERE Department = 'HR' AND Salary < 60000.00;	This uses the AND operator to target rows that satisfy both conditions (Shreya Patel, EmployeeID 202).

Result: Only the row for Shreya Patel is deleted.

Example D: The Catastrophic Delete (No WHERE Clause)

Goal (Hypothetical): The user wants to clear the table completely (delete all data).

Query	Explanation
DELETE FROM Employee_Salaries;	DANGER! Without a WHERE clause, this query affects every row. All 6 employee records will be instantly and permanently deleted.

Result: The table is now empty, but the table structure still exists. (Note: For completely clearing a table, the faster command TRUNCATE TABLE is often preferred, but it cannot be rolled back.)

---

CREATE TABLE Query 🏗️

The CREATE TABLE query is used to define the structure of a new table in the database. You must specify the table name, column names, and the data type for each column.

Aspect	Query	Explanation
Example	sql CREATE TABLE Departments ( DeptID INT PRIMARY KEY, DeptName VARCHAR(100) NOT NULL, Location VARCHAR(50) );	Creates a table named Departments with three columns. Notice the inclusion of constraints (PRIMARY KEY, NOT NULL) which define rules for the data.

Export to Sheets

---