1 / 19

Relational Oracle 11g Tutorial

Relational Oracle 11g Tutorial. Νίκος Πελέκης Εργαστήριο Πληροφοριακών Συστημάτων , Τμήμα Πληροφορικής, Πανεπιστήμιο Πειραιώς http://infolab.cs.unipi.gr/. Outline of seminar. Part II Introduction to Oracle Relational Creating a schema Connecting to Oracle Bulk loading using SQLloader

hanh
Télécharger la présentation

Relational Oracle 11g Tutorial

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. Relational Oracle 11gTutorial Νίκος Πελέκης Εργαστήριο Πληροφοριακών Συστημάτων, Τμήμα Πληροφορικής, Πανεπιστήμιο Πειραιώς http://infolab.cs.unipi.gr/

  2. Outline of seminar • Part II • Introduction to Oracle Relational • Creating a schema • Connecting to Oracle • Bulk loading using SQLloader • A Sample Application Using Relational Features

  3. Creating your schema • EM at UNIPI: https://kittie:1158/em • SID= ORCL • Server name=KITTIE • User “DMUSERX” password=userX 3

  4. Connecting to Oracle 4

  5. Connecting to Oracle

  6. Bulk loading using SQLloader CREATE TABLE trucks_final (obj_id NUMBER(*,0), id NUMBER(*,0), t_day NUMBER(*,0), t_month NUMBER(*,0), t_year NUMBER(*,0), t_hour NUMBER(*,0), t_min NUMBER(*,0), t_sec NUMBER(*,0), lat NUMBER(*,10), lon NUMBER(*,10), x NUMBER(*,10), y NUMBER(*,10)) PCTFREE 10 INITRANS 1 MAXTRANS 255 TABLESPACE users STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 ) 6

  7. Bulk loading using SQLloader • Download “Trucks.ctl” from http://infolab.cs.unipi.gr/courses/db/labs/Oracle%20Tutorials/SQLloader/ • sqlldr userid=dmuserX/userX@kittie:1521/orcl control=Trucks.ctl log=log.txt • Access table from SQLDeveloper • Do the following queries: • How many records does the “trucks_final” table has? • Where was I at “10/09/2002 – 09:15:59”? • How many days was I travelling? 7

  8. E-RDiagram for Purchase Order Application 8

  9. Purchase Order Application • The figure shows the entity relationships among the following four relational entities or tables: • Customer • Purchase Order • Line Item of a purchase order • Stock Item • A customer places zero or more purchase orders, but a purchase order has at most one customer. • A purchase order can contain many line items, but a given line item occurs on exactly one purchase order. • Each line item refers to exactly one stock item; a stock item can occur in zero or many different line items. 9

  10. Creating Tables Under the Relational Model CREATE TABLE Customer_reltab ( CustNo NUMBER NOT NULL, CustName VARCHAR2(200) NOT NULL, Street VARCHAR2(200) NOT NULL, City VARCHAR2(200) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR2(20) NOT NULL, Phone1 VARCHAR2(20), Phone2 VARCHAR2(20), Phone3 VARCHAR2(20), PRIMARY KEY (CustNo)); 10

  11. Creating Tables Under the Relational Model CREATE TABLE PurchaseOrder_reltab ( PONo NUMBER, /* purchase order no */ Custno NUMBER references Customer_reltab, /* Foreign KEY referencingcustomer */ OrderDate DATE, /* date of order */ ShipDate DATE, /* date to be shipped */ ToStreet VARCHAR2(200), /* shipto address */ ToCity VARCHAR2(200), ToState CHAR(2), ToZip VARCHAR2(20), PRIMARY KEY(PONo)); 11

  12. Creating Tables Under the Relational Model CREATE TABLE Stock_reltab ( StockNo NUMBER PRIMARY KEY, Price NUMBER, TaxRate NUMBER); CREATE TABLE LineItems_reltab ( LineItemNo NUMBER, PONo NUMBER REFERENCES PurchaseOrder_reltab, StockNo NUMBER REFERENCES Stock_reltab, Quantity NUMBER, Discount NUMBER, PRIMARY KEY (PONo, LineItemNo)); 12

  13. Inserting Values Under the Relational Model INSERT INTO Stock_reltab VALUES(1004, 6750.00, 2); INSERT INTO Stock_reltab VALUES(1011, 4500.23, 2); INSERT INTO Stock_reltab VALUES(1534, 2234.00, 2); INSERT INTO Stock_reltab VALUES(1535, 3456.23, 2); INSERT INTO Customer_reltab VALUES (1, 'Jean Nance', '2 Avocet Drive', 'Redwood Shores', 'CA', '95054', '415-555-1212', NULL, NULL); INSERT INTO Customer_reltab VALUES (2, 'John Nike', '323 College Drive', 'Edison', 'NJ', '08820', '609-555-1212', '201-555-1212', NULL); INSERT INTO PurchaseOrder_reltab VALUES (1001, 1, SYSDATE, '10/05/1997', NULL, NULL, NULL, NULL); INSERT INTO PurchaseOrder_reltab VALUES (2001, 2, SYSDATE, '20/05/1997', '55 Madison Ave', 'Madison', 'WI', '53715'); INSERT INTO LineItems_reltab VALUES(01, 1001, 1534, 12, 0); INSERT INTO LineItems_reltab VALUES(02, 1001, 1535, 10, 10); INSERT INTO LineItems_reltab VALUES(01, 2001, 1004, 1, 0); INSERT INTO LineItems_reltab VALUES(02, 2001, 1011, 2, 1); 13

  14. Get Customer and Line Item Data for a Specific Purchase Order SELECT C.CustNo, C.CustName, C.Street, C.City, C.State, C.Zip, C.phone1, C.phone2, C.phone3, P.PONo, P.OrderDate, L.StockNo, L.LineItemNo, L.Quantity, L.Discount FROM Customer_reltab C, PurchaseOrder_reltab P, LineItems_reltab L WHERE C.CustNo = P.CustNo AND P.PONo = L.PONo AND P.PONo = 1001; 14

  15. Get the Total Value of Purchase Orders SELECT P.PONo, SUM(S.Price * L.Quantity) FROM PurchaseOrder_reltab P, LineItems_reltab L, Stock_reltab S WHERE P.PONo = L.PONo AND L.StockNo = S.StockNo GROUP BY P.PONo; 15

  16. Get the Purchase Order and Line Item Data for Stock Item 1004 SELECT P.PONo, P.CustNo, L.StockNo, L.LineItemNo, L.Quantity, L.Discount FROM PurchaseOrder_reltab P, LineItems_reltab L WHERE P.PONo = L.PONo AND L.StockNo = 1004; 16

  17. Update – Delete statements UPDATE LineItems_reltab SET Quantity = 20 WHERE PONo = 1001 AND StockNo = 1534; DELETE FROM LineItems_reltab WHERE PONo = 1001; DELETE FROM PurchaseOrder_reltab WHERE PONo = 1001; 17

  18. Υλικό • Tutorials at UNIPI: http://infolab.cs.unipi.gr/courses/db/labs/ • Tutorials at OTN: • http://www.oracle.com/technology/obe/start/index.html • http://www.oracle.com/technology/obe/11gr1_db/otn_all_db11gr1.html • http://www.oracle.com/technology/obe/hol08/otn_all_hol08.html • SQL Developer: http://www.oracle.com/technology/obe/hol08/sqldev_mngdb/sqldev_mngdb/sqldev_mngdb_otn.htm • EM at UNIPI: https://kittie:1158/em • SID= ORCL • Server name=KITTIE • User “DMUSERX” password=userX • Oracle download: http://www.oracle.com/technology/software/products/database/index.html • Video tutorials: http://www.vtc.com/products/Oracle-Database-Administration-Fundamentals-I-tutorials.htm# 18

  19. Επικοινωνία University of Piraeus, Information Systems Laboratory 80-82 Zeas Str., 18534 Piraeus, Greece3rd Floor Phone: (+30) 210 4142428 http://infolab.cs.unipi.gr/ Νίκος Πελέκης npelekis@unipi.gr 19

More Related