Unit 3 Assignment 1 Directions Perform the following tasks below by using the co
ID: 3622169 • 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’
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.
11.From the l_employees table, count the number of employees hired in each year.
Explanation / Answer
5.SQL>select supplier_id, product_code,substr(description, 1,3)||’ ‘|| substr(description,instr(description,’’,3)) as description from l_foods;
11.SQL> select count(hire_date) as employees_hired_this_year,hire_date
from l_employees
where hire_date between '01-JAN-1998' and '01-JAN-2010'
group by hire_date;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.