Using SQL Server with Data Manipulation Language (DML) Thepurpose of these instructions is to instruct the user to use theSQL Developer tool to use Data Manipulation Language (DML) in thePremier Products Oracle database. Connecting and Managing thePremier Products Oracle Database Follow the SQL Server ConnectionInstructions and run the Premier Products Master Script (found onD2L). Insert data into a Table when all data is available Use thisSQL statement to know the columns in a table, and other informationabout the table. SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERETABLE_NAME = ‘Customer’; Use this SQL statement to insert thefollowing customer into the CUSTOMER table using the INSERTstatement. Notice that ALL fields are available. INSERT syntaxINSERT INTO table_name (list_of_columns) VALUES (list_of_values);HINT: You may have to “look up” some values to ensure the INSERTworks correctly Customer Number 111 Customer Name Ron’s HandballShoppe Address 123 Rollout Ave, Court City, UT 85457 Balance 21.00Credit Limit 2000.50 Customer’s Rep Juan Perez Check your resultsin the CUSTOMER table. TIP: Don’t forget to refresh the display ifnecessary. REQUIREMENT: Use SQL in the INSERT statement to look upJuan Perez’s rep number. IMPORTANT: Paste the INSERT statement youcreated into the notepad document. Insert data into a Table whenall data is NOT available Use this information to insert thefollowing customer into the CUSTOMER table using the INSERTstatement. If a field is not listed, do not list it in the INSERTstatement or VALUE clause. Customer Number 113 Customer Name Go BigRed City, State Lincoln, NE Credit Limit $9000 Check your resultsin the CUSTOMER table. IMPORTANT: Skip a line, and Paste the INSERTstatement you created into the notepad document. Insert data into aTable when Required data is not available Use this information toinsert the following customer into the CUSTOMER table using theINSERT statement. If a field is not listed, do not list it in theINSERT statement or VALUE clause. Customer Number 115 Address 456Second St., Good Thunder, MN 56037 Balance 36.99 Credit Limit 5000Customer’s Rep Juan Perez What happens when you try to insert thisrecord? Why? Know this for the final exam. IMPORTANT: Skip a line,and Paste the INSERT statement you created into the notepaddocument. Then, skip a line and give a one line sentence (maximum)explaining WHY the data was not inserted into the table. Beginninga Transaction Use the BEGIN TRANSACTION statement to start atransaction(s). There may be multiple SQL statements in atransaction. BEGIN TRANSACTION; –DROP TABLE “REP” cascadeconstraints; –DROP TABLE “CUSTOMER” cascade constraints; –DROPTABLE “ORDERS” cascade constraints; –DROP TABLE “PART” cascadeconstraints; –DROP TABLE “ORDER_LINE” cascade constraints; CREATETABLE REP (REP_NUM CHAR(2) PRIMARY KEY, LAST_NAME CHAR(15),FIRST_NAME CHAR(15), STREET CHAR(15), CITY CHAR(15), STATE CHAR(2),ZIP CHAR(5), COMMISSION DECIMAL(7,2), RATE DECIMAL(3,2) ); CREATETABLE CUSTOMER (CUSTOMER_NUM CHAR(3) PRIMARY KEY, CUSTOMER_NAMECHAR(35) NOT NULL, STREET CHAR(15), CITY CHAR(15), STATE CHAR(2),ZIP CHAR(5), BALANCE DECIMAL(8,2), CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) ); CREATE TABLE ORDERS (ORDER_NUM CHAR(5) PRIMARYKEY, ORDER_DATE DATE, CUSTOMER_NUM CHAR(3) ); CREATE TABLE PART(PART_NUM CHAR(4) PRIMARY KEY, DESCRIPTION CHAR(15), ON_HANDDECIMAL(4,0), CLASS CHAR(2), WAREHOUSE CHAR(1), PRICE DECIMAL(6,2),MINIMUM DECIMAL(4,0), REORDER CHAR(1) ); CREATE TABLE ORDER_LINE(ORDER_NUM CHAR(5), PART_NUM CHAR(4), NUM_ORDERED DECIMAL(3,0),QUOTED_PRICE DECIMAL(6,2), PRIMARY KEY (ORDER_NUM, PART_NUM) );INSERT INTO CUSTOMER VALUES (‘148′,’Al”s Appliance andSport’,’2837Greenway’,’Fillmore’,’FL’,’33336′,6550.00,7500.00,’20’); INSERTINTO CUSTOMER VALUES (‘282′,’Brookings Direct’,’3827Devon’,’Grove’,’FL’,’33321′,431.50,10000.00,’35’); INSERT INTOCUSTOMER VALUES (‘356′,’Ferguson”s’,’382Wildwood’,’Northfield’,’FL’,’33146′,5785.00,7500.00,’65’); INSERTINTO CUSTOMER VALUES (‘408′,’The Everything Shop’,’1828Raven’,’Crystal’,’FL’,’33503′,5285.25,5000.00,’35’); INSERT INTOCUSTOMER VALUES (‘462′,’Bargains Galore’,’3829Central’,’Grove’,’FL’,’33321′,3412.00,10000.00,’65’); INSERT INTOCUSTOMER VALUES (‘524′,’Kline”s’,’838Ridgeland’,’Fillmore’,’FL’,’33336′,12762.00,15000.00,’20’); INSERTINTO CUSTOMER VALUES (‘608′,’Johnson”s Department Store’,’372Oxford’,’Sheldon’,’FL’,’33553′,2106.00,10000.00,’65’); INSERT INTOCUSTOMER VALUES (‘687′,’Lee”s Sport and Appliance’,’282Evergreen’,’Altonville’,’FL’,’32543′,2851.00,5000.00,’35’); INSERTINTO CUSTOMER VALUES (‘725′,’Deerfield”s Four Seasons’,’282Columbia’,’Sheldon’,’FL’,’33553′,248.00,7500.00,’35’); INSERT INTOCUSTOMER VALUES (‘842′,’All Season’,’28Lakeview’,’Grove’,’FL’,’33321′,8221.00,7500.00,’20’); INSERT INTOORDERS VALUES (‘21608′,’20-OCT-2003′,’148’); INSERT INTO ORDERSVALUES (‘21610′,’20-OCT-2003′,’356’); INSERT INTO ORDERS VALUES(‘21613′,’21-OCT-2003′,’408’); INSERT INTO ORDERS VALUES(‘21614′,’21-OCT-2003′,’282’); INSERT INTO ORDERS VALUES(‘21617′,’23-OCT-2003′,’608’); INSERT INTO ORDERS VALUES(‘21619′,’23-OCT-2003′,’148’); INSERT INTO ORDERS VALUES(‘21623′,’23-OCT-2003′,’608’); INSERT INTO PART VALUES(‘AT94′,’Iron’,50,’HW’,’3′,24.95,20,’N’); INSERT INTO PART VALUES(‘BV06′,’Home Gym’,45,’SG’,’2′,794.95,30,’N’); INSERT INTO PARTVALUES (‘CD52′,’Microwave Oven’,32,’AP’,’1′,165.00,11,’N’); INSERTINTO PART VALUES (‘DL71′,’CordlessDrill’,21,’HW’,’3′,129.95,20,’N’); INSERT INTO PART VALUES(‘DR93′,’Gas Range’,8,’AP’,’2′,495.00,4,’N’); INSERT INTO PARTVALUES (‘DW11′,’Washer’,12,’AP’,’3′,399.99,8,’N’); INSERT INTO PARTVALUES (‘FD21′,’Stand Mixer’,22,’HW’,’3′,159.95,22,’Y’); INSERTINTO PART VALUES (‘KL62′,’Dryer’,12,’AP’,’1′,349.95,7,’N’); INSERTINTO PART VALUES (‘KT03′,’Dishwasher’,8,’AP’,’3′,595.00,3,’N’);INSERT INTO PART VALUES(‘KV29′,’Treadmill’,9,’SG’,’2′,1390.00,5,’Y’); INSERT INTO REPVALUES (’20’,’Kaiser’,’Valerie’,’624Randall’,’Grove’,’FL’,’33321′,20542.50,0.05); INSERT INTO REPVALUES (’35’,’Hull’,’Richard’,’532Jackson’,’Sheldon’,’FL’,’33553′,39216.00,0.07); INSERT INTO REPVALUES (’65’,’Perez’,’Juan’,’1626Taylor’,’Fillmore’,’FL’,’33336′,23487.00,0.05); INSERT INTOORDER_LINE VALUES (‘21608′,’AT94’,11,21.95); INSERT INTO ORDER_LINEVALUES (‘21610′,’DR93’,1,495.00); INSERT INTO ORDER_LINE VALUES(‘21610′,’DW11’,1,399.99); INSERT INTO ORDER_LINE VALUES(‘21613′,’KL62’,4,329.95); INSERT INTO ORDER_LINE VALUES(‘21614′,’KT03’,2,595.00); INSERT INTO ORDER_LINE VALUES(‘21617′,’BV06’,2,794.95); INSERT INTO ORDER_LINE VALUES(‘21617′,’CD52’,4,150.00); INSERT INTO ORDER_LINE VALUES(‘21619′,’DR93’,1,495.00); INSERT INTO ORDER_LINE VALUES(‘21623′,’KV29’,2,1290.00);
Expert Answer
Answer to Using SQL Server with Data Manipulation Language (DML) The purpose of these instructions is to instruct the user to use …