Transcripts
1. Introduction: Hello and welcome to
database systems course. My name is event Joshua. I work as a Database Engineer. Today I am here
to share with you the skews that I got from taking databases at university
and now that I am using relational
database at my workplace. This is like so this is like a short description of
the course in general. This is just like
the introduction to the design and use
of database systems, which are systems which manages every large
amounts of data. This course is an introduction
to the principles, use and application
of database systems. And students who
complete this course, we'll be able to
design and create databases and be able to extract information
from databases. Understand in broad terms, our database systems
wake and understand the purpose for which
database I used. The prerequisite for
this course is that you are someone with motivated
to learn databases. So it doesn't matter if you're majoring in computer science. It doesn't matter
if you're actually working as a database
administrator or engineer. As long as you are eager to revise your skews or
learn a new skew. This course is for everyone. Also what you need to know
about the structure of the course is that
the first part of this course is the theoretical patch of
relational databases. And so for the second part, which is the practical part, you'll be faced with
the database schema. Where together we are
going to create a database following the principles that we've learned from the
third part of this course. And after creating the database, we are going to insert
items into the database. And after that, we are willing to query from the databases. We are going to have a set
of questions which we hope which will guide us in
query inform the databases. And after the practical part
of the course to adjust, make sure that you fully
understand the concept. You'll be just to create
a library database, which is like a database used to manage the activities
in the libraries, such as people borrowing
from the libraries, such as information
about the author as the books available
in the library and everything about a library. That is going to be the
final part of this course. I hope that you guys will
find Michelle my skews about database systems useful in your carriers
in one franchise, in everything that
you are going to use. These skews full. So I hope you guys to understand every
part of this course. And in case you don't have the environment to do
to creating a database. And think what you only need
to have is only workbench, which you can download online. If you guys have any issues with downloads
in the webpage online, you can leave comments
on the comments section. And I'm also going to make
sure that I lead them. And if there is need for me
to teach you how to download, install Workbench, I'll
create a video on it. Thank you, and I hope you will enjoy the rest
of this course.
2. Theory aspect: Welcome back guys and two
kicks that with this course, I'm going to, we're
going to talk about the common uses of
databases systems. Database systems can be used in the banking sector to keep
the customer profiles, to keep information
about transactions. The databases. Database systems can be used in the library as a library
management system to keep information about
books, borrowers and alters. And also database
systems can be used in the health sector to keep
information about patients, their quads, hospitals, ETC. And database systems
are also being utilized in the social
media platforms to keep information about likes and comments on whereas
post midway people. Taking a step back. Before database systems,
they used to be file-based systems which were
used by lots of companies. Here on screen is an example
of a file-based approach to data management where they're like different files
for each department. And as you see from
here on the screen, this is the invoicing
department, which has tau, which contents, customer number, customer name, and fat code. And here we have
a CRM department which contains information
about the customer name, customer name, and turnover. We also have the GIS department, which has scotoma now by
customer name and zip code. As you can see, there is duplication of data, specifically the customer
number and the customer names. And as a result of duplication of data
meant to store def couch form these companies
to integrate their datasets and to make
sure that when they're running queries the query from different departments because
of disintegration of data. As I said earlier, these are the main problems
with pro based approach, which includes data redundancy, strong dependence between
applications and data and difficult in integrating
applications and data. Hence, this med,
the evolution of data-based way of keeping
very large and integration, integrated amounts of data. When we are talking
about databases, we also need to mention DBMS, which is a software
package which is designed to store and
manage data basis. So hence to have the
database systems, which is like the
title of this course. It is about database plus DBMS. What are the functions of
a DBMS? You might ask? It didn't. They include
data independence, concurrent data
access, security, and is the recovery from
crashes and data integrity. As we go deep and deep in, deep into databases, you need
to know what data modules. A module is just a collection of concepts for describing data. Details of data models
that we have added. Let us know Modules, network models, highly
highlight column models. And this course you need to
know that forecast is on relational DBMS and this
relational database model, the involved the
use of tables and each table Italy
lesson has a schema. So what is this schema? Schema just, just describes the coulombs
or the feuds of installation. Now we are going deep into
relational databases, which are like the most
commonly used type of databases by
main organization. Organizations. And it's something
that's mostly you'll learn about if you take
any degree program, each interface city which
has database systems, It's mainly you will learn
about listener databases. Yes. So now we are talking
about how to clear team a database and execute a
listener database and execute. The SQL command for
data definition is to create statement used to
create schemas intervals. For instance, if you want to create a database code laboring, what you need to write is
cleared database library. And this which is in red, who automatically create a
database code library for you. When you want to
clear the tables, you also use the
create table command, which specifies a new relation by giving it a name in space, specify its attributes
and initial constraints. This is like the principle
of creating a table. The attributes are specified first and it's attribute
is given a name and a detailed type to specify
its domain values into any attribute constraints
such as naught now, so don't worry if you don't understand this at the moment, we are going to do the
practical parallel feet where you will see what we were talking about when we say to the attributes
as specified. Yes. Good news. We have an example of
creating database. The lessons declared
through create table, I'm sorry, it's cleared, stable. The lessons did create declared through create table statements. Our code base relations,
or best tables. Best lessons,
differentiated from virtual relations
created through the create view statement, which may or may not quite exposed to an actual
physical file. This is like a basic structure
of creating a table, and in this case we
are calling it oughta. So as let me go back to
the PFAS client, this one. So when we say that the create
table command is used to specify any related by
giving it a name and specify its attributes and
its initial constraints. So as you can see from this, the name of the table is, as you said, create table. This is what they mean by giving it a name and
specify its attributes. And what are the
attributes here? Here? The attributes at
the ID, the name, and then the age, and what does the note now, vodka an int Min. These like the datatype, which are used to specify the domain values
of each attribute. We have like the, the int, the fica, and the int again for age, so only means integers. So the value that is allowed
for ID and age are integers. And for the nymph,
characters can be a mixture of late as in other numbers, but most, most often
the Allied just later. Then this team here
that you can see here, I think you can see it. It specifies the limit
of the number of characters that are
supposed to be in the name. It's also a way of
securing your database, meaning that someone
who doesn't know the specified limit
of characters might, might find it
difficult to temper around the database. Okay? So when you create a table and you want
to drop that table, you use, you use a command
called drop table. So this command is used
to remove any lesson which is In relation
and its definitions. The lesson can no longer be
used in queries, updates, or any other hormones since
its description no longer exists from the table
that we created earlier, which is the author table. If you want to drop it, you just say drop table. And this query will automatically delete
the wall at a table. Sometimes you might realize that you need to make some
changes in your table. What you use is the
outer table command. It's used to add an attribute or a new column to one
the best relations. So the example is like mid-70s. There is a table
called student and you want to add agenda Coulomb. So the command is alter
table students and Ed Kalam, gender induced specify whether it's cow with that
slacker, with its int. The database users
must do enter a value for the new attributes gender
for each student tuple. This can be done using
the update command. The alter table command
is used to remove an attribute or a column
from the relation. For instance, you can say, you can say outer table
student drop column, gender, cascade. You see if you might
have seen earlier we had a foreign key in
one of our tables. So a foreign key
with cascade delete means that if a record in
the parent table is deleted, then the query
expanding the codes in the child table
automatically be deleted. And this is code is
cascade delete in FQHC from cascade delete or update. The row from the parent
table and automatically delete or update the matching
growth in the child table. So whenever you specify cascade, it means that delete
this row from wherever it is appearing
in whatever table. You have to delete it. That's when you specify
cascade, delete or update. Then we move on to insert, delete and update. In SQL. This three commands
can be used to modify the database switch at
the insert, delete, and update instead is used to add a similar
tuple to a relation. So when we are inserting, we must specify the table name and at least of
values for the tuple. The values should be listed
in the same order in which the quadrants pointing
attributes with specified in the
create table command. For instance, if you want to
insert into student table, we need to say insert into student and then we
specify the values. In this case, 111 is the ID, AMA is the name, and 29 is
the edge of the student. When we use a demand
delete command. This command removes two
posts from a relation. But when you want to remove
two posts from a relation, you need to have a where clause to select the
two posts to be deleted. Else if you just use
delete on its own, the command to delete
the word table. The tuples are deleted only one. When one table it time. The tuples are deleted
from only one tip. Unless cascade is specified on a referential
integrity constraint. When there is no workloads. It means that all tuples in the relation
attributes delimited. However, the table remains in the database is an empty table. The number of tuples deleted
depends on the number of tuples in the collision that
satisfies the width laws. This is, these are the examples
of the delete command. So let's say you
want to delete from an employee table where the last name of the
employees Brown. You use the first
delete command, which is Delete from employee, where name is caused to Brown. If you want to deploy to
delete from the employee, the SSN is equal to this number, which is one to nine. You specify Delete
from employee, where SSN is equal to that
number which is given. And if you want to delete from employee where d
is close to five, few specify again, Delete from employee where t
is close to five. And this make this
command the last one. We'll go through the
employee table loop form the DNA attribute and
search with the DNA. Dna is equal to five into
to delete the tuples, which satisfies this condition. Yes, then we move
to update commands. For update command is used
to modify attributes, attribute values of one
or more selected to post. As we said earlier in
the delete command. We said, let him the delete
command about a where clause. In update command, we
also need to specify the where clause which selects the two posts
to be mortified. Some additional SET
clause specifies the attributes to be
modified in the new values. Here's an example of
the update command. So for instance,
maybe want to change the location and controlling
department number, or maybe projecting to these
and five respectively. What you do you say Update
Project and set P location to the name given to
the number given way, where project number is
close to ten. Like this. This is how you update. Sit in table in databases. If you instead and you make
changes to your databases. You always need to
remember is you have to get the information from the databases are presented
to sit the number of people to different people to the organization
into a flat fence. The information useful, this gets us to retry entering
data using select to retrieve or the attribute
values of facilitate to pose S tag the star that
you are seeing is used, which stands for
all the attributes. For example, we
have examples here. If you want to select employees, you want to select from the employee table where D
and voice goes to find few, say select all the
oil is represented by this star from employee
where voice calls to find. If you want to
select from employee where and maybe want to
select from two tables, the employee and the
department table, where the minimum cost to reach search and DEA number
goes to d num. What you need to do is to say
select star from employee. And then if you add to select
from more than one table, you Nim you lift those
tables and separate them by commas like employee,
coma, department. And what's the where clause? Where dynamics goes
to research in quotes and d goes to d number. Yes. But then sometimes
do you realize that? But then sometimes
you realize that when you do your select query, it's made the same employee
a number of times, which is like
duplication of data. It happens. So to avoid that
you use distinct. You say select
distinct lake here. You say select distinct. So you are telling it that it should be just
one similar thing. You shouldn't have the same name being repeated again and again. Okay? It also what you need to know about databases is that you can also do some mathematical
applications in databases. You can use the, you can count the number of people
in your setting table. Looks like by saying 12345, you can just write a simple
line which will go through the database and count people with specific
qualities that you want. You can some, maybe you have
an attribute which has, which has numbers,
may be the salaries, which represents the
salaries of employees. You can also sum those numbers, those salaries of the employees. You can also find
a maximum number. Let me just say maybe
in terms of salaries, you can find which
employee ends like the highest amount
of money from ACT, organization through
using the max, you can find the mean. What's the minimum salary? You can find the average lake. What is the average salary
you do need to go into cut. Calculate like every
employee's salary and divide by the total number of employees who don't
have to do that. You just Nimitz jesse, this mean that and then it'll automatically
provide you with the mean, the average salary,
the count of the sum. Let's have an example
of query which is like you have to find the sum of the salaries of employees, the maximum salary,
the minimum salary, and affiliates average
salary among all employees. This is how you go about it. You say sunlit and
then selecting, say select some salary. You put your salary in
brackets, like here. Then you separate
them by chroma. Maxillary mean salary,
average salary from employee. So this will automatically
give you the SAM, the max, the mean, and average of the salaries from
the employee table. Some SQL implementation
may not allow more than one function
in the select clause, but some are dance to allow that in case yours doesn't work. Sometimes it might be an issue with the SQL implementations. As we move on, we have what we call aliases in
database systems. Sometimes you may find
out that when you are running a database query, like for those who have done
database databases before, you come across like
the era which says an ambiguous to tell
something that's like maybe the query's ambiguous. To resolve such kind
of an ambiguous era, you need to specify
Alice says IS, is, is like let me say it is so normal in SQL to use the same name for two
or more attributes. Those attributes and
in different lessons. Sometimes when you query from query that refers to two or more attributes
with the same name, must qualify the attribute
name with the lesson Nim. By prefixing the relation
name to the attribute in. Here. For instance, you want
to select the name of maybe Nim of an employee
in the employee table, and then the same employee name is also in the department table. An example of an ileus
is saying, Okay, this is what you might do like employee department genome. But if you specify, instead of saying employee, if you just may be specific, you just specify one
later which you use to call out one like an
entity from the table, for instance, the
employee table, you can just use
two coordinates. Maybe the next table you
can choose to call it W, the score table, or whatever table you can
choose to call it S. To avoid ambiguous betas. Let me go back to the
slides so the eyelashes can also be used in SQL
query for convenience. So it can also use the is keyword to
specify a layer says, for example, select
Edit, fname, fname. This employee is, we are saying that
the employee table we are now calling it is in the employee is S. And where it goes to
this too afraid, the ambiguous area you
see we're calling, we are creating from
the employee table. But if we don't specify aliases, SQ, be confused and to
print out an ambiguous era. So it's not always that you
have to specify the width. Sometimes you can
just say employee, then space and then e.
Employee space is always deselect the two main methods of specifying the
aliases in databases. This is like, it was like
a quick run through of the history of databases up to the principles in databases, up to like using aliases. I'm up to like inserting, deleting, updating
tables and staff. But I hope you guys managed to pick things from the theoretical
patch of the course. Don't worry yourself. Each few didn't
understand some concepts from the theoretical
part of the course. Because if an in the
practicals are going like big to where we took them both may be querying
from the database. And then with examples, you fully understand what
I've been talking about. Nts. Thank you for paying
much attention throughout the theoretical
part of this class. And now we are moving
on to do the practice. And then after the practicals, we will move on to introduce the project that you
guys have to do. The aim of the project
is to make sure that you apply
these cues that you have gained from both
the theoretical part of the database systems and then the practical part
of database systems. So thank you once again guys, I hope you have linked
in math test must stand one or two things from
the teaching session. And I look forward seeing
you guys also watching the practical part of the core switches in the
next couple of videos.
3. Practical introduction: Thank you once again guys, for making it up to this
stage of the costs, this level of the course. Here what we're going to
do is to do the practicals of the database systems,
relational databases. From the previous classes, you realize that
we've been doing the theoretical
part of the course. And as I mentioned, this is the protocol
patch of the course. To just introduce the project that we are going to carry the
practicals of this course. Here we are going to create
an employee database whereby a defined
relation schema that you are going to follow. Also, I've said around
five questions, or the questions will guide us in how to query
from the database. From the relation
schema that you can see here on the screen. We have like we are going
to create full tables, the employee table the week
stay with the company table. In the managers table, you want to see
inside the brackets is the attributes
pay each table. For the employee table, we are going to have
three attributes, the employee name, the
street, and the CT. In the next table,
we are going to have also three attributes, the employee name, the
company name, and the salary. In the company table, we are going to have
three attributes as well. I'm sorry. We are going to have
two attributes, the company name and the city. And in the managers table, we are going to
have two attributes against the employee name
and the manager name. Here are the five questions like that to help us
in doing the queries. And just to give
you a general idea, when I say we are going to
query from the database, we're going to select
specific information from the database which is used to foo in the project that
we are going to carry. So for instance, we
can search through the database and only pick or select employees who in more than $10 thousand,
we can sell it. People who work for a specific
bank has specific company. This is what we are going to do. I hope you guys are
willing to inject this practical part
of the course. We'll be able to apply it
when you are faced with any database question
for an information this type of the credits that
you have been to run a more advanced queries, not like the normal query. So yes, Basically this is, I think this will actually
help you to understand, improved their teams databases and creating from it the tab is, thank you and see you in
the next session where we are going to kick start
creating the database.
4. Creating the Database: Welcome back guys. I am so excited to have you have magnitude
up to this stage. And as I said earlier, and right now we want
to get our hands dirty in creating the database, Integrating from it,
yes, Let me know, twist time and dive into
creating the database. So to just make it easy, I'm just going to copy
this schema here. I'm going to copy this schema
pasted on my wet paint. I'm going to do the coding. When you are creating
a database is received from the theoretical
part of the course. We said that UCF, to specify that the tab is, we want to create a
database once the name of the database
employee in this case, don't forget to put the
same colony is useful whenever you are creating
database is part of the syntax. If you forget it, it will give you a hint as to cost you lots of you're
attempting to debug it. And then after specifying
the name of the database, you need to say to use employee so that whenever I want
to use the database, you can easily call it big. It's like part of
the syntax in 3D. Needed. Yes. After saying Create
Database US employee. Now, we add to
create the tables. The first table
that we are going to create is the employee table. As I told you earlier, database is one of these topics. And you realize it that when
you are writing the codes, It's about English light
creates this, select these. So it's not like anything
gibberish or complicated. Right now we want
to create a tip. When we say Create. I love it when I put my database create table in
capital letters, it's mixed. My work looks nice, but it doesn't matter even if
you write it in small caps. So create table and what's
the name of the table? It's employee. You
have to specify it. Employing TA, you put brackets. And here you put black interior, you put brackets and don't
forget you semicolon. And if you say click
Create employee. And what are the attributes
of the employee? First, employee
name, employee name. Let me use an underscore here. Employee name. And as we said earlier, when you specify an attribute, you have to specify the
domain of the attribute. Is it supposed to
be in an integer? Is it supposed to
be only let us, how is it supposed to
be showing this case? We want to be valid characters. I saved vodka and then I specify the number of characters that I went inside. The employee named we have. And in this case I wanted
to have like 40 characters. So the point in space saying
the fica is kind of a way to also protect your
database from intruders. Like if someone doesn't know the limitations that
you put in the database, in the one to the
trade exploited. Basically Though filled
because of the restrictions, the forecasts, the
limits in staff. And then you put a comma here. This is part of the syntax
in creating databases. And then the other thing
that you would add, the attribute that you
need to specifying the street, the street. And then it's flat again. And the streets, let me just
say maybe five characters. I move to CT VAT again
because it's a name. It can be numbers
if we do as numbers put in, like for integers. Let me say it again. When the attribute is the
last attribute in your table, you actually don't
have to put coma. You just indeed like this. So basically this is
the syntax to create a table is I mentioned earlier in the theoretical
parts of this course. Then the next table
that I have to create here is the Whigs tables. So the same concept applies. So you say Create, Create Table. And what's the name
of the table here? Let me participate. See. It's weeks. We want to know where
does one employee wake? The next table is
the Whigs table. To just make sure that you don't forget the semicolon is just put it before you start
specifying the attributes. What we have here, if the employee name is usual, employee name, the
employee name, and we specify the
flat bar again, which is 50, which is 14. We specified above it, we put our coma and
then company name. Company name. Let me just put 14. In case of those companies
with long names, you need to accommodate them. You can put up to 255242. These any number that you want in that you think is
relevant considering what kind of attributes you
expect to have in the tables. Then salary. Salary here, Let's just
let it be just an integer. Then we move to create another
table which is company. And we say create table. I said your brackets
and then semicolon. India specific attributes. We have company name, NAM. We specify the limit for two, then the C chain, then the flat cataloging. This suggests two widths. It's coma, the CT.
Basicaly, this is it. Then we create the last table which is the manager is temp. I think you can see
it from the schema. Here are the tables that
you're supposed to create. The left table is the
money jeez, table. We specify create table. Then it's meninges. Then our brackets is usual. Then the colon. Then we specify
the employee name. Do we say we give it a flag? We have the money. Fourteen. Let me take off this. It's no longer needed. We just wanted to use
each to make sure that we create our bows without
granting full big and forth, like going to the
weight took and staff and coming to work bench. Basically, this is how
you create a database. Here. I just created a
simple, simple database. For your understanding. Let me run this
to make sure that everything is correct before we start inserting inserting things into the database
like the names, the addresses of the
employees and staff. So let me run
everything and see if the database is correct
in this node era in the database. We were able to run the database
in this new era in it. This is it about
creating the database. In the next video is going to be on how to insert items
into the database.
5. Insert into the Employee table: Hello guys. I'm happy
that we have created the database so far and
we've created our tables. And the next step
is to insert into our tables so that we
can create it from it. Yes, I'm going to
show you how to insert into the table
and I'm just going to insert like about
ten entries in each and every table to make sure that when
we query from it, we can find as much
information as we need. I mean, you can instead
as many as you want. But for this case, since we're just learning how
to do it in a simple way. Yeah, so we are going
to just create, if we're going to insert a
few entries in each table. For the first set of the work. We want to insert into
the employees table, which is the first table. So we say instead the code
is insert into employee. You need to specify
the attributes first. Employee name of the attribute
we're employing them. Make sure that you don't make mistakes with the spellings. Spelling that you gave here when you specify the
attributes should be the same. Then treat, then C team. What values do you
want to be there? So you save value. Then that's where you put the values that you want to
be in the employee name. In this case, let's say
the employee name is Josh. Then you specify it by comas. It makes sure that
your values in quotes. Yes. What's the value for this c. T, Where did she come from? We say maybe she come
from IF and green CT. This may not be
necessarily says that, you know, I'm just thinking
of things to put here. Yes. What is the city name? The street is if a green street drains let me put ST2
representatives treat, then the CTE is green. Ct put Z to represent
the CTF is a seed iliac, don't forget your
colon semicolon. Then we said we are
going to insert like ten entries in each
and every tables. So the next thing is instead
again into employee. Then we specify again this, but I'm just going to copy
from here and paste it here. What are the values? The value you just
save Alina to undoes. Mammary. G also comes from the Green Street CT.
From the if and when. She's from if a
green states, sorry. Yes. And then here we come to insert again,
insert into employees. So we're going to
do this ten times. And trust me, this
is the part of the databases that I don't like, especially when you have
to create your own data. Lake. In this case, I need to think of names
to remember from college. Normally you have to think of the names to put in the
database that you are creating, unless you are creating a
database with specific company. But for learning purposes
you have to just think nims or companies or anything to include in
your detailed piece. Here. Maybe let me say She's
he is from South Street. From the South seating. I forgot the quotes. Trust me. And sometimes you
realize that after. Thinking of the nims to
use in the database, you might find out that
when you try to query from each node to give you the information that
you are asking for. And you have to just play around with setting
values to make sure that leads to get the information when
you run some queries. Sit in. The spelling is wrong. Yeah, I'm sorry, let me
say it again into this. What are the values from here? And then it's continuous
limit do copy and pasting. Because it takes time
in denote coming, just be changing the values. We have 1234567819. Okay. Mike, and then these are the pacing laugh Mike and then this other base
and I can call him Jim. I'm just trying to play
around with the weights. So stretch through
to think of names. Sometimes MDS change, just change things that
I want to check. I can change. Finding some crazy
name to put here. For this treats our
CSO is what I think is quite efficient to
do is to make sure that I don't spread the
employees too many cities. Let me just say, okay, maybe they come from
three different cities into utilize the if and clean. If a green South is, is in, let me save
this one is south, east and east green. I will not waste my time. I just make sure that you
understand what I'm doing here. Make your weight easier. Ct this and these south
into supposed to be solved. Okay. We are done with inserting into our
employee table by dt. One thing that I forgot
to tell you is that sometimes you might run your
database once it's created. If you tried to deny
it, run it again, it will give you an error
because you to start from near created database
into to tell you that the
database is created, you need to specify
one thing which is like drop database. Exist. We are done with inserting into the database into one thing. Before we move on that I
forgot to tell you is that when you run your database
the first time it will create the database
and store it in. Sometimes you might
need to run it again, random walk into to
give you an error saying that the
database is created. So what do you need
to do is to say drop database so that you want to issue when you have
to run from the beginning, Let's say you are done to wake. You want to run from the
beginning to make sure that everything is waking, need to include this
drop database employee so that it won't give you in it as saying the database
is already depleted. Let me run it again
to make sure that my insets saved in the acquired, this new era into my first insets is done running. It is inserted into
the databases. But to just make sure
that we see the table, let me run the Select select from where from employee. I just wanted to see the table with all the details we need. I just wanted to run this code, this line of code,
so I light it. Yes, Here you go. This is the table
that we just created. In the next video. I'm going to insert into the Wix and then probably combine it with the company
in the manages. Thank you.
6. Insert into the Works table: Welcome again. The next step here is to
insert into the wax table and also be able to insects in the same video
into the other tables. I'm trying to blow
out this so that we can see what's happening here. Let me just close the Temple. Yes. So we want to insert into the table and then inserting the company tip
when the managers table. We say instead, as
I said earlier, you say insert into this
time around it's Wix. Then in the next table we need the employee name and
the company name, employee, the company. Now, here we need to make sure that the
names that we specify, the names from the previous
insects that we did. That if we tell it
to query the nims, each one to confuse the
new names in the week, stable in the names and
the employee table. Because here it's just like specifying the employee table, but we want to
match the employee, the employee from
the employee table with the company name. So here we have
our infant Jewish. Jewish. Jewish. Just Schuller in,
let's say the bank. Let me just check something from the equations that I
have to make sure that I include the banks and the inflammation from the
equations in instead queries. So that will not waste
time coming back to the search queries
and credit and boom, here we have bank cooperation. Do we have first bank
cooperation in width, small, big corporation,
some Geoff Green to create. Let me just say I'm
going to create. I'm going to make sure
that my employees week in one of the three banks, the bank cooperation,
the fifth been cooperation and the
smoke and cooperation. Yes, let me come back here. Being cooperation and
the bank cooperation and this moping cooperation control. I just want to make sure
that I don't waste my time. If I do Control V, I'll just edit the values, then repeating the inset
into over and over again. So here we have 12345678917. The next person is
memory GA let me just put next best thing is J Jewish. Let me put them here. The next person is. Then the next person is lav mic. The next lesson. And just drink, copy and paste
so that I will not waste my time doing these bodies. Like the one of the important things
that you need to do when you create
the timepieces. You can't create it from it. If you teach in, saved in it, Into can't jump, you can't run away from it. The integral part
of the databases. You also need to
understand how to insert a mixture that you
don't make mistakes. Yes. This one is from
bank cooperation. This one is one
bank cooperation. This one is from small bank,
small been cooperation. This one we can say it's faced bank, small big corporation. Small beam, fifth bingo. Bingo appreciated Pinchot. Cooperation. Yes. To also check if the okay. So let me run this the inset into the Whigs table
to make sure that it's running is done. Yeah. Inserting two weeks. Insert into weeks where I want to just select
from weeks. Select. I'm selecting
everything from weeks. I just wanted to see
if it actually worked. I'll be able to see it table. Okay. They seem to be
one thing that I forgot. We have salary in the weeks. We have salary in the weeks. It's not just about
weeks company name, but we also have salary. Let me 85 to 55 to five coma. Coma. These are the mistakes that you made actually come across, sweep along the way it happens. Let me quick tie power. As we said earlier,
it's an integer. We want you to be a huge
number to make sure that I think we have
equated that to request to select people
who ends like $10 thousand. So let me just make
it this one is 10 thousand, 15 thousand. Sorry, I'm not
supposed to include the quotes because
it's an integer. This one is two thousand, five, thousand, fifty thousand, forty thousand, fifteen. The most important thing is
not to forget the comas. Have to remember them. Safa, trying to
look for an error, but you will not be
able to find it. We don't like such kind
of things happening. Let me then if you see
from the beginning, see if we are making
progress so far. This is the wakes table. So, so far we can see that we were able to create
the wax table. And let me comment out this. And now let's create
the company table. Yes. We are going to create
the company table and the last table, the
managers table. In the next video,
since the half-life, a few attributes each, it will be easier
for us to combine them in the next video.
7. Insert into Company and Manages tables: Welcome back guys. And all the information
is quite exhaustive. Bytes. Yes, it's wet doing and following
every step in databases. And the next step here is to insert into the company table. We say is usual
instead into company. To make sure that we
don't make any mistake, we need to check the attributes that we
have in the company table, company name and seating. Seating. And then as usual, we specify value name here we have the value is already
specified the company name, so we don't have
to stress ourself. Thank you. Of the nims to
give for our companies. Yes. The city let us use the cities that we specify
for the employees. Let's just use those
cities through that. You don't have to
stress yourself again to think of new cities. Yes, but if you have
lots of cities in mind, you can use whatever cities
that you want to use. Yeah. Then let me just copy this to
make our weak quite easier. Paste it up to I've
made the mistake here. Copy and paste it two
entries in one line. 123456789. Yes. The entry could be a bank. Let me do Bank Corp year
Bank Corp. Ben cooperation, so that I'll know,
twist my stamp. And then here it could
be the small bank. Bank cooperation. Cooperation. Cooperation, cooperation Bank, Corp. Let me see. For me, I had three small
big corporations, IF1, 230 B-Corporations in a hedge to fall fest Bank Corp. So let me just try
uniformity. Yes. The cities we had, the south and the east
CT and the green CT. We need to make
sure that we don't repeat the same CT for the banks can make it South seating South CT. CT. If agreeing, then
sets it the South. If a green salt C T, C T, if a drink salt CT.
Then we also want to select from the company to just make sure that
everything is working guys. You don't need to go and do everything
income big and realize that some parts of
the codes I know tweaking it to be so
hard to depend error. In this case, I'm just
going to run this. I pray it's waking this new era. Let me see if I say select from these to
see if the table okay. What tip into here? Let me run everything
from the beginning to see because it's repeating
the same cities again and again and again. Okay, guys, I know this
is part of the work which is TdS and monotonic because they're repeating the
same thing again and again, but its width, its stress me. Yes, this is our
table and it's fine. Yes. Then from here, we need to move to let me highlight this so
that when I done everything, I don't repeat the
ciliate from company. This sofa is not like the creating that
I was talking about this just to make sure
that everything is recorded in the table. Yes. Now we're creating we're inserting into
the managers table. Instead. As usual. Into meninges. We have the employee name
and the manager name here. Employee Nim. Nim. Let me confirm from the table. Let me confirm what's
happening here. Let me confirm from the tables. We have employing them
into manager Nim. We need to specify the value
is always, you know what? The names of employees
in the employee table, just like assign one person is a manager or
someone, you know. We're just trying to
find information. So we're just going to say in those employees that we've named here in the US,
and the managers, and the managers are
also employees like who can everyone is an employee, but someone can be a
manager or someone that Melissa can also manage
or something like that. That is the assumption
that we're making here. I'll set with this employee. Let me start with those with
less income like gym Mike. Okay. The employee the manager for gym Mike is Mike is
from bank cooperation. The money jump. Nudging Jewish faith being
cooperation as well. I'm just making sure that I assign each employee
from each company, their own managers,
so they are not confused myself and
also confused you guys? Yes. Let me do the
contrast sequence of v then taping,
taping everything. Again in that game, we need tin and trees. Each. Delete this I'm sorry about that, guys. 1234567819 is let me make my wick easier by
copying this and ensured that I deleted after
I just wanted to put it here so that
I can see the names. We could say 1919
is an employee. My name is Denise from first
pin cooperation is if I if I then we come to Bank Corp cooperation, this person is 15 thousand. This is, let's just
say the managers ending the same money is
the business they manage. Before. Let me finish with
the fifth being capital guys. Also say Jim Mike is also under 1919 is the main posts
in his managing do mic. And if Andrew shines
the main force BC in this managing in
19, yes, indeed. This adaptation from
fifth bank memory J. Yes. Memory. Memory j, the manager for memory different
bank capital is if Mike Mike is. Jay Jewish. Jewish. On top of that memory, Jay's also under J Jewish. Interesting names
to mine the names. Then we move to small
being, let me say, let me see who lives in
small pink gum care. Ek is under tea milk. To Mike. Then to Mike is under Mike is under Mike is. And I just wanted to
create one roof with Jim. The spacing is also
an also anti gym. And then inserting into the managers table. Let me then everything in C, the different tables
that we have. Before we move to
the adult stage of running some
interesting queries. The database. Here I'm saying select or manages. Yes. It's just like I just want to see what we've done
so far in action. And then after that, we move to answer the
questions here from a to G. Just trying to
understand how to run some complex queries from this simple database that
we've created so far. So it's done running everything. So we can see so far we
have two weeks table, we have the company table, and then we have
the managers table. Thank you once again
and see you in the next video where we are going to run some
complex queries. Deep understanding of
querying from a database. Thank you.
8. Querying from the Database: Welcome back. The most interesting
parts of this course. As I said earlier, this is where we
are willing to run complex queries
for our database. Let's go to the web document where we can see the questions that you are trying to answer. The first question
is find the names, street address in cities of
residence for all employees who work for bank cooperation
and more than 10,002, unless we are here to find lots of information
from our database. And let me just copy
this and paste it in the SQL bench so that it will be easier for me to check what
I need to put in the query, like what I need to
create from the database, and what to put in the code. We want to say what
you want to do. We want to select employee
nims, employee names. We want to select treats. You want to select from way. This is fairly interesting. This is one of the interesting
parts about databases. I think you remember from the
theory I talked about LES, which is like it could
be a later time, it could be a weird, but mainly they are like later to point out to a
specific width. So instead of calling
it employee table, we can call it e. In this case, when we have two creative from different
tables like here, we need to query from
the employee table. I think we also need
to create a from the, from the wax table
so that you can get a combined
information of employee with working full been cooperation in this any
more than $10 thousand. So here we say select
from employee. So when we specify
aliases, you say e. I'm saying, okay, you understand what I'm
trying to do here. We're selecting from way we're selecting from the
employee table. Here, the employee I'm using E to represent the
employee table. So you can say
employee or you can say employee, then coma. And from weeks. From the West table,
that's where we can see the compounds
that the person is working for in how
much the person is in w. Then I think you guys remember that when
you running a query, you need to specify
what you want to get. In this case, we went to
get the employee name, the street in the city. Where do you want
to get it from? Then you say from
employee as E works is w. So we went to get
the information from the employee tip and
move the web stable way. It's like you're specifying
the condition with this. Is that in that is this. I think you guys understand
what I'm saying. In this case, we want
to make sure that the employee name is the same as the employee name in the employee table is the same as the employee name
in the weeks table. The company name is equal to being cooperation is greater than 10 thousand. This is very interesting. This is very, very
interesting for discarded. This is it. I think you guys
understand what M gene. Since we want to make sure that all the conditions are met, we use a yeah. So we are saying
that select from e, employee name, street, city employee is in weeks as w, weight from employee
as E and weeks as w, where this is the condition
employee employee name is close to dilute it. Employee name and
company name is close to where it goes to Ben, cooperation in salary is
greater than 10 thousand. Let me just confirm something. In the bank cooperation, we have a salary of
more than 10 thousand. I think we have some. Let's see if this
is green to week. Let me just run this part of the code to see if there
is no era and also to see if we'll get
information from it. Let me then all the
everything in the database. Note complicating ourselves. Yes. Yes. You can see that
guy's employees that work in the the twig from
the bank cooperation. The amounts which are above
ten thousand, ten thousand. So the conditions are met and it is selected people from
being cooperation. It admits sure that you see memory j's from Ben
cooperation J George, and if a mike and then
all of them ends. Money which is
about 10 thousand, let me just change
something here and make the manufacturers
and I wanted to see memory in j will
appear in our results. It's running. It's running. Yes. You can see that since we
have changed the money that he memo that memory ends, memory will not appear because memory will
not meet one of the conditions with the salary supposed to be above
10 thousand to less. We are done with the first part of the equation, which is a. We want to move to B. I'm just putting this one
so that we know this is a. Then let's move onto B. Let's move on to answer
the B part of the equation of our query of our queries. Yes. The second the b's
find the names of all employees in the database
will live in the same CTs as the companies for which
the wake we just wanted to find all the names of
employees in the database will live in the same CTs as the companies
for which the wake. What I'm doing here is
actually not really faint, but I'm just doing it so that I know what is expected from me. Like I'm just copying and
pasting the question here, but eventually when I'm
done, I will delete it. I know what is expected
for me than going forth back and forth
to the Word document. In this case. Selects. Select we wanted to select employee names. I hope you guys understand
what's happening here. This is what we went. We went employee names, but from way we want the employee names from
the employee table. We also want to include the the Whigs table and
the combined table table. The company table. Why? Because we want to sell it
the names of the employees. We live in the same cities and the companies, the companies. That's why we need the
company table with awake and we need the
wake, nowhere the like, where the work is like Petroff is part of the
requirement of our KT, so we need the weeks. The Whigs table also is
part of our creativity. I'm just going to
assign aliases is low. So this time around, I'm not going to write is, I just wanted to show
you that if you, if you don't say is you just put the alias after the name of
the table it to just wait. The same way is if you
specify the weight. Yes. From this way, what's
the condition? We want to make sure that
employee NAM is equal to. Employee nim is equal to. Okay, So we wanted
to make sure that it is employing them is close to the employee NAM. Here It's in the week. So you can see we have
the employee name. Employee employee name,
and we want to make sure that CT we can get the
cities from employee E, C T is equal to E dot, c2 is equal to E dot is equal to compounding taught CT. We wanted to make
sure that the CT in the employee table is
equal to the CT in the company table. We have one. W company is equal to is equal to c due to pump. Let me do it again. Patient, find the
names of all employees in the database will live
in the same cities as the company's employee goes to Cedar City in four
weeks, the wakeful. Let us run this and see if we are willing
to get something. I'm running everything. Everything again, writing
everything again. We just here for me to make
sure that everything is employee name in
food list is here. To resolve in ambiguous era, you need that's why
we need aliases. Let me see where
it's coming from. Yeah, we wanted to say the name of the employee. Okay. So here you can see that we have a table here of employees who work in the same CT with their company is
located in memory. Love and Tim, yes, the wick in the cell C2 with
their company is located. That is actually repetition. This appends. So what
do we do to make sure that there's no repetition? We do distinct about this. We use distinct to make
sure that it doesn't. Repeats. Setting things. Let's see how it goes. Okay. Yes. Let's move on to part C. We see that actually it's
almost like every week, our weeks in the same C2 the
company is located in is, so we move to C. Then
we move to sing. As to find the names of all employees in the database will live in the same cities, on the same streets, is to manage says, Okay, this time around
is managers and employees indeed sampled treats. Seat? Yes. I hope you guys are following through in understanding how I'm getting the codes
for the answers. I know it's kind of complicated, but if you understand
the theory, it would be easier for you
to just implement what we've learned into the
product, into practicing. Okay guys, let's move on to
the C part of our weight. Yes, let me just call it weight, since we are running codes into creating
from our database. In this case, we are gene C, whereby we want to
find the names of all employees in the
database who live in the same cities and on the same It's treats as
to their managers. Yes. Always. We have to say Select. We want to select
employee names. Unless it's always that we will not have the ambiguous era. We wanted to select from, from. This time around, we're
selecting from employee. In selecting manages. Manages. Interesting. Yes. What's the condition? Here? We need to make sure that employee name employee name is equal to m does
employee name. We need to make sure
that M manager, manager, nim is equal
to v dot employee. Treat like the street name, employee name is equal to. Finally, City schools to actually
feed mistake. This is very, very interesting. And CS, like we
have in some way, manages, Manages, managed to M. Thank you. At least I was able to figure out to the aorta within minutes. That's why bench is
easier to agree to. Give you clues to when
the data is reducing. Business with the deadline
is so the area is supposedly supposed to be here. Okay. So we said we do select employee
name from employee coma. Employee V. Coma manages. Employee. Employee name goes
to M dot employee name. M22 manage a nim is close
to the employee name. Int treat is close to V. Districts in cities goes to v. Let's see how it goes. Unknown M Treat wear clothes. Oh, apparently supposed
to be m2 v2 to treat. In 32, you're going to actually, that's this new streets
in the boat that yes. We have Jim Jim being repeated. Let me use distinct so that
you repeat suites like it's like it's just a mike. The answer is Jim. And if a mic, like they live in the
same cities and on the same stories,
their managers. Really interesting. Let me just check from the
state to see the meninges. Yes. Then we move on to D. With D here. Let's see what The pays for what we are supposed
to do with DDS. Very interesting. For D we as supposed to find the names of all employees
in the database. Do not wait for the face being. Cooperation. We are making an
assumption that all people with one company, yes. I'm just as usual, I'm copying this here so that so that I'm just flipping it so that it's easier for me to
follow what is required. From mean. For this d we had to select employee name from when? From Wix. Watch the condition where company Nim not equal
to, not equal to. Fifth is not equal to what is the condition
fifth been cooperation. For this one, we need to use LSS because we're just
creating from one table. Let me do control Z. So find the names of all
employees in the database would do no tweak full fest
being cooperation. So we just wanted to find
the names of people who do know tweak four-fifths
being cooperation. Do not wait for faced
being cooperation. Let me just do distinct limit and this memory
j with j Jewish. Let me see weeks for phase
two being cooperation. This is very interesting. You see that if
Joshua note here, you see that yeah, it's working. It's working. We just selected
people do to make full fist when cooperation. Then let's move to, Let's move to E. We are getting
there, beat by beat. Don't forget, the semicolon
is usually drink E. So we want to select
employee from. We want to select
improve employee name from where we're salary. They said We salaries went more than every employee
of small being cooperation. This is very interesting. Salary is greater than
o. Greater than o. We are, we are doing a loop. Like we are nesting, nesting two codes to get that, to just find what
we're looking for. Another certainly TM. Let me write this and
explain it to you just like we're repeating
select salary from weeks where company name, company name is equal to what? We are asked to find names
of employees in the database who more than every employee
of small big corporation. The quad, this one that I'm
going to highlight here is, is like selecting the salaries of all employees who work
for small bank cooperation. Then here we are
doing the comparison. We want to select two when
employees any more than this, we have no selecting an
employee with any more than employee of small
being cooperation. So let's run the coins. I hope you guys understand what HSD It's only love mike. Let me cross check
from the attendees. Lav mic. Lav mic is ending $50 thousand. Makes only actually the only
person who's any manual. It is above the employees
from swooping tap down. Then we move to the last. None of this is
second film lasts, tends to do to find the names of all employees who
attend more than the average salary of all
employees of the accompany. Assume that all employee worked
for at least one company. Here we want to
find the names of employees who does what, who more than the average salary of all employees
of their company. I think we're good
to go is usual. I copy my equation
here to make it easier for me to swallow. Yes. To know what is
expected from me. As usual. This is if we add to select employee name, employee name from Wix. Let me say this. From Wix was the
condition we are saying where sunlight is great. I'll explain it. Wastes that that
is greater than. Then we do select again. We want to select the
average salary is, I think from the
theory you guys, you saw that we can
find the average. The fact that we can do
lots of operations we put, like I was saying earlier, so I will explain everything. Let me write the code. We said where salary is greater than select average salary. Select average salary
from from w o. I didn't space
with LSE from wax. From wax W W dot
company is equals to W own company name. What are the conditions
do we need to specify? Let me go pick two, then. We add to find the names. For employees who in more than the average
salary of all employees of their company
assumed that people work for at least one company? Yes, I think that is what
we need for this code. So here we are aggregating to
select functions. Kind of. The first one is to
find the salary of an employee which
is greater than the average of all employees
in a specific company? Yes. Let's see how it goes. Let me run this. We have J Josh left Mike
in June. Interesting. We are moving onto the
g part of the work. Yes. Into a co-opt. This is usually come in
pasty team to make sure that we follow from the same page than going
from one page to another. And then we add to select, yes, select company name. We have select company name from from here, we use groupby. We want to group the answer. Let me see the query, the data by some
specific condition we are grouping by company, Nim. What should the company is in here being the sum of salary, the sum of highlighting, be less than or equal to1. Fall. Then we do select again. Then we do select and gain some salary from weeks. This is very interesting from week's group by company name. This is, it's kind of
really complicated codes. By now, you see the simple line is like
friendly name of the company. That is the smallest payroll. We out finding every company and compare which one has
the smallest payroll. Let's see which one is
the smallest payroll. From intuition, I
could save that. It's I would say maybe it's small bend bend per se. It's more bank is
seven thousand, twelve thousand,
which will give us 19 thousand plus 40 thousand. Okay. Let me okay. 40 thousand young. It will give us like I wanted to just make sure
that smooth being key, select photos and
I wanted to see if being could be like that
being let's see how it goes. Let me everything again. See how the last part of code to go about it internally, it's small Bain
Capital that he is the smallest amount paid rule
comparing to other banks. Okay, Thank you so much guys. This is like the
patch way we were creating from the DTaP is like doing some complex queries. I hope you've enjoyed this
patch of the course so far. To the next stage, I'm
just going to give you a project to do yes, into hopefully you'll be
able to do it and also run complex queries from it.
9. Conclusion + project: Hello guys, thank you for
completing the course. And I hope that
you guys, if land, mass stat, how to
design a database here, if master the principles, the use and application
of database systems, you've mustered out to extract
information from database. And you have understood in broad terms our database systems where the purposes for
which they are used. As I said in the
introduction of this course, I think I mentioned that the
screen to be a project for you guys so that you implement an action
what you have learned, and create your own database. In this case, we are going to
create a library database. So to allow for creativity, I didn't train you guys to specific attributes
that you should use, any name, specific
domain constraint. As such. I gave you examples like
for the attributes and give you three examples
of attributes. But you are free to add
as many as you wish, such as the author attribute, the book and the
broilers attribute. For the domain constraint. Please. Just think that in case if
it is a book attribute wide, kind of like it is in a
board domain constraint. Should you put the,
and I hope you guys, you just use like
Florida because you tend to use int for
book attributes. When you are done with
creating a detailed base and the Clayton with
tables mixture that you run in query
that you think is reasonable from the temples
that you might have created. For instance, if you have
like an author attribute, you can maybe run a query to search for the books,
a specific OTA. And the books are also available
in the library database, which I'm sure you guys can do. So, thank you so much. But if we have an equation, you have free to write it in the comment section and I'll follow through
and respond to you. Hope you guys follow me on this platform so that anytime that I upload
something new, you will be able to
receive a notification. If you do something
that interests you, you will be able to watch
it as well. Thank you. I hope to see you again.
Evern Joshua, Database Engineer
