Interview - SQL queries

Company1:

create database temp1

use temp1

create table emp (id int,names varchar(20))

insert into emp values (25,'venkates')

select * from emp

select names from emp order by  names    /* by default ascending order */

select names from emp order by  names asc

select names from emp order by  names desc

select names from emp where names like 'v%'

select names from emp where names like '%s'

select names from emp where names like 'venkat%'

select names from emp where names!='venkat'

Company2:

create database employee

use employee

create table emp (emp_id int primary key,names varchar(20),sal money,join_date datetime)

insert into emp values(8113,'koti',15000,'01/08/2011')

drop table emp

/* by default order by asceding order .If we want in ascending order then order by names asc*/

select *from emp order by names desc

select names from emp where sal>=18000 order by names desc

update emp set sal=18000 where  names='ram'

select * from emp where sal>=18000

create table student(stu_id int unique,names nvarchar(20),branch varchar(20))

select * from student

insert into student values (162,'koti','cse') 

select e.names from emp e , student s where e.sal>16000 and s.branch='cse'

select e.names from emp e inner join student s on e.names=s.names and e.sal>16000 and s.branch='cse'

select names from emp where names like 'ven%'   /* it would display 'ven' record also' */

select max(sal) as max_salary from emp

select top 3 sal from emp order by sal desc

/* find nth highest salary */

select min(sal) from emp where sal in (select top 4 sal from emp order by sal desc) 

create table hcl1(id int,names varchar(20),salary int,address varchar(20))

insert into hcl1 values (1234,'',234,'bang')  /* want to give second parameter as blank */

insert into hcl1 values (81234,null,234,'bang')

insert into hcl1 values ('','venky',234,'bang') 

insert into hcl1 values (null,'venky',234,'bang') 

insert into hcl1 values (default,'venky',234,'bang') 

select names from hcl1 where names=null

select names from hcl1 where names is null

select names from hcl1 where names=''

select names from hcl1 where names=""

select * from hcl1

delete from hcl1

select e.names from emp e inner join student s on  e.names=s.names and e.sal>16000 and s.branch='cse'

select e.names from emp e left join student s on  e.names=s.names and e.sal>16000 and s.branch='cse'

select e.names from emp e right join student s on  e.names=s.names and e.sal>16000 and s.branch='cse'

select top 1 * from hcl1 order by id desc 

select * from emp

sElect * from eMp where names <> 'venkat'   /* sql is not case sensitive */

/*The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. */

select * from student

select names from student group by branch  /* wrong */

select names,sum(sal) from emp group by names

/* The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. */

select names,sum(sal) from emp group by names having sum(sal)>18000

select names,sum(sal) from emp where sum(sal)>18000  /* wrong */

CREATE DATABASE TEMP1

CREATE TABLE TEST1(ID INT PRIMARY KEY)

CREATE TABLE TEST2(NUM INT FOREIGN KEY REFERENCES TEST1(ID))

Company3:

create  database temp3

create table test3(id int,lastname varchar(20),firstname varchar(20))

insert into test3 values(2134,'nabcd','nbcde')

select * from test3 

select count(id) from test3

select distinct firstname,lastname from test3

select distinct firstname,count(firstname) from test3

group by firstname

create table college(id int ,branch varchar(20),name varchar(20)) 

insert into college values(12,'cse','venky')

insert into college values(32,'cse','koti')

insert into college values(28,'ece','raju')

select branch,count(branch) from college group by branch

Company4:

use employee

create table emp1 (ename varchar(20),sal int)

insert into emp1 values ('ravi',15000)

insert into emp1 values ('gopi',18000)

insert into emp1 values ('venky',25000)

insert into emp1 values ('kasi',15000)

insert into emp1 values ('praveen',25000)

insert into emp1 values ('koti',18000)

insert into emp1 values ('kasi',15000)

insert into emp1 values ('ravindra',15000)

select * from emp1

/* select sal and count of employee in descending order   */

select sal,count(ename) from emp1 group by sal order by sal desc

create table emp2 (ename varchar(20),gender char(1))

insert into emp2 values ('abcd','m')

insert into emp2 values ('bcde','m')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','m')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','m')

delete  from emp2

select * from emp2

BEGIN TRANSACTION

UPDATE emp2

SET gender = CASE  

WHEN gender = 'm' THEN 'f' ELSE 'm' END

create table emp3(ename varchar(20),dname varchar(20))

insert into emp3 values('abcd','cse')

insert into emp3 values('bcde','ece')

insert into emp3 values('cdef','eee')

insert into emp3 values('defg','cse')

insert into emp3 values('efgh','ece')

insert into emp3 values('fghi','ece')

select * from emp3

delete from emp3

create table dept(dname varchar(20),city varchar(20))

insert into dept values('ece','hyd')

insert into dept values('cse','bang')

insert into dept values('ece','hyd')

insert into dept values('cse','hyd')

select * from dept

select distinct e.ename,d.city from emp3 e inner join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e left join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e right join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e full join dept d on e.dname=d.dname and d.city='hyd'

create table emp4(id int,ename varchar(20),sal int,dname varchar(20))

insert into emp4 values(234,'gopi',15000,'it')

select * from emp4

create procedure sample1(@newid int,@dname varchar(20))

as

begin

update emp4 set id=@newid where dname=@dname

end

execute sample1 345,'it'

execute sample 234,'it'

create procedure sample3(@newid int,@empname varchar(20),@esal int,@dname varchar(20))

as

begin

insert into emp4 values(@newid,@empname,@esal,@dname)

end

execute sample3 567,'venkat',1000,'cse'

execute sample3 '567','venkat','1000','cse'

select * from emp4

update emp4 set id=650 where sal=1000

Company5:

use employee

create table emp1 (ename varchar(20),sal int)

insert into emp1 values ('ravi',15000)

insert into emp1 values ('gopi',18000)

insert into emp1 values ('venky',25000)

insert into emp1 values ('kasi',15000)

insert into emp1 values ('praveen',25000)

insert into emp1 values ('koti',18000)

insert into emp1 values ('kasi',15000)

insert into emp1 values ('ravindra',15000)

select * from emp1

/* select sal and count of employee in descending order   */

select sal,count(ename) from emp1 group by sal order by sal desc

create table emp2 (ename varchar(20),gender char(1))

insert into emp2 values ('abcd','m')

insert into emp2 values ('bcde','m')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','m')

insert into emp2 values ('abcd','f')

insert into emp2 values ('abcd','m')

delete  from emp2

select * from emp2

BEGIN TRANSACTION

UPDATE emp2

SET gender = CASE  

WHEN gender = 'm' THEN 'f' ELSE 'm' END

select * from emp2

create table emp3(ename varchar(20),dname varchar(20))

insert into emp3 values('abcd','cse')

insert into emp3 values('bcde','ece')

insert into emp3 values('cdef','ede')

insert into emp3 values('defg','cse')

insert into emp3 values('efgh','ese')

insert into emp3 values('fghi','ece')

select * from emp3

select ename from emp3 group by ename having count(ename)>1

select * from emp3 where ename in (select ename from emp3 group by ename having count(*)>1)

delete from emp3

create table dept(dname varchar(20),city varchar(20))

insert into dept values('ece','hyd')

insert into dept values('cse','bang')

insert into dept values('ece','hyd')

insert into dept values('cse','hyd')

select * from dept

select distinct e.ename,d.city from emp3 e inner join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e left join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e right join dept d on e.dname=d.dname and d.city='hyd'

select distinct e.ename,d.city from emp3 e full join dept d on e.dname=d.dname and d.city='hyd'

create table emp4(id int,ename varchar(20),sal int,dname varchar(20))

insert into emp4 values(234,'gopi',15000,'it')

select * from emp4

create procedure sample1(@newid int,@dname varchar(20))

as

begin

update emp4 set id=@newid where dname=@dname

end

execute sample1 345,'it'

execute sample 234,'it'

create procedure sample3(@newid int,@empname varchar(20),@esal int,@dname varchar(20))

as

begin

insert into emp4 values(@newid,@empname,@esal,@dname)

end

execute sample3 567,'venkat',1000,'cse'

execute sample3 '567','venkat','1000','cse'

select * from emp4

update emp4 set id=650 where sal=1000

create table emp5(ename varchar(20),sal int)

insert into emp5 values ('abcd',456)

insert into emp5 values ('abcd',1456)

insert into emp5 values ('abcd',4856)

insert into emp5 values ('abcd',2456)

insert into emp5 values ('abcd',4856)

insert into emp5 values ('abcd',4596)

select * from emp5

/* select max salary with number of employees */

select top 1 sal,count(ename) from emp5 group by sal order by sal desc 

/* select  salary with number of employees */

select sal,count(ename) from emp5 group by sal

/* Constraints in SQL */

create table checkemp(eid int check (eid>5000),enames varchar(50))

insert into checkemp values (12345,'venkt')

insert into checkemp values (2345,'venkt')

select * from checkemp

/* The NOT NULL constraint enforces a column to NOT accept NULL values.  */

create table notnullemp(eid int not null,enames varchar(50))

insert into notnullemp values (2345,'venkat')

insert into notnullemp values ('','venkat')

insert into notnullemp values (null,'venkat')

select * from notnullemp

create table nullemp(eid int ,enames varchar(50))

insert into nullemp values (2345,'venkat')

insert into nullemp values ('','venkat')

insert into nullemp values (null,'venkat')

select * from nullemp

/* practise */

create table emp5(ename varchar(20),sal int)

insert into emp5 values ('ravi',2345)

insert into emp5 values ('sdd',1234)

insert into emp5 values ('dff',3456)

insert into emp5 values ('BCDE',6789)

insert into emp5 values ('CDEF',5789)

select * from emp5

select max(sal) from emp5     /* works */

select ename,max(sal) from emp5   /* wrong */

select ename,max(sal)  from emp5 group by sal

select sal,count(ename) from emp5 group by sal

select sal,count(ename) from emp5 group by sal having  sal>2345 and count(ename)>1 order by sal desc 

select top 1 sal,count(ename) from emp5 group by sal order by sal desc

select min(sal)  from emp5 where sal in (select top 2 sal from emp5 order by sal desc)

create table emp6(id int primary key,ename varchar(20))

insert into emp6 values(34,'venkat')

insert into emp6 values(null,'venkat')

insert into emp6 values(345,'')

select * from emp6

create table emp7(id int unique,ename varchar(20))

insert into emp7 values(34,'venkat')

insert into emp7 values(null,'venkat')

insert into emp7 values(345,'')

select * from emp7

create table emp8(id1 int,ename1 varchar(20),sal1 int)

insert into emp8 values(34,'venkat',1234)

insert into emp8 values(38,'sfs',5675)

insert into emp8 values(56,'jkh',2424)

insert into emp8 values(344,'qwe',7897)

create procedure emp9(@id int,@ename varchar(20),@sal int)

as 

begin

select id1 from emp8 where id1=@id

end

execute emp9 568,'jh454k',244

truncate table emp8

select * from emp8

drop  table emp8

alter table emp8 add adrress varchar(20)

create database employee2

use employee2

create table emp1(eid int ,sal int)

create table emp2(eid int ,sal int)

insert into emp2 values (5678,18000)

insert into emp2 values (2345,15000)

insert into emp2 values (7890,20000)

select * from emp2

select emp1.ename,emp2.eid from employee.dbo.emp3 inner join employee1.dbo.emp2 on emp1.sal=emp2.sal