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

ORACLE SQL Developer

Initial Setup - Read & Complete First The diagram below shows a relational schema made up of 6 tables with all primary keys u

Part 1: Write SQL Queries into lab3.sql script file Script File Create a new Lab3.sql script file using Notepad or Notepad++

/* 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;

  1. Display all product information for products thatcontain the string ‘saw’ anywhere in theirdescription.

Initial Setup – Read & Complete First The diagram below shows a relational schema made up of 6 tables with all primary keys underlined. Please make note of foreign keys (most of them carry the same name as the corresponding primary keys they reference): CUS CODE in INVOICE, INV NUMBER & P CODE in LINE. and V CODE in PRODUCT. The only exception to the naming convention is the EMP_MGR foreign key in EMPLOYEE which references the EMPLOYEE table in a recursive relationship. PRODUCT Spon CUSTOMER P DESCRIPT DISODE PINDATE QUSUNANE P OOH VENDOR PMN LOONE QUS PNAME OUS INITIAL UNE PRICE V NAME CIS AREACODE INVOICE MALISEE P DISCOUNT V CONTACT OUS PHONE INVAN MEER TINEREA VOODE V AREACODE QUS BALANCE Cus CODE P0005 V PHONE INV DATE UNE UNITS V STATE UNE PACE V ORDER EMPLOYEE PP NUM BID TITLE DUP UNAME PUP PNAME DUP INITIAL DUP DOS MP H RE DATE DUP AREACODE BMP PHONE 1 BD M. Click HERE to open and download an SQL script file called Carbarusal to your Desktop (The file may be opened in a browser when you click on it. If it is then right click in the background of this window and select Save As… to save the file to your desktop.). This file contains the SQL commands needed to create the tables above and add data to them in Oracle using SQL Developer. Transfer the file and/or its contents to the SQL Developer worksheet and execute it’s content two (2) times in a row in SQL Developer to see that all errors go away with the second running of the file. Go through the file and make sure you understand every line of code in it before you proceed. You will not need to execute this file again for this lab. The tables and data are stored permanently in your database schema in Oracle. Part 1: Write SQL Queries into lab3.sql script file 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 servergutput on; select sustimestamp from dual; show user; Follow the format given to you in the myCompany.sal file above. 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 file Lab3.sql select * from line; select * from vendor; You will use this file to insert your comments and SOL 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 return 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 Initial Setup – Read & Complete First The diagram below shows a relational schema made up of 6 tables with all primary keys underlined. Please make note of foreign keys (most of them carry the same name as the corresponding primary keys they reference): CUS CODE in INVOICE, INV NUMBER & P CODE in LINE. and V CODE in PRODUCT. The only exception to the naming convention is the EMP_MGR foreign key in EMPLOYEE which references the EMPLOYEE table in a recursive relationship. PRODUCT Spon CUSTOMER P DESCRIPT DISODE PINDATE QUSUNANE P OOH VENDOR PMN LOONE QUS PNAME OUS INITIAL UNE PRICE V NAME CIS AREACODE INVOICE MALISEE P DISCOUNT V CONTACT OUS PHONE INVAN MEER TINEREA VOODE V AREACODE QUS BALANCE Cus CODE P0005 V PHONE INV DATE UNE UNITS V STATE UNE PACE V ORDER EMPLOYEE PP NUM BID TITLE DUP UNAME PUP PNAME DUP INITIAL DUP DOS MP H RE DATE DUP AREACODE BMP PHONE 1 BD M. Click HERE to open and download an SQL script file called Carbarusal to your Desktop (The file may be opened in a browser when you click on it. If it is then right click in the background of this window and select Save As… to save the file to your desktop.). This file contains the SQL commands needed to create the tables above and add data to them in Oracle using SQL Developer. Transfer the file and/or its contents to the SQL Developer worksheet and execute it’s content two (2) times in a row in SQL Developer to see that all errors go away with the second running of the file. Go through the file and make sure you understand every line of code in it before you proceed. You will not need to execute this file again for this lab. The tables and data are stored permanently in your database schema in Oracle.
Part 1: Write SQL Queries into lab3.sql script file 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 servergutput on; select sustimestamp from dual; show user; Follow the format given to you in the myCompany.sal file above. 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 file Lab3.sql select * from line; select * from vendor; You will use this file to insert your comments and SOL 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 return 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…