--FOR ACTORS WHO HAVE ACTED IN SAME
MOVIE select a.title from movie a, movie
b
where
a.actor='
a.role ='actor' and
a.role=b.role and
a.title
=b.title --FOR
A-B select empname from emp1 where empname not in( select empname from
emp2 )
--FOR A-B select empname from emp1 a where not exists( select 1 from emp2 b
where a.name=b.name ) --FOR MAX SAL select a.sal from emp a where 1=( select count(*) from emp b where
b.sal>a.sal ) --1.Table A contains column1 which is
primary key and has 2 values (1, 2) and Table B contains column1 which is
primary key and has 2 values (2, 3). Write a query which returns the values that
are not common for the tables and the query should return
onecolumn with 2
records select a.col1 from a,b where a.col1<>(select b.col1 form a,b
where a.col1=b.col1) union select b.col1 from a,b where b.col1<>(select a.col1 from a,b
where a.col1=b.col1) --2.There are 3 tables Titles,
Authors and Title-Authors. Write the query to get --the author name and the number of
books written by that author, --the result should start from the
author who has written the maximum number --of books and end with the author
who has written the minimum number of books. select
authors.authorname,count(authtitles.authorid) from authors,authtitles where
author.authorid=authtitles.authorid group by authors.authorname order by count(authtitles.authorid)
desc --For duplicate rows in a table(
table should have an identity column(id) ) delete from emp where emp.id in -- list 1 - all rows that have
duplicates (select f.id from emp as f where exists (select field1, field2,
count(id) from emp where emp.field1 =
f.field1 and emp.field2
= f.field2 group by emp.field1, emp.field2 having count(emp.id) > 1)) and emp.id not
in --list 2 - one row from each set of
duplicate (select min(id) from emp as f where exists (select field1, field2,
count(id) from emp where emp.field1 =
f.field1 and emp.field2
= f.field2 group by emp.field1, emp.field2 having count(emp.id) > 1) group by field1, field2); --There is a table named Temperature
which contains three fields. The fields are DAYID, Day temperature and day. Get
the result with day temp delta (diff of prev day and to day) with out using
cursor? Select a.day, a.degree-b.degree from temperature a, temperature b
where a.id=b.id+1 --There are two employee tables named
emp1 and emp2. Both contains same structure (salary details) . But Emp2 salary
details are incorrect and emp1 salary details are correct. So, write a query
which corrects salary details of the table emp2 . update a set a.sal=b.sal from emp1 a ,emp2 b where
a.empid=b.empid --to find the employees who are
having more then one phone number select empid,count(phone_no) from emp group by empid having
count(phone_no)>1
|