Directions You must create the statements in Oracle by using the following steps

Question

Directions
You must 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 document. Upload this Word document for grading.

1. Write the SQL statement you would use to find out all the tables that you have access to.

SELECT table_name FROM user_tables;
2. Write the command to list all the columns and rows of the l_foods table.

3. Write the command to list all the columns and rows of the l_lunches table.
4. Write the command to list all the employees hired in 2003 and 2004. Sort the rows by the hire_date column in descending order. Show columns last_name, hire_date, phone_number, and dept_code in your results. Also, change the name of dept_code column to department_code.

5. List all the distinct employee_id values in the l_lunches table. Sort these ids in ascending order.

SELECT DISTINCT
EMPLOYEE_ID
FROM * L_LUNCHES ORDERBY EMPLOYEE_ID ASC;

6. List all distinct combinations of supplier_id and product_code from the l_lunch_items table. Exclude the data for lunch 17. Sort the result on both columns in ascending order.

7. Using the l_foods table, place the columns in this order: menu_item, description, price_increase, price, supplier_id, product_code. Sort the rows by product_code in ascending order and supplier_id in descending order.


8. From the l_foods table, get the price of a grilled steak. Show price and description in your results.

9. From the l_employees table, get the hire date and phone number for Paula Jacobs and Fred Campbell.

10. List the foods costing less than $2.00 that don’t have a price increase scheduled. Show the description and price of each food. Sort the rows by the description of the food.

11. List the all the foods supplied by all the suppliers except ASP and JRV.

12. List all the employees who have a credit limit of more than Jim Kern. (Hint: You might need two select statements for this)

Explanation / Answer

Dear, Here is the solutions to your queries.    1) SQL> Show tables; 2) SQL> SELECT * FROM l_foods; 3) SQL> SELECT * FROM l_lunch; 4) SQL> SELECT last_name, hire_date, phone_number, dept_code as department_code FROM employees WHERE hire_date BETWEEN 01-jan-03 AND 01-jan-2004 ORDERBY hire_date desc AND ALTER TABLE department RENAME COLUMN dept_code to department_code; 5) SQL> SELECT DISTINCT EMPLOYEE_ID FROM L_LUNCHES ORDERBY EMPLOYEE_ID ASC; 8) SQL> SELECT price, description
FROM l_foods
WHERE description = ‘grilled steak’; 9) SQL> Select hire_date,
phone_number
From l_employees where LAST_NAME IN (‘Paula Jacobs’, ‘Fred Campbell’); 10) SQL> SELECT description,price from l_foods where (price<2) ORDERBY DESCRIPTION ASC; 11) SQL> SELECT *FROM SUPPLIERS WHERE SUPPLIER_ID NOT IN(‘ASP’,’RJV’); 12) SQL> SELECT * FROM EMPLOYEES WHERE CREDITLIMIT>(SELECT CREDITLIMIT FROM EMPLOYEES WHERE EMPLOYEE_NAME=’JIM KERN’) 1) SQL> Show tables; 2) SQL> SELECT * FROM l_foods; 3) SQL> SELECT * FROM l_lunch; 4) SQL> SELECT last_name, hire_date, phone_number, dept_code as department_code FROM employees WHERE hire_date BETWEEN 01-jan-03 AND 01-jan-2004 ORDERBY hire_date desc AND ALTER TABLE department RENAME COLUMN dept_code to department_code; 5) SQL> SELECT DISTINCT EMPLOYEE_ID FROM L_LUNCHES ORDERBY EMPLOYEE_ID ASC; 8) SQL> SELECT price, description
FROM l_foods
WHERE description = ‘grilled steak’; 9) SQL> Select hire_date,
phone_number
From l_employees where LAST_NAME IN (‘Paula Jacobs’, ‘Fred Campbell’); 10) SQL> SELECT description,price from l_foods where (price<2) ORDERBY DESCRIPTION ASC; 11) SQL> SELECT *FROM SUPPLIERS WHERE SUPPLIER_ID NOT IN(‘ASP’,’RJV’); 12) SQL> SELECT * FROM EMPLOYEES WHERE CREDITLIMIT>(SELECT CREDITLIMIT FROM EMPLOYEES WHERE EMPLOYEE_NAME=’JIM KERN’)
Hope this will help you.    1) SQL> Show tables; 2) SQL> SELECT * FROM l_foods; 3) SQL> SELECT * FROM l_lunch; 4) SQL> SELECT last_name, hire_date, phone_number, dept_code as department_code FROM employees WHERE hire_date BETWEEN 01-jan-03 AND 01-jan-2004 ORDERBY hire_date desc AND ALTER TABLE department RENAME COLUMN dept_code to department_code; 5) SQL> SELECT DISTINCT EMPLOYEE_ID FROM L_LUNCHES ORDERBY EMPLOYEE_ID ASC; 8) SQL> SELECT price, description
FROM l_foods
WHERE description = ‘grilled steak’; 9) SQL> Select hire_date,
phone_number
From l_employees where LAST_NAME IN (‘Paula Jacobs’, ‘Fred Campbell’); 10) SQL> SELECT description,price from l_foods where (price<2) ORDERBY DESCRIPTION ASC; 11) SQL> SELECT *FROM SUPPLIERS WHERE SUPPLIER_ID NOT IN(‘ASP’,’RJV’); 12) SQL> SELECT * FROM EMPLOYEES WHERE CREDITLIMIT>(SELECT CREDITLIMIT FROM EMPLOYEES WHERE EMPLOYEE_NAME=’JIM KERN’)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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