SQL Tutorial for Beginners in 2020- Learn SQL quickly using 30 Scenarios on Mac and Windows | Valuetech Academy | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SQL Tutorial for Beginners in 2020- Learn SQL quickly using 30 Scenarios on Mac and Windows

teacher avatar Valuetech Academy, Learn SQL skills efficiently

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

34 Lessons (2h 19m)
    • 1. Introduction to Course

      2:28
    • 2. Installation Introduction

      0:23
    • 3. MAC Installation-SQL Server & Azure Data Studio

      19:34
    • 4. Windows Installation-SQL Server & Azure Data Studio

      10:26
    • 5. Scenario 1

      2:46
    • 6. Scenario 2

      4:41
    • 7. Scenario 3

      4:19
    • 8. Scenario 4

      1:59
    • 9. Scenario 5

      4:25
    • 10. Scenario 6

      3:06
    • 11. Scenario 7

      3:11
    • 12. Scenario 8

      1:41
    • 13. Scenario 9

      2:33
    • 14. Scenario 10

      3:07
    • 15. Scenario 11

      4:28
    • 16. Scenario 12

      3:02
    • 17. Scenario 13

      1:56
    • 18. Scenario 14

      2:17
    • 19. Scenario 15

      4:49
    • 20. Scenario 16

      3:28
    • 21. Scenario 17

      3:17
    • 22. Scenario 18

      4:49
    • 23. Scenario 19

      6:12
    • 24. Scenario 20

      4:47
    • 25. Scenario 21

      7:58
    • 26. Scenario 22

      1:58
    • 27. Scenario 23

      4:20
    • 28. Scenario 24

      3:36
    • 29. Scenario 25

      1:47
    • 30. Scenario 26

      1:59
    • 31. Scenario 27

      2:51
    • 32. Scenario 28

      4:13
    • 33. Scenario 29

      2:25
    • 34. Scenario 30

      4:20
  • --
  • 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.

74

Students

--

Projects

About This Class

SQL Tutorial for Beginners in 2020- Learn SQL quickly using 30 Scenarios on Mac and Windows
A comprehensive course to teach you SQL on Mac and Windows.

If you are looking for a complete SQL tutorial for beginners, this tutorial is a great way to learn SQL using 30 real-time scenarios.
You will be able to write SQL queries confidently by just practicing the 30 scenarios in this SQL Tutorial.
You can learn SQL on Mac and Windows from this SQL Tutorial. This SQL Tutorial will cover all the topics of Structured Query Language(SQL) starting from scratch. This tutorial is great for beginners who want to learn SQL and for professionals who want to brush up their SQL Skills.

Structured Query Language(SQL) is supported by various database management systems(DBMS).
In this SQL Tutorial, we will be using SQL Server to learn SQL on both Mac and Windows.
Microsoft's SQL Server is one of the most popular database management systems(DBMS) in the world and learning SQL using SQL Server can help you earn nearly $100,000 per year.Even if you use a different DBMS like MySQL, you can benefit a lot from this SQL Tutorial as more than 90% of the SQL skills you learn in this tutorial is easily transferable to other database management systems(DBMS).

What you'll learn from this SQL Tutorial:

1.Start from scratch installing SQL Server on Mac and Windows in 2020.
2.Learn SQL quickly using 30 real world Scenarios
3.Learn SQL using Azure Data Studio on Mac and Windows
4.Download and Restore a sample database(AdventureWorks) to SQL Server on Mac and Windows
5.Download and Install Azure Data Studio on Mac and Windows
6.Model real-world scenarios and write SQL queries across multiple tables

This beginner's course is for you if you want to learn SQL Skills in Mac or Windows.

Meet Your Teacher

Teacher Profile Image

Valuetech Academy

Learn SQL skills efficiently

Teacher

Hello, I'm Valuetech.

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.

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.

Transcripts

1. Introduction to Course: Hello, this is Sam from value. Take academy dot com In this video, I'm going to show you 30 different scenarios using which we can learn a skill quickly. Going through different scenarios is the key to loan excl Oscars effectively in order to meet today's demand. But before I get into the video, let me tell you first how this video can be useful to you. We are a team off I t professionals that more than 17 years off industry experience. When we started our career in early 2000 we took several school causes, the improver back in escrow skills. We often thought that that should be a better way to loan esque Oscars for bigness, but there was no choice at the time. Unfortunately, even off the many years, courses are being taught in the same former, and there's no change in the way of teaching in school. We created this free course after frustrated seeing all the school courses in the market offered in the same old former. Most of the skull causes have the same course structure, boring slights to explain the concept, then uses the same old employee table customers stable, student table examples to explain again the concept and they provide no real scenarios. So think on loan. The problem due to that is lack of confidence during interviews on Loughner's will have difficulty solving real problems at workplace. Based on our experience, we could say that such a school causes alone or not satisfactory enough to meet to raise demand for school skills. So going through different scenarios is the key to learn ESCWA Scholl's effectively in order to meet tourist demand. It's time to change the way we learn a skill, and we created this course for those who want to loan skills differently. And now in this video, I will show you 30 different scenarios, using which you can learn a skill quickly on more efficiently. But before we start seeing the scenarios one by one, we need to first set up the practice environment with sample later 2. Installation Introduction: in this course you will be learning. Sq. Using Microsoft SQL Server, I will show you how to install a school silver on both Mac as Willis Windows first. I was start with installing a school silver on Mac. Then I have a show you how to install a school sever on windows. 3. MAC Installation-SQL Server & Azure Data Studio: first, you need to get DACA for Mac. Just go to google dot com on typing Dhaka for Mac. You can see the official dr dot com upset. You have the doctor or the stop for Mac. Get the download button, okay. And you hear you have a doctor district for Mac on your Astro Logan to download. So if you don't have ah account, create a free account with Doc. Oh, so just free. You can create by providing a doctor ready a passer on the email, lady, and you can purely a free account. I already have a doctor account, so I'm just logging in using the doctor. Ready and password. Okay. After sending in, I can see the doctor the stuff off, Mac, and just get the get DACA on the I don't know it started so you can see the file getting downloaded. You can see the file downloaded in the dollars folder. Just click on the file. You can see it's asking, too. Dragon drop. Just drag and drop into the application's folder. Just wait for her toe, get corporate with applications. You can verify that. You can see you have the doctor. So once you've referred this, you can just close other windows. So no back to the applications. Pick on the dock off. It will ask you are the shorter open it casket open. It lasts for your password. So just click OK and probably a passer. Okay, okay. And now you can see the doctor symbol at the top, and you can see dot Docker is starting. So after a while, it will be running on. You can either poverty a doctor already, password and Logan, or you can just leave it. It's not required. So let's wait for it to start from. Start to running. Now you can see the doctor is still up and running on. You can either provide your doctor ready and passer, or you can just leave it. Now we can close this one. I'll show your tool called Kite Matic. This Kite Matic. It's a graphical interface using which you'll be managing your containers. So there is no need for using the terminal. So to get this crate Matic, just quickly here, you can see the fire getting downloaded here. It will take a few seconds for the dollar to get completar. Now you can see the file it go downloaded You can just close this one. Drop this file into the applications, go to the applications and yet you can see their father here. We conduct on its asking about you Sure you want to open it? Just click open You can either. Poverty using a passer Oh, you can just keep it for now. Okay, Now you can see a lot off images here We're looking for Emma's SQL server so just type and must ask your And after typing and misses school, you can see there are different images Starting with Amos SQL. You can see a Ms SQL server another image, Another one with emphasis school Another one with a minuscule. But you want the one from Microsoft, which is more downloads. Just click the cleared and you can see the image getting downloaded and see the image for Amos. SQL server got downloaded No help to increase the memory. So just go to the preferences. Click the advance step on the top on increase the memory toe four GB click Restart. I know you can see the doctor. The Stop it. Stop! No, it will be restarted again. You can see it getting started. It looked like a few seconds for the doctor to start running. You can see the doctor is running. Now you can close this window. No, Back to the settings part. Click on the missus school silver. Go to the settings on. You can give a name for the container. It can be in any whatever your name you want. So just added the name filled. I'm just giving goodness and miss a school server. So in just click See next we have to enter to environment variables. This is the first cable key on the value for the key Needs to be Wait and the second key is going to be S se and the score password. You can't change this key. This has to be saying the score password. But you can change the value so the password value can be anything. I'm just giving some SQL at 123 But you can give any value. Want for the password? Click See? Then you have to go to another sitting. That's host. Just come to the port numbers. You can see the port number 1 43 for DACA port. Well, how to just change it to the published port as Unfortunate week. Save it. Next. You need to connect to the SQL Server and to connect with a a school server, you need a toll. Call a zoo data studio. Just go to Google on tape As you data studio. You can see the official ing from Microsoft using which you can download the zoo Data studio. Yes, Clothing. You can see for McCoy as you have the download file. Discreet contact. You can see the zoo Tater studio cord downloaded. Just copied it to the application's folder. Go to the applications on DPI conduct. It'll ask you Are you sure you want to open it open? Let's know Connect to the server by providing the credentials for the preview features. Yes, we need to enable the privy features. So what do you have to do? Is just click? Yes, and you can just close this phone. That's not too quick. And now we need to know these silver name. So to get this 70 come to the host name on here. You can see the 70. This is a local host on. That's going to be the ceremony, so type in local coast and the authentication types is killed. Logan, you send it. Just come to gentle. You can see that s C. That's going to be that using him? Yes, sir. And for password, just probably deep value that you're supplied in my case is going to be some. It's Sam Sq at 123 District clinic. Now you can see the four system databases. They're covered. The sequel server? No, just kicked. You know, we're going to write a basic curry to get devotion of the SQL Server, select the statement. Click, run. And now you can see devotion of the SQL Server. You concedes a skill 7 2017 And you can see, like, just four data basis. You we need a sample database to work on which will see how to download and install it next . No, I don't know. This sample letter base from Microsoft website has go to Google on type the sample database . Name is going to be adventure works Date of this and you can see the official Microsoft website with the sample database. Discreet conduct is colder on. Look for the adventure works don t 17. Because we have a school 7 2017 in stone, So click contact. See you have the SQL 7 2017 Just go to the downloads on. You can see the adventure works. Back off. You can see the back off. God don't order next Oprah terminal window using this dermal window, you will be co opened this back off fight. So the docker file system get into the downloads folder. You can see the back of fight that we don't know. We're going to copy this friend to the doctor for system using this comment. First, we will be creating a directory called Back Up Inside the Docker file system On In the next step, we will be coping this back off I to the newly created backup directory Executed poverty. A Mac Plus or this Emmis SQL server is the name of the container. You can have a look at it if you go to the General Settings is the name which we provided earlier. So we are giving the container name here and you're creating a directory under the spot in the doctor fire system and the doctor name is back up very will be moving this back off. Fine. So using this coming. We're going to copy the back off. So the newly created backup folder a copy. Back off. Finally. Make sure you are between the downloads folder. If you're not in the dollars, Fuller, execute this comment. To get into the Downloads folder, - you can see the MSs School server Continent, which we provided earlier. So this continue name and using the copy Common. We're copping the back off file. So the back of folder in the doctor container. Next, we will restore the sample database to the school's server using the as your data studio, go to the local host, click restore and restore from backup fight because regarding back awful adventure works 2017. So it's a back off. Fine. So choose this option back off next Tuesday file. But so this list e file pot in the docker container. So we created the back off folder under this file part in the docker container. So just look for the part. It's underwear because school don't and Mrs School backup on you can see the back. Awful victory copy. Excellent. That fine on pick. OK, you can leave the name as a default. You can leave it us differently and click the store. You can see the database got successfully restored on the local host that this year. School server. Just go to the service on under data basis. You can see the new sample database. It got restored. Let's go to the state of this. Let's see how many tables are there within this database using the simple curry Select Discovery hit Run. So these money tables are there. 71 tables are there in this database and using the stables, we can see our demo. 4. Windows Installation-SQL Server & Azure Data Studio: to install a school server on Windows. First, you need to get the school's ever 2017 express addition, so just goto google dot com on typing a school. Silver 2017. Express sedition Don't load. You can see the school. 7 2017 Express tradition in the official makers of dot com. Quickly don't Lord no click. See, you can see the file getting downloader. The file got downloaded now, so just go to the Downloads folder. You can see the file there. The downloads folder Double click Different. You have three options. Tuesday. Basic one and go through the license terms. Click. Accept. You can see the excuse of 2017 Express tradition. Review the insulation location, the minimum free space required. Make sure you have the space and this is a download size click Install You can city experts tradition packages getting downloaded, said critic. A few minutes, the insulation getting started and it Riddick. 10 to 20 years for the installation toe get complete after 20 minutes, the installation got successfully completed. No, you need toe. Copy this connection string. Click on the icon, Open a note bird and copy this connection. String there because using this connection strength. You will be connecting to the server later so you can see the serve information. The local host Exclude Express. This is going to be this silver name using which you will be connecting later to the server . So just save this note back for later use. And right now we can close the insulation visit. We're done for. Done right now Installing the server. Yes. You can exit to connect with the SQL Server between stall now we need a tool called a zero data studio. So just goto google dot com on typing a zoo data studio and you can see the download link from the official Microsoft dot com Absurd Uganda. And you can see the download pitch for the zoo data studio. Go to the window section and you can see the user installer a commander file. Just click that Plexi. You can see the insulation fell. Getting downloaded. They don't notice. Complete. Now. Just goto the downloads folder. You can see the installation file double click on different click the accept the agreement . Let me close the browser. Next, make sure you have the correct local part where this file will be soon on. Just leave a default pick. Next on, you can verify the settings, the location and the startup Manila folder name and you can click Install. So once a click finish this date yesterday will be open. Now click finish so you can see the Zutautas studio getting started. The Privy futures are required for extensions to be fully supported. Yes, we need to enable the privy features. Just click. Yes, you can close this one to get the server name. Goto the note pad on Copy the silver name. Basically 70 and authentication type. Leave it us windows and other feels. Just leave it as before Click Konik. And no, you got connected to the SQL Server and you can verify this connection, but I think it's simply Cody clicked in. You could be using this goody. You can find devotion of the school's over to which you got connected. Now select a curry. Hey, Trump, you can see the abortionist SQL Server 2070 and it comes with four system later basis, which you can look at the top, but you need a sample database to practice. So to get this sample database, go to Google on type adventure works sample database. And this is the sample that abyss profited by Microsoft. You can find the link for adventure Works Sample database from Microsoft dot com Click conduct link. It will direct you to the download pitch school down and click The adventure looks 2017 Back off fight. You need adventure works 2017 because you installed a school sever 2017. Pick soon. So God downloaded. Go to the downloads folder. You can see the file here. Next. You need to restore this back off. I'll tow a skull server, so just go back to the data studio and you can fight in the restore option here. But before that, let me first copy this. Don't ordered back off site to my local. Right. So in my serum, I'm just creating a backup folder where I'm going to copy this. Don't ordered back off file just Corp a good. So this newly creator back off folder in my local crime. Now back to the studio to Studio Click restore and choose the back off. Fearless option on to the file part. Back up the seeder. Back off. Hold over. Czech Creator, I have this back off. Fine. Pick. OK, and leave the names is default. The Cristo. You can see the database getting the store ridiculous. You seconds and you can see the database successfully restored on the SQL Server. And we can verify this now by going to this window. If you don't see this window, just click the button here and you will be seeing this window again. Go to the data basis and you can see the sample database got restored. Let's try to Korea again the state of this click New curry and we'll try to find the number of tables in this database. So let's write a basic curry selected cootie hit run, and it can CD tables inside the sample database. So these tables already has pre populated data. This in which you can practice It has something on tables, So using this 70 on tables, we can see our demos 5. Scenario 1: make sure you got electrical their schools over. Then you can see the sample database likely. New goody Next. See the tables Within this database, you could go to the tables. You can see several tables, but we concerned this table person contravention for this scenario. So let's see what is there in this table. Let's find out the data. But in this table, person, country, region, she just busy. Good discovery and you can see like three columns are there. But data Andi conceiving name column that discerned country names. And if you have a look at the name columns, you can see the order of this name in different order. You can see that a u A and it isn't different orders. So the country names are not ordered. Any specific former. They're just randomly ordered. Let's see the requirement so that equipment is like we need toe. Although the country names in ascending order from A to Z, so that is a requirement. So it means the country names should start from a on end with Z. So should be a baby like that. I'm just end with Z, so let's rewrite this body are a glass full auto Big close. Then give the name off the column on Use the keyboard A s C for ordering the country names in ascending order from a to Z. Now you can see the country names in the ascending order from a to Z it's cold on. You can also see that the name serving the alphabetical order in this. If you want to order the country names in the sitting order from from Zito A then just replaces. Give R A S C with the keyword the SC it is going to be for descending, so just run. And now you can see the continents in the descending order. You can see it disorder from Zewe. 6. Scenario 2: go to the sample data vis on the table. This poll don't on. We're looking for the table production about product review. This is the table we will be using in this scenario. Let's see the data within this table. So that's cited. Coody guarded. Maybe you is going to be the table in the scenario. Yeah, you can see many columns, but in this table, but we are interested in only four columns. So let's really this body. So that would leave this four columns, other display in the output. So the first column is going to be the product ready. The 2nd 1 is going to be the Have you ever name the person who left the reviews for this particular product and the third column were interested. Is the rating and the fourth columnists the comments? It is the fourth column we're interested in. That's it's a good this one. You can see only the four columns in the output, so it's pretty easy. We'll see his name on the ratings. They left for the products on the comments they give for each product. Okay, lets see that agreement. So the requirement is toe mystic comments with more words in order to get more insight about the product. So basically, we need to find the Commons, which has more words so that we can get more inserts. So let's see how to get the Commons with more words because it's like, difficult to count every comment column. So let's really this Cody auto by then. In orderto find the characters in this comments the use of function called Ilian, it is going to return the length off the column on. Then we're going to use the Give a B SC. So find the Comus with more characters at the top. So then he exhibited this one. The output can see God like shuffle The Commons, with more words, got at the top on the comments with less worse, got to the bottom. Let's verify this. Just go to Google and the type of wood counter so that we can verify the votes in the Commons. So if you copy the first common and he placed into the toe, you can see these many words are there. So total 6 43 words are there in this comment on Let's Check the second Coleman and see how many boats are there in this common go to the to you can see just 89 words in the second common. Let's see how many words and the 3rd 1 You can see just 60 favors so you can see that the comments got, Oh, don't in a message that the Commons with higher votes I mean the comments with more words godless take to the top and the comments with less of us came to the bottom. 7. Scenario 3: goto the sample it of this you goody, Let's go to the tables, Scroll down And here the table production dot transaction history. This is the table of we're going to use in this scenario. So just try to Cody to see the data, but in this table contributed. Now you can see many columns in this table. We're interested in this four colors, so just relate this body so that only the four columns are displayed in the output. The first column name is going to be the transaction i d. The second column this product ready? The third column this transaction did on the Fourth Column is going to be the transaction type. You can see the four columns in the output, and you can see a total off 113 key rose go to return. Well, let me right by the number of us that got return. It's going to be 1 13 key, so it will be exactly 1 13 443 rows, according God return. Then you executor this curry. Let's see their equipment. Now the requirement is toe display only the 1st 10 personal heroes, So don't be those which got despaired we want only the 1st 10 person Toby despaired. So we can really discovery just adding a keyboard at the top. It's going to be it stopped 10% the keeper executed, and you can see the top 10 person off the listen. There's going to be 3 11,044 euros that God return when you use this keyword top 10%. But if you see out of the total round post which Patrice earlier, the 10% is going to be dissemble number is not going to be a whole number. It's going to be 11344.3. So this is the decimal value will be getting for 10 person, but you can see a whole number. So this is because SQL Server will be rounding to the next the whole number. So if you see here, 44 is there. So the next hole number is going to be 45. So SQL Server rounded to the nearest whole number so that swiger seeing for 40 45 and we have used talk 10% cable to list knock 10% off the rose from this incident 8. Scenario 4: goto the sample rate of this new goody under the tables just closed down. Andi, consider table sales. Start credit card on. This is the table we're going to use in this scenario. Let's see the data in the stable sales, not credit card just executed on. You can see these many columns, but in this table on, we won't go find the data within the column card type. So, Justo, period this curry onda into the column name card so you can see the output for only the column court papers and you can see multiple values repeater More than once. Let's go to the requirement. It says we need to find the total credit card types without any duplication. So we want to find only the unit values. If you see that a cost each values are like, um, repeated more than once, so used to give a distinct. In this scenario, the filter all duplicate records so you can see all the duplicate accords God filter, and you can see only the unique values under the column car type, so using distinct giver, you can do this 9. Scenario 5: go to the sample that service on just goto the tables Slow down on. We're looking for the table product review. You just hold on and you can see the table Product review on this is the table we're going to use in this scenario. Let's see the data but in this table, and you can see many columns in the stable. But for this scenario, we need only treat columns on. There's 1/3 column, which we require in this scenario. So let's really this curry to display only the three columns. The first column is going to be the Tory party. The second column is going to be the reviewer name the person who left the review for the product and the third column is going to be the rating column. Let's execute this one. You can see the output for only the three columns, and you can see the torrid ID's on the personal of tea reviews on the waiting. They left for every product ready you have in 18. Let's see the requirement. Now the equipment has to display the output in a more understandable way, so it means, like if it this one needed this players. Poor Dorman's fat treatments. Good on a fireman's. Excellent. So? So, if you come back to the output, these are just numbers. We want to display some meaningful text. So that's what we're going to do in this scenario. So let's relate this goody using the case statement. So this is the syntax for case it in certain in. So this is the Syntex on with this statement. They're going to write the conditions, Ben. It is going to be one. Then we need to display. Just refer the note back. Yeah, One minutes. We need poor. So we're giving this condition within the case statement on the next when it is too. Then we need to display us. Let's see. That's fair. Just write it here, huh? If the rating is going to be three, then we need to display good. I could hear. And if the rating is going to be four, then we need to diss players. Very good. This copy I've basted here. And finally, if the rating is going to be five, then we need to display us. Excellent. So if you see the note breath? Yeah, we have the equipment is excellent for rating number five. So just type in. Excellent. Let's so next. Well oh, it's a good discovery. Now you can see and output for the product ready. We have the rating in a more understandable instead of just numbers, you can see that text for each rating. 10. Scenario 6: go to the sample later. This click New Cody, go to tables, scroll down and have the table beloved material on This is the table we're going to use in this scenario. Let's see the data within this table using the seller Curie production dot beloved Materials insecure. It'd and you can see many columns in this table, but we're interested. Only these three columns, said Kendall, in on this 3rd 1 So we just want to relate this Cory with this three columns, the first column was going to be the Bill of Materials i d. The second column is going to be the product Assembly i d. The third column we are interested in the stabilised started just right. Start date on. Let's execute this one and you conceal it. Three columns in the output. It's good that the requirement now so it says in the output displayed instead of the null values need tohave values. Zero for product assembly ready? Call um, without changing any values in the table. So basically we need toe replace null values, but zero for one. Leading call him product assembly 80 in the output alone without changing any values inside the table. Let us. We're not going to do any permanent change. So if you see in the product assembly 80 you have no values. School done. You can still see some null values. We want to replace the snob value. Ask for the requirement but value zero. So I'm relating this curry now using the function is no. So using this is null function. We can replace null values in any color with the specific value. So in this case, we're replacing this null values but values zero so no and signalled this one. If you see in the output, you can see the non values got replaced with zero. It's a Skoda. You can see all novels got replaced with zero. So if you want to replace the null values, use the personal function. 11. Scenario 7: Goto the sample later this click New equity Go to the tables, Schooler, don't on. This is the table we're going to use in this scenario. Let's see the columns on data within this table. Priapic table in production dot product Morgan Product description. Culture is the labeling, so it's a good this one hit run and you can see four columns in the output. Let's see the government now, so we need to name the columns on the table in with an alias. Alias Smith is a temporary name which were going toe give for the payable Name on for the columns and we want to give The alias came for only the two columns, not for all columns. So let's go back on Really like this curry with only those two columns which were interested. So the first column there's going to be the party more lady. All right, The second column guarded description I. D. This is the second column were in custard 2nd 1 So if you go to the equipment, you can see these two columns. So we have this two columns here. That's a particular this goody, and this can see one leading new columns in the output, but aspect the requirement. We need to give an alias name for this column side. Give it i d. And for the second column, I'll give a alias. New MUS description. 80. So execute this one now is the serial put You can see the column Libs. God changed. You can see God change. So this is because you do the keyword asked which a used next. We want to give an alias name for the table. So go back to the curry. And for this table, you're going to give an alias name because it's a pretty long name. We can't use this every time in the curry. So the alias name E We're ascending Noticed Able name. So this is a temporary name ritual but despaired one leading the output. So you see no values got changed after using the alias name 12. Scenario 8: go to this sample that abyss iniquity, go to the Davis studio and look for the table person dot Addressed by This is the table we're going to use in this scenario. Let's see the columns on data in this table are the state. It's a good this goody and you can see columns in this table and also help six of those in this table. Let's go to the requirement. Now we need to filter Rose. That has the name RK from the display. So look for the word RK So we have, although, but the name RK in one of the column, which we need to filter out. So we're going to use a fever. It's not actually keyword. It's going to be operator. Not so using which we're going to filter that particular record. The sixth row we're going to filter out. So when you exited this one, you're going to see zero. But the name our kid was not short on the display. So use the operator not 13. Scenario 9: book of the sample databases. Nuclear Go to the Purpose scored on on We Have a Table producing dot purchase order detail on this is the table we're going to use in this scenario. Let's see the columns on data. But in this table insecure, this curry hit from you can see the purchase orders 40 products on. This is the place for each product. Let's go to the requirement. We need to find all purchase orders for only the product I disciple that cost only less than $35. So we have two conditions here. No filter out. Let's go back. So for the purchase orders we want to filter out portrayed is, let's fight all value first. So let's ah, check the road rules. Based on the first condition that we need the park I d. Fatal. That's confirm that Yes, I don't is it here and now you can see the purchase orders for only the products fatal. So if we see the equipment we need to sign the cost less than $35 also is the second condition. So if we see the price for each product, there are different prices, but we are interested so fine praises Richard Listen, 35. So do include a second condition, though Fichter rules were using the operator and in this scenario, execute this one and you can see the port disorders for products fight on that has placed less than $35 so using the operator and we have conveyed two conditions. 14. Scenario 10: go to the sample database. New clearly. Photo tables Hold on production Dark Kordic This is the table we will be using for this scenario. Let's see the columns on data in the 10 this table production doc product. That's exactly what this one Hitler and you can see. Many columns within this table could just more hurt. You can see many columns, but they're interested in only three colors. The name column. The product number on one more column, third columns in this place. These are the three columns were going to use in this scenario. So let's really this goody. So let's think about only the three columns in this query name is the first column. The Second column. We're interested in product number, so I D column name. The third color is going to be the list price. So these are the three columns we will be seeing for this scenario so you can see the output, but only these three columns so you can see the product names with different lists praises if you scroll down. We have different places for different product numbers, and you can see the product names. Let's go to the requirement now find the name of products that has a list price in the Rangers, 10 to $20. So we want to go find the products, but in the specific range. So we have a range 10 to $20. So let's really discovery. No, we can do this using the operator between, So the range of al is going to be 10 to 20. So if we execute this one, you can see new products. But in the strange 10 to $20 send we can do this using the operator between 15. Scenario 11: go to the sample bit of this, you could be Go to take a bus production dot Work order. This is the table even. See in this scenario that city columns and data within the table production dot What order you consider a work order ready and propriety order quantity and other columns in this table. So you see this order to suit Let's go to the requirement. You can see three requirements here the flesh torn This find records for porters with productivity 95. So let's go back to the table. You see the particularity cola you have Ah, different particularities. But we won't go find for according 90 side and it's work orders. So let's rewrite this curry. Now you can see the work orders for one needed productivity. 95. Let's see the second requirement Find record for Porter that porter 39 95 that has more than 500 orders. So let's go to the table and see how many orders are there for the product. Ready 90 feet. You can see unidentified. There are distant order quantities, but they want to find order quantities more than 500. This reader decree No people used the greater than operator for this requirement. Bartender, you can see the park it in, identify on order quantity more than 500 and go to the third requirement for poor recorded 95 that lasts more than 400 orders. We also need toe. Make sure that s a suit. Before May 2013 you can see the orders were received at different dates. You have a court start 2014 also, but we are interested to find recourse. The city only before May 2 2013. So again, we're going to filter that the courts using another comparison operator is going to be less than operator. May 3rd. And we got May told here, It's a good this one. And you can see the Park Cuddy and identify and order quantity more than 500. Let's see you before 2013 May the so I confided 2014 records. So you filter records using three comparison operators. In this scenario, the first is the equal operator. The second is the weight of them. And third, it's a listing operator 16. Scenario 12: go to the sample database. New Cleary Goto tables this colder and here the table production dot product that you will be seeing in this scenario. Let's see the columns and data in this table production dot product hit run and you can see the column Product I. D. Name product number on many columns, but in this table. But we're interested in the name Colon on one more column. The second Columbia interested in list price So very invested in only these two columns for this scenario. So let's relate this curry that includes only these two columns in the output. The first column is the name Colon. The second is going to be this. Let's praise so right this place in the goodie. Now you can see the two columns in the output. You can see the product names Andi in list prices, so each part has a different places. Let's go to the requirement. Find the name of products that has these three misplaced values. So we need to find the name of products with this three places. That's really this goody the colony list price, and they're going to use the operator in, and we'll copy this list values. You can see the product names for only the three places. You can see the 1003 this part of the list and triple three part of the list. So the values the list place are part of the list or not six. So the product names are only for the list values. 17. Scenario 13: go to the sample data vis new goody kowtow neighbors splattered all we have a table person dot country region. This is the table we're going to see in this scenario. Let's see the columns on data within this table using this simple select, you can see the country courts on the contrary names. Let's see the requirement. I need to find the name of the countries. Let's start with the letter V. So we need to get the name of countries starting with the letter V. That is the requirement so you can see that we have the continued names starting. The different characters started you and all characters out there. But we want to figure out contain names that start with only V. So we're going to use an operator called Like so, using the slight cooperator, we can filter the continent's so you can see I have used the percentage after V. So let's execute this one so you can see the containing starting with the guard Mr using the like operator. And if you want toe list only country starting with V A. Just include I, and you can see continues starting with the godless too 18. Scenario 14: Brookly Sample date of this. You could be goto tables production recorder. So this is the table we're going to use for this scenario. Let's see the columns on data within the stable production. No work order hit. Run. You can see people quarters for the product. ID's along with their order. Quantity also can see this crap reasons for the work orders. What you can see is majority of the column values are no, they don't have any value. So even if you scroll down, there are many null values. So let's go to the requirement and see so fine work orders that has a scrap prison. So we want work orders with some known none values. That is. We want columns calling values that should have some record values. So we want to filter the null values from the output. So very relating this curry using the or operator it's not know, so it's not null operator. Well, Senator, all the null values in a column. So they got all or coyotes that has this crap prison so you can see all the null values God filter so you can find any natural is here sort of filter novelists in a column. He used the operator. It's not now 19. Scenario 15: but there's something sort of this new, clearly good. Good requirement. Have you got to see You need to find the name of products having more than 20,000 order quantity. Get the order, quantity and permission. We need to go to a specific credible Just hold on and look for the table production. Dark work order. So this is the table where we're going to get the order. Quantity Looks like a simple select to see the columns within this table. Work, order, hit, Run. And you concede many columns that in this table so you can see the order quantity which we are looking for And you can see the product i d column. But it can't see the product name in this table. Well, you can find the productivity on order quantity The requirement were supposed to find the name of departed that has more than trumpeters. Another quantity. But we have only the productivity in this table. So we'll rewrite this body The first Find it, Porter. Pretty well. Find it. Product name later. So more than 20,000 club. Now you can see the Port Authority for order quantity More than 20,000. Next goal this is putting one. So this is just, er comment. Even if I execute this one, there won't be an issue. You can see that and recorded quarter tidies for order quantity more than 20,000. So coming back to the requirement they need to find the name of products. The name of products can be found in under the table, so correction dot product is a table name, very consigned it product names. So let's write a cell. A query. Find the columns. But in this stable production, don't product Hitler and you can see the Quarter cardy and me and also has many other columns. They want only these two columns, so we will relate this quickie first columns we wanted the Porta Chraidi on the second column we want is the name, so I d call him there on his own. You couldn't see new columns in the output, so call this query, too. I didn't see all the product names for all product ID's, but we want the product names for only report. A Tight is written by could be one that has order quantity more than 20,000. We don't know what the product names for all participating. So they need to pass quickly. One to quit it toe. So we will do this. But relating the Quito, we're going to pass quickly. One into that and we'll be using their any operator. Just copy the curry one on a positive equity to so you can execute it under conceding product names. But for order quantities more than 40,000 and you can see that you caused it could be one into quitting toe. Since your past, Corey one Intertoto Corleto we call the Cory one as a sub. Quit it according to 20. Scenario 16: go to the sample database. New Coody Poker tables, production, Dark border. But the table we're going to see in this scenario, that's Cory the table production dot product hit plan. We just need only two columns from this table for this interview. Name. Product number. Let's rewrite this Cody, including our ladies two colors. Man on the Second Column is going to be a product number. Hitler, and you can see only the name on product number in the output. Go to the grammar. The colon product number has characters in a book. It's so convert characters and name, column or soil a bookie's. So let's see the column product number. Send the colon product number. You can see the characters in a book. It's so we're occurred to convey the characters and name column also got Booker's. You can see the characters in lower tiers on. We require toe. Convert them into our focus. That's copy this curry. Let me Prester on Really this curry. Let me use a function, a book. So using this function upward, we can convert the characters from lower case to our bookers. So if you need to name the color, you can nd call him using the ask evil. Another considered column. Values got converted into our focus, using the uproar function. Let's see the original Cody. So we have characters in Lower Pierce. And if you want to convert this characters to lower case, we can do that also so we can convert a purpose characters to lower case using the lower function bonded columnar is he. Could this one Pete Tong and you can see the name column and lower kiss on product number got converted into lower case using the function lower. 21. Scenario 17: go to the sample database. New goody poker tables Production. Dark product. This is a table you're going to see for this scenario. Let's go to the stable and see the columns. Production. No product. Select Hitler can see many columns, but we need a leader. Two columns. Name on a product number. Well, let's relate this curry with the two columns. Name product number. Hitler. You can see only two columns in the output. Let's go to the agreement. We need to get only the two characters from the product number for each name column, so we need to get only the two characters from the product number column to go to the product number. You can see characters and numbers in the product number, but do you want to get the two characters from the left? Sir? So let's copy this party and based it again. I'm going to relate this goody using your function called Left So using dysfunction, you're gluing extract the left most do characters so that their function extracting the two characters from the corner hit her and you can see we got one lady do characters. Let's go to the well, Jill Corey so if you want to get the four digits for the names, we can very discord in a different way. So since you want to get the four numbers, which are on the right side, we're going to use a function called right, and we're going to extract the four numbers from the right side. So give number four. This will extract four numbers on the right side hit run. So for the names, you can see the only numbers for the numbers. So since there is no column name, let me give the column name as product number Hitler. For each product name, you can see the four digit product number. 22. Scenario 18: go to the sample database. New goody production. Dark work order is a foster, but we're going to see in this scenario Let's go to this table production dot work Order to see the columns on data in this table hit run were called already Product Ready are the two columns we have interested in this table. So let's eliminate this goody toe. Include only the two columns. Work order I D. On product. 80. You can see only two columns now in the output. Let's see the grammar. You want to find a product name off each product I d along with the work order, you can see the work order ready on product ready. You need to get the product name along with the work order ready so the product name needs to be displayed next to the Port Authority. Asked for the requirement, but to get the product name, we have to go to the table production don't product. So from this table, we're going to get the product. Name selects Carly the stable production dark product to see the columns I D on name are The two columns were interested. There are many other columns, but we need only this two columns Park I d. On name. So let's really this goody to include only the two columns I d man so can see only the two columns in the outcome. But the first table If you see you can find the name you can see only the product I d. So you're going to complain Tito tables. So let's copy the first table given Alias. Yeah, so for that we're quarter table. The aliases given us a We're getting the two columns work order I d on product i d from work order table. So they're giving the prefix to the call of names. So the second it was going to be product table. So we're going to combine the work order table with the product table using the in the joint so that we can get the product me given Alias new the production, that product as b the alias name is going to be be. And we're going to get into column name from the second table product. So included column lee be dot name. And if you see the two columns Sorry to tables, you have the common caller product idee. So we're going toe complaint. This two tables work order on product based on the column product i D. But is common to both off them. So a study for stable work order and be the first to the second table, product and product. Ready. The sourness from the four stable on the second ist from the second table product Hitler and you can see for corn variety product idea name and you can see the product names displayed next to the work order. Ready on product i d. So we have company two tables using the inner join based on the common column product ready . 23. Scenario 19: coated sample database. Nuclear good good tables ruled on on it. You can see the table production that border on. This is the table we're going to use in this scenario as the first table. So let's go to the stable production dot product on See the columns on data within the stable. It can. Kartik Idee on name are the two colors we had interested for this scenario, so let's really this goody, including only these two columns product idee on a name because he only that two columns in the output Let's go to the requirement. You want to find the sales soldiers for all productivity's, along with the product I d. On its name. So you need to get the same disorders for all the productivity's in the stable. Production that product. So the product I D name called a model. You need to spray the sales order next to so to get the sales of the details, you need to go to a second table. Just hold on. So the second temple is going to be the sense other deep in from which you can get the same . So the details Let's go to the stable and see the columns and data within the stable. Sales don't sell order detail, since already on productivity are The two columns were interested from this particular table, but you can see any product name in the stable. If you see the requirement, says owners needs to be displayed along with the product idea name. The name is the product name, but it can't find any product name columns in this table, since all the detail you have to get the name information from the table product. So now we'll really discovery. Using the two columns i D. On sale sold already core cardio and said it's already recorded in the second Curry and Output. Consider one leader, two columns. The 1st 1 You can see two columns on here. Also conceived two columns, but in the first goody, along with the product idea and name calling, we need to display the sales already. Seven. Copy this one. The fast Able Give a alias near yeah for the table product. So we have two columns. Product I D. Had name from the table production dot product. Soviet, including the alias is perfect. So it's a dark name. So you got the first call on the second column from the table product. Next, we're going toe to a left join. But the table says other deacon, even alias be for the since older detained. So for city toe tables, you can see the product I did column common to both of them. So we're going to do this. Left John based on the Common column. Carter I. D. So this particularity is from the product table on this park. Ready? It's from the second table since other details. So we got the two columns from table you, and we need to include the third column from the table since of the duty that is going to be the sales order i d. So they got the liquid. Three columns Andi combined These two tables you think love Joy based on the common column Product Ready. That's executive, this one. You can see all deported ID's on the same sort of for those products so you can see some done values for a few productivity's. But if you scroll down, you can see deported ID's and the sales orders associated with them, and you can see the product I D name along with the sales of the ladies 24. Scenario 20: go to the sample database. New clearly goto tables. We're going to see two tables in this scenario. Production dot product is the first table. Let's go to the stable on DSI. The columns and data production dot product hit him. You go to see only the two columns, product idea and name. So let's treat at this curry with the two columns. Product I D Unnamed said. Like this one hit run. You can see two columns in the output. The second table. We are going to see this product review. Let's see the columns and data in the stable product. Review Hitler Soviet interested in two columns. Product. I D On Corman's. I made a discovery. The first call Correct i d. The second column is going to be Commons hit Run. You can see two columns in the output. Let's go to the requirement. You can see that we need to find the reviews of products along with the product name. We have the reviews of products that is the Commons, but we need to get the product name next to that. So we have the Commons column. So next to the Common Scholar, we need to have the product name, so we can do that by combating product table with product review using a right joint. Let's take this must able. Let me give an alias for the stable as a and we need only this column Name time. Remove this product ready and it is from table product. So I'm giving the earliest in a next I'm going to come back with the second table using the right joint so the second table is going to be production dot product review. I go to give an alias for the second table as B on the second table, the product idee and Commons we need. So we're going toe. Add the column names in this quarry. So the alias B, which opposed to the product privilege table and they're getting the product ideas on the table. Next, we're going to get the comments from the product review using the alias. Be no comments. Se facility two tables. The common column is going to be the productivity using which were going toe. Combine this two papers, so a not productivity. It's the first of the product I dig in the table product because be dot productivity Richard Quest OD product. I d. In the table Product review. That's an error, because I left out the comma. Okay, if I execute this goody, you can see the product name next to the Commons. So the product name God displayed next to the comments. So if you take the first comment, you have the product, man, my quest for the common three. You have the product name aspect, the requirement. 25. Scenario 21: go to the sample database. New Coody Goto tables Production Good product is the table we're going to see in this scenario. Let's create a stable production dot product and see the columns on data within this table . Selected Hitler and you can see many colors. But the product i d. Name on one more column. The product, subcategory 80. These are the three columns were interested for this scenario, so let's regulate this curry with the first column product. Heidi The Second column. Men, I thought, call him product, subcategory idee. So only these three columns we will be using for this product. In this scenario, you can see the three columns productivity name targets of Category 80 so some of the columns are none. But if your school, though you can see some values, let's go to the agreement now. So you want to find this up category name to which each product belongs is the first condition, and also we need to find if any subcategory name. It's not a saint to a product name, so we have to find two things. So the fastest, like we need to find this up technical name for each product and the second is to see Is there any subcategory name? They took the product Name assent to it. So that would like to conditions settle Buddhist They will be using Ah, full John in this scenario. First, to find the subcategory name, we have to go to a second table This cold? Um, this is a table. So the second it was going to be production Dark product subcategory. So in this table, we're going to get this up category name. So let's go to this table product subcategory selected hit, run. And you can see the product, subcategory lady name and many columns. The part of sub critically i d. You can see the first table. Also the product subcategory 80. This comment on both tables and this is the column name. We're interested in this subcategory name. So let's ah, really discovery with these two columns. The first color. The common column to both tables. Product subcategory i d. The second is a name colon. So the name columnist, the subcategory name. It's not the product name. So executive, you can see only two columns, the output. So if a CD for stable, you can see product ready. Name part of Sub getting ready on the second table, you can see the products of category and name. So the name calling if you see it, looks like common to both tables. But there's a difference so that the name and for stable is a product name on the name in the second table is going to be the subcategory name, so that is the difference. The next copy the first able, and we'll join this now on Before that were given Alias you for the stable since we're getting this three columns product. I d. Naim Kordic subcategory I d from the table production. That product. We're going to use the alias he in all the column. It's as a perfect. Next, we're going to do a full John with the second table production dot product category and given Alias has be for the second table. If you're seeing the second table, we're goingto get only the name calling from the second table. So I'm just including this column name. Be dark name so that be alias a first to the second table products of degree and production . That product on productions of your degree. Other two tables, which has a common 80 that is a subcategory I d. So you're going to join using the common column product, subcategory 80. Using this on giver, I know you can see all the columns along with the subcategory name. If it's colder because see the product idee 6 80 It has a name and we also can see the Socratic, the name for the product liquids. You can see these are critically names for all products. So that is the first condition we need to find. We need to find us a critical name toe which each product belongs. So they phone d subcategory name for each products. So there are some products without any subcategory. Name on the second equipment is also need to find if there is any subject it'd name without the product. Name assent to it. So the name column The product name column should be no for any subcategory name, sir. If we go through this a pathetically name column you can't find any product names as now. So it means every product men got us into us up category name, so I can't find any normal values. So for the second requirement so all product names gotta cento us up category name so that so you're not able to find any null values in the product Name column. So we did the full of joy for the two tables using the common I D subcategory 80. 26. Scenario 22: go to the sample database, you could be co two tables. Production dot product is that people were going to see in this scenario that city columns that in this stable production, the product and you can see the caller's Porter tidy name product number. So let's see the equipment check. Homely product numbers are there without any duplication, so you to get the count of the product tumbles without any duplicates. So that's the requirement, so you can see the product number column we need to get how money off them under the count of the product numbers. So first, to get the count, you're going to use a function comb on the column name product number Petra, and this will give the count of the product tumbles, but go to the requirement. So along with the count of the product numbers, we need to find the records without any duplication sort of filter. The duplicates were going to use a keyboard called distinct 70 years distinct. If there are any duplicates, it will be removed. But after learning that you can see the same number, so it means there are no duplicates in the product number, so you can see distinct and count how it can be used. In this scenario, 27. Scenario 23: go to the sample it ofhis new Cleary Goto tables on sale starts, sales order detail. This is the table we will be using in this scenario. Let's go to the people sales. Don't sales order details Hitler and we need only four columns from this table sales sort of i d product Heidi Line Total Modified need. So let's so really this curry. With only this four columns, the first column is going to be a sales order. The I D. The second column product. Heidi The third column Lying total. The fourth column modified date and a consumer leader four columns this plant in the output Let's go to the requirement. We need to find the total revenue from the product Triple seven sold in the year 2011. So we need to get the revenue generator by the product. Triple seven during the year. 2011 If you see we have productivity's for different products. But we need a Frederick for only the poor Takai et Triple seven. So let's repair it. He called the Triple Seven. It's contender. It is a triple seven, and you can see we got records only for the poor credit triple seven and regard the total revenue for the Triple seven products. But we need to get the sales done only during the year. 2011. If you see here, we have the revenue generated in different dates, but we want a fritter it for only the sales done during the year. 2011. So we're going toe specifically the day trench it is. We want no filter by records starting from Jan. 1st 2011 Toe. December 31st 2011. Cynic Hitler. You can see the Porter County Triple seven for only the year 2011 now, so they got Triple seven on the year 2011 filter. Next, we need to get the total revenue. Total revenue is nothing, but we need toe. Add all this revenue generator during this year. Country 11 and it can be go we can get. It's on the column lying Total civic and no revenue. Other columns because we can get the total revenue from the column line total so other columns are not required. Get off the so you can see the total revenue for the poorer people. Seven. During the year, 2011 get the total revenue they're going to use the function some. So, using this, it will add all the revenue generator for the porter Triple seven during the year 2011. 28. Scenario 24: go to the sample that agrees nuclear Haber's on sale start sales solder detail is the table we're going to see in this scenario and similar to the previous scenario we're interested in. One lady, four columns says Order Ready product lady learn total. And what if I did Ralitsa. Really? This curry with the four columns sales order Ready Product. Haiti nine Total Modified Dude picture and you can see only the four columns in the output. Go to the equipment and we need to find the average price on which deported people seven. God sold in 2011. So for the product triple sever that got sold in 2011 we need to find the average sale price. So we have different product days. ID's so similar to the previous scenario. Well, first, filter the records for product I d propose seven and also really state of the records for the sales made only in 2011 and you can see only the part of credit to pull seven sold in the year 2011 and we need to find we already got a product. People seven sold in 2011. We need to find the average place for the product Triple seven, so the average Briskin can be seen from the column line. Total sort of compute the average place. We need one lady column line total. So we're just removing the other columns from decree. So we have the line total. You need to get the average sale price, so forget the average value. We can instill function. A V G stands for average, and you can see the average sell place for the product Triple seven sold in the year 2011. 29. Scenario 25: go to the sample database, you could be production dot product inventory. This is a table we will be seeing in this scenario. Let's carry this table production. Don't product inventory. You can see different border coyotes on their co introduced. Let's go to the requirement. We need to find a loss quantity in stock for the product. I 89 44 So we're interested in this only one part of charity, and we need to find the lowest quantity in stock for this product. Charity. So let's figure this those so that we can see when leading records for product ready 94 Cool. That's confirmed a number. It's 944 Hey, Trump, and you can see only the records for 944 and we can see different contradicts in stock for this particular product. Since you're interested in only this column quantity on, we need to find the minimum quantity. I'm using dysfunction men against the column quantity, and you can see the minimum quantity 81 for the product. 944 and regard the lowest quantity. Asper. The requirement on it is 81 for the product name for four 30. Scenario 26: go to the sample databases. New quickly. Haber's school, though on production dot product inventory, this is a table we're going to see in this scenario. Let's carry this table to see the columns and data that in this table you can see the port authorities on their locations and the quantities a different location. Let's go to the equipment we need to sign the Nargis quantity and stock for the particularities. Seven. Post seven So products. Seven for seven. It's what we're going to figure now. Senator Hitler and you got one leader Porter Carries 747 Are different locations having different quantities, but we need to get the largest quantity in stock for this product. Ready. So to get the not just quantity, we're going to use a function Max on this column quantity cellar, and you can see the maximum quantity suspected equipment. We found the largest quantity in stock for the quarter. 747 That is 161 Using the max function in this scenario, 31. Scenario 27: but the sample database New goody on Goto tables This growed up look for the terrible person dot state province on This is the table we're going to use in this scenario. So let's a courageous table and see the columns on data within this table. Person. Don't State Province hit run and you conceive many columns. State Province 30 State Province scored and you can see like a the name of the States. But we are interested in only three columns from this table, so I'm just going to like, really this goody using the three column names. The first is Straight Province 80 The Second Estate Province code. The third column is going to be the name the name of the stick. That's executive, and they have only the three columns in the output. Let's go to the requirement. The requirement is we need to display the state court on state name in this former because like oh, we have a state called a K state named Alice comments. We need to display both of them using a Hyson connecting them. So if it's a baby, you have to connect it with a hyphen. UK Hyson, Alaska A hyphen. Our concert like that, we have to, like, connect them. So we booted. Go to connect this to columns State Province Score on the name column using a function called Con Cat and to connect it to columns Column values using the hyphen. We're goingto provide the value in between them in the Concord function. The next executive, this one. And now you can see the output. The state court on the state name connecting with the hyphen. But you can see there is no column name. So if you want a name for the column, it could just providers for using the keyboard as so. If we execute, you can see the column name state, and it got the hyphen in between body called on the state. 32. Scenario 28: go to the sample that this new clearly on Goto tables and have the table purchase e dot purchase order Dayton. And this is the table we will be seeing in this scenario. So let's go to this table. What you see? Not but tooth order didn't. So you can see many columns in this table. But we're interested only the fight columns from this table. The productivity unit Price order, Quantity line total on rejected quantity. These are the only five columns we need from this table to see in this scenario. So let's hurry. Right. This goody so will include only this fight columns Support of Haiti. The second column is going to be the unit price. The third column order Quantity before line total on the fifth column requires ejected quantity. That s a good this one. And now we have only the fight columns. Correct idee unit price at order quantity on the total price on the quantity And if you see the line total, it got computer from the two columns unit price on order quantity. So if you take out any other aro product quality that your wordpress $47 on the other quantity. Three. If you multiply, that will get the total value 1 42 So let's see that equipment. We need to find the amount lost due to the rejected quantity. So if you go to the table, you have the total value for the orders. So we have the price per unit on the order quantity, but we want to get the amount lost. So what we can do is like we have the price per unit on the rejected quantity. So just payment to play both the columns. We can get a computer color, which will give the total loss value, so I'll show you how we can do that. So include the Colony unit price. So this column will give the price per unit. Include the column. Lee ejected quantity, so my to play them on. They will get the calculated scene, which is going to be the I won't lost due to the rejected quantity. And if you see Gotti Computer column lost amount. So might be playing both. This collar's so for product ready food. We got a price per unit, $57 and the ridiculous quantities one by my two big boat. We got the law summer. This quill don't will see for other products. So you can see the porter 29 4 to 1, which is the price per unit of $62 on the ticker quantity were 82. So if you want to pay, both will get the amount lost. So no, you saw in this scenario how to do the calculated see? 33. Scenario 29: go to the sample database. You could be good papers, hold on and production dark product inventory. This is the table we will be seeing in this scenario. Let's go to the stable to see the table columns on data so you can see the product piety on the locations where it is located on the quantities at each location. So for product ready one, you can see multiple locations on their quantities at each location. So let's go to the requirement. We need to find the lowest quantity for each correct ready in the inventory. I just need to get the lowest quantity for each participating. So if we see product anyone, it is located at the locations, and we need to get the lowest quantity among the key location. It is going to be 3 24 and if you see product to it is located at three locations on the lowest quantity 3 18 which is what we're going to get for all particularities, the lowest quantity. So let's really discovery. First, let's read the column important I d. And to get the minimum number they already used, the function meant him again, which we will be using on the column. Quantity aren't getting minimum quantity for each product. 80. They're going to group this product ID's together by a class called Group by so used to grew by, close the group. Each park it is. Let's see, You can see the park ID's got group and for each product ready, you can see the lows quantity in stop, and you can see how we can group the productivity using the group close. 34. Scenario 30: look of the sample drink of this nuclear on Goto tables scold Oh, and you can see the table production about product inventory. So this is the table we will be using in this scenario. Let's carry this table. You can see the quarter coyotes a different location, and they have their own quantities based on the location. The visit product one. It is located at a locations and has its own quantity. Let's go to the requirement. So the location count this less than three for any product ready, then we need to find the lowest talk quantity for only that property. So fit no down the need to find the location called. If it is less than three, then only we're going to find Lois quantity. So if you see Park Ideal one, it has three locations. So we're not going to find the lowest quantity for product ready one because it has three locations. He want Porter Crazies, which is listed three locations. So let me this cold on and show you some park ID's, which is less than three locations, and we're going toe find the lost quantity for only those properties. So let's see the party fire seven. It has only two locations, if you can see, and we're going to find the lowest quantity for such productivity's. So the test only two locations, so you can see only two locations for pork ready for your seven. So we can really this Cody The first of may include the Park Heidi. Next to find the minimal quantity for such products, which has less than the locations I went into. The function meant and give the quantity column name and to see homely locations are there for that particular product, I d. I'm going to use the count off Location 80 which will give me homely locations out there for that particular product. Heidi. Next, I'm going to group this product Heidi's, so I'm going to group it, using the group by close. And finally we have the condition such a use of condition on a group by close along with the group a close. We'll be using another close call having so we have the condition that this we need to find locations less than three segregating the location count that's seeded equipment, so we need to find location countless than three. So we're including a condition on the group. So you're using the having close along with the many quantity and grew by close so that we exhibited this one. Another can see only two locations for the purgative for your seven and also for other products. We have just listen three locations and we found the lowest quantity in stock only for such products using the main function and the having close. So now we have group the product right is using a condition in this scenario.