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. It is useful for all who want to utilize the full power of SQL to increase their productivity, including programmers and application designers, as well as database administrators and advanced power users who write their own ad hoc queries.

Isn't SQL a simple language to learn?

The syntax of SQL is simple, but using SQL to its maximum power can be quite difficult. This course gives you the in-depth understanding of the language that's necessary to take full advantage of SQL's capabilities.

It can be difficult to guarantee that a SQL statement will produce the correct result. This course focuses heavily on the pitfalls and dangers of writing seemingly correct queries that nevertheless potentially could produce wrong results.

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 593: Oracle^® Database 10g: A Comprehensive Hands-On Introduction, 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 to increase their productivity, including programmers and application designers, database administrators and advanced power users who write their own ad hoc queries. Typical participants include:

• Programmers, analysts, developers, designers, consultants and others who design and write Oracle applications
• DBAs who want to become proficient in querying and manipulating an Oracle database with SQL
• Those who are familiar with SQL 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 is the main topic of this course- SQL or SQL*Plus?

The main focus of the course is SQL and SELECT statements. SQL*Plus is 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. You may use either SQL*Plus or SQL*Plus Worksheet for the hands-on exercises.

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.

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.

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.

What version of Oracle is used?

This course is currently running on Oracle11g, 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.
• 593: Oracle® Database 10g: A Comprehensive Hands-On Introduction,  offers a comprehensive introduction to the relational features of Oracle Database 10g.

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 

US Dates