Oracle

SQL Tutorial

•CPSC 608 •Database System

Connecting to Oracle • Your username is identical to your UNIX account • To access your Oracle account, you need to change the initial password assigned to by the system • To access Oracle you need to be logged onto a Solaris 2.5 workstation

• You need to add the following line : source /usr/local/bin/SystemLogin in your .login file

Connecting to Oracle To generate a new password, enter the command “orapasswd” at the unix prompt. This program will generate a new Oracle password for you >orapasswd The new password for unix-username@oracledatabase is WU8vn23r

Connecting to Oracle When connecting to SQL use the full Oracle login as given by the orapasswd command as follows: >sqlplus SQL*Plus: Release 8.1.5.0.0 - Production on Wed Feb 9 18:54:47 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Enter user-name: mua9330@oracledatabase Enter password: Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production SQL>

Connecting to Oracle You can change the password to your own liking by typing the following command at the command prompt SQL> alter user identified by You are now ready to begin work in SQL environment To exit just type quit or exit at the command prompt SQL> exit Note: When a user exits the system automatically commits all changes

On line help • Oraview online help in Xwindows environment At the prompt the user can enter two types of command • SQL statements to access database • SQL*Plus commands,for editing and storing SQL statements and formatting query results. To get online help for SQL*Plus commands SQL> help [topic]

Some Useful SQL commands To set pause at the beginning of the each page SQL> set pause on; To view operating system files without getting out of SQL SQL>!ls

Database Relation/Table Operations • Create table • describe • alter table • insert into • drop table

Create table create table Course( Cno char(3) not null, Cname varchar2(22), Cdescp varchar2(25), Cred number(5), Clabfee number(5,2), Cdept char(3) );

Important Data type char(n): fixed -length character string of length n. Max = 255 varchar2(n): variable length character string having a maximum of n characters. Max = 2000 date: holds a date field. Usually specified in string format, such as ‘12 DEC- 1999’ number(n,d): real value occupying n spaces with d digits after the decimal point. Integer(n): integer value occupying n spaces.

Example create table Weather( city varchar2(13), noon number(3,1), midnight number(3,1), precipitation number, primary key (city,precipitation) ); create table Zipcodes( zip number(5) primary key, city varchar2(30) );

Example create table Customer( cno number(5) primary key, cname varchar2(30), street varchar2(30), zip number(5) refference zipcodes, phone char(12) );

describe • • • • • • • •

SQL> create table Course( Cno char(3) not null, Cname varchar2(22), Cdescp varchar2(25), Cred number(5), Clabfee number(5,2), Cdept char(3) );

•

Table created.

• • • • • • • • • •

SQL> describe Course Name Null? Type ----------------------------------------- -------- ---------------------------CNO NOT NULL CHAR(3) CNAME VARCHAR2(22) CDESCP VARCHAR2(25) CRED NUMBER(5) CLABFEE NUMBER(5,2) CDEPT CHAR(3)

Table Modifying •

• • • •

Alter table Course modify( Cdept char(6) ); alter table Course drop Cdescp; drop table Course; To see the list of all the tables that you have created select table_name, table_type from user_catalog; To insert a row into a table insert into Course values (‘614’, ‘Computer Architecture’, ‘Pipelining and memory’, 3,150.00, ‘th’);

Deletion • To delete a particular row from the table delete from Course where Cname = ‘intro to cs’ • To update a particular column value for a particular row update Course set Clabfee = clabfee*10 where cdept = ‘phil’

The select statement • Basic form select from < table list> where

SQL queries Query 1: Display entire course table. Select * from Course;

Note The changes made to the database using insert, delete and update can be reversed if necessary. Oracle SQL provides two commands : Commit & rollback. The changes made to the database can be made permanent by using commit. The changes made to the database since last commit can be reversed using roll back. There are some actions that force commit. One is exit. Others are create table, drop table, alter table

Comparison Operators • • • • • •

= equal to < less than > greater than <= less than or equal to >= greater than or equal to !=, ^=,<> not equal

Query Display all information about any course with a zero labfee. Select * from Course where Clabfee=0.00

Query Display all information about courses having course name values which follows “Database” in alphabetic sequence. select * from course where Cname > ‘Database’;

Sorting the result Table Display the entire Course table. Sort the output display by the clabfee values select * from Course order by Clabfee;

Sorting the result Table Display the department id and name of every course. Sort the result by department id. Within each department, sort by course name. Select Cdept, Cname from Course order by Cdept, Cname;

Boolean Connectors: AND - OR - NOT Display all information about any three credit philosophy course which has a lab fee which is strictly between zero and one hundred dollars. Select * from Course where Clabfee > 0 and Clabfee < 1000 and Cdept = ‘phil’ and Cred = 3;

Logical test against a list of values Display the course number, description and credit for any course which is worth 2, 6, or 9 credits. Select Cno, Cdescp, Cred from Course where Cred in (2,6,9);

Logical test against a list of values Display the course name and lab fee of any course with a lab fee less than $100 or greater than $200. Select Cname, Clabfee from Course where Clabfee not between 100 and 200;

Pattern Matching When you wish to retrieve information from rows having similar but not necessarily equal, values in a given column. The format is: where column-name like pattern Two special wildcard • “-” : represent exactly one character • “%” : represent character string of any length (including zero)

Query Display the course number and name of all introductory course. Select Cno, Cname from Course where Cname like ‘intro%’;