💻 SQL en Oracle: Curso Intensivo de Fundamentos Desde Cero! 💻
¿Quieres dominar SQL en Oracle? Este curso intensivo te llevará desde los conceptos básicos hasta los fundamentos avanzados de SQL, preparándote para trabajar con bases de datos Oracle en entornos empresariales y equipos de desarrollo.
✅ Qué Aprenderás:
Fundamentos de SQL: consultas, filtros y ordenación de datos.
Técnicas avanzadas como joins, subconsultas y funciones agregadas.
Consejos para optimizar consultas y trabajar eficientemente con Oracle.
Al finalizar este curso, tendrás las habilidades necesarias para manejar bases de datos Oracle con confianza. ¡Comienza tu viaje en SQL hoy mismo!
🔔 Suscríbete para más tutoriales de SQL, tips de bases de datos y recursos tecnológicos!
Okay, very well, in this
database training you are going to focus on the
query language to be able to
communicate with the database. And that
implies, uh, being able to communicate in
different ways, cross objects,
understand how, how, how are those
objects and what is the best use and
appropriation of that information. So
the invitation here is for you to have fun
consulting databases that you understand.
How you can consult any type of
database regardless of whether it is
now or not. uh, let's remember that this is
based on a standard defined in 1999,
so, uh, you are very likely going to
have, uh, possibly interrelation with
other types of databases, but you are not
going to have any problems because what you are learning in this training will help you with that. So, we are going to learn the fundamental aspects of query language to later be able to define it in an advanced course and
become experts in the
language. Hello, welcome once again to
this, your channel, your space, uh, where we
talk about many topics that have to
do with technology, entrepreneurship, good
practices, uh, databases, information,
uh, to stay up to date, as with so many
things or so many events that
happen. daily And that in some way it
touches us eh eh to be eh let's say
informed and have the knowledge about it
well for today what I bring you I
bring you something very interesting we are going to
do this Oracle course Eh then I
bring you an Oracle course eh that is going to
start from scratch yes I want us to
do a very
interesting very didactic Oracle course eh that you
have fun doing it eh And then we are going to
start eh even from the same
installation So what do we need
for this we need an engine with
which to interact eh And then I am going to
show you quickly How you can put it
eh to work on your machine right
now you don't need the big machine
because we are going to download an Express version
of Oracle and on those that we are going to
work then eh what we need to
do is going to be very easy ready we are
here in the Google browser to Google I am
going to say Oracle eh
database Express
eh here I have it Then eh in the
Google search well
some links are going to appear that are going to reference me I am
interested the first one will almost always come
out first So don't
worry and that is going to be oriented towards
the last version that we have on the
market yes So if the last one is
21c Well it will guide me towards 21c or
suddenly who knows we will release
22c well towards 22 So don't
worry about the version Then
we will click on it when I do that
Look, a tab opened and then
it redirected me to the Oracle site
Look, we are in Oracle so at this
moment here you are seeing Oracle
so basically What do we need to let
us Download the Oracle version
then here it tells us eh Download
Oracle database XE it means the
Express version it is not the
full version but for the subject of you
learning with this it will be
enough for us because we
will have all the example schemes here So
we will be able to work ready So
what do I need to download it So I will
click to download it Then it
tells me Look there are several versions of Oracle
it is for Linux in different
versions and it is for Windows which one are you
interested in well I am interested in
Windows So I will tell it
Windows x64 then it will be enough for me to
click on that link and it will
direct me to that download and it will
immediately put it to download in
some occasions it makes one
register because you register with an
account eh for this eh particular it will not
ask you to register then
just click on it Well that will be enough if it
asks you to register well very
easy you enter your data that are not eh
Very complex eh eh the information that it
asks you is not that complex
your basic information then it will
be very easy look what happened it is already
downloading if you see it here below
in the lower left there you
have it So you already have it
downloading it occupies
1.8 GB Well it really is a
considerable size but it is very functional and it
will work very well for you As soon as you
download it you will see how the
installation begins that we will see in
another video here is that you will learn to
download the version of Oracle that
works with which you can do eh everything
you can work with the
example schemes and therefore with all the
Oracle material which is what
we try to eh that it can be sufficient
in the tool and you can make use
of the database resource data in an engine
as robust as Oracle in the most
efficient way I hope you liked this video
having finished Downloading
Our video then Congratulations let's
install what we have to do or
What are the steps for you to
install it on your computer on the machine you are
working on So easy
the first thing we have to do
is go to the download that we did
right So we're going to go here and
say downloads and it will show us
the downloads that it has Here is the
last one I'm going to click on it so that it
opens the compressed one that it will
show me then it will open the winr or the
file decompressor that you have
installed on your machine So the one you
have there is no mess and it will show me the
files that are there what do I need well
go to the installable towards the
file that will allow me to install the
tool on the machine What is that
file well this one that says Oracle
database 21 cprx Ed eh According to the
version well it will appear there if you
take out in 22 then it will say h with
datab 22 Exactly the same well What do we
need to do is double click it
to start an installer which
is normally a wizard that will
help me through a series of steps so that
I finish
installing it,
let's wait ah There it is doing all the
necessary tasks at that moment it
unzips the files it needs
uh in the temporary directories
because they are the ones it will use to
install Oracle on this
machine,
okay then look what appeared
Then the wizard appeared
Welcome
to the database installation wizard
now with database Express it
very easy look it's not that complicated
now what are we going to do we are going to follow
the steps that it tells us there we are going to say
next and well the license terms
where we agree that we are
uninstalling it we are going to click uh yes we
accept them and we are going to click next
again uh A path will appear which
is where it is going to install it where it is going to
install the fonts Then it tells me
that c app PC product 21c I say
correct next
uh What will be the password for the
database let's put an easy one because this
is local there is no problem 1 2 3 4 5 6 at
home you put the one you want very
important that that password is going to be
used for the sis System and
pdb admin accounts for when we make
containers so it's very important that you
know that that password is going to be
set for those database users
later in another session uh in another
class well I'm going to show you uh what
those users are for the moment
I need you to be clear that that uh that is
going to be the that is going to be the password
so that those users can access the
engine Let's give it the next uh At
this moment he tells me what are the
folders in which he is going to install that
is transparent to you He
is simply informing you you do not need to do anything
no no you do not need to go create
directories nothing in Windows he does it
automatically in Linux you have to do
a few things but in Windows the process is going to be
totally transparent for you you do
not have to start doing bigger
things I am going to tell him to install and at this
moment the installer is going to start
well Uh he tells me that he is going to install it on
the machine that I give him access uh that
a new software is going to be installed because
Windows detects it Well you tell it
yes, that it is the normal thing, the
rigorous thing, then it takes a
little time while it unzips and begins to
validate its installation, this implies that
it identifies the machine on which it is
going to run Well yes, it is enough for you to
install it normally, as it is the
Express version, as I told you, well you are not going to
have so many problems, it is going to be a
really rather transparent process
at the moment Look, it is already copying
the files, that is how easy it is how you are going to
install it on your machine Then you
download it, you open it, you open the
executable file which was the one that I recommended to you,
eh, which is now called conav 21c Express
edition. msi and you open that and this
installer opens As soon as it starts to
install Well, it will tell you the normal thing
about where it is going to go and that you accept
the agreement eh of use which is normal in
any software that you install on your
machine, you are going to have to authorize the
application because it is new in Windows
So it is likely that your Windows will
tell you, eh, it will tell you or tell you that
a new software is going to be installed on your
machine, then basically what you're
going to do is tell him yes, that there's
no problem. Then you say
yes and start this installation. Look, it's
already going like
20% less eh than what he has to
install, there are some things that he
installs eh slowly and there are others Where he already
plays eh the time a little, so he
does it faster, they are more efficient for
now he is unzipping those
files that you downloaded from the internet and
what he's going to do is install them,
copy them, extract them to the directories
where eh they have to remain eh supporting
eh the application so that the application
works well for you so that it doesn't fail eh
normally we go for Oracle why
eh Because it is very robust because it does not
fail you because it generally gives you
confidence to any organization,
Oracle gives you confidence and eh well, I
'm not advertising it. No, not the
idea, but it really is very robust, it is
very reliable, eh the integrity,
security and transparency of the use and
management of that information.
Generally it is very good here, it
generally gives you confidence
and so that's what we're
doing installing a very
reliable engine right now eh We're already
like 30% and it's installing
all the fonts It's still a process that
takes a few minutes not that many but it
's convenient that we wait for it
after this what it does is tell me I'm
done and when it tells you I'm done
Oracle will have been installed on your machine
basically it's like the process
look we've done it slowly so you
can do it at home eh
so you can do it on your machine so
that it runs so that you don't
suddenly have fear that it's not going to
install well eh it's really very noble
when it bothers you well that I recommend you
when this bothers you when you've already
had an Oracle on the machine yes for
some circumstance There are people who
for example eh install Oracle or or
had Oracle installed and deleted the
folders there it's a little complicated
When you reinstall it Generally it doesn't
work it doesn't open it doesn't let you
consult So that Generally
eh has a difficulty there because you have to
uninstall that type of
things but if you are installing from
scratch on your machine Generally it doesn't
bother you yes Generally he will install
and you will almost not feel the
installation you can do many things
while he installs like we are
doing right now we could also
be in any other scenario and he
will easily install then eh You already
have it clear eh how do we do so that
he installs on your machine on your computer and
you can appropriate eh the material
to be able to work and learn because
here the idea is that we learn a lot and
this is what will allow us to be able to
focus on a very
structured material that will teach me in a
very
sufficient way Okay very well
eh let's unzip eh the
example diagrams to be able to work with the
hr user how do we do this then
very easy eh we open the browser a
Windows explorer and in the explorer
we look for the folder where the
example diagrams are where you have
unzipped them if you have them in a
compressed file and unzip them eh in x
location then you go to there
to where you unzip them now what do
you do we are going to copy and we are not
going to take them where the database is installed
normally I already have
a root directory in C called
app a little exploration then app
within App we are going to navigate through
the
folders until we find the
Oracle version that looks in this case is 21c and
there we are going to find like
uh the list of main folders
we are going to go to this one called
divon xc We are going to double click on it when
we are in there we are going to look for
one called
uh demo and in demo there is another one called
scheme I want that schemes that
schemes folder to paste it there at
this moment Look we have already passed that
folder that we unzipped to a path
within the oracol directories This is
going to be the most important thing for this to
turn out very well eh Where we have
our database we find a
folder called demo and another one
called scheme already in there eh is where we are
going to copy what we unzipped
which is the folder that has the arch
of the source files of the
example schemes of
Time until there is the first step second
step We are going to open a console we give it
cmd and open the console Once in
the
console what we are going to do is
connect to the database So
how do you do it then
sql allow Connect to the
database with a superior user who
has all the permissions to do what
we need ready So I am already in
Oracle look how I am
doing it eh it is transparent because I do not
ask you for a password yes however let's
look
eh With what user do you have us registered
that we connect like with what user
is the one that is accepting to
connect to the database Look it is
telling me that it is yes it is perfect I
needed to be with sis users with a
user that had permissions on the
database so great So
what have we done So first what
we did was download Oracle then
what we did was install it and now what we are
doing is putting in
the example schemes within that
procedure to create those
example schemes what we do well we unzip
the folder that I leave you here in the
comments of the video we unzip the
folder in a temporary in any path
that you want and we are going to place it
inside the directory from oracol
eh And what we're going to do is from there
eh we're going to run them then well we already
know what user we are with now
what we need Which is eh what
What is the next step in this
process that among other things is not that
complex eh really fun fon
as transparent as we would think and then
eh we can basically
eh
How to go step by step finding the
solution that we need that in
this case is to have the
example schemes working because all the
material runs on those schemes
well good here What happens What's next we are going to
use a command
yes it is called
Start and we are going to put
question mark Slash and we are going to put the
path then the path where we
left it inside the Oracle directory
Remember that here we are Look it is demo
scheme human
resources So we are going to put the
same path so that it is transparent and
the example schemes can be imported
then demo scheme now
follows human resources
right and then the hr
msql file is like the the self-executable that
will allow us eh transport all
these objects to the
oracol database so that we can connect and
follow the entire example scheme alright
then let's hit enter eh it told us
that the file could not be opened it
failed in let's look at what we
failed demo schema human
resource and H R Main is not mail the error
is that it is not mail it is Main yes when we
come here look here you have it it is
Main if you saw it is not it is not it is not the one we
had that's why maybe it failed
let's run it again effectively
now it did recognize it Then it tells us
What is going to be the password for Human
resource of the user hr Well let's put
an easy one that we remember 1 2 3 4 5 6
after that it tells us What is going to be the
default Table space here we are going to
put that it is users the default Table space
now it is going to tell us what the
temporary Table space is where where he is going to
use let's say that that space
temporarily to do his
operations we are going to tell him that the
temporary is going to be
called and it tells us next What is the
password for sis for this
example case well we put a 2 3 4 5 6 and
what is the lock the lock pack Ah okay
then there we are going to tell him what is
Oracle
home Slash
logs followed by this what we are going to do
is run the Script so that it works
let's see effectively he started
running the Script That means that at
this moment he has installed the
example schemas How do we know that he
installed them or in other words how do we know that the
schemas Yes they are there Ah well we should be
able to connect with the user hr
let's see if it is possible to connect I'm
going to go to the developer Yes in the
developer eh let's create a connection
eh let's put
hr
connection what user I need it to be hr
because I'm connecting with it is true
the Val the default value if we give it
like this What password did we put cu 1 2 3 4
4 5 6 to what Host to local Host 1521 the
Sid x he took it let's test that it works it
says that it is correct let's give it connect
let's see if it connected us This is not it is
this
effectively we have the connection with the
user now we go to data and the
data is there that is to say we have the entire
schema set up so this is the
process that you must follow to have
the hr schema working in
Oracle Hello Very good Now what do I want? Eh, I want us to
meet the developer, eh,
what is the tool with which we are going to
interact? That is the invitation, let's
see how it
works, ready, here we are. This is the
developer interface. And this is the
interface of the tool, so
basically you have a space in which, uh, you have
the connections, right. That's
where you make them, eh, I'm going to, I'm going to,
uh, highlight a little bit, I don't know that highlight,
but well, we already saw where it is
exactly, then, uh, That is where we are
going to have all the connections, ready
here at the bottom in the little window,
what we are going to have are reports,
data dictionary reports
defined by the users, modeler,
etc., so that is the way to
interact with it. To interact
with the database, what do I have to do?
Connect, access the
database. Because the database is there,
so you have to access that
database. How do you do it? Well, that is
the connection, right, we already saw how we
did it, and yes, and the password, we said we
were going to put 2 3 4 5 6. At this
moment, it is connecting to our
Stay eh database
called XE which is the Oracle Express
which was the one we learned to develop
ready Look it is already connected now what
can I look at Ah I can look at the tables
for example these are the objects that are
certain the
views it is perfect Look you have all
the indexes the packages the
procedures the functions Ah there I
navigate between the objects Yes you do it like that
Look it is very easy So in that way
you will interact Ah well And if I need
to make a query Ah, you're
going to make the queries in this part, on the
right side. That's where you're going to be able to make
queries. That's where you're going to be able to make
queries. You've seen how our
developer works. So that way you're going to
interact with it. You have some menus at
the top: file, the typical
file, save, close,
eh, open new, etc.
eh, editing for the data modeler
to undo, to copy and paste, search for what we
know that they are going to undo,
to see the different tools
that they have. If you realize there are many
tools that are very
interesting, some of them you don't use or
understand. So in that case,
simply under the need, you're going to
see them knowing, eh, the tools, the
execute, but it's when we are
running the queries, eh, the
source menu, the team menu, the
tools menu, very important because
we have certain tools that are going to
be very interesting and that have been
integrated into this version of developer, it
was really very limited and today they have been
growing, so to
take it into account, That is what your
interaction with the manager would be, right, the
manager is the SQL developer. Ah, we're missing
the the connection eh with the user hr Look
then we create the one at once hr and I think
our password was 1 2 3 4 5 6
then eh let's try it to see if it
works eh look here it appeared
correctly so let's connect
the connection name is not valid of
course I have not put that it is our
connection hr Now yes let's connect eh
Look it has connected These are the tables of the
user hr Congratulations we already know how to
connect how to interact with the
manager Now we just have to go to the
practical part very well let's talk now about
functions yes it turns out that when you are in
databases Well you need to use
functions the functions are going to serve us
because they generate a result so
basically a function enters some
parameters as we are seeing in the
slide Look, enter some parameters here and it is
going to
result in something it is going to give us something
for that is what we need the
functions and if we do it properly
that something that is given to me is the
result that I expect Yes so in
Oracle we have many functions in
the different database engine that
you are working in You are going to have diverse
functions and those functions can have
various characteristics Here In
particular, we are going to look at those that are
single-row, that is, a
parameter arrives and I get One, I process something and I
get a result. Or I have a set
of rows that come in, just as you can
see in the slide, and I get a single
result, which is what I'm
looking for. There are some particular functions
that we're going to call single-
row functions. For these functions, for
single-row functions, we have to
manipulate data elements that accept
arguments and return a value that
act on each of the rows that are
returned, uh, that a result is returned
per row. The
data types can be modified when we
are working on them, that they can be nested, that
is, I can have some functions
within others and that can
accept arguments that can be either a
column or an expression. The form or the
syntax that you are going to use to work
with functions, well, you have it here below,
here I am showing it to you. And then that
is the way how you can, uh, how we are going to
interact with
single-row functions, let's continue to the next one, okay.
So what I have are single-
row functions, then I have character,
number, look, how interesting,
date,
conversion, or general purpose,
we're going to use them. to call general purpose Those
are the kinds of single-
row functions that we're going to
have It turns out that There are times when
you have to work with characters The
best thing you can do is take advantage of the
character functions Why Because
they will allow you to do different
things with the character strings that
you want to operate So you have character functions
eh There are some that are conversion and there are
others that are manipulation Which are
the conversion ones Ah when you have
lowercase letters And you convert them to uppercase or
when you have uppercase letters and you're going to
convert them to lowercase for example lower That
is the function that goes from uppercase to
lowercase or aper is the function this
apper is the function that goes from lowercase
to uppercase and this incap is the
function that uh makes them look like a
title, that is to say the initials in
uppercase and the rest in lowercase. These are going to
be very interesting because they operate
on large volumes of data And in all
the records then you can operate
and modify the queries can
modify the data that you are
bringing to show And that is going to be very
interesting Well on the other hand you have
the character manipulation ones you have the
Conca the substring the leng the INS
the lpad the rpad is to remove spaces
or special characters from a
particular string the trm and the Replay
normally we see them in this other slide
eh a little clearer ent for example let
's look What are the conversion ones
Look at the the syntax is sql c and the
eh the result after applying the
function Well, we have eh
the context in lowercase or it comes in upper
and lowercase in this case in Upper and what
you and what you find Is that it
transforms eh it transforms the entire text
all those characters to uppercase or the
inap look it comes the same as the
previous one but leaves the
first one in uppercase as a title type and
Then then Great because that will
allow you to flow with character functions
on the other hand eh you can see it applied to
a query this query if you want to
run it there in the example scheme that we
have you can do it perfectly it
will run you have no
problem for it to run if you do not
have that scheme but another It does not matter
With what you use the function as you
are seeing it here you're going to be able to do
then it's not complicated if you
see the in the first uh you're looking for a a
last name, that is to say you make the
query Then that Select Looks for an
employee named higgins who has the
last name higgins and it tells you there isn't one
But you're going to see and if there really is one
uh What's going on there Ah that
the initial of higgins is capitalized
then in this in this query that you have
made or that has been made as an example
Well look that the initial is not being contemplated in
capital letters so that's why it
tells you that there are no records So what is
done to solve the easy issue I
arrive and take the la the field that I'm
bringing which is Last name Look if you see
that Last name is being taken and to Last
name I apply ler and when applying the ler
to Last name then it immediately brings me
to the record because what it does is that
the entire string compares lowercase with
lowercase, that is to say what it brings it
converted it to lowercase and you are
giving it the string in lowercase that's why it
already gives me an r lro I hope
this has been very illustrative of how you
work for example with
character conversion functions.
Remember that they are widely used and
when you have character manipulation,
this table would you believe is very
telling because it is telling you what it
does in each case. Look, in the
first case, concat is joining the
two words substring, right. It is
only taking the first to the fifth
character, len is counting the
characters, right. INS is showing you
where the letter W is. The epad is
replacing an initial character that
can be on the left or on the right,
like l is on the left and repat is on the
right. There are times when you are going to have to
interact with the characters, that is,
manipulate them. And that is what
these character use and handling functions are going to be useful for. So I
have two
types of functions. Which are they? I
can use them for conversion like
lower apper or init Cat, which simply
convert them from uppercase to lowercase
according to the context or whatever you
want, or I can have
character manipulation functions where I take
the string and modify it in terms of
my needs, ready, so both are going to be
very important for you.
keep in mind well let's look at the
conversion then conversion It's easy
look the string I want to convert
is sql c the sql is capitalized and and the
c the initial is capitalized but the
rest is lowercase and Look at the result
if I apply l everything is lowercase
if I apply to aper Well everything is left
uh everything is capitalized and if we don't
apply incap we are left with the initials
in capital letters it's going to be very valuable when we
have to convert
character strings and it is difficult for us between
uppercase and lowercase letters
well and on the other hand uh Ah well let's look at
the example So the example look you
're looking for the employee higgins you can't
find him but the database is there
But why didn't it find him Ah because the
initial H of the last name is capitalized
Ah well then what do you do for that Ah
well you use a function like laer so
that everything it brings back is
lowercase And then they compare
lowercase with lowercase And what do you think Well it
brings us the record then if you already
know how uh to carry it out successfully
well let's look at another type of functions
of manipulation And then in
manipulation in this little table we have a
very cool and very practical summary and it is
we take the function we apply the
parameters remember that every
function receives some parameters
I know that so look what you enter two
words and in concat what it does is
concatenate the the substring what it
does is it shows you the first
five len what it does is it
counts them INS eh what it's going to do is
Search where the particular letter
or the particular character is that I'm
looking for within the string the epad
does a fill based on what it
finds Yes and the epad fills it with eh
asterisks in this case on the left that's
why it's the L for Left and the rpad right
on the right it's the same but
basically What is the teaching
when you need something when you need to
modify characters Look for a function
someone already did it, that is Don't
complicate it everything is already done So you
already know how to use a function and How to
modify
characters Well it may happen that for a
particular exercise you need to
nest the functions But what is nesting
functions it's not going to be more than a
function within another and what
can it be useful for? Well, because that makes a
very robust expression, apart from putting the
capital letter, I can convert it and apart from
that, I can count it, that is, you can
do any number of very
valuable things. So what do I need to
nest the functions? understand that it
starts from the center outwards, so that's why
you're seeing here that this is
the first step, the second step is the
outermost one and the third step is the
outermost one. How many expressions? Well, the ones that
you normally need, I can do it, I don't
use that many either, but I can
do it and it's totally valid. So I
can do nesting of functions. Show me
an example, look here you have an
example if you go now with at this
moment you and run that query, it will
run. So there's no mess
now let's look at what it does,
look what it's looking for the
last name and what it does is use the
nesting of three sub functions, String,
concat, and apper, which we've already seen. And
then they are in a single expression and
the result is that the one that is
giving me the query, yes. So it's
doing a substream to take some
characters in particulars true
then it is doing a concatenation
to join it with something and then what it
is doing is making it
uppercase with oper look you have the
way to nest expressions in sql
especially functions do not forget