Create table student and execute all DDL Commands

-- Step 1: Create Table

CREATE TABLE student (

student_id INT PRIMARY KEY,

name VARCHAR(50),

age INT,

gender CHAR(1),

grade VARCHAR(10)

);

-- Step 2: Alter Table - Add a new column

ALTER TABLE student ADD address VARCHAR(100);

-- Alter Table - Modify an existing column

ALTER TABLE student MODIFY age TINYINT;

-- Alter Table - Drop an existing column

ALTER TABLE student DROP COLUMN grade;

-- Step 3: Rename Table

RENAME TABLE student TO student_info;

-- Step 4: Truncate Table

TRUNCATE TABLE student_info;

-- Step 5: Drop Table

DROP TABLE student_info;

Create table employee and execute all DML Commands. Create user also grant and revoke
privileges from user

-- Step 1: Create the Employee Table

CREATE TABLE employee (

emp_id INT PRIMARY KEY,

name VARCHAR(50),

position VARCHAR(50),

salary DECIMAL(10, 2),

department VARCHAR(50)

);

-- Step 2: DML Commands

-- 2.1: INSERT data into the table

INSERT INTO employee (emp_id, name, position, salary, department)

VALUES

(1, 'Alice', 'Manager', 75000, 'HR'),

(2, 'Bob', 'Developer', 60000, 'IT'),

(3, 'Charlie', 'Analyst', 50000, 'Finance');

-- 2.2: UPDATE data in the table

UPDATE employee

SET salary = salary * 1.1

WHERE department = 'IT';

-- 2.3: DELETE data from the table

DELETE FROM employee

WHERE emp_id = 3;

-- 2.4: SELECT data from the table

SELECT * FROM employee;

-- Step 3: Create a User

CREATE USER 'employee_user'@'localhost' IDENTIFIED BY 'password123';

-- Step 4: Grant Privileges

-- Grant SELECT and INSERT privileges on the 'employee' table to the user

GRANT SELECT, INSERT ON employee TO 'employee_user'@'localhost';

-- Step 5: Revoke Privileges

-- Revoke INSERT privilege from the user

REVOKE INSERT ON employee FROM 'employee_user'@'localhost';

-- Step 6: Verify Privileges

-- Display user privileges

SHOW GRANTS FOR 'employee_user'@'localhost';

Create table student and add 5 records also create view on student and insert, modify
delete record through view.

-- Create the student table

CREATE TABLE student (

roll_no INT PRIMARY KEY,

name VARCHAR(50),

age INT,

department VARCHAR(50)

);

-- Insert 5 records

INSERT INTO student VALUES (1, 'Alice', 20, 'Science');

INSERT INTO student VALUES (2, 'Bob', 22, 'Commerce');

INSERT INTO student VALUES (3, 'Charlie', 21, 'Arts');

INSERT INTO student VALUES (4, 'David', 23, 'Science');

INSERT INTO student VALUES (5, 'Eve', 20, 'Commerce');

-- Create a view

CREATE VIEW student_view AS

SELECT roll_no, name, department FROM student;

-- Insert through view

INSERT INTO student_view (roll_no, name, department)

VALUES (6, 'Frank', 'Engineering');

-- Update through view

UPDATE student_view

SET name = 'Alice Updated'

WHERE roll_no = 1;

-- Delete through view

DELETE FROM student_view

WHERE roll_no = 2;

-- Display the data

SELECT * FROM student;

Create table Employee and Department. Also assign primary key in employee table and
foreign key in Department table add 5 records in Department table.

-- Create Department table

CREATE TABLE department (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50)

);

-- Insert 5 records into Department

INSERT INTO department VALUES (1, 'HR');

INSERT INTO department VALUES (2, 'IT');

INSERT INTO department VALUES (3, 'Finance');

INSERT INTO department VALUES (4, 'Marketing');

INSERT INTO department VALUES (5, 'Operations');

-- Create Employee table with a foreign key to Department

CREATE TABLE employee (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES department(dept_id)

);

Create table employee, add 5 records also use like operator 5 different ways to display
name of employee.

-- Insert records into Employee table

INSERT INTO employee VALUES (1, 'Alice', 1);

INSERT INTO employee VALUES (2, 'Bob', 2);

INSERT INTO employee VALUES (3, 'Charlie', 3);

INSERT INTO employee VALUES (4, 'David', 4);

INSERT INTO employee VALUES (5, 'Eve', 5);

-- Use LIKE operator

SELECT * FROM employee WHERE emp_name LIKE 'A%'; -- Starts with A

SELECT * FROM employee WHERE emp_name LIKE '%e'; -- Ends with e

SELECT * FROM employee WHERE emp_name LIKE '%a%'; -- Contains a

SELECT * FROM employee WHERE emp_name LIKE '__v%'; -- Two underscores then 'v'

SELECT * FROM employee WHERE emp_name NOT LIKE '%e%'; -- Does not contain e

i) Write a PL/SQL program to print numbers from 50 to 60 using for loop.

BEGIN
FOR i IN 50..60 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

ii) Write a PL/SQL program to display factorial of a given number.

DECLARE

num INT := 5;

fact INT := 1;

BEGIN
FOR i IN 1..num LOOP

fact := fact * i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is ' || fact);

END;

i) Write a PL/SQL program to print numbers largest number from three numbers.

DECLARE

a INT := 10;
b INT := 20;
c INT := 15;
largest INT;
BEGIN
IF a > b AND a > c THEN
largest := a;
ELSIF b > c THEN
largest := b;
ELSE
largest := c;
END IF;
DBMS_OUTPUT.PUT_LINE('Largest number is: ' || largest);
END;

iii) Write a PL/SQL program to print numbers from 1 to 50 using while loop.

DECLARE

i INT := 1;

BEGIN

WHILE i <= 50 LOOP

DBMS_OUTPUT.PUT_LINE(i);

i := i + 1;

END LOOP;

END;

Use 5 arithmetic function,5 string functions and date time function

-- Arithmetic functions

SELECT ABS(-5), POWER(2, 3), MOD(10, 3), FLOOR(10.8), CEIL(10.2);

-- String functions
SELECT LENGTH('Hello'), UPPER('hello'), LOWER('WORLD'), SUBSTR('Oracle', 2, 3), INSTR('Hello
World', 'o');

-- Date-Time functions

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2), LAST_DAY(SYSDATE),

MONTHS_BETWEEN(SYSDATE, '2024-01-01'), NEXT_DAY(SYSDATE, 'FRIDAY');

I) Write a PL/SQL program to print even number between 1 to 100 numbers.

DECLARE
i INT := 1;
BEGIN
WHILE i <= 100 LOOP
IF MOD(i, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
i := i + 1;
END LOOP;
END;
i) Write a program to handle divide by zero exception.
DECLARE
num1 INT := 10;
num2 INT := 0;
result INT;
BEGIN
result := num1 / num2;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
ii) Write a PL/SQL program which accept the customer ID from the user if user
enters an invalid ID then the exception invalid_id is raised using exception
handling.
DECLARE
customer_id INT := 101; -- Input value
invalid_id EXCEPTION;
BEGIN
IF customer_id NOT IN (100, 200, 300) THEN
RAISE invalid_id;
END IF;
DBMS_OUTPUT.PUT_LINE('Valid Customer ID');
EXCEPTION
WHEN invalid_id THEN
DBMS_OUTPUT.PUT_LINE('Error: Invalid Customer ID');
END;

Consider the table Employee( Eid, Ename, Ecity, Ejob, dept, salary) write SQL query for
following:
i) Insert a record of employee

INSERT INTO Employee (Eid, Ename, Ecity, Ejob, dept, salary)

VALUES (1, 'John', 'New York', 'Manager', 'HR', 75000);

ii) Modify the name of employee “Raj” to “Rajesh.

UPDATE Employee

SET Ename = 'Rajesh'

WHERE Ename = 'Raj';

iii) Delete employee details who works in marketing department

DELETE FROM Employee

WHERE dept = 'Marketing';

iv) Display details of employee who works as clerk, salesman or manager.

SELECT * FROM Employee

WHERE Ejob IN ('Clerk', 'Salesman', 'Manager');

Consider the table Student (name, marks, dept, age, place, phone, birthdate) Write SQL
query for following :

i)To list students having place as ‘Pune’ or ‘Jalgaon’.

SELECT * FROM Student

WHERE place IN ('Pune', 'Jalgaon');

ii)To list students having same department (dept) as that of ‘Rachana’.

SELECT * FROM Student

WHERE dept = (SELECT dept FROM Student WHERE name = 'Rachana');

(iii)To change marks of ‘Rahul’ from 81 to 96.

UPDATE Student

SET marks = 96

WHERE name = 'Rahul' AND marks = 81;

(iv) To list student name and marks from ‘Computer’ dept.

SELECT name, marks

FROM Student

WHERE dept = 'Computer';

(v) To list student name who have marks less than 40.

SELECT * FROM Student

WHERE marks < 40;

(vi) To list students who are not from ‘Mumbai’.

SELECT * FROM Student

WHERE place <> 'Mumbai';

Consider the structure for book table as Book-Master (bookid, bookname, author, no_of
copies, price) Write down SQL queries for following:

(i) Write a command to create Book_master table.

SELECT * FROM Student

WHERE place <> 'Mumbai';

(ii) Get authorwise list of all books.

SELECT author, COUNT(*) AS total_books

FROM Book_Master

GROUP BY author;

(iii) Display all books whose price is between ` 500 & ` 800.

SELECT * FROM Book_Master

WHERE price BETWEEN 500 AND 800;

(iv) Display all books with details whose name start with ‘D’.

SELECT * FROM Book_Master

WHERE bookname LIKE 'D%';

(v) (v) Display all books whose price is above ` 700.

SELECT * FROM Book_Master

WHERE price > 700;

(vi) Display all books whose number of copies are less than 10.
SELECT * FROM Book_Master
WHERE no_of_copies < 10;

Consider following schema :

EMP (empno, deptno, ename, salary, designation, join_date, DOB, dept_location). Write
down SQL queries for following:

(i) Display employees name & number in decreasing order of salary.

SELECT ename, empno

FROM EMP

ORDER BY salary DESC;

(ii) Display employee name & employee number whose designation is Manager.

SELECT ename, empno

FROM EMP

WHERE designation = 'Manager';

(iii) Display age of employees with ename.

SELECT ename, FLOOR(MONTHS_BETWEEN(SYSDATE, DOB) / 12) AS age

FROM EMP;

(iv) Display total salary of all employees.

SELECT SUM(salary) AS total_salary

FROM EMP;

(v) Display employee names having deptno as 20 and dept_location is Mumbai.

SELECT ename

FROM EMP

WHERE deptno = 20 AND dept_location = 'Mumbai';

(vi) Display name of employee who earned lowest salary.

SELECT ename
FROM EMP
WHERE salary = (SELECT MIN(salary) FROM EMP);

Write a command to create table student (rollno, Stud_name, branch, class, DOB, City,
Contact_no) and write down queries for following:

(i) Insert one row into the table

CREATE TABLE Student (

rollno INT PRIMARY KEY,

stud_name VARCHAR(50),

branch VARCHAR(50),

class VARCHAR(20),

DOB DATE,

city VARCHAR(50),

contact_no VARCHAR(15)

);

(ii) Save the data

INSERT INTO Student (rollno, stud_name, branch, class, DOB, city, contact_no)
VALUES (1, 'Alice', 'Computer Science', 'First Year', '2002-06-15', 'Pune',
'9876543210');

(iii) Insert second row into the table

COMMIT;

(iv) (iv) Undo the insertion of second row

INSERT INTO Student (rollno, stud_name, branch, class, DOB, city, contact_no)

VALUES (2, 'Bob', 'Mechanical', 'Second Year', '2001-08-12', 'Mumbai', '8765432109');

(v) (v) Create save point S1.

ROLLBACK;

(vi) Insert one row into the table.

SAVEPOINT S1;

INSERT INTO Student (rollno, stud_name, branch, class, DOB, city, contact_no)
VALUES (3, 'Charlie', 'Electrical', 'Third Year', '2000-03-22', 'Nashik', '7654321098');