Practice 3 ociety.org



Practice 3, Functions

The solution must include the SQL query and the results of that query.

Clearly label all problems. These problems may not all be graded.

1. Write a query that produces the following for each employee: earns monthly but wants . Label the column Dream Salaries. Sort the rows in descending of Dream Salary. Example, Ward earns 1250 monthly but wants 3750.

2. Write a query that will display the employees’ names with the first letter capitalized and all other letters lowercase and the length of their name. Give each column an appropriate label.

3. Group functions work across many rows to produce one result per group. True/False

4. Group functions include nulls in calculations. True/False

5. The WHERE clause restricts rows prior to inclusion in a group calculation. True/False

6. Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Save your SQL statement in a file called p5q4.sql.

7. Modify p5q4.sql to display the minimum, maximum, sum, and average salary for each job type. Resave to a file called p5q5.sql. Rerun your query.

8. Write a query to display the number of people with the same job. (List the job and the number of people with that job.)

9. Determine the number of managers without listing them. Label the column Number of Managers.

10. Write a query that will display the difference between the highest and lowest salaries. Label the column DIFFERENCE.

To Put It All Together:

11. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $1000. Sort the output in descending order of salary.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download