Question

Consider the table given below :

Write Answer to (i). Write SQL queries for (ii) to (viii) and output for SQL queries (ix) and (x).

(Table : Salesperson)

SID	Name	Phone	DOB	Salary	Area
S101	Amit Kumar	98701789654	1967-01-23	67000.00	North
S102	Deepika Sharma	99104567834	1992-09-23	32000.00	South
S103	Vinay Srivastav	98101546789	1991-06-27	35000.00	North
S104	Kumar Mehta	88675345789	1967-10-16	40000.00	East
S105	Rashmi Kumar	98101567434	1972-09-20	50000.00	South

Note: Columns SID and DOB contain Sales Person Id and Data of Birth respectively.

(i) Write the data types of SID and DOB columns.

(ii) Display names of Salespersons and their Salaries who have salaries in the range 30000.00 to 40000.00.

(iii) To list Names, Phone numbers and DOB (Date of Birth) of Salespersons who were born before 1st November 1992.

(iv) To display Names and salaries of salespersons in descending order of salary.

(v) To displays areas in which Salespersons are working. Duplicate Areas should not be displayed. 

(vi) To display SID, Names along with Salaries increased by 500. (Increase of t 500 is only to be displayed and not to be updated in the table).

(vii) To display Area along with number of Salespersons working in that area.

(viii) To display Names of Salespersons who have the word 'Kumar' anywhere in their names.

(ix) SELECT Name, LENGTH (Name) FROM Salesperson;

(x) SELECT Area, COUNT (*)

FROM Salesperson

GROUP BY Area

HAVING COUNT (*) > 1;

Answer 1

(i) SID=varchar().

DOB = date.

(ii) SELECT Name FROM Salesperson WHERE Salary BETWEEN 30000.00 AND 40000.00;

(iii) SELECT Name, Phone, DOB FROM Salesperson WHERE DOB< '1 - 11 - 1992' ;

(iv) SELECT Name, Salary FROM Salesperson ORDER BY Salary DESC;

(v) SELECT DISTINCT(Area) FROM Salesperson;

(vi) SELECT SlD,Name,Salary+500 FROM Salesperson;

(vii) SELECT Area, Phone FROM Salesperson;

(viii) SELECT Name FROM Salesperson WHERE Name LIKE "%Kumar%";

(ix)   Amit Kumar                   10

        Deepika Sharma           14

        Vinay Shrivastav           16

        Kumar Mehta                11

       Rashmi Kumar               12 

(x) North      2

    South      2