Oracle_ch3_Morrison

Oracle_ch3_Morrison - Chapter 3: Using SQL Queries to...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu TM Oracle-2 Business Database Systems - Dr. Chen u Lesson A e Manually Add, View, Update Data running a script; SQL * PLUS Q Seqences and Object Privileges e Lesson B e Retrieve Data, Sort, Calculations e Functions - Number, Date, Group e Create Column Headings, Aliases e Create Oracle Dynamic SQL Queries e Format Output e Lesson C e Queries to Join multiple tables e SET operators, Database view, Indexes, TM Oracle-3 Business Database Systems - Dr. Chen Lesson A Objectives h Learn how to run a script to create database tables automatically e Learn how to insert data into database tables e Learn how to create database transactions and commit data to the database e Create search conditions in SQL queries e Understand how to update and delete database records, and how to truncate tables e Learn how to create and use sequences to generate surrogate key values automatically e Learn how to grant and revoke database object privileges TM Oracle-4 Business Database Systems - Dr. Chen Lesson A. Q Running a SQL script e SQL> start c:\oradata\ chapter3A\emptynorthwoods.sql e SQL> @ c:\oradata\ chapter3A\emptyclearwater.sql e Insert Data e INSERT INTO <tablename> e VALUES (column1 value, col2 value, ... ); e COMMIT; e ROLLBACK; e SAVEPOINT <point_name>; e ROLLBACK TO <point_name> TM Oracle-5 Business Database Systems - Dr. Chen Scripts Scripts h Scripts are simply files that contain a sequence of SQL and/or PL/SQL commands that can be executed in SQL*Plus. e Note: e SQL and PL/SQL are two languages Oracle uses. e SQL*Plus is the tool we use to access Oracle. e SQL*Plus can execute both SQL and PL/SQL commands plus it has its own commands. TM Oracle-6 Business Database Systems - Dr. Chen Six Important Script Files h Six files are available on the web and Jepson server (under Network neighborhood): Jepsonnt2\chen\bmis341_mbus673\OraData\ then look for each chapter and LESSON e Northwood univeristy database dropnorthwoods.sql (drop northwoods tables, no DDs) emptynorthwoods.sql (drop all tables DDs) northwoods.sql (drop, then create DDs, insert data into database) e Clearwater Traders database dropclearwater.sql (drop clearwater tables, no DDs) emptyclearwater.sql (drop all tables DDs) clearwater.sql (drop, then create DDs, insert data into database) TM Oracle-7 Business Database Systems - Dr. Chen Format Models h Used to format data retrieved from database e Can be used to format a date to display time or a number to display as a currency TM Oracle-8 Business Database Systems - Dr. Chen Inserting Data into Tables h INSERT command adds new records e Field values should match column order, or be specified in command e INSERT INTO faculty (F_ID, F_LAST, F_FIRST, F_MI, LOC_ID) VALUES (1, 'Cox', 'Kim', 'J', 9); See Figures 3-1 to 3-3 TM Oracle-9...
View Full Document

Page1 / 90

Oracle_ch3_Morrison - Chapter 3: Using SQL Queries to...

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

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