SQL for Data Science in 100 min | Oracle ,MySQL | Beginners --> Advance--> Expert | Piyush Sharma | Skillshare

SQL for Data Science in 100 min | Oracle ,MySQL | Beginners --> Advance--> Expert

Piyush Sharma, Data Scientist | Project Manger

SQL for Data Science in 100 min | Oracle ,MySQL | Beginners --> Advance--> Expert

Piyush Sharma, Data Scientist | Project Manger

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
25 Lessons (1h 46m)
    • 1. Course Intrduction

      1:43
    • 2. Why Learn SQL ?

      2:06
    • 3. Database Concept

      1:15
    • 4. SQL Concept

      2:35
    • 5. Oracle vs MySQL

      1:46
    • 6. Understand Company Schema

      2:28
    • 7. Installation of MySQL Database

      7:30
    • 8. Play with MySQL Workbench

      3:25
    • 9. Installation of Oracle Database (optional)

      6:41
    • 10. Understanding Select and Where Clause

      1:15
    • 11. Create Company DB ( MySQL)

      4:18
    • 12. Create Company DB in Oracle ( optional)

      2:54
    • 13. Write your First SQL

      11:03
    • 14. IN,NOT IN,BETWEEN Clause

      8:15
    • 15. Null ,Not Null , Order by Clause

      8:15
    • 16. Advance SQL - Aggregators

      1:41
    • 17. Basic Aggregators

      5:44
    • 18. Group by Clause 1

      2:34
    • 19. Group by Clause 2

      2:12
    • 20. Having Clause

      5:35
    • 21. Introduction to Nested SQL

      6:34
    • 22. Nested sql 2

      3:57
    • 23. Introduction to Joins

      2:13
    • 24. Inner Join

      2:35
    • 25. Outer Join

      7:09
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

88

Students

--

Projects

About This Class

1 .Learn Database design , simple to advance SQLs and art of data analysis. Learn to create ORACLE and MySQL databases

2.Start writing simple to the most advanced SQL queries

3.Start using filter , having clause , joins with multiple tables ,aggregators etc.

4.Create DDL commands and modify the schema objects.

5.Create own database in your laptop - Oracle and MySQL

6.Start using SQL queries on R and Python

Meet Your Teacher

Teacher Profile Image

Piyush Sharma

Data Scientist | Project Manger

Teacher

Hello, I'm Piyush

"PG in Data Science - IIIT Bangalore , PMP ( PMI, USA ) , CAPM , B.Tech"

I have over 12 years of industry experience in Python , R and SQL .

Have worked across various international finance companies including Insurance & Banking. I have been instrumental in implementing the various software products .

I am passionate about data and the insights which it gives. I am looking forward to share my experiences with you .  I will guide you step by step so that you can make a leap in your career . The Data Engineers and Data Science experts have the highest demand in the world.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Course Intrduction: welcome. Put a Course sequel for data Science, where you will learn the highest in demand skill structure. Curie Language will look concert on databases for my secret and articles and start in secret like a pro on powerful, our in person console. If you see that trend sequel is right on the top, so get ready for the highest in the mind skill. My Name Is huge and Alba lead instructor. I'm a data scientist in Kenya on a project manager with over 13 years of industry experience and about across seven Michelle banks and Internet industries. Four discourses for for anyone seeking to London undertakes and who want to make a career in the field of data signs or their time needy and, of course, who are passionate about data. By the end of the course, you'll be able to write simple to the most. And one secret curies on any database is like Article post. Great and my situation integrate Powerful are in Brighton Council with databases and do the vaccination and analysis and come up with the fashionable insights you'll be able to throw such kind of plots. In this course, you will go to the next religion or databases will understand The Concert of Secret and RTB EMAs will integrate our infighting counsel with my sequel and article database. We will learn on how to visualize and do the data analysts is forbidden insights and on important concert. 2. Why Learn SQL ?: Hello offense. So I want to learn escape. It is because it is one of the most in demon. Still have today almost all the application over are using some kind of data with a massive amount of data being collected from Facebook Twitter, Gmail instead onto Nemec you that the mind for data analyst and did a scientist is growing each and every day without the knowledge of SQL, it is almost impossible to do any kind of data analytics And today data scientists and Data Engineer are spending almost 80 to 90% of their time and it leaving the data and massaging the data for the father and illnesses get a scientist vendetta in year are spending quite a lot of time in playing with the data. And for them, the knowledge of SQL is usually very, very important. The first important step to become a get a scientist. If you see the demand for escape, it is right on the top three The flashes Bytom second part and Charlie usually learning The SQL is the first step off becoming a better scientists. And this is another reason to London s school right of it. In this course, we're going to cover the following topics the concert database and the SQL installation or database handle. Opportune. Here they're going to cover article on my SQL Simple almost at once spl curies which will cover select and very close pattern matching in Not in between close quarterback, plus aggregators Goodbye and having close National SQL joined on some in three questions and finally but not least masculine fighting and are so see you there. 3. Database Concept: let us know on constant doctor base. So here I'm doing growing are typical database server. It is a collection of data stored on a hard X For a server, the hardest can be your laptop. It can be computer. I can be a chore server to provide a more consistent view. Off data are the VMS, which is known in relation Out of SM immune system was invented. The example of rtb amiss is particle my SQL I will leave it to the most common way of storing the data and managing is using the column ago structure and that is where the RTB amiss does. Let's take an example support if you go to a bank and you meet the branch manager and ask for the account information like your account statement for the last month. So what the bank manager going to do is he's going to import your I don't information like your phone number. Your name. You're any of the i D. So what the system is going to do is going toe such across the database server and fashioned a account statement for the last month 4. SQL Concept: let us know go to the school concept escalators, particularly language To retrieve the data with the sissified condition. When I used to want to get the information from the database, he can issue a curie. So between the data remember our example off customer going to the bank where he requested the bank manager to retrieve that data off his account for the last month. So when the bank manager issued that come on, the program in the system went through all the records and the selected a specified recorded this with great condition. The recorded three will be displayed and printed for the blind. Let's take another example Suppose you goto ah store And were you there some purchasing and you side of your card Buddha on the strapping machine? So what? This side machine does it read the card information? Then it will Such a crowd that are the best server offer a con violence and accordingly it feel Did it the account balance from your car? So what happened? Then the same machine will read the scarring for and retrieve the data after Con woman are now here. It is a typical process floor bagrham for touching the data from the database server. So what's up in here is my This is that that every server and this is a school engine. So what happened? It is then from here, the user is going to issue s girl common. And for the escape, Indian is going too fast in the data model Bester were. And it's a receipt to retrieve the out Port Buddha user claimed user machines. So now what other types of Eskil common? They're typically two kinds of skill common the one of the better definition language and second event data manipulation language. The Devon Devon official language he used to create and modify the scheme of the doctor. Yes, and commands like Kate and Trent Lott are part of this language. Data manipulation language is the command to trip. Modify Belinda Detto so as a deterrent, this are going to spend most of your time in the manipulation language 5. Oracle vs MySQL: Hello, often, let's get our hands dirty. And now we're going to stall the article out ofhis or my SQL databases. But first we have to choose which one to stall. So far, let us see. My article article is widely used in most of the finance industries to store the critical information. It has the ability to scale to support the intensive upload, and it can store 1,000,000 billions off later. So mostly it EU then professed little plane like insurance and banks and falling us on the Fortune 100 companies that use particle like one word CS holding wiggle Chevron, NASA and many leading banks. However, if you win just all the article, it was slow down the laptop because off the ram limitation, on the other hand, my Escalade open source and provide the high speed at every system for a smaller projects and for small daughter by and small data size. That is why my escalates perfect for installation on the local PC If diagram size did not go, most of the small companies rely on my s girl on disagreed for practicing as well Curie's. So for your information, the escorts in text in both article and my ass killing almost similar. And in our course, we're going to stall the article. However, you multitudes to stall the my SQL and the document for the selection of my SQL can be downloaded from the ah falling session. So let us not become a construction worker to install the database. 6. Understand Company Schema: let us know, understand that they will such a which you're going to use in the help coming session. So we're going to use six tables which are employees department Liverpool Location first on independent and project. If you see her employ is carted to department vax on independent and department, On the other hand is country department, location and project. Is Lindo works on this? All our intel this all tables are interlinked to each other by way off. Primary key and foreign key employees have the serial number of the imply Visit. Curricular department embarks on independent and department is gonna department location based on the department number and project. Also presented by the number project and works on a currently to each other by way off project number. Let us know. See the actual table structure and their data. Now here we have actual table name, their columns and their records. This all records and data we're going to upload into the our databases. If you see the employee is having 123456 around 18 place department having the I T department, the department table, deep purple location. There are five records and like way there. The courts in the works on a project independent. So here, if you see employees, is connected to a department by a department number C department number and the department number here, these two are linked to each other. Developing location and department are linked to each other by we have the proper number. This column in this column box on his corrected to employees based on that e SSN and and another employee table as the Senate. The column which everything toe he assistant in the works on Project this curricular department by the Weapon number column. And again, this product is connected to a box on, by the way, our project number depended. Independent table this connected to employ table again by he s in column. 7. Installation of MySQL Database: I did. Let us know go through the solution of my skills out of this and my school. My bank. For this, you can go to your favorite closer and you can type your my school. Don't know, click open the first option. My stroll down school, you know, And here we are concerned about my SQL commented on Lord, which is a freeware is a free license You on my article to the community. So here click on the my school communities over. So as Nick estimate you click my comedy server Here you have option select operating system . Here I select Microsoft Windows You can choose depending on your own or ah, operative system. But our course covers only matter seven knows. So it's going along. So here you will see several option. But don't go here your duplicate go down north page. So on the go dollars page, you will again see two options here and our operative system that we have centered over Samara Sorvino's so you can select either one of them. If we have a high speed in connect, you can choose to download the 24.4 MBI file work. If you're Internet. Internet is slow. Then you have another 4 20.6 and the file bigger. If you choose to download the first option it till down Lord, it will try to install directly Honda that from 19 it. But if you don't know for 20.6 m file, it fell down or formula local system. So click on the download and you start downloading. So, under Donald is complete. You can see in your Donald folder, which is my skill and started community in the next section he will goto the installation. So here is my Donald file. So double click it to open. It will take about one minute time. Go open. So here it is now, my skill installer. So here we are several option, like first, their dollar per default. So where you can see my school server skills Shell rocker over bank Excel visual studio connector. So if you want to start all this, you can select this one in the lower 44. But we'll be doing the selection of the full. So we want to install everything which comes with this package. Click on the next. So hit it for sure. Some morning messages. So this is the requirement which is needed. So I headed Chris was someone misses and just ignored. Okay, The important thing to note here is that it should display my school server. MySQL ever banks my skin or the fire a secret. If these products are not displaying, it means there are somebody to software which is missing, which is mostly visual basic C plus plus. So those after any to install and then you have to come here again. Now this installation is going to take about 15 to 20 minutes off time. I will be doing fast forwarding. So here my old insulation is completed. So click on the next. Now here is Lincoln's configured my school server, my school odor and samples. An example. One will win. So too the first option is gentle on my school server. Click on the next. So hear my conversation Typing Della Print Computer on board is 3306 So keep everything as default. Do not change anything Here, click on the next. So here you have to mention a password for modern abyss. No dead. He should remove the passport after typing in. So you can keep so simple. Password offer Type in the password. Just noting down somewhere so that you don't forget not This password belongs to the menus that which is a road user now in the villa option. In my user account, you can add the user off your choice. So I'm adding one user user one so host. So equity, that server. Then you can define the horse chair and all I'll give. It doesn t v and Dr this at Mr and get the password here your select on host. But I want to make that user as and out of his and Mr Doe. Look on the next year. Okay, now here it will add in your service name. So don't you start up. You can organize it by my school 80 If you want between the name, then you contain the name here. Otherwise, keep it as default. Select on the start, my escort, settler, and then click on execute. So it is. Think about 3 to 4 minutes time. Okay. The conversation has been completed successfully for my school's ever league on the finish . Not to start for the next product. Between my skill older so here keep everything is different. Bulletin anything here. Click on the finish line on the next. So here it will ask for the password that you have and you just know. So enter the same password and click on the chair. If rich Texas full, the status will colorful changes to green. Make sure it should display condition. Succeeded Legal. The gnashed and executed will get about who? Minutes time. Here, look on the next First select more adoption so that it starts as soon as you finish. So now it is on my school shell. So if it is displaying and if it displays this message it when my skill insulation is successful So no, here it will also open on my scale backbench So in the next session even go through my skull of our branch. 8. Play with MySQL Workbench: let us now go through the basics off Eskil our bench. So go to the start and then click my skull over bench. Open it. As soon as you open, you can see the local instance My skill 80 Remember be installed My escalating at the sorriest named there and my user Waas route which is the main user and the host off this database is your local system. That is why it shows local host on the port was 23 years is just double click it to open it . So they didn't ask for the password here. I don't know. Same password that you have return before. Click OK, so it will open the escalator here. So here is showing some scheme us here. But if you're opening for the first time, you will be able to see this management navigator. So you advocate. He's had a lot of options Like their tax word that I imported the store. Start of shutdown. Hey, signal here More concerned about the steamers. So, by default my school has given us three schemers which is Secura sis and we're schema. If it click it, you can see within East Timor. You can see different objects. So this is stable. Then within each table, you can further alone. So is in neutral attributes like columns in Nexus, Foreign key and triggers. Yes, And you start typing. My spirit will already folders Kiemas here. So we're having to re skim us. Firstly, Sevilla No! So I'm including his cell estoppel secure not actor this active along with a schema. Secura No other schema is word schema where we have city country England the language as a three tables they can also see other option like stored procedure and functions. Nor that in a concession I'll be right in the curies on the article and you may choose to write the curies on my Eskil all oracle. So it is up to you, Martin or dead. It felt if you decided to install the article in your system your rams, I should be higher. Heidi, coming around a GB ram on the rise, your laptop or your national slowdown. My skill, the lighter version. So if you're Ram is not a G B or if you're excited, not 64 big, every company who keep on using my SQL as there is not much difference between the two article is news for enterprise applications. But my SQL can be used for the small and out of this. So in our fanatic society will be just simply right in their skill curies for which we can do on both particle in my still. 9. Installation of Oracle Database (optional): let us know. Don't know some articles off here and we begin our solution process. So before there not understand more than the new system. A requirement. So Article has given a free software. Lula's expedition on the Minister story Parliament are as follows. It should be even know seven ran 500 well in me and expressed to be do TV. However, I recommend your and should be manual for GV now Article developer, Plane one the selection or not always successful. There should be a platform on Michigan Item SQL cuties. And that platform is known as the line Della per plane on this s Colella. Per is a free man and for writing a script duties. However, there are some license and l A park lined our level Luna stored leave you with the letter A school vermin and Jalalabad. So here is their offense. Ling from Michigan Downloader Article L A Park lane and let us now start installing that, uh, soft years here we will go to the steps of non loneliness off here. Must be No, no, the particle out ofhis taxi. So good they look for the not of this. 18 c and depending on the oils, which you have, you need to select the not fight. So and I'm using my perception No. 64 weeks. So once you click, it will start downloading automatically. And if you don't have log in I d and passport for the article you need to create in before you are able to download next, we will goto normal in the SQL Apart plane. So let me open the link. So this is a link for the escalator plane. And then, uh, my worst has been no. 64. May I have the license download $1 installation he had. I only copy the fight. And how on Jimmy inside the folder he happen. Look for the file. Second number likely set off file and then you have to follow them in section for installation. No, that isn't that society will take about 3 to 4 hours. So here I am, doing the fast forwarding pick on the next here except the license agreement. And here, if you want to came the holder off your finds. You thinking it here now? This is an important step. You have to do with the password. What? You're talking it here that you can know if you don't Some here and then know that. And also used for the user cyst system and TV at me. The agreement is down. So so be on the next and then Providence and this will stick around the Google T hours. Okay, Now you can finish it here. I already have a full detail now then let that rascal helper and then inside the cola Look for the I can has total over. No one picked. This one was Never pick this one. The system will start application. So basically, this article Espindola part is applying for the article which connect to the article and then on this s pin up are you can execute your own Esca impurities, then connect some schema off just like cable. And then you can also create a record up your day course. So this Onley Milu in our practice session. So official you hard lower commission on the top left sane they must sign with new connection with this one. No, here. Another name. You have to give some easy to understand union. Let's see, I give my skill cause, uh, do I already have the name Esseker cause one in people on a school, cause I only have so use that name. Remember wild in the article installation the article give you by different three years of Nim system CeCe and Bebe Edmund. So, sis user it basically answer stevia. So let me show you less current first kiss houses. Devia, Our role is 60 here and pastor here under the same password as you enter. Why didn't a solution? The house name is visually the i p address off the server rather not obvious. Wasn't story understand this article helping the stone in our local system? Our house name is local My default is 15 to 1. And Assaidi is actually the name off The blotter visited an XY. So let's do our escalation. I give my permission is successful. And then I, Siri one c you can here again, asking for the password and the same possible. All I see here, there, Sam Haskell Course to So we have only that. So now here we can write some simple a skill that unites. Okay, now this data swing between yesterday before 1520 And here's what is actually a in boot. Hourigan, dummy. So let's says dead is actually in the system. Visually, with the white table, you can execute any competition as you on any simple competition. Like one plus two from shows. Three. Okay, so no, it is beyond. Then our there's still a chimp on. It s cool upon its successful. 10. Understanding Select and Where Clause: let us now go toe some basic construct off the SQL purees, and we'll try to understand that scale structure. So my first s territory is select half name and name from employees. So here we're trying to select two columns from the implied table. So here there are two things to understand. First, on this last Dettman, you have that I don't know columns you only select. And from you do find right that source table Second career writing is a star from employees with sex is will do him and Sandri is getting than 30,000. So what does this goody mean start here? We're going to select all the problems. The table is the employees, and they're going to select all the male employees where salary is more than 30,000. So we're having to pull her condition. So before we proceed further, let us now create some tables which is known of the deal Commons. And for those table again, we're going to insert that the cars, as you know, that incident because the Deimel common So what we have done that we can actually start writing the SP queries on the skull apart 11. Create Company DB ( MySQL): in this session, we're going to create a mini database for company. So click open my skull over bench and the password here for the user route. And then we're going to K no schema called company. The scheme other basically Ah, partition in the database to store the information off the similar nature like we only have existence came off Secura and word out ofhis like UniCredit and of this chemical company is apply here. We're gonna play again. Finish it. So our schema company is created successfully, So you can also see on the left side of the navigation which is a company? No, here before is getting in the script you have to make companies aren't deformed schema. So now you need to know, Lord, that my SQL create insert common skin which I have attached. And then from here you copy all these scripts which includes the deal Common and Deimel Common meaning they're going to create a new table for company. We're going to inside the records so that we can practice our SQL. I'll copy each and every a link. A copy on last krypton. So at the end, you can see the ultra table for employees were We're where we are, adding the foreign key. So made the company in the default schema here and then paste it here pays the scripts here . Okay, now look and relate to select all the script and then click toe as acute. So if you stand as the getting the scripts, Wonder script had been completed successfully. The buffering on the top 31 will stop. So it held informal successfully and the last an important step is to do the comet. Remember to look, um, it Let's go, do that. Come in. You can click this button months. I click this button. It will enable other Do Burton, which is coming, the tradition and the cross burnings to roll back. Now, when you re open that skull of our bench, you can see all the Mr Table, Another company. Let's check by writing one simple Curie Select star form company not employed. No, that company here, the schema and employees that table So from the employees are able to fashioned up records . Let me throw this and you open the skill of our binge. So as soon as you re open, you can see another company schema in that table which have been created. So no, we'll get down to the business and will start writing the SQL queries. 12. Create Company DB in Oracle ( optional): let us know create their tables are records. In the article database. You can download a script which I have given in their attachment. So the script for the article condition off the create table in certain records and anchor table, you can choose to execute all at once. Or you can execute one where one first you can create their tables. Then you can insert the record and finally you can alter the table. So let's see how to do it. So here I already have ah separate kiddy it common and insert common. So I will first select the create common and then as they could go stable. And then I will do our insert common and finally a beluga. Help her. Come on. So first copy pairs discreet, common, so you can selectively copy from your script the create common select all and copy on the article escalate, Loper, the select only can do control here and then you can execute by clicking on the play button . So here we have our own six tables in play and this is a very common security. They will come on, then we have department. The apartment location, project works on and table dependent. What I do, I do select control. It's like all and click the play button, which is an execute market. If you see here on the bottom part of the window, everything is successfully. So basically already deal. Come on, is successful. So now, like a Skopje the content from the insert common file So basically had what we're doing here. We're tryingto inserts, um, records. So, like if you see, employees were inserting some records here, so let us copy their own back under, and paste is here. So let us look control here and as a good. And don't forget to come in. This is the comrade session. It's a comic button. Okay, let's do some testing matter. If the table secured successfully are the records work record. So I will just issue Russell extra from imply Click control. Enter. So here on the bottom part, you can see that years meaning our creation off people and the record insertion is successful. Now we will go through some escalate purees, some of your condition and building and starting 13. Write your First SQL: So friends, Are you ready to write? Simple to the most advanced Eskil? Curious. So let us begin now. So what I want you to do is to open the profile First, the select Common file and second either company Levy file in muscle it common file have a series of questions having simple Luda most at once a scale Curie's We'll start writing the simple escalate beauties and then we'll progress store. But I did not complex sq. So you have to also open the completely be filing parallel so that it is easier to understand the table structure and it is easy to write. That s cules. So this is my completely be five. I'll open the SQL apart now nor dead in our intercession. We will rewriting the our curies Askar curies on the article Esseker developer, If we haven't started my SQL, you can also write on a my SQL our bench. Also, the sculptures will be similar. But if you are working for over, if your professional, you'll most likely to work on the that was not a base like Oracle or IBM. Give me too because these are used in the enterprise applications and they have NEC capability of storing millions or billions off record. It's connecting. No. Okay, so it's kind of okay in muscle it. Come on. One file. I have some cities, are basic questions and will try toe result. When my one My first question is the tree All rose and all columns off imply table in a company hand. Now this is an implied table and these are the rose the vato door. ITRI is the Selectmen select stuff from employees Semi colon. No, his star is a matin to select all those to select all the columns and all those. So I just select the statement and as you did, you see it has faced eight rows. Now here I would like to tell you two things. First, your command should always and with a semi colon second thing for ease of understanding of the SQL, you should always do a for meeting. I mean, the select line will be on the first line from I mean, the source of our table name should be on the second line and the other statement which you are going toe right in a few seconds should be on that car line. We will honest, follow this formatting. So it is easier for others others to understand. Okay. To sell a star from employees so we could have also return. The curie must actually mentioned making that column name support. My question is to select the first name and last name off employed. Okay, so here we will actually write that name of the column. Fascinating F new and last name is Hell name selected as he could. So it shows my first name and last name. Let's go. The next question. The trip details off. All male employees who draw us led off, which is at this three. About 20,000. Let us open. The company had no file. So my first question. So in this question, then basically what things which is being asked first? We want to start all the men employees. Okay. The column for meal is sex male and female. M stand for mill half stand for female in in other databases. You may have like this. Your mail, Mr. And for one female may stand for who? Okay, my column name is sex here. So last night? No, you start from that mitt. Okay, No, their head. There is actually one of the matin for the selection criteria. So here's my selection criteria. Waas mill. I was like, This one has a good my acuity. Okay, so we have all the men in place here. Burn. If you see I have a sec. Our second question also, which is whose salary is at least 20,000. So what we do here saying there are two conditions here, So we do. We put it here and and column new salary Better or equal Do Party 1000 semi colons said let and as he good in he before there's a getting So what bottle Rose, are we expecting here? If you see John should be selected. Franklin Celis, 49. He should be also part of the criteria Romish and Jim's. So the only person left town is Hammond, So as a good our common. All right, so the the realtor at Rs expected Let's move on to the next common retrieved the details of all dependent off He he SSN 334455 There's open The company had no the table name here dependent. So my dependent table name is here Here this is my dependent table name. And it is asking Do it to you. The defendant with E SSN three tt. It's basically issue. Select 1st 233 rows. Okay, so let us I just hit Select star. Um, depend if you see her while typing system will also show you display that devil him dependent? Yeah, he s Ihsan a little. A little. Okay. Hit. There is talk things to note here first in the depend dependant is actually in a small literally for system for the doctor visit. Doesn't matter. Like the cases doesn't matter. It can be a per case. It can be a lower case. Second thing E SSN is under course. Why didn't the court? Because it is a character and it's not a number. So if your character, it has to be under course in closing a shipping clerk, another course as the Goodall's treatment they should have done, it really does. So an expectant Tito's likewise, but it really the deals are protected. A base out of house done and stuff or it didn't do deals up with them hostin and stuff for Okay, let us see the project table. So this is a product table. And this is my location, house, done and stuff or are the delegation which is being passed? So 1234 said stuff from Hello, Jack, the the location. Bookish isn't Stefan or Holston stuffer? So let us has the good the first condition, staff or location, is it? Your issues was, too. The second is host. Um, so far, Phoenix Blue here is that there are two ways to execute the boat. The conditions Stephan House done. What we can do here is vehicle lighter or condition television Austin. So it's like the photos. So basically, money, the meaning of our here is just statement will be a secret in if either off the one condition is satisfied. So and the good practices you have, the on this enclosed this or condition another record, not the same. SQL can be as good in my using that in common. So make sure you have to write the increments that start from yeah, being location in so area piece of leather value for which it is being passed. Step four uh, posted flows, a picket, and by semi colon Was he good? So if you see here this SQL and this Eskil gives you that same two years 14. IN,NOT IN,BETWEEN Clause: we will not go through some basic ah function given what SQL first is in and not in between order by and large, been go to like So my security here is Celesta from for the bail the location Not in stuff one in Boston. So one disputed When you do is going to be taken all the project whose locating actually not in step for in Boston. So another not in close mother bracket. We have to provide the values that security is still a staffer project the television instead full in Boston. It is going to read them all the project whose location rising step for in Boston let us go toe order back plus celesta from employees ordered by Celery. So this is going to return all the employees in ascending order the Salvi sell a star from them play order of us that live descending now that defendant into beauty Our reversal in order of ascending descending is only the world is ending so descending mean as it says is going to return all the employees in order off there. How you how you do, Lewis. And now this light function together my while our character and it is widely used in the natural language. Processing of the data signs, so select a sin from employees were at least like hosting plus in this presentation. So if you see that if you see the address column the address in include all the Littles and including the city. So we're going to fetch on the employees whose addresses in host Um so we can we made a user person. This person will goto this exculpatory in more detail in the escalator. Suppose we want to find all the details of the project not in staff were in Boston In our previous security, we have abstracted those details from the project which which are in step for hostin. So basically what does in due here in will look for those value which are another in closing Breckin So in this case, the column P location is looking for stuff Foreign hostin Yes, it was. If you want to find out those projects which are not in step elevation, so we have to simply replace in with not in. So I'm a blessing in we're not in. So this is when did it on all those records which are not in stuff or in Causton. So I headed out there. Doesn't move onto the next question if you treat the details so protect every based out of hostile belongs to department Department number four. So I will leave this curie to you so you can try to right the Curie. If you have any doubt, you can post on the Forum next question. Display the name of the department and the year in with the manager waas appointing. So here in the company B, this is my department table, and here we were concerned about the name is the name of the department and the manager started. The question is asking that year from the Manya started. But here we're storing Children. So we have to Esther care year. So let's see how to do it. Okay, let us know. Right? Acuity. So let's start from department, which is our table there. So here we are selected who column the name which the department and manager started. That's what has secured the security. Okay, in my art court, we're having them and they're started with a question asking to extract that year. So let's see how that strict now this article and any database and they have their own Enbrel function Do as track this month and year from the date. So the way to do is no cap coma then here you have to put the former So here I'm using are meaning We're abstracting the here much says what say But suppose if you won't go fetch in a month, then you can use it. Here I am am eating double them And if you want to fetch the day you can court BD which is a day. So here's my course. So there's some Enbrel function return of level in my skill and article on like sub string and well in string. And like so, this you can ask your father in ah, in the resources of the level on his website. Okay, next question is who live in house? Done So how you can do it so we'll never use off the like function. So this is my employee table. So we had to describe in detail off SSN, whose addresses in host on If you see my address column, it shows the complete address. But we're looking for those address which are having hosted but longer decide that Curie here, Sadiq start from employees. Their address. Let's see the question again. Where Address is looking for Austin. So what we'll do in my column Name and address, like taken to court host, um, and then percentage. So this person is like ovary card character. If let's suppose if you're Austin is at the last off the address, then you need to put on Lee personal host. Um, and if it is in the middle of the person days at the beginning and end off the string. And I suppose if hostin was the first in the address, So you have to put on Lee Austin Percentage. I hope it is clear to you on how to use this white card while fire function, which is a percentage here. You can call it that the present. I also since questioning asking to display a system so we just select the column s in. Next question. I think you can try yourself this brother employees was named against in J before writing. Father, you have to first all that question and then you can move the and then you can look for the video so they're the solution. So let's start from employees half namely J Person days. Send a question asking The name should begin with J. So Jay's is at the beginning, So we have to put a percentage and the last If you're not put personnel before J if you put personal before J that is wounded and all those name Richard having J. But now the question is where the name started J. So that is value quote, Person day and the last. 15. Null ,Not Null , Order by Clause: question number nine and 10. I will leave it to you. You can practice it. Question number 11 This further access ent and fully conquered in inter name off all employees so many a time we need toe display the full name innocent in one column. So in employee table imply. And in play table, we have two column, half name and last name. So we need to display together. The way to do is like the column name here in the select different name in the Senate Must Fulham McCollum ele name. Okay, so, as a particularly so it is being it being this financial separate column. So me on to Concord in it. So Mr Double Line as four Lane John Smith support if you wonder space between the two name on the way to do is again have space women congregated on for our line again. So having that face don't Smith. But if you want to do Spacey with some line, some Boesch next question display the employee detail who does not have a supervisor. So let's open the company enough. This pleather employees tell who did not have a supporter either. So here in this example you can see on the last tour doesn't have the supervisor which is not okay, so imply have selecting on the rows and columns. So for me, it's easier to find which column it rougher. So who doesn't have that support? Essence is the portion. So my name is and then after fine the one with Donald Value. So we have to make the use off. We cannot use here. Acquittal. No, you have to make the use off is is not so. Yes, and there's only one push this only one card. So here, for information, the null place are very, very important role in any database. So for many a time, a lot of function with lord work if you don't make if you don't take care of the null values so we will know when we go in the next section. Now let's see my question You to display all the employees who support assistant is having some value. So what you will do having some element Definitely not. Have no. So we can make the user is not now. Okay, so here in this, employing all the column, All the roads are having so fast isn't and you know, very okay. My next question is displayed The assistant off employees shorter by their salary in ascending more. So basically, let me open the note. So is asking toe toe display the employees or Robaina salary? So order by means we have to display the imply first having a lower salary followed by that employee having the second lowest salary. But it's, um the night that skill. So again I was started by displaying all the column and those I've helped him place. No, it is asked him to order by celery so honored by celery. So here is my outboard. Okay, lets his support. My question is display all the employees in the descending or by beautiful or about Sally. All the display the data in ascending order. But if you want to have in descending order Oh, see, star from employees. We're order by call of name Sally. So this is Elliot would just add up descending. So it previous play no employees with the highest salary first, followed by the second highest salary. Next question in sort of box on table, based on the number in our so here. If you see it is asking the shot based on Pena men Our So now they're cool columns, Free number and hours compares on visit. You have to do the talking. My accent table is so there, Pena men are ours. So here, too, chartered gaze on PM In our somebody made a user who column in our order by clause to start playing on that was in college. So order by simply and the column a lot of a peanut man our So how does it work? Is first it feel Order my P number? So now if you see I have to a new record with freedom of one Okay. And then it will order by hours within the Pena More value Like what if you see, are not be number two. There are three cars and it ordered by powers. I hope you understand. Let's say suppose I do order by P number back how hours should be in descending order. How are you going to write security? I'll copy pissed and then on the hours are descending. Really? You see the Pena move on? It is having to records the weather I was with the maximum value is on the first line. Likewise. If you want to do the order of a P number in descending order somebody loyal bullets ending here and then hours is in ascending order by the fall Little Venus ending. You see, my being a movie authority are having tea records, and my hours are order by. Maybe next question you can try yourself were you have to do order by yes and be numbering off us. 16. Advance SQL - Aggregators: It's not timeto move on to the and once a skill curies, and the first stopping is aggregator grew back close and having plus, let's take an example off world famous top batsmen were not coolly. And let's find out how much one year school in market good match. So here, table immediately table And the very condition is ladies we are. So this kid is going to return the total number of unschooled by not holy the select some good work. Kordell Off eleven's not if somebody asks you how much in here score by each country. So what we're going to do, we're going to use a group back plus, So here, select country, some run from cricket table were clear, is on group, but 20 so grew by country, going to return all the countries and there friends, I suppose if you want to select the country's Virat Kohli has scored more than 100 so the way to do is having plus having some more than 1000. This studio Viguerie done all the country's Saviour here. We're not calling her score more than one partner. So here, if you see there are two filter condition. One is aware. Plus and second is the having clause. So having close here to use along with the group and we're close, you only know with a more mournful recognition. So let us know Go, go right in the actual skull pianist. 17. Basic Aggregators: Okay, so let us know for from some aggregation SQL So my the next question is displayed, the average protect ours. So let me open the company handle. And that is goto the product. Our table. So my project, our if you remember it under the table works on okay. And my column for the product our is column our the questioning asking to calculate the average project Our Okay, So let us see how to run their skill. Select. I started from looks on as Israel. We're going to select all the columns so that it is easy to find out the actual problem names in my column in hours. So let us select worse. On my in the SQL malfunction for the area is a B g. Close the bracket. The column should be a lot of cool. Breaking is a good dose treatment. Okay, See the value 18.3333 But if you see that a similar is too high, so we can use another function called as their own function. You know, record nos If you want to around two decimal places, you have to put a comma Go here. Is it you Yes, The second point TV again. The output in around So it doesn't look nice so we can put on us here is rich for all of us . Should be in level course Well, like so it didn't 0.3 d. My last question is display the number of employees who do not have a manager. So let's see that probably hand out This is my imply table they passed into display. The number of employees who doesn't have a manager basically will identify. They imply who are managers Base enough super SSN It must super SSN doesn't have any value like the last row against gems, which is no. So he didn't have a manager. So how are you going to display? But the question asking the number of employees Obviously that's in the cone. So you opened the for table name is employing. Let's start from price it No, we have to find those employees where super SSN is No, I think we have written similar kind of 30 before. Also, my number record is one. But how do you display the actual call? The function in the skinnies count flows of record the Comstock for the oil Yes, Number off be me. Number of empire. So and instructed that really is one. So I will keep the question number 17. Let's go through that question Number 18 for the highest selling of the female employees. So my table is still a star from fly. Is it good news? So we are seeing a higher salary off the female employees so the female and male is basically distinguished by column sex am stand for male afternoon for female. So my condition where condition will be the sex is going toe it's and then ask You know how your selfie So the highest Stanley is it deposited by the function Max And for the men, usually it is by a 1,000,000 men. Yes, mix female salary. You can put any Ah, yes, as you like. Correcting the spelling makes me militarily. So 42,000. So what will easily or the mail implying? I think you can try this question. Display the number off employees in his department. Okay. Before going through this question, I will explain you something about the having class 18. Group by Clause 1: but many times as it at least you would have done it, reported a little specific departments. In such scenarios. You work really information on departments, protect assembly lines. OH, vendors a sexual. The school provides a very special plus for the groubert plans for collecting facts about the certain categories. In my question, it is our sin that describe the number of employees in his department. Basically, uh, in the employ table. We have this column department and it is asking to display the number of employees by each department I have I'm having at all 45 departments, like department number five there toe sorry for the course. Want to three and four. Department number four are having to the courts and Department of Marvan is having only one record. So how do you go about at in the SQL? So let me write the skill first, and then I will spin you each steps that have start from implying as good in this first look in my point of inter cities department number, and it is asking to grow by each department. So I was one depart for number and then we have to use the aggregate function any aggregate function in our case, it is a count on this dot and the column on which it passed in tow aggregate will come. Another drawback. Plus, so it is a good or bad department number. So let it is it could this question this still only so we'll put the and yes, kid. So my department number one is having one common one employee. My department number five is having four employees on my department. Number four is having three count off employees. 19. Group by Clause 2: display the number off mill employs in each department. Okay, Another question is asking Display the number off male employees in his department. So basically, in our employ table, we have male employees and we have female employees. So we have to first filter on either men in place it a little. So after Phil thing, we have to display Ah, the number of made employees. So you look, come start and in each department, so to support the department. And then you go number Hey, so this is our court. This question is disfigure Average minimum maximum our spending each project. So basically on the escalator were to write us three aggregate function, which is an average minimum and maximum. I think you can. You can write this SQL and need the practice Also display the year rise count off employees based on the year apart. This is also almost have not a skill displayed in the motor project each and blight is working on. Let's give this also the Spirit Department number of those department that at least three employees it. So we're kind of like this Eskil before that, let me explain your basic all for having plus 20. Having Clause: when this question is displayed. A departure number of those department that at least three employees Okay, here, if you look at this question very closely, really basically asking two questions, the first question is we don't display all those department that has 1,000,000 Minimum off T implies so basically there to do on aggregate function on the department column and then we have to display No, we departed from number. So with this, I would like to introduce on another class called leather. Having close, I suppose your manager asked you toe count all the employees whose salary is more than every certainly in the particular department? No, into totally into totally you know that to aggregate function would be used here. One is account and second in the every. You decide to apply the air condition on the every celery off the department. But to your surprise, the curry feels and family should play writing this SQL before moving on him, I would repeat the question again. Suppose that your manager asked you to count all the employees whose salary is more than ever, Certainly in the particular department. Now, how do you need the answer? The answer for this will be by using the having plus, basically, the having clause is typically used when you have to apply filter condition on an aggregated function. This is because the were close applied before the aggregation take place and that it is not useful when you and I want to apply filter on that regular value. In other words, the having close is equivalent to a very close after the group by husband has included, I'll go back to the question again, disputed the partner number off those department didn't at least three employees. So let us know first group my department. And that is the count of that employee when I have this. Ah, scare. Only I can make the use off Samos school Copy Paste Yes, To move the we're conditioned to sweep, Anita says, selected between the mark and called. Okay, so I had this department number and then I have the count also. So no, it is asking The department should have at least three employees. So not here I've used having close, Having has remembered is almost similar to work were close. So I was just having on the count we had at least three employees. So we're late. So this is my out. Let's try some other questions among the people who draw at least 370. What? The department vice. Uh, what is the department vice average? Okay, no again. Hit up. Ah, two questions. First, we have to select the employees. Who's minimum salary is 30,000 and then we have to do average. So let's try to write it. Celeste are from employees stuff there. Celery is guitar equal to 30,000. Is your disk okay? Not zoom of a little later. Now it is asking toe. You seem to do the average celery. My department. So our aggregate function is on the celery and a function is every g and group. I will be department. So the number Hey v g sensory and Google play for number. Okay, so this you can also use the function, are back plus descending or ascending. Suppose you want to display now ever recently in the order off ascending. Make honor by so you can do to order by two. Yeah, so basically order by Bullhead means it. A tweed refers to the column number. My first column is one is department number. My second column is every celery, so it will be too, like they asked Ito low order of a departure number because it became it to one, because the first quality developer number is at your disposal 100 melon. 21. Introduction to Nested SQL: let us know focus on the next topic which invested SQL Well, you know, Internet Activist. There are multiple tables as we have seen in the company handout. Uh, like this one there are employed table department. They will development location and so on. I suppose it commanded it. Ask you no fashion the data to further the pills from the employees and from the department table with a particular before the department number five. Let's say that the sample it department number five How you going to do this? The way to do this. His vier using the national SQL now the national SQL Out off guys, plus the joint in Second International Securities are securities the joint we will look, we'll take a look into the next session. For now, we'll focus on nine national securities and securities, So basically, the nest of securities are typically used. Then you have to select a column from one table based on the filter condition from the another table. In such cases, you have to put our security inside the were class. Consider off a certain value. No, here the security is basically similar to the normal SQL curing. We'll go toe some example. Let's take Let's take a question and ah, we'll focus on this question. Let us know. Focus on the question of modern year this player half name off employees marking in the research department. Okay, Now let me open the handout. Now it is asking the F name in the first name off the employee who is working in the department. It is such okay now, if you see the imply table and department table, what is the language in the to table? The only linger in the pool table is the number here Department number denoted by Dean Omer . And here it is actually number. So he was a human history department. So my s killer with something like this select half name from employees. We're Denham er, equal toe Select. Not This is the subcommittee. I said I d number one department were Denham included. Such harder by that name. Aziz, Your disk ut here. Okay, What I will do, I'll beg no this curie into two curies less. Here, let me first select a name from employees. So here is my f name from employees. No, my second security must like in spl waas select Denham Earth. Um, it is such a department. Okay. And then the murders five. So my basically want to select the employee name. We belong to department number five. So the way to do is to do the sample selection and then put the value here. So these are my employees? No. If you don't do this separately, it then you had to do on Esther Security. So let's try the same SQL in another beer. Maybe it Needham Equality cellar You never from department They d name that is such in fact , it close. So this is my call s the National Eskil or the inner Inner Curie or cancer Security. No day. It has to be always in closed in the closing bracket. No, select on an insecure spending a little, you know, market. No, this and this too. This SQL on this s cale gives the same. Really? So this is all a vote? No national securities Then look at this question. Display the F name and certainly off employees whose salary is more than the every certainly off employees it. So how you do this? Sell it. Hit name Celery from employees. Yeah, selling his more than that. Every certainly off. All employees don't be, um in place. If you look at this escalate very closely this inner SQL or a security is going to give us that Every certainly off all the employees. If I as a goddess s close separate me, look at the one in the value here like a their value is 35125 Now in the men SQL selling f name celery from employees were Selby. So basically all those employees who salary is more than 35125 will be displaying if I execute. This has school as a whole, is he so all the salary is more than that The every Selvey No, here we can do order where you can order back loss out of our century ascending or descending in any way. This is my artwork 22. Nested sql 2: working on their doesn't water than this question. Which products have the least number of employees? Look, it This question looks very, very small. But this will cover all the bases scenario that we have learned in Italy. So which products have the least number of employees? Let us know. First, open the company here. Note. So we'll focus on the protect the cable box on people. It is having I implying number. And then we have project number. So it is asking which product number are having the least number of employees. Okay, so let s see, How would I know that skill? First, you will select all the column from the table. Watson from left, son has any good. Now let us do grew by predict number and do the count off the employees. So P number and gold star home love son. Goodbye. So this puree will give us the count off employees by each project. Number it. Now, If you look at this security, the predict number one and predict number T are having to employees. My other projects are having three employees. So basically, we need to find out the project one entry only, so My next step is to find that least number off employees how to do it. So let me in. So what we'll do, we'll put us onions here cnd Then we'll select instead of cnd um, close of record it. No, See my school here. What we're doing here? We're selecting the minimum menu off count from this curing, so it will give us do. Okay. No. So against. So we had to select all the projects. Where the imply Countless, too. Let us get night and a little pretty looks on. Yeah, number the number Start. So that's in our objectivity is door select a project where the count studies equal Go. Who having constor Well, so Okay, so that really as expected. But I don't want to write. I don't want to Hard quarter Valley to So what we'll do in place of who will replace the Curie below. So just put that same clean the closing record and then disease. So he didn't know 23. Introduction to Joins: Hello, Friends. Suppose if your manager asks you to further details off the employees like first name, last name and the name of the department in which he works. So how are you going to do this? So basically, if you see in the employ table, we have a first name. We have a last name, but there is no name of the department but the department number. But if you see in the department table the name of the department and anyone for number. So basically, you have to join both their tables with the help of her department number, which is a common key here. Too fast. The name of the department. So this is where the concept of joints So we are having four control joints first of the inner join Holy join lamp. Join in that I joined from Bagram. You could understand that it didn't, you know, join. So it is going to fetch on the door detail from Wonder Table, which are here nap devil in the right table which are common, do each of the But if they left joint, so it is going to find the deal's off all the recourse from the left table and only those records in the right table, which satisfied er enjoying condition. Likewise right Joint is within defended. It deals from all the records from the right table and then full join no matter. Condition is a decide or not. It is going to fed all the details details from both the tables. So let us discuss whether concert of the inner join So in a joint, if you did a sample SQL Select Star from the table one in our example off employing department table one is our employees table. You know, join able to so able to hear the department table on table one dot column X equal to table two dot column I So basically, this is the name of the column, which is common in both the table. So in our case, is that department number? 24. Inner Join: Okay, Now let us know. Right. Some SQL for the join conditions. Okay, My question is display. That s ascend. Last name on the name of the department, off other employees. Okay, let us open the come here Note. So this is my last name. And, uh, this is my SSN, And this is my neighbor, the department. So the only common column, everything the suitable is the divine phone number. So like to see how to write the That's cool. Sell it stuff. Boom, employ, You know, join department on what is my column name? Dino. Mud and Nina. More on the number. You know, as a good this one. Okay, question asking to first the name s ascent and the name of that department. So notice when the column name here live. This is thin And the name persecute. Okay, so this is my it years. Okay? Now, the same Eskil can be written in another way, which is Ah, really but less complex than what we do here. What I will do here acuity against stop home imply comma. You can end there. So here to Ponta actual column. Liam, The joint condition, which is called the joint munitions. I need them, but selling this one. Sorry. Appointment. Copy the column. Name? So what? Security give the same legal so you can use either one of the duty. Ah, in your SQL. 25. Outer Join: Okay, Let us know. See that next question? Display the incessant last name and name of the Predict off all the employees, so no to see the complete window. So it's asking the last name SSN and the name of the project off all the employees. Basically, if you see this predictable is lim Tow the table, Watson bar way off the project number. And then this vaccine table is linked to the employee table by the way off the Edison and assistant. So basically, we have to join the three tables, which is employees who box on and project. So let s see, How do I Daskal the select start from implying, you know, join box on on has sent it could boom years is in, you know, join project on being a bird. Good boo. You know, but is killed the question asking display last name medicine on the name of the present. You spin him here. So this is an inspector. The same curie can be returned in a different form it as he Britain here. So, Senator, from put all the tables here and separate eat by comma blocks bun and reject. Yeah. Now you have to contact. Join. Condition Looks, uh, Hess isn't equal to yes is in. And Pena, buddy, photo, you know, But was he killed and then select only those column attorney then and live. This is then and union. Okay, One important concept that you need to understand that if there might be a case where the column name where that chilled me on the column is same in different table like employees and works on may have the same column name. Then how do they finish? Differentiate between those columns. The very definition is the Ali s name. So we should have a practice off visiting Al Gasso Reunited Curia again. Mother, help off. Yes, him. So I'll copy Paste security when I would do here in play Hourly Northern E Look, son Le Bleu and project this p So for all the columns, you have to be on this. The column by failures, Neighbor table. So in case of assistant belongs to call imply table. So it will be e dark season. E Edison column will also works on her cable. So it will be the Lord. Yes, isn't being a month belongs to Project Devil? Certainly be dot be number and this column belongs to W. So is he good? This kidney. So with all of the good practice to use the alias name, he was in the select statement. Also, we can use our yes name. So Alim is e Nord medicine belonged to employ and P name belongs to project he dot IHS Last name SS him is putting him okay None of the movement of the next question display the incessant last name independent name off all the employees So here it's asking the name of the dependent. Let's see, the company had No So here we have a name here we have employed table and then here we have a dependent name If you see the relation between employees and dependent is yes sm and is the name of the dependent. But not one more thing here is there not all the employees have dependent. So how do you do this So we can do by through this? So let me and I did ask you select start from in play Oma dependent. There cenique little Yes, it's him Here This well last name this is and independent name. If you see her, it is fetching on Lee three employees detail. But if you want to fetch all the details of the employees, then what do you do so far? Then you have to write, uh, left outer join so select on a limb dependent name. Um, employees left joined, dependent on its ascent equal. Do you send Zika just will. Oh, like knife. You see here it featured the details of all the employees on the name and very ever the legend the dependent name is not found it this way. The null value the same goes to the right out to join.