JDT ISLAM COLLEGE OF ARTS AND SCIENCE

Programme: Bachelor of Computer Applications (BCA) Subject: Database Management System

(BCA4CJ205) Semester: 4 Module: 3 (Query Languages)

Signature: Notes Created by Noor Mohammed.

1. INTRODUCTION TO SQL (STRUCTURED QUERY LANGUAGE)

1.1 Overview
Definition: SQL is the standard language for relational database management systems (RDBMS).
Purpose: It is used to communicate with a database to perform tasks such as creating tables,
storing data, querying data, and managing permissions.
Standardization: SQL is an ANSI (American National Standards Institute) and ISO standard, though
different vendors (Oracle, MySQL, SQL Server) have their own proprietary extensions (e.g., PL/SQL,
T-SQL).

1.2 SQL Data Types

When creating a table, each column must have a defined data type. Common standard types include:

Category Data Type Description

Numeric INT / INTEGER Whole numbers.

DECIMAL(p, s) Exact numeric with precision p and scale s .

FLOAT / REAL Approximate numeric (floating point).

Character CHAR(n) Fixed-length string of n characters.

VARCHAR(n) Variable-length string up to n characters.

Date/Time DATE Stores year, month, and day (YYYY-MM-DD).

TIME Stores hour, minute, and second.

DATETIME Stores both date and time.

Boolean BOOLEAN Stores TRUE or FALSE.

2. DATA DEFINITION LANGUAGE (DDL)
DDL commands are used to define or modify the structure (schema) of the database.

2.1 CREATE Command

Used to create a new database object (table, view, index).

Syntax:

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
...
PRIMARY KEY (column_name)
);

Example: Creating a Student Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
DateOfBirth DATE,
Department VARCHAR(20),
Fees DECIMAL(10, 2)
);

2.2 Constraints
Rules enforced on data columns to ensure integrity.
1. PRIMARY KEY: Uniquely identifies each record. Cannot be NULL.
2. FOREIGN KEY: Ensures referential integrity; points to a Primary Key in another table.
3. NOT NULL: Ensures a column cannot have a NULL value.
4. UNIQUE: Ensures all values in a column are different.
5. CHECK: Ensures that all values in a column satisfy a specific condition.
6. DEFAULT: Sets a default value for a column if no value is specified.

Example with Constraints:

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 18),
JoinDate DATE DEFAULT GETDATE(),
 DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

2.3 ALTER Command

Used to add, delete, or modify columns in an existing table.

Adding a Column:

ALTER TABLE Student ADD Email VARCHAR(100);

Modifying a Column (Data Type):

ALTER TABLE Student MODIFY Name VARCHAR(100);

-- Note: Syntax varies (SQL Server uses ALTER COLUMN)

Dropping a Column:

ALTER TABLE Student DROP COLUMN Fees;

2.4 DROP and TRUNCATE Commands

DROP TABLE: Deletes the entire table structure and data from the database.

DROP TABLE Student;

TRUNCATE TABLE: Removes all rows from the table but keeps the structure (schema). It is faster
than DELETE.

TRUNCATE TABLE Student;

3. DATA MANIPULATION LANGUAGE (DML)

DML commands are used to manipulate the data stored within the schema.

3.1 INSERT Command

 Used to add new rows of data to a table.

Syntax:

INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

Examples:

-- Insert specifying columns

INSERT INTO Student (RollNo, Name, Department)
VALUES (101, 'Arjun', 'CS');

-- Insert values for all columns (order must match schema)

INSERT INTO Student VALUES (102, 'Riya', '2002-05-15', 'Physics', 5000.00);

3.2 UPDATE Command

Used to modify existing records in a table.
Warning: Always use a WHERE clause, otherwise all records will be updated.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

-- Increase fees by 10% for CS department

UPDATE Student
SET Fees = Fees * 1.10
WHERE Department = 'CS';

3.3 DELETE Command

Used to delete existing records from a table.
Warning: Always use a WHERE clause, otherwise all records will be deleted.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

-- Delete student with RollNo 105

DELETE FROM Student WHERE RollNo = 105;

4. DATA RETRIEVAL (THE SELECT STATEMENT)

The SELECT statement is the most important command in SQL, used to fetch data.

4.1 Basic SELECT

Select All Columns:

SELECT * FROM Student;

Select Specific Columns:

SELECT Name, Department FROM Student;

DISTINCT Keyword: Removes duplicates from the result set.

SELECT DISTINCT Department FROM Student;

4.2 Filtering Data (WHERE Clause)

Used to filter records based on specific conditions.

Comparison Operators:
= , <> , > , < , >= , <=

SELECT * FROM Student WHERE Fees > 4000;

Logical Operators:
AND , OR , NOT
 SELECT * FROM Student
WHERE Department = 'CS' AND Fees < 5000;

Special Operators:
1. BETWEEN: Selects values within a range.

SELECT * FROM Student WHERE Fees BETWEEN 3000 AND 6000;

2. IN: Specifies multiple possible values for a column.

SELECT * FROM Student WHERE Department IN ('CS', 'IT', 'BCA');

3. LIKE: Used for pattern matching with wildcards.

% : Matches zero or more characters.

_ : Matches exactly one character.

-- Names starting with 'A'

SELECT * FROM Student WHERE Name LIKE 'A%';

-- Names where the second letter is 'a'

SELECT * FROM Student WHERE Name LIKE '_a%';

4. IS NULL: Checks for NULL values.

SELECT * FROM Student WHERE Email IS NULL;

4.3 Sorting Data (ORDER BY)

Sorts the result set in ascending ( ASC - default) or descending ( DESC ) order.

SELECT Name, Fees FROM Student ORDER BY Fees DESC;

-- Sort by Dept ascending, then Name descending

SELECT * FROM Student ORDER BY Department ASC, Name DESC;

5. AGGREGATION AND GROUPING

5.1 Aggregate Functions
Functions that perform a calculation on a set of values and return a single value.
1. COUNT(): Returns the number of rows.

SELECT COUNT(*) FROM Student;

2. SUM(): Returns the total sum of a numeric column.

SELECT SUM(Fees) FROM Student;

3. AVG(): Returns the average value.

SELECT AVG(Fees) FROM Student;

4. MAX(): Returns the largest value.

5. MIN(): Returns the smallest value.

5.2 GROUP BY Clause

Groups rows that have the same values into summary rows (e.g., "find the number of students in
each department").
Often used with aggregate functions.

SELECT Department, COUNT(*) AS TotalStudents

FROM Student
GROUP BY Department;

5.3 HAVING Clause

Used to filter groups (since WHERE cannot be used with aggregate functions).

-- List departments having more than 50 students

SELECT Department, COUNT(*)
FROM Student
GROUP BY Department
HAVING COUNT(*) > 50;

Order of Execution:
 1. FROM (Choose tables)

2. WHERE (Filter rows)

3. GROUP BY (Aggregate)

4. HAVING (Filter groups)

5. SELECT (Return columns)

6. ORDER BY (Sort)

6. JOINS (QUERYING MULTIPLE TABLES)

Joins are used to combine rows from two or more tables based on a related column between them
(Primary Key - Foreign Key).

Consider two tables:

Student (RollNo, Name, DeptID)
Department (DeptID, DeptName)

6.1 INNER JOIN

Returns records that have matching values in both tables.

SELECT [Link], [Link]

FROM Student
INNER JOIN Department ON [Link] = [Link];

6.2 LEFT (OUTER) JOIN

Returns all records from the left table (Student), and the matched records from the right table
(Department).
If there is no match, the result is NULL on the right side.

SELECT [Link], [Link]

FROM Student
LEFT JOIN Department ON [Link] = [Link];

6.3 RIGHT (OUTER) JOIN

Returns all records from the right table (Department), and the matched records from the left table
(Student).

SELECT [Link], [Link]

FROM Student
 RIGHT JOIN Department ON [Link] = [Link];

6.4 FULL (OUTER) JOIN

Returns all records when there is a match in either left or right table. (Combines Left and Right
Joins).

6.5 CROSS JOIN (Cartesian Product)

Returns the Cartesian product of the set of records from the two tables.
If Table A has 5 rows and Table B has 3 rows, Result has 5 × 3 = 15 rows.

SELECT * FROM Student CROSS JOIN Department;

7. NESTED QUERIES (SUBQUERIES)

A Subquery is a query nested inside another query (usually inside the WHERE clause).

7.1 Non-Correlated Subquery (Independent)

The inner query runs once, and its result is used by the outer query.
Scalar Subquery (Returns single value):

-- Find students who pay fees greater than the average fees
SELECT Name FROM Student
WHERE Fees > (SELECT AVG(Fees) FROM Student);

Multi-row Subquery (Returns a list):

-- Find students enrolled in departments located in 'Block A'

SELECT Name FROM Student
WHERE DeptID IN (SELECT DeptID FROM Department WHERE Location = 'Block A');

7.2 Correlated Subquery

The inner query depends on the outer query for its values. It executes repeatedly, once for each row
selected by the outer query.

-- Find employees whose salary is higher than the average salary of THEIR department
SELECT Name, Salary, DeptID
FROM Employee E1
WHERE Salary > (SELECT AVG(Salary)
 FROM Employee E2
WHERE [Link] = [Link]);

8. VIEWS, ASSERTIONS, AND TRIGGERS

8.1 Views (Virtual Tables)

Definition: A view is a virtual table based on the result-set of an SQL statement. It does not store
data physically; it stores the query.
Purpose:
Security: Restrict user access to specific columns (e.g., hide Salary).
Simplicity: Hide complex joins from the user.

Creating a View

CREATE VIEW CS_Students AS

SELECT RollNo, Name
FROM Student
WHERE Department = 'CS';

Querying a View

SELECT * FROM CS_Students;

8.2 Assertions
Definition: A constraint that can span multiple tables or involve complex logic that simple CHECK
constraints cannot handle.
Example: "The total number of students in all courses cannot exceed 500."
Syntax (Conceptual - support varies by DBMS):

CREATE ASSERTION TotalLimit CHECK

((SELECT COUNT(*) FROM Student) <= 500);

8.3 Triggers
Definition: A stored procedure that automatically executes (fires) when a specific event occurs in
the database (INSERT, UPDATE, DELETE).
 Uses: Auditing, enforcing complex integrity rules, automatic calculations.

Example (Conceptual Syntax):

CREATE TRIGGER BackupBeforeDelete

BEFORE DELETE ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Backup VALUES ([Link], [Link], [Link]);
END;

9. INTRODUCTION TO NoSQL DATABASES

9.1 Why NoSQL?

Limitations of RDBMS:
Rigid Schema (hard to change structure).
Scalability issues (Vertical scaling is expensive; Horizontal scaling is hard).
Handling Unstructured Data (Social media posts, logs, multimedia).
NoSQL Definition: "Not Only SQL". A broad class of database management systems that differ
from the classic relational model.

9.2 Characteristics of NoSQL

1. Schema-less: No fixed table structure. Fields can be added on the fly.
2. Horizontal Scalability: Designed to run on clusters of commodity hardware.
3. Distributed: Data is naturally distributed across multiple servers (Sharding).
4. BASE Properties (vs ACID in RDBMS):
Basically Available: System guarantees availability.
Soft state: State of system may change over time.
Eventual consistency: System will eventually become consistent once inputs stop.

9.3 Types of NoSQL Databases

1. Key-Value Stores (e.g., Redis, DynamoDB).
2. Document Stores (e.g., MongoDB, CouchDB).
3. Column-Family Stores (e.g., Cassandra, HBase).
4. Graph Databases (e.g., Neo4j).

10. KEY-VALUE AND DOCUMENT DATABASES

10.1 Key-Value Stores (Example: Redis)
Data Model: The simplest NoSQL model. Stores data as a collection of key-value pairs (like a Hash
Map or Dictionary).
Key: Unique identifier (e.g., "User:101").
Value: Can be a string, number, or a binary object (BLOB). The database treats the value as an
opaque black box.
Operations: PUT(key, value) , GET(key) , DELETE(key) .
Use Cases:
Caching (Session management).
Shopping Carts.
Real-time counters.
Redis Characteristics:
In-memory (extremely fast).
Supports complex data structures (Lists, Sets) in values.

10.2 Document Stores (Example: MongoDB)

Data Model: Stores data as "Documents" (usually JSON or BSON/Binary JSON).
Hierarchy:
Database → Collection (Table) → Document (Row).
Characteristics:
Flexible Schema: Documents in the same collection can have different fields.
Doc 1: { "name": "John", "age": 20 }
Doc 2: { "name": "Alice", "email": "a@[Link]" } (No age, added email).
Querying: Can query by field values (unlike Key-Value stores).
Indexing: Supports indexes on any field.
Use Cases:
Content Management Systems (CMS).
E-commerce catalogs.
Analytics logs.

MongoDB vs. RDBMS Terminology

RDBMS MongoDB

Database Database
 Table Collection

Row / Tuple Document

Column Field

Join $lookup (limited)

11. PRACTICE SECTION

11.1 Solved SQL Query Problems

Scenario: Table EMPLOYEE ( EmpID , Name , Salary , JoinDate , DeptID ) Table DEPARTMENT
( DeptID , DeptName , Location )

Q1: Write a query to find the names of employees who joined after '2020-01-01'.

SELECT Name FROM EMPLOYEE WHERE JoinDate > '2020-01-01';

Q2: Find the total salary payout for the 'HR' department.

SELECT SUM([Link])
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON [Link] = [Link]
WHERE [Link] = 'HR';

Q3: List departments that have no employees.

SELECT [Link]
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON [Link] = [Link]
WHERE [Link] IS NULL;

Q4: Find the employee with the second highest salary.

SELECT MAX(Salary) FROM EMPLOYEE

WHERE Salary < (SELECT MAX(Salary) FROM EMPLOYEE);

Q5: Display the Department Name and the count of employees in each, sorted by count
descending.
 SELECT [Link], COUNT([Link]) AS EmployeeCount
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON [Link] = [Link]
GROUP BY [Link]
ORDER BY EmployeeCount DESC;

11.2 Theory Questions

1. Explain the difference between DELETE and TRUNCATE commands.
Answer: DELETE is a DML command that removes rows based on a WHERE clause and can be
rolled back. It logs each deletion. TRUNCATE is a DDL command that removes all rows, resets
identity counters, cannot be rolled back (in some contexts), and is faster as it deallocates data
pages.
2. What is a Correlated Subquery? Explain with an example.
3. Compare SQL and NoSQL databases. Under what conditions would you choose MongoDB over
MySQL?
4. Explain the ACID properties in the context of RDBMS vs. BASE properties in NoSQL.
5. Write the syntax for creating a view. What are the advantages of using views?

11.3 Multiple Choice Questions (MCQs)

1. Which SQL command is used to remove a table from the database?
A. DELETE TABLE
B. ERASE TABLE
C. DROP TABLE
D. REMOVE TABLE
Answer: C
2. The pattern matching operator in SQL is:
A. MATCH
B. LIKE
C. REGEX
D. SIMILAR
Answer: B
3. Which of the following is an Aggregate Function?
A. ABS()
B. ROUND()
C. COUNT()
 D. UPPER()
Answer: C
4. To sort the result of a query, we use:
A. SORT BY
B. ORDER BY
C. GROUP BY
D. ARRANGE BY
Answer: B
5. MongoDB stores data in which format?
A. Tables
B. XML
C. BSON (Binary JSON)
D. CSV
Answer: C
6. Which join returns all records from the left table and matched records from the right?
A. INNER JOIN
B. RIGHT JOIN
C. LEFT JOIN
D. CROSS JOIN
Answer: C
7. In SQL, NULL values are checked using:
A. = NULL
B. != NULL
C. IS NULL
D. LIKE NULL
Answer: C
8. Redis is an example of:
A. Document Store
B. Key-Value Store
C. Graph Database
D. RDBMS
Answer: B
 9. The HAVING clause is used to:
A. Filter rows before grouping
B. Filter columns
C. Filter groups after grouping
D. Sort groups
Answer: C
10. Which constraint ensures that all values in a column are different?
A. NOT NULL
B. PRIMARY KEY
C. UNIQUE
D. CHECK
Answer: C
Notes Created by Noor Mohammed.