Using SQL Server with Data Manipulation Language (DML)
The purpose of these instructions is to instruct the user to usethe SQL Developer tool to use Data Manipulation Language (DML) inthe Premier Products Oracle database.
Connecting and Managing the Premier Products Oracle Database
Follow the SQL Server Connection Instructions and run thePremier Products Master Script:
–DROP TABLE “REP” cascade constraints;–DROP TABLE “CUSTOMER” cascade constraints;–DROP TABLE “ORDERS” cascade constraints;–DROP TABLE “PART” cascade constraints;–DROP TABLE “ORDER_LINE” cascade constraints;CREATE TABLE 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) );CREATE TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(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) PRIMARY KEY,ORDER_DATE DATE,CUSTOMER_NUM CHAR(3) );CREATE TABLE PART(PART_NUM CHAR(4) PRIMARY KEY,DESCRIPTION CHAR(15),ON_HAND DECIMAL(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 CUSTOMERVALUES(‘148′,’Al”s Appliance and Sport’,’2837 Greenway’,’Fillmore’,’FL’,’33336′,6550.00,7500.00,’20’);INSERT INTO CUSTOMERVALUES(‘282′,’Brookings Direct’,’3827 Devon’,’Grove’,’FL’,’33321′,431.50,10000.00,’35’);INSERT INTO CUSTOMERVALUES(‘356′,’Ferguson”s’,’382 Wildwood’,’Northfield’,’FL’,’33146′,5785.00,7500.00,’65’);INSERT INTO CUSTOMERVALUES(‘408′,’The Everything Shop’,’1828 Raven’,’Crystal’,’FL’,’33503′,5285.25,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘462′,’Bargains Galore’,’3829 Central’,’Grove’,’FL’,’33321′,3412.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘524′,’Kline”s’,’838 Ridgeland’,’Fillmore’,’FL’,’33336′,12762.00,15000.00,’20’);INSERT INTO CUSTOMERVALUES(‘608′,’Johnson”s Department Store’,’372 Oxford’,’Sheldon’,’FL’,’33553′,2106.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘687′,’Lee”s Sport and Appliance’,’282 Evergreen’,’Altonville’,’FL’,’32543′,2851.00,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘725′,’Deerfield”s Four Seasons’,’282 Columbia’,’Sheldon’,’FL’,’33553′,248.00,7500.00,’35’);INSERT INTO CUSTOMERVALUES(‘842′,’All Season’,’28 Lakeview’,’Grove’,’FL’,’33321′,8221.00,7500.00,’20’);INSERT INTO ORDERSVALUES(‘21608′,’20-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21610′,’20-OCT-2003′,’356’);INSERT INTO ORDERSVALUES(‘21613′,’21-OCT-2003′,’408’);INSERT INTO ORDERSVALUES(‘21614′,’21-OCT-2003′,’282’);INSERT INTO ORDERSVALUES(‘21617′,’23-OCT-2003′,’608’);INSERT INTO ORDERSVALUES(‘21619′,’23-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21623′,’23-OCT-2003′,’608’);INSERT INTO PARTVALUES(‘AT94′,’Iron’,50,’HW’,’3′,24.95,20,’N’);INSERT INTO PARTVALUES(‘BV06′,’Home Gym’,45,’SG’,’2′,794.95,30,’N’);INSERT INTO PARTVALUES(‘CD52′,’Microwave Oven’,32,’AP’,’1′,165.00,11,’N’);INSERT INTO PARTVALUES(‘DL71′,’Cordless Drill’,21,’HW’,’3′,129.95,20,’N’);INSERT INTO PARTVALUES(‘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’);INSERT INTO PARTVALUES(‘KL62′,’Dryer’,12,’AP’,’1′,349.95,7,’N’);INSERT INTO PARTVALUES(‘KT03′,’Dishwasher’,8,’AP’,’3′,595.00,3,’N’);INSERT INTO PARTVALUES(‘KV29′,’Treadmill’,9,’SG’,’2′,1390.00,5,’Y’);INSERT INTO REPVALUES(’20’,’Kaiser’,’Valerie’,’624 Randall’,’Grove’,’FL’,’33321′,20542.50,0.05);INSERT INTO REPVALUES(’35’,’Hull’,’Richard’,’532 Jackson’,’Sheldon’,’FL’,’33553′,39216.00,0.07);INSERT INTO REPVALUES(’65’,’Perez’,’Juan’,’1626 Taylor’,’Fillmore’,’FL’,’33336′,23487.00,0.05);INSERT INTO ORDER_LINEVALUES(‘21608′,’AT94’,11,21.95);INSERT INTO ORDER_LINEVALUES(‘21610′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21610′,’DW11’,1,399.99);INSERT INTO ORDER_LINEVALUES(‘21613′,’KL62’,4,329.95);INSERT INTO ORDER_LINEVALUES(‘21614′,’KT03’,2,595.00);INSERT INTO ORDER_LINEVALUES(‘21617′,’BV06’,2,794.95);INSERT INTO ORDER_LINEVALUES(‘21617′,’CD52’,4,150.00);INSERT INTO ORDER_LINEVALUES(‘21619′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21623′,’KV29’,2,1290.00);
Insert data into a Table when all data is available
Use this SQL statement to know the columns in a table, and otherinformation about the table.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘Customer’;
Use this SQL statement to insert the following customer into theCUSTOMER table using the INSERT statement. Notice that ALL fieldsare available.
INSERT syntax
INSERT 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 Customer Name Address
Balance
Credit Limit Customer’s Rep
111
Ron’s Handball Shoppe
123 Rollout Ave, Court City, UT 85457 21.00
2000.50
Juan Perez
Check your results in the CUSTOMER table.
TIP: Don’t forget to refresh the display if necessary.
REQUIREMENT: Use SQL in the INSERT statement to look up JuanPerez’s rep number. IMPORTANT: Paste the INSERT statement youcreated into the notepad document.
Insert data into a Table when all data is NOT available
Use this information to insert the following customer into theCUSTOMER table using the INSERT statement. If a field is notlisted, do not list it in the INSERT statement or VALUE clause.
Customer Number Customer Name City, State
Credit Limit
113
Go Big Red Lincoln, NE $9000
Check your results in the CUSTOMER table.
IMPORTANT: Skip a line, and Paste the INSERT statement you createdinto the notepad document.
Insert data into a Table when Required data is not available
Use this information to insert the following customer into theCUSTOMER table using the INSERT statement. If a field is notlisted, do not list it in the INSERT statement or VALUE clause.
Customer Number Address
Balance
Credit Limit Customer’s Rep
115
456 Second St., Good Thunder, MN 56037 36.99
5000
Juan Perez
What happens when you try to insert this record? Why? Know thisfor the final exam.
IMPORTANT: Skip a line, and Paste the INSERT statement youcreated into the notepad document. Then, skip a line and give a oneline sentence (maximum) explaining WHY the data was not insertedinto the table.
Beginning a Transaction
Use the BEGIN TRANSACTION statement to start a transaction(s).There may be multiple SQL statements in a transaction.
BEGIN TRANSACTION;
Updating data in a table
Use this information to update the following customer into theCUSTOMER table using the UPDATE statement.
UPDATE syntax
UPDATE table_name
SET column_name = value [, column_name = value]… [ WHEREcondition ];
Author the Update statement to increase The Everything Shop’scredit limit to $99,999. Check your results in the CUSTOMERtable.
IMPORTANT: Skip a line, and Paste the UPDATE statement youcreated into the notepad document.
Committing transactions in a table
Use the COMMIT TRANSACTION statement to commit transactions. Inother words, committing transactions make their changes permanentin the database, erases any save points, and releases any locks onrecords. To make the changes you have already made to the CUSTOMERtable permanent, issue the COMMIT TRANSACTION command now.
COMMIT TRANSACTION;
Check your results in the CUSTOMER table.
IMPORTANT: Skip a line, and Paste the COMMIT TRANSACTIONstatement you created into the notepad document.
Beginning a Transaction
Issue the BEGIN TRANSACTION statement to start the next set oftransaction(s). BEGIN TRANSACTION;
Deleting data in a table
Use this information to delete the following customer from theCUSTOMER table using the DELETE statement.
DELETE syntax
DELETE from table_name [ WHERE condition ];
Author the Delete statement to delete the Brookings Directcustomer from the CUSTOMER table. Check your results in theCUSTOMER table.
IMPORTANT: Skip a line, and Paste the DELETE statement you createdinto the notepad document.
Rolling back transactions in a table
Use the ROLLBACK TRANSACTION statement to “undo” transactions.In other words, rolling back transactions do NOT make their changespermanent in the database. To undo the previous DELETE statementissued to the CUSTOMER table, issue the ROLLBACK TRANSACTIONcommand now.
ROLLBACK TRANSACTION;
Check your results in the CUSTOMER table.
IMPORTANT: Skip a line, and Paste the ROLLBACK statement youcreated into the notepad document. Display the Customer Table
Execute the SQL command below to display some of the Customertable.
SELECT CUSTOMER_NUM, CUSTOMER_NAME, STREET, CREDIT_LIMIT,REP_NUM FROM CUSTOMER;
IMPORTANT: Paste the output from the CUSTOMER table into thesame notepad document.
Submission Instructions
After creating the Notepad document entitle it,LastnameFIDML.txt, e.g.
Expert Answer
Answer to Using SQL Server with Data Manipulation Language (DML) The purpose of these instructions is to instruct the user to use …