Scribd
Upload
4 views31 pages

DBMS Project Work

The document outlines various SQL tasks including creating and managing employee and salary tables, inserting, updating, and deleting records, as well as creating views and practicing SQL functions. It provides detailed SQL queries for each task, demonstrating operations such as joins, date formatting, and aggregate functions. Additionally, it covers user creation and permissions in Oracle Server.

Uploaded by

parthivisaurabh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views31 pages

DBMS Project Work

The document outlines various SQL tasks including creating and managing employee and salary tables, inserting, updating, and deleting records, as well as creating views and practicing SQL functions. It provides detailed SQL queries for each task, demonstrating operations such as joins, date formatting, and aggregate functions. Additionally, it covers user creation and permissions in Oracle Server.

Uploaded by

parthivisaurabh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Sourabh Sahu [Link].

CS- 3rd Year

Q.1) Design an employee table in oracle server having-


eid (primary key), ename, edesignation, edoj, edob, eaddress, salary, econtact as
fields and answer the following questions;
a) Insert five records in above created table
b) Display all five records
c) Delete the fourth record
d) Update the third record of field ename as 'Hari'
e) Add one new field in the table

Query:-

CREATE TABLE EMPLOYEE (eid NUMBER PRIMARY KEY, ename VARCHAR2(50),


edesignation VARCHAR2(30), edoj DATE, edob DATE, eaddress VARCHAR2(100),
salary NUMBER(10,2), econtact VARCHAR2(15) );

a) Insert five records in above created table


INSERT INTO STAFF VALUES
(1, 'Naveen', 'Manager', DATE '2020-01-10', DATE '1998-05-15', 'Delhi', 60000,
'9876543210');
INSERT INTO STAFF VALUES
(2, 'Vimal', 'Developer', DATE '2021-03-12', DATE '1999-08-20', 'Mumbai', 50000,
'9876543211');
INSERT INTO STAFF VALUES
(3, 'Hemant', 'Tester', DATE '2022-06-18', DATE '2000-02-10', 'Chennai', 45000,
'9876543212');
INSERT INTO STAFF VALUES
(4, 'Pinky', 'HR', DATE '2019-11-05', DATE '1997-09-25', 'Kolkata', 40000,
'9876543213');
INSERT INTO STAFF VALUES
(5, 'Muskan', 'Accountant', DATE '2023-02-01', DATE '2001-12-30', 'Pune', 42000,
'9876543214');

b) Display all five records


SELECT * FROM STAFF;

1
Sourabh Sahu [Link]. CS- 3rd Year

c) Delete the fourth record


DELETE FROM STAFF
WHERE eid = 4;

d) Update the third record of field ename as 'Hari'


UPDATE STAFF
SET ename = 'Hari'
WHERE eid = 3;

e) Add one new field in the table


ALTER TABLE STAFF
ADD email VARCHAR2(50);

2
Sourabh Sahu [Link]. CS- 3rd Year

Q.2) Design a salary table Oracle server with one primary key and foreign key
(employee table) having following fields:
Month, working days, deptid, gross, incentive, deduction and net salary.
a)Insert five records in above created table
b) Display all five records
c) Use foreign key relation and display all records
d) Update the second record of field dept id as 'Sales'
e) Add one new field in the table.

Query:-

EMPLOYEE TABLE (PARENT TABLE)


CREATE TABLE EMPLOYEE (emp_id NUMBER PRIMARY KEY, emp_name
VARCHAR2(50), deptid VARCHAR2(30) );

INSERT INTO EMPLOYEE VALUES (1, 'Naveen', 'HR');


INSERT INTO EMPLOYEE VALUES (2, 'Vimal', 'IT');
INSERT INTO EMPLOYEE VALUES (3, 'Hemant', 'Finance');
INSERT INTO EMPLOYEE VALUES (4, 'Pinky', 'Admin');
INSERT INTO EMPLOYEE VALUES (5, 'Muskan', 'Support');
COMMIT;

SALARY TABLE (CHILD TABLE)


CREATE TABLE SALARY ( emp_id NUMBER, month VARCHAR2(15),
working_days NUMBER, deptid VARCHAR2(30), gross NUMBER(10,2),
incentive NUMBER(10,2), deduction NUMBER(10,2), net_salary NUMBER(10,2),
CONSTRAINT pk_salary PRIMARY KEY (emp_id, month), CONSTRAINT fk_emp
FOREIGN KEY (emp_id) REFERENCES EMPLOYEE(emp_id) );

a) Insert five records


INSERT INTO SALARY VALUES (1, 'JAN', 26, 'HR', 50000, 2000, 1500, 50500);
INSERT INTO SALARY VALUES (2, 'JAN', 25, 'IT', 48000, 1500, 1200, 48300);
INSERT INTO SALARY VALUES (3, 'JAN', 26, 'Finance', 52000, 2500, 2000, 52500);
INSERT INTO SALARY VALUES (4, 'JAN', 24, 'Admin', 40000, 1000, 800, 40200);
INSERT INTO SALARY VALUES (5, 'JAN', 26, 'Support', 42000, 1200, 1000, 42200);
COMMIT;

3
Sourabh Sahu [Link]. CS- 3rd Year

4
Sourabh Sahu [Link]. CS- 3rd Year

b) Display all five records


SELECT * FROM SALARY;

c) Display records using foreign key relation


SELECT e.emp_id, e.emp_name, [Link], [Link], s.net_salary
FROM EMPLOYEE e
JOIN SALARY s
ON e.emp_id = s.emp_id;

d) Update second record deptid as 'Sales'


UPDATE SALARY
SET deptid = 'Sales'
WHERE emp_id = 2;
COMMIT;

e) Add one new field to the table


ALTER TABLE SALARY
ADD bonus NUMBER(10,2);

5
Sourabh Sahu [Link]. CS- 3rd Year

Q.3) Create a new user in Oracle Server.

Query:-

CREATE USER library_user


IDENTIFIED BY library123;

GRANT CONNECT, RESOURCE TO library_user;

ALTER USER library_user QUOTA UNLIMITED ON USERS;

6
Sourabh Sahu [Link]. CS- 3rd Year

Q.4) Create a view in Oracle Server.

Query:-

CREATE TABLE SALES (sale_id NUMBER PRIMARY KEY, product_name


VARCHAR2(50), category VARCHAR2(30), quantity NUMBER, price
NUMBER(8,2), sale_date DATE);

CREATE OR REPLACE VIEW sales_summary AS


SELECT category,
SUM(quantity) AS total_quantity,
SUM(quantity * price) AS total_sales
FROM SALES
GROUP BY category;

INSERT INTO SALES VALUES (1, 'Laptop', 'Electronics', 2, 50000, SYSDATE);


INSERT INTO SALES VALUES (2, 'Mobile', 'Electronics', 5, 20000, SYSDATE);
INSERT INTO SALES VALUES (3, 'Chair', 'Furniture', 10, 3000, SYSDATE);
INSERT INTO SALES VALUES (4, 'Table', 'Furniture', 4, 7000, SYSDATE);
COMMIT;
SELECT * FROM sales_summary;

7
Sourabh Sahu [Link]. CS- 3rd Year

Q.5) Create a table in oracle server and practice for commit and rollback command.

Query:-

CREATE TABLE EMPLOYEES ( emp_id NUMBER PRIMARY KEY, emp_name


VARCHAR2(50), department VARCHAR2(30), salary NUMBER(8,2) );

INSERT INTO EMPLOYEES VALUES (1, 'Amit', 'HR', 30000);


INSERT INTO EMPLOYEES VALUES (2, 'Neha', 'IT', 45000);
COMMIT;

INSERT INTO EMPLOYEES VALUES (3, 'Rahul', 'Finance', 40000);


INSERT INTO EMPLOYEES VALUES (4, 'Priya', 'IT', 50000);
ROLLBACK;

SELECT * FROM EMPLOYEES;

8
Sourabh Sahu [Link]. CS- 3rd Year

Q.6) Create a new table in Oracle server and practice for inner join.

Query:-

CREATE TABLE STUDENTS (student_id NUMBER PRIMARY KEY, student_name


VARCHAR2(50), department VARCHAR2(30) );

CREATE TABLE MARKS (student_id NUMBER , subject VARCHAR2(30), marks


NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES
STUDENTS(student_id) );

INSERT INTO STUDENTS VALUES (1, 'Naveen', 'CSE');


INSERT INTO STUDENTS VALUES (2, 'Vimal', 'ECE');
INSERT INTO STUDENTS VALUES (3, 'Hemant', 'MECH');
INSERT INTO STUDENTS VALUES (4, 'Pinky', 'CSE');

INSERT INTO MARKS VALUES (1,


'DBMS', 85);
INSERT INTO MARKS VALUES (2,
'DBMS', 78);
INSERT INTO MARKS VALUES (3,
'DBMS', 90);

SELECT s.student_id, s.student_name, [Link], [Link]


FROM STUDENTS s
INNER JOIN MARKS m
ON s.student_id =
m.student_id;

9
Sourabh Sahu [Link]. CS- 3rd Year

Q.7) Create a new table in Oracle server and practice for outer join.

Query:-

CREATE TABLE STUDENTS (student_id NUMBER PRIMARY KEY, student_name


VARCHAR2(50), department VARCHAR2(30) );

CREATE TABLE MARKS (student_id NUMBER , subject VARCHAR2(30), marks


NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES
STUDENTS(student_id) );

INSERT INTO STUDENTS VALUES (1, 'Naveen', 'CSE');


INSERT INTO STUDENTS VALUES (2, 'Vimal', 'ECE');
INSERT INTO STUDENTS VALUES (3, 'Hemant', 'MECH');
INSERT INTO STUDENTS VALUES (4, 'Pinky', 'CSE');

INSERT INTO MARKS VALUES (1, 'DBMS', 85);


INSERT INTO MARKS VALUES (2, 'DBMS', 78);
INSERT INTO MARKS VALUES (3, 'DBMS', 90);

10
Sourabh Sahu [Link]. CS- 3rd Year

-- LEFT OUTER JOIN


SELECT s.student_id, s.student_name, [Link], [Link]
FROM STUDENTS s
LEFT OUTER JOIN MARKS m

ON s.student_id = m.student_id;

-- RIGHT OUTER JOIN


SELECT s.student_id, s.student_name, [Link], [Link]
FROM STUDENTS s
RIGHT OUTER JOIN MARKS m
ON [Link]

11
Sourabh Sahu [Link]. CS- 3rd Year

Q.8) Create a new table in Oracle server and practice for lower() and upper()
function .

Query:-

CREATE TABLE EMP_DETAILS (emp_id NUMBER PRIMARY KEY, emp_name


VARCHAR2(50), city VARCHAR2(30) );

INSERT INTO EMP_DETAILS VALUES (1, 'Ravi Kumar', ' Delhi ');
INSERT INTO EMP_DETAILS VALUES (2, 'Anu Sharma', 'Mumbai');
INSERT INTO EMP_DETAILS VALUES (3, 'kiran singh', ' Chennai ');
INSERT INTO EMP_DETAILS VALUES (4, 'MEENA DAS', 'Kolkata');

SELECT emp_name, LOWER(emp_name) AS lower_name


FROM EMP_DETAILS;

SELECT emp_name, UPPER(emp_name) AS upper_name


FROM EMP_DETAILS;

12
Sourabh Sahu [Link]. CS- 3rd Year

Q.9) Create a new table in Oracle server and practice for any two date formats.

Query:-

CREATE TABLE ORDERS (order_id NUMBER PRIMARY KEY, customer_name


VARCHAR2(50), order_date DATE);

INSERT INTO ORDERS


VALUES (1, 'Ravi', TO_DATE('15-01-2025', 'DD-MM-YYYY'));

INSERT INTO ORDERS


VALUES (2, 'Anu', TO_DATE('20-JAN-2025', 'DD-MON-YYYY'));

SELECT order_id,
customer_name,
TO_CHAR(order_date, 'DD/MM/YYYY') AS format1,
TO_CHAR(order_date, 'MONTH DD, YYYY') AS format2
FROM ORDERS;

13
Sourabh Sahu [Link]. CS- 3rd Year

Q10) Create a new Student table with column names:


(SID, SNAME, SCONTACT, PERCENTAGE, STREAM, DOB) in Oracle/SQL Server
and practice for max(), min(), avg() functions.

Query:-

CREATE TABLE STUDENT (SID NUMBER PRIMARY KEY, SNAME VARCHAR2(50), SCONTACT VARCHAR2(15),
PERCENTAGE NUMBER(5,2), STREAM VARCHAR2(20), DOB DATE);

INSERT INTO STUDENT VALUES (1, 'Amit', '9876543210', 85.50, 'Science', DATE '2002-05-14');

INSERT INTO STUDENT VALUES (2, 'Neha', '9123456780', 92.30, 'Commerce', DATE '2001-11-20');

INSERT INTO STUDENT VALUES (3, 'Rahul', '9988776655', 78.00, 'Arts', DATE '2003-02-10');

INSERT INTO STUDENT VALUES (4, 'Priya', '9090909090', 88.75, 'Science', DATE '2002-08-05');

SELECT MAX(PERCENTAGE) AS HIGHEST_PERCENTAGE

FROM STUDENT;

SELECT MIN(PERCENTAGE) AS LOWEST_PERCENTAGE

FROM STUDENT;

SELECT AVG(PERCENTAGE) AS AVERAGE_PERCENTAGE

FROM STUDENT;

14
Sourabh Sahu [Link]. CS- 3rd Year

Q.11) Create a table in oracle server and practice for Order by Clause.

Query:-

CREATE TABLE SALES (sale_id NUMBER PRIMARY KEY, product_name


VARCHAR2(50), category VARCHAR2(30), quantity NUMBER, price
NUMBER(8,2), sale_date DATE);

INSERT INTO SALES VALUES (1, 'Laptop', 'Electronics', 2, 50000, DATE '2024-01-
10');
INSERT INTO SALES VALUES (2, 'Mobile', 'Electronics', 5, 20000, DATE '2024-01-
11');
INSERT INTO SALES VALUES (3, 'Chair', 'Furniture', 10, 3000, DATE '2024-01-12');
INSERT INTO SALES VALUES (4, 'Table', 'Furniture', 4, 7000, DATE '2024-01-13');
INSERT INTO SALES VALUES (5, 'Headphones', 'Electronics', 8, 2000, DATE '2024-
01-14');

SELECT * FROM SALES


ORDER BY price ASC;

SELECT * FROM SALES


ORDER BY quantity DESC;

15
Sourabh Sahu [Link]. CS- 3rd Year

16
Sourabh Sahu [Link]. CS- 3rd Year

Q.12) Create a new table in Oracle server and list the students whose name starts
with 'N'.

Query:-

CREATE TABLE STUDENT (SID NUMBER PRIMARY KEY, SNAME VARCHAR2(50), SCONTACT VARCHAR2(15),
PERCENTAGE NUMBER(5,2), STREAM VARCHAR2(20), DOB DATE);

INSERT INTO STUDENT VALUES (1, 'Neha', '9123456780', 92.30, 'Commerce', DATE '2001-11-20');

INSERT INTO STUDENT VALUES (2, 'Nikhil','9876543211', 88.50, 'Science', DATE '2002-03-15');

INSERT INTO STUDENT VALUES (3, 'Amit', '9988776655', 85.00, 'Science', DATE '2002-05-14');

INSERT INTO STUDENT VALUES (4, 'Riya', '9090909090', 78.20, 'Arts', DATE '2003-01-10');

SELECT * FROM STUDENT

WHERE SNAME LIKE 'N%';

17
Sourabh Sahu [Link]. CS- 3rd Year

Q.13) Create a new table in Oracle server and list the students whose name is of five
characters.

Query:-

CREATE TABLE STUDENT (SID NUMBER PRIMARY KEY, SNAME VARCHAR2(50), SCONTACT VARCHAR2(15),
PERCENTAGE NUMBER(5,2), STREAM VARCHAR2(20), DOB DATE);

INSERT INTO STUDENT VALUES (1, 'Neha', '9123456780', 92.30, 'Commerce', DATE '2001-11-20');

INSERT INTO STUDENT VALUES (2, 'Nikhil', '9876543211', 88.50, 'Science', DATE '2002-03-15');

INSERT INTO STUDENT VALUES (3, 'Amit', '9988776655', 85.00, 'Science', DATE '2002-05-14');

INSERT INTO STUDENT VALUES (4, 'Rohit', '9090909090', 78.20, 'Arts', DATE '2003-01-10');

INSERT INTO STUDENT VALUES (5, 'Priya', '9012345678', 90.10, 'Science', DATE '2002-08-05');

SELECT * FROM STUDENT

WHERE LENGTH(SNAME) = 5;

18
Sourabh Sahu [Link]. CS- 3rd Year

Q.14) Create a new table in Oracle server and practice for drop command.

Query:-

CREATE TABLE COURSES (course_id NUMBER PRIMARY KEY, course_name


VARCHAR2(50), instructor VARCHAR2(50) );

INSERT INTO COURSES VALUES (1, 'DBMS', 'Naveen');


INSERT INTO COURSES VALUES (2, 'OS', 'Vimal');
INSERT INTO COURSES VALUES (3, 'CN', 'Hemant');

SELECT * FROM COURSES;

DROP TABLE COURSES;

19
Sourabh Sahu [Link]. CS- 3rd Year

Q.15) Create a new table in Oracle server and practice for unique and check
constraints.

Query:-

CREATE TABLE USERS (user_id NUMBER PRIMARY KEY, username VARCHAR2(30)


UNIQUE, email VARCHAR2(50) UNIQUE, age NUMBER CHECK (age >= 18), status
VARCHAR2(10) CHECK (status IN ('ACTIVE', 'INACTIVE')) );

INSERT INTO USERS VALUES (1, 'ravi01', 'ravi@[Link]', 22, 'ACTIVE');


INSERT INTO USERS VALUES (2, 'anu02', 'anu@[Link]', 25, 'INACTIVE');

SELECT * FROM USERS;

20
Sourabh Sahu [Link]. CS- 3rd Year

Q.16) Create a database in oracle server having atleast four tables for Covid
Vaccination Management System.

Query:-

CITIZENS TABLE
CREATE TABLE CITIZENS (citizen_id NUMBER PRIMARY KEY, citizen_name
VARCHAR2(50), age NUMBER, gender VARCHAR2(10), phone VARCHAR2(15) );

VACCINE TABLE
CREATE TABLE VACCINES ( vaccine_id NUMBER PRIMARY KEY, vaccine_name
VARCHAR2(30), manufacturer VARCHAR2(30), doses_required NUMBER);

VACCINATION CENTRE TABLE


CREATE TABLE VACCINATION_CENTERS (center_id NUMBER PRIMARY KEY,
center_name VARCHAR2(50), location VARCHAR2(50) );

21
Sourabh Sahu [Link]. CS- 3rd Year

VACCINATION TABLE
CREATE TABLE VACCINATIONS (vaccination_id NUMBER PRIMARY KEY, citizen_id
NUMBER, vaccine_id NUMBER, center_id NUMBER, dose_number NUMBER,
vaccination_date DATE, CONSTRAINT fk_citizen FOREIGN KEY (citizen_id)
REFERENCES CITIZENS(citizen_id),
CONSTRAINT fk_vaccine
FOREIGN KEY (vaccine_id) REFERENCES VACCINES(vaccine_id),
CONSTRAINT fk_center
FOREIGN KEY (center_id) REFERENCES
VACCINATION_CENTERS(center_id) );

22
Sourabh Sahu [Link]. CS- 3rd Year

Q.17) Create a table in oracle server having at least five tables for Library
Management System.

Query:-

TABLE BOOKS
CREATE TABLE BOOKS (book_id NUMBER PRIMARY KEY, title VARCHAR2(100),
author VARCHAR2(50), publisher VARCHAR2(50), price NUMBER(8,2) );

TABLE MEMBERS
CREATE TABLE MEMBERS (member_id NUMBER PRIMARY KEY, member_name
VARCHAR2(50), phone VARCHAR2(15), email VARCHAR2(50) );

TABLE LIBRARIANS
CREATE TABLE LIBRARIANS (librarian_id NUMBER PRIMARY KEY,
librarian_name VARCHAR2(50), phone VARCHAR2(15) );

23
Sourabh Sahu [Link]. CS- 3rd Year

TABLE ISSUES
CREATE TABLE ISSUES (issue_id NUMBER PRIMARY KEY, book_id NUMBER,
member_id NUMBER, issue_date DATE, return_date DATE, CONSTRAINT fk_book
FOREIGN KEY (book_id) REFERENCES BOOKS(book_id), CONSTRAINT
fk_member FOREIGN KEY (member_id) REFERENCES MEMBERS(member_id) );

TABLE FINES
CREATE TABLE FINES (fine_id NUMBER PRIMARY KEY, issue_id NUMBER,
fine_amount NUMBER(6,2),
paid_status VARCHAR2(10), CONSTRAINT fk_issue FOREIGN KEY (issue_id)
REFERENCES ISSUES(issue_id) );

24
Sourabh Sahu [Link]. CS- 3rd Year

Q.18) Write a PL/SQL Query for addition of two numbers.

Query:-

DECLARE
a NUMBER := 10;
b NUMBER := 20;
sum NUMBER;
BEGIN
sum := a + b;
DBMS_OUTPUT.PUT_LINE('Sum = ' || sum);
END;

25
Sourabh Sahu [Link]. CS- 3rd Year

Q.19) Write a PL/SQL Query for swapping of two numbers.

Query:-

DECLARE
--declare of datatype number
num1 number;
num2 number;
temp number;
begin
num1:=5;
num2:=4;
DBMS_OUTPUT.PUT_LINE('before');
DBMS_OUTPUT.PUT_LINE('num1= '||num1||'num2= '||num2);
--swapping of numbers num1 and num2
temp:=num1;
num1:=num2;
num2:=temp;
DBMS_OUTPUT.PUT_LINE('after');
DBMS_OUTPUT.PUT_LINE('num1= '||num1||'num2= '||num2);
END;

26
Sourabh Sahu [Link]. CS- 3rd Year

Q.20) Write a PL/SQL Query to print first ten natural numbers.

Query:-

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/

27
Sourabh Sahu [Link]. CS- 3rd Year

Q.21) Write a PL/SQL Query to find the Smallest number between two numbers.

Query:-

DECLARE

a number:=15;

b number:=10;

smallest number;

begin

smallest:=least(a,b);

dbms_output.put_line('smallest number is:' || smallest);

end;

28
Sourabh Sahu [Link]. CS- 3rd Year

Q.22) Write a PL/SQL Query to practice for implicit and explicit cursor.

Query:-

CREATE TABLE student ( roll_no NUMBER PRIMARY KEY, name VARCHAR(30),


marks NUMBER
);

INSERT INTO student VALUES (1, 'Ravi', 60);


INSERT INTO student VALUES (2, 'Anita', 75);
INSERT INTO student VALUES (3, 'Suresh', 45);
INSERT INTO student VALUES (4, 'Huresh', 45);

SET SERVEROUTPUT ON
BEGIN
UPDATE student
SET marks = marks + 5
WHERE marks < 50;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' row(s) updated');
END;
/

29
Sourabh Sahu [Link]. CS- 3rd Year

SET SERVEROUTPUT ON
DECLARE
CURSOR c_student IS
SELECT roll_no, name FROM student;

v_roll student.roll_no%TYPE;
v_name [Link]%TYPE;
BEGIN
OPEN c_student;

LOOP
FETCH c_student INTO v_roll, v_name;
EXIT WHEN c_student%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_roll || ' ' || v_name);


END LOOP;

CLOSE c_student;
END;
/

30
Sourabh Sahu [Link]. CS- 3rd Year

Q.23) Write a PL/SQL Query to find the factorial value of any entered number.

Query:-

DECLARE
NUM NUMBER:=6;
FACT NUMBER:=1;
TEMP NUMBER;
BEGIN
TEMP:=NUM;
WHILE(TEMP>0)
LOOP
FACT:=FACT*TEMP;
TEMP:=TEMP-1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FACTORIAL OF'||NUM||'IS'||FACT);
END;

31

You might also like