Learn to become a fee-earning SQL developer in just seven weeks.
This fast, easy and effective course will take you from zero sql writing skills to being able to make money as a SQL developer.
This one-stop-shot-style course will teach you everything you need to know, from learning various database concepts to writing SQL to interact with the database.
Throughout the course I will be using the below methodology to make you think and help in forming the SQL statement.
Business Need:
Manager asks you to give you a report containing total sales volume for January month by day and city
Questions in your mind to build your query
Question | Answer |
---|---|
Give? | SELECT |
What columns? | DAY, CITY |
Summary? | SUM(VOLUME) |
Preposition? | FROM |
Which table contains sales orders data? | SALES |
Filter? | WHERE SALES_DATE BETWEEN ’01-JAN-2015’ and ’31-JAN-2015’ |
Grouping? | GROUP BY DAY, CITY |
Combine the above answers to form the below SQL
SELECT day, city, SUM(volume)
FROM sales
WHERE sales_date BETWEEN ’01-JAN-2015’ AND ’31-JAN-2015’
GROUP BY day, city;
In this course participants will learn
Softwares used
Lecture 1 |
Welcome!!
|
05:45 | |
Lecture 2 |
How to make best use of the program
|
1 page | |
Section 1: Software Installation | |||
---|---|---|---|
Lecture 3 |
Installing Oracle
|
04:07 | |
Lecture 4 |
Installing Java SDK
|
02:20 | |
Lecture 5 |
Installing SQL Developer
|
03:21 | |
Lecture 6 |
Running scripts necessary for the course
|
02:12 | |
Lecture 7 |
Scripts for our Lab Exercises
|
4 pages | |
Section 2: Database Concepts | |||
Lecture 8 |
What is a File?
|
02:05 | |
Lecture 9 |
What is a Table?
|
02:23 | |
Lecture 10 |
What is a Database?
|
03:15 | |
Lecture 11 |
What is a Relational Database (RDBMS)?
|
01:47 | |
Lecture 12 |
What is a Transaction?
|
02:27 | |
Lecture 13 |
What is ACID??
|
02:40 | |
Lecture 14 |
RDBMS Vendors
|
01:17 | |
Lecture 15 |
Database Normalization
|
1 page | |
Lecture 16 |
CODD Rules
|
2 slides | |
Section 3: Enter the Dragon (Database) | |||
Lecture 17 |
How do you talk with a Database?
|
01:59 | |
Lecture 18 |
What are the most common commands?
|
02:43 | |
Lecture 19 |
What type of Data can we store?
|
03:12 | |
Lecture 20 |
Data Model for our Course
|
01:46 | |
Section 4: Lets see the Data... | |||
Lecture 21 |
What data can I see?
|
03:45 | |
Lecture 22 |
How do I see data (SELECT) ?
|
02:51 | |
Lecture 23 |
How do I see a part of the data (SELECT) ?
|
02:57 | |
Lecture 24 |
Lab 1 (Exercises with Answers)
|
2 pages | |
Section 5: Lets limit the data we see (Filtering) | |||
Lecture 25 |
Filtering Data (WHERE command)
|
03:21 | |
Lecture 26 |
Comparison Operators
|
01:38 | |
Lecture 27 |
Filtering Data (More Examples...)
|
03:34 | |
Lecture 28 |
Filters on Character and Date values...
|
04:39 | |
Lecture 29 |
Filtering based on a column in a table...
|
04:02 | |
Lecture 30 |
Lab 2 (Exercises with Answers)
|
2 pages | |
Section 6: Logical Operators | |||
Lecture 31 |
BETWEEN and NOT BETWEEN
|
02:32 | |
Lecture 32 |
IN and NOT IN
|
02:52 | |
Lecture 33 |
LIKE operator
|
04:17 | |
Lecture 34 |
ALL and ANY
|
03:33 | |
Lecture 35 |
Lets learn about NULL...
|
02:08 | |
Lecture 36 |
IS NULL and IS NOT NULL
|
03:18 | |
Lecture 37 |
AND and OR
|
05:27 | |
Lecture 38 |
Lab 3 (Exercises with Answers)
|
2 pages | |
Section 7: Arithmetic operators | |||
Lecture 39 |
They are simple!!! (10 + 20 = 30)
|
02:42 | |
Lecture 40 |
Lab 4 (Exercises with Answers)
|
2 pages | |
Section 8: Lets sort the data we see (Sorting) | |||
Lecture 41 |
ORDER BY clause
|
04:04 | |
Lecture 42 |
How are NULL values treated while Sorting Data?
|
02:06 | |
Lecture 43 |
Lab 5 (Exercises with Answers)
|
2 pages | |
Section 9: How do we combine data from 2 tables? | |||
Lecture 44 |
SET Operators
|
02:39 | |
Lecture 45 |
UNION operator
|
04:22 | |
Lecture 46 |
UNION ALL operator
|
01:28 | |
Lecture 47 |
INTERSECT operator
|
02:00 | |
Lecture 48 |
MINUS operator
|
02:24 | |
Lecture 49 |
Lab 6 (Exercises with Answers)
|
2 pages | |
Section 10: Lets group the data... | |||
Lecture 50 |
Aggregate/Summary Functions
|
05:28 | |
Lecture 51 |
GROUP BY clause
|
04:08 | |
Lecture 52 |
GROUP BY with multiple columns
|
05:00 | |
Lecture 53 |
GROUP BY with HAVING clause
|
03:21 | |
Lecture 54 |
Lab 7 (Exercises with Answers)
|
2 pages | |
Section 11: Joining data from 2 or more tables (JOINS) | |||
Lecture 55 |
Why Joins?
|
03:53 | |
Lecture 56 |
Inner Join
|
05:08 | |
Lecture 57 |
Left Outer Join
|
02:43 | |
Lecture 58 |
Right Outer Join
|
02:35 | |
Lecture 59 |
Full Outer Join
|
02:26 | |
Lecture 60 |
Cross Join
|
02:58 | |
Lecture 61 |
Natural Join
|
02:22 | |
Lecture 62 |
Lab 8 (Exercises with Answers)
|
2 pages | |
Section 12: Functions available for us to use... | |||
Lecture 63 |
DUAL (What is this?)
|
02:37 | |
Lecture 64 |
Why do we need functions?
|
01:28 | |
Lecture 65 |
NUMBER related functions
|
03:47 | |
Lecture 66 |
CHARACTER related functions
|
04:07 | |
Lecture 67 |
DATE related functions
|
04:56 | |
Lecture 68 |
Lab 9 (Exercises with Answers)
|
1 page | |
Section 13: Interesting THINGS!!! | |||
Lecture 69 |
IF THEN ELSE (CASE statement)
|
03:19 | |
Lecture 70 |
Alternative Name (ALIAS name)
|
04:03 | |
Lecture 71 |
DISTINCT values
|
03:18 | |
Lecture 72 |
PSEUDO Columns
|
03:26 | |
Section 14: Data Definition Language (DDL) | |||
Lecture 73 |
Let's create a table (CREATE)
|
02:57 | |
Lecture 74 |
Let's change the structure of a table (ALTER)
|
04:50 | |
Lecture 75 |
Let's delete the table (DROP)
|
01:01 | |
Lecture 76 |
Lab 11 (Exercises with Answers)
|
2 pages | |
Section 15: Data Manipulation Language (DML) | |||
Lecture 77 |
COMMIT and ROLLBACK
|
01:28 | |
Lecture 78 |
Insert data into a table (INSERT)
|
05:14 | |
Lecture 79 |
Delete data from a table (DELETE)
|
03:32 | |
Lecture 80 |
Modify existing data in a table (UPDATE)
|
03:49 | |
Lecture 81 |
Delete all the data from a table (TRUNCATE)
|
03:01 | |
Lecture 82 |
Lab 12 (Exercises with Answers)
|
2 pages | |
Section 16: Lets put some restrictions on a table (Constraints) | |||
Lecture 83 |
Why constraints?
|
01:51 | |
Lecture 84 |
No empty data allowed (NOT NULL)
|
03:35 | |
Lecture 85 |
No Duplicate data (UNIQUE)
|
03:06 | |
Lecture 86 |
What is NOT NULL + UNIQUE = PRIMARY KEY
|
03:25 | |
Lecture 87 |
Accept only few values (CHECK)
|
03:26 | |
Lecture 88 |
Check data in other table before inserting/deleting (FOREIGN KEY)
|
04:46 | |
Lecture 89 |
Constraints on multiple columns
|
03:13 | |
Lecture 90 |
Adding constraints to an existing table
|
02:04 | |
Lecture 91 |
More operations on Constraints...
|
02:11 | |
Lecture 92 |
Lab 13 (Exercises with Answers)
|
2 pages | |
Section 17: Views | |||
Lecture 93 |
Why Views?
|
06:25 | |
Lecture 94 |
Lets modify an existing view
|
01:56 | |
Lecture 95 |
Lets update View Data
|
02:48 | |
Lecture 96 |
Create View from more than 1 Table
|
06:06 | |
Lecture 97 |
Lab 14 (Exercises with Answers)
|
2 pages | |
Section 18: Other Database Objects | |||
Lecture 98 |
Alternative name for Objects (SYNONYMS)
|
02:56 | |
Lecture 99 |
Series of Numbers (SEQUENCE)
|
03:34 | |
Section 19: Giving Permissions to other users (GRANT) | |||
Lecture 100 |
I like him (GRANT)
|
04:33 | |
Lecture 101 |
I don't like him (REVOKE)
|
02:51 | |
Lecture 102 |
List of all GRANT's
|
2 pages | |
Section 20: SUB Queries | |||
Lecture 103 |
What is a SUB Query?
|
04:33 | |
Lecture 104 |
Correlated SUB Query
|
05:21 | |
Section 21: Advanced Topics - Index | |||
Lecture 105 |
Book Index??
|
02:25 | |
Lecture 106 |
Lets create an Index...
|
03:44 | |
Lecture 107 |
UNIQUE Index
|
02:42 | |
Lecture 108 |
Function based Index
|
03:40 | |
Lecture 109 |
Dropping and Renaming an Index
|
01:48 | |
Section 22: Where to Go From Here | |||
Lecture 110 |
Advanced Database concepts you can explore!!!
|
1 page | |
Lecture 111 |
Practice Exercises
|
3 pages |