Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Homework NoEffort
For a table created using the following commands. State the SQL SELECT command to find the 1st and 2nd highest salary earned by staff working in DEL or MUM
	CREATE TABLE Employees (
		ID int NOT NULL AUTO_INCREMENT,
		Name varchar(100),
		Sal decimal (10,2),
		City char (3),
		PRIMARY KEY (ID)
	);
 
	INSERT INTO Employees (Name, Sal, City) VALUES ('Ramesh',20000, 'BLR');
	INSERT INTO Employees (Name, Sal, City) VALUES ('Sunil',24000, 'DEL');
	INSERT INTO Employees (Name, Sal, City) VALUES ('Sreeja',21000, 'MUM');
	INSERT INTO Employees (Name, Sal, City) VALUES ('Pavan',23000, 'DEL');
	INSERT INTO Employees (Name, Sal, City) VALUES ('Maya',24000, 'MUM');
Posted 18-Jan-14 6:16am
Edited 18-Jan-14 6:18am
v3
Comments
Member 10532589 at 18-Jan-14 11:21am
   
please reply soon
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.
 
Try it yourself, you may find it is not as difficult as you think!
 
I'll give you a hint though: SELECT has an optional TOP specifier, and can ORDER BY columns...
  Permalink  
Comments
Jörgen Andersson at 18-Jan-14 11:47am
   
I wouldn't count on the TOP clause existing on a MySQL or MariaDB server ;-)
OriginalGriff at 18-Jan-14 11:59am
   
It's not my homework! :laugh:
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I suggest to use ranking functions[^].
 
For example:
SELECT RowNo, [Name], Sal
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY City ORDER BY Sal DESC) AS RowNo, [Name], Sal
    FROM Employee
    WHERE City IN ('DEL','MUM') 
) AS T
WHERE RowNo IN (1,2)
 
NOTE: Depending on your needs, you should use proper ranking function.
  Permalink  
Comments
Christian Graus at 18-Jan-14 17:56pm
   
I prefer not to give copy and paste answers to people who have obviously posted their homework
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Ignore the other answers you got. One is correct, but over complicated and your teacher will know you cheated, and the other is just stupid and wrong. Read OriginalGriff's answer. It's really easy to do, with basic SQL.
 
Here[^] is my article on select statements. Work through it. Learn SQL and pass your course. Don't try to cheat and end up as clueless as you started. You won't find a job that way.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can get the first and the second numbers in a table quite easily.
Even without TOP, its not very difficult to do.

Hint: Based on OriginalGriff's homework pointer, pick the top number and then pick the top number.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Below is your TSQL:
 
SELECT
    TOP 2 *
FROM
    Employees
WHERE
    City IN ('DEL','MUM')
  Permalink  
Comments
Christian Graus at 18-Jan-14 17:56pm
   
Amusingly, this is plain wrong :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 DamithSL 385
1 BillWoodruff 170
2 Zoltán Zörgő 165
3 OriginalGriff 164
4 Maciej Los 123
0 OriginalGriff 7,854
1 DamithSL 5,949
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,084
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 18 Jan 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100