(Solved) : Oracle Sql Developer Mycompanysql Introduction Sql Script File Oracle Dbms Script File Cre Q41343362 . . .

ORACLE SQL Developer

Script File Create a new lab3.sql script file using Notepad or Notepad++ on your Desktop. The first four commands in this fil

/* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */set echo on;set serveroutput on;select systimestamp from dual;show user;ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY’;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;CREATE TABLE VENDOR ( V_CODE INTEGER, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE));CREATE TABLE PRODUCT (P_CODE VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY,P_DESCRIPT VARCHAR2(35) NOT NULL,P_INDATE DATE NOT NULL,P_QOH NUMBER NOT NULL,P_MIN NUMBER NOT NULL,P_PRICE NUMBER(8,2) NOT NULL,P_DISCOUNT NUMBER(5,2) NOT NULL,V_CODE NUMBER,CONSTRAINT PRODUCT_V_CODE_FKFOREIGN KEY (V_CODE) REFERENCES VENDOR);CREATE TABLE CUSTOMER (CUS_CODE NUMBER PRIMARY KEY,CUS_LNAME VARCHAR(15) NOT NULL,CUS_FNAME VARCHAR(15) NOT NULL,CUS_INITIAL CHAR(1),CUS_AREACODE CHAR(3) DEFAULT ‘615’ NOT NULL CHECK(CUS_AREACODE IN (‘615′,’713′,’931′)),CUS_PHONE CHAR(8) NOT NULL,CUS_BALANCE NUMBER(9,2) DEFAULT 0.00,CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME));CREATE TABLE INVOICE (INV_NUMBER NUMBER PRIMARY KEY,CUS_CODE NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE),INV_DATE DATE DEFAULT SYSDATE NOT NULL,CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE(’01-JAN-2012′,’DD-MON-YYYY’)));CREATE TABLE LINE (INV_NUMBER NUMBER NOT NULL,LINE_NUMBER NUMBER(2,0) NOT NULL,P_CODE VARCHAR(10) NOT NULL,LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL,LINE_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL,PRIMARY 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));CREATE TABLE EMPLOYEE (EMP_NUM NUMBER PRIMARY KEY,EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL,EMP_FNAME VARCHAR(15) NOT NULL,EMP_INITIAL CHAR(1),EMP_DOB DATE,EMP_HIRE_DATE DATE,EMP_AREACODE CHAR(3),EMP_PHONE CHAR(8),EMP_MGR NUMBER);/* Notice how we can’t include a FOREIGN KEY constraint in EMPLOYEE to reference itself because we still have not yet created the table. This has to be done separately as shown below */ALTER TABLE EMPLOYEE ADD FOREIGN KEY (EMP_MGR ) REFERENCES EMPLOYEE;/* Loading data rows Turn Escape character on Default escape character “” Used to enter special characters (&) */SET ESCAPE ON;/* VENDOR rows*/INSERT INTO VENDOR VALUES(21225,’Bryson, Inc.’ ,’Smithson’,’615′,’223-3234′,’TN’,’Y’);INSERT INTO VENDOR VALUES(21226,’SuperLoo, Inc.’ ,’Flushing’,’904′,’215-8995′,’FL’,’N’);INSERT INTO VENDOR VALUES(21231,’D&E Supply’ ,’Singh’ ,’615′,’228-3245′,’TN’,’Y’);INSERT INTO VENDOR VALUES(21344,’Gomez Bros.’ ,’Ortega’ ,’615′,’889-2546′,’KY’,’N’);INSERT INTO VENDOR VALUES(22567,’Dome Supply’ ,’Smith’ ,’901′,’678-1419′,’GA’,’N’);INSERT INTO VENDOR VALUES(23119,’Randsets Ltd.’ ,’Anderson’,’901′,’678-3998′,’GA’,’Y’);INSERT INTO VENDOR VALUES(24004,’Brackman Bros.’ ,’Browning’,’615′,’228-1410′,’TN’,’N’);INSERT INTO VENDOR VALUES(24288,’ORDVA, Inc.’ ,’Hakford’ ,’615′,’898-1234′,’TN’,’Y’);INSERT INTO VENDOR VALUES(25443,’B&K, Inc.’ ,’Smith’ ,’904′,’227-0093′,’FL’,’N’);INSERT INTO VENDOR VALUES(25501,’Damal Supplies’ ,’Smythe’ ,’615′,’890-3529′,’TN’,’N’);INSERT INTO VENDOR VALUES(25595,’Rubicon Systems’ ,’Orton’ ,’904′,’456-0092′,’FL’,’Y’);/* PRODUCT rows*/INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’03-NOV-2011′, 8, 5,109.99,0.00,25595);INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’13-DEC-2011′, 32, 15, 14.99,0.05,21344);INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’13-NOV-2011′, 18, 12, 17.49,0.00,21344);INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’15-JAN-2012’, 15, 8, 39.95,0.00,23119);INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’15-JAN-2012’, 23, 5, 43.99,0.00,23119);INSERT INTO PRODUCT VALUES(‘2232/QTY’,’B&D jigsaw, 12-in. blade’ ,’30-DEC-2011′, 8, 5,109.92,0.05,24288);INSERT INTO PRODUCT VALUES(‘2232/QWE’,’B&D jigsaw, 8-in. blade’ ,’24-DEC-2011′, 6, 5, 99.87,0.05,24288);INSERT INTO PRODUCT VALUES(‘2238/QPD’,’B&D cordless drill, 1/2-in.’ ,’20-JAN-2012′, 12, 5, 38.95,0.05,25595);INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’20-JAN-2012′, 23, 10, 9.95,0.10,21225);INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’02-JAN-2012′, 8, 5, 14.40,0.05,NULL);INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’15-DEC-2011′, 43, 20, 4.99,0.00,21344);INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’07-FEB-2012′, 11, 5,256.99,0.05,24288);INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’20-FEB-2012′,188, 75, 5.87,0.00,NULL);INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’01-MAR-2012’,172, 75, 6.99,0.00,21225);INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’24-FEB-2012’,237,100, 8.45,0.00,21231);INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’17-JAN-2012′, 18, 5,119.95,0.10,25595);/* CUSTOMER rows*/INSERT INTO CUSTOMER VALUES(10010,’Ramas’ ,’Alfred’,’A’ ,’615′,’844-2573′,0);INSERT INTO CUSTOMER VALUES(10011,’Dunne’ ,’Leona’ ,’K’ ,’713′,’894-1238′,0);INSERT INTO CUSTOMER VALUES(10012,’Smith’ ,’Kathy’ ,’W’ ,’615′,’894-2285′,345.86);INSERT INTO CUSTOMER VALUES(10013,’Olowski’ ,’Paul’ ,’F’ ,’615′,’894-2180′,536.75);INSERT INTO CUSTOMER VALUES(10014,’Orlando’ ,’Myron’ ,NULL,’615′,’222-1672′,0);INSERT INTO CUSTOMER VALUES(10015,’O”Brian’,’Amy’ ,’B’ ,’713′,’442-3381′,0);INSERT INTO CUSTOMER VALUES(10016,’Brown’ ,’James’ ,’G’ ,’615′,’297-1228′,221.19);INSERT INTO CUSTOMER VALUES(10017,’Williams’,’George’,NULL,’615′,’290-2556′,768.93);INSERT INTO CUSTOMER VALUES(10018,’Farriss’ ,’Anne’ ,’G’ ,’713′,’382-7185′,216.55);INSERT INTO CUSTOMER VALUES(10019,’Smith’ ,’Olette’,’K’ ,’615′,’297-3809′,0);/* INVOICE rows*/INSERT INTO INVOICE VALUES(1001,10014,’16-JAN-2012′);INSERT INTO INVOICE VALUES(1002,10011,’16-JAN-2012′);INSERT INTO INVOICE VALUES(1003,10012,’16-JAN-2012′);INSERT INTO INVOICE VALUES(1004,10011,’17-JAN-2012′);INSERT INTO INVOICE VALUES(1005,10018,’17-JAN-2012′);INSERT INTO INVOICE VALUES(1006,10014,’17-JAN-2012′);INSERT INTO INVOICE VALUES(1007,10015,’17-JAN-2012′);INSERT INTO INVOICE VALUES(1008,10011,’17-JAN-2012′);/* LINE rows*/INSERT INTO LINE VALUES(1001,1,’13-Q2/P2′,1,14.99);INSERT INTO LINE VALUES(1001,2,’23109-HB’,1,9.95);INSERT INTO LINE VALUES(1002,1,’54778-2T’,2,4.99);INSERT INTO LINE VALUES(1003,1,’2238/QPD’,1,38.95);INSERT INTO LINE VALUES(1003,2,’1546-QQ2′,1,39.95);INSERT INTO LINE VALUES(1003,3,’13-Q2/P2′,5,14.99);INSERT INTO LINE VALUES(1004,1,’54778-2T’,3,4.99);INSERT INTO LINE VALUES(1004,2,’23109-HB’,2,9.95);INSERT INTO LINE VALUES(1005,1,’PVC23DRT’,12,5.87);INSERT INTO LINE VALUES(1006,1,’SM-18277′,3,6.99);INSERT INTO LINE VALUES(1006,2,’2232/QTY’,1,109.92);INSERT INTO LINE VALUES(1006,3,’23109-HB’,1,9.95);INSERT INTO LINE VALUES(1006,4,’89-WRE-Q’,1,256.99);INSERT INTO LINE VALUES(1007,1,’13-Q2/P2′,2,14.99);INSERT INTO LINE VALUES(1007,2,’54778-2T’,1,4.99);INSERT INTO LINE VALUES(1008,1,’PVC23DRT’,5,5.87);INSERT INTO LINE VALUES(1008,2,’WR3/TT3′,3,119.95);INSERT INTO LINE VALUES(1008,3,’23109-HB’,1,9.95);/* EMPLOYEE rows*/INSERT INTO EMPLOYEE VALUES(100,’Mr.’ ,’Kolmycz’ ,’George’ ,’D’ ,’15-JUN-1942′,’15-MAR-1985′,’615′,’324-5456′,NULL);INSERT INTO EMPLOYEE VALUES(101,’Ms.’ ,’Lewis’ ,’Rhonda’ ,’G’ ,’19-MAR-1965′,’25-APR-1986′,’615′,’324-4472′,100);INSERT INTO EMPLOYEE VALUES(102,’Mr.’ ,’Vandam’ ,’Rhett’ ,NULL,’14-NOV-1958′,’20-DEC-1990′,’901′,’675-8993′,100);INSERT INTO EMPLOYEE VALUES(103,’Ms.’ ,’Jones’ ,’Anne’ ,’M’ ,’16-OCT-1974′,’28-AUG-1994′,’615′,’898-3456′,100);INSERT INTO EMPLOYEE VALUES(105,’Mr.’ ,’Williams’ ,’Robert’ ,’D’ ,’14-MAR-1975′,’08-NOV-1998′,’615′,’890-3220′,NULL);INSERT INTO EMPLOYEE VALUES(104,’Mr.’ ,’Lange’ ,’John’ ,’P’ ,’08-NOV-1971′,’20-OCT-1994′,’901′,’504-4430′,105);INSERT INTO EMPLOYEE VALUES(106,’Mrs.’,’Smith’ ,’Jeanine’,’K’ ,’12-FEB-1968′,’05-JAN-1989′,’615′,’324-7883′,105);INSERT INTO EMPLOYEE VALUES(107,’Mr.’ ,’Diante’ ,’Jorge’ ,’D’ ,’21-AUG-1974′,’02-JUL-1994′,’615′,’890-4567′,105);INSERT INTO EMPLOYEE VALUES(108,’Mr.’ ,’Wiesenbach’,’Paul’ ,’R’ ,’14-FEB-1966′,’18-NOV-1992′,’615′,’897-4358′,NULL);INSERT INTO EMPLOYEE VALUES(109,’Mr.’ ,’Smith’ ,’George’ ,’K’ ,’18-JUN-1961′,’14-APR-1989′,’901′,’504-3339′,108);INSERT INTO EMPLOYEE VALUES(110,’Mrs.’,’Genkazi’ ,’Leighla’,’W’ ,’19-MAY-1970′,’01-DEC-1990′,’901′,’569-0093′,108);INSERT INTO EMPLOYEE VALUES(111,’Mr.’ ,’Washington’,’Rupert’ ,’E’ ,’03-JAN-1966′,’21-JUN-1993′,’615′,’890-4925′,105);INSERT INTO EMPLOYEE VALUES(112,’Mr.’ ,’Johnson’ ,’Edward’ ,’E’ ,’14-MAY-1961′,’01-DEC-1983′,’615′,’898-4387′,100);INSERT INTO EMPLOYEE VALUES(113,’Ms.’ ,’Smythe’ ,’Melanie’,’P’ ,’15-SEP-1970′,’11-MAY-1999′,’615′,’324-9006′,105);INSERT INTO EMPLOYEE VALUES(114,’Ms.’ ,’Brandon’ ,’Marie’ ,’G’ ,’02-NOV-1956′,’15-NOV-1979′,’901′,’882-0845′,108);INSERT INTO EMPLOYEE VALUES(115,’Mrs.’,’Saranda’ ,’Hermine’,’R’ ,’25-JUL-1972′,’23-APR-1993′,’615′,’324-5505′,105);INSERT INTO EMPLOYEE VALUES(116,’Mr.’ ,’Smith’ ,’George’ ,’A’ ,’08-NOV-1965′,’10-DEC-1988′,’615′,’890-2984′,108);COMMIT; SET ESCAPE OFF;

Display all product information for products currently sellingfor less than $20.00 and which have morethan 100 units in stock (recorded in attribute P_QOH orquantity on hand).

Display all product information for products currently sellingfor less than $20.00 or which have morethan 100 units in stock (recorded in attribute P_QOH orquantity on hand).

Script File Create a new lab3.sql script file using Notepad or Notepad++ on your Desktop. The first four commands in this file should be (you should only have these in your lab3.sql file once) set echo on; set serveroutput on; select systimestamp from dual; show user Follow the format given to you in the Campansal script down. Each SQL query should have a comment before it stating the question number it applies to. For example, let’s say the first question asks you to display all records in the line table and the second question asks you to display all records in the vendor table. The content you should add to the lab3.sql file would be as follows: this is a comment in your script fle lab3.sql select*from line; 2 select*from vendor; You will use this file to insert your comments and SQL query commands for the 15 questions detailed below. NOTE: You might want to test your commands first in SQL Developer before placing them into your lab3.sql file to make sure they give you the output you desire. P. S. Your queries should be generic enough to retun proper results EVEN if the data inside the database tables changes. For example, if I ask you to display the products provided by vendors located in TN, you can’t manually extract the V CODE for vendors in TN and then use the those values to search the PRODUCT table for the corresponding products; instead, your query should be written using a join between the two tables with a WHERE clause for TN or something similar Show transcribed image text Script File Create a new lab3.sql script file using Notepad or Notepad++ on your Desktop. The first four commands in this file should be (you should only have these in your lab3.sql file once) set echo on; set serveroutput on; select systimestamp from dual; show user Follow the format given to you in the Campansal script down. Each SQL query should have a comment before it stating the question number it applies to. For example, let’s say the first question asks you to display all records in the line table and the second question asks you to display all records in the vendor table. The content you should add to the lab3.sql file would be as follows: this is a comment in your script fle lab3.sql select*from line; 2 select*from vendor; You will use this file to insert your comments and SQL query commands for the 15 questions detailed below. NOTE: You might want to test your commands first in SQL Developer before placing them into your lab3.sql file to make sure they give you the output you desire. P. S. Your queries should be generic enough to retun proper results EVEN if the data inside the database tables changes. For example, if I ask you to display the products provided by vendors located in TN, you can’t manually extract the V CODE for vendors in TN and then use the those values to search the PRODUCT table for the corresponding products; instead, your query should be written using a join between the two tables with a WHERE clause for TN or something similar

Expert Answer


Answer to ORACLE SQL Developer /* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the follo…