Test Your Knowledge 1

You can test to basic SQL & PL/SQL knowledge using the following listed questions.

Q1. Create Table Students with following fields & Write 2 insert statements for it.

1. RollNo integer 10 => Auto Increment
2. FName string 50
3. LName string 50
4. DOB Date => Date of Birth
5. Add1 string 150
6. Add2 String 150
7. City String 100
8. State String 100
9. Pin String 10
10. Class String 10 => class in which student is studying

First field will be used as a unique identifier for the record.

Q2. Create table Subjects with following fields & Write 2 insert statements for it.

1. SubID integer 10 => Auto Increment
2. SubName String 100

First field will be used as a unique identifier for the record.

Q3. Create table Exams with following fields.

1. ExamDate Date
2. RollNo integer 10
3. SubId integer 10
4. MarksObstained integer 10
5. MarksFrom integer 10

First 3 fields will be used as a unique identifier for the record.

Q4. Create a PL/SQL Procedure SP_Exam_result which should accept exam date as parameter and should create the records for each Student and each Subject in Exams table automatically assuming that all the exams were conducted on the same date. The MarksFrom field should be having value as 100. The MarksObtained field should have random values generated using following function.

ROUND(DBMS_RANDOM.VALUE(30,100))

Q5. Using SP_Exam_result create exam results for exams date 31-Aug-2018, 30-Nov-2018, 28-Feb-2019 and 15-May-2019. Write calls to SP_Exam_result procedure.

Q6. Write SQL query to generate output as given below for the students by adding up all the marks for the Student for each Subject

Roll No.	Student Full Name	Subject Name	Marks1	Marks2	Marks3	Marks4	Total	%
1	Student One	Subject One	40	60	40	60	200	50

Q7. Store the above out put in a table called Results.

Q8. Write a query to find out the total highest mark scorer for each subject.

To view the solution to above questions kindly read next blog.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.