Sql Mostly used queries.

1.Views

CREATE OR REPLACE VIEW passStudent AS
SELECT StudentId,StudentName
FROM Student
WHERE result=pass

2.GroupBy Clause
Student Table
create table student(studId number(5),studName varchar2(10),marks number(3));  

  • insert into student values(1,’sur’,30);   
  • insert into student values(2,’jit’,65); 
  • alter table student add(collegeId number(3));
  • SQL> update student set collegeId=1 where studId=1;   
  • SQL> update student set collegeId=2 where studId=2; 
  • SQL> insert into student values(3,’ramesh’,45,1); 

  Question select collegeId,count(collegeId) from student group by collegeId;                          

COLLEGEID COUNT(COLLEGEID)                                                                                                                  
  • ———- —————-                                                                                                                        
             1                2                                                                                                                        

             2                1                                               

Onother Table College

CREATE TABLE TestSuresh.college ( cid INT(7) NOT NULL , cname VARCHAR(15) NOT NULL ) ENGINE = InnoDB;
  • INSERT INTO college (cid, cname) VALUES (‘1’, ‘pvm’), (‘2’, ‘asc’)

                                                                                                                        

SQL> select * from student order by marks desc;                                                                                                    
     
STUDID  STUDNAME         MARKS   COLLEGEID
2 jit 65 2
3 ramesh 45 1
1 suresh 30 1

                                                                                                       

Question:To Get the first three records in ascending order

select distinct(s1.marks) from student s1 where 3>=(select count(distinct(marks)) from student s2 where s1.marks<=s2. marks) order by s1.marks desc       

Question:To get The student also include students which does not belong to any college

This gives you hint you need to use left join.
select * from student s1 left join college c on s1.c_id=c.cid                

One thought on “Tricky Sql Interview Questions”

Leave a Reply

Your email address will not be published. Required fields are marked *