Tricky Sql Interview Questions
Sql Mostly used queries.
1.Views
CREATE OR REPLACE VIEW passStudent AS
2.GroupBy Clause
Student Table
create table student(studId number(5),studName varchar2(10),marks number(3));
1.Views
CREATE OR REPLACE VIEW passStudent AS
SELECT StudentId,StudentName
FROM Student
WHERE result=pass
2.GroupBy ClauseStudent 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 22 1
Onother Table College
CREATE TABLE `TestSuresh`.`college` ( `cid` INT(7) NOT NULL , `cname` VARCHAR(15) NOT NULL ) ENGINE = InnoDB;
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
select * from student s1 left join college c on s1.c_id=c.cid
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
0 comments: