CS 340 Lab Manual



CS 340 Lab ManualOracle & SQLPrince Sultan UniversityComputer and Information Sciences DepartmentTable of Contents TOC \o "1-3" \h \z \u Introduction to Oracle PAGEREF _Toc280232001 \h 1Oracle Utilities and Development Tools PAGEREF _Toc280232002 \h 1SQL*Plus PAGEREF _Toc280232003 \h 1Introduction PAGEREF _Toc280232004 \h 1Getting Started PAGEREF _Toc280232005 \h 1Getting Help PAGEREF _Toc280232006 \h 1Entering and Executing SQL Statements PAGEREF _Toc280232007 \h 1Saving Buffer Contents PAGEREF _Toc280232008 \h 1Retrieving and Executing SQL Files PAGEREF _Toc280232009 \h 1Describing Tables PAGEREF _Toc280232010 \h 1Quitting PAGEREF _Toc280232011 \h 1Structured Query Language (SQL) PAGEREF _Toc280232012 \h 1Types of SQL Statements PAGEREF _Toc280232013 \h 1Syntax PAGEREF _Toc280232014 \h 1Oracle Express Download PAGEREF _Toc280232015 \h 1Introduction to OracleOracle Utilities and Development Tools SQL*Plus: For creating and testing command-line SQL queries and executing PL/SQL procedural programs. Developer Suite: For developing database applications. Two developer tools:Forms Builder: for creating custom user applications.Report Builder: for creating reports for displaying, printing, and distributing summary data. Enterprise Manager:For performing database administration tasks such as creating new user accounts and configuring how the DBMS stores and manages data.SQL*PlusIntroductionSQL*Plus is essentially an interactive command editor which enables the user to interrogate the database, e.g.: CREATE, ALTER, and DROP tables. INSERT, UPDATE, and DELETE rows. Specify primary keys, foreign keys, and indexes. GRANT and REVOKE access privileges. In order to perform these functions, the user enters a series of SQL statements.Getting StartedOpen SQL*Plus, enter appropriate name, password, & host stringscottpsustddata If the previous information have been entered correctly, SQL*Plus displays its own command line prompt; by default, SQL>.Any SQL command can be entered hereGetting Help Full on-line help is available while using SQL*Plus.To get help: Select the help menu option, or Enter help at the SQL*Plus promptEntering and Executing SQL StatementsSQL statements entered at the SQL*Plus prompt are held in a multi-line command buffer. Non-SQL statements (such as help) may be entered without changing the contents of the SQL buffer.The buffer retains the contents of the latest SQL statement until a new SQL statement is entered or the buffer is cleared using the clear buffer command. The contents of the SQL buffer can be edited using several commands. (see SQL part)An SQL statement is executed immediately if the last line of the statement is terminated with a semi-colon. Additionally, the statement currently in the SQL buffer can be executed by entering:A / (on its own), orThe keyword runat the SQL*Plus prompt.Saving Buffer ContentsThe contents of the SQL buffer can be saved in an operating system file by entering the command: save filename[.ext] [replace] If the .ext is omitted, the file will be given the extension .sql.The optional argument replace allows an existing file to be overwritten.Using the optional argument append instead of replace allows the contents of the SQL buffer to be appended to an existing file.Retrieving and Executing SQL Files Either of the following commands can be issued at the SQL*Plus prompt:start filename[.ext]or@filename[.ext] In either case the specified file is executed and the final SQL statement in the file is retained in the buffer.To load an SQL file into the buffer without executing it the following command may be issued: get filename[.ext]Using a Text Editor To overcome the limitations of the SQL buffer, a host operating system text editor or notepad may be used instead. The main benefit of using a text editor (apart from its editing capabilities) is the ability to create files containing multiple SQL statements.Each statement (except optionally the last) must be terminated by a semi-colon.Describing Tables The structure of any table within the database may be displayed by entering the command describe at the SQL*Plus prompt. Syntax:describe tablename The names and types of each column within the specified table will be displayed.Quitting To quit SQL*Plus, either one of the following commands can be used: quit, or exit.Both commands automatically commit any outstanding changes to the database.Structured Query Language (SQL)Types of SQL Statements Data Definition Language (DDL): Commands that define a database. E.g. CREATE, ALTER, DROP, ...etc. Data manipulation language (DML): Commands that maintain and query a database. E.g. SELECT, INSERT, UPDATE, DELETE. Data Control Language (DCL): Commands that control a database, including administering privileges and committing data. E.g. CONNECT, GRANT, REVOKE, ...etc.SyntaxData Types:CHAR(n)VARCHAR2(n)NUMBERNUMBER(n,d)NUMBER(n)DATEColumn Level Constraints:NOT NULLUNIQUEPRIMARY KEYREFERENCES table (key) CHECK (condition)Table Level Constraints:UNIQUE (column-list) [CONSTRAINT name] PRIMARY KEY (column-list)[CONSTRAINT name] FOREIGN KEY (column-list) REFERENCES table (column-list) [ON DELETE CASCADE] [ON DELETE SET NULL]CHECK (condition)CREATE TABLE Syntax:CREATE TABLE name (column-1 TYPE [DEFAULT value] [constraints],column-2 TYPE [DEFAULT value] [constraints], :column-n TYPE [DEFAULT value] [constraints],[table-constraints])ALTER TABLE Syntax:ALTER TABLE name ADD …ALTER TABLE name MODIFY …ALTER TABLE name DROP …DROP TABLE Syntax:DROP TABLE name [CASCADE CONSTRAINTS]SELECT Statement Syntax: SELECT[DISTINCT] attribute-listFROMtable-list WHEREcondition(s) GROUP BYgrouping-attributesHAVINGgroup-condition(s)ORDER BYattribute-listINSERT INTO Syntax:INSERT INTO table [(attribute-list)]VALUES (value-list)INSERT INTO table [(attribute-list)]SELECT statementDELETE FROM Syntax:DELETEFROM table [WHERE conditions]UPDATE Syntax:UPDATEtable SETattribute-1 = value-1,:attribute-n = value-n [WHERE conditions]UPDATEtable SET(attribute-list)=(SELECT statement)[WHERE conditions]CREATE VIEW Syntax:CREATE VIEW name ASSELECT statement Oracle Express DownloadOracle Database 10g Express Edition (XE) is an entry-level, small-footprint (150MB) database based on the Oracle Database 10g.Free to develop, deploy, and distribute,Fast to download;Simple to administer.Oracle Database XE is a great starter database for students who need a free database for their curriculum With Oracle Database XE.To download, go to: you’ll need to register to be able to download Oracle Database XE. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download