1Z0-071 Oracle SQL Developer: Certified Associate (Database) from Udemy

What's inside

Learning objectives

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

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

Syllabus

Introduction

Welcome to Udemy

The Udemy Interface

Do you want auto-translated subtitles in more languages?

Resources

Curriculum

Install Oracle Express Edition and SQL Developer

Do you have Windows Home?

Download and Install Oracle Express Edition

Download and Install Oracle SQL Developer

3a. The SELECT statement - an overview

Connect to Database

Solving "The network adaptor cannot establish the connection" error

8e. The SELECT and FROM clauses

Renaming fields

Using the AS keyword

Semicolons and comments

3b, 13b. The WHERE clause

15b. The GROUP BY clause

15c. The HAVING clause, and information about error messages.

13a, 13c. The ORDER BY clause

Remembering the order of the clauses

Practice Activity Number 1

Practice Activity Number 1 - The Solution

8b. Session 1 - Creating tables - First pass

Creating a table using the GUI

Creating our first table using SQL

17a. Inserting values

Deleting the data, then the table

Trying to create an Employee table

Session 1 - The DUAL table and Number types

Using the DUAL table

Now it's your turn.

This exercise is to practice writing a query, and creating simple mathematical expressions.

Practice Activity Number 2 - The Solution

8c. Creating sample table; Run Script v Run Statement

8a. The NUMBER data type

Do you want to use the comma as a decimal point?

8a. FLOATing data types

Practice Activity Number 3

Practice Activity Number 3 - The Solution

Session 1 - Number functions

5b, 14a. Numeric functions Part 1

Rounding functions

Practice Activity Number 4

Practice Activity Number 4 - The Solution

We will look at the four major types of string data types, and various string functions, together with NULL

Character Encoding Systems

8a. Character Data Types

5b, 14a. String Functions

NULL - an introduction

NVL, NVL2, NULLIF and COALESCE

Adding quotation marks and Alternative Quoting Mechanism in string literals

Practice Activity Number 5

Practice Activity Number 5 - The Solution

Session 1 - Converting numbers to strings, and strings to numbers

5b, 14a. Joining a string to a number using implicit conversions

5a, 14b. Converting a number to a string

5a, 14b. Converting a string to a number, including the CAST function

5a, 14b. International conversions

Practice Activity Number 6

Practice Activity Number 6 - The Solution

We will look at the date data types, and various data functions, together with offset dates

8a. DATE and TIMESTAMP() data types

5b, 14a. Date extraction functions

5a, 14b. Converting dates to strings

5a, 14b. Converting times to strings

5a, 14b. Converting strings to timestamps, and international considerations

Practice Activity Number 7

Practice Activity Number 7 - The Solution

End of Session 1, Start of Session 2

End of Session 1

Welcome to Session 2

In this section, we will create an Employee table, and querying the table using a WHERE clause.

Creation of tblEmployee table

Adding and modifying additional columns

3b, 13b. SELECTing only part of a table - strings

3b, 13b. SELECTing only part of a table - numbers

3b, 13b. SELECTing only part of a table - dates

Session 2 - Practice Activities

Practice Activity Number 8 - Creating Tables

Practice Activity Number 8 - The Solution

Populating the Practice Activity tables

Populating the Practice Activity tables - The Solution

Practice Activity Number 9

Practice Activity Number 9 - The Solution

In this section, we will produce analyses and order the results.

13c, 15b. Summarising and ordering data

15c. Criteria on summarised data

Exercise - Part 1

13a, 13c. Exercise - Part 2, and ORDER BY NULLS FIRST/LAST

Practice Activity Number 10

Practice Activity Number 10 - The Solution

Understand what information is needed to connect two tables together, and in what format.

Adding a second table

Designing a connection

12a. Importing data and showing tables graphically

Writing a JOIN query

4a. Different types of JOIN

4b. Using NATURAL JOINs

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Develops foundational skills in querying an Oracle database using SQL and Oracle SQL Developer

Taught by Phillip Burton, recognized for his work in Oracle SQL

Provides a comprehensive foundation for the Oracle Database SQL certification 1Z0-071

Covers advanced SQL concepts such as subqueries and analytical functions

Requires prior knowledge of Oracle Express Edition and Oracle SQL Developer

Does not provide hands-on labs or interactive materials

Reviews summary

Effective oracle sql dev training

Learners say this course is an exceptionally thorough resource for passing the 1Z0-071 Oracle SQL Developer: Certified Associate (Database) exam. The 19-hour course is led by an English-speaking instructor who clearly explains concepts using practical examples and reinforces lessons with practice exercises. By the end of the course, students feel confident in their ability to pass the exam.

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in 1Z0-071 Oracle SQL Developer: Certified Associate (Database) with these activities:

Review SQL SELECT clauses

Show steps

Reviewing SQL SELECT clauses will help solidify your understanding of the building blocks of SQL queries.

Browse courses on Oracle SQL

Show steps

Start by reviewing the syntax of the SELECT clause.
Practice writing SELECT queries for various data types.
Test your understanding by completing practice problems.

Read 'Oracle Database 12c SQL'

Show steps

Supplement your learning by reading a book dedicated to Oracle Database SQL.

View Oracle Database 12c The Complete Reference... on Amazon

Show steps

Purchase or borrow the book.
Read through the chapters relevant to the course topics.
Take notes and highlight important concepts.

Follow tutorials on data types and their usage

Show steps

Supplement the course material and enhance understanding of different data types used in SQL.

Browse courses on Data Types

Show steps

Search online for tutorials on data types in Oracle SQL
Follow the steps in the tutorials to create tables and insert data
Experiment with different data types to understand their properties
Attempt practice exercises to solidify learning
Review the course material on data types for reinforcement

Nine other activities

Expand to see all activities and additional details

Show all 12 activities

Follow tutorials on Oracle SQL

Show steps

Following tutorials on Oracle SQL will allow you to learn from experts and reinforce your understanding of the concepts.

Browse courses on Oracle SQL

Show steps

Search for tutorials that cover the core concepts of Oracle SQL.
Work through the tutorials step-by-step and try the practice exercises.
Take notes on the key concepts and examples provided in the tutorials.

Follow tutorials on data types and constraints in Oracle SQL

Show steps

Deepen your understanding of data types and constraints by following guided tutorials.

Browse courses on Data Types

Show steps

Find online tutorials on data types and constraints in Oracle SQL.
Follow the tutorials to learn about different data types and constraints.

Practice SQL queries

Show steps

Reinforce SQL skills and improve proficiency in writing queries.

Browse courses on SQL

Show steps

Create a table in Oracle SQL Developer
Insert data into the table
Write a query to select specific columns from the table
Practice joining tables together
Use aggregate functions to summarize data

Practice SELECT, INSERT, UPDATE, DELETE & JOIN queries

Show steps

Reinforce your understanding of the fundamental SQL operations by practicing writing queries.

Browse courses on SQL Queries

Show steps

Create a sample database and tables.
Write SQL queries to perform data manipulation operations (SELECT, INSERT, UPDATE, DELETE).
Write SQL queries to join multiple tables.

Create a cheat sheet of SQL functions

Show steps

Solidify your knowledge of SQL functions by creating a handy reference guide.

Browse courses on SQL Functions

Show steps

List down all the SQL functions covered in the course.
Provide a brief description and syntax for each function.

Join a study group for Oracle SQL

Show steps

Collaborate with fellow learners by joining a study group to discuss concepts and practice queries.

Browse courses on Oracle SQL

Show steps

Find or create a study group focused on Oracle SQL.
Meet regularly to discuss course materials, work on assignments together, and prepare for assessments.

Create a data visualization of your Oracle SQL project results

Show steps

Showcase your understanding of data analysis by creating a visually appealing representation of your project findings.

Browse courses on Data Visualization

Show steps

Choose an appropriate data visualization tool.
Import your Oracle SQL project data into the tool.
Create a data visualization that effectively communicates your findings.

Attend a workshop on advanced Oracle SQL techniques

Show steps

Expand your knowledge and skills by attending a workshop focused on advanced Oracle SQL techniques.

Browse courses on Oracle SQL

Show steps

Identify a workshop that aligns with your learning goals.
Register for the workshop.
Attend the workshop and actively participate in the sessions.

Participate in a SQL coding competition

Show steps

Test your problem-solving abilities and enhance your competitive edge by participating in a SQL coding competition.

Browse courses on SQL

Show steps

Find a SQL coding competition that interests you.
Register for the competition and familiarize yourself with the rules.
Complete the competition and submit your solutions.

Career center

Learners who complete 1Z0-071 Oracle SQL Developer: Certified Associate (Database) will develop knowledge and skills that may be useful to these careers:

Data Analyst

Data analysts sift through data to uncover insights that can help businesses make better decisions. This course can help you develop the skills needed to succeed in this role, including data manipulation, analysis, and visualization. You'll learn how to use SQL to extract and analyze data, and how to use statistical techniques to identify trends and patterns. You'll also learn how to communicate your findings effectively to stakeholders.

See salaries and explore the career path for Data Analyst

Database Administrator

Database administrators are responsible for the day-to-day operation and maintenance of databases. This course can help you develop the skills needed to succeed in this role, including database design, implementation, and performance tuning. You'll learn how to use SQL to create and manage databases, and how to use database management tools to monitor and maintain database performance.

See salaries and explore the career path for Database Administrator

Data Scientist

Data scientists use data to solve business problems. This course can help you develop the skills needed to succeed in this role, including data mining, machine learning, and statistical modeling. You'll learn how to use SQL to extract and analyze data, and how to use statistical and machine learning techniques to build predictive models.

See salaries and explore the career path for Data Scientist

Software Engineer

Software engineers design, develop, and maintain software applications. This course can help you develop the skills needed to succeed in this role, including software design, development, and testing. You'll learn how to use SQL to interact with databases, and how to use software development tools to build and test software applications.

See salaries and explore the career path for Software Engineer

Business Analyst

Business analysts help businesses understand their data and make better decisions. This course can help you develop the skills needed to succeed in this role, including data analysis, visualization, and communication. You'll learn how to use SQL to extract and analyze data, and how to use data visualization tools to communicate your findings effectively to stakeholders.

See salaries and explore the career path for Business Analyst

IT Consultant

IT consultants help businesses with their technology needs. This course may be useful for developing the skills needed to succeed in this role, including data analysis, database management, and software development. You'll learn how to use SQL to interact with databases, and how to use software development tools to build and test software applications.

See salaries and explore the career path for IT Consultant

Project Manager

Project managers lead and manage projects. This course may be useful for developing the skills needed to succeed in this role, including project planning, execution, and monitoring. You'll learn how to use SQL to extract and analyze data, and how to use project management tools to plan and track project progress.

See salaries and explore the career path for Project Manager

Product Manager

Product managers are responsible for the development and management of products. This course may be useful for developing the skills needed to succeed in this role, including product design, development, and marketing. You'll learn how to use SQL to extract and analyze data, and how to use product management tools to plan and track product development.

See salaries and explore the career path for Product Manager

Data Engineer

Data engineers build and maintain the infrastructure that supports data analysis and machine learning. This course may be useful for developing the skills needed to succeed in this role, including data architecture, design, and implementation. You'll learn how to use SQL to create and manage databases, and how to use data engineering tools to build and maintain data pipelines.

See salaries and explore the career path for Data Engineer

Database Designer

Database designers design and develop databases. This course can help you develop the skills needed to succeed in this role, including data modeling, normalization, and performance tuning. You'll learn how to use SQL to create and manage databases, and how to use database design tools to design and optimize databases.

See salaries and explore the career path for Database Designer

Systems Analyst

Systems analysts analyze and design computer systems. This course may be useful for developing the skills needed to succeed in this role, including systems analysis, design, and implementation. You'll learn how to use SQL to extract and analyze data, and how to use systems analysis tools to design and implement computer systems.

See salaries and explore the career path for Systems Analyst

Information Architect

Information architects design and manage information systems. This course may be useful for developing the skills needed to succeed in this role, including information architecture, design, and implementation. You'll learn how to use SQL to extract and analyze data, and how to use information architecture tools to design and implement information systems.

See salaries and explore the career path for Information Architect

Web Developer

Web developers design and develop websites. This course may be useful for developing the skills needed to succeed in this role, including web design, development, and testing. You'll learn how to use SQL to interact with databases, and how to use web development tools to build and test websites.

See salaries and explore the career path for Web Developer

Technical Writer

Technical writers create and maintain technical documentation. This course may be useful for developing the skills needed to succeed in this role, including technical writing, editing, and publishing. You'll learn how to use SQL to extract and analyze data, and how to use technical writing tools to create and maintain technical documentation.

See salaries and explore the career path for Technical Writer

UX Designer

UX designers design and evaluate user interfaces. This course may be useful for developing the skills needed to succeed in this role, including UX design, research, and evaluation. You'll learn how to use SQL to extract and analyze data, and how to use UX design tools to design and evaluate user interfaces.

See salaries and explore the career path for UX Designer