LIVE Course for free

Rated by 1 million+ students
Get app now
0 votes
112k views
in Computer by (76.1k points)

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;

1 Answer

+1 vote
by (74.5k points)
selected by
 
Best answer

(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

Welcome to Sarthaks eConnect: A unique platform where students can interact with teachers/experts/students to get solutions to their queries. Students (upto class 10+2) preparing for All Government Exams, CBSE Board Exam, ICSE Board Exam, State Board Exam, JEE (Mains+Advance) and NEET can ask questions from any subject and get quick answers by subject teachers/ experts/mentors/students.

Categories

...