Q17) List the V_CODE and V_NAME of vendors that do not provide productsQ18) ?How much is the total value (P_QOH*P_PRICE) of our product inventory DROP TABLE LINE cascade constraints;DROP TABLE INVOICE cascade constraints;DROP TABLE CUSTOMER cascade constraints;DROP TABLE PRODUCT cascade constraints;DROP TABLE VENDOR cascade constraints;DROP TABLE EMPLOYEE cascade constraints;DROP TABLE EMP cascade constraints;DROP TABLE P cascade constraints;DROP TABLE V cascade constraints;purge recyclebin;CREATE TABLE PRODUCT (P_CODE VARCHAR2 (10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY,P_DESCRIPTP_INDATEP_QOHP_MINP_PRICEP_DISCOUNTV_CODECONSTRAINT PRODUCT_V_CODE_FKFOREIGN KEY (V_CODE) REFERENCES VENDOR);VARCHAR2 (35) NOT NULL,DATE NOT NULL,NUMBER NOT NULL,NUMBER NOT NULL,NUMBER (8,2) NOT NULL,NUMBER (4,2) NOT NULL,NUMBER,CREATE TABLEV (V_CODEV_NAMEV_CONTACTV_AREACODEV_PHONEV_STATEV_ORDERINTEGER PRIMARY KEY,VARCHAR ( 35) NOT NULL,VARCHAR (15) NOT NULL,CREATE TABLE CUSTOMER (Cus_CODECuS_LNAMECuS_FNAMECus_INITIALCUS_AREACODECuS_PHONECus_BALANCECONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME, CUS_FNAME));NUMBER PRIMARY KEY,VARCHAR (15) NOT NULL,VARCHAR (15) NOT NULL,CHAR(1),CHAR (5) DEFAULT '615' NOT NULL CHECK (CUS_AREACODE IN ('615',"713', '931')),CHAR(8) NOT NULL,CHAR (5) NOT NULL,CHAR (8) NOT NULL,CHAR (2) NOT NULL,CHAR (1) NOT NULL);NUMBER (9,2) DEFAULT 0.00,CREATE TABLE P (P_CODEP_DESCRIPTP_INDATEP_QOHP_MINVARCHAR2 (10) PRIMARY KEY,VARCHAR2 (35) NOT NULL,DATE NOT NULL,NUMBER NOT NULL,NUMBER NOT NULL,NUMBER (8,2) NOT NULL,NUMBER (4,2) NOT NULL,NUMBER);CREATE TABLE INVOICE (INV_NUMBERCus_CODEINV_DATECONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2819","D0-NON-YYYY")));NUMBER PRIMARY KEY,NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE),DATE DEFAULT SYSDATE NOT NULL,P_PRICEP_DISCOUNTV_CODECREATE TABLE LINE (CREATE TABLE VENDOR (V_CODEV_NAMEv_CONTACTV_AREACODEV_PHONEV_STATEv_ORDERPRIMARY KEY (V_CODE));NUMBER NOT NULL,INV_NUMBERLINE_NUMBERP_CODELINE_UNITSLINE_PRICEPRIMARY KEY (INV_NUMBER, LINE_NUMBER),FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE,FOREIGN KEY (P_CODE) REFERENCES PRODUCT (P_CODE),CONSTRAINT LINE_UI1 UNIQUE (INV_NUMBER, P_CODE));INTEGER,VARCHAR ( 35) NOT NULL,VARCHAR (15) NOT NULL,CHAR (5) NOT NULL,CHAR (8) NOT NULL,CHAR (2) NOT NULL,CHAR (1) NOT NULL,NUMBER (2,0) NOT NULL,VARCHAR (10) NOT NULL,NUMBER (9,2) DEFAULT 8.00 NOT NULL,NUMBER (9,2) DEFAULT 8.00 NOT NULL, CREATE TABLE EMPLOYEE (EMP_NUMEMP_TITLEEMP LNAMEEMP_FNAMEEMP_INITIALEMP DOBEMP_HIRE DATEEMP_YEARSEMP_AREACODEEMP_PHONE/*P rowsNUMBER PRIMARY KEY,INSERT INTO P VALUES("11QER/31", "Power painter, 15 psi., 3-nozzleINSERT INTO P VALUES("13-Q2/P2","7.25-in. pwr. saw blade"INSERT INTO P VALUES("14-Q1/L3", "9.e8-in. pwr. saw blade"INSERT INTO P VALUES("1546-Q02", "Hrd. cloth, 1/4-in., 2x5e"INSERT INTO P VALUES("1558-QW1", "Hrd. cloth, 1/2-in., 3x5eINSERT INTO P VALUES( "2232/QTY", "B\&D jigsaw, 12-in. bladeINSERT INTO P VALUES("2232/QWE", "B\&D jigsaw, 8-in. blade"INSERT INTO P VALUES("2238/QPD", "B\40 cordless drill, 1/2-in."INSERT INTO P VALUES("23109-HB", "C1law hamnerINSERT INTO P VALUES("23114-AA", "Sledge hamner, 12 lb."INSERT INTO P VALUES("54778-21', "Rat-tail file, 1/8-1in. fine'INSERT INTO P VALUES("89-WRE-Q', "Hicut chain saw, 16 in."INSERT INTO P VALUES("PVC230RT", "Pc pipe, 3.5-in., 8-ft"INSERT INTO P VALUES("SM-18277",'1.25-in. netal screw, 25"INSERT INTO P VALUES("SW-23116","2.5-in. wd. screw, seINSERT INTO P VALUES("WR3/TT3' ,"Steel ratting, 4''x8" "x1/6", .5" nesh',"17-JAN-2019", 18, 5,119.95,8.10,25595);e3-NOV-2018', 8, 5,109.99,8.ee,25595);,'13-DEC-2018", 32, 15, 14.99,8.e5, 21344);,'13-NOV-2019', 18, 12, 17.49,0.ee,21344);,'15-JAN-2019', 15, 8, 39.95,0.00,23119);,'15-JAN-2819", 23, 5, 43.99,8.80, 23119);,38-DEC-2018', 8, 5,109.92,8.e5,24288);,"24-DEC-2018', 6, 5, 99.87,8.e5,24288);,"28-JAN-2019', 12, 5, 38.95,8.e5,25595);,"20-JAN-2019', 23, 10, 9.95,0.18,21225);,"e2-JAN-2019', 8, 5, 14.40,0.e5, NULL);,'15-DEC-2018', 43, 20, 4.99,0.00,21344);,'87-FEB-2019", 11, 5,256.99,8.05, 24288);,28-FEB-2019',188, 75, 5.87,0.e0, NULL);e1-MAR-2019", 172, 75, 6.99,8.e0,21225);"24-FLB-2019', 237,100, 8.45,0.e0, 21231);CHAR (10),VARCHAR (15) NOT NULL,VARCHAR (15) NOT NULL,CHAR (1),DATE,DATE,NUMBER,CHAR (3),CHAR (8));CREATE TABLE EMP (EMP_NUMEMP_TITLEEMP LNAMEEMP_FNAMEEMP_INITIALEMP_DOBEMP_HIRE DATEEMP_AREACODEEMP_PHONEEMP_MGRNUMBER PRIMARY KEY,CHAR (10),VARCHAR(15) NOT NULL,VARCHAR (15) NOT NULL,CHAR (1),DATE,* VENDOR rowsINSERT INTO VENDOR (SELECT • FROM V);DATE,CHAR (3),CHAR (8),NUMBER);/* PRODUCT rowsINSERT INTO PRODUCT (SELECT • FRROM P);* CUSTOMER rOwsINSERT INTO CUSTOMER VALUES (18018, "RanasINSERT INTO CUSTOMER VALUES(10011, 'DunneINSERT INTO CUSTOMER VALUES(10012, 'SithINSERT INTO CUSTOMER VALUES (10013, 'Olowski" , "Paul' ,'F' ,'615', '894-218e",536.75);INSERT INTO CUSTOMER VALUES (18014, 'Orlando" , 'Hyron ,NULL, '615','222-1672',8);INSERT INTO CUSTOMER VALUES (18015, '0""Brian", "Any"INSERT INTO CUSTOMER VALUES (10016, 'Brown"INSERT INTO CUSTOMER VALUES (18017, 'Willians", "George", NULL, '615", "298-2556",768.93);INSERT INTO CUSTOMER VALUES (18018, 'Farriss" ,'Anne ,' ,'713",'382-7185', 216.55);INSERT INTO CUSTOMER VALUES (10019, 'Snith"/* Loading data rows/* Turn Escape character on/* Default escape character "I/* Used to enter special characters (8),'Alfred", 'A" ,'615s', '844-2573",8);"Leona" ,"K" ,'713','894-1238',8);"Kathy" , "W ,'615",'894-2285', 345.86);SET ESCAPE ON;,'B' ,'713','442-3381',8);"Janes' ,'6" ,'615','297-1228', 221.19);/* v rOwsINSERT INTO V VALUES (21225, 'Bryson, Inc."INSERT INTO V VALUES (21226, 'SuperLoo, Inc."INSERT INTO V VALUES (21231, 'D\&E Supply"INSERT INTO V VALUES (21344, 'Jabavu Bros."INSERT INTO V VALUES (22567, 'Dome Supply'INSERT INTO V VALUES (23119, 'Randsets Ltd.'INSERT INTO V VALUES (24804, 'Bracknan Bros."INSERT INTO V VALUES (24288, 'ORDVA, Inc."INSERT INTO V VALUES (25443, 'B\&K, Inc."INSERT INTO V VALUES (25501, 'Damal Supplies"INSERT INTO V VALUES (25595, 'Rubicon Systens ,'Du Toit',"0lette", "K" ,'615', 297-3829",8);,'Smithson', '615','223-3234', "TN','Y');"Flushing', '984','215-8995', "FL','N');615','228-3245', "TN','Y');'615','889-2546', 'KY', 'N');981','678-1419', "GA', 'N');,'Anderson', '981','678-3998", " GA',"Y');"Browning', '615', 228-1410, TN', 'N');,"Hakford','615','898-1234',"TN',"Y');*984','227-0093', 'FL','N');,"Smythe' ,'615','890-3529","TN', 'N');984','456-0092", 'FL', 'Y');/* INVOICE rowsINSERT INTO INVOICE VALUES(1801,18014, '15-JAN-2019');INSERT INTO INVOICE VALUES(1002, 18811, '16-JAN-2019');INSERT INTO INVOICE VALUES(1003,10012, '16-JAN-2019');INSERT INTO INVOICE VALUES(1004, 18e11, '17-JAN-2019');INSERT INTO INVOICE VALUES(1005,10018, '17-JAN-2819');INSERT INTO INVOICE VALUES(1006,10014, '17-JAN-2819');INSERT INTO INVOICE VALUES(1007,18e15, '17-JAN-2019');INSERT INTO INVOICE VALUES(1808,18011, '17-JAN-2019');"Singh","Khunalo'Smith"Smith

Part(17) Following is the query for the listing V_CODE and V_NAME of those vendors that does not…

Answered: Q17) List the V_CODE and V_NAME of…

Q17) List the V_CODE and V_NAME of vendors that do not provide products Q18) ?How much is the total value (P_QOH*P_PRICE) of our product inventory

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

given data Team (tname, year, coach, salary) Player (pname, position) Winner (tname, year) Played (pname, tname, year, salary) write the sql queries for the following questions. 1) List all the players who have salary higher than 2M$, and have played for all the teams in the league . 2) List coaches who have also been players, and have coached only teams they once played for. 3)List all the players who won the Stanley Cup during two consecutive years with two different teams.
Find the total revenue generated by each product in the past quarter (last 3 months), you would need the following tables and their respective columns: Orders table: order_id (Primary key) order_date Order_details table: order_id (Foreign key) product_id (Foreign key) quantity price_per_unit Products table: product_id (Primary key) product_name
4
have data base with following information: customer table with filed(cid,cname,city,country) order table with filed(cid ,oid,total amount) Q: List all orders with customer information.
8. use sql and the following data (as shown below )to Display product Id, product name for products that their list price is more than any highest product standard cost per warehouse outside Americas regions.(You need to find the highest standard cost for each warehouse that is located outside the Americas regions. Then you need to return all products that their list price is higher than any highest standard cost of those warehouses.) Sort the result according to list price from highest value to the lowest.
i need code sql THE TABLES (PROJECT, DEPARTMENT, EMPLOY). • Each EMPLOY has (nemp, ID number, ndept, nproj,salary) NEMP NOT NULL VARCAHR(30) PRIMARY KEY, ID_NUMBER(10) NOT NULL, NDEPT VARCAHR(15), NPROJ VARCAHR(7) PRIMARY KEY, SALARY NUMBER(17). Requirements • Each PROJECT has (nproj, ndept,nemp) NPROJ VARCAHR(15) NOT NULL FOREGIN KEY KEY, NEMP VARCAHRH(30) PRIMARY KEY, NDEPT VARCAHR(10). • Each DEPARTMENT has(ndept, number nemp) Number NUMBER(3), NDEPT VARCAHR(30) NOT NULL, NEMP VARCAHR(15) FORGEIN KEY. Entity Relationship THE EMPOLY HAS A NEMP AND NPROJ AS PRIMARY KEY WILL CONACTED TO BOTH DEPARTMENT AND PROJECT AS FOREIGN KEY. THE PROJECT HAS A NPROJ AS FORGIEN KEY WILL CONACTED TO EMPOLY AS PRIMARY KEY. THE DEPARTMENT HAS A NEMP AS FORGIEN KEY WILL CONACTED TO BOTH EMPLOY AND PROJECT AS PRIMARY KEY. (RELASHINSHEP: EVERY DEPARTMENT has PROJECT and EVERY EMPLOY work on PROJECT and work in DEPARTMENT)
List the number and name of all customers who are assigned the Rep Juan Perez (first name & last name). Show 2 different ways to achieve these results. a) Accessing multiple tables b) Accessing multiple tables
Given the ORDER, ORDER_DETAIL, CUSTOMER, and PRODUCT tables. CUSTOMER CUSTOMER_ID CUSTOMER_NAME 1565 Emre 1789 Akif 1265 Eyup 4537 Fatih 6542 Murat CITY CITY_ID CITY_NAME 1 Gaziantep 2 Balıkesir 3 Edirne 4 Van 5 Rize 6 Kayseri ORDER ORDER_ID ORDER DATE 1 05.08.2012 2 11.04.2015 3 12.06.2013 4 02.05.2015 5 02.09.2014 ADDRESS CITY_ID CITY_NAME Gatem 2 Rasaf yolu 1 Damlacık 6 köyü Seyrantepe 1 Sarıgüllük PRODUCT PRODUCT_ID PRODUCT_NAME PRICE (TL) 1 Bilgisayar 3250 2 Telefon 1800 3 Monitör 650 4 Klavye 45 5 1200 Televizyon Masa 6 60 CUSTOMER_ID 1565 1265 4537 6542 1565 ORDER_DETAIL ORDER_ID PRODUCT_ID 1 1 1 3 1 4 2 4 3 4 3 5 4 1 4 3 4 5 4 6 5 1 5 3
write following queries
I need help with the SQL query for the following question: 1. Show the total number of customers who complained about Bluetooth issues for every product name of every manufacturer from every retailer Here is the information on the field names: The table name is: Reviews Review Form Fields Product/ModelName: Product/Category: Product/Price: Retailer/Name: Retailer/ZipCode: Retailer/City: Retailer/State: Product/On Sale: Manufacturer/Name: Manufacturer/Rebate: Review/UserId: Review/Rating: Review/Date: Review/Text: Example of Review Form Filled out ProductModelName: Galaxy S4 ProductCategory: Smart Phone ProductPrice: 499 RetailerName: Bestbuy RetailerZipCode: 44114 RetailerCity: Cleveland RetailerState: OH ProductOnSale: No ManufacturerName: Samsung ManufacturerRebate: No ReviewUserId: xmm473 ReviewRating: 3 ReviewDate: 6/10/2012 ReviewText: overheats after 2 hours
Product Order PID Description Quantity 300 Price Vendor OID Date 2012/5/23 2 CID Unique traders AT Cosmetics General traders Unique traders General traders Foodie Shop Shampoo 50 1 1 2 Soap 140 230 40 30 2012/5/24 3 Detergent 4 2016/4/23 4 Milk 14 33 3 2016/2/12 2016/1/10 4 Lemon Max 10 20 6 1 6. Chocolate 34 34 Invoice Customer OrderNo 1 ProductID Quantity CustomerID Name city 1 1 Ali Rawalpindi 1 3 1 Ahmad Islamabad 4 2 1 Umer Chakwal 2 6. 3 Каrim Islamabad 2 3 1 Shafig Islamabad 3 2 6. Waleed Rawat 4 Afzal Islamabad 4 3 4 1 Bilal Rawalpindi 4 4 Write SQL queries for the following statements (Use only Cartesian product and join) 1. Showname of customerand orderno who have placed any order and belong to "Islamabad". 2. Showname of customer and product description who placed order before 2016/2/23. 3. Show data of those customer who place two or more than two order in same day. 4. Showdata of those product that are purchased by those customer who neither belong 5. To"Rawat"nor…
Using the Tables provided create two store procedures and define the business reasons for them.