Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Step by Step Practical Oracle SQL with real life exercises

Name: Step by Step Practical Oracle SQL with real life exercises
Rating: 4.5 (287 reviews)

Explains both the what and why of an Oracle SQL concept. Deals with real-life situations.

Created byRamesh R

Last updated 6/2017

English

What you'll learn

Fundamentals of SQL commands and their nuances.
Appreciate that real-life SQL is not always a "Yes/No" problem. There is also a "May be". A solution that works for scenario X could have issues in scenario Y.
Code backend DB for applications.
Think in "SQL" terms. More importantly, think practically.
CREATE Tables and INSERT data into them.
SELECT data from tables and FILTER them using WHERE clause
JOIN data across multiple tables
Use GROUP BY to group results and calculate aggregates
Use functions to act on data.
Control access to users using GRANTs and SYNONYMs.
Create INDEXes for performance reasons and CONSTRAINTS on data to protect data integrity
Much more things..

Course content

15 sections • 37 lectures • 4h 3m total length

Introduction3:01

What is data?2:38
Why do we need a database and SQL?3:37
Why do we need a database? Why not store in simple excel sheets? How does a database help?
Install Oracle3:47
Download and install Oracle; select edition (express, standard, enterprise) and version (12c or 11g); use Windows or Linux with admin privileges; configure SYS and SYSTEM passwords and complete installation.
Install SQL developer1:13
Connect to DB (Why do we need Hostname, Port, SID), Users, Schemas9:56
Learn how Oracle uses schemas and users to manage access, grant permissions, and connect to a host, port, and SID using SQLPlus and SQL Developer.

Data Model for the videos2:06
Build a practical data model for a library using two tables, book_inventory and customer_book_list, linked by book_id, including price_per_day, and learn to create, alter, drop, and load sample data.
Create table statement2:33
Create table statements define book_inventory and customer_book_list with their columns and data types, using varchar2, number, and date. Insert rows to load data into these skeleton tables.
What are Data Types? (Why does Oracle expect us to provide it?)3:16
Create Table, Alter table, Drop Table in action3:54

LIKE (with variants), NOT LIKE, IN, NOT IN7:30
Relational operators, Arithmetic operators, Concatenation5:20
Learn relational and arithmetic operators in Oracle SQL with real-life examples, using >, <, between, not equal, concatenation with ||, and aliasing in select and where clauses.
NULL Values (Scenarios where you have to be careful)10:33
Understand null values as missing or unknown in Oracle, use is null or is not null, and beware not in with nulls; NVL and empty strings are treated as null.

DUAL table (what's the need?), Rownum (why doesn't it work sometimes), Sysdate8:50
Explore the dual table in Oracle, learn how rownum behaves as a pseudo column, and use sysdate to fetch the current date with practical examples.
Functions - Lower(), Upper(), length() - empty string?,substr(), INSTR(), trim()8:41
Functions - nvl(), round(), floor(), to_date()/ to_char() -datatype, to_number()11:40
Handle nulls with nvl(), and round or floor numbers. Convert strings to dates with to_date() and format them with to_char(), and convert numbers with to_number(); understand date versus character types.

min(), max(), sum(), avg(), count(), common mistake with count(*)4:53
Explore aggregate functions in Oracle SQL, including min, max, sum, avg, and count, and learn how count(*) differs from count(column) across multiple rows, including count(distinct column), while nulls are ignored.
Group by on single/multiple columns, Having clause vs whereclause9:53
Learn how to use group by on single and multiple columns. Master having vs where clauses with practical library-style examples counting per customer and filtering aggregates.

Requirements

Basic knowledge of computers.
No programming knowledge necessary.

Description

Relational databases have spread across the software world. Learning SQL means you know how to access them. This skill enables you to stay competitive.

The course teaches you Oracle SQL. But, SQL is a standard that is used for other relational databases also. So, you can re-use the SQL learning (not 100% though) for other DBs as well with some tweaks specific to those DBs.

The course tries to impart knowledge using two methods.

1. Video lectures. The lectures introduce you to the basics of Oracle SQL and also the important nuances. For e.g. apart from learning what a NULL value means, you will also learn the situations the NULL values can be problematic.

2. Exercises - PDFs -> The exercises deal with the concepts introduced in the videos and their nuances.

- The answer for a question and the explanation will be provided in the next page. Occasionally, some questions can also ask you to do some self-exploration (recommended) on a topic related to the lecture. If you do not have time to do that, you can look at the answer with the explanation in the next page.

- The exercises vary in complexity. Some of them can seem quite complex for a beginner. Some questions have been made intentionally confusing. A confusion/complexity now is better than the one at an examination hall or work. Isn't it?.

- Several questions deal with real-life situations and their handling. Please attempt them using logical and practical reasoning, and if needed, take the help of the answer and the explanation in the next page. You can always contact me for any doubts.

I believe, the course will help in preparing you for the complexity and ambiguity in the real-life database world.

Good luck. Enjoy the course.

PS: Exercises are attached as PDF resources in the videos. And not all videos will have a PDF. Questions across lectures could be clubbed together and uploaded against a single video lecture.

Who this course is for:

Anyone who wants to learn Oracle SQL.

Step by Step Practical Oracle SQL with real life exercises

What you'll learn

Explore related topics

Course content

Welcome to the world of SQL1 lecture • 3min

Oracle SQL: Getting Started5 lectures • 21min

Oracle SQL: Creating, Altering and Dropping the table4 lectures • 12min

Oracle SQL: Inserting into a table, Commit and Rollback1 lecture • 8min

Oracle SQL: Read data from table3 lectures • 15min

Oracle SQL: Read data from table (continued..) - More complex operations3 lectures • 23min

Oracle SQL: Updates, Deletes and Truncates1 lecture • 6min

Oracle SQL: Pseudocolumns and Functions3 lectures • 29min

Oracle SQL: Aggregate Functions2 lectures • 15min

Oracle SQL: Joins2 lectures • 18min

Requirements

Description

Who this course is for: