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');
