Oracle Database 11g, 12c, 18c and 19c SQL 1Z0-071
Overview
Become an Oracle Database SQL Certified Associate and demonstrate understanding of fundamental SQL concepts needed to undertake any database project. Passing the exam illustrates depth of knowledge of SQL and its use when working with the Oracle Database server. Gain a working knowledge of queries, insert, update and delete SQL statements as well as some Data Definition language and Data Control Language, the optimizer, tables and indexes, data modelling and normalization. By passing this exam, a certified individual proves fluency in and a solid understanding of SQL language, data modelling and using SQL to create and manipulate tables in an Oracle Database.
Qualified candidates have knowledge of general computing concepts, knowledge of command line interfaces and experience working in command line.
This course prepares you for Exam 1Z0-071. Test your current knowledge "SQL Preliminary Test". Just enter your name and password would be unisoft.
Why Unisoft?
- Hello visitor, if you are really looking for top quality educational training in SQL or looking to get certified in SQL exam 1Z0-071. Then your Google Search query has landed you on the right website. We at Unisoft Technologies have been delivering SQL Training from past many years with great success. Our success can be seen on Google Maps Reviews.
- We have a well-defined Day Wise Course Agenda which you can download from the link given at the end of this page.
- We are also Oracle Corporation Partners for delivering official SQL curriculum. That means, all the course contents are designed and updated by "Oracle University" and we are just going to deliver as it is to our students. You will get course ware (PDF's E-kits) directly from Oracle University.
- The instructor, who will guide you during your course will be a Certified SQL instructors. They help you understand, conceptualize and master SQL step by step.
- Real world solutions with expert instructors.
- Focus on practice and hands-on exercises ensure development of practical skills.
- Active training which ensures better learning curve.
- We help you not only in learning SQL. But you will also learn SQL developed by Oracle. We would help you with installation of Oracle Database 12c to 19c on your laptops. SQL can be used on various other RDBMS also like MYSQL, MS SQL SERVER, IBM DB2, ACCESS, POSTGRESQL, and SQLite. But we prefer Oracle Database as they are market leaders and our partner also.
- We would also conduct an assessment test after few days in the course. You can download a PDF copy of "Day Wise SQL Course Agenda" from a link at the end of this page for more info.
- Learning Structure Query Language is very similar to driving a car or swimming. Hands-on is must. We focus on such methods so that all our students are well versed with SQL Hands-on labs, assignments and day to day practice of each topic.
- Oracle Database SQL 1Z0-071 pdf or Oracle Database SQL 1Z0-071 study guide or Oracle Database SQL 1Z0-071 book or whatever you may call it, are available directly from Oracle University.
- Oracle Database SQL 1Z0-071 practice test will also be provided by our instructors. These practice test would help you get better understanding of Oracle Database SQL 1Z0-071 exam. You can say they are sort of Oracle Database SQL 1Z0-071 sample questions or Oracle Database SQL 1Z0-071 mock test.
- We prepare students in such a way that our students don’t ever need refer or use Oracle Database SQL 1Z0-071 dumps or Oracle Database SQL 1Z0-071 exam dumps to pass the exam. Using dumps is illegitimate way to pass the exam.
Exam Details
| Exam Title: | Oracle Database SQL |
|---|---|
| Exam Number: | 1Z0-071 |
| Exam Price: | ₹11,350 + 18% GST More on exam pricing |
| Format: | Multiple Choice |
| Duration: | 120 minutes |
|---|---|
| Number of Questions: | 78 |
| Passing Score: | 63% |
| Validated Against: |
This exam was validated against 11g Release 2 version 11.2.0.1.0 and up to 19c |
Course Content / Exam Content
Course Content:
Introduction
- DATA
- DATABASE
- OBJECTS
- RDBMS.
Overview of various Oracle Versions and Their Differences
- Oracle Database Versions
- Oracle Database Version V2 to 8,8i,9i
- What is 'i' in Oracle Database 8i or Oracle Database 9i
- Oracle Database Version 10g to 11g
- What is 'g' in Oracle Database 10g or Oracle Database 11g
- Oracle Database Version 12c
- What is 'c' in Oracle Database 12c
Oracle Database Installation
- Oracle Database 12c installation on Windows.
- Oracle Dababase 12c installation on Linux is covered in Oracle Administration 12c Paper.
- Overview of Various Oracle Tools
- SQLPLUS (CLI)
- How to Open, Connect, Use, Disconnect, Choose Database to Connect,... etc.
- SQL Developer (GUI)
- Overview of Using SQL Developers - What's New in latest Version,... etc.
- SQL cl
- Another lite weight newly launched Java Based Command Line Tool with Added features.
- SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, all while also supporting your previously written SQL*Plus scripts.
- SQLPLUS (CLI)
Installing Or Managing Oracle Sample Schema
- Account Unlocking & Resetting Password of Preinstalled Users (Sample Schema’s)
- Manually Installing the HR Schema
- Uninstalling HR Schema
- Starting with Oracle Database 12c Release 2, the latest version of the sample schema scripts are available on GitHub at https://github.com/oracle/db-sample-schemas/releases/latest.
- Installing Sample Schemas from GitHub
- Resetting Sample Schemas
- Uninstalling Sample Schemas
Overview of Oracle Multitenant Architecture - (With Respective to Learning of SQL)
- What is Container Database – (CDB)
- How to Connect to CDB's.
- How to Switch Container at Session Level.
- What is Pluggable Database – (PDB’s)
- How to Connect to PDB's.
- Creating a TNS entry for connecting to a PDB.(Net Service Name Creation)
- Using Local - Windows Environment Variable.
Overview of SQLPLUS Command’s
- CONNECT
- DISCONNECT,
- DESCRIBE,
- SET command's
- set linesize
- set pagesize
- set underline
- set timing {on|off}
- set time {on|off}
- set wrap {on|off}
- set sqlprompt { | _USER _DATE }
- set serveroutput {on|off}
- and...etc
- SPOOL
- START
- @
- @@
- INPUT
- LIST
- CHANGE
- APPEND
- TTITLE
- BTITLE
- BREAK
- etc.
Overview of SQL Statement’s
- Data Manipulation Language - DML
- Data Defination Language - DML
- Data Control Language - DCL
- Transaction Control - TC
Retrieving Data Using the SQL SELECT Statement (Projection)
- SELECT clause
- Capabilities of SQL SELECT Statements
- Basic SELECT Statement
- Selecting All Columns
- Selecting Specific Columns
- Writing SQL Statements
- Column Heading Defaults
- Arithmetic Operators or Expressions
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Operator Precedence
- Defining a Null Value
- Null Values in Arithmetic Expressions
- Defining a Column Alias
- Using Column Aliases
- Concatenation
- Double Pipe or Virtical Bars (||)
- Literals
- Alternative Quote (q) Operator
- Displaying the Table Structure
- Using the DESCRIBE Command
- Duplicate Rows - Distinct or Unique Keyword
- FROM clause
- Use of Special Table Called – Dual
Restricting and Sorting Data (Selection)
- WHERE clause
- Comparison Operators
- Logical Operators
- Rules of Precedence
- ORDER BY clause
- Ascending Orders
- Descending Orders
- Columns, Numeric Position, Alias , Expression, Nulls First & Nulls Last
- ROW LIMITING clause (aka OFFSET FETCH clause)
- Substitution Variables (&,&&) –
- Define
- Undefine
- SET Verify
Using Single Row Functions to Customize Output
- Character Functions
- Case Conversion
- UPPER
- LOWER
- INITCAP
- Character Manipulation
- SUBSTR
- INSTR
- LENGTH
- REPLACE
- TRANSLATE
- TRIM
- LTRIM
- RTRIM
- RPAD
- LPAD
- SOUNDEX
- CHR
- ASCII
- Case Conversion
- Number Functions
- ROUND(number)
- TRUNC(number)
- MOD
- FLOOR
- CEIL
- SIGN
- ABS
- SQRT
- POWER
- Date Functions
- SYSDATE
- CURRENT_DATE
- SYSTIMESTAMP
- CURRENT_TIMESTAMP
- EXTRACT
- MONTHS_BETWEEN
- ADD_MONTHS
- NEXT_DAY
- LAST_DAY
- ROUND(date)
- TRUNCT(date)
Using Conversion Functions and Conditional Expressions
- Conversion Functions
- TO_CHAR
- TO_DATE
- TO_NUMBER
- General Functions
- NLV
- NLV2
- NULLIF
- COALESCE
- Conditional Expressions
- CASE
- DECODE
Reporting Aggregated Data Using the Group or Multi-Row or Aggregate Functions.
- AGGREGATE FUNCTIONS
- SUM
- AVG
- COUNT
- MAX
- MIN
- VARIANCE
- STDDEV
- MEDIAN
- GROUP BY clause
- HAVING clause.
Analytic Functions or Windowing Functions
- ANALYTIC FUNCTIONS
- LEAD
- LAG
- RANK
- DENSE_RANK
- LISTAGG
- FIRST
- LAST
- FIRST_VALUE(*)
- LAST_VALUE(*)
- PERCENTILE_CONT(*)
- SUM(*)
- COUNT(*)
- MAX(*)
- MIN(*)
- AVG(*)
- QUERY_PARTITION_CLAUSE
- ORDER_BY_CLAUSE
- WINDOWING_CLAUSE
Displaying Data From Multiple Tables Using Joins
- Inner Join – (Equi-Join or Simple Join)
- Outer Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Self-Join
- Non Equi-Join
- Cross Join or Cartesian Product
Using Subqueries To Solve Queries
- Scalar Subqueries
- Single Row Subqueries
- Multi Row Subqueries
- Pair Wise Subqueries
- Non-Pair Wise Subqueries
Using Advance Subqueries
- Correlated Subqueries
- Correlated Update & Delete.
- Subqueries using EXISTS and NOT EXISTS Operators
- WITH clause –(Containing Set of Subqueries) & Recursive WITH clause
- Subqueries in FROM clause –(Inline Views)
Using Set Operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
Data Definition Language - DDL
- Database Objects
- Naming Rules
- CREATE TABLE Statement
- Referencing Another User’s Tables
- DEFAULT Option
- Creating Tables
- Data Types
- Numeric
- Binary
- Character
- Date Time
- LOB’s
- Including Constraints
- Constraint Guidelines
- Defining Constraints
- NOT NULL Constraint
- UNIQUE Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- FOREIGN KEY Constraint: Keywords
- CHECK Constraint
- Deferrable Constraints
- Enable & Disable Constraints.
- CREATE TABLE: Example
- Violating Constraints
- Creating a Table Using a Subquery
- Read-Only Tables
- DROP TABLE Statement
- TRUNCATE TABLE Statement
- ALTER TABLE Statement
- Renaming an Object
- COMMENT Statement
- Table
- Column
- E-R Diagram’s or Model & Introduction to Normalization or Normal Forms
- Creating Table Using Subqueries –(CTAS) & Creating Virtual Columns.
Data Control Language (DCL) & Controlling User Access
- Creating A USER,
- Default Tablespace & Quota
- Privilege Management
- Granting Privileges to User or Role (System or Object Privileges)
- Revoking Privileges from User or Role (System or Object Privileges)
- Managing Roles
Managing Tables Using DML & TC Statements, Locks, Deadlocks & For Update Clause.
- Data Manipulation Language
- Adding a New Row to a Table
- INSERT Statement Syntax
- Inserting New Rows
- Inserting Rows with Null Values
- Inserting Special Values
- Inserting Specific Date and Time Values
- Creating a Script
- Copying Rows from Another Table
- Changing Data in a Table
- UPDATE Statement Syntax
- Updating Rows in a Table
- Updating Two Columns with a Subquery
- Updating Rows Based on Another Table
- Removing a Row from a Table
- DELETE Statement
- Deleting Rows from a Table
- Deleting Rows Based on Another Table
- Database Transactions
- Database Transactions: Start and End
- Advantages of COMMIT and ROLLBACK Statements
- Explicit Transaction Control Statements
- Rolling Back Changes to a Marker
- Implicit Transaction Processing
- State of the Data Before COMMIT or ROLLBACK
- State of the Data After COMMIT
- Committing Data
- State of the Data After ROLLBACK
- State of the Data After ROLLBACK: Example
- Statement-Level Rollback
- Read Consistency
- Implementing Read Consistency
- FOR UPDATE Clause in a SELECT Statement
- FOR UPDATE Clause: Examples
Managing Other Schema Objects
- Views
- Sequences
- Indexes
- Synonyms
- Temporary Tables
- Directories
- External Tables
Flashback Technologies
- Flashback Table
- Flashback Drop
- Flashback Query.
- Flashback Version Query.
Managing Objects with Data Dictionary Views
- USER_CONSTRAINTS
- USER_CONS_COLUMNS
- USER_CATALOG
- USER_TABLES
- DBA_OBJECTS
- USER_OBJECTS
- ALL_OBJECTS
- USER_TABLES
- USER_TAB_COLUMNS
- USER_VIEWS
- USER_INDEXES
- USER_IND_COLUMNS
- USER_SYNONYMS
- USER_SEQUENCES
- USER_TAB_PRIVS
- USER_TAB_PRIVS_MADE
- USER_TAB_PRIVS_RECD
- SESSION_PRIVS
- USER_SYS_PRIVS
- USER_COL_PRIVS
- USER_COL_PRIVS_MADE
- USER_COL_PRIVS_RECD
- SESSION_ROLES
- USER_ROLE_PRIVS
- ROLE_SYS_PRIVS
- ROLE_TAB_PRIVS
- ROLE_ROLE_PRIVS
- DBA_DIRECTORIES
- ALL_DIRECTORIES
- USER_EXTERNAL_TABLES
- USER_EXTERNAL_LOCATIONS
- USER_TAB_COMMENTS
- USER_COL_COMMENTS
- USER_UNUSED_COL_TABS
Manipulating Large Data Sets – (Multi-table INSERT Statements)
- Using Subqueries to Manipulate Data
- Retrieving Data by Using a Subquery as Source
- Inserting by Using a Subquery as a Target
- CHECK OPTION Keyword on DML Statements
- Using the WITH
- Overview of the Explicit Default Feature
- Using Explicit Default Values
- Copying Rows from Another Table
- Overview of Multitable INSERT Statements
- Types of Multitable INSERT Statements
- Unconditional INSERT
- Conditional INSERT ALL
- Pivoting INSERT
- Conditional INSERT FIRST
- Pivoting INSERT
- MERGE Statement
- MERGE Statement Syntax
- Merging Rows: Example
Managing Data in Different Time Zones
- Time Zones
- TIME_ZONE Session Parameter
- CURRENT_DATE
- Comparing Date and Time in a Session’s Time Zone
- DBTIMEZONE and SESSIONTIMEZONE
- TIMESTAMP Data Types
- TIMESTAMP Fields
- Difference Between DATE and TIMESTAMP
- Comparing TIMESTAMP Data Types
- INTERVAL Data Types
- INTERVAL Fields
- INTERVAL YEAR TO MONTH
- INTERVAL DAY TO SECOND
- EXTRACT Function
- TZ_OFFSET Function
- FROM_TZ Function
- TO_TIMESTAMP Function
- TO_YMINTERVAL
- TO_DSINTERVAL
- Daylight Saving Time (DST)
Examination Score Report & Certificate
Downloads
Testimonials
Anuja Rananware
Unisoft Technologies is the best Institute for training and courses. This is the best platform for getting the Global Certification Courses and for learning. Environment is good for practice and study. Faculty is good.My faculty Mr. Tushar sir is well experienced and good at his teaching. He is helpful in nature and always available for our queries..
MADHURIMA DAS
The management is really good especially Saurabh Sir who taught us very efficiently even during lockdown via video conferencing though it was difficult to understand at first but he managed it very well.
Rituja Uikey
Very good education provided as well as environment is very useful for learning and. The teaching staff is very professional man. It's very supportive and friendly.. learning environment. Good institute for all software languages, SQL Java c c++ best staff provided which enhance our skills and everyone are very experienced teachers.
Pratik Agrawal
it was a wonderful experience studying in unisoft.I enrolled for SQL course only but i was so impressed by the teaching and faculty (Saurabh Joshi) that i started the java course (Taught By Raina Nair) too. You can take a free demo of any topic whenever you want.So yes I will always recommend unisoft for oracle certifications training.
Fahim Azeem Jeinulabdeen
hey guys if you are reading this it means you’re planning to looking for a professional training institute to do SQL or Oracle or something related to machine language. I was in this situation scrolling reviews 5 months ago when i was in Srilanka. Yaa i am a Sri Lankan and i have no idea about Unisoft, believe it or not thats the truth. My friends and my brother asked me to join big and fancy (big fee) institute but I didn’t when i was reading the review like you guys doing now. I got to know through the reviews Unisoft is the best place in our part of world ( south east Asia) to learn oracle. Then like everyone i called pratigya and talked to her she explained me everything and like that... i came to india guess what i dont know a single word of hindi or marathi and also Nagpur people don’t speak my language even if the know english they don’t speak in english. Finally with the help of Google direction i came to Unisoft building and i met Pratigya in person. She arranged a PG for me to stay and she said about my assigned lecturer that" she is a very good person and talented person, even if you ask a same question 100 times she will explain"(that "she" her name is Renu) I know what you are thinking cause i also thought the same. I thought that its her figure of speak or i am a foreign student that's why she is saying like that but soon i got to know that i am wrong, in SQL i am very weak in joins. Renu taught me only that chapter more than 3 times because i am an average student. Within 40 days i able to complete SQL (theory and practical ) and I started to preparing for 1Z0-071 Global Certification with the help of Renu. On 2020-02-24 i cracked the Global Cert Paper with 86 marks (if you cracked the paper with good marks my personal advise hide from pratigya she will defiantly ask for party) And now i am preparing for 1Z0-082 (Admin I). I don’t know about other faculties.if you want to know scroll the reviews a little you will get to know i guess. But i know only one thing if you are planning to learn SQL or Oracle Unisoft is the best place to learn. I was and still learning Oracle Under Renu. She is an Oracle Repository "Just loot it". she is an excellent teacher. When i was preparing for the paper she helped me soo much when i got doubts she was there to clear my doubts. I don’t know what else to say about her, if you got a chance to study under her i hope you will understand. And also there is lecturer named Monali Jamburkar. she also helped me when i was preparing for paper when Renu taking classes. She also very talented person. I don't wan’t to talked every person in Unisoft but yes here the environment is learner friendly result oriented and highly professional . Before i came here i know that i am gambled with my future but i took that risk. Now i understand that it worth the risk. I don’t know whether you will understand or not, cause i came from another country to study here. I also got to know that there has been number of such students who have taken services of Unisoft from different parts of Globe , Lot many NRI , few students from Australia & Germany has been here in past . It is truly a place which we can call as “Modern Temple of Learning High end IT” Thank you very much Unisoft . All the best for everyone.
Kunal Maheshwari
Best institute in nagpur that some can definitely look for. The faculty is very dedicated and specialized in their field. From my experience I joined for Oracle 12c and believe me the course coverege I got was well inlined with the industry standard and my trainer Saurabh Joshi was super excellent and master in SQL, undoubtedly he is the best and holds expertise in his field. My mentor Pratigya mam always listen to your concerns and ready for any kind of support you need or for any help. She is always there to motivate us and provide her best to explain and sort the things in amazing manner. She holds special expertise in technical domain. Hope this helps !!!