Question

SimonM37 on Mon, 04 Apr 2016 18:34:58


Hi Everyone,

Im completely new to SQL and have an exam in a couple of days, I have a list of sample questions and a dataset to read from and am wondering if anyone can answer some of the questions for me as I am not sure of the correct answers and therefore may get the answer wrong in the exam…Anyway the questions are here and the dataset is attached underneath. I know is a lot to ask but If anyone can help would be so appreciatedthanks.

Questions: 

1. Write a SQL statement which displays surname, position, started, reg, make, model, division name and location for each employee.

2. Write a SQL statement to produce results that will show each of the unique makes of car in the company database.

3. Write a SQL statement which would display the following results.

Start Dates

4488 began on 01-DEC-87 ...

4. Write a SQL statement which will return the surname and capitalize the first letter and return position and sex in lower case. Order the results by surname in descending order.

5. Write a SQL statement to calculate how many days each staff member has been working based on the Started Date. You will need to use the sysdate for comparison. Results should be rounded

6. What is the purpose of SUBSTR function?

7. Write a SQL statement which calculates each employees Total Pay (salary plus commission). Where an employee does not receive a commission substitute the null value with a zero in order to calculate the total pay. The result should also display the surname and position of each employee.

8. Write a SQL statement which displays surname, position and gives each employee's salary an increase of 11.5%. Order the results by salary in decreasing numerical order.

9. Write a SQL statement which displays all employee data where surname has an R in its spelling.

10. Write a SQL statement for members of staff in division 10 only, showing those who are female, have a job title of Director of Secretary and have I in their names.

12. Write a SQL statement to show members of staff who have a company car. In the results show the surname and the mileage they have achieved with the car. The results should exclude staff that have mileages of less than 22,000 miles.

13. Write a SQL statement to calculate the total(sum) salary paid to employees in the company (You can assume that salary refers to monthly salary) on a yearly basis.

14. Write a SQL statement which shows the number of employees working in each division.

16. Write a SQL statement to return all the surnames of all those members of staff who have the same position and division as Mr.Rao.

18 Write a SQL statement that lists each of the names of the divisions in the company, showing each member of staff employed in each division, including the divisions to which no staff belong at this time.


Dataset here:

EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM
............ ............ ....... ......... ................. ......... ...........
7369 SMITH CLERK 7902 12/17/1980 800
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300
7521 WARD SALESMAN 7698 2/22/1981 1250 500
7566 JONES MANAGER 7839 4/2/1981 2975
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400
7698 BLAKE MANAGER 7839 5/1/1981 2850
7782 CLARK MANAGER 7839 6/9/1981 2450
7788 SCOTT ANALYST 7568 4/19/1987 3000
7839 KING PRESIDENT 11/17/1981 5000
7844 TURNER SALESMAN 7698 9/8/1981 1500 0
7876 ADAMS CLERK 7788 9/8/1981 1100
7900 JAMES CLERK 7698 5/23/1987 950
7902 FORD ANALYST 7566 12/3/1981 3000
7934 MILLER CLERK 7782 1/23/1982 1300
DEPT
DEPTNO DNAME LOC
.............. .............. .........
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SAL GRADE
GRADE LOSAL HISAL NAME
............ ........... ........... ...........
1 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999



Sponsored