1

Roll No.:- 1129

1. CREATE Table SALESPEOPLE with field SNO, SNAME, CITY, COMM

Create table salespeople

(

SNO number PRIMARY KEY,

SNAME varchar2(21),

CITY varchar2(21),

COMM number

);

Table created.

Name Null? Type

SNO NUMBER

SNAME VARCHAR2(21)

CITY VARCHAR2(21)

COMM NUMBER

2. Insert 10 Records in SALESPEOPLE

Insert into salespeople values(1,'VIKAS','PUNE',500);

Insert into salespeople values(2,'KOMAL','MUMBAI',100);

.

.

.

.

Insert into salespeople values(9,'KAMAL','SOLAPUR',1500);

Insert into salespeople values(10,'SANDHYA','NAGPUR',NULL);

SNO SNAME CITY COMM

1 VIKAS PUNE 500

2 KOMAL MUMBAI 100

3 AVI THANE

4 NITIN NASHIK

5 VINAYAK NANDED 1100

6 AMOL PUNE 100

7 RAJ MUMBAI 1000

8 BALAJI PUNE

9 KAMAL SOLAPUR 1500

10 SANDHYA NAGPUR

10 rows selected.

3. Display NAME & CITY of salesman where city is PUNE and COMM is 100

SELECT SNAME,CITY

from SALESPEOPLE

WHERE CITY='PUNE' and COMM=100;

O/P:-

SNAME CITY

AMOL PUNE

4. Display all SALES PERSONS names starting with character G and end with ASELECT SNAME

FROM SALESPEOPLE

WHERE sname like 'V%S';

O/P:-

SNAME

VIKAS

5. Assume each salesperson has a 12% COMM and display the calculated amount in descending order.select ename, sal *(12/100) as inc from emp order by incENAME INC

RAHUL 96

RAJ 114

SHOMEN 132

WARD 150

AJINKYA 150

MILLER 156

AADESH 180

KAMAL 192

CLARK 294

VINIT 342

BALAJI 357

WARD 360

SWASTIK 360

KING 600

14 rows selected.

6. Write a query which will include null values also

select count(NVL(sal,-1)) from emp;COUNT(NVL(SAL,-1))

14

7. Write a query which will Calculate Second highest salary

select max(sal) from emp where sal