Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 1 SELECT * FROM orders_aj WHERE snum IN
- (SELECT snum FROM salesman_aj WHERE city LIKE 'London');
- ONUM AMOUNT ODATE CNUM SNUM
- 3011 9891.88 10-JUN-97 2006 1001
- 3008 4723 10-MAY-97 2006 1001
- 3003 767.19 10-MAR-97 2001 1001
- 3002 1900.1 10-MAR-97 2007 1004
- 2 SELECT sname,commision FROM salesman_aj WHERE snum IN
- (SELECT snum FROM customer_aj WHERE city LIKE 'London');
- SNAME COMMISION
- Piyush 12
- 3 SELECT cnum FROM customer_aj WHERE snum IN (SELECT snum FROM salesman_aj WHERE snum>1002);
- CNUM
- 2007
- 2008
- 2002
- 4 SELECT COUNT(cnum) FROM customer_aj WHERE rating > (SELECT avg(rating) FROM customer_aj WHERE city LIKE 'Surat' );
- COUNT(CNUM)
- 2
- 5 SELECT * FROM orders_aj WHERE cnum IN (SELECT cnum FROM customer_aj WHERE cname = 'Chirag' );
- ONUM AMOUNT ODATE CNUM SNUM
- 3008 4723 10-MAY-97 2006 1001
- 3011 9891.88 10-JUN-97 2006 1001
- 6 SELECT cname,rating FROM customer_aj WHERE cnum IN
- (SELECT cnum FROM orders_aj WHERE amount>
- (SELECT avg (amount) FROM orders_aj));
- CNAME RATING
- Chirag 100
- Lalit 200
- --select cname,rating,cnum from customer_aj where cnum in (select avg(amount) as average,cnum from orders_aj where amount>=avg);
- -- error too many values
- 7 SELECT SUM(amount),snum FROM orders_aj GROUP BY snum HAVING amount > (SELECT MAX(amount) FROM orders_aj );
- -- not a group by statement
- 8 SELECT * FROM orders_aj WHERE odate IN (SELECT odate FROM orders_aj WHERE odate='10-Mar-97');
- ONUM AMOUNT ODATE CNUM SNUM
- 3006 1098.16 10-MAR-97 2008 1007
- 3005 5160.45 10-MAR-97 2003 1002
- 3002 1900.1 10-MAR-97 2007 1004
- 3003 767.19 10-MAR-97 2001 1001
- 3001 18.69 10-MAR-97 2008 1007
- -- correct one
- -- select cname from customer_aj where cnum in (select cnum from orders_aj where odate='10-Mar-97');
- -- CNAME
- -- Harsh
- -- Lalit
- -- Pratik
- -- Chinmay
- 9 SELECT sname,snum FROM salesman_aj WHERE snum IN (SELECT snum FROM customer_aj GROUP BY snum HAVING COUNT(snum)>1);
- SNAME SNUM
- Piyush 1001
- Sejal 1002
Add Comment
Please, Sign In to add comment