Oracle Sql Learning By Example

Tools of the trade - Introduction Preview 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.

Client Server

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

Oracle virtual box and developer VM Preview 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

Setting up the database in the VM Preview 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"

Alternatives to Virtual Machine Preview 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

Select statement : The very basics Preview 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.

Basic Select and Describe

Basic Select and Describe

Data Types Preview 08:34

Data types Quiz

Aliases, Primary keys and unlocking other accounts Preview 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

Grants/Privileges - Accessing someone else's objects Preview 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.

Filtering data - "where clause" in SQL Preview 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.

Groups and aggregates - part 1 Preview 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.

Groups and Aggregates - Part -2 Preview 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.

Sorting Data - "Order by" Clause in Oracle Preview 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.

Introduction to Oracle functions Preview 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.

Character and Numeric functions in Oracle Preview 21:04

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

Date and Time functions in Oracle Preview 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.

Conversion Functions in Oracle Preview 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

Null handling functions in Oracle Preview 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.

Conditional Logic functions in Oracle Preview 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 Preview 4 pages

Normalization Preview 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

Introduction to Joins, Inner-joins (part-1) Preview 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.

Inner joins (Contd..), Self joins, and Cartesian Joins Preview 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.

Outer Joins Preview 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.

ANSI vs Oracle Syntax Preview 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.

Joins Quiz

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

Joins programming exercises Preview 2 pages

Subqueries Preview 17:04

Set Operators Preview 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 :)

Introduction to DDL statements Preview 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) Preview 13:27

Modifying objects - the alter command Preview 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.

Dropping objects (DROP Command) Preview 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.

Transaction Control in Oracle Preview 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.

the INSERT Statement Preview 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.

the UPDATE Statement Preview 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.

the MERGE statement Preview 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.

the DELETE statement Preview 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.

Oracle's TRUNCATE statement Preview 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

ORA-12514: TNS:listener does not currently know of service requested in connect Preview 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.

Big Data Pocket Reference Preview 00:08