1) Illustrate Use of DDL Commands:
1. CREATE
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
EnrollmentDate DATE
);
2. ALTER
ALTER TABLE Students
ADD Email VARCHAR(100);
3. DROP
DROP TABLE Students;
4. TRUNCATE
TRUNCATE TABLE Students;
5. RENAME (Optional in some systems like MySQL, Oracle)
RENAME TABLE Students TO Learners;
2) Illustrate Use of Select Command by using of possible Clauses:
The basic syntax of the select statement is −
Select column 1, column 2 ... column N
From table_name
An example of the select statement is −
<Student>
Student_Major
Student_Number Student_Name Student_Phone Student_Marks
Subject
1 Andrew 6615927284 95 Literature
2 Sara 6583654865 65 Maths
3 Harry 4647567463 48 Literature
4 Sally 6537837084 30 Literature
5 Anne 7457337732 88 Maths
Query −
Select Student_Name From Student
This query yields the following result −
Student_Name
Andrew
Sara
Harry
Sally
Anne
Clauses in Select statement
00:08
Where
The where clause is used to filter out data i.e it returns information that satisfies a certain
condition. For example −
Select Student_Name
From Student
Where Student_Marks >50
This query will return the following result:
Student_Name
Andrew
Sara
Anne
Group by
This is mostly used with aggregate functions to group the result set according to the value of a
column. For example −
Select Count (Student_Number), Student_MajorSubject
From Student
Group by Student_MajorSubject
This query will return the following result −
Count (Student_number) Student_MajorSubject
3 Literature
2 Maths
Having
This is used along with Group By clause because Where clause could not be used by aggregate
functions. For Example −
Select Count(Student_number), Student_MajorSubject From Student
Group by Student_MajorSubject
Having Count(Student_Number) > 2
This query will return the following result −
Count (Student_Number) Student_MajorSubject
3 Literature
Order by
The order by keyword is used to sort the results in ascending or descending order. By default,
the order is assumed to be ascending. For Example −
Select Student_Name
From Student
Where Student_Marks>50
Order by Student_Marks
This query will return the following result −
Student_Name
Sara
Anne
Andrew
3) Illustrate Use of DDL Commands
SQL INNER JOIN
SELECT StudentCourse.COURSE_ID, [Link], [Link] FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:
COURSE_ID NAME AGE
1 HARSH 18
2 PRATIK 19
2 RIYANKA 20
3 DEEP 18
SQL LEFT JOIN
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
NAME COURSE_ID
HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
SQL RIGHT JOIN
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
NAME COURSE_ID
HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
NULL 4
SQL FULL JOIN
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output :
NAME COURSE_ID
HARSH 1
PRATIK 2
RIYANKA 2
DEEP 3
4) Illustrate Use of DDL Commands
COUNT(): Counts the number of rows in each group.
SELECT column1, COUNT(*) as row_count
FROM table_name
GROUP BY column1;
SUM(): Calculates the sum of values in a column for each group.
SELECT column1, SUM(column2) as total_sum
FROM table_name
GROUP BY column1;
AVG(): Computes the average of values in a column for each group.
SELECT column1, AVG(column2) as average_value
FROM table_name
GROUP BY column1;
MAX(): Retrieves the maximum value in a column for each group.
SELECT column1, MAX(column2) as max_value
FROM table_name
GROUP BY column1;
MIN(): Retrieves the minimum value in a column for each group.
SELECT column1, MIN(column2) as min_value
FROM table_name
GROUP BY column1;
5) illustrate queries using possible inbuilt function ,number,, date function
Date Functions:
CURDATE() / CURRENT_DATE(): Returns the current date.
Code
SELECT CURDATE(); -- Example Result: 2025-10-07
NOW(): Returns the current date and time.
Code
SELECT NOW(); -- Example Result: 2025-10-07 14:28:00
YEAR(): Extracts the year from a date.
Code
SELECT YEAR('2023-08-15'); -- Result: 2023
MONTH(): Extracts the month from a date (1-12).
Code
SELECT MONTH('2023-08-15'); -- Result: 8
DAY(): Extracts the day of the month from a date.
Code
SELECT DAY('2023-08-15'); -- Result: 15
DATE_ADD() / ADDDATE(): Adds a specified time interval to a date.
Code
SELECT DATE_ADD('2025-10-07', INTERVAL 5 DAY); -- Result: 2025-10-12
DATEDIFF(): Calculates the difference in days between two dates.
Code
SELECT DATEDIFF('2025-10-10', '2025-10-07'); -- Result: 3
Number Functions:
ABS() (Absolute Value): Returns the absolute value of a number.
Code
SELECT ABS(-10); -- Result: 10
ROUND(): Rounds a number to a specified number of decimal places.
Code
SELECT ROUND(123.456, 2); -- Result: 123.46
CEIL() / CEILING(): Returns the smallest integer greater than or equal to a number.
Code
SELECT CEIL(123.45); -- Result: 124
FLOOR(): Returns the largest integer less than or equal to a number.
Code
SELECT FLOOR(123.99); -- Result: 123
POWER(): Raises a number to a specified power.
Code
SELECT POWER(2, 3); -- Result: 8 (2 raised to the power of 3)
6) Design program to find sum of N number
#include <iostream>
int sumOfNaturalNumbers(int n) {
if (n == 0) {
return 0; // Base case
}
return n + sumOfNaturalNumbers(n - 1); // Recursive call
}
int main() {
int n;
std::cout << "Enter a positive integer N: ";
std::cin >> n;
int sum = sumOfNaturalNumbers(n);
std::cout << "The sum of the first " << n << " natural numbers is: " << sum << std::endl;
return 0;
}
7) Design program to find Factorial of N
#include <iostream>
int main() {
int n;
long long factorial = 1; // Use long long to handle larger factorials
std::cout << "Enter a non-negative integer: ";
std::cin >> n;
if (n < 0) {
std::cout << "Factorial is not defined for negative numbers." << std::endl;
} else if (n == 0) {
std::cout << "Factorial of 0 is 1." << std::endl;
} else {
for (int i = 1; i <= n; ++i) {
factorial *= i;
}
std::cout << "Factorial of " << n << " is " << factorial << std::endl;
}
return 0;
}
8) Design program to find greatest amongst three given number
#include <iostream> // For input/output operations
#include <algorithm> // For using the std::max function
int main() {
int num1, num2, num3;
std::cout << "Enter three numbers: ";
std::cin >> num1 >> num2 >> num3;
int greatest = std::max({num1, num2, num3}); // C++11 and later
std::cout << "The greatest number is: " << greatest << std::endl;
return 0;
}
9) Implementation of insertion technique in array
#include <iostream>
void insertElement(int arr[], int& size, int element, int position, int capacity) {
if (size >= capacity) {
std::cout << "Error: Array is full. Cannot insert more elements." << std::endl;
return;
}
if (position < 0 || position > size) {
std::cout << "Error: Invalid position for insertion." << std::endl;
return;
}
for (int i = size - 1; i >= position; --i) {
arr[i + 1] = arr[i];
}
arr[position] = element;
size++;
}
void printArray(const int arr[], int size) {
for (int i = 0; i < size; ++i) {
std::cout << arr[i] << " ";
}
std::cout << std::endl;
}
int main() {
const int MAX_CAPACITY = 10; // Maximum capacity of the array
int arr[MAX_CAPACITY] = {10, 20, 30, 40, 50}; // Initial array
int currentSize = 5; // Current number of elements in the array
std::cout << "Original array: ";
printArray(arr, currentSize);
insertElement(arr, currentSize, 25, 2, MAX_CAPACITY);
std::cout << "Array after inserting 25 at position 2: ";
printArray(arr, currentSize);
insertElement(arr, currentSize, 5, 0, MAX_CAPACITY);
std::cout << "Array after inserting 5 at position 0: ";
printArray(arr, currentSize);
insertElement(arr, currentSize, 100, 10, MAX_CAPACITY);
insertElement(arr, currentSize, 60, 6, MAX_CAPACITY);
insertElement(arr, currentSize, 70, 7, MAX_CAPACITY);
insertElement(arr, currentSize, 80, 8, MAX_CAPACITY);
std::cout << "Array before attempting to insert into a full array: ";
printArray(arr, currentSize);
insertElement(arr, currentSize, 90, 9, MAX_CAPACITY);
std::cout << "Array after attempting to insert into a full array: ";
printArray(arr, currentSize);
return 0;
}
10) Implementation of deletion technique in array
#include <iostream>
void deleteElement(int arr[], int& size, int indexToDelete) {
// Check if the index is valid
if (indexToDelete < 0 || indexToDelete >= size) {
std::cout << "Invalid index for deletion." << std::endl;
return;
}
// Shift elements to the left to fill the gap
for (int i = indexToDelete; i < size - 1; ++i) {
arr[i] = arr[i + 1];
}
size--;
}
void printArray(const int arr[], int size) {
std::cout << "Array elements: ";
for (int i = 0; i < size; ++i) {
std::cout << arr[i] << " ";
}
std::cout << std::endl;
}
int main() {
int arr[10] = {10, 20, 30, 40, 50}; // Example array
int size = 5; // Current number of elements
printArray(arr, size);
int indexToDelete = 2; // Index of the element to delete (value 30)
std::cout << "Deleting element at index " << indexToDelete << std::endl;
deleteElement(arr, size, indexToDelete);
printArray(arr, size);
indexToDelete = 0; // Deleting the first element (value 10)
std::cout << "Deleting element at index " << indexToDelete << std::endl;
deleteElement(arr, size, indexToDelete);
printArray(arr, size);
indexToDelete = 10; // Invalid index
std::cout << "Attempting to delete at invalid index " << indexToDelete << std::endl;
deleteElement(arr, size, indexToDelete);
return 0;
}
11) Implementation of data deletion in binary search tree
BinaryTreeNode<int>*
deleteData(BinaryTreeNode<int>* root, int data)
{
//BASE CASE
if(root == NULL)
return NULL;
//IF WE REACHED THE NODE TO BE DELETED
if(data == root->data)
{
//CASE OF LEAF NODE
if(!root->right && !root->left)
{
delete root;
return NULL;
}
//CASE OF ONLY ONE CHILDREN OF THE NODE TO BE DELETED
if(!root->right)
{
root = root->left;
return root;
}
else if(!root->left)
{
root = root->right;
return root;
}
else
{
//WHEN THE NODE CONTAINS BOTH THE CHILDREN
BinaryTreeNode<int>* minNode = root->right;
while(minNode->left!= NULL)
{
minNode = minNode->left;
}
//PLACE THE LEFTMOST DATA OF NODE-RIGHT AS ROOT AND DELETE
THE LEFTMOST CHILDREN OF ROOT,RIGHT
int rightmin = minNode->data;
root->data = rightmin;
root->right = deleteData(root->right, rightmin);
return root;
}
return root;
}
//ON THE WAY TO FIND THE NODE TO BE DELETED
if(data < root->data && root->left)
{
root->left = deleteData(root->left, data);
return root;
}
else if(data > root->data && root->right)
{
root->right = deleteData(root->right, data);
return root;
}
}