DBMS Practical List - 7

Platform: Oracle Express Implementation of

Constraints 1

1. Write a query to create an account table with following constraints:

ALL CONSTRAINTS TO BE GIVEN AT COLUMN LEVEL
AccountNo Number Primary Key
CustName Varchar2 Not Null
AccountType Varchar2 DefaultValue‘Saving’
Balance Number balance>1000
PassportNo Varchar2 Unique
2. See and note the structure of above table, and answer the following questions:
a) Which constraints are displayed in the structure?
b) Can you tell from the structure which column is unique?
c) Can you tell from the structure which column is Primary and which Not Null?
d) Can you tell from the structure which column has check constraint?
3. Insert the following data in the table:
117501 Puneet Saving 1100 B9895
117502 Saving 2100 A9895

117507 Rohit Current 500 C9898

117507 Rohit Current 5000 C9898
117504 Kumar 1100 C9898

117508 Shweta 7100 A6789

117503 Deepak Current 12000

Notetheoutput of eachquery

Commit the Data

4. WAQ to create an “Account2” table with following constraints:
AccountNo Number Primary Key
CustName Varchar2 Not Nul
AccountType Varchar2 Default Value
 ‘Current’
Balance Number >5000
PassportNo Varchar2 Unique
5. See and note the structure of above table.
6. Write a query to create an “Account3” table with following constraints:
AccoutNo Number Primary Key(at row/table level)
CustName Varchar2 Not Null(only at table/row level) If an error occurs, note
the error and rectify it.
7. See the contents of “User_Constraints” table and note the constraint number and type
the constraintsfor above table.
8. Drop the Not Null constraint from “Account3” table.
9. WAQ to create an “Account4” table with following constraints:
AccountNo Number Primary Key (Constraint Name:
Pkey)
CustName Varchar2 Not Null (Constraint Name: NNull)
AccountType Varchar2 Default Value ‘Current’
Balance Number >5000 (Constraint Name: ChkBal)
PassportNo Varchar2 Unique (Constraint Name: UU)
10. See the contents of “User_Constraints” of above table.
11. Drop the Primary Key & Unique Constraint from Account4 table.
12. WAQ to create an “Account5” table without any consrtaints:
AccountNo Number
CustName Varchar2
Balance Number
13. See the structure of above table.
14. Add primary key constraint for AccountNo in Account5 table.
(In case of error rectify the table contents and repeat the question)
15. Add Unique constraint in the table for CustName field.
16. Add Check constraint in the table for Balance field (Amount > 1500) (In case of error
rectify the table contents and repeat the question)
17. Display the data in Account5 table, also record its structure.
 DBMS Practical List - 8
Date and Time
Platform: Oracle Express
1. Logon to Oracle using your User Name
2. See list of tables using “Tab”
3. Write a query to create an “Employee” table with following constraints:
EmpCode
EmpName
EmpCity
DOB (date of birth)
DOJ (date of joining)
Salary
4. Display the structure of the table
5. Insert the following data:

EMPCODE EMPNAME EMPCITY DOB DOJ Salary

1 YogeshDelhi 1/11/75 1/1/05 12000

2 Dinesh Mumbai 22/5/78 Current 6000
Date

3 Sheena Pune 16/6/89 1/6/99 50000

5 Sundar Bangalore 15/9/90 1/1/02 65000
6. Execute the following queries:
1) Select empname,DOB from employee
2) Select empname, DOB, sysdate from employee.
3) Select empname, DOB, DOB + 15 from employee
4) Select empname, DOB,DOB-3 from employee
5) Select empname, DOB, TO_CHAR(dob,’DD MM YYYY’) from employee
6) Select empname, DOB, TO_CHAR(dob,’Day Month Year’) from employee
7) Select empname, DOB, TO_CHAR(dob,’Day MON Year DD MM YY’) from
employee
8) Select empname, DOB, TO_CHAR(dob,’DD DD MM MM YY YYYY
YYYY’)from employee
 9) Select empname, DOB, TO_CHAR(dob,’DDsp MMsp YYYYsp’) from
employee
10)Select empname, DOB, TO_CHAR(dob,’DDspth MMspth YYYYspth’) from employee
11)Select empname, DOB, TO_CHAR(dob,’DDsp MMspth YYYYsp’) from employee
12)Select empname, DOB, TO_CHAR(dob,’DD MM YYYY HH MI SS’) from employee
13)Select empname, DOB, TO_CHAR(dob,’HH MI SS’) from employee
14)Insert into employee values(4,’Amit’,’Meerut’,’1-Jan-67’,’21-Sep-04’,15000)
15)Select empname, DOB, TO_CHAR(DOB,’DD MM YYYY’), TO_CHAR(DOJ,’DD
MM YYYY’) from employee where empcode=4
16)Insert a record with DOB=1674 and DOJ=1734 17)Display this record with full year in
DOB and DOJ
18)7. Write queries for the following:
19)Show all records
20)Show names and salary of all employees
21)Display highest salary
22)Display lowest salary
23)Show the total number of records
24)Display the empname, actual salary and salary with 15% increment
25)Display all records in format: “The Salary of Yogesh is 12000 and he belongs to Delhi”
26)Display the total number of characters in all employee names
27)Display all employee names in Capital Letters
28)Display all cities in Small Letters
29)Display names in Initial capital letters
30)Display only current date
31)Display only current time
32)Display the current date and time
33)Display only current year
34)Display only current month
35)Display all records in following format: “The date of birth of Yogesh is 1 November
Nineteen Seventy Five”
36)Display the total number of months an employee is working
37)Display the name and age of each employee
38)Display total experience of all employees in years
39)Display the full 4 digit birth year of all employees
40)Enter a record with your detail in the table with your actual DOB
41)Display on which day you were born
42)Display all list of employees who were born after your dob
43)Delete your record
44)Again enter your record but this time enter your birth time also
45)Display names, DOB, Time of Birth, age of all records
 PRACTICAL LIST- 10
(SUBQUERY, GROUPING, JOINS) SUBJECT:
DBMS
1. Ensure that your login has following tables with proper constraints anddata:-

COURSE TABLE
CourseCode CourseName CourseFees Department
101 MCA 80000 MCA
102 MBA 60000 MBA
103 Btech 75000 Engg
104 BCA 30000 MCA
105 BSc(IT) 25000 MCA
106 MSc(IT) 35000 MCA
107 BBA 22000 MBA

STUDENT TABLE
RollNo Name City
1 Amit Delhi
2 Sumit Goa
3 Shweta Gwaliar
4 Kumar Goa
5 Puneet Meerut
6 Bharat Gwaliar

REGISTER TABLE
RollNo CourseCode
2 101
4 103
5 101
3 105
1 103
2. See the contents of above table and commit data
“Please verify your output each time with above tables”
3. Write queries for the following:-
 a) Show details of all students
b) Show details of all students who aren’t registered in any course
c) Show details of students who are registered in any course:
• Write Query Using Join
• Write Query Using Sub query
d) Details of all students along with course details in which they areregistered, if
any. Include those students also which are not registered
(Use Outer Join)
e) Display third highest fees
f) Number of students from each city
g) Details of students who are registered in most expensive course
h) Display highest course fees
i) Display second highest course fees
j) Display total sum of all course fees
k) Display department wise total fees of all courses. Show bothdepartment name
and total fees
l) Display department wise total fees of all those departments whose total
department fees is greater than Rs. 80000/- (How many rows are displayed?)
m) Display department wise total fees of only those courses having feesgreater
than Rs. 30000/- ( How many rows are selecte
 PRACTICAL LIST – 9
(JOINS ANd SubqueRy)SubJeCT: dbmS
1. Create the following tables:

COURSE TABLE
CourseCode Numeric Primary key
CourseName Varchar 10 Null not allowed
CoourseFees Numeric Not less than 20000
Department Varchar 10

STUDENT TABLE
RollNo Numeric Primary Key
Name Varchar 10 Unique
City Varchar 10 Not Null

REGISTER TABLE
RollNo Foreign key for student table, Prime
CourseCode Foreign key for course table, Prime

Enter the following data into table:

RollNo CourseCode
2 101
4 103
5 101
3 105
1 103
REGISTER TABLE

COURSE TABLE
CourseCode CourseName CourseFees Department
101 MCA 80000 MCA
102 MBA 60000 MBA
103 Btech 75000 Engg
104 BCA 30000 MCA
105 BSc(IT) 25000 MCA
106 MSc(IT) 35000 MCA
107 BBA 22000 MBA

STUDENT TABLE
RollNo Name City
1 Amit Delhi
2 Sumit Goa
3 Shweta Gwaliar
4 Kumar Goa
5 Puneet Meerut
6 Bharat Gwaliar

2. Write queries for the following:

a) Details of students who are not registered in any course. (Complete Detail)
b) Rollno of students not registered in any course. (Only Rollno)
c) Details of students who are registered in “MCA” course
d) List of courses in which no students are registered
e) List of courses in which at least one student is registered
f) List of students having fees greater than “BCA” course
g) Total fees of all courses and also show average fees
h) Department wise total fees of all courses
i) Maximum course fee of each department
j) Details of courses which are having fees greater than average fees
k) Details of those courses in which students of “Goa” are registered
l) Increase the fees of all courses by 10% and commit
m) View all records of course table
n) Display highest, lowest and average fees
o) Names of students who are registered in a course having fees less than 30000
p) Display coursecodes along with the total number of registration in those
courses
q) Display the total number of students from Goa
r) Display highest fees
s) Display second highest fees