Oracle Interview Questions

students
-----------
student_id (primary key) student_name date_of_birth city email

student_course_detail
---------------------
detail_id student_id course_id course_name

Note: A student may or may not have joined a course. student may have joined more than 1 course.

1. write a query to find students doing courses. show following detail:
student_id, student_name, course_id, course_name

2. write query to show students comming from chennai. Also show age of the student. show age lesser to greater. show as follows:
student_id, student_name, city, age, date of birth (eg: '10-NOV-1992' )

3. write query to show city that has students greater than 10. show as follows:
city, no of students

4. write query to show course that has joined by maximum no of students. If more than one course is maximum then show all.
(eg) if course 'oracle' has 15 students & 'java' has 15 students then show both.
course_id, course_name, no of students

5. write query to show students who are not taken any course.
student_id, student_name

6. write query to show courses having less than 10 students.
course_id, course_name, no of students

7. write query to insert following data into students table:
student_id = 100
student_name = kumar
date_of_birth = '10-November-1992' (note: this needs to converted to date)
email = 'scott@tiger.com'
city = 'Chennai'

8. write query to update following data for student_id 100 in students table
student_name = kumar
date_of_birth = '11-December-1992' (note: this needs to converted to date)
email = 'scott@tiger.com'
city = 'Chennai'

9. write a query to delete all students who's data_of_birth is 1992.
10. write a plsql stored procedure with Input parameter date_of_birth as varchar in 'DD-MM-YYYY' format.
using the input date of birth, query students table and display all student_id,student_name using Dbms_output.

11. write a query to show students who are joined maximum no of courses. Show as follows: