--FOR ACTORS WHO HAVE ACTED IN SAME MOVIE

select a.title from movie a, movie b

                where     a.actor='arnold' and            b.actor='vandame' and

                                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