1.

SQL> select onum,amount,odate from order1;

ONUM AMOUNT ODATE

—– ——— ———

3001 18.69 10-MAR-99

3002 767.19 10-MAR-99

3003 1900.1 10-MAR-99

3004 5160.45 10-MAR-99

3005 1098.25 10-APR-99

3006 1713.12 10-APR-99

3007 75.75 10-MAY-99

3008 4723 10-MAY-99

3010 9898.87 10-JUN-99

10 rows selected

————————————————————————-

2.

SQL> select * from customer where snum=1001;

CNUM CNAME CITY RATING SNUM

—- ———- ———- ——— ———

2001 HARDIK LONDON 100 1001

2005 CHANDU LONDON 100 1001

——————————————————————————

3.

SQL> select city,sname,snum,commissian from salesman2;

CITY SNAME SNUM COMMISSION

———- ———- ——— ———-

LONDON PIYUSH 1001 12

SURAT NIRAJ 1002 13

LONDON MITI 1003 11

BARODA RAJESH 1004 15

NEW DELHI ANAND 1005 10

PATAN RAM 1006 10

BOMBAY LAXMAN 1007 9

7 rows selected.

—————————————————————————–

4.

SQL> SELECT * FROM CUSTOMER2 WHERE CITY=’SURAT’;

CNUM CNAME CITY RATING SNUM

—– ———- ———- ——— ———

2003 LAXIT SURAT 200 1002

2006 CHAMPAK SURAT 300 1007

—————————————————————————

5.

SQL> SELECT DISTINCT SNUM FROM ORDER2;

SNUM

——

1001

1002

1003

1004

1007

—————————————————————————-

6.

SQL> SELECT * FROM ORDER2 WHERE AMOUNT > 1000;

ONUM AMOUNT ODATE CNUM SNUM

—- ——— ——— ——— ———

3003 1900.1 10-MAR-99 2007 1004

3004 5160.45 10-MAR-99 2003 1002

3005 1098.25 10-APR-99 2007 1007

3006 1713.12 10-APR-99 2002 1003

3008 4723 10-MAY-99 2006 1001

3009 1309.25 10-MAY-99 2004 1002

3010 9898.87 10-JUN-99 2006 1001

————————————————————————–

7.

SQL>SELECT SNUM,SNAME,COMMISSIAN,CITY FROM SALESMAN2

WHERE CITY=’LONDON’ AND COMMISSIAN >10;

SNUM SNAME COMMISSION CITY

——————– ———- ———-

1001 PIYUSH 12 LONDON

1003 MITI 11 LONDON

————————————————————————-

8.

SQL> SELECT RATING FROM CUSTOMER WHERE RATING < 100 OR CITY = ‘ROME’; RATING —— 200 100 ——————————————————————————– 9. SQL>SELECT * FROM ORDER1 WHERE AMOUNT > 1000 AND

SNUM != 1006 AND ODATE != ’03-OCT-99′ ;

ONUM AMOUNT ODATE CNUM SNUM

—– ——— ——— ——— ———

3005 1098.25 04-OCT-99 2007 1007

3006 1713.12 04-OCT-99 2002 1003

3008 4723 05-OCT-99 2006 1001

3009 1309.25 05-OCT-99 2004 1002

3010 9898.87 06-OCT-99 2006 1001

————————-OR——————-

SQL> SELECT * FROM ORDER1 WHERE AMOUNT > 1000

AND (NOT(SNUM=1006 AND ODATE=’10-MAR-99′;

(MARCHANT)

——————————————————————————–

10.

SQL>SELECT * FROM ORDER1 WHERE ODATE=’03-OCT-99′ OR

ODATE=’04-OCT-99′ OR ODATE=’06-OCT-99′;

ONUM AMOUNT ODATE CNUM SNUM

—– ——— ——— ——— ———

3001 18.69 03-OCT-99 2007 1007

3002 767.19 03-OCT-99 2001 1001

3003 1900.1 03-OCT-99 2007 1004

3004 5160.45 03-OCT-99 2003 1002

3005 1098.25 04-OCT-99 2007 1007

3006 1713.12 04-OCT-99 2002 1003

3010 9898.87 06-OCT-99 2006 1001

11.

SQL> SELECT * FROM CUSTOMER WHERE CNAME LIKE ‘C%’;

CNUM CNAME CITY RATING SNUM

—– ———- ———- ——— ———

2005 CHANDU LONDON 100 1001

2006 CHAMPAK SURAT 300 1007

—————————————————————————————————

12.

SQL>SELECT CNAME FROM CUSTOMER WHERE CNAME BETWEEN ‘A%’ AND ‘G%’;

CNAME

———

CHANDU

CHAMPAK

—————-OR————

SQL>SELECT * FROM CUSTOMER WHERE SUBSTR(CNAME,1,1)

BETWEEN ‘A%’ AND ‘G%’;

(MARCHANT)

—————————————————————————–

13.

SQL>SELECT * FROM ORDER1 WHERE AMOUNT = 0 OR AMOUNT = NULL;

no rows selected

——————————————————————————

14.

SQL>SELECT SNUM, MAX(AMOUNT) FROM ORDER1 WHERE SNUM=1002 OR

SNUM=1007 GROUP BY SNUM ;

SNUM MAX(AMOUNT)

——— ———–

1002 5160.45

1007 1098.25

—————————————————————————-

15.

SQL> select count(amount) from order1 where odate=’03-oct-99′;

COUNT(AMOUNT)

————-

4

————————————————————————-

16.

SQL> select sum(amount) from order1;

SUM(AMOUNT)

———–

26664.67

—————————————————————————

17.

SQL> select avg(amount) from order1;

AVG(AMOUNT)

———–

2666.467

—————————————————————————

18.

SQL> select snum,count(snum) from order1 group by snum;

SNUM COUNT(SNUM)

——- ———–

1001 3

1002 3

1003 1

1004 1

1007 2

——————OR——-

SQL> SELECT COUNT(DISTINCT (SNUM)) “TOTAL SALESMAN” FROM ORDER1;

(MARCHANT)

—————————————————————————–

19.

SQL> select odate,max(amount) from order1 group by odate;

ODATE MAX(AMOUNT)

———– ———————

03-OCT-99 5160.45

04-OCT-99 1713.12

05-OCT-99 4723

06-OCT-99 9898.87

—————————————————————————-

20.

SQL>select odate,max(amount) from order1

where odate=’03-oct-99′ group by odate;

ODATE MAX(AMOUNT)

——— ———————-

03-OCT-99 5160.45

—————OR——————

SQL>SELECT SNUM ,MAX(AMOUNT)”LARGEST ORDER” FROM ORDER1

WHERE ODATE =’10-MAR-99′ GROUP BY SNUM;

(MARCHANT)

21.

SQL>select city,count(city) from customer group by city ;

CITY COUNT(CITY)

————- ——————–

BOMBAY 1

LONDON 2

ROME 2

SURAT 2

———————OR——————

SQL> SELECT COUNT(DISTINCT (CNUM)) “TOTAL CITY “FROM CUSTOMER WHERE

CITY IS NOT NULL;

(MARCHANT)

————————————————————————

22.

SQL>select cnum,min(amount) from order1 group by cnum;

CNUM MIN(AMOUNT)

———- ———————

2001 767.19

2002 1713.12

2003 5160.45

2004 75.75

2006 4723

2007 18.69

6 rows selected.

——————————————————————————

23.

SQL> SELECT * FROM CUSTOMER WHERE CNAME LIKE ‘G%’ORDER BY CNAME;

CNUM CNAME CITY RATING SNUM

———- ———— ——— ———– ———

2002 GITA ROME 200 1003

2004 GOVIND BOMBAY 300 1002

——————————————————————————

24.

SQL> SELECT COUNT(DISTINCT (SNUM)) FROM “TOTAL SALESMAN ODATE

FROM ORDER1 GROUP BY ODATE;

——————————————————————————

25.

SQL> SELECT COMMISSION FROM SALESMAN;

COMMISSION

— ——–

12

13

11

15

10

10

9

7 rows selected.

—————————————————————————

26.

SQL>SELECT TO_CHAR(ODATE,’MON/DD/YYYY’),ODATE FROM ORDER1

TO_CHAR ODA ODATE;

———– ———

OCT/03/2099 03-OCT-99

OCT/03/2099 03-OCT-99

OCT/03/2099 03-OCT-99

OCT/03/2099 03-OCT-99

OCT/04/2099 04-OCT-99

OCT/04/2099 04-OCT-99

OCT/05/2099 05-OCT-99

OCT/05/2099 05-OCT-99

OCT/05/2099 05-OCT-99

OCT/06/2099 06-OCT-99

10 rows selected.

—————–OR——————

SQL> SELECT COUNT(ONUM) ” TOTAL ORDER” TO_CHAR(ODATE,’DD/MM/YY)

“DATE” FROM ORDER1 GROUP BY ODATE

(MARCHANT)

—————————————————————————-

27.

SQL>SELECT O.ONUM,O.SNUM,S.COMMISSION FROM

ORDER1 O,SALESMAN S WHERE O.SNUM=S.SNUM AND S.COMMISSION =12;

ONUM SNUM COMMISSION

———- ——— ——————-

3002 1001 12

3008 1001 12

3010 1001 12

—————————OR———————–

SQL> SELECT ONUM,SNUM,AMOUNT * .12 “NET AMOUNT” FROM ORDER1;

(MARCHANT)

—————————————————————————-

28.

SQL> SELECT CITY,MAX(RATING) FROM CUSTOMER GROUP BY CITY;

CITY MAX(RATING)

———- ——————–

BOMBAY 300

LONDON 100

ROME 200

SURAT 300

—————————————————————————-

29.

SQL>SELECT * FROM CUSTOMER ORDER BY RATING DESC;

CNUM CNAME CITY RATING SNUM

——— ———– ———- ———– ———-

2004 GOVIND BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

2002 GITA ROME 200 1003

2003 LAXIT SURAT 200 1002

2001 HARDIK LONDON 100 1001

2005 CHANDU LONDON 100 1001

2007 PRATIK ROME 100 1004

7 rows selected.

——————————————————————————

30.

SQL>SELECT ODATE,COUNT(ONUM)FROM ORDER1 GROUP BY ODATE;

ODATE COUNT(ONUM)

——— ———–

03-OCT-99 4

04-OCT-99 2

05-OCT-99 3

06-OCT-99 1

———————OR———————

SQL> SELECT ODATE,SNUM(AMOUNT) “TOTAL” FROM ORDER1 GROUP BY ODATE;

(MARCHANT)

31.

SQL>SELECT CNAME,SNAME FROM CUSTOMER A,SALESMAN B

WHERE A.SNUM=B.SNUM;

CNAME SNAME

———- ———-

HARDIK PIYUSH

GITA MITI

LAXIT NIRAJ

GOVIND NIRAJ

CHANDU PIYUSH

CHAMPAK LAXMAN

PRATIK RAJESH

7 rows selected.

——-OR——–

SQL>SELECT CNAME,SNAME FROM CUSTOMER,SALESMAN WHERE

SALESMAN.SNUM=CUSTOMER.SNUM;

CNAME SNAME

———- ———-

HARDIK PIYUSH

GITA MITI

LAXIT NIRAJ

GOVIND NIRAJ

CHANDU PIYUSH

CHAMPAK LAXMAN

PRATIK RAJESH

———————————————————————————–

32.

SQL>SELECT A.CITY,B.CITY,A.SNAME,B.CNAME FROM

SALESMAN A,CUSTOMER B WHERE A.SNUM=B.SNUM AND A.CITY =B.CITY;

CITY CITY SNAME CNAME

———- ———- ———- ———-

LONDON LONDON PIYUSH HARDIK

SURAT SURAT NIRAJ LAXIT

LONDON LONDON PIYUSH CHANDU

————————————————————————–

33.

SQL>select s.sname,c.cname,o.amount from

salesman s,customer c,order1 o where

s.snum=o.snum and c.cnum=o.cnum;

SNAME CNAME AMOUNT

———- ———- ———

LAXMAN PRATIK 18.69

PIYUSH HARDIK 767.19

RAJESH PRATIK 1900.1

NIRAJ LAXIT 5160.45

LAXMAN PRATIK 1098.25

MITI GITA 1713.12

NIRAJ GOVIND 75.75

PIYUSH CHAMPAK 4723

NIRAJ GOVIND 1309.25

PIYUSH CHAMPAK 9898.87

10 rows selected.

——————————————————————————

34.

SQL> select o.onum,s.snum,c.cnum,s.city,c.city from

salesman s,customer c,order1 o

where s.snum=o.snum and s.city !=c.city and o.cnum=c.cnum;

ONUM SNUM CNUM CITY CITY

—– ——— ——— ———- ———-

3001 1007 2007 BOMBAY ROME

3003 1004 2007 BARODA ROME

3005 1007 2007 BOMBAY ROME

3006 1003 2002 LONDON ROME

3007 1002 2004 SURAT BOMBAY

3008 1001 2006 LONDON SURAT

3009 1002 2004 SURAT BOMBAY

3010 1001 2006 LONDON SURAT

8 rows selected.

——————————————————————————

35.

sql>

1 select s.sname,c.cnum,c.cname,s.commission from salesman s,

2* customer c where s.snum=c.snum and commission >12;

SNAME CNUM CNAME COMMISSION

———- ——— ———- ———-

NIRAJ 2003 LAXIT 13

NIRAJ 2004 GOVIND 13

RAJESH 2007 PRATIK 15

———————————————————————–

36.

SQL>

1 select distinct s.sname,s.commission,c.cname,c.rating from

2 salesman s,customer c,order1 o

3 where s.snum=o.snum and c.cnum=o.cnum and rating >100

SNAME COMMISSION CNAME RATING

———- ———- ———- ———

MITI 11 GITA 200

NIRAJ 13 GOVIND 300

NIRAJ 13 LAXIT 200

PIYUSH 12 CHAMPAK 300

or

SQL> select distinct s.snum,c.cnum,o.onum,c.rating from salesman s,customer c,order1 o wh

2 s.snum =o.snum and c.cnum=o.cnum and rating>100;

SNUM CNUM ONUM RATING

——— ——— ——— ———

1001 2006 3008 300

1001 2006 3010 300

1002 2003 3004 200

1002 2004 3007 300

1002 2004 3009 300

1003 2002 3006 200

6 rows selected.

————————OR———————–

SQL> SELECT 0.* ,S.COMMISSION (O.AMOUNT,S.COMMISSION(100)”CALCULATE

AMOUNT ” FROM SALESMAS S,CUSTOMER C,ORDER1 O WHERE O.SNUM=S.SNUM AND O.CNUM=

C.CNUM AND RATING > 100;

——————————————————————————————

37.

SQL> select * from customer order by rating;

CNUM CNAME CITY RATING SNUM

——— ———- ———- ——— ———0

2001 HARDIK LONDON 100 1001

2005 CHANDU LONDON 100 1001

2007 PRATIK ROME 100 1004

2002 GITA ROME 200 1003

2003 LAXIT SURAT 200 1002

2004 GOVIND BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

7 rows selected.

———————————————————————————

38.

SQL>SELECT CNAME FROM CUSTOMER WHERE SNUM =(SELECT SNUM FROM SALESMAN WHERE SNAME =’NIRAJ’);

CNAME

———-

LAXIT

GOVIND

———————————————————————————

39.

SQL> select s.sname,c.cname,s.snum from salesman s,customer c

where s.snum=c.snum;

SNAME CNAME SNUM

———- ———- ———

PIYUSH HARDIK 1001

MITI GITA 1003

NIRAJ LAXIT 1002

NIRAJ GOVIND 1002

PIYUSH CHANDU 1001

LAXMAN CHAMPAK 1007

RAJESH PRATIK 1004

7 rows selected.

—————————————————————————————————-

40.

SQL> select distinct city,sname from salesman order by city;

CITY SNAME

———- ———-

BARODA RAJESH

BOMBAY LAXMAN

6LONDON MITI

LONDON PIYUSH

NEW DELHI ANAND

PATAN RAM

SURAT NIRAJ

7 rows selected.

41.

SQL>

1 SELECT CNAME,CITY FROM CUSTOMER WHERE

RATING = (SELECT RATING FROM CUSTOMER

WHERE CNAME=’HARDIK’)

CNAME CITY

———- ———-

HARDIK LONDON

CHANDU LONDON

PRATIK ROME

———————————————————-

42.

SQL> SELECT ONUM,SNUM FROM ORDER1 WHERE

SNUM=(SELECT SNUM FROM SALESMAN WHERE SNAME=’MITI’);

ONUM SNUM

——— ———

3006 1003

OR

SQL> SELECT O.SNUM,O.ONUM FROM SALESMAN S,ORDER1 O WHERE SNAME=’MITI’ AND S.SNUM=O.SNUM;

SNUM ONUM

——— ———

1003 3006

———————————————————-

43.

SQL> SELECT SNUM,ONUM FROM ORDER1 WHERE SNUM=(SELECT SNUM FROM SALESMAN WHERE CITY =’BARODA’)

SNUM ONUM

——— ———

1004 3003

———————————————————-

44.

SQL>

1 SELECT ONUM,SNUM,CNUM FROM ORDER1

WHERE CNUM = (SELECT CNUM FROM CUSTOMER

WHERE CNAME =’HARDIK’)

.

ONUM SNUM CNUM

——— ——— ———

3002 1001 2001

———————————————————-

45.

SQL> SELECT * FROM ORDER1 WHERE AMOUNT > (SELECT AVG(AMOUNT) FROM ORDER1 WHERE ODATE=’04-OCT-99′

ONUM AMOUNT ODATE CNUM SNUM

——— ——— ——— ——— ———

3003 1900.1 03-OCT-99 2007 1004

3004 5160.45 03-OCT-99 2003 1002

3006 1713.12 04-OCT-99 2002 1003

3008 4723 05-OCT-99 2006 1001

3010 9898.87 06-OCT-99 2006 1001

——————————–

46.

SQL> SELECT * FROM ORDER1 WHERE SNUM IN (SELECT SNUM FROM SALESMAN WHERE CITY=’LONDON’)

ONUM AMOUNT ODATE CNUM SNUM

——— ——— ——— ——— ———

3002 767.19 03-OCT-99 2001 1001

3006 1713.12 04-OCT-99 2002 1003

3008 4723 05-OCT-99 2006 1001

3010 9898.87 06-OCT-99 2006 1001

———————————————————-

47.

SQL> SELECT * FROM CUSTOMER WHERE CITY IN (SELECT CITY FROM SALESMAN WHERE CITY =’LONDON’);

CNUM CNAME CITY RATING SNUM

—- ———- ———- ——— ———

2001 HARDIK LONDON 100 1001

2005 CHANDU LONDON 100 1001

———————————————————-

48.

SQL> SELECT C.CNUM,C.CNAME,S.SNUM FROM SALESMAN S,CUSTOMER C WHERE

S.SNUM =C.SNUM AND S.SNAME=’NIRAJ

CNUM CNAME SNUM

——— ———- ———

2003 LAXIT 1002

2004 GOVIND 1002

OR

SQL> SELECT * FROM CUSTOMER WHERE SNUM=(SELECT SNUM FROM SALESMAN WHERE SNAME =’NIRAJ’)

CNUM CNAME CITY RATING SNUM

——— ———- ———- ——— ———

2003 LAXIT SURAT 200 1002

2004 GOVIND BOMBAY 300 1002

———————————————————-

49.

SQL> SELECT * FROM CUSTOMER WHERE RATING > (SELECT

AVG(RATING) FROM CUSTOMER WHERE CITY =’SURAT’)

CNUM CNAME CITY RATING SNUM

——— ———- ———- ——— ———

2004 GOVIND BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

———————————————————-

50.

SQL> 1 SELECT CNUM ,ONUM FROM ORDER1 WHERE

CNUM =(SELECT CNUM FROM CUSTOMER WHERE CNAME=’CHANDU’)

no rows selected

OR

SQL> SELECT C.CNUM,O.CNUM FROM ORDER1 O,CUSTOMER C WHERE C.CNUM=O.CNUM AND CNAME=’CHANDU’;

no rows selected

51.

SQL>

1 SELECT CNAME,RATING FROM CUSTOMER WHERE CNUM > ANY(SELECT

2 CNUM FROM ORDER1 WHERE AMOUNT> ANY(SELECT AVG(AMOUNT) FROM ORDER1))

CNAME RATING

———- ———

GOVIND 300

CHANDU 100

CHAMPAK 300

PRATIK 100

—————————————————————-

52.

SQL>

1 SELECT C1.CNAME,O1.ONUM FROM CUSTOMER C1,ORDER1 O1

2* WHERE O1.ODATE=’03-OCT-99′ AND O1.CNUM =C1.CNUM

CNAME ONUM

———- ———

PRATIK 3001

HARDIK 3002

PRATIK 3003

LAXIT 3004

OR

SQL>

1 SELECT C1.CNAME,O1.ONUM FROM CUSTOMER C1,ORDER1 O1

2 WHERE O1.ODATE IN(SELECT ODATE FROM ORDER1 WHERE ODATE=’03-OCT-99′) AND O1.CNUM=C1.CNUM

CNAME ONUM

———- ———

PRATIK 3001

HARDIK 3002

PRATIK 3003

LAXIT 3004

——————————————————————

53.

SQL>

1 SELECT SNAME,SNUM FROM SALESMAN WHERE

2 SNUM = ANY (SELECT SNUM FROM CUSTOMER)

SNAME SNUM

———- ———

PIYUSH 1001

NIRAJ 1002

MITI 1003

RAJESH 1004

LAXMAN 1007

——————————————————————–

54.

SQL> select odate,amount from order1 where amount > 2000;

ODATE AMOUNT

——— ———

03-OCT-99 5160.45

05-OCT-99 4723

06-OCT-99 9898.87

———————————————————————-

55.

SQL> select * from customer where (city ,rating ) in (select city,max(rating) from

2 customer group by city);

CNUM CNAME CITY RATING SNUM

——— ———- ———- ——— ———

2004 GOVIND BOMBAY 300 1002

2001 HARDIK LONDON 100 1001

2005 CHANDU LONDON 100 1001

2002 GITA ROME 200 1003

2006 CHAMPAK SURAT 300 1007

————————————————————————-

56.

SQL> SELECT S.SNUM,S.SNAME FROM SALESMAN S WHERE S.SNUM =ANY

2 (SELECT SNUM FROM CUSTOMER WHERE S.CITY= CITY)

SNUM SNAME

—- ———-

1001 PIYUSH

1002 NIRAJ

OR

SQL> SELECT DISTINCT S.SNUM,S.SNAME FROM SALESMAN S ,CUSTOMER C

WHERE S.SNUM =C.SNUM AND S.CITY=C.CITY

SNUM SNAME

——— ———-

1001 PIYUSH

1002 NIRAJ

——————————————————————————

57.

SQL> SELECT SNUM,SNAME FROM SALESMAN WHERE SNUM = ANY

(SELECT SNUM FROM CUSTOMER WHERE RATING >300)

no rows selected

——————————————————————————

58.

SQL>SELECT S.SNUM,S.SNAME,C.CNUM,C.CNAME FROM SALESMAN S,CUSTOMER C

WHERE S.SNUM =C.SNUM AND S.CITY =C.CITY;

SNUM SNAME CNUM CNAME

——— ———- ——— ———-

1001 PIYUSH 2001 HARDIK

1002 NIRAJ 2003 LAXIT

1001 PIYUSH 2005 CHANDU

——————————————————————————

59.

SQL> SELECT S.SNAME,C.CNAME FROM SALESMAN S ,CUSTOMER C WHERE

S.SNUM =C.SNUM ORDER BY S.SNAME,C.CNAME

SNAME CNAME

———- ———-

LAXMAN CHAMPAK

MITI GITA

NIRAJ GOVIND

NIRAJ LAXIT

PIYUSH CHANDU

PIYUSH HARDIK

RAJESH PRATIK

7 rows selected.

——————————————————————————

60.

SQL> SELECT * FROM CUSTOMER WHERE RATING >

2 (SELECT MAX(RATING) FROM CUSTOMER WHERE CITY=’ROME’)

CNUM CNAME CITY RATING SNUM

——— ———- ———- ——— ———

2004 GOVIND BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

61.

SQL > SELECT * FROM ORDER1 WHERE AMOUNT >ANY (SELECT AMOUNT

FROM ORDER1 WHERE ODATE=’06-OCT-99′)

no rows selected

————————————————————————–

62.

SQL>SELECT ONUM,CNUM,SNUM,AMOUNT FROM ORDER1 WHERE AMOUNT < ANY (SELECT MIN(AMOUNT) FROM ORDER1 WHERE CNUM IN (SELECT CNUM FROM CUSTOMER WHERE CITY=’ROME’)) RUN COMPLETE ————————————————————————— 63. SQL>SELECT * FROM CUSTOMER WHERE RATING >ANY

(SELECT MAX(RATING) FROM CUSTOMER WHERE CITY=’ROME’)

CNUM CNAME CITY RATING SNUM

—- ——————– ———- ——— ———

2004 GOVINDA BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

——OR—–

SQL> SELECT * FROM CUSTOMER WHERE RATING >ALL (SELECT RATING FROM CUSTOMER WHERE CITY =’ROME’)

CNUM CNAME CITY RATING SNUM

—– ——————– ———- ——— ———

2004 GOVINDA BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

——————————————————————————————-

64.

SQL> SELECT A.CNUM,A.CNAME,A.RATING FROM CUSTOMER A,CUSTOMER B

WHERE A.CNUM=B.CNUM AND A.CITY <>’SURAT’ AND A.RATING =B.RATING

CNUM CNAME RATING

—- ——————– ———

2001 HARDIK 100

2002 GITA 200

2004 GOVINDA 300

2005 CHANDU 100

2007 PRATIK 100

————————————————————————-

65.

SQL> SELECT * FROM CUSTOMER WHERE RATING >=ANY

(SELECT RATING FROM CUSTOMER WHERE SNUM

IN (SELECT SNUM FROM SALESMAN WHERE SNAME=’NIRAJ’))

CNUM CNAME CITY RATING SNUM

——— ——————– ———- ——— ———

2002 GITA ROME 200 1003

2003 LAXIT SURAT 200 1002

2004 GOVINDA BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

————————————————————————-

66.

SQL>SELECT MAX(AMOUNT),MIN(AMOUNT),ODATE FROM ORDER1 GROUP BY ODATE;

MAX(AMOUNT) MIN(AMOUNT) ODATE

———– ———– ———

5160.45 18.69 03-OCT-99

1713.12 1098.25 04-OCT-99

4723 75.75 05-OCT-99

9898.87 9898.87 06-OCT-99

————————————————————————-

67.

SQL> SELECT CNAME,CITY,’HIGH RATING’ FROM CUSTOMER WHERE RATING>=200

2 UNION

3 SELECT CNAME,CITY,’LOW RATING’ FROM CUSTOMER WHERE RATING <200; CNAME CITY ‘HIGHRATING ——————– ———- ———– CHAMPAK SURAT HIGH RATING CHANDU LONDON LOW RATING GITA ROME HIGH RATING GOVINDA BOMBAY HIGH RATING HARDIK LONDON LOW RATING LAXIT SURAT HIGH RATING PRATIK ROME LOW RATING 7 rows selected. ————————————————————————— 68. SQL> INSERT INTO SALESMAN (SNUM,SNAME,COMMISSION)

2 VALUES(100,’RAKESH’,14);

1 row created.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

8 rows selected.

—————————————————————————-

69.

SQL> INSERT INTO CUSTOMER(CNUM,CNAME,CITY)

VALUES(2005,’PRATIK’,’LONDON’);

1 row created.

—————————————————————————–

70.

SQL> create table london_staff as select * from salesman ;

Table created.

SQL> select * from london_staff;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

8 rows selected.

71.

SQL> insert into LONDON_STAFF select * from salesman WHERE city=’LONDON’;

2 rows created.

SQL> select * from LONDON_STAFF;

SNUM SNAME CITY COMMISSION

—- ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

1001 PIYUSH LONDON 12

1003 MITI LONDON 11

10 rows selected.

—————————————————————————-

72.

SQL> CREATE TABLE DAY_TOTALS1 AS SELECT ODATE,SUM(AMOUNT) TOTAL_SALES

FROM ORDER1 GROUP BY ODATE

Table created.

SQL> SELECT * FROM DAY_TOTALS1;

ODATE TOTAL_SALES

——— ———–

03-OCT-99 7846.43

04-OCT-99 2811.37

05-OCT-99 6108.7

06-OCT-99 9898.87

—————————————————————————-

73.

SQL> CREATE TABLE MULTICAST AS SELECT * FROM SALESMAN;

Table created.

SQL> SELECT * FROM MULTICAST;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

SQL> DELETE FROM SALESMAN;

8 rows deleted.

————————————————————————–

74.

SQL> INSERT INTO SALESMAN SELECT * FROM MULTICAST;

8 rows crated.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

8 rows selected.

——————————————————————————

75.

SQL> DELETE FROM OREDER1 WHERE CNUM IN(SELECT CNUM FROM CUSTOMER

WHERE CNAME=’CHANDU’);

0 rows deleted.

——————————————————————————

76.

SQL> UPDATE CUSTOMER SET RATING= 400 WHERE SNUM IN

(SELECT SNUM FROM SALESMAN WHERE SNAME=’PIYUSH’);

2 rows updated.

SQL> SELECT * FROM CUSTOMER;

CNUM CNAME CITY RATING SNUM

——— ——————– ———- ——— ———

2001 HARDIK LONDON 400 1001

2002 GITA ROME 200 1003

2003 LAXIT SURAT 200 1002

2004 GOVINDA BOMBAY 300 1002

2005 CHANDU LONDON 400 1001

2006 CHAMPAK SURAT 300 1007

2007 PRATIK ROME 100 1004

7 rows selected.

——————————————————————————

77.

SQL> UPDATE CUSTOMER SET RATING=RATING+100 WHERE CITY=’ROME’;

2 rows updated.

SQL> SELECT * FROM CUSTOMER;

CNUM CNAME CITY RATING SNUM

——— ——————– ———- ——— ———

2001 HARDIK LONDON 400 1001

2002 GITA ROME 300 1003

2003 LAXIT SURAT 200 1002

2004 GOVINDA BOMBAY 300 1002

2005 CHANDU LONDON 400 1001

2006 CHAMPAK SURAT 300 1007

2007 PRATIK ROME 200 1004

7 rows selected.

————————————————————————–

78.

SQL> UPDATE SALESMAN SET SNAME= ‘GOPAL’,CITY= ‘BOMBAY’,

COMMISSION =10 WHERE SNAME=’MITI’

1 row updated.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 24

1002 NIRAJ SURAT 13

1003 GOPAL BOMBAY 10

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

8 rows selected.

————————————————————————————–

79.

SQL> UPDATE SALESMAN SET COMMISSION =COMMISSION * 2 WHERE CITY=’LONDON’;

2 rows updated.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 24

1002 NIRAJ SURAT 13

1003 MITI LONDON 22

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 9

100 RAKESH 14

8 rows selected.

—————————————————————————-

80.

SQL> UPDATE CUSTOMER SET RATING=NULL WHERE CITY =’LONDON’;

2 rows updated.

SQL> SELECT * FROM CUSTOMER;

CNUM CNAME CITY RATING SNUM

——— ——————– ———- ——— ———

2001 HARDIK LONDON 1001

2002 GITA ROME 300 1003

2003 LAXIT SURAT 200 1002

2004 GOVINDA BOMBAY 300 1002

2005 CHANDU LONDON 1001

2006 CHAMPAK SURAT 300 1007

2007 PRATIK ROME 200 1004

7 rows selected.

81.

SQL> CREATE TABLE SALES_MAN AS SELECT * FROM SALESMAN S WHERE SNUM IN

(SELECT SNUM FROM ORDER1 WHERE 5000 < (SELECT SUM(AMOUNT) FROM ORDER1 * WHERE ORDER1.SNUM=S.SNUM)) Table created. SQL> SELECT * FROM SALES_MAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

——————————————————————————–

82.

SQL> CREATE TABLE SMCITY AS SELECT S.* FROM SALESMAN S,CUSTOMER C

WHERE S.SNUM=C.SNUM AND S.CITY=C.CITY

Table created.

SQL> SELECT * FROM SMCITY;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

1001 PIYUSH LONDON 12

——————————————————————————–

83.

SQL> CREATE TABLE BOUNSE AS SELECT O.* FROM ORDER1 O

WHERE AMOUNT IN (SELECT MAX(OO.AMOUNT) FROM ORDER1 OO

WHERE O.SNUM= OO.SNUM );

Table created.

SQL> SELECT * FROM BOUNSE;

ONUM AMOUNT ODATE CNUM SNUM

——— ——— ——— ——— ———

3003 1900.1 03-OCT-99 2007 1004

3004 5160.45 03-OCT-99 2003 1002

3005 1098.25 04-OCT-99 2008 1007

3006 1713.12 04-OCT-99 2002 1003

3010 9898.87 06-OCT-99 2006 1001

——————————————————————————–

84.

SQL>CREATE TABLE MULTICAST4 AS SELECT S.* FROM SALESMAN S,CUSTOMER C

WHERE S.SNUM =C.SNUM AND C.CNUM IN (SELECT A.CNUM FROM

CUSTOMER A,CUSTOMER B WHERE A.SNUM =B.SNUM AND A.CNUM >B.CNUM)

Table created.

SQL> SELECT * FROM MULTICAST4;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1002 NIRAJ SURAT 13

1001 PIYUSH LONDON 12

——————————————————————————–

85.

SQL> DELETE FROM CUSTOMER WHERE SNUM IN (SELECT SNUM FROM

SALESMAN WHERE CITY=’NEW DELHI’);

0 rows deleted.

———————————————————————————–

86.

SQL> DELETE FROM SALESMAN WHERE SNUM IN (SELECT SNUM FROM CUSTOMER

WHERE RATING =100);

0 rows deleted.

—————————————————————————-

87.

SQL>DELETE FROM SALESMAN WHERE SNUM IN (SELECT A.SNUM

FROM ORDER1 A WHERE A.AMOUNT =ANY (SELECT MIN(B.AMOUNT)

FROM ORDER1 B WHERE A.ONUM=B.ONUM GROUP BY B.ODATE));

5 rows deleted.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

—- ——————– ———- ———-

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

——————————————————————————

88.

SQL>UPDATE SALESMAN SET COMMISSION=COMMISSION-2 WHERE

SNUM IN (SELECT SNUM FROM ORDER1 O WHERE AMOUNT

IN (SELECT MIN(AMOUNT) FROM ORDER1 OO WHERE O.ODATE=OO.ODATE))

3 rows updated.

SQL> SELECT * FROM SALESMAN;

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 10

1002 NIRAJ SURAT 11

1003 MITI LONDON 11

1004 RAJESH BARODA 15

1005 ANAND NEW DELHI 10

1006 RAM PATAN 10

1007 LAXMAN BOMBAY 7

100 RAKESH 14

8 rows selected

—————————————————————————

89.

SQL> DELETE FROM CUSTOMER WHERE CNUM IN (SELECT O.CNUM

FROM ORDER1 O WHERE CNUM=O.CNUM);

6 rows deleted

SQL> SELECT * FROM CUSTOMER;

CNUM CNAME CITY RATING SNUM

—- ——————– ———- ——— ———

2005 CHANDU LONDON 1001

——————————————————————————–

90.

SQL> SELECT SUM(AMOUNT),ODATE FROM ORDER1 GROUP BY ODATE;

SUM(AMOUNT) ODATE

——– ———

7846.43 03-OCT-99

2811.37 04-OCT-99

6108.7 05-OCT-99

9898.87 06-OCT-99

91.

SQL> ALTER TABLE ORDER1 ADD(ITEM_NAME VARCHAR2(20));

Table altered.

SQL> DESC ORDER1;

Name Null? Type

——————————- ——– —-

ONUM NUMBER(4)

AMOUNT NUMBER(9,2)

ODATE DATE

CNUM NUMBER(4)

SNUM NUMBER(4)

ITEM_NAME VARCHAR2(20)

——————————————————————————

92.

SQL> CREATE TABLE TORDER1 AS SELECT * FROM ORDER1;

Table created.

SQL> SELECT * FROM TORDER1;

ONUM AMOUNT ODATE CNUM SNUM ITEM_NAME

——— ——— ——— ——— ——— ——————–

3001 18.69 03-OCT-99 2008 1007

3002 767.19 03-OCT-99 2001 1001

3003 1900.1 03-OCT-99 2007 1004

3004 5160.45 03-OCT-99 2003 1002

3005 1098.25 04-OCT-99 2008 1007

3006 1713.12 04-OCT-99 2002 1003

3007 75.75 05-OCT-99 2004 1002

3008 4723 05-OCT-99 2006 1001

3009 1309.95 05-OCT-99 2004 1002

3010 9898.87 06-OCT-99 2006 1001

10 rows selected.

SQL> DELETE FROM ORDER1;

10 rows deleted.

—————————————————————————-

93.

SQL> CREATE TABLE ORDER1_TABLE AS SELECT ONUM,ODATE,SNUM,CNUM FROM ORDER1;

Table created.

SQL> SELECT * FROM ORDER1_TABLE;

ONUM ODATE SNUM CNUM

——— ——— ——— ———

3002 03-OCT-99 1001 2001

3003 03-OCT-99 1004 2007

3004 03-OCT-99 1002 2003

3005 04-OCT-99 1007 2008

3006 04-OCT-99 1003 2002

3007 05-OCT-99 1002 2004

3008 05-OCT-99 1001 2006

3009 05-OCT-99 1002 2004

3010 06-OCT-99 1001 2006

3001 03-OCT-99 1001 2008

10 rows selected.

——————————————————————————–

94.

SQL> CREATE TABLE SS_SALESMAN

2 (

3 SNUM NUMBER(4),

4 SNAME VARCHAR2(20),

5 CITY VARCHAR2(10),

6 COMMISSION NUMBER(9,2) DEFAULT.10);

Table created.

SQL> desc SS_SALESMAN;

Name Null? Type

——————————- ——– —-

SNUM NUMBER(4)

SNAME VARCHAR2(20)

CITY VARCHAR2(10)

COMMISSION NUMBER(9,2)

—————————————————————————————-

95.

SQL> 1 CREATE TABLE S_SALESMAN

2 (

3 SNUM NUMBER(4) PRIMARY KEY,

4 SNAME VARCHAR2(20)NOT NULL,

5 CITY VARCHAR2(10),

6 COMMISSIOM NUMBER(4) NOT NULL);

Table created.

SQL> DESC S_SALESMAN;

Name Null? Type

——————————- ——– —-

SNUM NOT NULL NUMBER(4)

SNAME NOT NULL VARCHAR2(20)

CITY VARCHAR2(10)

COMMISSIOM NOT NULL NUMBER(4)

———————–

SQL> 1 CREATE TABLE S_CUSTOMER

2 (

3 CNUM NUMBER(4) PRIMARY KEY,

4 CNAME VARCHAR2(20) NOT NULL,

5 CITY VARCHAR2(10) NOT NULL,

6 RATING NUMBER(4) NOT NULL,

7 SNUM NUMBER(4) FORIEGN KEY );

Table created.

SQL> DESC S_CUSTOMER;

Name Null? Type

——————————- ——– —-

CNUM NOT NULL NUMBER(4)

CNAME NOT NULL VARCHAR2(20)

CITY NOT NULL VARCHAR2(10)

RATING NOT NULL NUMBER(4)

SNUM NUMBER(4)

————————————-

SQL> CREATE TABLE S_ORDER1

2 (

3 ONUM NUMBER(4) PRIMARY KEY,

4 AMOUNT NUMBER(7,2),

5 ODATE DATE NOT NULL,

6 CNUM NUMBER(4) NOT NULL,

7 SNUM NUMBER(4) NOT NULL,

8 FOREIGN KEY(CNUM) REFERENCES S_CUSTOMER,

9 FOREIGN KEY(SNUM) REFERENCES S_SALESMAN);

Table created.

SQL> DESC S_ORDER1;

Name Null? Type

——————————- ——– —-

ONUM NOT NULL NUMBER(4)

AMOUNT NUMBER(7,2)

ODATE NOT NULL DATE

CNUM NOT NULL NUMBER(4)

SNUM NOT NULL NUMBER(4)

—————————————————————————-

96.

SQL> CREATE VIEW BIG AS SELECT * FROM ORDER1 WHERE AMOUNT > 4000;

View created.

SQL> SELECT * FROM BIG;

ONUM AMOUNT ODATE CNUM SNUM

—– ——— ——— ——— ———

3004 5160.45 03-OCT-99 2003 1002

3008 4723 05-OCT-99 2006 1001

3010 9898.87 06-OCT-99 2006 1001

——————————————————————————–

97.

SQL> CREATE VIEW VIEW97 AS SELECT COUNT(DISTINCT(A.RATING))COUNT

FROM CUSTOMER A,CUSTOMER B WHERE A.RATING = B.RATING;

View created.

SQL> SELECT * FROM VIEW97;

COUNT

———

3

—————————————————————————

98.

SQL> CREATE VIEW VIEW99 AS SELECT * FROM CUSTOMER WHERE RATING IN

(SELECT MAX(RATING) FROM CUSTOMER);

View created.

SQL> SELECT * FROM VIEW99;

CNUM CNAME CITY RATING SNUM

—– ——————– ———- ——— ———

2004 GOVINDA BOMBAY 300 1002

2006 CHAMPAK SURAT 300 1007

——————————————————————————–

99.

SQL>CREATE VIEW VIEW99 AS SELECT CITY,COUNT(SNUM)SNUM FROM

SALESMAN GROUP BY CITY;

View created.

SQL> SELECT * FROM VIEW99;

CITY SNUM

———- ———

BARODA 1

BOMBAY 1

LONDON 2

NEW DELHI 1

PATAN 1

SURAT 1

7 rows selected.

———————————————————————————-

100.

SQL> CREATE VIEW VIEW100 AS SELECT CITY,

COUNT(SNUM)SNUM FROM SALESMAN GROUP BY CITY;

View created.

SQL> SELECT * FROM VIEW100;

CITY SNUM

———- ———

BARODA 1

BOMBAY 1

LONDON 2

NEW DELHI 1

PATAN 1

SURAT 1

7 rows selected.

101.

SQL> CREATE VIEW VIEW101 AS SELECT S.SNUM,S.SNAME ,COUNT(ONUM)”TOTAL COUNT”,

AVG(AMOUNT)”AVERAGE” FROM SALESMAN S ,ORDER1 O

WHERE S.SNUM =O.SNUM GROUP BY S.SNUM,S.SNAME

View created.

SQL> SELECT * FROM VIEW101;

SNUM SNAME TOTAL COUNT AVERAGE

——— ——————– ———– ———

1001 PIYUSH 3 5129.6867

1002 NIRAJ 3 2182.05

1003 MITI 1 1713.12

1004 RAJESH 1 1900.1

1007 LAXMAN 2 558.47

——————————————————————————

102.

SQL> CREATE VIEW VIEW102 AS SELECT * FROM SALESMAN WHERE

SNUM IN (SELECT A.SNUM FROM CUSTOMER A ,CUSTOMER B

WHERE A.SNUM =B.SNUM AND A.CNUM <> B.CNUM );

View created.

SQL> select * from VIEW102;

SNUM SNAME CITY COMMISSION

—– ——————– ———- ———-

1001 PIYUSH LONDON 12

1002 NIRAJ SURAT 13

———————————————————————————

103.

SQL> CREATE VIEW VIEW103 AS SELECT ODATE,COUNT(ONUM) “NO OF ORDER”,AVG(AMOUNT) “AVG AMOU

FROM ORDER1 GROUP BY ODATE;

View created.

SQL> SELECT * FROM VIEW103;

ODATE NO OF ORDER AVG AMOUNT ORDER

——— ———– —————–

03-OCT-99 4 1961.6075

04-OCT-99 2 1405.685

05-OCT-99 3 2036.2333

06-OCT-99 1 9898.87

————————————————————————————-

104.

SQL> CREATE VIEW SCO AS SELECT O.ONUM,S.SNUM,C.CNUM,S.SNAME,C.CNAME FROM

SALESMAN S,CUSTOMER C,ORDER1 O WHERE S.SNUM=C.SNUM AND S.SNUM=O.SNUM

AND C.CNUM =O.CNUM;

View created.

SQL> SELECT * FROM SCO;

ONUM SNUM CNUM SNAME CNAME

— ——— ——— ——————– ——————–

3002 1001 2001 PIYUSH HARDIK

3003 1004 2007 RAJESH PRATIK

3004 1002 2003 NIRAJ LAXIT

3006 1003 2002 MITI GITA

3007 1002 2004 NIRAJ GOVINDA

3009 1002 2004 NIRAJ GOVINDA

6 rows selected.

————————————————————————————

105.

SQL> CREATE VIEW SH AS SELECT O.* ,SNAME,COMMISSION FROM

SALESMAN S,ORDER1 O WHERE S.SNUM = O.SNUM

AND S.SNAME =’RAJESH’;

View created.

SQL> SELECT * FROM SH;

ONUM AMOUNT ODATE CNUM SNUM SNAME COMMISSION

—– ——— ——— ——— ——— ——- ———-

3003 1900.1 03-OCT-99 2007 1004 RAJESH 15

———————————————————————————————-

106.

SQL> CREATE VIEW MAXSALES AS SELECT S.SNUM,S.SNAME,O.ODATE FROM SALESMAN S,ORDER1 O

WHERE S.SNUM=O.SNUM AND (O.AMOUNT,O.ODATE) = (SELECT MAX(AMOUNT), ODATE FROM ORDER1

GROUP BY ODATE)

View created.

—————————————————————————————–

107.

SQL> 1 CREATE VIEW MAXSALES1 AS SELECT S.SNUM,S.SNAME FROM SALESMAN S , MAXSALES M WHERE

S.SNUM=M.SNUM AND M.SNUM IN (SELECT SNUM FROM ORDER1 WHERE (SNUM,AMOUNT) IN

(SELECT SNUM,MAX(AMOUNT) FROM ORDER1 GROUP BY SNUM HAVING COUNT(SNUM)>1))

View created.

——————————————————————————————————-

108.

SQL> CREATE VIEW SAMECITY AS SELECT C.CNUM,C.CNAME,C.CITY FROM CUSTOMER C,SALESMAN S WHERE

C.SNUM=S.SNUM AND C.CITY=S.CITY;

View created.

SQL> SELECT * FROM SAMECITY;

CNUM CNAME CITY

——— ——————– ———-

2001 HARDIK LONDON

2003 LAXIT SURAT

2005 CHANDU LONDON

—————————————————————————————————-

109.

SQL> CREATE TABLE COMMISSION_SALES

2 (

3 SNUM NUMBER(4),

4 COMMISSION NUMBER(4) CHECK ( COMMISSION >=10 OR COMMISSION< =20 ) Table created. SQL> DESC COMMISSION_SALES;

Name Null? Type

——————————- ——– —-

SNUM NUMBER(4)

COMMISSION NUMBER(4)

SQL> CREATE VIEW COMMISSION_SALES1 AS SELECT * FROM COMMISSION_SALES

View created.

SQL> SELECT * FROM COMMISSION_SALES1;

no rows selected

—————————————————————————————————

110.

—75 % SURE—

SQL> CREATE TABLE CUR

2 (

3 ONUM NUMBER(4),

4 AMOUNT NUMBER(7,2),

5 ODATE DATE DEFULT’03-MAR-99′,

6 CNUM NUMBER(4) ,

7* SNUM NUMBER(4) );

TABLE CREATED

—————————————————————————————————

111.

SQL>SELECT * FROM SALESMAN WHERE CITY=’LONDON’ AND SNUM

IN (SELECT SNUM FROM CUSTOMER WHERE CITY =’LONDON’)

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1001 PIYUSH LONDON 12

—————————————————————————————————

112.

SQL> SELECT * FROM SALESMAN WHERE CITY=’LONDON’ AND SNUM

2 NOT IN (SELECT SNUM FROM CUSTOMER WHERE CITY =’LONDON’);

SNUM SNAME CITY COMMISSION

——— ——————– ———- ———-

1003 MITI LONDON 11

===================================================================================================================

===================================================================================================================

===================================================================================================================

Advertisements