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.

1.     Retrieve and print the data of an employee having name=’Rishi' using Record.

2.     Retrieve and print the data of all the employees using Explicit Cursors.

3.     Write a PL/SQL code block to display the names and GPA of students from student table using an explicit cursor.

4.    Write a PL/SQL code block that displays the names, GPA of students along with the grades of students after calculation from student table using an explicit cursor.

Add a column grade to the student table; update the grades of students to the table after calculation. (The criteria of grade can be considered as grade = A if gpa>3.7; and grade = B, otherwise).

1.     Write a PL/SQL code block for calculating addition of Three numbers.

3.     Write a PL/SQL code block to find largerst of three numbers.

 

4.     Write a PL/SQL code block to print table of a given number.

5.     Write a PL/SQL code block to compute the factorial of a number.

6.     Write a PL/SQL code block to determine whether the number is prime or not.

7.     Write a PL/SQL code block to display n terms of a fibonacci series.

Q2. Insert DoB for each Student in corresponding table using above instance of Student table. 

Q3. Find average of GPA round off to 2 decimal places. 

Q4. Find year of DoB of Student having less than 1000. 

Q6. Display name of all Students in uppercase and name of college they applied in lower case. 

Q7. Find fourth alphabet of each student. (Hint: use substring)

 Q8. Find sID and sName of student whose sName has string length greater than 3. 

Q9. Find floor, ceiling and truncate (to one decimal place) value of average GPA. 

Q10. Display details of all students whose sID is even. 

Q11. Compute Square Root of 900 and 247 . 

Q12. Consider the string “Peter Piper picked a peck of pickled peppers. A peck of pickled peppers Peter Piper picked. If Peter Piper picked a peck of pickled peppers, Where the peck of pickled peppers Peter Piper picked?” Find 6th occurrence of string „pick‟. (Hint: use INSTR) 

Q13. Consider String „Satya Nadella‟ replace this using the key (Hint: use translate) a d e l N S t y 1 2 3 4 5 6 7 8 

Q14. Display sID, sname and DoB in this format „February 26, 2014‟ 

Q15. Convert the text ‟26/02/2014‟ to date. 

Q16. Compute on which date is next Saturday and last day of this month?

For Download - click Here