1Z0-071 Oracle Sql Developer Certified Associate (Database)

What you'll learn
Create tables in a database and ALTER columns in the table.
Know what data type to use in various situations, and use functions to manipulate date, number and string data values.
Retrieve data using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
JOIN two or more tables together, finding missing data.
INSERT new data, UPDATE and DELETE existing data, and export data into a new table.
Create constraints, views and sequences, subqueries and CTEs
Use UNION, CASE, MERGE and error checking
Apply ranking and analytic functions, grouping
Learn about transactions, indexes, users, privileges, roles and more
Requirements
You need to know how to use a computer, and hopefully know how to use a spreadsheet.
No prior knowledge of Oracle SQL Database or any other database required - although the more knowledge you do have, the better.
To install Oracle SQL Database on your computer, you will need a 64-bit machine.
Oracle SQL Database cannot easily be installed on the Mac OS. If you wish to install it on a Mac, you will need either to dual boot into Windows or be running Parallel Desktop.
You don't even need Oracle SQL Database installed - I'll show you have to install it on your computer for free!
There is a 30-day money back guarantee of this Udemy course.
Why not have a look at the curriculum below and see what you can learn?
Description
This course is the foundation for the Oracle Database SQL 1Z0-071 certification. It covers the SELECT statement in detail, with additional requirements for controlling user access. It is divided into 6 sessions, each of which should take a morning or afternoon to complete.What do people like you say about this course?Prashant says: "Course has been designed in way that a person with no knowledge of Oracle can understand it. Good learning and thanks a lot for making such nice course."Shubho says: "Awesome course. The instructor explains the concepts very thorougly and in a easy-to-grasp way. Also, the practice exercises are super helpful. If you want to master Oracle SQL, this is the course for you."Henry says: "I want to thank Phillip for this formidable course. This course along with other materials helped me to pass the 1Z0-071 Exam last Saturday."Session 1We'll install for free Oracle Express Edition and Oracle SQL Developer. Then we'll take a look at the 6 principal clauses of the SELECT statement: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.Then we'll start creating tables, but find that we can't get very far without understanding data types. We'll then look at string, date and number types and functions, together with looking at functions converting between them.Session 2We'll revisit the SELECT statement and go into more detail. We'll create tables and JOIN them together. Once you have finished this session, you should be secure in your knowledge of the 6 principal clauses of the SELECT statement, the most important part of Oracle SQL.Session 3In session 3, we'll looking for missing data, and find out how to delete and update data. We'll look at the difference between implicit and explicit transactions, and various constraints, including primary and foreign keys.Session 4We'll start off by saving our queries in views. Then we'll combine tables, not by adding additional columns using JOINS, but adding additional rows such Oracle SQL set operators such as UNION, INTERSECT and MERGE. Then we'll look at analytical functions using the OVER clause.Session 5Session 5 starts off by adding totals to our SELECT queries. We'll then look at subqueries, and how you can add them into the FROM, SELECT and WHERE clauses, and also into the WITH clause. Finally, we'll look at Oracle SQL self-joins, which are useful when you have hierarchies.Session 6Session 6 starts off with two additional data types, TIME ZONE and INTERVAL data types and functions. We'll then have a look at SEQUENCEs and INDEXes, together with how these are shown in the Data Dictionary. We'll then create new users, and assign privileges and roles to them, and we'll finish with the last few requirements for the exam.No prior knowledge is required - we'll even install Oracle Database and Oracle SQL Developer on your computer for free!Once finished, you will know what how to manipulate numbers, strings and dates, and create database and tables, create tables, insert data and create analyses, and have an appreciation of how they can all be used in Oracle SQL.
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 Welcome to Udemy
Lecture 3 The Udemy Interface
Lecture 4 Do you want auto-translated subtitles in more languages?
Lecture 5 Resources
Lecture 6 Curriculum
Section 2: Install Oracle Express Edition and SQL Developer
Lecture 7 Do you have Windows Home?
Lecture 8 Download and Install Oracle Express Edition
Lecture 9 Download and Install Oracle SQL Developer
Section 3: 3a. The SELECT statement - an overview
Lecture 10 Connect to Database
Lecture 11 Solving "The network adaptor cannot establish the connection" error
Lecture 12 8e. The SELECT and FROM clauses
Lecture 13 Renaming fields
Lecture 14 Using the AS keyword
Lecture 15 Semicolons and comments
Lecture 16 3b, 13b. The WHERE clause
Lecture 17 15b. The GROUP BY clause
Lecture 18 15c. The HAVING clause, and information about error messages.
Lecture 19 13a, 13c. The ORDER BY clause
Lecture 20 Remembering the order of the clauses
Lecture 21 Practice Activity Number 1
Lecture 22 Practice Activity Number 1 - The Solution
Section 4: 8b. Session 1 - Creating tables - First pass
Lecture 23 Creating a table using the GUI
Lecture 24 Creating our first table using SQL
Lecture 25 17a. Inserting values
Lecture 26 Deleting the data, then the table
Lecture 27 Trying to create an Employee table
Section 5: Session 1 - The DUAL table and Number types
Lecture 28 Using the DUAL table
Lecture 29 Practice Activity Number 2 - Writing mathematical queries
Lecture 30 Practice Activity Number 2 - The Solution
Lecture 31 8c. Creating sample table; Run Script v Run Statement
Lecture 32 8a. The NUMBER data type
Lecture 33 Do you want to use the comma as a decimal point?
Lecture 34 8a. FLOATing data types
Lecture 35 Practice Activity Number 3
Lecture 36 Practice Activity Number 3 - The Solution
Section 6: Session 1 - Number functions
Lecture 37 5b, 14a. Numeric functions Part 1
Lecture 38 Rounding functions
Lecture 39 Practice Activity Number 4
Lecture 40 Practice Activity Number 4 - The Solution
Section 7: Session 1 - String data types and functions
Lecture 41 Character Encoding Systems
Lecture 42 8a. Character Data Types
Lecture 43 5b, 14a. String Functions
Lecture 44 NULL - an introduction
Lecture 45 Useful NULL functions
Lecture 46 Adding quotation marks and Alternative Quoting Mechanism in string literals
Lecture 47 Practice Activity Number 5
Lecture 48 Practice Activity Number 5 - The Solution
Section 8: Session 1 - Converting numbers to strings, and strings to numbers
Lecture 49 5b, 14a. Joining a string to a number using implicit conversions
Lecture 50 5a, 14b. Converting a number to a string
Lecture 51 5a, 14b. Converting a string to a number, including the CAST function
Lecture 52 5a, 14b. International conversions
Lecture 53 Practice Activity Number 6
Lecture 54 Practice Activity Number 6 - The Solution
Section 9: Session 1 - Date data types and functions
Lecture 55 8a. DATE and TIMESTAMP() data types
Lecture 56 5b, 14a. Date extraction functions
Lecture 57 5a, 14b. Converting dates to strings
Lecture 58 5a, 14b. Converting times to strings
Lecture 59 5a, 14b. Converting strings to timestamps, and international considerations
Lecture 60 Practice Activity Number 7
Lecture 61 Practice Activity Number 7 - The Solution
Section 10: End of Session 1, Start of Session 2
Lecture 62 End of Session 1
Lecture 63 Welcome to Session 2
Section 11: Session 2 - Creating and querying part of a table
Lecture 64 Creation of tblEmployee table
Lecture 65 Adding and modifying additional columns
Lecture 66 3b, 13b. SELECTing only part of a table - strings
Lecture 67 3b, 13b. SELECTing only part of a table - numbers
Lecture 68 3b, 13b. SELECTing only part of a table - dates
Section 12: Session 2 - Practice Activities
Lecture 69 Practice Activity Number 8 - Creating Tables
Lecture 70 Practice Activity Number 8 - The Solution
Lecture 71 Populating the Practice Activity tables
Lecture 72 Populating the Practice Activity tables - The Solution
Lecture 73 Practice Activity Number 9
Section 13: Session 2 - Summarising and ordering data
Lecture 74 13c, 15b. Summarising and ordering data
Lecture 75 15c. Criteria on summarised data
Lecture 76 Exercise - Part 1
Lecture 77 13a, 13c. Exercise - Part 2, and ORDER BY NULLS FIRST/LAST
Lecture 78 Practice Activity Number 10
Lecture 79 Practice Activity Number 10 - The Solution
Section 14: Session 2 - Adding a second table
Lecture 80 Adding a second table
Lecture 81 Designing a connection
Lecture 82 12a. Importing data and showing tables graphically
Lecture 83 Writing a JOIN query
Lecture 84 4a. Different types of JOIN
Lecture 85 4b. Using NATURAL JOINs
Lecture 86 4b. Old notation joins
Lecture 87 Practice Activity Number 11
Lecture 88 Practice Activity Number 11 - The Solution
Section 15: Session 2 - Adding a third table
Lecture 89 Creating a third table
Lecture 90 6a, 6c. JOINing three tables
Lecture 91 Practice Activity Number 12
Lecture 92 Practice Activity Number 12 - The Solution
Section 16: End of Session 2, Start of Session 3
Lecture 93 End of Session 2
Lecture 94 Welcome to Session 3
Section 17: Session 3 - Find missing data, and delete and update data
Lecture 95 2b. Missing data
Lecture 96 2b. Deleting data
Lecture 97 2b. Updating data
Lecture 98 Practice Activity Number 13
Lecture 99 Practice Activity Number 13 - The Solution
Section 18: Session 3 - Database terminology
Lecture 100 1a, 11c. The relationship of a database and SQL
Lecture 101 2a, 2b, 11a, 11b. DML, DDL, DCL and TCL
Lecture 102 17d. What are transactions?
Lecture 103 17d. Implicit transactions
Lecture 104 2c, Explicit Transactions - Start and end transactions
Lecture 105 2c, Savepoints and roolback to savepoints
Lecture 106 Formatting in Oracle SQL Developer
Section 19: 8c, 10a. Session 3 - Data integrity, including Create and modify constraints
Lecture 107 Problems with our existing database
Lecture 108 What are constraints?
Lecture 109 Unique constraints - what are they?
Lecture 110 Unique constraints in action
Lecture 111 Default constraints - what are they?
Lecture 112 Default constraints in action
Lecture 113 Check constraint - what are they?
Lecture 114 Check constraints - in practice
Lecture 115 Primary key
Lecture 116 Primary key - in practice
Lecture 117 Foreign key - what is it?
Lecture 118 Foreign key - in practice
Lecture 119 12a. How are constraints shown in ERDs?
Lecture 120 Practice Activity Number 14
Lecture 121 Practice Activity Number 14 - The Solution
Section 20: End of Session 3, Start of Session 4
Lecture 122 Well done for getting half way through the course
Lecture 123 Welcome to Session 4
Section 21: 18a. Session 4 - Views
Lecture 124 Creating views
Lecture 125 Altering and dropping views
Lecture 126 Adding new rows to views
Lecture 127 Hiding/Unhiding Columns In Views
Lecture 128 Practice Activity Number 15
Lecture 129 Practice Activity Number 15 - The Solution
Section 22: Session 4 - Combining sets
Lecture 130 7a. UNION and UNION ALL
Lecture 131 7a. INTERSECT and MINUS
Lecture 132 13d. Use ampersand substitution to restrict and sort output as runtime
Lecture 133 CASE statement
Lecture 134 NVL, NVL2 and Coalesce
Lecture 135 Practice Activity Number 16
Lecture 136 Practice Activity Number 16 - The Solution
Section 23: Session 4 - the MERGE statement
Lecture 137 20b. MERGE statement - in theory
Lecture 138 20b. Let's Build our MERGE statement
Lecture 139 20b. Let's expand our MERGE statement
Lecture 140 20b. Merge with additional columns
Lecture 141 Practice Activity Number 17
Lecture 142 Practice Activity Number 17 - The Solution
Section 24: Session 4 - The OVER Clause
Lecture 143 Introduction
Lecture 144 OVER()
Lecture 145 PARTITION BY and ORDER BY
Lecture 146 RANGE
Lecture 147 CURRENT ROW and UNBOUNDED
Lecture 148 RANGE versus ROWS
Lecture 149 Omitting RANGE/ROW?
Lecture 150 Practice Activity Number 18
Lecture 151 Practice Activity Number 18 - The Solution
Section 25: 14c. Session 4 - Analytical Functions
Lecture 152 ROW_NUMBER, RANK and DENSE_RANK
Lecture 153 NTILE
Lecture 154 FIRST_VALUE and LAST_VALUE
Lecture 155 LAG and LEAD
Lecture 156 CUME_DIST and PERCENT_RANK
Lecture 157 PERCENTILE_CONT and PERCENTILE_DISC
Lecture 158 Other Aggregation functions
Lecture 159 Practice Activity Number 19
Lecture 160 Practice Activity Number 19 - The Solution
Section 26: End of Session 4, Start of Session 5
Lecture 161 You are two-thirds of the way through the course
Lecture 162 Start of Session 5
Section 27: 15a. Session 5 - Group functions
Lecture 163 Adding Totals
Lecture 164 ROLLUP, GROUPING and GROUPING_ID
Lecture 165 GROUPING SETS
Lecture 166 Practice Activity Number 20
Lecture 167 Practice Activity Number 20 - The Solution
Section 28: 16. Session 5 - Sub-queries
Lecture 168 The WHERE clause
Lecture 169 WHERE and NOT
Lecture 170 ANY, SOME and ALL
Lecture 171 The FROM clause
Lecture 172 The SELECT clause
Lecture 173 16d. Correlated subquery - WHERE EXISTS
Lecture 174 Practice Activity Number 21
Lecture 175 Practice Activity Number 21 - The Solution
Section 29: 16g. Session 5 - WITH clause, and getting the top rows
Lecture 176 Top 5 from various categories
Lecture 177 WITH statement
Lecture 178 Generating a list of numbers
Lecture 179 Grouping numbers
Lecture 180 Selecting the third row using rownum and OFFSET and FETCH
Lecture 181 Deleting the second row of results
Lecture 182 Practice Activity Number 22
Lecture 183 Practice Activity Number 22 - The Solution
Lecture 184 Practice Activity Number 23
Lecture 185 Practice Activity Number 23 - The Solution
Section 30: Session 5: CTE statement
Lecture 186 4c, 6b.Self-Join
Lecture 187 Recursive CTE
Section 31: End of Session 5, Start of Session 6
Lecture 188 Almost there - you can do it!
Lecture 189 Welcome to Session 6
Section 32: Session 6 - Time Zone and Interval data types
Lecture 190 Time Zone data types
Lecture 191 Time Zone functions
Lecture 192 Interval data types
Lecture 193 Interval functions
Section 33: Session 6: Sequences and Data Dictionary
Lecture 194 Data Dictionary
Lecture 195 Defining Sequences
Lecture 196 Using Sequences
Lecture 197 Practice Activity Number 24
Lecture 198 Practice Activity Number 24 - The Solution
Section 34: Session 6: Indexes
Lecture 199 Heaps
Lecture 200 B-Tree
Lecture 201 Indexes
Lecture 202 Practice Activity Number 25
Lecture 203 Practice Activity Number 25 - The Solution
Section 35: Session 6: Users, Privileges and Roles
Lecture 204 Users and Schema
Lecture 205 What are System and Object Privileges
Lecture 206 Granting system and object privileges to users and roles
Lecture 207 Namespaces
Lecture 208 Privileges Data Dictionary
Section 36: Session 6: Miscellaneous
Lecture 209 Dropping columns and making them UNUSED
Lecture 210 Flashback Tables
Lecture 211 Create and use External Tables
Lecture 212 Non-Equi Joins
Lecture 213 Multi-Table INSERT statements
Section 37: Session 6: Congratulations
Lecture 214 Taking the exam
Lecture 215 Well done!
Lecture 216 Bonus Lecture
This SQL course is meant for you, if you have not used Oracle SQL Database much (or at all), and want to learn SQL.,This course is also for you if you want a refresher on SQL. However, no prior Oracle SQL Database knowledge is required.


Homepage

https://www.udemy.com/course/1z0-071-learn-oracle-sql-database-using-developer/