1 / 17

Oracle 9i User Definition and Table Creation using the Oracle Interface

Oracle 9i User Definition and Table Creation using the Oracle Interface. Name: Andre Bugay (Andi) Email: andre.bugay@gmx.de. Overview. Presentation of Enterprise Manager Console User Interface for Oracle Database Administration Create New User Create New Table

harris
Download Presentation

Oracle 9i User Definition and Table Creation using the Oracle Interface

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. Oracle 9i User Definition and Table Creation using the Oracle Interface Name: Andre Bugay (Andi) Email: andre.bugay@gmx.de

  2. Overview • Presentation of Enterprise Manager Console • User Interface for Oracle Database Administration • Create New User • Create New Table • Alter Table Content and Constraints

  3. Enterprise Manager Console [1] • Start the Program  Windows Start Menu [시작]  Programs [프로그램]  Oracle - OraHome92  Enterprise Manager Console Launch Standalone [OK]

  4. Enterprise Manager Console [2] 1 Menu 2 Objects 3 Details 4 Tools

  5. Login / Connect to Database • Right-click on Main Database  Connect... • Login with • Username: system • Password: uit310 1 2

  6. Create New User [1] • Name: TOM • Password: test • Tablespaces: USERS • Units of Database where Tables are saved in • Granted System Privileges • SELECT ANY TABLE • CREATE ANY TABLE

  7. Create New User [2] • Click on Main Database • Go to Menu "Object"  "Create" • Choose "User" and press [Create] 2 1 3

  8. Create New User [4] • Enter Name and Password • Select tab "System",double-clickprivileges to grantto user • Admin Option • User can assignspecific privilegeto other users

  9. Create Table [1] foreign key

  10. Create Table [2] • Click on Main Database • Go to Menu "Object" "Create" • Choose "Table" and press [Create] 2 1 3

  11. Create Table [3] • Step through Wizard to create Table "STUDENT" • Step 1: Name, Schema, Tablespace • Step 2: Add Columns (Names, Datatypes) • Step 3: Set Primary Key on "ID"-Column • Step 4: Assign "ID", "NAME", "DEP_ID" as not null • Step 6: Select "YEAR" and enter Check Condition "YEAR in (1,2,3,4)" to constrain possible values • Step 13: Verify generated SQL-Statement and press [FINISH] to generate table • Repeat to create 2nd Table "DEPARTMENT"

  12. Alter Table Contents • Go to Databases  ORCL  Schema  SCOTT  Tables • Right-click on table to alter  View/Edit Contents • You can alter existing data or add new entry by clicking (empty) row

  13. Relations between Tables [1] "STUDENT"."DEP_ID" (Foreign Key) "DEPARTMENT"."ID" (Primary Key)

  14. Relations between Tables [2] • Select Table "STUDENT" and tab "Constraints" • Click last blank line to define new constraint • Check "Foreign Key" as type, "SCOTT" as Referenced Schema and "DEPARTMENT" as Referenced Table • Referencecolumn"DEP_ID"with "ID"

  15. Alter Table Scheme • Go to Databases  ORCL  Schema  SCOTT  Tables • Click on cell in table that you want to alter • You can add a new table column by clicking on the last blank row

  16. Exercise [1] • Open SQL*Plus • [시작]  [프로그램]  [Oracle]  [Application Development]  [SQL Plus] • Login as generated User TOM (password: test) • Call SQL-Statement to select Student's Name and his/her phone number from tables STUDENT, DEPARTMENT joining on key 'dep_id' • Expected result: NAME PHONE -------------------- -------------- Eggi 51-320-5555 Marin 51-320-9876 Lee 51-320-9876

  17. Exercise [2] • Possible solution 1 SELECT "SCOTT"."STUDENT".NAME, "SCOTT"."DEPARTMENT".PHONE FROM "SCOTT"."STUDENT", "SCOTT"."DEPARTMENT" WHERE ("SCOTT"."STUDENT".DEP_ID = "SCOTT"."DEPARTMENT".ID); • Possible solution 2 (Inner Join) SELECT "SCOTT"."STUDENT".NAME, "SCOTT"."DEPARTMENT".PHONE FROM "SCOTT"."STUDENT" INNER JOIN "SCOTT"."DEPARTMENT" ON ("SCOTT"."STUDENT".DEP_ID = "SCOTT"."DEPARTMENT".ID);

More Related