Oracles Technology (ORT) is an IT company offering vast assortment of IT hardware products and services to its customers

[answerhappygod]

Oracles Technology Ort Is An It Company Offering Vast Assortment Of It Hardware Products And Services To Its Customers 1 (531.25 KiB) Viewed 15 times

Oracles Technology (ORT) is an IT company offering vast assortment of IT hardware products and services to its customers. Although the company is performing well, there is nothing extraordinary about it: it is nothing more than a well-oiled organization, employing great people in present but flat hierarchies. As you will be working with ORT's DB, ORT's MIS department supplied you with some documents to begin your DB consulting work. To start, the model of ORT's database is presented below: CONTACTS CONTACT_ID FIRST_NAME LAST_NAME EMAIL PHONE CUSTOMER_ID CUSTOMERS OH CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT_LIMIT ORDERS ORDER_ID CUSTOMER_ID STATUS SALESMAN_ID ORDER_DATE EMPLOYEES EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE HIRE DATE MANAGER_ID JOB_TITLE COUNTRIES COUNTRY_ID COUNTRY_NAME REGION ID Ha LOCATIONS LOCATION_ID O ADDRESS POSTAL_CODE CITY STATE COUNTRY_ID ORDER_ITEMS ORDER_ID ITEM_ID PRODUCT_ID QUANTITY UNIT PRICE REGIONS REGION_ID REGION NAME WAREHOUSES WAREHOUSE ID WAREHOUSE NAME LOCATION_ID INVENTORIES PRODUCT_ID OA WAREHOUSE_ID QUANTITY 生 PRODUCTS PRODUCT_ID PRODUCT NAME DESCRIPTION STANDARD COST LIST_PRICE OCATEGORY_ID PRODUCT_CATEGORIES CATEGORY_ID CATEGORY_NAME 主 Although you still do not have the complete access to ORT'S RDBMS, ORT's DBA Ali was kind enough to provide you an excerpt of the data you will be working with in a separate XLSX file attached. After all, you will only be providing some rudimentary services and proof of concepts, so you do not actually need a complete access to the complete database with operational business data in it. He also mentions that they just start experimenting with one new entity and that that particular entity might not be present in the database model. You spent some time inspecting those files and after you ended up establishing the first impression of ORT's data resources you decided to start answering Ali's questions presented below.

Q6-10 are SQL based question. Explain how the presented SQL code works and provide the result you would expect to see given that the presented code is executed on the data excerpt provided to you. If errors are present in the code, make sure to correct them so that the command would execute properly.

Q6: SELECT FIRST_NAME FROM (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES MINUS SELECT VIPEMPLOYEE_ID, VIPFIRST_NAME, VIPLAST_NAME, VIPEMAIL FROM VIPEMPLOYEES) WHERE LENGTH(FIRST_NAME)<1 OR FIRST_NAME='Morgoth'; Q7: SELECT PRODUCT_NAME FROM PRODUCTS WHERE STANDARD_COST> ((SELECT AVG(STANDARD_COST) FROM PRODUCTS) * FIVE); Q8: SELECT CITY, WAREHOUSE_ID FROM LOCATIONS FULL JOIN WAREHOUSES ON LOCATIONS. LOCATION_ID = WAREHOUSES. LOCATION_ID WHERE WAREHOUSE_ID IS 'NULL' AND LENGTH (CITY)<5; Q9: SELECT CATEGORY_ID, COUNT (PRODUCT_ID) AS NUMPRODS FROM PRODUCTS GROUP BY CATEGORY_ID HAVING AVG(LIST_PRICE) >30 ORDER BY CATEGORY_ID; Q10: CREATE OR REPLACE PROCEDURE EOY_SALES AS BEGIN UPDATE PRODUCTION SET LIST_PRICE-STANDARD_COST*0.95 WHERE LIST_PRICE < 500; UPDATE PRODUCTS SET LIST_PRICE-STANDARD_COST*0.90 WHERE LIST_PRICE > 500; END; BEGIN EOY_SALES; END;