Thank you very much in advance for any help! This is feedback from my instructor

Question

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 needed and 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’t have 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 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’
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 the last 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)<(SELECT AVG(PRICE) FROM L_FOODS)));

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 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.
Here you will use the COUNT() function searching on both dept_code and credit_limit according to the specifications in the directions. You will also 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 specific examples.
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 specific examples.

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 Error finder 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.

Explanation / Answer

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=NVL(CREDIT_LIMIT,0) + 10 ; 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 * from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P' and TABLE_NAME = 'L_LUNCHES'; 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. select employee_id, first_name, last_name, ROUND(SYSDATE - NVL(hire_date,to_date('01-JAN-2012'))) 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 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’ CREATE VIEW l_suppliers_view as SELECT L_SUPPLIERS.*, SUBSTR(supplier_name,1, INSTR(supplier_name,' ',1,1)-1) as ABRV_NM FROM L_SUPPLIERS; 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’ ***For future questions, PLS also provide the table structures used for the assignment. It is very tough to do the guess work. In this question, I am providing the logic to derive the column. You can use this in your select statement. CONCAT( (SUBSTR(description,1,3)) , SUBSTR(description, INSTR(description,' ',1,1)+1) ) 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,AVG(CREDIT_LIMIT),MAX(CREDIT_LIMIT),MIN(CREDIT_LIMIT) FROM l_EMPLOYEES GROUP BY DEPT_CODE; 7. Find all the suppliers from the l_foods table whose average price of food is greater than $2.50 SELECT SUPPLIER_NAME , AVG(PRICE) FROM L_FOODS GROUP BY SUPPLIER_NAME HAVING AVG(PRICE) >2.5; 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 ORDER 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 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. SELECT dept_code, credit_limit, COUNT(*) as NUM_EMPLOYEES FROM L_EMPLOYEES WHERE dept_code 'SHP' AND credit_limit>12.5 GROUP BY dept_code, credit_limit ORDER BY dept_code, credit_limit; 10. From the l_lunches table, count the number of lunches served each day. (The day the lunch is served is the lunch_date.) SELECT lunch_date,COUNT(*) FROM L_LUNCHES GROUP BY lunch_date; 11. From the l_employees table, count the number of employees hired in each year. select to_char(hire_date,'YYYY'),count(*) as employees_hired_this_year from l_employees group by to_char(hire_date,'YYYY'); 12. From the l_employees table, count the number of employees hired in 1999. select to_char(hire_date,'YYYY'),count(*) as employees_hired_this_year from l_employees where to_char(hire_date,'YYYY')='1999' group by to_char(hire_date,'YYYY'); 13. Find the date when you will be 15,000 days old. Show the date in MM/DD/YYYY format. select to_Date((to_date('25-06-1984')+15000),'MM/DD/YYYY') as getting_old from dual; 14. Find the number of days between July 29, 1969 and January 1, 2004. SQL>SELECT ROUND(DATEDIFF( to_Date('1969-07-29'), to_Date('2004-01-01'))) FROM DUAL;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.