OracleDB1 - SQL in Oracle Outline: SQL in Oracle Oracle...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 1 SQL in Oracle Outline: SQL in Oracle Oracle database system architecture - Oracle server - Oracle client SQL*Plus PL/SQL
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 2 SQL in Oracle Oracle system architecture Oracle server and Oracle client Oracle server Oracle client Oracle client Oracle client data management transaction control recovery security interface to manipulate data tools to support development of application
Background image of page 2
Jan. 2008 Yangjun Chen ACS-3902 3 SQL in Oracle SQL*Plus Interface to manipulate Oracle databases Tool to support the development of application - SQL*Plus as an interface To start SQL*Plus, enter Oracle username and password : $> sqlplus jason/athena (from a command line operating system such UNIX) or click: Start Program Oracle (for Windows - SQL*Plus)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 4 SQL in Oracle - SQL*Plus as an interface Create a table with integrity constraints CREATE TABLE bank_account (bank_acct_no VARCHAR2(40), empid NUMBER(10), BANK_ROUTE_NO VARCHAR2(40), BANK_NAME VARCHAR2(50), CONSTRAINT pk_bank_acct_01, PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_acct_01 FOREIGN KEY (empid) REFERENCE employee (empid));
Background image of page 4
Jan. 2008 Yangjun Chen ACS-3902 5 SQL in Oracle - SQL*Plus as an interface Create a table with integrity constraints CREATE TABLE bank_account (bank_acct_no VARCHAR2(40), empid NUMBER(10), BANK_ROUTE_NO VARCHAR2(40), BANK_NAME VARCHAR2(50), CONSTRAINT pk_bank_acct_01, PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_acct_01 FOREIGN KEY (empid) REFERENCE employee (empid) ON DELETE CASCADE);
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 6 SQL in Oracle CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR2(25), firstname VARCHAR2(25), salary NUMBER(10, 4), home_phone NUMBER(15), CONSTRAINT pk_employee_01 PRIMARY KEY (empid), CONSTRAINT uk_employee_01 UNIQUE (home_phone)); The difference between PRIMARY KEY and UNIQUE is that for a UNIQUE attribute NULL value is allowed.
Background image of page 6
Jan. 2008 Yangjun Chen ACS-3902 7 SQL in Oracle - SQL*Plus as an interface NOT NULL constraints and check constraints CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR2(25), NOT NULL firstname VARCHAR2(25), NOT NULL salary NUMBER(10, 4), CHECK(salary < 50000 home_phone NUMBER(15), CONSTRAINT pk_employee_01 PRIMARY KEY (empid), CONSTRAINT uk_employee_01 UNIQUE (home_phone));
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 8 SQL in Oracle - SQL*Plus as an interface Adding and modifying columns ALTER TABLE products ADD (color VARCHAR2(10)); ALTER TABLE products MODIFY (SERIAL# VARCHAR2(25)); Assume that SERIAL# is an attribute in PRODUCTS with type VARCHAR2(10).
Background image of page 8
Jan. 2008 Yangjun Chen ACS-3902 9 SQL in Oracle - SQL*Plus as an interface Modifying integrity constraints ALTER TABLE products MODIFY (color NOT NULL); ALTER TABLE products ADD (CONSTRAINT pk_products_01 PRIMARY KEY (product#)); ALTER TABLE products ADD (CONSTRAINT fk_products_01 FOREIGN KEY REFERENCES (AVAIL_COLOR.color));
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2008 Yangjun Chen ACS-3902 10 SQL in Oracle
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/17/2010 for the course ACS ACS-3911 taught by Professor Chen during the Spring '10 term at Windsor.

Page1 / 59

OracleDB1 - SQL in Oracle Outline: SQL in Oracle Oracle...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online