1 / 14

Agenda for Class 2/02/2006

Agenda for Class 2/02/2006. Finish discussing constraints generated with the CREATE TABLE statement. Discuss DROP statement. Discuss INSERT, COMMIT, DELETE, and UPDATE statements. Introduce SELECT statement. Discuss a few helpful SQLPlus commands. Do SQL class exercise.

hallie
Download Presentation

Agenda for Class 2/02/2006

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda for Class 2/02/2006 • Finish discussing constraints generated with the CREATE TABLE statement. • Discuss DROP statement. • Discuss INSERT, COMMIT, DELETE, and UPDATE statements. • Introduce SELECT statement. • Discuss a few helpful SQLPlus commands. • Do SQL class exercise.

  2. Example 1: Simple Order Database discussed in class on Tuesday: We discussed in class how to create the tables with primary key and referential integrity constraints, now let’s add some other constraints.

  3. CREATE TABLE ord (order_no NUMBER(5), order_date DATE, customer_no NUMBER(3) NOT NULL, ship_code CHAR(2) CHECK (ship_code in(‘02’,’A1’,’B3’,’04’)), CONSTRAINT ord_pk PRIMARY KEY (order_no)); CREATE TABLE prod (prod_no NUMBER(3), description VARCHAR2(50), cost NUMBER(8,2) CHECK (cost > .02), CONSTRAINT prod_pk PRIMARY KEY (prod_no)); CREATE TABLE prod_on_ord (order_no NUMBER(5), prod_no NUMBER(3), quantity NUMBER(6,2) DEFAULT 1, price NUMBER(8,2), CONSTRAINT prodord_pk PRIMARY KEY (order_no, prod_no), CONSTRAINT ord_fk FOREIGN KEY(order_no) REFERENCES ord(order_no), CONSTRAINT prod_fk FOREIGN KEY(prod_no) REFERENCES prod(prod_no));

  4. What is a recursive referential integrity constraint? • Imagine a situation where a company is a parent company of another company. The organization as a whole keeps track of company name, phone and the identifier of the parent company. CompanyID Name Phone ParentCompanyID 123 Dining Supply Co 858-133-4551 null 177 DineOut 775-677-6771 123 897 RestCo 805-891-1233 123 788 Jansen Supply 503-281-0667 897

  5. Creating a recursive referential integrity constraint CREATE TABLE company (companyID CHAR(3), companyname VARCHAR2(40), phone CHAR(10), parentcompanyID CHAR(3), CONSTRAINT company_pk PRIMARY KEY (companyID), CONSTRAINT parent_fk FOREIGN KEY (parentcompanyID) REFERENCES company(companyID));

  6. Deleting a table • Cannot have more than one data object with the same name. • Must delete data objects before re-creating them. • SQL Statement is: DROP TABLE prod_on_ord; • Must delete objects in the order of referential integrity constraints, unless the constraints are “cascaded” during the delete process. • SQL Statement is: DROP TABLE prod_on_ord CASCADE CONSTRAINTS;

  7. SQL INSERT Statement • Used to “populate” a table with data. • Used to enter one row of data. • Character/string data must be entered surrounded by single quotes. • Dates are best entered using the Oracle default format of dd-mon-yy or dd-mon-yyyy. For example, today’s date in the Oracle default format is: 02-feb-2006

  8. Using the original ord table example, these are sample INSERT statements used to add rows to the table INSERT INTO ord VALUES (14452, ’02-feb-06’, 234, ‘A1’); INSERT INTO ord VALUES (23415, SYSDATE, 2100, ‘B3’); INSERT INTO ord VALUES (14419, ’08-jul-1999’, 320, ‘02’); INSERT INTO ord (order_no, customer_no) VALUES (2231, 334); COMMIT;

  9. Purpose of the COMMIT Statement • Some SQL statements act directly to save results on disk, others do not. • CREATE produces a data object on disk; not just in main memory. • INSERT adds a row of data in main memory. • To save data from memory to disk, use the COMMIT statement.

  10. Deleting Data from a Table DELETE FROM ord WHERE ord_no = 3344; DELETE FROM prod WHERE cost < 1.56; DELETE FROM prod_on_ord WHERE price < 1.25 AND quantity < 3;

  11. Changing Existing Data in a Table UPDATE ord SET order_date = ’22-jan-06’ WHERE order_no = 14452; UPDATE ord SET order_date = SYSDATE WHERE order_no = 14452; UPDATE prod SET cost = 0 WHERE description = ‘bolt’;

  12. Retrieving Data from a Table General Syntax for accessing data from a table SELECT [all or distinct] (what columns) FROM (table) WHERE (condition) GROUP BY (grouping fields) HAVING (condition) ORDER BY (sort fields)

  13. Example of Retrieving Data from a Table SELECT * FROM ord; SELECT * FROM prod’ SELECT order_id, order_date FROM ord; SELECT order_id, order_date FROM ord WHERE cust_id = ‘6511’; The * means retrieve all columns. The FROM statement, without a WHERE statement, means retrieve all rows.

  14. Retrieving Info from Oracle SQLPlus command to see structure of data object: DESCRIBE emp; SQL command to see contents of your tablespace: SELECT table_name FROM tabs; Tabs is a data view in the Oracle data dictionary containing information about all database objects in a given tablespace.

More Related