• Accelerated Fundamentals of SQL Using Oracle


  •   
  • FileName: cs335.pdf [read-online]
    • Abstract: Accelerated Fundamentals of SQL Using OracleWhat You Will Learn...................................................................................................................... 1Getting Started with Oracle and SQL*Plus .................................................................................... 2

Download the ebook

Accelerated Fundamentals of SQL Using Oracle
What You Will Learn...................................................................................................................... 1
Getting Started with Oracle and SQL*Plus .................................................................................... 2
Logging on to SQL*Plus .......................................................................................................... 3
Rules for Using SQL*Plus........................................................................................................ 4
Selecting Data in Oracle ................................................................................................................. 5
Learning about Your Tables ..................................................................................................... 5
Running Basic select Statements ........................................................................................... 6
Restricting Your Results by Using where Clauses ................................................................... 7
Sorting the Results of Your Queries ....................................................................................... 10
Using Column Aliases ............................................................................................................ 11
Concatenating Columns .......................................................................................................... 11
Manipulating NULL Values ................................................................................................... 12
Selecting Data from Another User’s Table............................................................................. 13
Understanding SQL*Plus Commands .......................................................................................... 14
Issuing SQL*Plus Commands ................................................................................................ 14
Saving SQL Statements and Output ....................................................................................... 14
Editing SQL Statements in SQL*Plus .................................................................................... 15
Generating Readable Output................................................................................................... 16
Customizing the SQL*Plus Interface...................................................................................... 17
Getting Help in SQL*Plus ...................................................................................................... 18
Connecting to and Disconnecting from Oracle....................................................................... 18
Exercise 1: Running SQL and SQL*Plus Commands.................................................................. 19
Working with SQL Functions....................................................................................................... 21
Using Single-Row Functions .................................................................................................. 21
Working with Date Data ......................................................................................................... 24
Using Group Functions ........................................................................................................... 29
Exercise 2: Working with SQL Functions .................................................................................... 33
Creating Tables ............................................................................................................................. 36
Understanding the create table Command ........................................................................ 36
Choosing a Table Name.......................................................................................................... 36
Understanding Oracle’s Datatypes ......................................................................................... 37
Creating a Simple Table.......................................................................................................... 39
Adding Records to a Table ..................................................................................................... 40
Creating a Copy of an Existing Table..................................................................................... 40
Adding Comments .................................................................................................................. 41
Assigning Default Values ....................................................................................................... 42
Working with Constraints ....................................................................................................... 42
Accelerated Fundamentals of SQL Using Oracle
www.micros.umsl.edu
Creating Tables with Constraints............................................................................................ 46
Exercise 3: Creating Tables .......................................................................................................... 49
Modifying Tables.......................................................................................................................... 51
Adding Columns ..................................................................................................................... 51
Removing Columns ................................................................................................................ 52
Modifying Column Definitions............................................................................................... 52
Adding and Dropping Constraints .......................................................................................... 54
Renaming Tables .................................................................................................................... 55
Renaming Columns................................................................................................................. 55
Exercise 4: Modifying Tables....................................................................................................... 56
Working with Users ...................................................................................................................... 58
Creating Users......................................................................................................................... 58
Starting a New User Session................................................................................................... 59
Working with System Privileges ............................................................................................ 60
Working with Object Privileges.............................................................................................. 64
Granting a Public Privilege..................................................................................................... 67
Using Roles to Simplify User Management ........................................................................... 67
Viewing Information about User’s Privileges and Roles ....................................................... 71
Exercise 5: Working with Users ................................................................................................... 73
Writing Scripts .............................................................................................................................. 75
Running a Script File .............................................................................................................. 75
Editing a Script File ................................................................................................................ 76
Using Variables in Script Files ............................................................................................... 78
Writing Complex Scripts ........................................................................................................ 79
Appendix A: SQL*Plus Commands ............................................................................................. 80
Running SQL Commands ....................................................................................................... 80
Editing SQL Statements.......................................................................................................... 81
Generating Readable Output................................................................................................... 84
Customizing the SQL*Plus Interface...................................................................................... 89
Appendix B: Single-Row SQL Functions..................................................................................... 93
Numeric Functions.................................................................................................................. 93
Character Functions ................................................................................................................ 95
Date Functions ........................................................................................................................ 98
Appendix C: Solutions to Exercises 1-5 ..................................................................................... 100
Accelerated Fundamentals of SQL Using Oracle
www.micros.umsl.edu
What You Will Learn
This course covers the fundamentals of SQL in an Oracle environment for users who already
have intermediate SQL experience using another database system, such as Microsoft SQL
Server. Specifically, this course assumes prior knowledge and experience with a variety of
introductory and intermediate SQL commands, such as select, insert, delete, and
create table statements. In addition, you should have experience selecting data from multiple
tables, using subqueries and joins.
During this class, you will practice running some familiar SQL commands in an Oracle
environment, while learning about extensions to standard SQL that are specific to Oracle or vary
between database management systems. In the latter part of the class, you also will learn about
several advanced uses of SQL in Oracle, such as working with users and writing SQL scripts.
Specifically, the topics covered in this course include the following:
• Using Oracle’s SQL*Plus environment to run standard SQL commands, such as select
statements to view table data.
• Understanding SQL*Plus commands that can be useful in editing SQL statements,
customizing the Oracle environment, and more.
• Using single-row and group functions that may not be available in all database systems.
• Creating new tables, including understanding the datatypes available in Oracle and
applying constraints.
• Modifying existing tables, including renaming tables, adding and removing columns, and
adding and dropping constraints.
• Understanding how to control access to your database by creating users and assigning
roles and privileges.
• Writing scripts to run frequently used SQL commands.
While some review is necessary to practice running SQL commands in Oracle, this review will
be conducted at an accelerated pace, and not all standard SQL commands and techniques will be
discussed. For example, in the interest of time, some fundamental techniques, such as specifying
multiple criteria in a select statement, creating joins and subqueries, and running data
manipulation commands to update and delete records, are not reviewed in this course.
Accelerated Fundamentals of SQL Using Oracle 1
www.micros.umsl.edu


Use: 0.378