LAB ASSIGNMENT - Laurentian



LAB ASSIGNMENT - INTRODUCTION TO SQL

Purpose:

To introduce students to structured query language (SQL), the assignment consists of starting up Oracle SQL, learning how to create a table, enter data, change data in the table, delete data, and drop the table.

To continue the introduction to structured query language (SQL), the assignment explores variations on the select command, logical tests, and null combining of tables, subqueries, and views.

This is only a practice exercise and not to be submitted.

Requirements:

1. To start up SQLPLUS, click on the Start menu, then the Programs menu. One of the choices is ORACLE for Windows NT. Click on that menu item, then on the menu that comes up click on the choice SQLPLUS 8.0 (Near the bottom). You will then be prompted with a dialog box that asks for your user name, password and the host. We are running ORACLE on a client-server architecture, where the clients are on the Windows NT and the server is on the UNIX (SUN server) you need to enter the user name as your student number, password as your social security number, as shown below. You need to provide a host entry as “oracledb”. If you have correctly entered the user name and password, Oracle will respond with the word “Connected to” followed by 3 or four lines of information, then the prompt “SQL>”. This indicates you have successfully logged on.

| | |

|User Name |Student# |

| | |

|Password |SIN |

| | |

|Host |oracledb |

2. Since we are using ORACLE installed on the server, any objects such as tables, views, etc. that you create, and their associated data, will be available on the server. Therefore, you can use any machine in the lab for working on Oracle labs.

3. Spooling SQL*PLUS Commands and output to a File

a. Start SQL*PLUS, click File on the menu bar, point to spool, then click Spool File. Select the name of a file and click save.

b. Run the query from SQL*PLUS.

c. Click File on the menu bar, point to Spool, then click Spool Off. The spooled input and output are not written to the file until the spooler is turned off.

d. Open the file you saved in Notepad and check that it contains the commands of the query and the results.

4. One of the immediate tasks we need to do before you start is to identify if there are any unnecessary objects on your workspace that may be taking up disk space. Therefore, once you have succeeded in signing on, use the following command to list the objects in your ORACLE workspace:

SELECT OBJECT_NAME, OBJECT_TYPE

FROM USER_OBJECTS;

This command will produce a list of the objects (e.g. tables, views, etc.) that are currently in your workspace.

(Note: It is possible that there are no objects currently on your workspace. In that case, the above select statement will indicate no rows returned.)

5. The following table represents information on sales representatives for Premiere Products, and contains the following data fields for sales representatives:

sales representative’s number (SLSRNUMB),

sales representative’s name (SLSRNAME),

sales representative’s address (SLSADDR),

total commission paid to the sales representative (TOTCOMM), and

sales representative’s commission rate (COMMRATE).

Sales Representative

|SLSRNUMB |SLSRNAME |SLSRADDR |TOTCOMM |COMMRATE |

|3 |MARY JONES |123 MAIN, GRANT, MI |2150.00 |0.05 |

|6 |WILLIAM SMITH |102 RAYMOND, ADA, MI |4912.00 |0.07 |

|12 |SAM BROWN |419 HARPER, LANSING, MI |2150.00 |0.05 |

6. Using a text editor such as notepad, create the following ORACLE command file to create the table. Save the file as SREP.SQL on your diskette. (Note: Be careful when saving to indicate the “all files” file type, and put double quotes around the file name so that Windows will not put an additional .txt extension on your file name.)

create table SALESREP (

slsrnumb number,

slsrname varchar2(25) not null,

slsraddr varchar2(25) not null,

totcomm number(6,2),

commrate number(2,2));

7. Return to ORACLE, and when you get the SQL> prompt, run the ORACLE script file created in 4 above by typing the following:

start a:\srep.sql;

Note: if you are running the file from another location then specify the path where the file is stored

This will create a table, give you a message to that effect, and return to the SQL> prompt. As you type a statement and press enter, a line number will appear at the left. When you have typed in all the command and have pressed enter, a blank line number will appear. simply type another enter and the SQL> prompt will reappear. To run the command, type run or / and press enter.

To see what you have created, i.e. the structure of the table, type the following command:

describe salesrep;

8. You have now created your first ORACLE table. We now need to enter some data into that table. To do so, type the following command at the SQL> prompt:

insert into salesrep values (3,'Mary Jones','123 Main, Grant, MI',2150.00,0.05);

9. Then, enter more data into the table by typing the following command. (You can create a *.sql (* is for any file name) file in Notepad and start it as described in 2 and 3 above.) Don’t be afraid to use edit copy - edit paste to lighten the typing load. (Yes, the row for Mary Jones was meant to be repeated!)

insert into salesrep values (3,'Mary Jones','123 Main, Grant, MI',2150.00,0.05);

insert into salesrep values (6,'William Smith','102 Raymond, Ada, MI',4912.00,0.07);

insert into salesrep values (12,'Sam Brown','419 Harper, Lansing, MI',2150.00,0.05);

10. You have now entered data into your table. To see the data, type the following command. Again, as for any command, type it in, press enter, and at the blank line press enter again to get the SQL> prompt. Then type run or / to execute the command

select slsrnumb,slsrname,slsraddr,totcomm,commrate from salesrep;

11. Now, type the following command and compare the results from this command with that in the above command (8).

select * from salesrep;

12. To delete the two rows for Mary Jones, type in and execute (by pressing enter) the following command:

delete from salesrep where slsrnumb = 3;

13. Again look at the date to see if your command was successful by repeating the command used in 9 above.

14. Assume that the commission rate for Sam Brown was entered incorrectly, and should be changed to 0.06. Use the following command to update the commission rate field for Sam Brown:

update salesrep set commrate = 0.06 where slsrnumb = 12;

15. You have now created a table, inserted data, updated data within a field, and deleted a record or row on the table. These are some of the fundamental operations to be done with a table, and we will be practising these in detail with the various variations over the next few weeks.

16. The only operation that might be of further practical use is that of removing, or dropping a table. To illustrate, create a table with the following command:

create table TEMP (

order_no number,

order_description varchar2(25) not null

);

Then, use the describe command to make sure it is properly created.

Finally, execute the following command to drop the table from your workspace:

drop table TEMP;

You have now dropped the temporary table you have just created, and it no longer exists in your workspace.

17. Practise your newfound skills by creating and loading the following tables. Please help me in assisting you to debug your statements by using the table names and column names exactly as shown below. Give the field definitions sizes that would appear reasonable based on the data below.

************************************************************************

Customer

|CUSTNUMB |CUSTNAME |ADDRESS |BALANCE |CREDLIM |SLSRNUMB |

|124 |SALLY ADAMS |481 OAK, LANSING, MI |418.75 |500 |3 |

|256 |ANN SAMUELS |215 PETE, GRANT, MI |10.75 |800 |6 |

|311 |DON CHARLES |48 COLLEGE, IRA, MI |200.10 |300 |12 |

|315 |TOM DANIELS |914 CHERRY, KENT, MI |320.75 |300 |6 |

|405 |AL WILLIAMS |519 WATSON, GRANT, MI |201.75 |800 |12 |

|412 |SALLY ADAMS |16 ELM, LANSING, MI |908.75 |1000 |3 |

|522 |MARY NELSON |108 PINE, ADA, MI |49.50 |800 |12 |

|567 |JOE BAKER |808 RIDGE, HARPER, MI |201.20 |300 |6 |

|587 |JUDY ROBERTS |512 PINE, ADA, MI |57.75 |500 |6 |

|622 |DAN MARTIN |419 CHIP, GRANT, MI |575.50 |500 |3 |

Order Detail

|ORDNUMB |PARTNUMB |NUMBORD |QUOTPRIC |

|12489 |AX12 |11 |14.95 |

|12491 |BT04 |1 |402.99 |

|12491 |BZ66 |1 |311.95 |

|12494 |CB03 |4 |175.00 |

|12495 |CX11 |2 |57.95 |

|12498 |AZ52 |2 |22.95 |

|12498 |BA74 |4 |4.95 |

|12500 |BT04 |1 |402.99 |

|12504 |CZ81 |2 |108.99 |

Parts

|PARTNUMB |PARTDESC |UNONHAND |ITEMCLSS |WREHSNM |UNITPRCE |

|AX12 |IRON |104 |HW |3 |17.95 |

|AZ52 |SKATES |20 |SG |2 |24.95 |

|BA74 |BASEBALL |40 |SG |1 |4.95 |

|BH22 |TOASTER |95 |HW |3 |34.95 |

|BT04 |STOVE |11 |AP |2 |402.99 |

|BZ66 |WASHER |52 |AP |3 |311.95 |

|CA14 |SKILLET |2 |HW |3 |19.95 |

|CB03 |BIKE |44 |SG |1 |187.5 |

|CX11 |MIXER |112 |HW |3 |57.95 |

|CZ81 |WEIGHTS |208 |SG |2 |108.99 |

Orders

|ORDNUMB |CUSTNO |ORDDTE |

|12489 |124 |01-MAR-98 |

|12491 |311 |10-MAR-98 |

|12494 |315 |31-MAR-98 |

|12495 |256 |15-APR-98 |

|12498 |522 |16-APR-98 |

|12500 |124 |21-APR-98 |

|12504 |522 |05-MAY-98 |

18. The following sections are meant to illustrate the select, from, where, and order by clauses. Using the customer table you have just created, type in the following commands, and in each case look at the output from each and compare the results:

select custnumb, custname, address, balance from customer where credlim = 500;

select custnumb, custname, address, balance from customer where credlim = 500 order by custnumb;

select custnumb, custname, address, balance from customer where credlim = 500 order by custname;

select custnumb, custname, address, balance from customer where credlim = 500 order by custnumb, custname;

select custnumb, custname, address, balance from customer where credlim = 500 order by custnumb desc, custname;

19. The following commands illustrate logical tests. Type in the following commands, and in each case look at the output from each and compare the results:

select custnumb, custname, address, balance from customer where balance > 300;

select custnumb, custname, address, balance from customer where custname > ‘D’;

select custnumb, custname, address, balance from customer where custname like ‘ADAM%’;

select custnumb, custname, address, balance from customer where custname like ‘_ _N%’;

select custnumb, custname, address, balance from customer where custname like ‘%A%A%’;

select custnumb, custname, address, balance from customer where custname like ‘%A%I%’;

20. The following commands illustrate null and not null clauses. Using your new-found knowledge, create the following table called MARKS:

Student varchar2(15)

Test1 number

Test2 number

Test3 number

Enter the following data into the table:

|Student |Test #1 |Test #2 |Test #3 |

|Mike Harris |75 |65 |70 |

|Jean Chretien |80 | |60 |

|Lucien Bouchard |70 |65 |68 |

Enter the following commands, and compare the results:

select student, test1, test2, test3 from marks where test2 is null;

select student, test1, test2, test3 from marks where test2 is not null;

21. The following commands illustrate the logical test of values. Using the part table, enter the following commands, and compare the results:

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss not in (‘HW’, ‘SG’);

select partnumb, partdesc, unonhand, wrehsnm, unitprce where itemclss in (‘HW’, ‘SG’);

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where unitprce between 100.00 and 400.00;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss = ‘HW’ and unitprce > 30.00;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss = ‘HW’ or unitprce > 30.00;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss = ‘HW’ and intprce between 19.00 and 30.00;

22. The following commands illustrate the combining of commands using and or or. Using the part table, enter the following commands, and compare the results:

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss = ‘HW’ or itemclss = ‘SG’ and unitprce > 50.00;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where itemclss = ‘HW’ and unitprce > 50.00 or itemclss = ‘SG’;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where unitprce > 50.00 and itemclss = ‘HW’ or itemclss = ‘SG’;

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where unitprce > 50.00 and (itemclss = ‘HW’ or itemclss = ‘SG’);

select partnumb, partdesc, unonhand, wrehsnm, unitprce from parts where (itemclss = ‘HW’ or itemclss = ‘SG’) and unitprce > 50.00;

23. The following commands illustrate the use of subqueries. Using the customer table, enter the following commands and compare the results.

select custnumb from customer where custname = ‘MARY NELSON;

select custnumb from customer where slsrnumb = 12;

select custnumb from customer where slsrnumb = (select custnumb from customer where custname = ‘MARY NELSON’);

select * from customer where custname = (select custname from customer where slsrnumb = 3);

24. The following commands illustrate the combining of tables. Use the order and order detail tables to do the following command:

select orders.ordnumb, orders.orddte, order_detail.partnumb, order_detail.numbord, order_detail.quotpric from orders, order_detail

select orders.ordnumb, orders.orddte, order_detail.partnumb, order_detail.numbord, order_detail.quotpric from orders, order_detail where orders.ordnumb > 12500 order by orders.ordnumb

25. The following commands illustrate the creation of a view. Use the part table to do the following command:

create view hardware as select partnumb, partdesc, unonhand, wrehsnm, unitprice from part where itemclss = ‘HW’;

select partnumb, partdesc, unonhand from hardware;

Congratulations!! You have just run your first ORACLE programs. The rest is easy?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download