Syntax - DBMS - CodeAriyoo

DDL Commands (CREATE, ALTER, DROP, TRUNCATE, RENAME)

Definition

Data Definition Language (DDL) commands are SQL statements used to build, alter, and delete database structures such as tables, schemas, and indices.

Purpose

Used by database administrators and developers to define schema blueprints and modify structure without altering the data engine.

General Syntax

-- Create Table
CREATE TABLE tableName (col1 datatype, col2 datatype);
-- Add Column
ALTER TABLE tableName ADD colName datatype;
-- Drop Column
ALTER TABLE tableName DROP COLUMN colName;
-- Rename Table
ALTER TABLE oldName RENAME TO newName;
-- Truncate Data
TRUNCATE TABLE tableName;
-- Drop Table
DROP TABLE tableName;

Example

-- Create Supplier Table
CREATE TABLE Supplier (
    SID INT PRIMARY KEY,
    Sname VARCHAR(50),
    branch VARCHAR(50),
    city VARCHAR(50),
    phone VARCHAR(15)
);

-- Add State attribute
ALTER TABLE Supplier ADD state VARCHAR(50);

-- Remove City attribute
ALTER TABLE Supplier DROP COLUMN city;

-- Rename Table
ALTER TABLE Supplier RENAME TO sup;

Creates a relational table `Supplier`, alters it by adding `state`, drops `city`, and renames the table to `sup`.

Integrity Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT)

Definition

Constraints are rules enforced on data columns to ensure the accuracy, uniqueness, validity, and referential integrity of data stored in relational tables.

Purpose

Prevents invalid data entry, preserves relational relationships between tables, and guarantees domain rules.

General Syntax

CREATE TABLE tableName (
    col1 datatype PRIMARY KEY,
    col2 datatype UNIQUE,
    col3 datatype CHECK (condition),
    col4 datatype DEFAULT defaultValue,
    col5 datatype REFERENCES parentTable(parentCol)
);

Example

CREATE TABLE Part (
    PID INT PRIMARY KEY,
    Pname VARCHAR(50) UNIQUE,
    color VARCHAR(20) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0)
);

CREATE TABLE Supplies (
    SID INT REFERENCES Supplier(SID) ON DELETE CASCADE,
    PID INT REFERENCES Part(PID) ON DELETE CASCADE,
    qty INT DEFAULT 1 CHECK (qty >= 0),
    date_supplied DATE NOT NULL,
    PRIMARY KEY (SID, PID, date_supplied)
);

Defines primary keys, unique part names, positive price check constraints, foreign key references with cascade deletion, and default quantity.

DML Statements (INSERT, SELECT, UPDATE, DELETE)

Definition

Data Manipulation Language (DML) manages data stored within database tables, allowing insertion, retrieval, modification, and deletion of tuples.

Purpose

Enables application programming logic to perform CRUD (Create, Read, Update, Delete) transactions against relational tables.

General Syntax

-- Insert Record
INSERT INTO tableName (col1, col2) VALUES (val1, val2);
-- Retrieve Records
SELECT col1, col2 FROM tableName WHERE condition;
-- Update Record
UPDATE tableName SET col1 = newVal WHERE condition;
-- Delete Record
DELETE FROM tableName WHERE condition;

Example

-- Insert supplier
INSERT INTO Supplier (SID, Sname, branch, city, phone) 
VALUES (204001, 'Vandana', 'local', 'Delhi', '9876543210');

-- Update city to Goa
UPDATE Supplier SET city = 'Goa' WHERE Sname = 'Vandana';

-- Select suppliers in Delhi
SELECT Sname, phone FROM Supplier WHERE city = 'Delhi';

-- Delete record
DELETE FROM Supplier WHERE SID = 204001;

Inserts a new tuple into `Supplier`, updates city conditionally for 'Vandana', queries suppliers living in 'Delhi', and deletes by primary key.

SQL Pattern Matching & String Operators (LIKE, %, _)

Definition

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column using wildcards: `%` (zero or more characters) and `_` (exactly one character).

Purpose

Enables flexible textual searches, such as finding names starting with a letter, ending with a letter, or containing a substring.

General Syntax

SELECT * FROM tableName WHERE columnName LIKE 'pattern';
-- Starts with 'V' and ends with 'A': 'V%A'
-- Contains 'lk': '%lk%'
-- 'R' in second position: '_R%'

Example

-- Supplier names with 'lk' anywhere
SELECT Sname FROM Supplier WHERE Sname LIKE '%lk%';

-- Supplier names with 'R' in 2nd position
SELECT Sname FROM Supplier WHERE Sname LIKE '_R%';

-- Supplier names starting with 'V' and ending with 'A'
SELECT Sname FROM Supplier WHERE Sname LIKE 'V%A';

`%lk%` finds names containing 'lk'; `_R%` matches strings where 'R' is the 2nd character; `V%A` matches names beginning with 'V' and ending with 'A'.

Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

Definition

Aggregate functions calculate a single summary result from a set of values in a column, automatically ignoring NULL values (except `COUNT(*)`).

Purpose

Used for statistical summaries, business reporting, and financial metric calculations.

General Syntax

SELECT 
    MIN(columnName),
    MAX(columnName),
    AVG(columnName),
    SUM(columnName),
    COUNT(columnName)
FROM tableName
WHERE condition;

Example

-- Aggregate statistics on Part prices
SELECT 
    MIN(price) AS min_cost,
    MAX(price) AS max_cost,
    AVG(price) AS avg_cost,
    SUM(price) AS total_cost,
    COUNT(*)   AS total_parts
FROM Part;

-- Average cost of parts supplied by 'Mike'
SELECT AVG(p.price) AS mike_avg_cost
FROM Part p
JOIN Supplies sp ON p.PID = sp.PID
JOIN Supplier s ON sp.SID = s.SID
WHERE s.Sname = 'Mike';

Calculates minimum, maximum, average, total sum, and total count across records, and computes average price of parts supplied by 'Mike'.

GROUP BY and HAVING Clauses

Definition

The GROUP BY clause groups rows sharing the same values in specified columns into summary rows. The HAVING clause filters grouped records after aggregation.

Purpose

Used to calculate aggregated values per category, department, branch, or color, and filter groups based on aggregate conditions.

General Syntax

SELECT col1, AGG_FUNC(col2)
FROM tableName
WHERE row_filter_condition
GROUP BY col1
HAVING AGG_FUNC(col2) > threshold
ORDER BY col1;

Example

-- Total price of parts for each color
SELECT color, SUM(price) AS total_color_price
FROM Part
GROUP BY color;

-- Branches with more than 2 suppliers
SELECT branch, COUNT(SID) AS supplier_count
FROM Supplier
GROUP BY branch
HAVING COUNT(SID) > 2;

Groups parts by color to sum prices per color, and groups suppliers by branch to find branches having strictly more than 2 suppliers.

ORDER BY and Sorting

Definition

The ORDER BY clause sorts the result-set in ascending (ASC, default) or descending (DESC) order based on one or multiple columns.

Purpose

Ensures query output is presented in a readable, predictable, and ranked sequence for end-users.

General Syntax

SELECT col1, col2 FROM tableName ORDER BY col1 [ASC|DESC], col2 [ASC|DESC];

Example

-- Sort parts by pname ascending and price descending
SELECT PID, Pname, color, price
FROM Part
ORDER BY Pname ASC, price DESC;

Sorts output primarily alphabetically by part name (`ASC`) and secondary ties by price in descending order (`DESC`).

SQL Joins (INNER, NATURAL, LEFT, RIGHT, FULL OUTER)

Definition

Joins combine rows from two or more tables based on a related column between them (matching primary and foreign keys).

Purpose

Essential for querying normalized relational schemas where entity details reside in separate tables.

General Syntax

-- Inner Join
SELECT cols FROM TableA A INNER JOIN TableB B ON A.id = B.a_id;
-- Left Outer Join
SELECT cols FROM TableA A LEFT JOIN TableB B ON A.id = B.a_id;
-- Full Outer Join
SELECT cols FROM TableA A FULL OUTER JOIN TableB B ON A.id = B.a_id;

Example

-- Inner Join: suppliers and parts they supplied
SELECT s.Sname, p.Pname, sp.qty, sp.date_supplied
FROM Supplier s
INNER JOIN Supplies sp ON s.SID = sp.SID
INNER JOIN Part p ON sp.PID = p.PID;

-- Left Outer Join: all suppliers, including those with no supplies
SELECT s.Sname, sp.PID, sp.qty
FROM Supplier s
LEFT JOIN Supplies sp ON s.SID = sp.SID;

Demonstrates Inner Join to fetch matching records and Left Join to retain all left-table rows (even if no matching supply record exists).

Set Theory Operators (UNION, INTERSECT, EXCEPT / MINUS)

Definition

Set operators combine the results of two or more SELECT queries into a single result set. Queries must be union-compatible (same number of columns and compatible datatypes).

Purpose

Used for mathematical set union, intersection, difference, and Cartesian product across datasets.

General Syntax

-- Union (distinct)
SELECT col FROM TableA UNION SELECT col FROM TableB;
-- Intersect
SELECT col FROM TableA INTERSECT SELECT col FROM TableB;
-- Difference (MINUS / EXCEPT)
SELECT col FROM TableA EXCEPT SELECT col FROM TableB;
-- Cartesian Product
SELECT * FROM TableA CROSS JOIN TableB;

Example

-- Parts not yet supplied
SELECT PID, Pname FROM Part
EXCEPT
SELECT p.PID, p.Pname FROM Part p JOIN Supplies sp ON p.PID = sp.PID;

-- All parts except 'NUT' and 'BOLT' ordered by cost
SELECT Pname, price FROM Part WHERE Pname NOT IN ('NUT', 'BOLT') ORDER BY price ASC;

Calculates set difference using EXCEPT to identify unsupplied parts, and filters parts using `NOT IN` with sorting.

Transaction Control Language (TCL: COMMIT, ROLLBACK, SAVEPOINT)

Definition

TCL commands manage database transactions to maintain ACID properties (Atomicity, Consistency, Isolation, Durability).

Purpose

Allows developers to group multiple SQL operations into an atomic unit that can be permanently saved or rolled back on error.

General Syntax

BEGIN TRANSACTION;
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
COMMIT;
ROLLBACK;

Example

BEGIN;
INSERT INTO Supplier VALUES (999, 'TestSup', 'main', 'Pune', '12345');
SAVEPOINT sp1;

UPDATE Supplier SET city = 'Mumbai' WHERE SID = 999;
SAVEPOINT sp2;

DELETE FROM Supplier WHERE SID = 999;

-- Rollback to sp1 restores state after initial insert
ROLLBACK TO sp1;
COMMIT;

Starts a transaction, establishes savepoints, performs rollback to `sp1` to undo subsequent changes, and commits the valid state.

Analytical & Window Functions (ROW_NUMBER, DENSE_RANK, OVER)

Definition

Window functions perform calculations across a set of table rows related to the current row without collapsing rows into a single summary like GROUP BY.

Purpose

Used for ranking, computing moving averages, finding Nth highest values, and running totals.

General Syntax

SELECT col1,
       DENSE_RANK() OVER (PARTITION BY groupCol ORDER BY sortCol DESC) AS rank_num
FROM tableName;

Example

-- Find 5th highest earning employee
WITH RankedEmp AS (
    SELECT emp_id, emp_name, salary, dept_id,
           DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM Employee
)
SELECT * FROM RankedEmp WHERE rnk = 5;

Uses `DENSE_RANK()` over descending salary to rank employees and select the 5th highest salary earner.

Hierarchical & Recursive Queries (WITH RECURSIVE)

Definition

Recursive Common Table Expressions (CTEs) iterate over hierarchical data such as organizational charts, bill-of-materials, or graph networks.

Purpose

Enables traversal of parent-child relationships, hierarchy depth levels, and breadth-first / depth-first tree paths in SQL.

General Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor member
    SELECT id, name, manager_id, 1 AS level FROM Employee WHERE manager_id IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM Employee e
    JOIN cte_name c ON e.manager_id = c.id
)
SELECT * FROM cte_name;

Example

WITH RECURSIVE OrgHierarchy AS (
    SELECT emp_id, emp_name, manager_id, 1 AS level, CAST(emp_name AS VARCHAR(255)) AS path
    FROM Employee WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.manager_id, o.level + 1, CAST(o.path || ' -> ' || e.emp_name AS VARCHAR(255))
    FROM Employee e
    JOIN OrgHierarchy o ON e.manager_id = o.emp_id
)
SELECT emp_id, emp_name, manager_id, level, path FROM OrgHierarchy ORDER BY path;

Recursively traverses employee-manager relationships, calculating level depth and concatenated hierarchy path string.

MongoDB Document CRUD (insertOne, insertMany, find, updateOne, deleteMany)

Definition

MongoDB is a NoSQL document-oriented database storing BSON JSON-like documents. CRUD operations interact with collections via mongosh.

Purpose

Used in modern applications for schema-less data storage, rapid prototyping, and horizontal scaling.

General Syntax

// Insert
db.collection.insertOne({ key: value });
db.collection.insertMany([{ doc1 }, { doc2 }]);
// Find
db.collection.find({ query });
// Update
db.collection.updateOne({ filter }, { $set: { key: newVal } });
// Delete
db.collection.deleteMany({ filter });

Example

// Insert supplier document
db.suppliers.insertOne({
    SID: 101,
    sname: 'Geeta',
    city: 'Delhi',
    phoneno: '9988776655'
});

// Update Geeta's city and phone
db.suppliers.updateOne(
    { sname: 'Geeta' },
    { $set: { city: 'Bombay', phoneno: '11223344' } }
);

// Delete parts with price > 5000
db.parts.deleteMany({ price: { $gt: 5000 } });

Demonstrates single document insertion, targeted field updating with `$set`, and conditional batch deletion with `$gt`.

MongoDB Query Operators & Projections ($in, $or, Projection, Sort, Limit)

Definition

MongoDB provides query operators like `$or`, `$in`, `$gt`, `$lt` for complex criteria, projections to include/exclude fields, and cursor modifiers (`sort`, `limit`).

Purpose

Enables expressive document filtering, pagination, and tailored payload retrieval.

General Syntax

db.collection.find(
    { $or: [ { col: 'A' }, { val: { $lt: 200 } } ] },
    { field1: 1, field2: 1, _id: 0 }
).sort({ field1: 1 }).limit(2);

Example

// Color red or green
db.parts.find({ color: { $in: ['red', 'green'] } });

// Part P1 or price < 200
db.parts.find({ $or: [{ part_name: 'P1' }, { price: { $lt: 200 } }] });

// Project name and city, sort by city, first 2 records
db.suppliers.find({}, { sname: 1, city: 1, _id: 0 }).sort({ city: 1 }).limit(2);

Performs array membership matching with `$in`, disjunction with `$or`, field projection hiding `_id`, sorting, and pagination limiting.

Syntax Recap — DBMS