Oracle SQL for beginners

Learn how to query and manage data in the Oracle Database Using SQL. Practical, concept-building examples and quizzes.
4.0 (32 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
348 students enrolled
$19
$50
62% off
Take This Course
  • Lectures 40
  • Length 8.5 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 2/2014 English

Course Description

Course Summary

This introductory course teaches you how to work with data in your Oracle Database using Oracle's proprietary SQL language. It starts with the basic overview of what SQL is all about and then covers basic queries and common things you might be expected to do on a job. We then proceed to learn complex queries and data management such as inserting/updating/deleting data.

All the SQL examples and the power point slides are available for download and there are a few exercises/queries at the end of each chapter for you to test your understanding. I'll try and add more each week. At the end of the course, we also discuss some of the frequently asked questions in Oracle SQL to assess our understanding of the course and various topics and to understand some of the frequently asked interview questions.

The course should take about 2 weeks to complete, with 2-3 hours dedicated each day to go through the examples and work them out yourself.

What are the requirements?

  • Access to a computer with 2-4 GB of RAM is preferred
  • any knowledge of prior programming would help, though not required
  • Basic knowledge of installing/unintsalling software and other usual system tasks

What am I going to get from this course?

  • Understand the different components of an Oracle Database and how it works
  • Connect to an Oracle Database and run queries from different tools or from a file
  • Write basic queries involving Select, where, group by and other constructs
  • Write complex queries involving multiple tables, joins, subqueries and other techniques
  • Manage data using DML (insert, delete, update) and transaction control statements
  • Answer most of the frequently asked questions that are asked in interviews :)

What is the target audience?

  • Oracle Database developers
  • Data analysts
  • Software developers (java/.net/python) interacting with the Oracle Database
  • BI and ETL developers

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction to the course and Promo video
Important stuff about the course
Preview
02:58
Section 2: Setting up the environment
09:43

In this video, we take a look at some of the commonly used software that are used when working with the Oracle Database. Here's the list.

Oracle Database Server

Command line client tools (SQL*Plus)

IDEs (SQL Developer, Quest for Oracle, SQL Navigator and so on.

We also discuss a new term (Client-Server) which you might hear many times in the future when working with Database development or when working with a lot of other software like web servers.

2 questions

Which of these components is the server in our database environment?

03:03

In this video, we look at a new interesting way to set up your development environment using Oracle Virtual box and Developer VM.

virtual box : https://www.virtualbox.org/wiki/Downloads

Database application development VM : http://www.oracle.com/technetwork/community/developer-vm/index.html#dbapp

15:53

In this video, we go through the steps to set up the accounts in the database. We'll look at 2 accounts. An administrative account called "SYS" and a "normal" database user account called "SCOTT"

10:22

In this video, we look at a couple of alternatives to installing the virtual machine. (download links are present in the slide attached to the first video)

1) Installing each component individually

2) SQLFiddle.com

SQL*Plus
3 questions
Section 3: (Select part 1) Let's write some SQL! Selecting data
16:32

In this chapter, we start writing very basic queries. Just the column list and the table. We also recap how to run scripts or SQL statements and see how you can see the structure of a table in any of the client tools.

3 questions

Basic Select and Describe

Data Types
Preview
08:34
Data types Quiz
2 questions
14:30

In this chapter we complete our understanding of the basic select statement by understanding a few more important concepts such as primary keys and column aliases. We also unlock a few other sample accounts that come with the Oracle Database, to be used in the upcoming chapters.

Chapter 3 Quiz
5 questions
Section 4: (select part-2) Grants, Filters, Groups and more.
17:01

In this chapter we learn that each database object is owned by one (and only one)database user. We discuss grants, privileges and how one user can access another's objects. We complete our schema set up by making sure each user in our sample database has read access on all the other users' tables.

36:02

In this video, we focus on filtering the data based on our needs, using the "where clause" in SQL. Programming exercises are in the materials section.

17:29

In this section, we discuss how to group and aggregate data and the in-built functions oracle provides that helps you group data.

15:01

In this chapter, we discuss some common errors and best practices when it comes to grouping and aggregating data. We complete the section by discussing how to filter on groups using the "HAVING" clause inside sql.

17:35

In this section, we learn how to sort data in Oracle using the order by clause.

We discuss the different ways possible and finally some good practises to keep in mind when sorting.

Section 5: (select part-3) Oracle functions and SQL
09:04

This video has a brief overview of Oracle functions and how they are generally used.

We also take a look at the dummy table DUAL, which can be used to test out expressions/calculations.

21:04

In this video, we discuss two of Oracle's most frequently used function types, Character(string) and Numeric functions.

21:32

In this section, we discuss some of the most commonly used Oracle date and time functions. We also understand how "function overloading" practically works. At the end, we look at two real-world examples of how we often need to use multiple functions together to achieve the desired result.

24:48

In this section, we discuss various oracle functions that help us easily convert data from one data type to another and functions that help us format data.

List of Oracle 11gr2 conversion functions

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF20034

14:58

In this section, we discuss Oracle's functions that are used to deal with nulls. We look at three of the most commonly used functions. NVL, NVL2 and COALESCE.

13:32

We complete our discussion of Oracle functions by looking at DECODE and CASE functions, which help us write conditional logic in SQL

Oracle Functions - Programming exercises
4 pages
Section 6: (Select part-4) Joins, Sub queries and more advanced SQL
06:22

This video has a brief overview of Normalization and the problems that would occur when data is not normalized in a database.

More information here.

http://en.wikipedia.org/wiki/Database_normalization

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

17:35

In this video, we start learning about joins. We look at how to identify the join columns and discuss inner-joins, which you can use to get matching data from 2 or more tables.

12:45

In this video, we continue our discussion of inner joins and understand how to identify the right join columns. We also take a look at two special types of joins, the self-join and the cartesian-join.

14:28

In this section, we discuss the three kinds of inner joins (left outer join, right outer join and full outer join) and understand when we need to use full outer joins, instead of inner joins.

08:22

In this video, we'll take a look at ANSI syntax for inner joins, left and rigt outer joins and understand why the choice for this course was to explain joins using Oracle syntax.

4 questions

This quiz tests you on one of the most important parts of SQL - JOINS

Joins programming exercises
2 pages
Subqueries
17:04
16:13

In this section, we discuss oracle subqueries, which help us combine results from two or more queries. The section might remind you of sets in mathematics :)

Section 7: Creating and modifying objects (DDL commands)
02:52

In this section, we look at a brief overview of DDL (Data definition language) statements in Oracle and what they are used for.

CREATE tables (and other objects)
13:27
07:59

In this section, we take a look at the MODIFY statement in SQL and learn how to use it to modify attributes at the table level and column level.

06:47

In this section, we discuss how to drop objects from the database. We continue to use tables as the example object for our dicussion.

Section 8: Data Manipulation - Basic DML
07:45

Transactions are one of the main features that seperate a database from a file system. In this section, we take a look at transactions and the two most used transaction control statements in Oracle : COMMIT and ROLLBACK.

13:19

Oracle's delete statement is used to delete data from a table. In this section, we look at the syntax and a few examples of the delete statement.

17:53

In this section, we look at how to use the UPDATE clause in oracle to make changes to existing data in a table. We discuss different possible scenarios and how you can control which columns to update and which rows to update.

18:15

What do you do when you want to update or insert date in a table based on the input data? You use the "MERGE" command in oracle. It's a very interesting and powerful SQL Command.We'll take a look at a couple of reasonably advanced use cases and see how merge makes it so easy to implement them.

05:40

Oracle's delete statement is used to delete data from a table. In this section, we look at the syntax and a few examples of the delete statement.

12:35

Deleting large amounts of data or all data from a table has to be often done with care.In this chapter, we'll take a look at the TRUNCATE command in oracle and see why you would want to use it instead of DELETE, when you want to delete all the data in a table

Section 9: Common Errors
01:39

If you are using a VM or if your database instance is in IDLE state, you might see this error when trying to connect. This short video shows how you can fix it.

Section 10: Extra Resources
Big Data Pocket Reference
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

About the company :

A Data Guru (http://adata.guru) is an attempt at creating a learning community around all things data. To be job ready, learning one particular language or one skill usually is not sufficient and the same is true about data. 

On a usual day, most analysts, databsae developers/engineers and data scientists work on SQL, work with BI tools, look at ETL code, use EXCEL and do much more. 

Our attempt is at creating a resource that will help you be industry ready for a career in data. Stay tuned!

Ready to start learning?
Take This Course