Hi all,
recently I tried to pass elance sql code test and managed to reply to only 8 out of 10 questions. I sent expert rating a feedback after the first pass as I believe limits should be allowed (however, maybe they are not in ANSI, I don’t remember). Anyway, here are the questions and my answers:
1. Consider a table named “salary” having the following columns:
“id” (type: INT)
“salary” (type: INT)
“incentive” (type: INT)
“tax” (type: INT)
Write a standard SQL query which will update the tax column with the sum of 10% of salary and 2% of incentive, for those salaries which are more than 15000.
UPDATE salary SET tax = 0.1*salary+0.02*incentive WHERE salary > 15000
2. Consider a table named “employee” having the following columns:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the empnames whose values start with the string ‘john’ followed by any characters.
SELECT empname FROM employee WHERE empname LIKE 'john%'3. Consider a table named “employee” having the following columns:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the number of rows where the salary is not null. The returned value should be represented using the column name “validsalarycount”.
SELECT count(salary) AS validsalarycount FROM employee WHERE salary IS NOT NULL
4. Consider a table named “store” having the following columns:
“storename” (type: TEXT)
“sales” (type: INT)
“Date” (type: DATE)
Write a standard SQL query which retrieves the storenames, whose sales lie between 100 and 2000 (not inclusive). The storenames should not be repeated.
SELECT DISTINCT storename FROM store WHERE sales > 100 AND sales < 2000
5. Consider a table named “staff” having the following column structure:
“empid” (type: INT)
“empname” (type: TEXT)
“salary” (type: INT)
Write a standard SQL query which retrieves the sum of 75 percent of the salaries from the staff table (only salaries above 5000 are to be considered). The returned value should be represented using the column name ‘total’.
SELECT sum(0.75*salary) AS total FROM staff WHERE salary > 5000
6. Consider the following tables:
department
———-
deptid (type: INT)
deptname (type: TEXT)
hours (type: INT)
active (type: BIT)
employee
——–
empid (type: INT)
empname (type: TEXT)
deptid (type: INT)
designation (type: TEXT)
salary (type: INT)
Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.
This is one of the questions I did not cope with. Any solution would be appeciated.
7. Consider a table called carrecords with the following structure:
name (type: TEXT)
price (type: INT)
color (type: TEXT)
vehicletype (type: TEXT) eg. SEDAN/SUV
A customer wants to see the details (name, price, color, vehicletype) of the vehicles that suit his preferences. This is what he says:
“If its a black sedan, I’m ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won’t go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.”
Write a query that returns the desired information in ascending order of price.
This is one of the questions I did not cope with. Any solution would be appeciated.
!WRONG SQL!:
SELECT name, price, color, vehicletype FROM carrecords WHERE (vehicletype = 'SEDAN' AND color = 'black' AND price <= 10000) OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 ) OR (vehicletype = 'SEDAN' /* here I tried != "SUV" as well - no luck*/ AND color NOT IN('red','white','black') AND price <= 7000) OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000) OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000) ORDER BY price ASC
8. Consider a database with a table called “accounts”, having two fields:
“entrydate” (type: DATE)
“accountno” (type: INT)
Write a SQL query which returns the accountno of the most recent entrydate. The returned value should be represented using the column name, “accountno”.
Correct solution:
SELECT accountno AS accountno FROM accounts WHERE entrydate = (SELECT max(entrydate) FROM accounts)
!WRONG SQL!:
SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 19. Consider a table called “students”, having the following column fields:
“id” (type: INT)
“name” (type: TEXT)
“marks” (type: INT)
Write a SQL query which will calculate the average of the marks of the students passing. The passing criteria is that the marks should be at least 40. The average marks are to be returned using the column name ‘marksaverage’.
SELECT avg(marks) AS marksaverage FROM students WHERE id IN (SELECT id FROM students WHERE marks >= 40)
10. Consider a table called “department”, having the following columns:
“id” (type: INT)
“deptname” (type: TEXT)
“rank” (type: INT)
Write a SQL query which will return the deptnames of the departments whose rank lies between 2 and 5 (inclusive). The results should be returned in increasing order of rank (rank 3 being higher than rank 6).
SELECT deptname FROM department WHERE rank >= 2 AND rank <= 5 ORDER BY rank ASC
Thanks
The answer of Question 6 is
select e.empname , d.deptname, d.`deptid`from employee e inner join department d where e.`deptid` = d.`deptid` and e.`deptid` in (select deptid from employee group by deptid having (count(`deptid`)) >= 4) order by e.empname
But this query is not optimized because i have used sub-query.
By the way thanks for publishing these questions. I have read your php and sql question.
Thanks Alan, I just tried and your solution resulted in “Wrong Answer”, I think I tried something similar, but no luck
Thanks Alan,
Such a nice posts.
Thanks a lot..
Hi Alan,
I have found solution.
with rs as (
select d.deptname, e.empname, count(*) over(partition by e.deptid) as cnt
from employee as e inner join department as d on e.deptid = d.deptid
)
Hi Alan,
I have found solution problem no. 6
with rs as (
select d.deptname, e.empname, count(*) over(partition by e.deptid) as cnt
from employee as e inner join department as d on e.deptid = d.deptid
)
Hi Alan,
Sorry. The right solution for the problem no. 6 is
with rs as (
select d.deptname, e.empname, count(*) over(partition by e.deptid) as cnt
from employee as e inner join department as d on e.deptid = d.deptid
)
select deptname, empname
from rs
where cnt >= 4
order by cast(empname as varchar(50))
This post is very useful for me, much appreciated! 🙂
Hi,
Thanks for great effort.
Can you solve the 6 and 7 number question?
@Kanak: thanks for the answer, but “partition by” looks everything but standard ANSI SQL to me… Actually, I wrote a standard ANSI SQL answer, and it kept telling me “wrong”. I’m starting to believe they use Oracle to test it, which is NOT completely standard.
SELECT e1.empname, d.deptname from employee AS e1
FULL JOIN department AS d on e1.deptid = d.deptid
WHERE e1.deptid IN(
SELECT deptid FROM(
SELECT e2.deptid, COUNT(e2.empid)
FROM employee AS e2
GROUP BY e2.deptid
HAVING COUNT(e2.empid) >= 4
)
)
ORDER BY empname;
check this, it will work
Select * from carrecords
where (vehicletype=’SEDAN’ AND price <= 7000)
OR (vehicletype='SEDAN' and color in ('red','white') and price <= 8000)
OR (vehicletype='SEDAN' AND color like 'black' AND price <= 10000)
OR (vehicletype='SUV' and price <=14000)
OR (vehicletype='SUV' and color like 'black' and price <=15000)
order by price
this is the best…
SELECT name, price, color, vehicletype FROM carrecords WHERE
(vehicletype = ‘SEDAN’ AND color = ‘black’ AND price <= 10000)
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
OR (price <= 7000)
ORDER BY price ASC
The tests with the departments with headcount of 4 or more and carrecords must be wrong.
I’ve tried a lot of versions that I’m sure are correct, including the ones presented on this page and they didn’t work.
All these answers on comment sections failed!
For the carrecords all of the colors and the vehicletypes must be submitted in uppercase in order for the database to accept them.
Couldn’t figure out why the departments didn’t go through. Probably something equally as minor.
The 100% correct and tested answer for question 7:
SELECT name,
price,
color,
vehicletype
FROM @carrecords
WHERE (vehicletype LIKE ‘SEDAN’ AND
((color LIKE ‘black’ AND price <= 10000) OR
((color LIKE 'red' OR color LIKE 'white') AND price <= 8000) OR
(price <= 7000))) OR
(vehicletype LIKE 'SUV' AND
((color LIKE 'black' AND price <= 15000) OR
price <= 14000))
ORDER BY price
Yes, but you dont need LIKE . Put = instead of LIKE
Of course, the table name is carrecords without @.
ha ha 😀
SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 1
Correct:
SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 0,1
Solution for Question 6 is :
SELECT employee.empname, department.deptname
FROM employee
INNER JOIN department ON employee.deptid = department.deptid
WHERE employee.deptid IN (
SELECT employee.deptid
FROM employee
GROUP BY employee.deptid
HAVING COUNT(employee.deptid) >= 4
)
ORDER BY employee.empname ASC;
SELECT name,
price,
color,
vehicletype
FROM carrecords
WHERE (vehicletype LIKE ‘SEDAN’ AND
((color LIKE ‘black’ AND price <= 10000) OR
((color LIKE 'red' OR color LIKE 'white') AND price <= 8000) OR
(price <= 7000))) OR
(vehicletype LIKE 'SUV' AND
((color LIKE 'black' AND price <= 15000) OR
price <= 14000))
ORDER BY price
Thank you for submitting this post. Excellent code
I tried following solution for Question No 7. It is
SELECT name, price, color, vehicletype FROM carrecords WHERE
((Convert(varchar, vehicletype) = ‘SEDAN’ AND Convert(varchar, color) = ‘black’ AND price <= 10000) OR
(Convert(varchar, vehicletype) = 'SEDAN' AND Convert(varchar, color) IN('red','white') AND price <= 8000 ) OR
(Convert(varchar, vehicletype) = 'SEDAN' AND Convert(varchar, color) NOT IN('red','white','black') AND price <= 7000)) OR
((Convert(varchar, vehicletype) = 'SUV' AND Convert(varchar, color) = 'black' AND price <= 15000) OR
(Convert(varchar, vehicletype) = 'SUV' AND Convert(varchar, color) ‘black’ AND price <= 14000))
ORDER BY price ASC
100% correct solution:
SELECT name, price, color, vehicletype FROM carrecords WHERE
— black sedans up to 10k
(vehicletype = ‘SEDAN’ AND color = ‘black’ AND price <= 10000)
— red or white sedans up to 8k
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
— black SUV up to 15k
OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
— non-black SUV up to 14k
OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
— any other vehicle up to 7k
OR (price <= 7000)
ORDER BY price ASC
Question 6.
Created against standard HR Schema in Oracle 11g
which works perfectly:
select d.department_id, d.department_name,e.last_name from employees e join departments d on
(d.department_id=e.department_id)
and d.department_id in (select department_id from
employees group by department_id having count
(department_id) >=4)
order by last_name asc;
You should be able to swap out the names stated in the question accordingly and should work.
for question 6 try this:
SELECT
e1.empname
,d.deptname
FROM
(
SELECT
e.deptid
,COUNT(e.empid) AS empcount
FROM
employee AS e
GROUP BY
e.deptid
) AS ec
INNER JOIN
employee as e1
on ec.deptid = e1.deptid
INNER JOIN
department as d
ON e1.deptid = d.deptid
WHERE
ec.empcount >= 4
ORDER BY
e1.empname
hey thanks for great help
here is solution for Question no 6. 100% working and tested
select
e.empname, d.deptname
from employee as e inner join department d
on e.deptid = d.deptid
where e.deptid in
( select deptid from employee
group by deptid
having COUNT(deptid) >=4
)
order by e.empname;
Select * from carrecords
where ………..
Thanks Srdjan, your solution worked, but I wouldn’t like to publish it here, let’s keep it secret for some time 🙂 Thanks anyway!
any chance to get the correct SQL ?
Thanks!
Anwser for
7. Consider a table called carrecords with the following structure:
name (type: TEXT)
price (type: INT)
color (type: TEXT)
vehicletype (type: TEXT) eg. SEDAN/SUV
A customer wants to see the details (name, price, color, vehicletype) of the vehicles that suit his preferences. This is what he says:
“If its a black sedan, I’m ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won’t go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.”
Write a query that returns the desired information in ascending order of price.
SELECT name, price, color, vehicletype FROM carrecords WHERE
— black sedans up to 10k
(vehicletype = ‘SEDAN’ AND color = ‘black’ AND price <= 10000)
— red or white sedans up to 8k
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
— black SUV up to 15k
OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
— non-black SUV up to 14k
OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
— any other vehicle up to 7k
OR (price <= 7000)
ORDER BY price ASC
7 th soluion is : SELECT name, price, color, vehicletype FROM carrecords WHERE (vehicletype LIKE ‘SEDAN’ AND ((color LIKE ‘black’ AND price <= 10000) OR ((color LIKE 'red' OR color LIKE 'white') AND price <= 8000) OR (price <= 7000))) OR (vehicletype LIKE 'SUV' AND ((color LIKE 'black' AND price <= 15000) OR price <= 14000)) ORDER BY price
Can it work for Number 6 :
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = ‘SEDAN’ AND color = ‘BLACK’ AND price <= 10000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color IN ('red','white') AND price <= 8000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color NOT IN('RED','WHITE','BLACK') AND price <= 7000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color = 'BLACK' AND price <= 15000 UNION
SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color != 'BLACK' AND price <= 14000)
ORDER BY price ASC;
probably, only elance (which had been closed a while ago) can answer 🙂 We’re unable to try anymore, but probably it would have worked, thanks for your input!