Developing SQL Queries for Oracle^® Databases: Hands-On

What is this course about?

In this course, you learn to write robust, reliable and efficient SQL queries to retrieve data from database tables. Once you master the full power of SQL queries, it will enable you to solve many complex business problems easily, efficiently and elegantly with only a few lines of SQL code. This is useful for all who want to utilize the full power of SQL to increase their productivity.

Isn't SQL a simple language to learn?

The syntax of SQL is simple, and many programmers are unaware of the huge potential this language has. This course provides an in-depth understanding enabling you to take full advantage of SQL's capabilities.

The hard part of SQL is to guarantee that a query will always produce a correct result. This course describes typical errors and pitfalls that could potentially produce wrong results. Being aware of those pitfalls will help you in writing correct and robust queries.

What SQL background should I have?

This is not an introductory course. You should not take this course if you have no familiarity with SQL. Prior knowledge of SQL and Oracle RDBMS at the level of Course 925: SQL Programming Language: A Comprehensive Hands-On Introduction, or Course 926: Oracle^® Database 11g: A Comprehensive Hands-On Introduction, is assumed.

Who will benefit from this course?

This course is valuable for all who want to utilize the full power of SQL. Typical participants include:

• Programmers, analysts, developers, designers, consultants and others who design and write Oracle applications
• Business analysts and power users who need to extract data from their Oracle databases
• DBAs who want to become proficient in querying and manipulating an Oracle database with SQL
• Those who are familiar with SQL in general but wish to learn the special features of Oracle's SQL dialect
• Experienced data processing professionals working with traditional database systems-hierarchical or network-who are now moving to the relational world

Which tool is used for entering and running SQL queries?

The main focus of the course is the SELECT statement itself, independent on which client tool is used. When doing the exercises, students have the choice of using SQL*Plus or SQL Developer as the interactive tool. The course assumes that you are familiar with the basics of how to enter and execute queries in one of those or in a similar tool.

Does this course cover the ANSI SQL standard?

This course focuses on the advantages of Oracle's implementation of SQL. Oracle has some powerful additional features that are not ANSI-standard compatible, but are extremely useful in a real-world environment. Starting with version 9.0, Oracle now supports the ANSI standard join syntax, in addition to the old non-standard syntax. A programmer should be familiar with both the ANSI syntax for new development, and the old syntax for maintenance of older programs. Another new feature of version 9.0 is full support for the ANSI standard CASE operator. The course covers this, as well as the Oracle proprietary DECODE function.

How much time is spent on each topic?

How much hands-on experience will I get?

You spend about 50% of the course doing hands-on exercises under guidance. Every concept addressed in the course is illustrated with both examples and hands-on exercises.

Which is the main topic of this course - SQL or SQL*Plus, or SQL Developer?

The main focus of this course is SQL and SELECT statements. SQL*Plus of SQL Developer are used as the interactive environment for running SQL statements. The course assumes that you are familiar with the basics of how to enter and execute queries in SQL*Plus or SQL Developer. You may use either SQL*Plus of SQL Developer for the hands-on exercises.

I mostly use frontend tools that generate SQL automatically; do I still need this course?

Most frontend tools that generate SQL statements require you to enhance the statements or add in your own statements in order to use the full capabilities of the tool. Therefore, the ability to write complex SQL statements will be a useful skill when working with such tools.

Is performance a main topic in this course?

Database performance issues become increasingly important as queries become more complex. This course shows how different queries can perform the same task and give identical results, but with very different performance. You learn how to choose the appropriate query to achieve the best performance of a given task. The details of performance and application tuning are covered extensively in Course 492: Application Development and Tuning for Oracle^® Databases: Hands-On.

What version of Oracle is used?

This course is currently running on Oracle11g Release 2, on the Windows platform. None of the topics in this course are operating system dependent. Everything you learn applies equally well to Oracle running on any OS platform.

Is this course useful if I'm working with an earlier version of Oracle?

The fundamentals of SQL are not version dependent. Most of the course material applies to earlier versions as well as Oracle 11g. Features that are available in later versions only are clearly identified, and in most cases workaraounds for achieving similar functionality in earlier versions are described. And even if you are currently using an older Oracle version, it will be useful for you to learn about new features which will be available once you upgrade.

How does this course relate to other Learning Tree courses?

• 382:  Relational Database Design, Tools and Techniques: Hands-On 
• 492: Application Development and Tuning for Oracle® Databases: Hands-On  shows you how to tune queries for optimal performance, in addition to various other topics which are relevant for application developers.
• 493: Oracle® PL/SQL Programming: Hands-On benefits those who will use PL/SQL to write stored procedures, functions and packages.

You also might be interested in these Learning Tree courses:

• 926: Oracle® Database 11g: A Comprehensive Hands-On Introduction 
• 927: Oracle® Database 11g Administration: Hands-On 
• 928: Hands-On Oracle® Database 11g: Backup, Recovery and Server Tuning 
• 925: SQL Programming Language: A Comprehensive Hands-On Introduction 

US Dates