commonly asked question in sql with solution for placement

The entire question is based on the STUDENT table,
contents of this table is as follow:

(1) Display the maximum cgpa of any student.
Ans:
Select max (cgpa) from student;

(2) Write a sql query to find out second maximum cgpa of student table.
Ans:
Select max (cgpa) from student where cgpa!=(select max(cgpa) from student);

(3) Write a sql query to find out fourth maximum cgpa of student table.
Ans:
Select min (cgpa) from (select * from student order by cgpa) where rownum<=4;


(4) Write a sql query to display the details of that student which has maximum cgpa.
Ans:
Select * from student where cgpa= (select max (cgpa) from student);

(5) Create a view from STUDENT table which name will be RESULT and it hides date of birth of all student, also display the contents of RESULT view.
Ans:
CREATE VIEW:

Create view result as select roll_no,name,cgpa from student;
TO DISPLAY TUPLES OF VIEW RESULT:

Select * from result;

(6)Write a select clause in sql which display roll no and cgpa of all students but cgpa of all students should less then actual cgpa by one.
Ans:
Select roll_no,cgpa-1 new_cgpa from student;

(7) Write a sql query to display details of all students whose date of birth comes in January month.
Ans:
Select * from student where date_of_birth like ‘%JAN%’;

(8) Write a sql query to display age of all students with name, roll and cgpa.
Ans:

Select name,roll_no,cgpa,trunc((sysdate-date_of_birth)/365) age from student;

(9) Write a sql query which display output in the format: roll number of x is y
of all student where x and y is variable which content name and roll_no of student respectively.
Ans:
Select ‘roll number of ‘ name’ is ‘roll_no “roll of student” from student;

(10) Write a sql query to find out the last date of those months in which month his birth comes of each student of student table.
Ans:
Select last_day(date_of_birth) from student;

(11) Write a sql query to find out day of a week in which his birth day comes, of each student of student table.
Ans:
Select to_char(date_of_birth,’day’) as day from student;

(12) Write a sql query to display year of the birth day with name of student of each student.
Ans:
Select name, to_char (date_of_birth,’YYYY’) year from student;

(13) Write a sql query to display the sum, average, maximum, minimum, variance and standard deviation of cgpa from student table.
Ans:
Select sum (cgpa) sum, avg (cgpa) avg, max (cgpa) max, min (cgpa) min, variance (cgpa) vari, stddev(cgpa) stddev from student;

The entire question is based on the STUDENT table,
contents of this table is as follow:

(14) Write a sql query to find out average of cgpa, increase cgpa by one then calculate average cgpa and also find out cgpa with a condition if two students have same cgpa then exclude any one. In all cases exclude null value.
Ans:
Select avg (all cgpa),avg (cgpa+1),avg (distinct cgpa) from student;

(15) Write a sql query to calculate the average cgpa of all students assuming null value as zero value from student table.
Ans:

select avg (nvl (cgpa, 0)) avg from student;

(16) Write a sql query which display student table in which name is arranged according to alphabet in ascending order, there should be a table header “EMPLOYEE TABLE”
And table footer “END”.
Ans:
SQL>ttitle ‘EMPLOYEE TABLE’
SQL>btitle ‘END’
SQL>select * from student order by name;