Unit -4

SQL DATA DEFINITION LANGUAGE

What is DDL?

● Defines schema, tables, indexes, etc.

DDL Commands

● CREATE, ALTER, DROP, TRUNCATE

CREATE TABLE Syntax

 Examples:
1. Basic Create: CREATE TABLE Employees ( ID INT, Name VARCHAR(50));

2. Create Table with Multiple Columns, Constraints, and Foreign Keys:

Create Table Orders (

Orderid Int Primary Key Auto_increment,
Orderdate Datetime Default Current_timestamp,
Customerid Int,
Totalamount Decimal(10, 2) Check (Totalamount > 0),
Status Varchar(50) Default 'pending',
Shippingaddress Varchar(255) Not Null,
Foreign Key (Customerid) References Customers(Customerid) On Delete Cascade);
3.Using Composite Key:

Create Table Orderdetails (

Orderid Int,
Productid Int,
Quantity Int Check (Quantity > 0),
Unitprice Decimal(10, 2) Check (Unitprice > 0),
Primary Key (Orderid, Productid),
Foreign Key (Orderid) References Orders(Orderid),
Foreign Key (Productid) References Products(Productid));
Attribute data types and domains in SQL
In SQL, attribute data types and domains are essential concepts
that define the kind of data that can be stored in a database
column.

Attribute Data Types in SQL

An attribute data type defines the type of data that a column

can hold in a table.

SQL provides various data types to handle different kinds of

information.
Numeric Data Types

● INT or INTEGER: Whole numbers.

Example: Age INT;

● SMALLINT: A smaller range of whole numbers.

Example: SmallIntValue SMALLINT;

● DECIMAL(p,s) or NUMERIC(p,s): Exact numeric values with precision p and scale s. Useful for
storing monetary values.
Example: Salary DECIMAL(10, 2);

● FLOAT: Approximate numeric values with floating points.

Example: Temperature FLOAT;

● DOUBLE: Similar to FLOAT but with double precision.

Example: Distance DOUBLE;
Character/String Data Types

● CHAR(n): Fixed-length character string of length n. If the string is shorter

than n, it’s padded with spaces.
Example: Gender CHAR(1);

● VARCHAR(n): Variable-length character string, where n specifies the

maximum length.
Example: Name VARCHAR(100);

● TEXT: Variable-length string with no specified maximum length. Useful for

large text fields.
Example: Description TEXT;
Date and Time Data Types

● DATE: Stores date in the format YYYY-MM-DD.

Example: Birthdate DATE;

● TIME: Stores time in the format HH:MI:SS.

Example: StartTime TIME;

● DATETIME: Stores date and time in the format YYYY-MM-DD HH:MI:SS.

Example: CreatedAt DATETIME;

● TIMESTAMP: Stores date and time with time zone information.

Example: LastUpdated TIMESTAMP;
Boolean Data Type

● BOOLEAN: Stores true or false values.

Example: IsActive BOOLEAN;

Example:
CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerName VARCHAR(100),

OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

 Domains in SQL
A domain in SQL is essentially a set of permissible values for an attribute (column).
In other words, a domain specifies the constraints on the data that can be stored in a
column. These constraints could include:

● Data Type: The basic type of the column (e.g., integer, varchar, etc.)

● Range of Values: The permissible range of values (e.g., Age could be between
0 and 120).

● Format: For example, a column storing emails could specify that the values
must follow the email format.

● Default Value: The value that is automatically assigned if no value is specified.

 Specifying basic constraints in SQL
In SQL, constraints are rules applied to the columns of a table to ensure data
integrity, accuracy, and consistency.

1. NOT NULL Constraint: The NOT NULL constraint ensures that a column cannot
have a NULL value. This is useful when a column must always contain a valid
value.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName
VARCHAR(100) NOT NULL, LastName VARCHAR(100) NOT NULL);
UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are unique across
the table.

Unlike the PRIMARY KEY, a column can accept NULL values, but all
non-NULL values must be unique.

Example:

CREATE TABLE Users (

UserID INT PRIMARY KEY,
Username VARCHAR(50) UNIQUE,
Email VARCHAR(100) UNIQUE
);
PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It

combines both NOT NULL and UNIQUE constraints. Each table can have only
one primary key, which can consist of one or more columns (composite primary
key).

Example:

CREATE TABLE Products (

ProductID INT PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL
);
FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a link between columns in two tables.

It ensures that the value in the foreign key column matches one of the values in the
referenced table’s primary key or unique key.

Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES
Customers(CustomerID));
CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specified
condition.

It can be used to enforce domain integrity by restricting the possible values

that can be stored in a column.

Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Salary DECIMAL(10, 2) CHECK (Salary > 0));
DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is
specified during record insertion. If no value is provided for the column, the
default value is inserted automatically.

Example:

CREATE TABLE Products (

ProductID INT PRIMARY KEY,
ProductName VARCHAR(255),
Available BOOLEAN DEFAULT TRUE
);
INDEX Constraint

Indexes are used to speed up the retrieval of data.

The index doesn’t enforce data integrity like other constraints but improves
performance.

Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
LastName VARCHAR(100),
FirstName VARCHAR(100),
INDEX idx_lastname (LastName));
AUTOINCREMENT/IDENTITY (for Numeric Data Types)

The AUTOINCREMENT attribute is used with numeric columns to automatically

generate a unique value when a new record is inserted.

Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100));

CREATE TABLE Employees (

EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100));
 COMPOSITE KEY Constraint

A composite key is a primary key composed of two or more columns. The combined
values in the columns must be unique.

Example:

CREATE TABLE OrderDetails (

OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
Example of Creating a Table with Various
Constraints
Create Table Customers (
Customerid Int Primary Key,
Firstname Varchar(100) Not Null,
Lastname Varchar(100) Not Null,
Email Varchar(100) Unique Not Null,
Age Int Check (Age >= 18),
Joindate Date Default Current_date,
Status Varchar(20) Check (Status In ('active', 'inactive', 'pending')),
Salesrepid Int,
Foreign Key (Salesrepid) References Salesreps(Salesrepid));
Schema change statements in SQL
Used to modify the structure of database tables after they have been created.

Adding, Removing, or changing columns, constraints, or renaming tables.

ALTER TABLE – Add a Column

ALTER TABLE Employees ADD DateOfBirth DATE;

ALTER TABLE – Modify a Column

ALTER TABLE Employees MODIFY Salary DECIMAL(12,2);
ALTER TABLE – Drop a Column

ALTER TABLE Employees DROP COLUMN MiddleName;

ALTER TABLE – Add a Constraint

ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK

(Salary > 0);
ALTER TABLE – Drop a Constraint

ALTER TABLE Employees

DROP CONSTRAINT chk_salary;

RENAME TABLE

RENAME TABLE Employees TO Staff;

DROP TABLE

DROP TABLE OldRecords;

TRUNCATE TABLE

TRUNCATE TABLE Logs;

Basic Queries in SQL
SELECT Statement

SELECT * FROM Employees;

SELECT Specific Columns

SELECT FirstName, LastName FROM Employees;

WHERE Clause

SELECT * FROM Employees WHERE Department = 'Sales';

ORDER BY Clause

SELECT * FROM Employees ORDER BY Salary DESC;

LIMIT Clause (or TOP in SQL Server)

SELECT * FROM Employees LIMIT 5;

INSERT INTO

INSERT INTO Employees (FirstName, LastName, Salary)

VALUES ('John', 'Doe', 50000);

UPDATE Statement

UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

DELETE Statement

DELETE FROM Employees

WHERE EmployeeID = 1;

DISTINCT Keyword

SELECT DISTINCT Department FROM Employees;

COUNT Aggregate Function

SELECT COUNT(*) FROM Employees;

Complex INSERT Queries
Insert using SELECT (Copy data from one table to another)

INSERT INTO archived_orders (order_id, customer_id, order_date)

SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date < '2024-01-01';
Insert with JOIN (Insert data based on related
tables)
INSERT INTO customer_logs (customer_id, activity,
timestamp)
SELECT c.id, 'Made a purchase', NOW() // constant string, NOT
from any table and SQL function, gives current timestamp
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date = CURRENT_DATE;
Complex DELETE Queries
Delete with a subquery (Delete customers with no orders)
DELETE FROM customers
WHERE id NOT IN (
SELECT DISTINCT customer_id FROM orders);

Delete using JOIN (Remove discontinued products from carts)

DELETE c
FROM cart_items c
JOIN products p ON c.product_id = p.id
WHERE p.status = 'discontinued';
Complex UPDATE Queries: Update with JOIN (Change prices for suppliers from a
specific country)

UPDATE products p
JOIN suppliers s ON p.supplier_id = s.id
SET p.price = p.price * 1.1 // price change
WHERE s.country = 'Germany'; // supplier country only Germany
Update with Subquery (Set VIP status based on total spend)
UPDATE customers
SET status = 'VIP'
WHERE id IN (
SELECT customer_id // Get customer IDs from orders and group
orders by customer, Only keep those with a total spend over 5000.
FROM orders GROUP BY customer_id HAVING
SUM(total_amount) > 5000);
Nested SELECT Queries
Subquery in the SELECT Clause: Subquery in the SELECT clause to return
calculated values or data derived from other tables.

SELECT name,
(SELECT AVG(salary) FROM employees WHERE department_id =d.id)
AS avg_salary FROM departments d;

This query calculates the average salary of each department and displays the
department name alongside it.
Subquery in the WHERE Clause (Correlated Subquery)
A correlated subquery refers to a subquery that uses values from the outer query. It’s
evaluated once for each row processed by the outer query.

SELECT name FROM employees e

WHERE e.salary > (SELECT AVG(salary) FROM employees
WHERE department_id = e.department_id);

This retrieves employees whose salary is greater than the average salary of
their department.
Subquery in the FROM Clause (Inline View)

A subquery can be used in the FROM clause to treat it like a temporary table.

SELECT department, total_sales

FROM ( SELECT department_id, SUM(sales) AS total_sales
FROM sales GROUP BY department_id) AS department_sales
WHERE total_sales > 100000;

This finds departments with total sales greater than 100,000 by first calculating
the total sales per department in a subquery.
Nested UPDATE Queries

Update with a Subquery in the SET Clause

You can use a nested query in an UPDATE statement to modify a field based on
values from another query.

UPDATE employees
SET salary = ( SELECT AVG(salary)
FROM employees WHERE department_id = 3)
WHERE department_id = 3;
Nested DELETE Queries

Delete with a Subquery in the WHERE Clause

You can delete rows based on a condition derived from a subquery.

DELETE FROM employees

WHERE id IN (
SELECT id
FROM employees
WHERE hire_date < '2000-01-01');

This deletes all employees who were hired before 2000-01-01 by selecting their IDs
from the subquery.
Nested INSERT Queries

Insert with a Subquery

You can insert values into a table using a subquery to select values from another table.

INSERT INTO employee_backup (employee_id, name, salary)

SELECT id, name, salary
FROM employees
WHERE department_id = 3;

This inserts all employees from department 3 into the employee_backup table.
Advanced Nested Query (Using EXISTS and NOT EXISTS)

Using EXISTS in Subqueries

EXISTS checks if a subquery returns any rows. It’s typically used to test for existence.

SELECT name
FROM customers c
WHERE EXISTS ( SELECT *
FROM orders o WHERE o.customer_id = c.id);

This query selects all customers who have placed at least one order by
checking for the existence of related rows in the orders table.
Using NOT EXISTS in Subqueries
NOT EXISTS works similarly, but returns rows where the subquery does not return

SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id);

This query selects customers who have never placed an order.

Nested Query for Aggregation with GROUP BY

Using Nested Queries for Grouped Data Analysis

SELECT department_id, COUNT(*) AS num_employees

FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'New York')
GROUP BY department_id;

This query first finds the departments located in New York and then counts the
employees in those departments.
These nested queries allows to break down complex problems into
manageable steps and help you manipulate data more flexibly.
1. List the names of employees who earn more than the average salary in their department.
2. Find the departments that have more than 5 employees.
3. Retrieve the names of customers who have not placed any orders.
4. Display the employee names who earn more than the highest salary in the HR department.
5. Find the names of employees who have been with the company for longer than the average
tenure.
6. Get the total sales for each department, and only display those departments with total
sales greater than 1,000,000.
7. Find the names of employees who work in the same department as the employee with the
highest salary.
8. Update the salary of employees in the 'Sales' department to the average salary of their
department.
9. Delete all products that have not been sold in the past 6 months.
10. Insert records into an 'archived_orders' table for orders older than 1 year.
 Assertions in SQL
What is an Assertion in SQL?

An ASSERTION is a database-level constraint that enforces a condition over one or more

tables. It ensures that the condition always holds true in the database.

CREATE ASSERTION assertion_name

CHECK (condition);

CREATE ASSERTION positive_balance

CHECK (
NOT EXISTS (
SELECT * FROM accounts WHERE balance < 0 ));
In SQL, assertions are generally implemented using constraints (e.g., CHECK, NOT NULL,
UNIQUE), and triggers are used to perform actions when certain events happen in the
database.
CHECK Constraint (Assertion in SQL)

● Purpose: A CHECK constraint is used to enforce a condition that must always be true for
the values in a column.

● Example: Ensuring a balance is always greater than or equal to 0 in an accounts table.

CREATE TABLE accounts (account_id INT PRIMARY KEY,account_name

VARCHAR(100), balance DECIMAL(10, 2),CONSTRAINT balance_check CHECK
(balance >= 0));
NOT NULL Constraint (Assertion in SQL)

● Purpose: Ensures that a column cannot have a NULL value.

● Example: Enforcing that a username column in a users table must

always have a value.

CREATE TABLE users (

user_id INT PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
UNIQUE Constraint (Assertion in SQL)

● Purpose: Ensures that all values in a column are unique across rows.

● Example: Ensuring that an email is unique for each user.

CREATE TABLE users (

user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
name VARCHAR(100)
);
Triggers in SQL
Triggers are used to automatically enforce rules or perform actions when certain
events (INSERT, UPDATE, DELETE) occur on a table.

BEFORE Trigger: Preventing Invalid Updates

● Purpose: A BEFORE trigger can be used to prevent invalid data changes before
they are committed to the database.

● Example: Preventing negative balances in an accounts table before an

update is made.
CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Balance cannot be negative');
END IF;
END;

This trigger checks if the new balance value (represented as NEW.balance) is

negative. If it is, the update is rejected, and an error is raised with a custom message.
AFTER Trigger: Logging Changes

● Purpose: An AFTER trigger can be used to perform an action after an

insert, update, or delete operation, such as logging changes to an
audit table.

● Example: Logging changes to a transactions table.

CREATE TABLE transaction_log (transaction_id INT PRIMARY KEY,action VARCHAR(50),
old_balance DECIMAL(10, 2), new_balance DECIMAL(10, 2),
transaction_date TIMESTAMP);

CREATE TRIGGER log_balance_change

AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
INSERT INTO transaction_log (transaction_id, action, old_balance, new_balance,
transaction_date)
VALUES (accounts.account_id, 'UPDATE', OLD.balance, NEW.balance, SYSDATE);
END;
Explanation: The log_balance_change trigger fires after any update on the accounts table, logging t
old_balance, new_balance, and the type of action performed to a transaction_log table.
BEFORE Trigger: Preventing Specific Deletions

● Purpose: A trigger can be used to prevent deletion of data if certain

conditions are met.

● Example: Preventing the deletion of a critical admin user.

CREATE TRIGGER prevent_admin_deletion
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
IF OLD.username = 'admin' THEN
RAISE_APPLICATION_ERROR(-20002, 'Cannot delete the admin user');
END IF;
END;

Explanation: This BEFORE DELETE trigger checks if the user being deleted has the
username "admin". If it does, it raises an error, preventing the deletion from proceeding.
Triggers:
Structure:
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW
BEGIN
IF <condition> THEN
<action>;
END IF;
END;
 Example:
CREATE TABLE accounts (
account_id INT PRIMARY KEY, balance DECIMAL(10, 2));
Trigger:
CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
-- Condition: if the new balance is negative
IF NEW.balance < 0 THEN
-- Action: raise error and block the operation
RAISE_APPLICATION_ERROR(-20001, 'Balance cannot be negative');
END IF;
END;
Note:
Event is always required.

Condition is optional — if omitted, the action always runs.

Action must be valid SQL statements; in PL/SQL-style databases (like

Oracle), you can use control structures (IF, LOOP, etc.).