1|Page

BACHELORS IN TECHNOLOGY
FROM
SHADAN COLLEGE OF ENGINEERING &
TECHNOLOGY

NAME : NABEEL URREHMAN

ROLL NO : 22081A1210
BRANCH : I. T. – II YR. – II SEM.
SUBJECT : D. B. M. S. PROJECT
SUBMITTED TO : DR. NAZIA TAZEEN
2|Page

TOPIC
Q1. Explain with DDL, and DML
Commands
(i) Aggregate Functions
(ii) Integrity Constraints

CONTEXT PAGE NO.

Introduction of DDL 3-6
Commands
Introduction of DML 6-10
Commands
Aggregate Functions with 13-16
DDL Commands
Aggregate Functions with 17-20
DML Commands
Integrity Constraints with 24-27
DDL Commands
Integrity Constraints with 27-31
DML Commands
3|Page

DDL COMMANDS:

Data Definition Language (DDL) commands are used

to define and manage the structure of database objects.
These commands are essential for creating, altering, and
deleting database objects such as tables, indexes, and
databases.
Here are the main DDL commands along with their
definitions:
1. CREATE:

The CREATE command is used to create new database

objects. These objects can include databases, tables,
indexes, views, and more.

Definition
Database: Creates a new database.
 Table: Creates a new table with specified columns
and constraints.
 Index: Creates an index on specified columns of a
table

.
EXAMPLE QUERY
4|Page

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
emp_email VARCHAR(100) UNIQUE,
emp_salary DECIMAL (10, 2) CHECK
(emp_salary > 0),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

2. ALTER:

The ALTER command is used to modify the

structure of existing database objects. This can
include adding, modifying, or dropping columns in
a table, as well as adding or dropping constraints.

Definition
 Table: Modifies the structure of an existing table by
adding, modifying, or dropping columns and
constraints.

EXAMPLE QUERY
5|Page

ALTER TABLE employees ADD COLUMN

emp_age INT;

3. DROP:

The DROP command is used to delete existing

database objects. This can include databases,
tables, and indexes.

Definition
 Database: Deletes a database and all its objects.
 Table: Deletes a table and all its data.
 Index: Deletes an index.

EXAMPLE QUERY

DROP TABLE employees;

4. TRUNCATE:

The TRUNCATE command is used to remove all

records from a table but keeps the table structure
intact. It is faster than the DELETE command
because it does not generate individual row delete
operations.

Definition
6|Page

 Table: Removes all rows from a table, resetting any

auto-increment counters.

EXAMPLE QUERY

TRUNCATE TABLE employees;

5. RENAME:

The RENAME command is used to rename

existing database objects, such as tables or
columns.

Definition
 Table: Renames a table.
 Column: Renames a column in a table.

EXAMPLE QUERY

ALTER TABLE employees RENAME TO staff;

DML COMMANDS:
Data Manipulation Language (DML) commands
are used to manage and manipulate the data stored
7|Page

in database objects such as tables. These commands

allow you to perform various operations on the
data, including inserting, updating, deleting, and
retrieving records.
Here are the main DML commands along with their
definitions:

1. INSERT:

The INSERT command is used to add new records

(rows) to a table.

Definition
 Table: Adds new rows of data to a specified
table.

EXAMPLE QUERY

INSERT INTO employees (emp_id,

emp_name, emp_email, emp_salary,
department_id)
VALUES (1, 'John Doe',
'[email protected]', 50000, 10);

2. SELECT:
8|Page

The SELECT command is used to retrieve data

from one or more tables. It allows you to
specify which columns to return, filter rows
based on conditions, and perform sorting and
aggregation.

Definition
 Table: Retrieves data from specified columns
and rows in one or more tables.

EXAMPLE QUERY

SELECT emp_name, emp_email, emp_salary

FROM employees WHERE department_id =
10;

3. UPDATE:

The UPDATE command is used to modify

existing records in a table. It allows you to
update the values of specified columns for
rows that meet certain conditions.

Definition
 Table: Modifies the values of existing rows in
a specified table.
9|Page

EXAMPLE QUERY

UPDATE employees SET emp_salary = 55000

WHERE emp_id = 1;

4. DELETE:

The DELETE command is used to remove

existing records from a table. It allows you to
delete rows that meet certain conditions.

Definition
 Table: Removes specified rows from a table.

EXAMPLE QUERY

DELETE FROM employees WHERE emp_id

= 1;
5. CALL:

The CALL command is used to execute a

stored procedure.

Definition
 Procedure: Executes a specified stored
procedure.
10 | P a g e

EXAMPLE QUERY

CALL update_employee_salary(1, 60000);

6. LOCK TABLE:

The LOCK TABLE command is used to lock a

table or tables to control concurrent access and
prevent conflicts.

Definition
 Table: Locks a specified table or tables to
control access.

EXAMPLE QUERY
LOCK TABLE employees IN EXCLUSIVE
MODE;

(i) AGGREGATE FUNCTIONS:

Aggregate functions are used to perform calculations

on a set of values and return a single value. They are
commonly used in SQL to summarize data.
Here are the main aggregate functions:
11 | P a g e

1. COUNT ():
The COUNT () function returns the number of rows
that match a specified criterion. It is often used to
determine the number of rows in a table or the
number of non-NULL values in a column.

Definition
 COUNT (expression): Returns the number of
non-NULL values in the specified column.
 COUNT (*): Returns the total number of rows
in the table, including rows with NULL values.
2. SUM ():
The SUM () function calculates the total sum of a
numeric column. It adds up all the values in the
specified column.

Definition
 SUM (expression): Returns the sum of all
non-NULL values in the specified column.

3. AVG ():
12 | P a g e

The AVG () function computes the average of a

numeric column. It calculates the sum of all the
values and then divides it by the number of non-
NULL values.

Definition
 AVG (expression): Returns the average of all
non-NULL values in the specified column.

4. MAX ():
The MAX () function returns the maximum value
in a set of values. It finds the highest value in the
specified column.

Definition
 MAX (expression): Returns the maximum
value of all non-NULL values in the
specified column.

5. MIN ():
The MIN () function returns the minimum
value in a set of values. It finds the lowest
value in the specified column.
13 | P a g e

Definition
 MIN (expression): Returns the minimum
value of all non-NULL values in the
specified column.

DDL COMMANDS IN AGGREGATE

FUNCTIONS:
To demonstrate the use of aggregate functions, we'll
need to set up a database structure using DDL
commands. After setting up the structure, we can then
insert data and use aggregate functions to analyze it.
Here’s a step-by-step demonstration:
Step 1: Create the Database and Tables
We'll start by creating a database and a table to store
data that we can later aggregate.

-- Create a database
CREATE DATABASE company;

-- Use the database

USE company;
14 | P a g e

-- Create a table for storing employee sales data

CREATE TABLE employee_sales (
sale_id INT PRIMARY KEY,
emp_id INT,
emp_name VARCHAR (100),
sale_amount DECIMAL (10, 2),
sale_date DATE
);

Step 2: Insert Data into the Table

Next, we'll insert some sample data into the
employee_sales table.

-- Insert sample data into employee_sales table

INSERT INTO employee_sales (sale_id, emp_id,
emp_name, sale_amount, sale_date) VALUES
(1, 101, 'John Doe', 150.75, '2023-01-15'),
(2, 102, 'Jane Smith', 200.00, '2023-01-16'),
(3, 101, 'John Doe', 300.50, '2023-01-17'),
(4, 103, 'Robert Brown', 250.00, '2023-01-18'),
(5, 102, 'Jane Smith', 400.00, '2023-01-19');
15 | P a g e

Step 3: Use Aggregate Functions

Now that we have data in our table, we can demonstrate
the use of various aggregate functions.
1. COUNT ():
The COUNT () function returns the number of rows that
match a specified condition.

-- Count the number of sales

SELECT COUNT (*) AS total_sales FROM
employee_sales;

2. SUM ():
The SUM () function returns the total sum of a numeric
column.

-- Calculate the total sales amount

SELECT SUM (sale_amount) AS total_sales_amount
FROM employee_sales;

3. AVG ():
The AVG () function returns the average value of a
numeric column.
16 | P a g e

-- Calculate the average sale amount

SELECT AVG (sale_amount) AS average_sale_amount
FROM employee_sales;

4. MAX ():
The MAX () function returns the maximum value in a
column.

-- Find the maximum sale amount

SELECT MAX (sale_amount) AS max_sale_amount
FROM employee_sales;

5. MIN ():
The MIN () function returns the minimum value in a
column.

-- Find the minimum sale amount

SELECT MIN (sale_amount) AS min_sale_amount
FROM employee_sales;
17 | P a g e

DML COMMANDS IN AGGREGTE FUNCTIONS:

let's demonstrate the use of aggregate functions with
DML (Data Manipulation Language) commands. We
will start by inserting data into the table and then use
various aggregate functions to analyze this data.
Below is a step-by-step demonstration using SQL.

Step 1: Create the Table and Insert Data

First, we'll create the employee_sales table and insert
sample data. Although creating the table is a DDL
command, it sets the stage for using DML commands.

-- Create the table (DDL command)

CREATE TABLE employee_sales (
sale_id INT PRIMARY KEY,
emp_id INT,
emp_name VARCHAR (100),
sale_amount DECIMAL (10, 2),
sale_date DATE
);

-- Insert sample data into employee_sales table (DML

command)
18 | P a g e

INSERT INTO employee_sales (sale_id, emp_id,

emp_name, sale_amount, sale_date) VALUES
(1, 101, 'John Doe', 150.75, '2023-01-15'),
(2, 102, 'Jane Smith', 200.00, '2023-01-16'),
(3, 101, 'John Doe', 300.50, '2023-01-17'),
(4, 103, 'Robert Brown', 250.00, '2023-01-18'),
(5, 102, 'Jane Smith', 400.00, '2023-01-19');

Step 2: Use Aggregate Functions with SELECT

Statements
Now we will demonstrate the use of various aggregate
functions.

1. COUNT ()
The COUNT () function returns the number of rows that
match a specified criterion.
-- Count the number of sales (DML command)
SELECT COUNT (*) AS total_sales FROM
employee_sales;
2. SUM ():
The SUM () function calculates the total sum of a
numeric column.
19 | P a g e

-- Calculate the total sales amount (DML command)

SELECT SUM (sale_amount) AS total_sales_amount
FROM employee_sales;

3. AVG ():
The AVG () function computes the average of a numeric
column.

-- Calculate the average sale amount (DML command)

SELECT AVG (sale_amount) AS average_sale_amount
FROM employee_sales;

4. MAX ():
The MAX () function returns the maximum value in a
set of values.

-- Find the maximum sale amount (DML command)

SELECT MAX (sale_amount) AS max_sale_amount
FROM employee_sales;

5. MIN ():
The MIN () function returns the minimum value in a set
of values.
20 | P a g e

-- Find the minimum sale amount (DML command)

SELECT MIN (sale_amount) AS min_sale_amount
FROM employee_sales;

(ii) INTEGRITY CONSTRAINTS:

Integrity constraints in databases are rules that

enforce data integrity and consistency. They define
the valid values and relationships that data can have
in tables. These constraints help maintain accuracy,
reliability, and integrity of the data stored in
databases.
21 | P a g e

Here are the main types of integrity constraints

commonly used in databases:

1. Primary Key Constraint:

 Definition: Ensures that each row in a table

is uniquely identified by a specific column
or set of columns. It uniquely identifies each
record in a table and does not allow NULL
values.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR (100),
emp_email VARCHAR (100) UNIQUE
);
2. Foreign Key Constraint:

 Definition: Maintains referential integrity

between two related tables. It ensures that
the values in a column (or a set of columns)
of one table match the values in another
table's primary key or unique key column(s).
22 | P a g e

CREATE TABLE orders (

order_id INT PRIMARY KEY,
order_date DATE,
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)
);

3. Unique Constraint:

 Definition: Ensures that all values in a

column (or a set of columns) are unique (no
duplicates).
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR (100),
product_code VARCHAR (20) UNIQUE
);

4. Check Constraint:
23 | P a g e

 Definition: Validates the values entered into

a column based on a specific condition. It
ensures that data meets certain criteria
before being inserted or updated.

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
emp_name VARCHAR (100),
emp_age INT CHECK (emp_age >= 18)
);

5. Not Null Constraint:

 Definition: Ensures that a column does not
accept NULL values. It requires each row to
have a value for that column.

CREATE TABLE customers (

customer_id INT PRIMARY KEY,
customer_name VARCHAR (100) NOT NULL,
customer_email VARCHAR (100)
);
24 | P a g e

DDL COMMANDS IN INTEGRITY

CONSTRAINTS

Let's use DDL (Data Definition Language) commands

to demonstrate various types of integrity constraints in
SQL. We'll create tables and apply different constraints
to enforce data integrity.
1.PRIMARY KEY CONSTRAINT:

-- Create a table with a primary key constraint

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR (100),
emp_email VARCHAR (100) UNIQUE
);

In this example:
 emp_id is defined as the primary key,
ensuring each emp_id value is unique.
 emp_email has a UNIQUE constraint,
ensuring each email address is unique across
all rows.
25 | P a g e

2.FOREIGN KEY CONSTRAINT:

-- Create a table with a foreign key constraint

CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)
);
Here:

 emp_id in the orders table references emp_id in the

employee’s table.
 This ensures that every emp_id in the orders table
must exist in the employee’s table.

3.UNIQUE KEY CONSTRAINT:

-- Create a table with a unique constraint

CREATE TABLE products (
product_id INT PRIMARY KEY,
26 | P a g e

product_name VARCHAR (100),

product_code VARCHAR (20) UNIQUE
);
This ensures:
 Each product_code must be unique across all rows
in the products table.

4.CHECK KEY CONSTRAINT:

-- Create a table with a check constraint

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR (100),
emp_age INT CHECK (emp_age >= 18)
);
Here:

 The CHECK constraint ensures that the emp_age

column only accepts values greater than or equal to
18.
27 | P a g e

5.NOT NULL CONSTRAINT:

-- Create a table with a not null constraint

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR (100) NOT NULL,
customer_email VARCHAR (100)
);

In this case:
 customer_name cannot contain NULL values,
ensuring every customer has a name.

DML COMMANDS IN INTEGRITY

CONSTRAINTS:

To demonstrate integrity constraints using DML (Data

Manipulation Language) commands, we'll insert data
into tables that have been previously defined with
various integrity constraints.
Here's a step-by-step demonstration using SQL.
28 | P a g e

1.PRIMARY KEY CONSTRAINT:

Let's assume we have a table employee with a primary
key constraint on emp_id.

-- Insert data into employees table with primary key

constraint
INSERT INTO employees (emp_id, emp_name,
emp_email)
VALUES (1, 'John Doe', '[email protected]'),
(2, 'Jane Smith', '[email protected]'),
(3, 'Robert Brown', '[email protected]');

In this example:
 Each emp_id value must be unique due to the
primary key constraint.

2. FOREIGN KEY CONSTRAINT:

Assume we have two tables: employees and orders,
where order has a foreign key constraint referencing
emp_id in employees.

-- Insert data into employees table

29 | P a g e

INSERT INTO employees (emp_id, emp_name,

emp_email)
VALUES (1, 'John Doe', '[email protected]'),
(2, 'Jane Smith', '[email protected]');

-- Insert data into orders table with foreign key

constraint
INSERT INTO orders (order_id, order_date, emp_id)
VALUES (101, '2023-01-01', 1),
(102, '2023-01-02', 2),
(103, '2023-01-03', 3); -- This will fail due to
foreign key constraint violation

Here:
 The third INSERT statement will fail because
emp_id = 3 does not exist in the employee’s table.

3. UNIQUE KEY CONSTRAINT:

Assume we have a products table with a unique
constraint on product_code.

-- Insert data into products table with unique constraint

30 | P a g e

INSERT INTO products (product_id, product_name,

product_code)
VALUES (1, 'Laptop', 'LT001'),
(2, 'Smartphone', 'SP001'),
(3, 'Tablet', 'LT001'); -- This will fail due to unique
constraint violation
In this case:
 The third INSERT statement will fail because
product_code = 'LT001' already exists in the
products table.

4. CHECK KEY CONSTRAINT:

Assume we have an employees table with a check
constraint on emp_age.

-- Insert data into employees table with check constraint

INSERT INTO employees (emp_id, emp_name,
emp_age)
VALUES (1, 'John Doe', 25),
(2, 'Jane Smith', 30),
(3, 'Robert Brown', 17); -- This will fail due to
check constraint violation
31 | P a g e

Here: The third INSERT statement will fail because

emp_age = 17 does not satisfy the CHECK constraint
(emp_age >= 18).

5. NOT NULL KEY CONSTRAINT:

Assume we have a customers table with a NOT NULL

constraint on customer_name.

-- Insert data into customers table with not null

constraint
INSERT INTO customers (customer_id,
customer_name, customer_email)
VALUES (1, 'John Doe', '[email protected]'),
(2, NULL, '[email protected]'); -- This will
fail due to not null constraint violation

In this scenario:

 The second INSERT statement will fail because

customer_name cannot be NULL.