DATABASE MANEGMENT SYSTEMS

Lab: Using SQL Developer (Individual Assignment) Objective: Creating/populating tables using SQL commands in SQL Developer Learning how to add referential integrity and other constraints. Lab Requirements: You will create and save a SQL script which must be uploaded to Assignments link by the deadline. Running the lab steps below creates two tables in your Oracle account. By the end of this lab and to receive a grade, you will have created tables with data in your account. Run the SQL statement when instructed. Please read carefully: It is necessary to read ahead and understand the context of what you are doing to avoid unnecessary mistakes. Start Here: Now that installed SQL Developer on your laptop, run and log into the application. You may have to double-click the cs605connection that you created during installation and may enter your password. This might be a good time to watch the Overview Video on the Start Page. A SQL worksheet is now open. If a worksheet does not open by default, click Tools Worksheet and OK to select connection. The top of the worksheet is used to enter/edit SQL commands. As shown: This is usually done once, click Tools Preferences, then click + sign next to Code Editor and check the Line Gutter property to show script Line Numbers. Click Fonts under Code Editor to change Font Size to your liking, 24 font size is the max. 1. Building a table using CREATE TABLE command: The general syntax to the CREATE TABLE command is described in Chapter 6. Basically, a table definition consists of a comma-separated list specifying the name and the type of each field, as well as, default values and constraints on the fields. Among the constraints, the PRIMARY KEY constraint is mandatory, and the rest are optional. As a running example in this lab we will create the following two tables: Emp (emp_no, first_name, last_name, address, city,state, date_hired, hourly_wage) Hours (emp_no, week_ending, hours_worked) Required: Copy and paste the SQL below (in red and gray) into the worksheet then run the full script using Run Script button or hit F5. CREATE TABLE emp (emp_no varchar2(3), first_name varchar(15) NOT NULL, last_name varchar2(20) NOT NULL, address varchar2(30), city varchar2(15), state varchar2(2), date_hired date, hourly_wage number(5,2) NOT NULL, CONSTRAINT constr_emp_PK PRIMARY KEY(emp_no)); Refreshing the connection will show the new table EMP. Sometimes it takes a little time to show the table that you created. Refresh and be patient! When commands are run successfully without errors, SQL Developer does not show a message confirming successful run, it only shows how long it took to run the commands as follows: Note the following in the above table definition: - NOT NULL declares that the field value cannot be NULL, hence the user must enter a value - The PRIMARY KEY constraint in the last line: defines field emp_no as the primary key for the table. Do not delete the SQL statement copied into the worksheet; continue adding more statements as needed. Now is a good time to save the script to a known location on your hard-drive: Go to File Save and name the file Oracle_Lab_YourFullName.sql Notice that a Script Output tab shows in the bottom of screen the results of the running of the command You may execute statements separately using Execute Statement by highlighting the statement, then hit F9. Each SQL statement ends with a semicolon. Problem: if you execute a statement that already created a table or a constraint, you will receive an error that the object is already created. Begin to read error messages carefully to learn about errors. 2. Viewing the table field description Using DESCRIBE command: Syntax: DESCRIBE table-name; The DESCRIBE statement shows a tables column names, data type and null status. It does not show table constraints. Back to SQL editor, add a blank line (hit enter once as we do in Word) Required: Type: DESCRIBE EMP; To run a single SQL statement, NOT the whole script, clicks inside the statement or highlight it, and click Execute Statement or F9 Look in the Script Output in bottom of worksheet 3. Viewing table data using SELECT command: Syntax: SELECT * FROM table-name; * means that we are selecting ALL columns in a table. Required: Go to a new line in the editor and type: SELECT * FROM EMP; At this point, no data will display because the table is empty, see below caption 4. Inserting Data using INSERT command Syntax: there are two types of INSERT: the first is used when the inserted row specifies values of all fields in the same order as fields are listed in CREATE TABLE: INSERT INTO table-name VALUES (col1-val, col2-val, col3-val, , coln-val); Second is used when some attributes values are omitted. The field names should be provided, specifying the fields that are being filled. INSERT INTO table-name (col1-name, col2-name, ,coln-name) VALUES (col1-val, col2-val, , coln-val); Required: Add the following two lines to your script, highlight both statement and run: INSERT INTO EMP VALUES ('A01', 'Joan', 'Garfield', '437 Elm St', 'Hoboken', 'NJ', To_Date('02-APR-1994'), 11); Commit; If you have multiple INSERT statements, use commit; ONCE after all INSERT statements. The result in the Script Output: Res To look at the data in the table, click EMP table once under Tables tree on left, go to the Data tab, you may have to click refresh Required: Use the INSERT statement to insert the following records. Go back to the SQL sheet and insert the following records. A02, Alice, Walter, 326B 31 St, Far Rockaway, RI, 12-mar-1985, 9 A03, Joseph, Miller, 2036 Park Ave, New York, NY, 22-MAR-1989, 10.25 Commit; Dont forget the commit; statement which is not shown below. You can highlight the Insert statements plus commit; in the editor and click Execute Statement. Dont forget that Execute Statement or F9 only runs the highlighted statement otherwise F5 will execute the whole script Required: After executing these insert statements run SELECT * FROM EMP; to see all three records SAVE THE SCRIPT BEFORE YOU LOSE YOUR WORK! File Save. 5. Constraints Review: a. Entity integrity -- Primary key: Entity integrity means no primary key value can be NULL. Primary key value should be unique. Automatically enforced by PRIMARY KEY constraint. b. Domain integrity use CHECK which ensures that values in each of the columns of a table must come from a certain domain. It is enforced via data types/length of columns. The CHECK operator is used to check that every inserted value belongs to an appropriate range. Syntax: CHECK is used within CREATE TABLE by adding: CONSTRAINT constraint-name CHECK (test) where test is the field that you want to check its values Here is an example: If you wanted to create the EMP table with the constraints, the code would have looked as follows: CREATE TABLE EMP (emp_no varchar(3), . . . state varchar(2) , . . . CONSTRAINT constr_emp_PK PRIMARY KEY(emp_no), CONSTRAINT constr_state_CH CHECK (state in (NY, RI, NJ)), CONSTRAINT constr_hourly_CH CHECK (hourly_wage >= 6.25 AND hourly_wage <= 50) . . . ); A constraint can be added to the table after the table is created, for example: ALTER TABLE table-name ADD CONSTRAINT constr-name CHECK (test); For MAC users (and some PC users), in the following step, if you receive an error after you run the alter statement the first time, manually re-type the single quotes around the three states: Required: To alter a table by adding a constraint, add the following statement to the script and execute: ALTER TABLE EMP ADD CONSTRAINT emp_state CHECK (state in (NY, RI, NJ)); Warning: for some users, you may have to manually type the single quotes around each state if the above syntax produces errors. Notice that when you run the above statement for the first time, you will not see any results because the table is altered with no feedback. If the statement runs successfully once, then you try running it a second time, you will receive an error indicating that the constraint name already exits: Required: Read and add the following constraint: ALTER TABLE EMP ADD CONSTRAINT emp_ch_wage CHECK (hourly_wage >= 6.25 AND hourly_wage <= 50); No error comes back means that the system accepted the alter command To find out what you have for constraints so far in EMP, click the table EMP on left, click the Constraints tab on right: c. Referential Integrity Foreign key Referential integrity: a foreign key value must be either NULL or a valid primary key value in the relation to which the foreign key refers. Required: Lets create the hours table with the foreign key constraint specified, copy the following: CREATE TABLE hours (emp_no varchar2(3) NOT NULL, week_ending date, hours_worked number(3), CONSTRAINT hours_pk PRIMARY KEY (emp_no, week_ending), CONSTRAINT emp_fk FOREIGN KEY (emp_no) REFERENCES emp(emp_no)); Results: Refresh connection to ensure that hours was created: Note: The data type of the foreign key attribute must match the data type of the primary key in the referenced (parent) table EMP. The name of the foreign key attribute does not have to match can be any valid name. Required: Use the INSERT statement to insert the following records into the hours table. Use commit; after all INSERT statements. Here is the data to insert: A01 21-MAR-1998 38 A02 21-MAR-1998 42 A03 28-MAR-1998 20 Result: Optional: Come up with an INSERT command that violates the referential integrity (Hint: insert a record in table hours for a non-existent employee). The command should be rejected by Oracle: 6. Deleting (dropping) a table, and Foreign Key constrains. Syntax: DROP TABLE table-name; THINK TWICE BEFORE YOU DROP TABLES. The DROP statement removes the table including data. Sometimes the DROP command would be rejected due to the references from other tables foreign keys. Optional but recommended: Do this to learn what happens when a table is dropped. Please do not drop emp or hours tables: Create a simple table named temp with one column of any type. Execute the DROP statement on temp. FYI: There is a quick way to copy a table to a new one before you delete: right-click the table to copy Table Copy. Enter a new table name. If you attempt to delete EMP, you will receive an error if you have created references to hours table correctly in the previous section. Here is the error when you try to drop EMP: Please do not do in this lab: Usually the above error can be fixed by dropping the "child" tables first, THEN dropping the "parent" tables, for example, dropping the hours first, THEN dropping the emp table. Otherwise, it is possible to just drop the FK constraints first, then drop the tables -- but again, that's normally not necessary. Using a SQL Comment Like many languages, you can comment each line of SQL code or the whole script using the following line anywhere: /* This is a comment */ Required: Go to the beginning of the script and insert the following line: /* Oracle Lab First, Last Name */ Final Step: Save the script and answer Lab questions on BB. Before you quit SQL Developer, ensure that you have two tables that you created in this lab as well as the data that you inserted in each table. No tables with data in your account, no credit even if the script is correct