Binary Cipher
1. Write a query for each employee, display the employee number, last name, salary increased by 15% and expressed as a whole number. Label the column New Salary.
2. For above query add a column that subtracts the old salary from the new salary and Label the column Increase.
3. Write a query that display the employees last names with the first letters capitalized and all other letters lower case, and the length of the names, for all employees whose name starts with J,A, or M. and give each column with the appropriate label. Sort the result by the employee’s last names.
4. For each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed.
5. For above query Round the number months up to the closest whole number.
6. Write a query that produces the following for each employee:
<employee last name> earns <salary> monthly but wants <3 times salary>. Label the column dream salaries.
7. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long. Left padded with $. Label the column SALARY.
8. Display the employees last name , hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “MONDAY, the Thirty-First of the July, 2000.”
9. Display the employees last name, hire date, and day of the week on which the employee started. Label the column DAY. Order your results by the day of the week starting with Monday.
10. Write a query that display the employees last names and commission amounts. If the employee does not earn commissions then put “No Commission”. Label the column COMM.
11. Create a query that displays the employees last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column Employees_and_Their_Salaries.
12. Using the decode function. Write a query that display the grade of all employees based on the values of the column JOB_ID, as per the following data.
Job Grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
SA_CLERK E
None of the O
above
13. Write a Query that uses all case manipulation functions and concatenate them
14. Write a Query that uses all character manipulation functions and concatenate them
15. Display the data for those employees whose last names end with an n.
16. Display the last name, hire date and number of weeks employed for all employees in the departments 60, 90.
17. Display the last name, employee number, hire date and number of months employed , six-months review date, first Friday after hire date, and last day of the hire months for all employees employed for fewer than 36 months.
18. Compare the hire dates for all employees who started in 1997. Display the last name, employee number, start month using Round and Trunc functions.
19. Display the dates in a format that appears as seventh of June 1994 12:00:00 AM .
20. Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, August 13th , 1999 . Order results by hire date.
21. calculate annual compensation of all employees, you need to multiply the monthly salary by 12 and then add the commission percentage to it.
Practice 1:
1. There are four coding errors in this statement. Can you identify them?
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
2. Show the structure of the DEPARTMENT table. Select all data from the table.
3. Show the structure of the EMPLOYEES table. Create a query to display the last name, job code,
hire date, and employee number for each employee, with employee number appearing first.
Provide an alias STARTDATE for the HIRE_DATE column.
4. Create a query to display unique job codes from the EMPLOYEES table.
5. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the HIRE_DATE column. Name the column Headings Emp #, Employee, Job, and Hire Date, respectively. Run your query again.
6. Display the last name concatenated with the job ID, separated by a comma and space, and name
the column Employee and Title.
7. Create a query to display all the data from the EMPLOYEES table. Separate each column by a
comma. Name the column THE_OUTPUT.
Practice 2:
1. Create a query to display the last name and salary of employees earning more than $12,000.
2. Create a query to display the employee last name and department number for employee number
176.
3. Modify problem1 to display the last name and salary for all employees whose salary is
not in the range of $5,000 and $12,000. Place your SQL statement in a text file named
lab2_3.sql.
4. Display the employee last name, job ID, and start date of employees hired between February
20, 1998, and May 1, 1998. Order the query in ascending order by start date.
5. Display the last name and department number of all employees in departments 20 and 50 in
alphabetical order by name.
6. Modify problem3 to list the last name and salary of employees who earn between $5,000
and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly
Salary, respectively.
7. Display the last name and hire date of every employee who was hired in 1994.
8. Display the last name and job title of all employees who do not have a manager.
9. Display the last name, salary, and commission for all employees who earn commissions. Sort
data in descending order of salary and commissions.
10. Display the last names of all employees where the third letter of the name is an a.
11. Display the last name of all employees who have and a and an e in their last name.
12. Display the last name, job, and salary for all employees whose job is sales representative or
Stock clerk and whose salary is not equal to $2,500, $3,500, or $7,000.
13. Display the last name, salary, and commission for all employees whose commission amount is 20%.
14. Display the last name, hire date of all employees who joined between January 99 and December 99.
15. Display the last name, job id, Commission for all employees who are not entitled to get a commission.
16. Display the last names from employees except names starts with letter W.
17. Display the last names from employees where third letter starts with letter ‘a’.
18. Display employee type representative and earns more than 20,000 or if the employee is sales manager.
19. Display employee type representative or president and if the employee earns more than 20,000.
20. Display the last name and salaries of all employees order the result by department number and then in descending order by salary.
21. Display the department id column sorted in ascending order and the salary column in descending order.
22. Display hire dates of an employee that is hired between 01-02-99 from 01-12-99.
23. Display the employees’ salarie for one-day, one-week and average salaries.