SQL-RDBMS Queries and Question Bank

SQL QUERI ES MD MUDASSI R K  Create an employees table (empid, empname, jobname, salary, dateofjoin, dateofbirth, commission ). 1. List unique job names. select distinct jobname from emp2 2. Display the names who earn annual salary more than 1,00,000 select * from empm1 where salary >100000 3. Display employees who joined between 20-Feb-2010, and 1-May-2012 4. List last name and salary who earn in the range 5,000 and 12,000, and are in department 20 or 50 select empname,salary from emp2 where salary between 100000 and 110000 5. Display the name and hire date who were born in 1995. select empname,doj from emp2 where datepart(year,dob)=199

6. Display the name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions. SELECT salary, commision, empname FROM emp2 where commision >0 order by commision desc 7. Display the name of all employees whose third letter contains is an ‘a’ in their name. select * from emp2 where empname like '__l%' 8. Create a function to display a given two digit number in words. create function n2w(@n int ) returns varchar(10) as begin declare @w varchar(20) if(@n=0) set @w='zero' if(@n=1) set @w='one' if(@n=2) set @w='two' if(@n=3) set @w='three' if(@n=4) set @w='four' if(@n=5) set @w='five'

if(@n=6) set @w='six' if(@n=7) set @w='seven' if(@n=8) set @w='eight' if(@n=9) set @w='nine' return @w end create function words(@n int) returns varchar(20) as begin declare @w VARCHAR(20) declare @x int declare @y int set @x=@n/10 set @y=@n/10 set @w=dbo.n2w(@x)+' '+dbo.n2w(@y) return @w end select dbo.words(33)

9. Create a user defined function to find the maximum of two numbers create function maximum(@a int,@b int) returns int as begin declare @c int if(@a>@b) set @c=@a else set @c=@b return @c end select dbo.maximum(12,23) 10. Create a trigger to insert record in another table whenever a record is inserted in employees table. create trigger ash on emp2 after insert as insert into emp2backup select* from inserted

select * from emp2backup ------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------  Create an employees table (empid, empname, jobname, salary, dateofjoin, dateofbirth, commission ). 1. Display all records. If commission is NULL then display zero. select *, coalesce(commision,0)from emp2 2. Increase salary by 15% whose salary is below 10000. SELECT *, salary+salary*15/100 from emp2 where salary<200000 3. Calculate the number of months between today and the date the employee was joined. select *,datediff (month, doj,getdate()) from emp2 4. Display the employees who earn less than the average salary. select empname from emp2 where salary<(select avg(salary) from emp2) 5. Displays the difference between the highest and lowest salaries. select max(salary)-min(salary) from emp2

6. Display employees who have the same jobname as of Mr. Anil. select empname from emp2 where jobname=(select jobname from emp2 where empname like 'aamir') 7. Create a trigger to store all deleted records in another table whenever a record is deleted. CREATE TRIGGER ass on studinfo after delete as insert into deletedstudent select * from deleted select* from studinfo select * from deletedstudent delete studinfo where studid=338 8. Create a procedure to output employee name for a given empid. create procedure getnames @id int,@name varchar(12) output as select* from emp2 where empid=@id declare @x varchar(40) execute getname 111,@x output

print @x 9. Create a function to find the maximum of two given numbers. create function maximum(@a int,@b int) returns int as begin declare @c int if(@a>@b) set @c=@a else set @c=@b return @c end select dbo.maximum(12,23) 10. Use any four string functions in a query. select len('thufail') select upper('thufail') select lower('THUFAIL') SELECT ascii('A') ------------------------------------------------------------------------------------------------------------- ------------------

m3 Create a Students table (studid, name, dob, friendid, mark1, mark2, mark3, total, average, city). 1. Add constraint to mark1,mark2 and mark3 to accept values from 0 to 100. alter table studinfo add constraint c1 check (m1 between 0 and 100) 2. List the age of each student. select * ,datediff(year,dob,getdate()) as age from studinfo 3. List the students whose name begins with vowel. select * from studinfo where studname like '[aeiou]%' 4. Prefix all names with ‘Mr.’ if name doesnot begin with ‘Mr.’ select 'mr.'+studname from studinfo where studname not like 'Mr.' 5. List each student name and his friend name. SELECT studinfo.studname, frnd.frndid, studinfo.friendid FROM studinfo INNER JOIN frnd ON studinfo.friendid = frnd.frndid

6. Find out in how many different cities students have come from. select count(*) city from studinfo group by city. 7. Compute Total and Average of each student. update studinfo set total=(m1+m2+m3) update studinfo set avrg=avg(total) 8. Add a field result and set it to PASS if each mark is >=40 else FAIL. update studinfo set result=case when total>=35 then 'pass' when total<35 then 'fail' end from studinfo 9. Create a trigger that ensures that you never allows multiple records at once from student table. create trigger safe on studinfo after insert as if(select count(*) from inserted)>1 rollback transaction //test query is needed

10. Create a function to find the product of two given numbers. create function maximum(@a int,@b int) returns int as begin declare @c int if(@a>@b) set @c=@a else set @c=@b return @c end select dbo.maximum(12,23) ------------------------------------------------------------------------------------------------------------- ----------------------- M4 Create a Students table (studid, name, dob, friendid, mark1, mark2, mark3, total, average, city). 1. How many days, hours, minutes are left for 1st May 2013.

select datediff(day,getdate(),'2014/05/01') 2. List students who are in the age group of 20 to 25 select * ,datediff(year,dob,getdate()) as age from studinfo where datediff(year,dob,getdate()) between 20 and 100 3. List the eldest and the youngest student. select top 1 * from studinfo order by dob desc select top 1 * from studinfo order by dob 4. Add a field Division and compute division as Distinction if total is 200-300, First Class if total is 100-199 and Second class if total is less than 100. alter table studinfo add div varchar(50) update studinfo set div=case when total between 200 and 300 then 'first class' when total between 100 and 199 then 'second class' when total<100 then 'third class' end from studinfo

5. List student name and his friend name. SELECT frnd.fname, studinfo.studname FROM studinfo INNER JOIN frnd ON studinfo.friendid = frnd.frndid 6. List students who have no friends. SELECT top 1 * ,frnd.fname, studinfo.studname FROM studinfo INNER JOIN frnd ON studinfo.friendid = frnd.frndid 7. List all students who are elder than student Peter 8. Write a function to convert the given temperature to either Centigrade or Fahrenheit CREATE function conver(@x int,@t varchar(50)) returns float as begin declare @temp float if @t ='c' set @temp=(@x-32)*5/9 else set @temp=9*@x/5+32

return @temp end select dbo.conver(0,'f') select dbo.conver(32,'c') 9. Create a trigger to display the old and new contents during an update command. create trigger display on studinfo after delete as select d.studname+'is chnaged to'+i.studname from inserted i,deleted d update studinfo set studname='rahul' where studid=111 10. Write a procedure to delete a record given an id. create procedure getnames @id int,@n varchar(10) output as

delete empname from emp2 where empid=@id declare @name varchar(30) exec getname 111,@name output print @name ------------------------------------------------------------------------------------------------------------- ---------------------------------------- M5 Create two tables. customers (customerid, customername) and orders (orderid, orderdate, orderamount, customerid) 1. Display Name, orderdate, orderamount of all customers. SELECT customers.custname, orders.orderdate, orders.orderamount FROM customers right JOIN orders ON customers.custid = orders.custid 2. List customers who have not placed any orders. select c.custid,custname,orderamount from customers as c left join orders as o on c.custid=o.custid

where orderamount is null or orderamount=0 3. List the order amount placed by each customer SELECT customers.custname, orders.orderamount FROM customers INNER JOIN orders ON customers.custid = orders.custid 4. Find the number of orders placed by each customer (in ascending order) select custname,count(*) as 'num' from customers join orders on customers.custid=orders.custid group by custname 5. What is the highest and lowest order placed in the month of March. select max(orderamount) 'high',min(orderamount)'low' from orders where datepart(month,orderdate)=3 6. Find the two customers who have placed least orders. select top 2 custname,sum(orderamount) from customers join orders on customers .custid= orders.custid group by custname

order by 2 7. Find the total order amount placed on each date. select sum(orderamount)from orders group by orderdate 8. Create a trigger to display a print message whenever a table is created or deleted create trigger printtri on database for create_table,drop_table as print 'table created/deleted' create table temp(id int) DROP TABLE TEMP 9. Write a function to find the order amount of a given ordered. CREATE function ord(@d int) returns int as begin declare @m int select @m=orderid from orders where orderid=@d

return @m end select dbo.ord(123) 10. Write a procedure to print multiplication table of a given number create procedure mtable @n int as declare @i int set @i=1 while(@i<=10) begin print str(@n)+'X'+str(@i)+'='+str(@i*@n) set @i=@i+1 end mtable 15 =================================================================== ===================== M6 Create two tables.

customers (customerid, customername) and orders (orderid, orderdate, orderamount, customerid) 1. Find the Maximum, Minimum, Total, Average and number of orders of each customer. Give proper heading. select custname ,max(orderamount) 'MAX' ,min(orderamount) 'MIN' , sum(orderamount) 'TOTAL' ,avg(orderamount) 'AVERAGE' ,count(orderamount) 'total no order' from customers join orders on customers.custid=orders.custid group by custname 2. List 50% of orders with highest order amounts in the date order. 3. Copy the Customers table to newcustomers table with out any records. select * into studnew from studinfo where 1=2 4. List the order amount placed by each customer on each day.

SELECT orders.orderamount, customers.custname, orders.orderdate FROM customers INNER JOIN orders ON customers.custid = orders.custid 5. Find the two customers who have placed highest orders. select top 2 custname,sum(orderamount) from customers join orders on customers.custid=orders.custid group by custname 6. Which months have got the highest and the lowest orders? select datepart(month,orderdate) from orders where orderamount=(select max(orderamount) from orders) union select datepart(month,orderdate) from orders where orderamount=(select min(orderamount) from orders) 7. Find number of orders and total order amount received in each month. select count(orderid)'number of order' from orders select sum(orderamount) from orders group by orderdate 8. Create a trigger that ensures that you never allow deletion of multiple records at once.

create trigger thu on studinfo after delete as delete from studinfo 9. Create a procedure to print the ascii table. create procedure asciitab as declare @i int set @i=0 while(@i<=255) begin print str(@i)+''+char(@i) set @i=@i+1 end asciitab 10. Create a simple trigger. create trigger mytri on emp2 after delete

as print 'table altered' drop emp2 where empid=1234 =================================================================== ===================== M7 Create the following four tables. Location ( locationid, locationname) Department ( deptid, deptname, locationid) Job ( jobid, jobname) Employees ( empid, name, , dateof join, salary, commission, deptid, managerid) 1. List all department details with its location name. SELECT department.deptid, department.deptname, location.locname, department.locid FROM department LEFT OUTER JOIN location ON department.locid = location.locid 2. List out the employees whose name length is 4 and start with “S” select * from employees where empname like 'b%'

3. List employees and their commission. If commission is null, then display 0 select empname,coalesce(commission,0) 'commission' from employees 4. How many employees are working in each department? select deptname,count(*) 'no of employee' from employees join department on employees.deptid=department.deptid group by deptname 5. List the number of employees joined in each month in ascending order. select datepart(month,doj) 'month',count(*) 'no of emp' from employees group by datepart(month,doj) order by (month) 6. How many employees joined each month in 1985. select datepart(year,doj),count(*) 'no of emp' from employees group by datepart(year,doj),datepart(month,doj) having datepart(year,doj)=2012 7. Create a procedure to list the employees of a given departmet create procedure ems @a int as

select * from employees where deptid=@a ems 111 8. Add a computed column 9. Create a trigger to display the old and new contents during an update command. create trigger disc on employees after delete as select d.empname+'is changed to'+i.empname from inserted i,deleted d update employees set empname='harrish' where empid=434 10. Copy employees table to another table without records. select * into empnew from employees where 1=2 =================================================================== ===================== M8

Create the following four tables. Location ( locationid, locationname) Department ( deptid, deptname, locationid) Job ( jobid, jobname) Employees ( empid, name, , dateof join, salary, commission, deptid, managerid) 1. How many designations are in the organization? select count(jobname) from job 2. Display the employee details with their manager names. select e.empname 'empnmae',m.empname 'maneger' from employees e join employees m on e.manegerid=m.empid 3. Show the number of employees working under every manager select m.empname 'maneger name',count(*) from employees e join employees m on e.manegerid group by m.empname 4. List out the job wise maximum salary, minimum salary, and average salaries of the employees. select jobname,min(salary),max(salary),avg(salary)from employees join job on employees.jobid=job.jobid

5. List out the employees whose name start with “S” and does not end with “H” select * from employees where empname like 'b%' 6. How many employees have joined in January month? select * from employees where empname like 'b%' 7. Display the employee who got the maximum salary. select empname,salary from employees where salary=(select max(salary)from employees) 8. If a SIM card is free for 1000 hours, find the last valid date. select dateadd(hour,1000,getdate()) 9. Create a trigger to display the old and new contents during an update command. create trigger disc on employees after delete as select d.empname+'is changed to'+i.empname from inserted i,deleted d update employees set empname='harrish' where empid=434

10. Create a simple procedure. create procedure news as select * from employees execute news =================================================================== ===================== M9 Create a table PROGRAMMER (name, dateofbirth, dateofjoin, sex, salary, proficiency) 1.What is the average age of male programmers? select * ,datediff(year,dob,getdate()) as age from program where sex like 'male' 2.List the experience in years for each programmer in descending order select pname,datediff(year,doj,getdate()) as 'exp' from program order by pname desc 3.Who are the programmers who celebrate their birthdays during the current month?

select * from program where datepart(month,dob)=datepart(month,getdate()) 4.How many male , female programmers are there? select count(*) from program where sex like 'male' select count(*) from program where sex like 'female' 5.Display the details of those who don’t know C, C++ or Pascal. select pname from program where prof not like 'pascal' 6.Produce the following output for all the male programmers Programmer Mr. Arvind – has 15 years of experience select 'mr.'+pname+'_has',(datediff(year,doj,getdate()))'years of exp' from program 7.Write query to insert characters in a string. select stuff('thufail',3,3,'s') 8.Add and Delete a Primary key Constraint.

alter table program add primary key(pname) alter table program drop primary key(pname) 9.Write a procedure to print multiplication table of a given number. reate procedure mtable @n int as declare @i int set @i=1 while(@i<=10) begin print str(@n)+'X'+str(@i)+'='+str(@i*@n) set @i=@i+1 end mtable 15 10.Create a trigger to store all deleted records in another table whenever a record is deleted. create trigger ast on program after delete as insert into emp2backup select* from deleted

select * from emp2backup delete program where pname like 'javeed' =================================================================== ===================== M10 Create the tables Sailors (sid,sname,rating,age) Boats (bid,bname,color) Reserves(sid,bid,date) 1. Create above tables with necessary relation ships. 2. Find the names of sailors who have reserved boat number 103 select sname from sailores join reserve on sailores.id=reserve.sid and bid =103

3. Find the sids of sailors who have reserved a Green boat ELECT sailores.sname, reserve.bid FROM sailores JOIN reserve ON sailores.id = reserve.sid and bid=(select bid from boats where color like 'green') 4. Find sailors whose rating is greater than that of some sailor called Kishan select * from sailores where rate>(select rate from sailores where sname like 'aaa') 5. Find the sailors with the highest rating select * from sailores where rate=(select max(rate) from sailores ) 6. Find name and age of the eldest sailor. elect top 1 with ties * from sailores order by age desc 7. List Sailors Name and Experience as follows Experience - Age Senior >=50

Middle >=40 Junior >=25 Beginner < 25. select *,case when age>=50 then 'senior' when age>=40 then 'middler' when age>=25 then 'junior' else 'begin' end from sailores 8. Find the names of sailors who’ve reserved a red and a green boat. select s.sname from boats b join reserve r on b.bid=r.bid join sailores s on r.sid=s.id where b.color like 'green' intersect select s.sname from boats b join reserve r on b.bid=r.bid join sailores s on r.sid=s.id where b.color like 'blue'

9. Write a function to convert the given temperature to either Centigrade or Fahrenheit. CREATE function conver(@x int,@t varchar(50)) returns float as begin declare @temp float if @t ='c' set @temp=(@x-32)*5/9 else set @temp=9*@x/5+32 return @temp end select dbo.conver(0,'f') select dbo.conver(32,'c') 10. Create a trigger to display the old and new contents during an update command. create trigger display on studinfo after delete

as select d.studname+'is chnaged to'+i.studname from inserted i,deleted d update studinfo set studname='rahul' where studid=111 =================================================================== ===================== M11 Create Student table (regnumber, name, marks, email, contactnumber) 1. Create a user defined function to find the maximum of two numbers as begin declare @c int if(@a>@b) set @c=@a else set @c=@b return @c end

select dbo.maximum(12,23) 2.. Compute the Grade point of each student from his marks Marks Grade Marks Grade 90-100 O+ 60-69 A 80-89 D+ 50-59 B 75-79 D 40-49 C 70-74 A+ 0-39 U update students set grade= case when mark between 90 and 100 then 'o+' when mark between 80 and 89 then 'D+' when mark between 75 and 79 then 'D' when mark between 70 and 74 then 'A+' when mark between 60 and 69 then 'A' when mark between 50 and 59 then 'B' when mark between 40 and 49 then 'C'

when mark between 1 and 39 then 'U' end from students 3. Create a trigger to store all deleted records in another table whenever a record is deleted from a table. create trigger ast on program after delete as insert into emp2backup select* from deleted select * from emp2backup delete program where pname like 'javeed' 4. Create a procedure to print the ascii table create procedure asciitab as declare @i int

set @i=0 while(@i<=255) begin print str(@i)+''+char(@i) set @i=@i+1 end asciitab 5. If a credit card is valid for 100 days from today, find the last valid date. select dateadd(hour,1000,getdate()) 6. Write any four mathematical functions in a query. 7. Write query to find average marks including nulls, excluding nulls. select avg(mark) 'excluding null',sum(mark)/count(*) 'including null' from students 8. List all databases select * from sys.databases 9. Rename a field contactnumber as phonenumber.

10. List students who do not have account in gmail or yahoo. select sname from students where email not like '%@gmail.com%' =================================================================== ===================== M12 Create tables authors ( authorid, name, address, city, state) Titles ( titleid, title, price, publisheddate, authorid) 1. Which authors are not in TN State? select * from author where state not like 'tn' 2. Which title is the most expensive? select top 1 with ties * from title order by price desc 3. Show the latest published title. select top 1 with ties * from title order by pubdate desc

4. Show the title and author name of each title. SELECT title.title, author.aname FROM author INNER JOIN title ON author.aid = title.aid 5. List the authors who live in the same city of Suresh. SELECT author.aname, author.address FROM author INNER JOIN author AS author_1 ON author.address = author_1.address AND author.city = author_1.city AND author.state = author_1.state AND author.aid = author_1.aid 6. Give a discount of 10% in price if the price is above 1000 SELECT *, price-price*10/100 from title where price>1000 7. Which author has published more titles? SELECT top 1 author.aname,count(title.tid) FROM author INNER JOIN title ON author.aid = title.aid group by author.aname order by 2 desc

8. Create a function to compute the Electricity bill from the units consumed. First 100 Units - Rs. 1 per unit Next 100 Units - Rs. 1.50 per unit Next 200 Units - Rs. 3.00 per unit Above 400 Units - Rs. 5.00 per unit. 9. List authors who have not written any title. 10. Add a field gender with a constraint which can accept either Male or Female. select * ,case gender when 'm' then 'Male' when 'f' then 'Female' end from author

SQL-RDBMS Queries and Question Bank

SQL-RDBMS Queries and Question Bank

More Related Content

What's hot

Similar to SQL-RDBMS Queries and Question Bank

Recently uploaded

In this document

SQL-RDBMS Queries and Question Bank