Elance ANSI SQL code test with answers

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.

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.

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”.

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.

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’.

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!:

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:

!WRONG SQL!:

9. 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’.

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).

  • raj

    Thanks

  • Alan

    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.

  • admin

    Thanks Alan, I just tried and your solution resulted in “Wrong Answer”, I think I tried something similar, but no luck

  • Kanak

    Thanks Alan,

    Such a nice posts.

    Thanks a lot..

  • Kanak

    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
    )

  • Kanak

    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
    )

  • Kanak

    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))

  • Ruth Sewing

    This post is very useful for me, much appreciated! 🙂

  • Sumon

    Hi,

    Thanks for great effort.

    Can you solve the 6 and 7 number question?

  • Diego

    @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.

  • sugan

    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;

  • dd agarwal

    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

  • dd agarwal

    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

  • w0lf

    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.

  • Siripala

    All these answers on comment sections failed!

  • jr3

    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.

  • koxxx

    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

    • joss

      Yes, but you dont need LIKE . Put = instead of LIKE

  • koxxx

    Of course, the table name is carrecords without @.

  • Rashidul

    ha ha 😀
    SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 1

    Correct:
    SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 0,1

  • Varun Handa

    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;

  • hitesh singh

    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

  • Avinash Kumar

    Thank you for submitting this post. Excellent code

  • Geeta

    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

  • he he

    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

  • Michael Wishlow

    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.

  • Steven Willis

    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

  • Milind

    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;

  • Srdjan

    Select * from carrecords
    where ………..

    • admin

      Thanks Srdjan, your solution worked, but I wouldn’t like to publish it here, let’s keep it secret for some time 🙂 Thanks anyway!

    • alex

      any chance to get the correct SQL ?
      Thanks!

  • anand

    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

  • Jai Nigam

    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

  • joss

    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;

    • admin

      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!

Leave a Reply to Milind Click here to cancel reply.

Your email address will not be published. Required fields are marked *