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 utilise 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 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 utilise 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 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 front-end tools that generate SQL automatically; do I still need this course?

Most front-end 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.

How much time is spent on each topic?

How much hands-on experience will I get?

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

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-Onemphasises database concepts, including what relational databases are, why they are used, how to design and set up a relational database, and how a database is used in a client/server environment. It could be a predecessor to this course
• 492, Application Development and Tuning for Oracle® Databases: Hands-Onshows 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-Onbenefits 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.
• 594, Oracle® Database 10g Administration: Hands-On, provides the fundamental knowledge and skills to create and configure Oracle Database 10g, manage memory and storage effectively, and deploy essential procedures to maintain your organization's data.

You might also 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

Storbritannien

USA