Data, data everywhere…take control of your database and learn to manipulate it with the tried-and-true Structured Query Language (SQL) in Oracle Database 11g. Whether you're an IT pro who wants to add Oracle DBA and SQL querying to your tool belt, a DBA in another technology who wants to dive into Oracle, or a newcomer to relational databases who yearns to learn SQL, this course will give you what you need to reach your goal.

In this course, you'll gain SQL proficiency and be able to take control of your data. Best of all, you can apply the skills you learn not only to Oracle, but to almost any RDBMS platform! And, trainer Tim Warner gives you all sorts of cool supplemental learning references -– all of the SQL work files can be downloaded free at
nuggetlab.com, and the course is filled with recommendations for white papers, Web sites and more.

Once you've finished this Nugget course, you'll be fully prepared to pass the 1Z0-051 SQL Fundamentals exam – and be ready to move on to the next in line for the Oracle DBA OCA track. More importantly, you'll gain an extremely useful and portable tool that will help you make the most of your database.

1.
Course Introduction
(18 min)

In this nugget, we start by defining what Oracle is and how it fits into the concepts of the database, relational database, and relational database management system. Next we perform a deep-dive into the Oracle certification program, learning exactly how the current course maps to the Oracle Certified Associate (OCA) credential. We also provide tips and tricks for getting the most out of the training, and give suggestions on how to pass the 1Z0-051 exam with a minimum of muss or fuss.

2.
About SQL
(41 min)

In this nugget, we set the stage for the rest of the training. Here we formally define the Structured Query Language (SQL), explaining its basic modes of operation, as well as how Oracle's implementation of SQL stacks up to the ANSI/ISO standard as well as other vendors' implementations. We complete this nugget with quick-start tutorials on how to use SQL*Plus as well as Oracle SQL Developer.

3.
Retrieving Data By Using the SELECT Statement
(38 min)

The SELECT statement is the universal workhorse of SQL. In this nugget we discuss both the theory and practice of using SELECT to retrieve query results from Oracle database.

4.
Restricting and Sorting Data
(37 min)

In this nugget we continue our discussion of the SQL SELECT statement, this time learning how to pare down our query results by employing the powerful WHERE clause. We also learn how to sort our query results by leveraging the ORDER BY statement, and finally begin to write more dynamic, interactive SQL by using substitution variables.

5.
Using Single-Row Functions to Customize Output
(36 min)

In this nugget we introduce the concept of the function, both from a general mathematics standpoint, and then with sharper focus on Oracle Database 11g. We spend our time learning to master the basic character, number, and date functions that you'll be expected to understand on the SQL Fundamentals certification exam.

6.
Reporting Aggregated Data Using the Group Functions
(30 min)

In this nugget we apply many of the function-related principles we picked up in the previous nugget. Here we learn how to aggregate data across multiple table rows by employing both grouping functions as well as the GROUP BY SQL statement. We also will understand how to filter grouped rows by using the HAVING clause.

7.
PL/SQL Basics
(36 min)

In this nugget we truly round out our Oracle SQL skills by conducting a survey of PL/SQL, Oracle's procedural programming extensions to the Structured Query Language. By the end of this nugget you will not only understand the basic nomenclature and syntax of PL/SQL, but you'll also be able to write basic PL/SQL blocks, both the anonymous and named varieties.

8.
Displaying Data from Multiple Tables
(38 min)

In this nugget we truly begin to see the great beauty of the relational database model. Here we learn how to query across multiple related tables by using joins. In addition to learning all about the supported join types in Oracle Database, we will also flirt around with the concept of constraints, two of which (primary key and foreign key) are crucial to our understanding of the relational model.

9.
Using Subqueries
(24 min)

In this nugget we broaden and deepen our usage of the SELECT statement by learning how to embed one SELECT inside of another SELECT; this is called a subquery. As we'll learn, subqueries are useful for data load scenarios as well as when we aren't quite sure which row(s) we need to evaluate in a WHERE clause. By the end of this nugget you will not only understand how subqueries work, but you'll be able to write both single-row and multi-row subqueries in either the simple or correlated processing formats.

10.
Using the Set Operators
(23 min)

SQL is a set-oriented database access language. In this nugget we learn various methods of combining multiple result sets. Here we master the use of the UNION, MINUS, and INTERSECT keywords in combining multiple queries into a single result set.

11.
Using DDL Statements to Create and Manage Tables
(46 min)

In this nugget we formally treat the subject of creating, altering, and dropping database objects; this collection of SQL commands is formally called Data Definition Language, or DDL. In this nugget we start with a high-level overview of the major database objects. We then follow up with learning how to design and create tables, and then conclude with a consideration of the importance and use of integrity constraints in database tables and views.

12.
Creating Other Schema Objects Part 1
(31 min)

In this nugget we continue the discussion we opened in the previous nugget on DDL statements in Oracle Database 11g. Here we learn how to design and implement sequences and views.

13.
Creating Other Schema Objects Part 2
(34 min)

Indexes, when properly designed and used, can dramatically speed up query performance in an RDBMS. In this nugget we cover the fundamental points concerning indexes. We also learn just what synonyms are--not in the English grammar context, but instead in the Oracle database nomenclature.

14.
Manipulating Data Part 1
(30 min)

Thus far in the course we have covered the use of SELECT and the DDL command suite. Formally speaking, SELECT is actually one of the Data Manipulation Language (DML) statements in SQL. In this nugget we cover two of the major DML players, namely the INSERT and UPDATE statements.

15.
Manipulating Data Part 2
(30 min)

In this nugget we "book end" our discussion from Nugget #14, wrapping up our consideration of basic DML by investigating the finer points of the DELETE statement. We also define and discuss what database transactions are as well as how to control their operation in Oracle Database 11g.

16.
Basic Oracle Database Security
(35 min)

Relational database security is a huge topic and can constitute a very lucrative career for those who are so talented and interested. In this nugget we survey some of the major principles behind user account security in Oracle Database 11g. In particular, we learn how to create and administer Oracle user accounts. We then delve into access control, learning how to leverage the GRANT and REVOKE statements. We complete this nugget with some best-practice advice on leveraging database roles to make our database security more efficient and effective.

17.
Accessing the Data Dictionary
(22 min)

The data dictionary in Oracle Database 11g is a read-only collection of tables that stores metadata about the database. As such, an understanding of the data dictionary is crucial for anyone who presents himself or herself as an Oracle DBA. By the conclusion of this nugget you will not only be able to intelligently discuss the data dictionary, but you will also know how to query the data dictionary views as an ordinary schema (user) and as a DBA.

18.
Referencing the Oracle Documentation
(22 min)

While Oracle's documentation library is presented to the public at no charge, Oracle keeps much of the "good stuff" behind a paywall for, well, paying Oracle Support customers. The purpose of this nugget is to deeply familiarize you with the free and excellently written/organized corpus of technical documentation in the Oracle Web site. This is a best-kept secret of Oracle, and you should make the official Oracle Documentation a daily read as you build your skill set up and out.

19.
Using Oracle Flashback Query
(24 min)

In the life of the DBA, restoring data that was accidentally or intentionally dropped or deleted can be a fairly regular occurrence. Here we turn to the nifty Flashback technology in Oracle Database 11g to learn first of all how Flashback works, and then how we can leverage Flashback to recover individual rows or entire tables from the ether. We'll also spend some time with the Oracle Recycle Bin.

20.
Query Optimization Basics
(25 min)

In this concluding nugget of the course we apply everything we've learned over the course of this training and pick up some practical advice on how to tune and optimize our SQL queries. To start with we will explain how Oracle parses (interprets) SQL. We will then practice using the EXPLAIN PLAN statement to view the fine details of Oracle's query execution plans. Finally, we finish up with a "laundry list" of strategies for customizing how Oracle builds its execution plans.

Please help us improve by sharing your feedback on training courses and videos. For customer service questions, please contact our support team. The views expressed in comments reflect those of the author and not of CBT Nuggets. We reserve the right to remove comments that do not adhere to our community standards.

Community Standards

We encourage you to share your wisdom, opinions, and questions with the CBT Nuggets community. To keep things civil, we have established the following policy.

We reserve the right not to post comments that:
contain obscene, indecent, or profane language; contain threats or defamatory statements; contain personal attacks; contain hate speech directed at race, color, sex, sexual orientation, national origin, ethnicity, age, religion, or disability;
contributes to a hostile atmosphere; or promotes or endorses services or products. Non-commercial links, if relevant to the topic, are acceptable.
Comments are not moderated, however, all comments will automatically be filtered for content that might violate our comment policies. If your comment is flagged by our filter, it will not be published.

We will be continually monitoring published comments and any content that violates our policies will be removed. Users who repeatedly violate our comments policy may be prohibited from commenting.

Entry

10

Hours

20

Videos

Entry10 hrs20 videos

COURSE RATING

Basic Plan Features

Speed Control

Included in this course

Play videos at a faster or slower pace.

Bookmarks

Included in this course

Pick up where you left off watching a video.

Notes

Included in this course

Jot down information to refer back to at a later time.

Closed Captions

Follow what the trainers are saying with ease.

NuggetLab

Included in this course

Files/materials that supplement the video training

Premium Plan Features

Practice Exams

Included in this course

These practice tests help you review your knowledge and prepare you for exams.

Selftest Oracle 1Z0-051

Transcender Oracle Cert-1Z0-051

Virtual Lab

Use a virtual environment to reinforce what you are learning and get hands-on experience.

Unless otherwise stated all references in this License Agreement to “training videos” or to "videos" includes both individual videos within a series, entire series, series packages, and streaming subscription access to CBT Nuggets content. All references to CBT or CBT Nuggets shall mean CBT Nuggets, LLC, a Delaware limited liability company located at 44 Country Club Road, Ste. 150, Eugene, Oregon.

A CBT Nuggets Learner is defined as a single user license and the single user license must be connected to a named user. The email address attached to the single user license must be specifically associated solely with that individual CBT Nuggets Learner. Accounts may purchase multiple learners, and each learner is assigned a single license. Each single user license is considered a CBT Nuggets Learner, and is expressly governed by the terms of this License Agreement.

GRANT OF LICENSE. CBT Nuggets grants you a non-transferable, non-exclusive license to use the training videos contained in this package or streaming subscription access to CBT content (the "Products"), solely for internal use by your business or for your own personal use. You may not copy, reproduce, reverse engineer, translate, port, modify or make derivative works of the Products without the express consent of CBT. You may not rent, disclose, publish, sell, assign, lease, sublicense, market, distribute, or transfer the Products or use them in any manner not expressly authorized by this Agreement without the express consent of CBT. You shall not derive or attempt to derive the source code, source files or structure of all or any portion of the Products by reverse engineering, disassembly, decompilation or any other means. You do not receive any, and CBT Nuggets retains all exclusive ownership rights, title and interests in the Products, including, but not limited to ownership rights in all copyrights, trademarks, service marks, or trade secrets. The Products are copyrighted and may not be copied, distributed or reproduced in any form, in whole or in part even if modified or merged with other Products. You shall not alter or remove any copyright notice or proprietary notice or legend contained in or on the Products. You shall take all reasonable steps and precautions to ensure that your use of the Products is in strict compliance with this License Agreement. Upon your discovery and/or the request of CBT, you agree to promptly remedy any violation of this License Agreement and to provide CBT with sufficient evidence that you are in compliance with this License Agreement.

TERMINATION OF LICENSE. Once any applicable subscription period has concluded, the license granted by this Agreement shall immediately terminate and you shall have no further right to access, review or use in any manner any CBT Nuggets content. CBT reserves the right to terminate your subscription if, at its sole discretion, CBT believes you are in violation of this License Agreement or if CBT believes you have exceeded reasonable usage. Upon the occurrence of any of these termination events, no refund will be made of any amounts previously paid to CBT.

DISCLAIMER OF WARRANTY AND LIABILITY. The Products are provided to you on an "as is" and "with all faults" basis. You assume the entire risk of loss in using the products. The Products are complex and may contain some nonconformities, defects or errors. CBT Nuggets does not warrant that the Products will meet your needs, "expectations or intended use," that operations of the products will be error-free or uninterrupted, or that all nonconformities can or will be corrected. CBT Nuggets makes and user receives no warranty, whether express or implied, and all warranties of merchantability, title, and fitness for any particular purpose are expressly excluded. In no event shall CBT Nuggets be liable to you or any third party for any damages, claims, losses, expenses, liabilities or costs arising out of, incurred because of, or related to the Products and/or this License Agreement (including, without limitation, compensatory, incidental, indirect, special, consequential or exemplary damages, lost profits, lost sales or business, expenditures, investments, or commitments in connection with any business, loss of any goodwill, or damages resulting from lost data or inability to use data), including attorney fees, irrespective of whether CBT Nuggets has been informed of, knew of, or should have known of the likelihood of such damages. This limitation applies to all causes of action in the aggregate including without limitation breach of contract, breach of warranty, negligence, strict liability, misrepresentation, and other torts. In no event shall CBT Nuggets' liability to you or any third party exceed $100.00.

REMEDIES. In the event of any breach of the terms of the Agreement CBT reserves the right to seek and recover damages for such breach, including but not limited to damages for copyright infringement, trademark infringement and for unauthorized use of CBT content. CBT also reserves the right to seek and obtain injunctive relief in addition to all other remedies at law or in equity.

APPLICABILITY. The provisions of this License Agreement will prevail over any conflicting provisions in any purchase order or other document issued by you in connection with your use of the Products.

MISCELLANEOUS. This is the exclusive Agreement between CBT Nuggets and you regarding its subject matter. You may not assign any part of this Agreement without CBT Nuggets' prior written consent. This Agreement shall be governed by the laws of the State of Oregon and venue of any legal proceeding shall be in Lane County, Oregon. In any proceeding to enforce or interpret this Agreement, the prevailing party shall be entitled to recover from the losing party reasonable attorney fees, costs and expenses incurred by the prevailing party before and at any trial, arbitration, bankruptcy or other proceeding and in any appeal or review. You are fully responsible for determining whether any form of tax is due relating to the Products or transactions, and you shall pay any sales tax, use tax, excise, duty or any other form of tax relating to the Products or transactions. If any provision of this Agreement is declared invalid or unenforceable, the remaining provisions of this Agreement shall remain in effect. Any notice to CBT under this Agreement shall be delivered by U.S. certified mail, return receipt requested, or by overnight courier to CBT Nuggets at the following address: Attn: Matthew L. Bromley, 44 Club Rd Suite 150, Eugene, OR 97401 or such other address as CBT may designate.

CBT Nuggets reserves the right, in its sole discretion, to change, modify, add, or remove all or part of the License Agreement at any time, with or without notice.

CBT Nuggets Billing Agreement

By entering into a Billing Agreement with CBT Nuggets, you authorize CBT Nuggets to use automatic billing and to charge your credit card on a recurring basis.

You agree to pay subscription charges on a monthly, semi-annual or annual term based on your subscription, under the following terms and conditions:

All payments are non-refundable and charges made to the credit card or payment method under this agreement will constitute in effect a "sales receipt" and confirmation that services were rendered and received;

To terminate the recurring billing process and/or arrange for an alternative method of payment, you must notify CBT Nuggets at least 24 hours prior to the end of the billing cycle;

You will not dispute CBT Nugget’s recurring billing charges with your credit card issuer so long as the amount in question was for periods prior to the receipt and acknowledgement of a written request to cancel your account or cancel individual licenses on your account.

You guarantee and warrant that you are the legal cardholder for the credit card associated with the account, and that you are legally authorized to enter into this recurring billing agreement.

You agree to indemnify, defend and hold CBT Nuggets harmless, against any liability pursuant to this authorization.

You agree that CBT Nuggets is not obligated to verify or confirm the amount for the purpose of processing these types of payments. You acknowledge and agree that Recurring Payments may be variable and scheduled to occur at certain times.

If your payment requires a currency conversion by us, the amount of the currency conversion fee will be determined at the time of your payment. You acknowledge that the exchange rate determined at the time of each payment transaction will differ and you agree to the future execution of payments being based on fluctuating exchange rates.

CBT Nuggets reserves the right, in its sole discretion, to change, modify, add, or remove all or part of the Billing Agreement at any time, with or without notice.

CBT Nuggets Privacy Policy

Data Collection

P3P policies declare the data they collect in groups (also referred to as "statements"). This policy contains 4 data groups. The data practices of each group will be explained separately.

Group "Access log information"

We collect the following information:

Click-stream data

Search terms

Last URI requested by the user

User agent information

This data will be used for the following purposes:

Completion and support of the current activity.

Web site and system administration.

Research and development.

This data will be used only by ourselves.

The following explanation is provided for why this data is collected:

Our Web server collects access logs containing this information.

Group "Cookies"

We collect the following information:

HTTP cookies

Unique identifiers

This data will be used for the following purposes:

Research and development.

One-time tailoring.

This data will be used only by ourselves.

The following explanation is provided for why this data is collected:

Cookies are used to track visitors to our site, so we can better understand what portions of our site best serve you. We also use cookies to allow our server to maintain information about the contents of your shopping cart.

Group "Transaction info (required)"

We collect the following information:

Physical contact information

Online contact information

Purchase information

Search terms

User's Name

Name of User's Employer

Transaction history

Computer information

This data will be used for the following purposes:

Completion and support of the current activity.

Research and development.

This data will be used only by ourselves. In addition, the following types of entities will receive this information:

Delivery services.

The following explanation is provided for why this data is collected:

Information we collect in order to process your purchase.

Group "Transaction info (optional)"

We collect the following information:

Name of User's Employer

User's Home Contact Information

User's Business Contact Information

Contact Information for the Organization

Organization Name

Department or division of organization

This data will be used for the following purposes:

Contacting visitors for marketing of services or products.

This data will be used only by ourselves. In addition, the following types of entities will receive this information:

Delivery services.

The following explanation is provided for why this data is collected:

Information we collect in order to process your purchase.

Cookies

Cookies are a technology which can be used to provide you with tailored information from a Web site. A cookie is an element of data that a Web site can send to your browser, which may then store it on your system. You can set your browser to notify you when you receive a cookie, giving you the chance to decide whether to accept it.

In some cases we may collect cookies from 3rd parties, such as Google or DoubleClick.

Compact Policy Summary

The following table explains the meaning of each field in the compact policy.

Field

Meaning

CP=

This is the compact policy header; it indicates that what follows is a P3P compact policy.

CAO

Access is available to contact and other information.

CURa

The data is used for completion of the current activity.

ADMa

The data is used for site administration.

DEVa

The data is used for research and development.

TAIa

The data is used for tailoring the site.

CONa

The data is used for contacting the user.

OUR

The data is given only to ourselves.

DELa

The data is given to delivery services.

BUS

Our business practices specify how long the data will be kept.

IND

The data will be kept indefinitely.

PHY

Physical contact information is collected.

ONL

Online contact information is collected.

UNI

Unique identifiers are collected.

PUR

Purchase information is collected.

COM

Computer information is collected.

NAV

Navigation and clickstream data is collected.

INT

Interactive data is collected.

DEM

Demographic and socioeconomic data is collected.

STA

State-management data is collected

The compact policy is sent by the Web server along with the cookies it describes. For more information, see the P3P deployment guide at http://www.w3.org/TR/p3pdeployment.

Policy Evaluation

Microsoft Internet Explorer 6 will evaluate this policy's compact policy whenever it is used with a cookie. The actions IE will take depend on what privacy level the user has selected in their browser (Low, Medium, Medium High, or High; the default is Medium. In addition, IE will examine whether the cookie's policy is considered satisfactory or unsatisfactory, whether the cookie is a session cookie or a persistent cookie, and whether the cookie is used in a first-party or third-party context. This section will attempt to evaluate this policy's compact policy against Microsoft's stated behavior for IE6.

Note: this evaluation is currently experimental and should not be considered a substitute for testing with a real Web browser.

Unsatisfactory policy: this compact policy is considered unsatisfactory according to the rules defined by Internet Explorer 6. The behavior of Internet Explorer 6 regarding cookies set under this compact policy is as follows:

First-party usage

Third-party usage

Persistent Cookies

Low: Policy satisfactory at this level; cookies will be accepted.

Medium: Opt-out is not provided for all unsatisfactory purposes and recipients, so the cookie will be downgraded to a session cookie.

Medium High: No opt-out is provided, so the cookie will be blocked.

High: Since opt-in is not required, the cookie will be blocked.

Low: Opt-out is not provided for all unsatisfactory purposes and recipients, so the cookie will be downgraded to a session cookie.

Medium: Opt-out is not provided for all unsatisfactory purposes and recipients, so the cookie will be blocked.

Medium High: Since opt-in is not required, the cookie will be blocked.

High: Since opt-in is not required, the cookie will be blocked.

Session Cookies

Low: Policy satisfactory at this level; cookies will be accepted.

Medium: Policy satisfactory at this level; cookies will be accepted.

Medium High: Policy satisfactory at this level; cookies will be accepted.

High: Since opt-in is not required, the cookie will be blocked.

Low: Policy satisfactory at this level; cookies will be accepted.

Medium: Opt-out is not provided for all unsatisfactory purposes and recipients, so the cookie will be blocked.

Medium High: Since opt-in is not required, the cookie will be blocked.

High: Since opt-in is not required, the cookie will be blocked.

A policy which is considered unsatisfactory by Internet Explorer 6 contains certain categories of data which are used or shared in a particular manner. This policy is placed in the unsatisfactory category, because the following categories of data are associated with this policy's cookies:

Physical contact information is collected.

Online contact information is collected.

In addition, the data is used in the following manner, marking the policy as unsatisfactory:

The data is used for contacting the user.

Note that allowing an opt-out will make this policy acceptable under the Low and Medium settings, and under Medium High for first-party cookie usage. At the High setting, and at the Medium High setting for third-party cookies, all of these data uses must be opt-in for the policy to be considered satisfactory.

Social Media

Opinions expressed on this website and in any corresponding comments are the personal
opinions of the original contributing authors, not of CBT Nuggets, LLC. The content is
provided for entertainment, educational, or informational purposes only and is not meant
to be an endorsement or representation by CBT Nuggets, LLC or any other party. Visitors
may post reviews, comments, photos and other content, submit suggestions, questions, or
other information, so long as the content is not illegal, obscene, threatening, defamatory,
invasive of privacy, infringing of intellectual property rights, or other injurious to
third parties. You may not use a false email address, impersonate any person or entity,
or otherwise mislead as to the origin of your content and/or your identity.

This site is available to the public. No information you consider confidential or desire
to remain private should be posted to this site. By posting you agree to be solely responsible
for the content of all information you contribute, link to, or otherwise upload to the website
and release CBT Nuggets, LLC from any liability related to your use of the posting in its entirety.
If you choose to post, you grant CBT Nuggets a non-exclusive, royalty-free, perpetual, irrevocable
and fully sub-licensable right to use, reproduce, modify adapt, publish, translate, distribute,
and display such content throughout the world in any media. This includes, but is not limited to,
use of website url, links, pictures, movies, and commentary.

Posts are moderated for relevance and suitability and are approved at the discretion of the moderator.