1 Laboratory 5 Getting Started with Oracle SQL*PLUS™ References Lecture Notes: Topic 6 - Elmasri and Navathe, 2014: Chapter 4 In this laboratory session you will begin using Oracle SQL*PLUS to create tables and manipulate data. The current version of Oracle’s SQL is 12c. Helpful Tips: 1. Create a table: CREATE TABLE tableName( AttributeName1 type, AttributeName2 type, … AttributeName5 type, … PRIMARY KEY(attributeName1), FOREIGN KEY(attributeName5) REFERENCES tableName(matching-attributeName-in-tableName) ); 2. Insert a record: INSERT INTO tableName (attributeName1, attributeName2, …, attributeNameN) VALUES (value1, value2, …, valueN); 3. Drop column from a table: ALTER TABLE tableName DROP (attributeName); 4. Add attributes to a table: ALTER TABLE tableName ADD (attributeName1 type, attributeName2 type); 5. Change attribute type in a table: ALTER TABLE tableName MODIFY attributeName type; 6. Update attribute’s value: UPDATE tableName SET attributeName = newValue WHERE attributeName = searchValue; 2 7. Delete record from a table: DELETE FROM tableName WHERE attributeName = searchValue; 8. Drop a table: DROP TABLE tableName; 9. Retrieve the table from the recyclebin: FLASHBACK TABLE tableName TO BEFORE DROP; 10. Drop a table without considering the integrity constraint: DROP TABLE tableName CASCADE CONSTRAINTS; 11. Display the tables in recyclebin: SHOW RECYCLEBIN; 12. Drop a table from recyclebin PURGE TABLE tableName; 13. Drop all tables from recyclebin: PURGE RECYCLEBIN; Discussion Questions 1. What is SQL? 2. What is Oracle? 3. What is SQL*PLUS? 3 Logging on to Oracle SQL*Plus You can access SQL*Plus by following the file path: Start → Programs → Oracle → Oracle-Client 12c → Application Development → SQL Plus In the SQL Plus shell window, type the following: User Name: @dbf Password: change When you login for the first time, SQL*PLUS will ask you to provide a new password. Type in the new password. PLEASE REMEMBER YOUR NEW PASSWORD. Note: Should you want to change your password at another time type password at the SQL prompt. 4 Exercise 1 – Creating Tables, Adding Constraints and Inserting Data 1. At the SQL prompt type the following statement and write down your observations. (Note: your observation should not be a number, if you get a number can you tell what it is?) SELECT * FROM TAB; TAB is a pre-defined table that stores, amongst other things, information regarding the tables in your database. 2. Open Notepad++ and save the file as .sql file format, then type the following SQL statement in the file and copy it across to the Oracle SQL window. The Oracle text editor is not particularly user-friendly, so typing the SQL statements in Notepad++ allows them to be easily edited and saved independently of the database. CREATE TABLE Venues (venueID VARCHAR2(6) NOT NULL, venueName VARCHAR2(50), venueAddress VARCHAR2(70), venueCapacity NUMBER(4) NOT NULL, costPerDay NUMBER(7,2), venueManager VARCHAR2(50), managerPhoneNo VARCHAR2(15), PRIMARY KEY (venueID)); --YOU DO NOT NEED TO TYPE THIS --Things to note: The primary key is the attribute that --uniquely identifies each record --NOT NULL stipulates that this attribute MUST have a value --when a record is inserted into the table --As you may have guessed, the double dash -- signifies --comments 3. At the SQL prompt type the following statement and write down your observations. SELECT * FROM TAB; 4. At the SQL prompt type the following statement and write down your observations. DESCRIBE Venues; 5 5. Type in Notepad++ the following SQL statement and copy it across to the Oracle SQL window. --NOTE: The format of Sean O'Riley below is NOT a typo! --First try without the extra quote and see what happens. INSERT INTO Venues (venueID, venueName, venueAddress, venueCapacity, costPerDay, venueManager, managerPhoneNo) VALUES ('V00001', 'Town Hall', '15 High St, Local Town', 800, 650.00, 'Sean O''Riley', '9333 2498'); Once you have successfully added the above row (Oracle will tell you when you are successful – "1 row created") try inserting the next record. INSERT INTO Venues (venueID, venueName, venueAddress, venueCapacity, costPerDay, venueManager, managerPhoneNo) VALUES ('V00001', 'Lyndhurst Street Community Centre', '12 Lyndhurst St, Local Town', 170, 310.00, 'Kylie Ong', '9333 1212'); What happens? Why? 6. Note that if you are entering values for all the attributes in a table, as in 5 above, you do not need to include the attribute names. Type the following SQL statement in Notepad++ and copy it across to the Oracle SQL window. INSERT INTO Venues VALUES ('V00003', 'Local Town Community Theatre', '146 Main Rd, Local Town', 650, 1500.00, 'James McPhee', '9333 8569'); 7. If you are only entering partial information you MUST specify the attributes and ensure that all NOT NULL fields have a value. Type the following SQL statement in Notepad++ and copy it across to the Oracle SQL window. INSERT INTO Venues (venueID, venueName, venueAddress, costPerDay, venueManager) VALUES ('V00004', 'Glass Street Scout Hall', '6 Grange Rd, Local Town', 20.00); Any problems? Fix the problem and try again. 6 8. Please create a table call Event in the database, table Event includes the following attributes, and the primary key is eventId, foreign key is venueId, please define these attributes appropriately. You can first type the code in Notepad++ and copy it across to the Oracle SQL window. Events(eventId, eventName, eventDescription, venueCapacityRequired, cateringRequired, venueId ) 9. How do you ensure cateringRequired in the table above is only given the values 'y' or 'n'? 10. What is the SQL statement to display all the records in the Venue table? Try it and see. 11. Please insert one record into event table by yourself. You can type the code in Notepad++ and copy it across to the Oracle SQL window. 12. Type in Notepad++ the following SQL statement and copy it across to the Oracle SQL window. INSERT INTO Events (eventID, eventName, eventDescription, venueCapacityRequired, cateringRequired, venueID) VALUES ('E00002', 'Little Athletic''s Trivia Night', 'Fundraiser for the local little athletics club', 250, 'n', 'V00009'); What happens? Think carefully about the significance of referential integrity. 7 Exercise 2 – Altering Tables and Modifying Data 1. Please drop attribute venueManager from table Venues without recreating the table. 2. At the SQL prompt type the following statements and write down your observations. DESCRIBE Venues; SELECT * FROM Venues; 3. Please add three attributes: streetAddress, addressCity, addressPostcode into table Venues without recreating the table. 4. At the SQL prompt type the following statements and write down your observations. Take particular note of the order in which the attributes are listed. What impact will this have on future insert statements? DESCRIBE Venues; 5. Please change the type of attribute addressCity to VARCHAR2(200) without recreating the table. 6. At the SQL prompt type the following statements and write down your observations. DESCRIBE Venues; 7. At the SQL prompt type the following statements and write down your observations. ALTER TABLE Venues MODIFY venueName VARCHAR2(5); DESCRIBE Venues; 8. Write the SQL statement to update the street address of the venues with id 'V00001' to '12 Lyndhurst St'. Display the data stored in the Venues table. 8 9. Write the SQL statement to delete the venue with venueId 'V00002' from the Venues table. Display the data stored in the Venues table. 10. Write the SQL statement to delete the venue with venueId 'V00001' from the Venues table. What happens? Display the data stored in the Venue table. Display the data stored in the Venues table. 9 Exercise 3 – Deleting Tables 1. At the SQL prompt type the following statement and write down your observations. DROP TABLE Venues; What happens? 2. Correct the code to drop the table Venues that has a referential integrity constraint with another table? 3. At the SQL prompt type the following statements and write down your observations. SELECT * FROM TAB; 4. When you issue the drop table command the table is placed in the recycle bin, thereby allowing you to retrieve it if necessary. If you want to retrieve the table , what command you need to type? 5. When you selected the tables in the database after dropping the Venues table you will have noticed that the representation of the Venues table does not make it easy to identify. What command you need to type to identify Venues table in recyclebin? 6. To delete a table from the recycle bin you need to "purge" it. Please type code to delete the table Venues from recycle bin. After that, at the SQL prompt type the following statements and write down your observations. 7. Please type the code to delete all the tables in your recycle bin. 10 A word on style… The commands in SQL*PLUS are not case sensitive. The mixed use of upper- and lower-case in this lab is for readability only. HOWEVER, it is suggested you follow a similar format when writing your SQL statements. Formatting Columns… You will become used to reading the format of the SQL*PLUS output. However, you can format the display of column headings and column data (though you will not be required to). Try typing the following at the SQL prompt: COLUMN eventID FORMAT a8 HEADING 'Event ID' WORD_WRAPPED COLUMN eventName FORMAT a50 heading 'Event Name' WORD_WRAPPED COLUMN eventDescription FORMAT a60 heading 'Event|Description' WORD_WRAPPED COLUMN venueCapacityRequired FORMAT 9999 heading 'Venue|Capacity|Required' JUSTIFY left Using the above as a guide, format the display for the remaining attributes of Event. For more practice try the following from your text book (Elmasri and Navathe, 2014): Q4.10, Q4.11, Q4.12, Q4.13 and Q4.14 (pages 107 – 109)