DBMS Lab Expriment Assignment-8 full solution

 

STUDENT TABLE

Q1. As we need to notify in system birthday of each student so kindly create an index DoBindex on column DoB of Student table.

Q2. Which index be more suitable for major in Apply? Create Bitmap Index name MAJORindex.

 

Q3. Remove index on DoB column.

 

Q4. Create an Unique index ENRindex on enrollment.

 

Q5. Create an composite Unique index SCMindex on Apply using columns sID, cName, major.

 

Q6. Create composite index on Apply using columns cName and major. Name this index as CMapplyINDX.

 

Q7. Create sequence sID_seq with following parameters, increment by 3, cycle, cache 4 and which will generate the numbers among 988 to 999.

 

Q8. Display next value of Sequence sID_seq.

 

Q9. A new student entered the database named Eric with next sID from sequence sID_seq having GPA 9.9 , sizeHS 9999, DoB as ’23-Apr-98’ to table Student.

Q10. Now, another boy registered to our system named Troy with next sID from sequence sID_seq having GPA 9.8 and sizeHS 989 and Dob as ’25-Nov-99’.

 

Q11. Display details of Student table and observe newly inserted Eric and Troy sID.

 

Q12. Create sequence GPA_seq having maximum value as 5 and min value as 3 you are supposed to start sequence with 5 and decrement the sequence with -1, cycle and no cache.

 

Q13. Update GPA of Eric to next value of sequence GPA_seq.

 

Q14. Insert student Jack with sId from sID_seq , GPA from GPA_seq, sizeHS as 1500 and DoB as ’22- OCT-97’.

 

Q15. Display detail of Student Table and observe GPA and sID of Jack. 

 Ans: select GPA,sID from student; 

or 

Q16. Display next value of sequence GPA_seq.

Q17. Drop sequence sID_seq.

 

Q18. Drop sequence GPA_seq.