Oracle Structured Query Language Examples

1
Oracle Structured Query Language Examples

• Lloyd D. Brooks, Professor
• Management Information Systems
• MIS 3775

2
Basic SQL Commands

• SELECT
• FROM
• WHERE
• Appendix

3
SQL Example

• SELECT First, Last
• FROM Student
• WHERE Num 23
• Num Last First Phone
• 18 Doe John
  2382
• 23 Brown Betty 4722
• 27 Carlson Daniel 4920

Appendix
4
Creating a table

• Create Table Inventory
• (PackID Varchar2(4),
• PackName Varchar2(20),
• PackVer Number(3,2),
• PackType Varchar2(15),
• PackCost Number(5,2))
• Note Mark there is no comma after the last
  field/Column description
• Appendix

5
Text Data Defined

• Note Oracle defines text data as follows
• Packid Varchar2(4)
• Appendix

6
Decimal Data Defined

• Note Oracle defines decimal data as follows
• Salary Number(8,2)
• Appendix

7
Null Constraints

• Note Null constraints can be entered as
  follows
• Quantity INTEGER NOT NULL
• Order_Date DATE NOT NULL
• Appendix

8
Primary Key Definition

• Note The primary key can be defined at table
  definition end as follows
• CONSTRAINT Inventory_PK PRIMARY KEY (PackID)
• Notes Inventory_PK is a arbitrary name
  assigned to the constraint. PackID is the name
  of the primary key field in the table.
• Primary key can also be created as
• PackID varchar210 primary key
• Appendix

9
Boolean Operators and Algebra

• AND OR NOT
• Multiply
• / Divide
• Add
• - Subtract
• Appendix

10
SQL Comparison Operators

• Equal to
• gt Greater than
• gt Greater than or equal to
• lt Less than
• lt Less than or equal to
• ltgt Not equal to
• ! Not equal to
• Appendix

11
Data Type Samples in SQL

• Order_Date DATE
• Last_Name VARCHAR2(18)
• Quantity INTEGER
• Amount Number(7,2)
• Appendix

12
Data Query No Restrictions

• SELECT PackID, PackName, PackCost
• FROM Inventory
• Appendix

13
Data Query Wild Card

• SELECT
• FROM Inventory
• Appendix

14
Data Query Value Condition

• SELECT EMP_Name
• FROM Employee
• WHERE Salary gt 50000
• Appendix

15
Data Query - Not Equal

• SELECT Compid, Mfgname
• FROM Computer
• WHERE Mfgname ltgt Bantam
• Note Alternative for the criteria command
• WHERE Mfgname ! Bantam
• Note SQL data are case sensitive.
• Appendix

16
Data Query Text Condition

• SELECT PackID, PackName
• FROM Inventory
• WHERE PackType Database
• Appendix

17
Data Query Compound Condition

• SELECT PackName
• FROM Inventory
• WHERE PackType Database
• and PackCost gt 400
• Appendix

18
Data Query NOT Command

• SELECT PackName
• FROM Package
• WHERE NOT (PackType Database)
• Appendix

19
Sorting Records Single Field

• SELECT
• FROM Employee
• ORDER BY Last_Name
• Note The default order is ascending. Add DESC
  after the attribute to change the order to
  descending.
• Appendix

20
Sorting Records Compound Fields

• SELECT
• FROM Employee
• ORDER BY Last_Name, First_Name
• Note Indicate the major sort field first and
  then the minor sort field next with a comma
  between the fields.
• Appendix

21
Built-In Functions - Count

• SELECT COUNT(PackID)
• FROM Inventory
• Note Oracle has numerous built-in functions for
  a variety of statistical, financial, and other
  applications.
• Appendix

22
Count Function With a Condition

• SELECT COUNT(Tagnum)
• FROM PC
• WHERE Location Home
• Note COUNT is a function. Do not space after
  the function name.
• Appendix

23
Count and Sum Functions Combined

• SELECT COUNT(PackID), Sum(PackCost)
• FROM Package
• Note Count is a function that determines how
  many. Sum is a function that determines how
  much.
• Appendix

24
Average Function

• SELECT AVG(Packcost)
• FROM Package
• WHERE Packtype Database
• Note AVG will initiate the function to
  compute the average for the values for the field
  indicated in parenthesis.
• Appendix

25
Grouping by a Control Field

• SELECT TagNum, Sum(SoftCost)
• FROM Software
• GROUP BY TagNum
• ORDER BY TagNum
• Appendix

26
Grouping With a Condition

• SELECT TagNum, Sum(SoftCost)
• FROM Software
• GROUP BY TagNum
• HAVING Sum(SoftCost) gt 600
• ORDER BY TagNum
• Note The Having command takes the place of the
  Where command for groups.
• Appendix

27
Joining Tables

• SELECT Software.Packid, Package.Packname,
  Package.Packver, Software.Tagnum,
  Software.Instdate, Software.Softcost
• FROM Software, Package
• WHERE Software.Packid Package.Packid
• Note The WHERE command determines if a value
  exists in both tables and is used when tables are
  being joined.
• Appendix

28
Alias in Oracle

• SELECT pt_fname First Name, pt_lname Last
  Name, ptdob BirthDate
• FROM Patient
• Note Alias must include double quotes
• Note Omission of quotes will result in an all
  uppercase heading
• Note Quotes required, regardless, for headings
  requiring a space
• Appendix

29
Null Value Criteria in Oracle

• SELECT Doc_ID FROM Doctor WHERE
  Annual_Bonus IS NULL
• Note Use IS NULL and not NULL in the
  criteria expression.
• Appendix

30
Like Command

• SELECT FROM Billing where Addr LIKE N.
  Allen
• Note LIKE should be used for criteria
  comparisons whenever a wild card is used within
  the criteria.
• Appendix

31
Table Name Designation in SQL

• Note When the same attribute is included in
  two or more tables, it is necessary to indicate
  both the table name and the attribute name
• WHERE Adviser.AdvID Student.AdvID
• Note The period separates the table and
  attribute names. Note that AdvID appears in both
  tables in this example.
• Appendix

32
Updating Data in a Table

• UPDATE Inventory
• SET PackName Manta
• WHERE PackID DB33
• Note Revise name for ID DB33 Package
• Appendix

33
Adding a Record to a Table

• INSERT INTO Employee
• VALUES (402, Sanders, Robert, 2056)
• Note The order of values must match the table
  structure. If only data for certain fields are
  to be entered, list the fields in parenthesis
  between the table name and the word VALUES.
• Appendix

34
Updating Selected Records

• UPDATE Package
• SET Packcost Packcost 10
• WHERE Packtype Word Processing
• Note Records that do not meet the criteria
  will not be included in the update process.
• Appendix

35
SQL Wildcard Examples in Oracle

• Takes the place of all characters before or
  after it.
• ville All cities ending in ville
• C All last names beginning with C
• _ (underline) takes the place of 1 character
• T_M Will match any 3-character values
  beginning with a T and ending with a M
• Appendix

36
Date Comparisons and Like Values

• WHERE Order_Date gt 24-OCT-99
• Note This will be a criteria match for order
  dates that occurred later than 10/24/99. Notice
  that the date has an apostrophe before and after
  it.
• WHERE Last_Name LIKE son
• Appendix

37
Deleting a Record

• DELETE FROM Employee
• WHERE EmpPhone 8716
• Note Deletes record(s) matching the
• condition. Values must be
• unique or multiple records will
• be deleted.
• Appendix

38
Column Deletion

• ALTER TABLE Employee
• DROP Address
• Appendix

39
Column Width Revision

• ALTER TABLE Employee
• CHANGE Column Last_Name
• TO VarChar2(22)
• Appendix

40
Column Addition

• ALTER TABLE Employee
• ADD Address Varchar2(20)
• Appendix

41
Table Deletion

• DROP TABLE Employee
• Appendix

42
View Creation

• CREATE VIEW Brown
• AS SELECT EmpID, EmpName
• FROM Employee
• Note This view will permit the user to view
  data from the EmpID and EmpName columns in the
  Employee table.
• Appendix

43
View Deletion

• DROP VIEW Brown
• Appendix

44
Updating All Records No Condition

• UPDATE Employee
• SET Salary Salary 1.05
• Appendix

45
Averaging With a Condition

• SELECT AVG(Salary)
• FROM Employee
• WHERE Dept Accounting
• Appendix

46
Averaging With Grouping

• SELECT DeptName, Avg(Salary)
• FROM Employee
• GROUP BY DeptName
• ORDER BY DeptName
• Appendix

47
Major SQL System Commands

• Saving a script
• Save a\filename.ext
• Saving a session to a file
• Spool a\filename.txt
• Running a script stored as a .txt file
• _at_a\filename.txt
• Exit from session
• Exit
• Access program text editor
• Edit
• Appendix

48
Null Values in a Field

• UPDATE PC
• SET EmpNum to Null
• WHERE TagNum 37691
• Appendix

49
Commands Oracle

• DESCRIBE Student
• Note Provides a listing of fields and attribute
  types for the Student table.
• RUN
• Note Executes a program.
• SELECT FROM TABS
• Note Gets a listing of tables in the database
• SAVE C\Filename.ext
• Note Saves a program
• Appendix

50
Comments - Oracle

• - -
• Note Place at the beginning of the comment line
  for one-line comments
• / /
• Note Place multi-line comment between these
  notations.
• Appendix

51
Comments?
52
Appendix
53
Appendix