Oracle General SQL Exercise
In this post full schema script needed for this LAB is given. So before testing this Lab you have to run the tables and insert statement.
Write SQL statements to solve the following requests.
1.List all employee information in department 30.
2.List employees name, job, and salary that is a manager and has a salary > $1,000
3.Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
4.Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
5.Select all employees that are in department 10 and 30. Use the IN operator.
6.Select all employee names with an "A" in the first position of the employee name. Use the substring function or a wild card.
7.Select all employees with an "A" as the second character of their name. Use a wildcard.
8.List the employee names in alphabetical sequence.
9.List the job, salary, and employee name in job order and then salary in descending order.
10.Show a list of different jobs. Eliminate repeating values.
11.Show employee names, salary that has not earned a commission yet.
12.Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
13. Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with 'Intro', sorted by their prerequisites in descending order.
14. Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with "A" though "T" and who work for 'Competrol Real Estate', sorted by the last names.
15. Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.16. Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format.
17.Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
18.Write a SQL statement that converts a char datatype to a date datatype.
19.Write a SQL statement that convert a number value to a character.