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

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.