Thank you very much in advance for any help! This is feedback from my instructor (in red letters)…

Thank you very much in advance for any help! This is feedback from my instructor (in red letters) after completing the assignment. Help in corrections are neededand would be much appreciated!

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.

1. Using the l_employees table, add 10 dollars to everyone’s credit limit. If they don’t have a credit limit, assume that it is zero.
UPDATE L_EMPLOYEES SET CREDIT_LIMIT=10 where CREDIT_LIMIT=10 OR CREDIT_LIMIT IS NULL
To find Null values you would use the NVL() function.

2. Show the primary key column(s) of the l_lunches table. Show all the SQL statements used. Lunch_id is the primary key.
SELECT L_LUNCHES.LUNCH_ID, L_LUNCH_ITEMS.LUNCH_ID
FROM L_LUNCHES INNER JOIN L_LUNCH_ITEMS ON L_LUNCHES.LUNCH_ID = L_LUNCH_ITEMS.LUNCH_ID;

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’thave a hire_date assume it was the first of this year.

select employee_id, first_name, last_name, ROUND(SYSDATE – NVL(hire_date,to_date('01-JAN-2004'))) AS Number_Of_Days
FROM l_employees

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 columncalled 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’
Here you can use the SUBSTR and INSTR functions to create the view.

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 thelast word. For example, ‘FRESH SALAD’ would become ‘FRE SALAD’
Here you can use the SUBSTR(), INSTR(), and CONCAT() functions to retrieve the data.

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.
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)

You only need to use one select statement to compute the values in the above scenario.
7. Find all the suppliers from the l_foods table whose average price of food is greater than $2.50
SELECT L_FOODS.*, L_FOODS.PRICE
FROM L_FOODS
WHERE (((L_FOODS.PRICE)

8. From the l_employees table, count the number of employees who work in each department. Sort the rows of the result table by the dept_code;
SQL> SELECT Dept_code, COUNT(Employee_Id) AS NumEmployee
FROM l_employees
GROUP BY Dept_code;

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 thesecategories. 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 over12.50.
Here you will use the COUNT() function searching on both dept_code and credit_limit according to the specifications in the directions. You willalso use the GROUP BY and ORDER BY clauses.

10. From the l_lunches table, count the number of lunches served each day. (The day the lunch is served is the lunch_date.)

Here you can use the COUNT() function
11. From the l_employees table, count the number of employees hired in each year.
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;
To extract just the year from the date, you will use the formatting function to_char() ‘YYYY’. Review the Unit 3 PPT presentation to see specificexamples.
12. From the l_employees table, count the number of employees hired in 1999.
SQL> select count(employee_id)
from l_employees
where hired_year='1999';
To extract just the year from the date, you will use the formatting function to_char() ‘YYYY’. Review the Unit 3 PPT presentation to see specificexamples.

13. Find the date when you will be 15,000 days old. Show the date in MM/DD/YYYY format.
SQL>select to_date('25-06-1984','MM-DD-YYYY') + 15000 as getting_old
from dual;
The following error message was returned upon running the above query: ORA-01843: not a valid month Be sure to run it using the Oracle Errorfinder link inside of the Course Materials &Resources folder inside of the class.
14. Find the number of days between July 29, 1969 and January 1, 2004.
SQL>SELECT DATEDIFF('1969-07-29 23:59:59','2004-01-01');

ORA-00923: FROM keyword not found where expected See feedback above.

 

“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”

The post Thank you very much in advance for any help! This is feedback from my instructor (in red letters)… appeared first on Nursing Assignment.

 

“Are you looking for this answer? We can Help click Order Now”

The post Thank you very much in advance for any help! This is feedback from my instructor (in red letters)… first appeared on Nursing Essays Writers.


 

“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”

The post Thank you very much in advance for any help! This is feedback from my instructor (in red letters)… first appeared on nursing writers.

"Is this qustion part of your assignmentt? We will write the assignment for you. click order now and get up to 40% Discount"
blank