1.
Write and execute any four DDL commands
-- Create a table
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
-- Add a new column
ALTER TABLE Students ADD Gender VARCHAR(10);
-- Rename the table
ALTER TABLE Students RENAME TO Learners;
-- Drop the table
DROP TABLE Learners;
---
2. Create a table and execute any four DML commands
-- Create a table
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2)
);
-- Insert data
INSERT INTO Employees VALUES (1, 'Alice', 50000.00);
INSERT INTO Employees VALUES (2, 'Bob', 45000.00);
-- Update data
UPDATE Employees SET Salary = 48000.00 WHERE ID = 2;
-- Delete data
DELETE FROM Employees WHERE ID = 1;
-- Select data
SELECT * FROM Employees;
---
3. Create a table, insert two rows, and perform a TCL command
-- Create a table
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
-- Insert data
INSERT INTO Departments VALUES (1, 'HR');
INSERT INTO Departments VALUES (2, 'Finance');
-- Commit the transaction
COMMIT;
---
4. Create a table, insert two rows, and execute two queries with arithmetic and logical operators
-- Create a table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10, 2),
Stock INT
);
-- Insert data
INSERT INTO Products VALUES (1, 100.00, 50);
INSERT INTO Products VALUES (2, 200.00, 30);
-- Arithmetic operator
SELECT Price * Stock AS TotalValue FROM Products;
-- Logical operator
SELECT * FROM Products WHERE Price > 150 AND Stock > 20;
---
5. Create a table, insert two rows, and execute queries with relational and set operators
-- Create a table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Amount DECIMAL(10, 2)
);
-- Insert data
INSERT INTO Orders VALUES (1, 300.00);
INSERT INTO Orders VALUES (2, 150.00);
-- Relational operator
SELECT * FROM Orders WHERE Amount < 200;
-- Set operators
CREATE TABLE Orders2 (
OrderID INT PRIMARY KEY,
Amount DECIMAL(10, 2)
);
INSERT INTO Orders2 VALUES (3, 400.00);
INSERT INTO Orders2 VALUES (4, 150.00);
-- UNION
SELECT * FROM Orders UNION SELECT * FROM Orders2;
-- INTERSECT (works in some databases like Oracle)
SELECT * FROM Orders INTERSECT SELECT * FROM Orders2;
---
6. Create a table, insert two rows, and execute queries with set operators
-- Same setup as above
SELECT * FROM Orders UNION SELECT * FROM Orders2;
SELECT * FROM Orders INTERSECT SELECT * FROM Orders2;
SELECT * FROM Orders EXCEPT SELECT * FROM Orders2; -- MINUS in Oracle
SELECT * FROM Orders UNION ALL SELECT * FROM Orders2;
---
7. Create a table, insert two rows, and execute queries with string functions
-- Create a table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50)
);
-- Insert data
INSERT INTO Customers VALUES (1, 'Alice');
INSERT INTO Customers VALUES (2, 'Bob');
-- String functions
SELECT UPPER(Name) AS UpperName FROM Customers;
SELECT LENGTH(Name) AS NameLength FROM Customers;
SELECT SUBSTRING(Name, 1, 2) AS NamePrefix FROM Customers;
SELECT CONCAT(Name, ' Smith') AS FullName FROM Customers;
---
8. Create a table, insert two rows, and execute queries with arithmetic functions
-- Create a table
CREATE TABLE MathOps (
ID INT PRIMARY KEY,
Value1 INT,
Value2 INT
);
-- Insert data
INSERT INTO MathOps VALUES (1, 10, 20);
INSERT INTO MathOps VALUES (2, 30, 40);
-- Arithmetic functions
SELECT ABS(Value1 - Value2) AS AbsoluteDifference FROM MathOps;
SELECT POWER(Value1, 2) AS Square FROM MathOps;
SELECT SQRT(Value2) AS SquareRoot FROM MathOps;
SELECT CEIL(Value1 / 3.0) AS CeilingValue FROM MathOps;
---
9. Create a table, insert two rows, and execute queries using date and time functions
-- Create a table
CREATE TABLE Events (
EventID INT PRIMARY KEY,
EventDate DATE
);
-- Insert data
INSERT INTO Events VALUES (1, '2024-01-01');
INSERT INTO Events VALUES (2, '2024-02-15');
-- Date functions
SELECT CURRENT_DATE AS Today;
SELECT DATEDIFF('2024-12-31', EventDate) AS DaysUntil FROM Events;
---
10. Create a table, insert two rows, and execute queries using aggregate functions
-- Create a table
CREATE TABLE Scores (
StudentID INT PRIMARY KEY,
Score INT
);
-- Insert data
INSERT INTO Scores VALUES (1, 85);
INSERT INTO Scores VALUES (2, 90);
-- Aggregate functions
SELECT SUM(Score) AS TotalScore FROM Scores;
SELECT AVG(Score) AS AverageScore FROM Scores;
SELECT MAX(Score) AS HighestScore FROM Scores;
SELECT MIN(Score) AS LowestScore FROM Scores;
---
11. Query with GROUP BY and HAVING
SELECT StudentID, AVG(Score) AS AverageScore FROM Scores
GROUP BY StudentID
HAVING AVG(Score) > 80;
---
12. View and drop the view
CREATE VIEW HighScores AS
SELECT * FROM Scores WHERE Score > 80;
SELECT * FROM HighScores;
DROP VIEW HighScores;
---
13. PL/SQL program using conditional statement
DECLARE
num INT := 10;
BEGIN
IF num > 5 THEN
DBMS_OUTPUT.PUT_LINE('Number is greater than 5');
ELSE
DBMS_OUTPUT.PUT_LINE('Number is 5 or less');
END IF;
END;
---
14. PL/SQL program with exception handling
DECLARE
num INT := 10;
denom INT := 0;
result INT;
BEGIN
result := num / denom;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero error!');
END;
---
15. Function to find maximum of two numbers
CREATE FUNCTION MaxValue(a INT, b INT)
RETURN INT IS
BEGIN
RETURN CASE WHEN a > b THEN a ELSE b END;
END;
---
16. Function to add two numbers
CREATE FUNCTION AddValues(a INT, b INT)
RETURN INT IS
BEGIN
RETURN a + b;
END;
---
17. Procedure to insert one row into employees table
CREATE PROCEDURE InsertEmployee (empID INT, empName VARCHAR)
IS
BEGIN
INSERT INTO Employees VALUES (empID, empName, NULL);
END;
