Spring 2012 ITCS 3160 Database Design and Implementation

  • Slides: 18
Download presentation
Spring 2012 ITCS 3160: Database Design and Implementation Hands-on Learning Ning Zhou

Spring 2012 ITCS 3160: Database Design and Implementation Hands-on Learning Ning Zhou

Connect to the Oracle database • Instructions – https: //cci-wiki 01. uncc. edu/node/247 •

Connect to the Oracle database • Instructions – https: //cci-wiki 01. uncc. edu/node/247 • User name: your 49 er account ( e. g. nzhou is mine) • Password(default): qwe 123

Screen Shots • Using IE if firefox, chrome and safari fails • Please install

Screen Shots • Using IE if firefox, chrome and safari fails • Please install Java Run time • Use IE to open – http: //www. uncc. edu/citrix

Click “CCI” Directory

Click “CCI” Directory

Click “SQL-Developer”

Click “SQL-Developer”

Tasks • • • CREATE TABLE - creates a new table ALTER TABLE -

Tasks • • • CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) (next time hands-on learning) DROP INDEX - deletes an index SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database

Example Instances • We will use these instances of the Sailors and Reserves relations

Example Instances • We will use these instances of the Sailors and Reserves relations in our examples. R 1 S 1 B 1 bid bname color 101 interlake blue 102 interlake red 103 clipper green 104 marine S 2 red Raghu Ramakrishnan

Step 1: CREATE TABLE Sailors ( sid int, sname varchar(35), rating int, age int,

Step 1: CREATE TABLE Sailors ( sid int, sname varchar(35), rating int, age int, PRIMARY KEY (sid) ) Or CREATE TABLE Sailors ( sid int, sname varchar(35), rating int, age int, ) ALTER TABLE Sailors ADD PRIMARY KEY (sid)

Step 2: CREATE TABLE Reserve ( …. . ) CREATE TABLE Boats ( ……

Step 2: CREATE TABLE Reserve ( …. . ) CREATE TABLE Boats ( …… )

Step 3: INSERT INTO table_name VALUES (value 1, value 2, value 3, . .

Step 3: INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) Or INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ) INSERT ALL INTO table_name (column 1, …) VALUES (v 1, v 2, …) SELECT * FROM dual;

alter table_name modify ( column_name data_type) Data_type: int, float, numeric(9, 2), double precision

alter table_name modify ( column_name data_type) Data_type: int, float, numeric(9, 2), double precision

UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value DELETE

UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value DELETE FROM table_name or DELETE * FROM table_name DROP TABLE table_name

Step 4: SELECT column_name(s) FROM table_name SELECT * FROM table A, table B A

Step 4: SELECT column_name(s) FROM table_name SELECT * FROM table A, table B A SELECT statement on multiple tables without a proper JOIN condition will lead to a cartesian product. (i. e. No. Of Output rows = No. of rows in table 1 X No of rows in table 2. . SELECT * FROM table A, table B where …. (see the difference, try different join)

 • Select the ‘name’ of the boat that ‘dustin’ reserves • (think about

• Select the ‘name’ of the boat that ‘dustin’ reserves • (think about it a little bit and then click next)

 • select bname from boats b, reserves r, sailors s • where s.

• select bname from boats b, reserves r, sailors s • where s. sname = 'dustin' and r. sid = s. sid and b. bid = r. bid

Step 5: SELECT column_name(s) FROM table_name Where …

Step 5: SELECT column_name(s) FROM table_name Where …