DBMS Practical List - 3
Platform : Oracle Express
Implementation of DDL and DML commands of SQL with suitable examples
1. Logon to Oracle by system/manager.
2. Create a user of your name & assign it a password.
3. Grant resource ,connect to a new user.
4. Disconnect from Oracle.
5. Connect as a new user.
6. See list of tables by using”Tab / user_tables /cat” tables.
7. View the user name from dual.
8. Clear the screen.
9. Perform some mathematical operations by using dual.(Addition,multiplicationetc)
10. Create a table student with following attributes :
Roll No Number 6
Name varchar 15
City varchar 12
Pincode varchar 8
Age Number 2
11. Display the structure of table
12. Insert the following data into the table (Enter as it is)
Roll Name City Pincode Age
No
1 Amit Delhi 214213 21
2 Kumar Bombay 25
3 Kshitij Madras 110101 16
4 Puneet Calcutta 110107 18
5 Rohit Delhi 502207 19
6 Karnal Dehradun 28
7 Shweta Dehradun 98102 23
8 Mohit Bombay 12434 22
9 Pankaj Bombay 17
10 Parul dELhi 76312 24
11 Rohit Baroda 12131 29
13. Show all records
14. Show names of all students living in Delhi.
15. Insert the following data further into the same table.
2 Gaurav Rampur 312125
Manish 314136
14 Aviral 319143 29
15 Gwaliar 313149 25
7.Write queries for the following :-
a. Assign Roll No. “13” to Manish.
b. Assign the name “Abhijeet” to Roll No. 15
c. Set the cities of Roll No. 13 & 14 to “Meerut”
d. Increase all age by 3 years.
e. Set the age of all students living in Meerut to “25”.
f. Add a new column named “Balance”, type number”(6) to the student table.
g. Set the balance of all students to Rs. 20,000/-
h. Increase the balance by Rs. 500/- for all Bombay & Delhi students.
i. Show the total balance due from all students.
j. Show the maximum balance.
k. Increase the size of “balance” column to 10.
l. Reduce the size of city to “10”
m. Create a new table “student2” as a copy of student with all records from student
table.
n. Show the structure of student2
o. Delete the records of those students from “student2” who are greater or equal to
25 years.
p. Show all records of student2 table
q. Change the name of student2 to “Newstudent”
r. Delete all records from “newstudent”
s. Drop the “newstudent” table.
t. Again create the “Newstudent” table similar to student table but this time do
not copy the record, only the structure.
u. Create a new table named “BCA” with only three columns Roll No, Name &
balance, similar to student table. (Create with the help of student table only &
also copy its data)
