Academy Of Technology, Adisaptagram

CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 1 of 11

INTRODUCTION
Different types of commands in SQL:
A). DDL commands: - To create a database objects.
1. The Create Table Command.
2. Modifying the structure of tables.
3. Dropping a column from a table.
4. Modifying existing columns.
5. Renaming :
i) renaming the table.
ii) renaming the column.
6. Truncating the tables.
7. Destroying tables.
B). DML commands: - To manipulate data of a database objects.
1. Inserting Data into Tables.
2. Delete operations.
3. Updating the contents of a table.
4. Types of data constrains.
i) not null constraint at column level.
ii) unique constraint.
iii) unique constraint at table level.
iv) primary key constraint at column level
v) primary key constraint at table level.
vi) foreign key constraint at column level.
vii) foreign key constraint at table level
viii) check constraint
ix) check constraint constraint at table level.
C).DQL command: - To retrieve the data from a database.
1. Viewing data in the tables.
2. Filtering table data
i) Selected columns and all rows.
ii) Selected rows and all columns.
iii) Selected columns and selected rows.
3. Sorting data in a table.
D). DCL/TCL commands: - To control the data of a database.
1. Grant privileges using the GRANT statement
2. Revoke permissions using the REVOKE statement

Assignment-2
COMMANDS:
1. create table
2. insert into
3. Alter table --- add, rename, modify columns
4. Update ---using & not using where
5. Select--- all columns and some columns
6. Delete ---all rows and some rows
7. Rename ---table
8. drop table
9. FLASHBACK TABLE
10. drop table customer2 purge
CSE 6th SEMESTER
Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 2 of 11

11. copy the structure and data from another table

12. copy only table without data
13. insert data from other table
14. delete, drop and truncate

customer (cust_id ,cust_fname ,cust_lname ,territory ,cred_lmt, mngr_id , marital_status ,sex ,income)

1. Create and insert given data in table customer.

2. Alter table and Add column stay_from_year
3. Set value of stay_from_year as 2001 for Italy/America and 2003 otherwise
4. Display credit limit attribute for America
5. Delete the record corresponding to Meg Sen
6. Show all attributes for Italy// Show all data in the territory Italy
7. If territory is India and status is Single set value of credit to 7000
8. Rename cust_fname to first_name
9. Rename cust_lname to last_name
10. Create table cust1 from the old table customer(copy structure as well as data using CTAS statement).
11. Create tables cust2 without values of cust1 using CTAS statement.
12. Create tables cust3 with attributes cust_id,cust_fname,cust_lname,income from old customer table
only 5 rows.(using CTAS statement).
13. Create tables cust4 with attributes name customer_id,firstname,lastname, income from old customer
table(using CTAS statement).
14. Drop column income from cust1.
15. Rename table cust1 to cust_one
16. Insert values into cust2 table from customer table
17. Insert values into cust3 table with attributescust_ id, f_name, l_name,Income from customer table
where income > 50000
18. alter the table cust4 change cust id to varchar(6) and income to number(5)
19. Add new attribute mngr_name to cust4 and insert 5 records
20. Add attribute territory to cust4
21. Drop table cust3 and then bring it back.
22. Increase the size of the column custid by 5.
23. Suppose the customer with id no C63 has changed her last name & now it is just same as the customer
with id no C68.
24. Update customer set lname=(select lname from customer where cid=C63) where cid=C68.
25. Display the records where territory=America & crd_lmt=25000.
26. Display the records of all Indian customers whose income>20000.
27. Display the name of the customer having crd_lmt between 2000 and 7000.
28. Display the records of the customers having income 20000,24000,300,4500 using only one query.
29. Display the records in ascending order of first name
30. Display the records in descending order of income.
31. Insert a duplicate record and display all the records.
32. Suppose your friend wants to select a name from the names of the customers. Show the different names
of the student.
CSE 6th SEMESTER
Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 3 of 11

--

Assignment-3
Create table dept with the following attributes:
Column name Data type(size) Constraints
dept_id number(3) primary key
dept_name varchar2(15) ------
Insert 4 depts with names and id’s 90, 69, 100 and 110.

Create table cust_100 with the following attributes:

Column name Data type(size) Constraints
emp_id number(3) Primary key
first_name varchar2(10) Initial letter capital
last_name varchar2(10) Initial letter capital and not null
e_mail varchar2(20) All upper case
ph_no varchar2(15) ------
hire_date Date Should be > than 01-jan-1980
job_id varchar2(10) Must begin with FI or AD or IT
salary number(8,2) ≥ 4000 & ≤ 25000
mgr_id number(3) ------
dept_id number(3) Foreign key, refer table dept

1. Add 10 records to cust_100

2. Drop column mrg_id
3. Add column mgr_id and make it self referenced such that first 4 id’s correspond to first emp_id, next 4
correspond to fifth emp_id and the last 2 correspond to the ninth emp_id.

Assignment-4
1. Oracle table DUAL
2. Types of functions
3. Numeric Functions
(ABS,POWER,ROUND,SQRT,EXP,GREATEST,LEAST,MOD,TRUNC,FLOOR,CEIL)
4. String Functions
(LOWER,UPPER,SUBSTR,INSTR,LPAD,RPAD,TRIM,LTRIM,RTRIM,LENGTH,INITCAP,
SOUNDEX)
5. Conversion Functions
(TO_CHAR, TO_NUMBER,TO_DATE)
6. Date Functions
(SYSDATE, SYSTIMESTAMP, ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY,
ROUND)
Create the following tables with the data types and constraints
sailor:
Attribute Datatype Constraints
SID varchar2(4) primary key and start with small s
SNAME varchar2(15) initial letter capital
MNAME varchar2(15)
CSE 6th SEMESTER
Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 4 of 11

SURNAME varchar2(15) not null

RATING number(2) default zero
AGE number(3,1) not null

Attribute Datatype Constraints

BID number(3) primary key & start with small b
BNAME varchar2(10) all upper case
COLOR varchar2(6) red,green,blue

Attribute Datatype Constraints

SID varchar2(4) Foreign key referencing sailor
BID number(3) Foreign key referencing boat
DAY date <1-JAN-2000
SID,BID composite primary key

SID SNAME MNAME SURNAME RATING AGE

s22 Fredrico Roberts 7 45
s31 Lubber Sheen 8 55.5
s32 Charlotte Gordin 8 25.5
s58 Mary Beth Lyon 10 35
s64 Horatio Powell 7 35.4
s71 Zorba Alex 10 16
s29 Brutus Slater 1 33.8
s95 Deep Graceb Davis 3 63.5
s74 Horatio Forrest 9 35
s85 Sara Art Powell 3 25.5
s80 Deep Kumar Kumar 6 17
s87 Deep Kumar Jha 8 51

SID BID R_DAY

s22 101 10-OCT-98
s22 103 10-AUG-98
s22 102 10-OCT-98
s22 104 10-JUL-98
s31 102 11-OCT-98
s31 102 11-JUN-98
s31 104 11-DEC-98
s64 101 09-MAY-98
s64 102 09-AUG-98
s74 103 09-AUG-98
s80 102 07-JUL-98
s87 101 08-JUL-98
s87 102 12-DEC-98

CSE 6th SEMESTER

Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 5 of 11

BID BNAME COLOR

101 Interlake blue
102 Interlake red
103 Clipper green
104 Marine red

Assignment 4 PART A
For the above schema, perform the following query—
1) Find the names and ages of all sailors.
2) Show names under the heading of names_of_sailors and add 2 to age.
3) Select all records from sailors in ascending order by name;
4) Show all sailors name.
5) Select all distinct sailors name.
6) Show all distinct sailors names, ratings who have rating between 5 and 10.
7) Select all records from sailors in ascending order by rating and descending order by age.
8) Select all records from sailors whose rating>7.
9) Find records for sailor name Horatio and age=35.4.
10) Find records for sailor name Horatio or age=35.4.
11) Select names of sailors who have reserved boat 104.
12) Find sid of sailors who have reserved red boat
13) Select records for name beginning with ‘B’.
14) Select records for name containing ‘B’/’b’.
15) Select names for rating present.
16) Select names for rating absent.
17) Find color of boats reserved by Lubber.
18) Find a sailor name that have reserved at least one boat.
20) Compute the increments of rating of persons who have sailed on diff boats on the same day.
21) Find name of sailors whose name begins and ends with B and has at least 3 characters.
22) Find names of sailors whose name begins and ends with ‘B’ and has exactly 3 chars.
23) Find names of sailors who have reserved a red boat or a green boat.
24) Find names of sailors who have reserved a red boat but not a green boat.
25) Find names of sailors who have reserved boat 103.
26) Find names of sailors who have reserved red boat.
28) Find names of sailors who have not reserved red boat.
29) Count distinct sailor name from sailors.
30)a) Find all records for the rating>some sailor name where sailor name like ‘Horatio’.
b) Find all records for the rating>all sailor name where sailor name like ‘Horatio’.
32(a)Find all records for the rating<some sailor name where sailor name like ‘Horatio’.
(b)Find all records for the rating<all sailor name where sailor name like ‘Horatio’.
32) Select all records for s_name neither Lubber nor Horatio.
33) Find names of sailors whose rating is>10/20/30 using multirow subquery operator.
34) Find names of sailors whose rating is>10 & 20 & 30 using multirow subquery operator.
35) Find average age of sailors with rating 10.
36) Find the name of sailor who are older than oldest sailor of rating=10.
37) Find the age of youngest sailor for each rating level.
38) Find the name of each sailor who is eligible to vote for each rating level.
39) Find the age of youngest sailor who is eligible to vote for each rating level with at least two such sailors.
CSE 6th SEMESTER
Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 6 of 11

40) Find the average age of sailor for each rating level with at least two such sailor.
41) For each red boat count the no of reservations for this boat.
42) Find sailor with highest rating.
44) Find those rating for which the average age of sailors is minimum over all rating.
45) Find sailors who have reserved all boats.

ASSIGNMENT 4 PART B

46) Display s_name with left side padding by at least 3 *.

47) Display length of each name.
48) Display all sailors names in uppercase.
49) Display all sailors’ names in lower case.
50) Display all sailors names in sentence case.
51) Display 4th to 7th letter of sailors name.
52) Display 4th and 7th letter of sailors name.
53) Concat s_id, s_name.
54) Display square root of rating.
55) Display floor values of all ages.
56) Display ceiling values of all ages.
57) Select all s_name with 1st 2 letters off.
58) List months between today and reservation date.
59) Select day between today and reservation date.
60) Shift all reservation day by 2 months.
61) Shift all reservation day earlier by 3 months.
62) Suppose after sailing they enjoy their next Monday as holiday. Find that day.
63) Display 3 * before and after each s_name.
64) Find the date when sailing ends.
65) Display all reservation day.
66)Find the position of ‘Kumar’ in the sailors name.
67)display all saiors name order by its length.
68)display sid,sname and availability of middle name which pint as ‘available’ or ‘not available’.
69)display all reservation day like ‘22nd March twenty ten’ and 12/09/1998.
70)find the day of weekdays of reservation date.
71)find the number of day of weekdays of reservation date.
72) Find the number of days passed upto reservation date of that year.
73) Display the number of weeks of the year for reservation day.

CSE 6th SEMESTER

Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 7 of 11

Assignment-5

EMPLOYEE
FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO

DEPARTMENT
DNAME DNUMBER MGRSSN MGRSTARTDATE

DEPT_LOCATIONS
DNUMBER DLOCATION

PROJECT
PNAME PNUMBER PLOCATION DNUM

WORKS_ON
ESSN PNO HOURS

DEPENDENT
ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP

For the above schema, perform the following query—

1. For every project located in 'Stafford', list the project number, the controlling department number, and the department
manager's last name, address, and birth date.
2. Retrieve the name of each employee who works on all the projects controlled by department number 5.
3. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker
or as a manager of the department that controls the project.
4. Retrieve the names of employees who have no dependents.
5. List the names of managers who have at least one dependent.
6. For each employee, retrieve the employee's first and last name and the first and last name of his or her immediate
supervisor.
7. Show the resulting salaries if every employee working on the 'ProductX' project is given a 10 percent raise..
8. Retrieve a list of employees and the projects they are working on, ordered by department and, within each
department, ordered alphabetically by last name, first name.
9. Retrieve the names of all employees who do not have supervisors.
10. Retrieve the name of each employee who has a dependent with the same last name as the employee.
11. Retrieve the social security numbers of all employees who work on project numbers 1,2.
12. Returns the names of employees whose salary is greater than the salary of all the employees in department 5:
13. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary.
14. Find the sum of the salaries of all employees of the 'Research' department, as well as the maximum salary, the
minimum salary, and the average salary in this department.
15. Retrieve the names of all employees who have two or more dependents.
16. Count the total number of employees whose salaries exceed $40,000 in each department, but only for departments
where more than five employees work.
17. For each project, retrieve the project number, the project name, and the number of employees who work on that
project.
18. For each project on which more than two employees work, retrieve the project number, the project name, and the
number of employees who work on the project.
19. For each project, retrieve the project number, the project name, and the number of employees from department 5 who
work on the project.
20. For each department that has more than five employees, retrieve the department number and the number of its
employees who are making more than $40,000.

CSE 6th SEMESTER

Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 8 of 11

Assignment-6
A) Create the following Table: (ID number(3),Name varchar2(20),location varchar2(10)
Value of ID should be Auto generated(using Sequence)

ID NAME LOCATION
108 Ram Kolkata
107 Kaushik Naihati
106 Subhendu Narayanpara
105 Arti Bhatpara

B)Create TOUR table

TOUR_ID TOUR_SPOT FARE TYPE
TI Gangtok 6000 Delux
T2 Puri 2000 General
T3 Nainital 9000 Delux
T4 Andaman 12000 General
T5 Madurai 7000 Deux

Express the following SQL:

1) Create a view TOURVIEW for deluxe type tour containing two fields, tour-spotnames and fares.
2) Find all the Tour spots for fare greater than 8000 and Delux type tour from a) TOUR table, b)
TOURVIEW view
3) insert three rows to TOURVIEW.the location will be Bangladesh, Delhi, Hyderabad.
4)Display these Records.Are they seen in TOURVIEW? Are they seen in TOUR Table?
5)Make them Seen in TOURVIEW.
6)Create a view Tour_type_num containing two fields i.e. tour_type and its total number of count.
7) try to insert records via this view. Errors!!,Explain why?

Assignment-7 (for practice)

Customer(Cust id : integer, cust_name: string)

Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the bills for the current date with the customer names and item numbers
d) List the total Bill details with the quantity sold, price of the item and the final amount
e) List the details of the customer who have bought a product which has a price>200
f) Give a count of how many products have been bought by each customer
g) Give a list of products bought by a customer having cust_id as 5
h) List the item details which are sold as of today
i) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount
j) Create a view which lists the daily sales date wise for the last one week

CSE 6th SEMESTER

Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 9 of 11

Assignment-8

1) Write a PL/SQL program that takes marks as input and displays grade using if-else ladder
2) Write a PL/SQL program to display all even numbers up to a number using simple loop
3) Write a PL/SQL program to find the factorial of a number using For loop
4) Write a PL/SQL program to display the Fibonacci series upto 20000 using While loop
5) Write a PL/SQL program to insert random numbers in a table random_num (rand_no number(20))
6) Write a PL/SQL program to fill up the table sphere(rad number(2),area number(10,2), volume
number(15,2)) with radius values from 1 – 20
7) Write a PL/SQL program to display name, age and rating from table sailor from user input s_id
Practice assignment

8) Write a PL/SQL program to display all multiples of 3 up to a number using while loop
9) Write a PL/SQL program to display multiples of 5 using for loop
10) Write a PL/SQL program to calculate the salary from basic pay
11) Write a PL/SQL program to STRING REVERSE.
12) Write a PL/SQL program to find LEAP YEAR.

Assignment-9
1) Take s_id as input from keyboard and display the corresponding record. If s_id is not present in the sailor
table, then raise the proper predefined exception.

2) Take b_name as input from keyboard & show the corresponding boat table. If more than one record satisfy
for the particular b_name, raise the proper predefined exception.

3) Take basic as input from keyboard and da=60% of basic, hra=15% of basic, interim=35% of (basic + da).
Display da, hra, interims & total. If interim is between 3000 and 5000, then total=2*basic. If interim is greater
than 5000, then total=3*basic. (User defined exception).

Assignment-10
1) Create a PL/SQL procedure to calculate factorial of a number
2) Create a PL/SQL procedure that takes a user-input date and then prints if the year is a leap year.
3) Create a PL/SQL procedure to find prime number.
4) Create a PL/SQL procedure to STRING REVERSE.
5) Create a PL/SQL function that takes a user-input date and then prints if the year is a leap year.

Practice assignment

6) Create a PL/SQL procedure to implement Fibonacci series.

7) Write a PL/SQL procedure that takes marks as user input and prints the corresponding grade.
8) Create a PL/SQL function to calculate factorial of a number
9) Create a PL/SQL function to implement Fibonacci series.

Assignment-11
1) Create a trigger
a. This trigger is fired when an update or delete operation is performed on the table CUST_MSTR. The trigger
first checks for the operation being performed on the table. Then depending on the operation being performed

CSE 6th SEMESTER

Academy Of Technology, Adisaptagram
CSE

LAB ASSIGNMENT Doc No.: AOT/CSE/691

DBMS LAB(CS691) Page 10 of 11

, variable is assigned in the value update or delete . Previous values of the modified record of the table
CUST_MSTR are inserted into the AUDIT_CUST table.

The Schema of Cust_Mstr table which stores details about customer:

CUST_MSTR(Cust_No,FName,Lname,Mname,DOB_Inc)

b. Write a database trigger on the TRANS_MSTR that checks the following:

The account number for which the transaction is being performed is a valid account number. The Transaction
Amount is not zero and is positive and in case of a withdrawal the amount does not exceed the current
balance for the account number.

The Schema of Trans_Mstr table which stores details about transaction:

TRANS_MSTR(Trans_no,Acct_no,Dt,type,Particular,Dr_Cr,Amt,Balance)

Assignment-12
1) From table reserve, write a PL\SQL program using cursor to show all boat id nos. reserved and their
corresponding day when a sailor id is entered as user input.

2) Write a PL\SQL program to display table sailor using cursor.

3) Write a PL\SQL program where you use the table customer and for a particular user input territory value,
all other attributes are displayed corresponding to that territory.

4) WRITE A PROGRAM TO FIND ALL S_ID,S_NAME,THEIR RESERVED BOAT FOR THE SAILORS
WHOSE 20<AGE<55.
Practice Assignment for Home
Student(Stud_no : integer, Stud_name: string)
Membership(Mem_no: integer, Stud_no: integer)
Book(book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)

For the above schema, perform the following—

a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the student names with their membership numbers
d) List all the issues for the current date with student and Book names
e) List the details of students who borrowed book whose author is CJDATE
f) Give a count of how many books have been bought by each student
g) Give a list of books taken by student with stud_no as 5
h) List the book details which are issued as of today
i) Create a view which lists out the iss_no, iss _date, stud_name, book name
j) Create a view which lists the daily issues-date wise for the last one week

CSE 6th SEMESTER