Unit 3 Assignment 1 Directions Perform the following tasks below by using the co
ID: 3622134 • Letter: U
Question
Unit 3 Assignment 1
Directions
Perform the following tasks below by using the correct SQL statements. Create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command). Once your command is error-free, copy and paste your statements into this assignment document. Upload this Word document for grading.
3. Find the employee_id, first_name, last_name and number of days that everyone has been working with the company from the l_employees table. If someone doesn’t have a hire_date assume it was the first of this year.
4. Create a new view for the l_suppliers table. Call this view l_suppliers_view. This view will have all the columns of the l_suppliers table, plus a new column called abrv_nm. The abrv_name is the supplier_name column up to the first space. So for example, a name of Alice & Ray’s Restaurant will have an abrv_nm of ‘Alice’
5. List the supplier_id, product_code, and description of all columns in the l_foods table. Show only the first three letters of the first word, and all of the last word. For example, ‘FRESH SALAD’ would become ‘FRE SALAD’
6. Find the average credit limit, minimum credit, and maximum credit of employees for each department from the l_employees table. Show average credit limit, minimum credit, maximum credit, and dept_code in your results.
9. From the l_employees table, list all the different combinations of dept_code and credit_limit. Count the number of employees who are in each of these categories. Sort the rows by dept_code and then by credit_limit. Exclude the ‘SHP’ department in your results. Show only the dept_code, with a credit limit of over 12.50.
10. From the l_lunches table, count the number of lunches served each day. (The day the lunch is served is the lunch_date.)
11. From the l_employees table, count the number of employees hired in each year.
Explanation / Answer
Dear, 6. SELECT DEPT_CODE FROM l_EMPLOYEES WHERE(SELECT MAX(CREDIT_LIMIT) AS MAXIMUM FROM l_employees) SELECT DEPT_CODE FROM l_EMPLOYEES (SELECT MIN(CREDIT_LIMIT) AS MINIMUM FROM l_employees) SELECT DEPT_CODE FROM l_EMPLOYEES (SELECT AVG(CREDIT_LIMIT) AS Average FROM l_employees)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.