Pass Oracle 1z0-071 Exam in First Attempt Easily

Introduction

1. Introduction

SQL is an abbreviation of the term "structured query language." It has been widely adopted by many RDBM servers such as Oracle, Microsoft SQL Server, MySQL Sidebase, IBM, DB2, etc. Using SQL, we create, read, and modify the data stored within our DBMS database. Whenever we interact with a database, we primarily do four basic activities. One is to read the data. For example, if there is a table like this, we can read data from it, we use select command for it, or we can insert new data. We use insert command for it, or we can delete the data. We use delete command for it, or we can delete the data. We use delete command for it, and we can modify the data. Like I can change it from two to three, right? I can change it from "box" to "Daffy. Okay? and that's called updating the data. Okay? So we basically do four primary actions: we can read, that is, select; we can insert; we can delete; and we can update data. Now, many of you have come across many SQL development courses that are actually very good at explaining the syntax to you. After all, a syntax is just a syntax. However, this course will not only teach you the syntax but also about coding your sequel statements in the right way. Wait, what is the right way? The right way is not only to use the right syntax, but also to achieve the fastest possible performance. For example, I can write a simple select Start from employee to get the data, but what if this table is massive with millions of rows? How do I know it before running my query and thereby filling my screen with continuous scrolling data? Or how do I know about columns that have indexes so that I can use them in my sequel for faster performance? In the above examples, there may be an index on the Last Name column and may not bean index on the First Name column. So obviously using the last name in the Where class might give much faster performance than using first name. So writing a SQL statement which performs faster is always preferable in an Odbus environment as it prevents issues related to slowness. This course also teaches you about how to join two tables in a query. This course teaches SQL development in a way so that you will be able to write high performing SQL statements. This also covers RDBMS architecture as needed. This course is for those who are new to SQL development and are interested in beginning their career in SQL development. Or if you have already done some SQL development and would like to learn about the performance aspects of it, then this course is for you too. Also, if you are an It manager and is looking to understand the SQL statements as well as to refer a good SQL course to your team, then this course is for you too. This course comprises primarily of Oracle's implementation of SQL, and it's mostly similar to the sequel implementation in Microsoft SQL server MySQL site base.

Hide

Installation

1. Installing Oracle 12c in Windows 10

So go to Oracle.com and go to downloads. Okay, click Oracle download Oracle database to see now. OK, I'm going to download the this one which is Microsoft Windows 64 bit release 12.1.2.Accept the licence agreement and then click this one again. Accept the licence agreement. And like this if you haven't registered, you would like to register here. Okay. And that would download the zip file. Okay, since it has been downloaded, let me go and download the next file. Okay, it's downloading the next one. Okay, I've downloaded both the files. They are of compressed format, so we need to uncompress them. So just do a right click on the file one of two and choose Extract all and it will prompt you a location where you need to extract those files. Okay, this is very important. I'm going to extract both the files into same location. So I don't want this one of two or those things. I'm just going to extract them to win. So the extraction of first file is complete. So let me extract the second one. Right click extract all. The important thing is it has to be extracted in the same location as the first one. Okay, it's extracting. So we have extracted both the files in this location which is Windx 60, 412, 10 two. Let's go to that location and go inside. There will be a folder called Database and let's open that. And there is a file called setup. Exe. Let's run it. This will invoke the installer. It's starting. Oracle universal installer. You will briefly see this command window. The first option is to configure security updates. This is the screen where you will give your email address or if you already have subscribed Oracle support, you would give the username and password so that you'll receive information about security patches and updates. Okay, so I'm going to skip this. Click next and it's going to complain about that. This is basically for educational purpose, right? So we can skip it. Okay, so in the next screen we are presented with three options. One is create and configure a database. The next one is Install database software only. The third one is Upgrade and existing Database. Let me begin with the second one. Install database software only. What this means is it will install just the database software and it will do nothing after that. Meaning that if you want to install just the software and create the database later, we can choose this option. Even if we do this at some point of time when we are ready to create a database, we can do that by invoking DBCA which is Database Configuration Assistant which will be part of this database software. Okay, and this first option which is create and configure a database is nothing but the second option plus that DBCA which I just mentioned, basically it will install the software and invoke DBCA for us to create a database. The third one is to upgrade an existing database. If you already have a database and we want to upgrade that, then we use this option. Click Next. Okay, let me go back. And I have chosen Create and configure a database, meaning that I want to install the database software as well as I want to create a database. Click Next. So in this screen, we are presented with two options. One is, do we want to choose desktop class configuration parameters or server class configuration parameters? Since this is for educational purposes and I'm installing Oracle on a Windows desktop, I'll select desktop class. Okay, so in this screen, it's asking who should be the owner of that process when Oracle runs. So in a multi-user operating system like Windows, Unix, or Linux, any process that's running or any software that has been installed has to be owned by some user, first and foremost. And when it runs, that instance of that software which is running in memory Okay, if yes, keep the Windows username and password. So whenever Oracle runs, it will run as this user, ordo you want to create a new Windows user for it? Okay, I'm going to go with the old method, which is just use Windows built in account as I'm using this only for educational purpose, but definitely Oracle is going to complain about that. Okay, I'm still going to continue with that. In this screen, we need to choose various options. And let me explain one by one. Oracle base. ORACLEBASE is a folder which serves as a base for any Oracle software installation. In this mission, by default, it would be C. In this Windows box, the term Sam comes from the name of the OS user. In this Windows box, which is my Windows desktop, I have an OS user called Sam and I logged in using that user. So by default, it gives me an option CsUser, which would be Sam. Okay. And that serves as an Oracle base. Next is the software location. Remember, I'm installing database. Okay. The software for the database goes into ORACLEBASE, which is SQL product 12.1.0 slash DB home underscore one. So c colon app. Sam is the Oracle base. And then beneath that, it creates a folder called Product and then the version 12.1.0 and then database home underscore one, the first home, meaning that I can install the same software in another home also. Okay, so next is the database file location. Remember, we are creating a database, right? So the data files have to go somewhere. Of course, it will go into a folder called Auradata under ORACLEBASE APPSAM, which is ORACLEBASE, and then Auradata and leave the default value for this database edition, enterprise edition, and character set. I'm going to change it to Unicode. You can keep it, whatever you want, because this is just going to be a training system. And the global database name, I'm going to keep it So right now it's doing prerequisite checks. Okay, so we are presented with this summary screen and everything looks fine. So I'm going to click install. So once that completes, it will start the creation of the database and you will get a screen like this. So after a while, once it is done, it's going to give an output like this saying that the database has been configured and it will also give the URL where the enterprise manager database express location will be. We will talk about this later. Okay. So click okay. And we are done. Okay, so now let's connect to it. Go to the run menu in Start and then type CMD to get to the command prompt. Now set or equals to demo DB to make sure it has been set properly. Just do an echo percentage. Oracle set. It has been set to demo DB. Okay, set the Oracle home to this one where we installed the Oracle database software. Select name from Vdollar. So that's how we install Oracle database in Windows Ten. Thanks.

2. Using SQL Developer

In this video, we'll look at how to use SQL Developer to connect to an Oracle database. To do so, go to the start menu and all apps, scroll down all the way to O for Oracle, and you'll see your Oracle menu here. Open that and run SQL Developer. Okay, so here I need to configure the connections. So do right-click New connection and let me call the connection Demodb. The username is system. I'm going to connect as System. Okay. You want to save this connection. You would see the connection here and click Connect. So now I'm connected. I'm going to verify the name of the database. Select Name from Vdara Database and then click this run statement arrow. You get the name. So that's how you use OracleSQL Developer to connect to a database. Thanks.

3. Troubleshooting Connection Issues

In this video, we will look at a couple of things to see if we can connect to the Oracle database that we installed on our Windows 10 PC: go to the Web and Windows bar, which was previously the run bar in your Windows 10, type regedit, which is regedit to open Registry Editor, go to hkey local machine software and Oracle. You should see something similar to key RRDB Twelve home one. Click that on the right side. Oracle said they should be set properly. Oracle base is C colonappsam, which is correct. In my PC, it should be similar, or wherever you installed it in your PC, Oracle Sid should match. Next, open up the Oracle administration assistant for Windows. You can find it by going to start all apps. Oracle RRDB twelve home one. Which is this Oracle home. Once in there, you should see the Oracle home. Under the OS Database Administrators group, you should see your OS user. In my case, I've logged in as Sam on my PC, okay? And this would be the OS user that Ilogin and work with Oracle database in your PC. Under this OS Database Administrators group, you should see the OS user that you use tologin and work with Oracle database. If it shows up, then you would be able to connect as Sisdba as shown in this screen, like SQLplus space, no log, connect systepa and then you just do a select name from Vidala database. Thank you for watching.

Hide

Infrastructure Architecture

1. Client Server Architecture

This is a high-level overview. Okay, I have a single-host computer running an Oracle database; what should we do with it? I can use the tool SQL Plus, which is part of the database software square installation, to manage the database server. Also, I can use it to run some ad hoc SQLs. But how do I use this entire system for my business? I can run a business application such as accounting software on the same box. The accounting software will use the database as its backend. This forms a preliminary client-server environment. The client is the accounting software; the server is the database. But is this a good idea? No. Both the client software and the database running on the same host computer is not a good idea. The computer could get overloaded, and any maintenance activity would cause a logistics nightmare. So the solution is to move the client software to its own computer. But how can it talk to the database server? Install the appropriate database client software in the application host computer and configure it to talk to the database running on the database host computer. This database client software is a small piece of software that this accounting software uses to interact with the database. The accounting software is the main client software here.

2. Different types of Clients

In this video, let us take a look at different types of client applications. These days, it is common to have three tier architecture where there will be a web server facing the users. The web server will interact with the application software and serve the web enabled portion of the application as Web pages. The application server will interact with the database server. In some cases, the application host runs both the application software as well as the webserver software, which is the Http server. Since I'm calling the accounting software as a client, let me address this database client piece as Database Driver. So it is database driver. Now, let us take a look at various types of database clients available. If the application is Java based, then it can use the Oracle Java database. Connectivity driver. Also known as JDBC driver. This enables Java programmers to send SQL statements to RDBMS systems such as Oracle database. Or if the application is Cased application, then it can use the Oracle call interface driver, also known as OCCI driver. This enables C programmers to access Oracle databases. If the application is ODBC based, then it can use the Open database connectivity driver, also known as ODBC driver. ODBC stands for Open database Connectivity. ODBC is designed to make applications independent of the databases and operating systems, allowing them to be easily ported to different systems, and so they can access various data sources such as spreadsheets, commod limited files, etc. Or if you use Proc Pro C plus plus, you can then use Pro C plus plus precompiler. This will allow you to insert the SQL codes within the source program. Then, during the compilation, it will transform those SQLs into standard Oracle runtime calls. If you use proposal, then you can use Procobal. Precompiler. It will do the similar function as described above. Or you can create web based applications using Oracle Application Express, which is also called as Apex. You can also use Hypertext, which is a server side scripting language. For web based applications. Oracle supports Microsoft's net Framework through various tools such as ODP net, which is Oracle data provider for net oracle Developer tools for Visual Studio net stored procedures Oracle providers for ASP.Net thank you for watching.

Hide

Oracle RDBMS Architecture

1. Quick Overview of Oracle RDBMS Architecture

This video, let us take a quick look at Oraclems architecture. You know, just to get us started for the sequel development. During the installation, Oracle software gets installed in one location which is them product. Then, when we create a database which is nothing but a bunch of files, they get created in another location which is CM auradata. So when we start up an Oracle database, two things happen. First, an empty Oracle instance gets created in the memory. Then that instance opens the data files belonging to the database and the database will be available for use. More like when you click Notepad icon, it creates an empty Notepad instance in the memory. Then we either open a text file or create one. When the Oracle database is open, to access it, you would need to log in as a user. Meaning that we need to have database user accounts within the database. And when the users create tables, they create them in a logic structure called table space. A table space can be made up of one or more data files. So from the OAS perspective, we will see only the data files. But if we log into the database, then we can see the table spaces. Objects such as tables are owned by users. For example, user one can create a table called Test. It can be addressed as User one test. User three can also create a table called Test and it can be addressed as User three test. Also, if needed, it can be stored in the same table space. Note that they both are different tables. One is User one test and the other one is User three test. I named both the tables as test to explain the difference. Since user one and user three on an object which is table, in this case they can also be called as schemas. User one can be called as a schema and user three can be called as a schema. User two remains just as a user. Before giving example, please note that I changed the table names to avoid any confusion. Now we can say user one is the owner of the table customer and user three is the owner of the table orders. If user one has permission to use the table space two, then he or she can create a table there too and call it something else. In this case, the name of the new table is Address. However, the user one cannot name it as Customer. Why Because there is already a table called Customer that belongs to that user. In other words, objects cannot have duplicate names within a schema or within a user. By default, owners have all the privileges on the objects they want. They can read, write, delete, and modify the data. If needed, they can completely drop the objects. They can also grant access on their objects to others. For example, if user one grants read privileges on the customer table to user two, then user two can read data from that table. Also, there will be powerful users with DBA privileges. They can do almost anything in the database. They can read, write, drop, etc. These accounts will be only used by DBS for database administration purposes. They can do almost anything in the database. They can read, write, drop, etc. These accounts will be only used by DBS for database administration. Thank you for watching this quick overview of the Oracle RDBMS. These accounts will be used by DBS for database administration.

Hide