The Complete SQL Server For Beginners | Ahmad Mohey | Skillshare

The Complete SQL Server For Beginners

Ahmad Mohey, Software Developer & Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
60 Lessons (5h 31m)
    • 1. Introduction to The Course

      4:15
    • 2. What is Relational Databases and SQL Server

      3:04
    • 3. Cloud Databases

      4:10
    • 4. Install SQL Server

      6:14
    • 5. Management Studio

      4:14
    • 6. SQL Server Database on AWS

      8:14
    • 7. SQL Server Database on Microsoft Azure

      13:19
    • 8. Create Your First Database

      8:44
    • 9. Create and Manage Users

      9:25
    • 10. Database Schema

      7:05
    • 11. Exercise Create User

      1:51
    • 12. AdventureWorks Database

      2:35
    • 13. Database Tables

      16:12
    • 14. Data Types

      11:49
    • 15. Constraints

      9:52
    • 16. Drop and Truncate Tables

      2:08
    • 17. Exercise Create Tables Employees and Jobs

      9:18
    • 18. Column Properties

      4:56
    • 19. Primary Keys

      7:34
    • 20. Foreign Keys

      9:14
    • 21. Exercise Edit Country Column in Employees Table

      1:53
    • 22. Execrise Create Table Previous Experience

      5:57
    • 23. Create Table Using Import Wizard

      2:49
    • 24. Import and Export Data

      11:53
    • 25. Exercise Primary and Foreign Keys for Status Table

      2:04
    • 26. Entity Relationship Diagram (ERD)

      4:49
    • 27. Select Statement

      5:47
    • 28. Exercise Select Data From AdventureWorks

      2:37
    • 29. Where Clause

      15:00
    • 30. Exercise Select Data From AdventureWorks Based on Conditions

      1:55
    • 31. Order By

      4:00
    • 32. Create Table From Existing Table

      5:48
    • 33. Exercise Create Table From Another Table

      2:12
    • 34. And, Or, Not, Is Null

      9:43
    • 35. Exercise And, Or, Not, Is Null

      3:13
    • 36. Like and Wildcards

      5:15
    • 37. Exercise Like and Wildcards

      3:48
    • 38. Advanced Like and wildcards

      3:44
    • 39. In

      3:45
    • 40. Between

      3:17
    • 41. Exercise Between and In

      3:44
    • 42. Distinct

      3:27
    • 43. Percent

      1:46
    • 44. Change The Rule of 1000

      2:41
    • 45. Union Operator

      6:21
    • 46. Exercise Union Operator

      4:48
    • 47. Intersect and Except

      3:37
    • 48. CASE Statement

      9:46
    • 49. Exercise Case Statement

      3:59
    • 50. Exercise Fill PreviousExpereince Table with Data

      1:41
    • 51. Subquery

      5:44
    • 52. Exercise Subquery

      3:48
    • 53. Inner Join

      10:18
    • 54. More Inner Joins

      2:14
    • 55. Multiple Inner Joins

      5:52
    • 56. Exercise Joining Cities and Countries

      3:07
    • 57. Exercise No of Cities in Each Country

      3:04
    • 58. Create a View using SQL Script

      3:59
    • 59. Modifying The Structure of a View

      2:03
    • 60. Create a View using Management Studio

      5:47
16 students are watching this class

About This Class

Welcome to the Complete SQL Server For Beginners Course, The ultimate practical guide to master SQL Server for developers.

This course will start with you from zero level experience in SQL databases, starting to level up as we are going through the course.

This course will guide you how to install SQL Server on a local machine step by step and even how to create your own cloud databases on the most required cloud services providers in the market...AWS (Amazon Web Services) and Microsoft Azure.

You will learn what is meant by concepts like DBaaS and Amazon Relational Database Service (RDS)

After you learn the basic concepts of the SQL and relational databases and how to install it on a machine and on the cloud, now it will be about time to dive deeper into the world of SQL databases and learn :

  • How create databases and schemas.

  • How create tables and views.

  • Understand all the big concepts like DML, DDL, T-SQL and DBaaS.

  • You will learn how to create users to access your database based on specific permissions you gave them.

  • How to create different constraints.

  • Create primary keys, foreign keys and composite keys.

  • How to export and import a bulk of data in and out of your database.

  • How to retrieve data from your database using select statement.

  •  How to filter data using where clause.

  • How to use aggregate functions (Sum, Min, Max..etc).

  • Learn how to use group by and having with aggregate functions.

  • How to use case statement.

  • How to use the keywords like between, in, distinct and percent.

  • Understand how to create subqueries.

  • How to manipulate data using Insert, update, delete statements.

  • How to use joins to link two tables or more together.

  • How to design complex database relations.

  • How to create different stored procedures to perform different jobs.

  • You will learn how to use string functions such as replace, substring, CharIndex and more.

  • How to use the powerful date functions in SQL server such as DateAdd, DateDiff and more.

  • You will learn how to use the numeric functions such as round, ceiling, floor and more.

You will be designing a small human resources database from scratch, and we will also work with the  AdventureWorks database. AdventureWorks Database is a Microsoft product sample. The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles. You will learn how to install AdventureWorks database onto your database server and we are going to perform different sql queries and statements on both HumanResources and AdventureWorks.

You will learn how to increase your productivity with relational databases using joins and views, you will be creating inner joins, left outer joins and right outer joins.

You will have the chances to create joins in two different ways, first using SQL script and you will learn how to do this step by step, and visually using the management studio features which allows you to create joins much faster and easier.

With over 30 real world exercises to help you practice and learn more about almost each major topic in the course, I am going to solve all of the exercises with you while giving you some time to try to solve it on your own.

Give your CV a huge boost by mastering the SQL server with the Complete SQL Server for Beginners course, do not hesitate joining this class.

Transcripts

1. Introduction to The Course: so to present what we're going through in the course briefly, where they're going and details of each section. Let's start with the first chapter on the first chapter. We're going to discuss the concept of a relational database on the sequel server. Andi. Also, the cloud databases on this will include installing the sequel server on the local machine and creating sequel server instances on the AWS Andi Microsoft Hazard. Then we will move to how to great databases on users and give the user symptom permissions on syndications on a specific databases moving to the search section, we're going to discuss about the most important concepts off the relation of the debates, which is the tables primary keys on foreign. This is how the relations are being made, the relation between the different tables we're going to discuss about the constraints and actually primary keys on foreign keys are kind off constraint and then all see how to import data into our database. Moving to the next section, we're going to see how to retrieve or select or using this licked statement, select statements at the most common used statement and sequel server. Then we'll see how to filter, using the wear clothes and ordering our results using the key word order. By then, we will see how to use other keywords, like the lying and the oil chords in between. On more then we'll see the more advantage select statements like the union. How to Comme Pine If I can say this compliant. Two different where is into one very how to use the interest, like how to use the cases statement and using the amazing and very powerful sub queries moving to the next sexual going to discuss about the aggregate functions have a good function of very powerful features. In Sequel Server. You're going to see this. Why yourself? We are going to discuss the minimum maximum. Some count on average functions available, and we're going to see lots of examples. Something. We're going to see how to use a group by two group records together. Wouldn't they are used with the aggregate functions on. Also, we will be using having and we'll see what is the difference between the having keyword on the wear and how to use both of them appropriately. Then moving to the data manipulation section, we're going to see how to use the famous insert Delete update statements How toe Add Rikers , Internet Ways How to lead them, how to update existing records, then moving to the Built and string functions and sequel server. We're going to see how to use the upward to convert columns or data in the columns to on Upper Case and lower to convert them to lower case and even reverse them. Then we're going to more advancing, built in function like the relays. Some mystery in concatenation and even more advances function like the character Index Cola's under the Kate Bulldozers. Other functions that we can use it Sequel server regarding the date on the Mass I get Day to get Today the date out are part of the date to some colon that I have off time date. Of course, I'm going to see also the difference to subtract two dates on, get the representation name off the date, bark off the time using the date name and some of the mass function like the round ceiling floor, and will be generating random numbers using Amazon medical functions built in sequel server finally would be discussing about one of the most important concept which is the joints, views and stored procedures. We're going to see what is a joint 100 rejoins and different times of Jordan going to discover the most common. Once the inner Jain left out of Giant right out to join, we're going to see how to create views. Waas using SQL script on visually using three management studio. And then we will be discussing the stored procedure. But it's the benefits of using stored procedures, how to create it, how to a sign parameters or great stored procedures was prayers. So Zant's is a bland. That's the road we're going through that you're interested. Let's go add on star. 2. What is Relational Databases and SQL Server: Let's start with what is religion of the debates is on what Sequels our religion. A little bees is a collection of data. Items was redefined relationships between them. These items are organized and set of people who is colon and rose. Tables are used to hold information about the object to be represented and the database. What does this mean? Let's take a look at this example in here. Assume that we haven't really cold more. It, for example, have the first name, last name, birthday. Like all of us, a building that he's living in ST City, a job company and the higher date, for example. We can add, of course, items disease information about more. The job itself that marking is working has information the trump name, the minimum salary, the maximum salary on many other elements we can add. If you think of it, there is some kind of relation with all of this information. We can say that there is a group of data or a table. Full information about more containing the first name, last name birthday, and we can add other elements like the email, phone number, nationality and many others. We can have enough people for the addresses off more, which will record all the addresses that more lived in the building number Street the city . We can also have another table for the job company on the Hard eight, and we can link this re cabled with the I D number. The I D number can be regenerated number or the Social Security number. For example, Goolsby linking all of this information about Mark or any other person or in pleat in my company. We can also have another table that contained the data off the job itself in here. So this people would contain information about the job, maybe the job name, the minimum salary, the maximum salary requirements for the job. So what is best, really? Yes, you or structured query language is the primary interface used to communicate with relational databases. SQL is the standard language for relational database management systems, such as most of the sequel sequel server, My Scale, Oracle and Maria TV. We have some SQL statements. SQL statements are used to before on tasks, as we will see as he update the data on the database or retrieve data from the database statements like the evades, lack, bleed, unserved growth create and many others, as we will see. So I hope this is a brief introduction about religion on the basis on physical. Next, we're going to see how can we install the SQL Server. See you in the next lecture. 3. Cloud Databases: we'll come back and this lecture, we're going to discuss the concept off cloud databases. If you are familiar with the cloud computing, you can go ahead and skip this lecture. So what is a cloud database clouded? Obey simply, is that the base service held and access through a cloud platform service many of the same functions after additional database, there's two times over the clarity. The bases virtual machine image on D. B. A s stands for database as service. I'm going to explain. What is this? The virtual machine? If you're familiar with the virtual machine concept, cloud platforms allow users to use virtual machine instances, and you can run a database on such virtual machines so you can create your own virtual machine. With any operating system, we can install the windows and lyrics or any operating systems that you choose any car and stole your database on it. Users can either upload their own machine images database of stolen it. If you haven't image already, that was you on your local machine. You can upload it on the virtual machine on the cloud where you can use already made machine images that already include on optimize the installation off the database. That's the first time the second time the D. B A s and this one we will be using in this course was database. As a service model application owners don't have to list hole and maintain the database and houses instead that the best service provider takes responsibility for installing maintaining the database on the application. Owners are charged according to the usage of the service. So this hear, say, for the time this model here say, for the time doing a stole maintained and the configure All of this is steps are saved for us. You can directly use your database using the database at the service Brothers on you. Virtual machine image benefits of using a cloud Databases Ease of access Users get access cloud with basis from virtually anywhere using a vendor's, A B I or interface scalability. Cloud databases kept X panzer storage capacities on run time coming. They changing needs, and this is very powerful as human that you have a real server and it's told on your data center. For example, if the users increased, you'll need to buy a new server by a new license and stole and configure all of the silver , and this will take a lot of time. What if the uses dropped? Would you sell the servers it love? Very flexible. But was clarity the bases? It's very flexible and very scale, um, disaster recovery. In the event of a nature disaster, equipment failure or par outage, they does skeptic. You're through backups on removed server, and this is very safer here. It's much, much cheaper. Clark databases uses remote resources, savings organizations, the cost of server license and other equipment. Automatic software updates. You don't need to install any update. Service providers take care of them for you. And really, I would regulars or store updates, including security and evade. So you don't have to worry about wasting time maintaining the system yourself, leaving you free to focus on the sing staff matter like growing your business. What are the Bow Beeler Cloud Computing service providers? It is a lot of service providers out of there, but the really big one of the familiar one, which the Amazon Web services AWS it's very popular and very powerful, and the Microsoft as well. On the Google cloud platform, these are the three Giants to next. We're going to see how to use some of the cloud sources. See you in the next lecture. 4. Install SQL Server: OK, now it's time to see how doing has told a secret service. So you can go ahead directly to zero in here and shoes, which virgin that you go into the load and I'm going to see this in a bet or you conceivably go to Google and time. That's gold's over. No, look is And it will be the first link. This book that is so we have three different editions. This one here in the Enterprise edition, the free trial evaluation. And this one is banks, or they're going to use it. We're going to use the developer edition after is also the express edition and it's also free. But for the scores were guard use the developer edition. Go ahead and press. They will look down. It will no little falling here. Old sequel Server 2017 Access E I Dash, They've gotta have on click it. Yes. Now we have three different options. We have the basic. The basic will not let us choose almost anything because the armed they will load media Well, let us choose almost everything. The only difference between the custom handed the load media. If the custom will be they're letting everything from the Internet while it will load. Media will be available on your local machine if you would like to copy it into one of the machine or server. So I'm going to choose no load media, Bronx shoes, English on the eyes on the cab. If you know how to deal with eyes, are you comparing it on a DVD or you can extract it, but I'm going to use you can for so that the eggs er available immediately after the double load is finished. So go ahead and load. It will start a load. And of course, from here you're gonna choose where you would like to to load it. So go ahead and first alert and let's continue off. Load is finished. Okay, hold that. It was fast. Now I have the file in here sequel server themselves and 17 the Ash X 64. So go ahead on click it. It's going to extract some files. Purity is I'm going to extract it in the same directory. Here it is, as you can see, Manfred until it's finished. The installation sometimes take sometimes, and it's somehow boring, but we're going to see later how to restore in the cloud and it would be much faster. Here it is. Now we have the sequel, Server 2000 seen in installation. So go ahead and choose. Installation. Use equals reverse down alone and installation. We'll have a window here. Here it is. What? Using the developer tradition Next. Accept the license terms. Thanks. Are you gonna go ahead and choose? Take two of data. Another really important for him. It's only giving me a warning about the Windows firewall, and that's totally fine. There is no problem now in here. That's difference. Mean the basic and the costume. Now we have these options. I can go ahead and choose any option that I would like to install with the database. So now I would be using the database engine service. I can, of course, any time come and and stole any off this services in here. So what's next? After your true Z database injured service Now in here, you can leave it to the fold in distance. This is the innocents. Name off the sequel server by default. It's going to be called M s for Microsoft sequel server. You can go ahead and choose any name that you like or leave it other default. I'm going to choose, for example, my name. That's Gil Server, but I havent respects. Okay, now this window. Don't mind yourself with all of this options. Grand Empress. Next for now and in here, you can choose the syndication, and we're going to discuss the decertification in detail later, which is syndication or Mexico. Mexico is going to use the Windows authentication on the sequel servers Indication. Go ahead and choose it. And now this and the password and confirm password textbooks is are highlighted. I don't need us to insert or Arab the password for the sequel Server System Administrator, which is the estate. It's a is every famous user in the sequel server database, and we need to give it a name. We can, of course, come and I have any administrator later, but this is the default so I can come here and at the password and this one. This was fine. Sequel to River Administrator. If you are inside a domain or there is many users using this machine, you can go ahead and add anyone from here. Can you choose the name and add it. Or you can simply add the current user, which is going to add my user. Here it is. Who's next? Now? Just revising with me that I'm going to do this or perform this installation. I'm going to the Yes, go ahead and install it on. That's it. For now. Relax until the installation is done. Okay? Now the installation is done, As you can see here, installed, successful on you can go have now. Um, close, but we're not done yet. D database engine is in its told, but we need to install one more component, which the management studio that will allow us to graphically manipulate the sequel server , and we're going to do this next. 5. Management Studio: okay, as I told you in the previous video that we need the management studio, which is a tool from Microsoft that will allow us to a graphically do everything, almost with a sequel server database engine. So let's go ahead and see. How can we get this monuments to here from the sequel server installations Under From the installation, Choose installed Secret server management tools. Click it when you click it. It will take you directly to this euro. Here you can find the link for the management studio. Here it is. The load sequel Server Management's did you? 17.5. Click it. It will start a learned When it's finished. You will have this file in here. It's s M s set up. So go ahead and click it, Chris. Yes, on. Here it is. Let's go ahead on person Stole Pretty straightforward. There is do a lot of options with the management studio loading back. Choose. Please wait. Okay, here it is. It's in stolen right now. Here. The overall progress. Okay, Elbows a video in here on. I will be back when it's done. Okay, Now the installation is complete. Go ahead and include this window down from the start menu. You can come in here and shoes. Microsoft Sequel Server management studio. Go ahead, open it. It's going to take some time Well, before the first time because it's forming the basic settings. That was a video in here. Ankle. It's finished. It is. Now. The settings are complete on. Now we can connect to our database. So here is the This is the computer name, actually. Well, the server name. You can replace it with the I B if you want, or you believe it with the name. And then here here is in distance. If you remember from Dennis Elation, we have my name or your name. If you entered your name Sequels over there. Is that the name of the Sequels ever? If you have other options going to choose from here, cross for more database engines out is there is others of these engines. You're gonna choose it from here. It will appear. Are you gonna choose it? I'm here Is a syndication weaken? A. True with the windows indication, all these sequel server authentication when this medication will be validating my users uses that I'm Look then with the windows in here and this user is it can be any other user . If you have many users on this machine or it can be a sequel server and this will be a user name and that's what I'm going to explore this later I choose. Do you winded syndication? No, it is connecting. Here it is. It's done. And you can see Here is all the options. We have the databases. We actually, right now, I don't have any databases that we will be creating a lot of the debaters hand tables. Here is the security. We can go ahead and add users from here. Remove users, set permissions or remove permissions. There is many other things in here to create another base. For example, we can create a database using a code, and we'll see this were using E. Jui off the management studio in here so I can go ahead and said the database name maybe this unjust database. Call it this database. I don't hear it is, As you can see, here is a test database overnight. We have tables. I Gramm's views programmable the inside the program. Unless there is a start procedures, we're going to see most of this feature is actually in the upcoming lectures. If you would like to remove this that a vase you can go ahead and right click on pris lead . Um, click OK on its legal. So that's it for now. This is very basic things off the management studio. In the coming lectures regard, see more and more off the management studio Handy Sequel Server Database. See you in the next lectures. 6. SQL Server Database on AWS: we'll come back now. Let's see. How can we create a sequel server database on Amazon Web services over the aid of yes, goes this euro a deadly as dot amazon dot com It will take you to this form. Here. You can go ahead and click. Create three account again, as we have seen with the marks off hazard to ask you a couple of questions. Email address with a basket for the account last year about the visa MasterCard Again. It's out of the undergone steel. You It will totally charge you for $1 to be able to use the services off the AWS. So how would you create your account on the complete registration on Differ that will be able to create You ate every s cloud database. Okay. After you finish frustration, we should have something like this and you restrained right now. This consul here, As you can see, there is a lot of troops is in the eight ugliest. Really, a lot of services don't. It would be interested in its under the database. Get or you're in here. We're going to choose relation of the base service and here I will be able to create an Aniston's for my sequel server. So I'm going to choose this option here. Lunch the be in distance and from here I have six different options. That was an Aurora. My skill really be. Most agree Sequel Oracle Handle On will be interested in. Which is Microsoft's seek. Walter In here I have the different additions on it. Abusing and the express addition. Go ahead on the click next in here to ask you about some detailed about that the base itself, the license and the Virgin. I'm going to change it with the last one. As you can see here, this blue boxes killing that's a free here. The AWS is different than the Microsoft. They will allow you to use their services for will. Months isn't taking anything from you the only condition that you will have to use a specific instance. And in our case, you would be using this instance BB the T to Michael. This is an abbreviation for some conflagration off the service that will be using What does this mean? So the T two is If we chicken here, it's giving you Onley one CPU on one getting friend us really few conflagration devices or sea views and grams, but that's what's available for the free one. If you like to increase this, you're going to pay for this. So if you click in here only enable options illegals or the RDS three usage tear, it will only show you only the option that's available for free. And that's what we want. So go ahead on continues with Life to choose the Time Zone 20 the 20 kegs for the storage for the database And here, as you can see, this is, ah, database. Aniston's identify specifying gains. It is unique for all the DP innocents on by your AWS account in the current region. Go ahead on choose maybe my database. Go ahead and choose a user named this user name you will use to look into your database later. So please remember it. Gonna choose my name when entries yours or any other one. Now we go go ahead and click Next. I'll leave all of this. And if the public X ability is no, Li said it too. Yes, and leave almost everything as it is everything. Just leave it on the Becca vacancies ears on option in here. If you would like to choose the back of specified, the number of day that the Amazon already asked should retain automatic backups off this db Aniston's found that that again, there is nothing more to you can create a whole course about the A degree as. But we're not doing this right now. Now the database Aniston's is being created. This will take a couple of minutes, so relax until it's created. I can come in here and take it any time from in here for Go back. There is a DB instance I have from 40. This is a whole the one that I have. It's available. As you can see, this is my new one, the status of it. It is creating a go inside of it. You can see it's still creating. It's not available yet, and here's some options that I can see the CEB utilization, the connections of free storage space. A lot of informations re useful. Actually, I do like the heat of the hissed, and here is all the information, and here is the name will have you in here later so that we can access it from outside of the eight of the s right now. Well below the video. Until the instances created successfully on we will be back. Okay, Now the anastomosis created successfully on is available. As you can see here now, we can access since the debates in distance from any where using this name. If you go down in here, you can see the jury's and name. You're at the end point. This long name. Go ahead and take it. Copy Can use many months. Did you go ahead and open it on? Use the server name. This is a server name. Go ahead. And at the user name and passport that you've used in recreating the distance could make on an error. If you have this. Hair of the certificate of chain was issued Bangles already. That is not trusted. Go ahead and open the options and choose trust server. Sort of. I connect. Hold it could neck correctly. Yes. So look, it's low, but it's okay, you know, it is He was over. Need to be asked at the base. As you can see, there is no databases like coming here on create new database. This is a debate. Is this one is already in the 80 s. It contains some information about the A TV s the rd Yes, Hantman until it's finished. Here it is. Whatever. I haven't read me. Me and please database we haven't like Okay, it's going to be created on They can use it normally like database on my local machine coming here. Of course. I don't see the If I go back to instance if I have many innocents, they will be displayed normally. Have another one called STB. Part of them are available. Here is the users you of the CPU sports. I can come in here any time. Andi The snapshot. I would like to do something and go back restored to a time to a point in time. Become Modifies all of the settings. I can't stop it. I can report it or I can even delete three flexible and very powerful to use of features with the aid of Yes, And here with the database created. I've been coming here on, of course, create tables or views. It's empty right now. So that's it for the AWS RTs service. Have you enjoyed it to your NZ next Natures 7. SQL Server Database on Microsoft Azure: will come back. Now let's see how to create a sequel server database, own Microsoft other The cloud database services off my results. So go ahead to this year l as a redox Microsoft dot com, you will be directed. It is your Ellen here. Go ahead and press start tree again. Press again Started free. I'm just going to ask you a very simple questions like your email, your user name password, the mobile number, the region that you're from there. It'll ask you about your visa core, Al. It will charge you on Lee for $1. You will not be charged anything, and you will have as you can see in here, $200 credit for 30 days. But this $200 on lee to use on the cloud off the max off the azar cloud in here to get services, make servers make as databases on the servers have to use it anywhere else. And Southerland this anything you will be doing, you will be charged as you're going, and we're not going to do anything. So don't worry. Hand. It's Mike softly and they're going to steal you. Go ahead, provide all of this information. Andi don't work. Once you're done, we're going to go to the other portal and see how can we create a sequel server that abates own Microsoft, Has it. Okay, I hope it's easy steps. Let's go ahead. Now do this. Your el portal dot as o dot com You should be seeing something like this. Maybe was held this three resources in here? I was playing with the databases and servers. So this is the portal that you will be managing your servers and databases on Lee Cloud from here so you can go ahead and change. Maybe the seam you can truth to be white flag, But this color, I'm just leaving it to the fold. You can make it, by contrast, team gallantly with. If you would like to change it and you can even this is a dashboard. And here you can go ahead and edit it. You can move this in here can make this larger. You go ahead, play with it. As you done now. How can we create he get a base on Lee Clark on the list in here. You can find some of these services to see all the services off the Microsoft other. You can click on all services, and here is older service on the heart. Allah, I don't think that anyone can be familiar with all of this services again. Issues on Lee. Some services to work with. Ask for our course. Right now we're going to choose sequel databases. This will take you to hear this. The debates I have right now I'm going to read with one like this. So go ahead. And here and And the sequel to the base Now who last me some questions. What did the database name? I'm gonna have a cool it. Maybe persons on this is the subscription. It's the free crow. Choose it on the resource ing wrote the Resources group simply is as a description in here a collection of three sources that chair the same life cycle. Permissions and policies were not going to worry about this. We can go ahead and create a new one. Call it just my the source. We're on the sourcing here. I can create a blank that the base was no tables, no procedures, no use nothing or you can use He did have a schooled adventure, works adventure works. Is that the base where Microsoft that has some ready made table that you can see some data there. Just a dummy. That'll be to start ways. I'm going to choose it. Now it's time to configure these server press this Lincoln here. I'm going to create a new server. Now the server name. Go ahead and use a valid name. And this name will be a bended to this. So what Avery typing here would be, for example, test the database dot windows dot net. Gonna choose Maybe my just database. It's No, it's not available. Go ahead and have your name. Maybe I would just database on this one is available. Go ahead and shoot the server admin. Look, And this will be the you that you will use toe looking into your database. So I'm gonna choose maybe my name. This is something that's up to you can choose any I've been named that you would like to go ahead and choose a password. Confirmed the vast word Andrew, the location that the server will be installed. You can go ahead and choose for all of this year. I'm gonna leave it to west. Your Chris Licht. No, Leave this. You won't use equal elastic bull. Doesn't matter right now. The pricing tier. Go ahead and click it. Now we will be introduced to something called the the T U. If you go in here to the description off the detail you of the racks off reduced House War database transaction units. What is either the base transaction unit for a single other sequel? The debates that specific performance level within service DMX after guarantees this certain level off resource off the database and providing a predictable level of performance this amount of resource calculated at the number of dead of a strange actions on blended measure of the CPU memory Ondimba town output was that would all of Hissene here simply added to you as a unit off measurement for the service tear and the performance? I don't worry about the trick. Now go ahead and just two basic one on as you continue near five. Did you use again if you go to this link? So I'm going to tell you all about the need to use and how they are calculated. If you drive to we developed it now, click, apply and one thing we go, go ahead. And once again, it's doing that starting from and there is no number in here, as you can see. Now we have it. It's five years did this Five years needs its from the $200 that you have in your account. So don't worry. You will be charging, saying only the $1 for the registration Glinka Blanken. Now, go ahead, arm Chris, create. It's going to be a little bit to create our database on the server. As you can see, it will show me a boxing here telling me that it is deploying. You hear the notifications now? One of this one. Here it is. Employment and progress. That was a video here until the deployment is finished. Okay, now the deployment succeeded on. We have our databases on Lee Cloud. Now click to go to resource. Quote this modification in here and now we have our server. Here it is. Amethyst database. The database, The window. Do not. Of course, you will have a different name. That's the on your choice. Now, if I come in here to the query editor on the left. Chris Logan. It's telling me to look in. Go ahead. And years now we're using the name from the creation or the insulation off the database on the cloud. I hope that you remember the password. I have the other hunters. Okay. Booking in and Hammond on now. Here it is. We have some tables. If you remember, we choose to have the adventure. Works that a base. Now we have some people in here customer for like, um, there is some data in here as you can see how I can come in here. And don't worry about all of the stable's out of years. We're going to discuss this in detail. Um, even this cold I'm writing in here. Don't worry about it. It's like Asterix from sales lt no customer on and win It found it would retrieve some data from the stable. Here it is. It's executing on here. Is some Dina very interesting and very amazing that we have a database on the cloud that is accessible from any Where? But what if I wanted to go ahead and connect? The management told you? Excuse me from here management studio that even is told to this cloud in here to manage it better. So go ahead and open the management studio. Let's go ahead and check something before you go. So did you. Over you? Yes. Present. Very. Go ahead in here on monitor all the performance or few database. I would like to see the status. It's a lineup where it is located. The free trial Here it is going ahead to this and control you that you have a 200 years D remaining in this number on de ni Usage for your database holds over only cloud. It will be displayed in here. So let's go back, Persons. No. How can I connect this database? I need the name. Of course. This is the server name. So let's go ahead and get the name. If I get the name right now, it's not going to work. I try to connect it. Interest me on giving any valid password only. Here it is. Your ivy didn't have access to the server. So how can I give an axis? Don't want Heidi Andrews in here. So you see this option in here? Sit. Server five world present. Go ahead and make sure that allow access to other services. Is on and simply Blake at client. I mean and for safe, it will add your ivy address. We'll make an exception for RV. Address on. Now. You'll be connecting to the database. Go ahead and Chris collect. Ah, Now we're connecting to the cloud database that we've created. Go ahead to the base. It depends on the speed, of course. Dependence on the connection that you have right now. Here, the persons on here, the tables from the adventure work. You should see them in a bit again. It's depending on the Internet connection. Right now. Here it is. Here is the customer that have selected from. And even if I had it in you table creating your table and again, don't worry about this. We're going to discuss in detail about tables and views at primary keys. Don't worry about this. We're just seeing how to in its told the Secret Service cells and helping solid on the club . So I'm going to create This is just he tests. I'm gonna give it a empty time. Go ahead. And Chris, on the this king here it's available. It is safe. It medical this taste even things that don't worry about the naming convention right now. This is old yester test when it's done, when we go back to the cloud, Although the chortled off the other, we can see that we have a database or a new table in order to base cold. Just if we refresh this narrow, you should see it in here. Here it is this taken. But if you go back over the that amazed query editor again looking okay. Choose sequel, sir. Syndication, do the name and the password. And if we open the people in here, we should see that we have a new table. Cold testicle. Here it is. Very amazing. Yes. So that would be it for the creating sequel server databases on Microsoft. As I see you in the next pictures. 8. Create Your First Database: we'll come back. And this lecture, we're going to create you first Sequel, server database. Let's go ahead and do this. So I have the management studio opened in here. If you don't have the window toe Logan, or to access the database, go ahead. And the click this icon going to display this window in here? If you don't have the server name ready, you can go ahead and open this list. Click on press for more and from the database engine. Expound it on. You should find your innocents name in here. Click. It wonders. Okay? And you should have it. We have to type of authentication. And when going to choose between the windows and sequel server syndication later. But right now, leave it as windows and you should connect. Was your windows user normally? Or you can use Ze s a user's. We've created password. When we were installing this, I'm going to use the window. This implication mode right now on Chris Golic. Here it is. It's connected successfully. If I opened database. As you can see, there is no data bases in here. This is assistant of the base. It's the default database. Is we're going to miss with this. We need to create owned the debate so I can come in here and right click on choose new database. Of course, there is a code to do this, but I'm not going to use. It is a reason alternative to do with. I'm going to use alternative because it's match faster, But we're going to encounter some codes letter. So it's asking about the database thing I can go ahead on. For example, I'm going to create database, maybe to contain some information about human resource is maybe it's a database for human resource is. So as I'm typing in here, the name in here is being out of there is too records in here. As you can see it, you see human sources, it's being absent. What is this? Two files in here. Let me expound it in here. There's two funds Julian here, dears, Toward in this location, this two foils are called the MDF oil and the LDF file. The 1st 1 is a storing that data rates of the table, the views, the data itself. This is the primary fine. The second fathers being saved is the look The log for monitoring. So who's looked in my database? Whose deleted? Who has created? So there is two files on we can see the Earth's toward in here. This is the passage here. Stored them. I had, of course. Come on. A change where they are stored. I can create my own. This is that the full for the box of sequel server? I'm going to create it. But before I graded, I'd like to check this options in here we can select the collegian. The collision actually is set of rules for how the data is sorted and compared. You know that there is some language is required specific characters like a Chinese, Arabic or Japanese. So I'm you going to choose one of these collisions on your preferences. But right now I'm going to leave it as of the fold. One of the important options in here is the compatibility level. Right now this database is going toe work on Lee with sequel Server 2017 which is 140. This is the number for the database. If I would like to make my database compatible was earlier or older Virgins off sequel server. I'm going to choose from the Virgin's available for me in here. Maybe 2016 or 15 or 12 or even eight. But no earlier is. And 2000 and eight, there is earlier virgins, the 5 4000 and even seven. But I'm not going to choose its not allowing me to choose before 2008 and think that really good, because it's about 10 years old, so I'm going to leave it at 2017. Right now, there is a bunch of options in here, but I'm not going to play with them. So if I click, OK, right now, it will be creating two files in here. So this is the default databases that in its told when I press Okay, you do a lot of this. Here it is. Now I have the human resource. Is mustard avoidable? The MDF on the look database file. Here it is. They are created right now. As you can see on these, I go to the management studio. I have my database created Very, very nice. Very smooth. If I open it, I have the database die grounds. So like to design a diagram about May database. It's asking me. He wants to create them regard to exclude this in a bet loving this. Listen, but we're going to see them. We have the tables right now. It's an empty database. There is nothing inside of this database on. We're going to create tables. Worry there is of use. External resource is program abilities storage hound security. Here is the users that have access for the database. On in the next pictures, we're going to see how to create users and gives him a specific access. Maybe some user can read. Maybe some users can ride. Maybe you have a full permission to do almost everything. So this is how you created at the base. We can go ahead, of course, on right click See, Check the options he and check the options in here on. As you can see, the properties giveaway is giving me the properties. Eric created the database. Here is he filed a recreated the owner. It's telling me that this here is my A. I be on my computer name or the server name right now. It's called the of course Is VM on my name is the owner defaulting Ruby really nice option that I can work with them. Here is the collegian. I can't change it on the compatibility. Also, I can change it. I'd like to show you something interesting if I go do this file and try to delete it. Find the in the F fire. That's containing the data itself. I'm going to permanently delete it. What Cheek? What's going to happen is like, refreshed. This something is happening. You cannot move it. You cannot delete it. It's some kind of protection. If I wanted to calling them even, just call me them. As you can see, this action cannot be completed because the foil is being opened. Even if I clothes a collection in here, it's not going to be copied or deleted. It's like copy does here, let going Anywhere as you can see. Why is this happening? Good. This is as I told you, some kind of protection for your database. You cannot lead it by mistake. You cannot copy it. You gonna do anything? So how can I manage this? Maybe I would like to copy this database from one computer of one server. Who? Leather. How can I do that? There is a plenty of ways to do this, and I'm going to a lot, show you a lot of ways to do this right now. Let's take a look at this. I can come in here from task struggling, of course. Hand from tasks. I can think it off line when you take the flying. The database is off line. It's not accessible. You can perform some tasks I can detach it on. All of this here can be done with a coat, but I am going to show you the wizard right now because it's faster. So if I detach at this database right now, everyone sees there is one color me that I'm concerned was, If some bebel or user application is connecting to my database right now, it will not be dropped or detached. So you need to click this in here to drop any connection to database on. Now. We don't have the database, but it's not deleted. It's detaching. She go to the folder in here that's here now I can copy. It can take it. Copy from here. Place it in here. Down here it is very nice. If I wanted to get it back, Maybe to the another server or to this server over this machine. I can come in here until that I would like to attach a database on its telling me Where is this That are basically lighter touch. Here it is. I'm going to click it on. Here is it's telling me that there is to file the human resources again. MDF on ldf Okay, on the data base is very That's so That's it for creating you first database. See you into next sector. 9. Create and Manage Users: okay, in this picture, we going to see how to create on manage users. So go to the management studio again on Connect. Go to the second Fuller, which is security Logan's. He didn't confine all of this. Logan's in here. We didn't create them. They were created by the fold. The user here they, of course, is dash VM. Slash is created, if you remember from the installation when we have the the current user. This here is a server or the machine name on this here that uses where it is tooling the sequel server. Andi essays. And it's told also by the fold, and we have set a password for it. I hope that your memory, if you don't remember it, we can go ahead and visited. Unsure you how in a bit, the great and you look in. We can come in here on treason. You're looking, and we have two options. Windows authentication or sequel servers. Indication where the syndication it means it's going to validate or search for the users are available on the current Virgin off wins in here or inhumane if you're in a domain controller. So if you go and search for users. There is a user called, but it's my chick name in here. It told me that there is a user in this machine name or server name hold so I can go ahead and added and given permission. But it's already added. If there is another name, you can go in and choose it. If there is no names, for example, there is no mark on this computer and hair new user. Goldmark. If a chicken is going to kill me, we didn't find any Mark. The second option is a sequel of syndication. It's not a Windows user, it's a sequel. It's I uses. It doesn't exist. It exists only on the sequel server. I can go ahead and specify its name. So, for example, if your memory created a human resource, is that a base in the previous lecture? So I'm going to create that you are human resources user. I'm gonna call it user. Will it, um, I have to specify it. Best work free. I'm going to make it 123 Just keep it very simple. Lantos, treat. There is a couple of options in here. I can leave this chicken here so user must have checked password at the change, actually. Password at the next Logan. So if I created the user right now, so if it's created in here on to connect while keeping the collection this year exists, I can connect. You die can see. And I'm going to choose sequel server syndication. I can do this or I can open another management to do. But I don't take him on to hope in another management studio. Just go ahead on the connecting at the time. We'll never won. Go ahead and change user or the look, and I'm going to choose it. You are Use one of these that we've created. We're going to truth upstream. This is the passport. Once I connect, it's telling me to change password. Remember that, actually, that they use their need to change the passport, but I'm willing to change the password. Found the Logan here. The user looked successfully on here. We have our human resources database. What do you think will happen if I'm tryingto open this database? Can you guess it? It will not open because this user doesn't have a permission. As you can see, the death of babies human resources is not accessible because these it doesn't have the sufficient permission to access this database. So how can we do this? Let's this connect and go again to our users. Es the user all the administrator user, which is armor that I logged in. Where's right now? I can come in here and scored manipulating this user in here so I can go to user mapping. As you can see, there is Lessing checked in here from this option in here. I can give it a specific permission with a specific database right now. I don't have any detonations. Only this database, the human resource, is if I added another database, it will be added in here. That's right. So if I go to database, this is just for a test. We're going to great. And you to the base. I'm gonna call it maybe best of it, just leave it as theirs. I'm going to remove it anywhere. Do you realize we have to databases If I open the each or user right now her to use mapping ? Here it is. We have the test of the base on the human database. Human resources on this amateur you before. This are before databases to if I chicken here now we're talking. So if you go down in here that the base rolled membership for human resources, what kind of permission? I would like to give it to HR. Use one. I can leave in public on with this permission. It will be able to open the database, but not do anything. Just open it. If I give it to the base owner. The DB steps for the debate database owner. Now he will be able to do anything, create objects, views, tables, have data removed data. If I give it E dent a reader who will be able on Lee to read data Data writer who will be only able to write data, Consider. So let's go ahead and check if I give it only public and again, I'm going to connect using the sequel server syndication at your underscore. Usual. Um, give it, then you password. It's connected. Database. If I opened Human resource is its opening. You see, the last time it didn't if I tried to open the test database, it's not going to open because I did not say the permission for the user to open the test. If I tried to grade a table, we're going to discuss tables and details. Don't worry, just this is a test. As you can see, it's telling me we're not. Look there on as a database owner or system administrator to be able to do this. If I pressed their K and try to keep or add Coghlan's maybe the name variable collector. Don't worry about this. We're going to discuss this in detail if I try to save yes because it's not going to let me . What if warnings can create the table because of the permission is denied, as you can see, because I didn't give it get of his honor. If I go by Andi, given the permission to go to you having again here, make it another based on, for example. So now he is the owner of the database. If I come in here and try to save this table that I wasn't able to be saved four seconds, it's safe. Ready. If I refresh it, you can find that we have table one in here on the course. I can come in here on open the design or delete it. I don't going to go through all of this right now. Now it's do it. I deleted it already. So what if they wanted to reset a password for this may be the s a password if you forget it. Or the itch War one, if you forget it or he forget it. Actually thinking, coming here on properties, I'm just go ahead and change it so I can go ahead and make this on 1212 And, as you can see here, enforce password exploration. You know, possibly is going to expire after Sometimes I can remove this chick from here on. The password will never be expired. So I can come in here and click OK on Now I try to connect. It's going to ask me for I'm going to drive the old password. It's not going to let me looking held going to choose 12 on its reset it successfully and with the same permissions I can open human resources. But I cannot open the this. If I tried to add, go back and add the test through the use of one. Were to use mapping. Go to this making public Emma going to make it database owner only. Open it. So now if I refresh, use a one going to user. One user. One is down here. We have two connections. Don't get confused if I try to open the database as usual. One. I found it in here. As you can see now, you can see better that the first connection is among which is the administrator on the second connection is the HR Use one. If I tried to open test now it's being open without any problem, because I give it thesis efficient function. So that's it for creating Logan's and users Onda sign permissions to year. It's easy and fun to you in the next picture. 10. Database Schema: Okay. Dissection. We go to explain one of the really interesting concepts of that of its world, which is that the basis loomer scheme. As a collection off database objects, this collection can be tables used for procedures or any other databases. Object. Let's go ahead and see scheme and read bullet scenario. So in here I have a database called Venture Works. I know that you don't have this database. I'm going to show you how to get this. That a base into you Sequel server in the upcoming pictures. Student worried This database is created by Microsoft for people who are learning Marcus off Sequel Server. It should seem you leave it business. If a company called Adventure works, it has some sales productions. Persons HR serve he open it in here. You can find the it's full stables in here. I didn't create all of this. It's created for us, as you can see in here, full of tables, views and many other objects in this database. Ready for us to use or learn from? So is I opened the person in here table. Go ahead, Greg, click and select a ruse, and this 1000 can change it. We're going true. How to change it? Don't worry. It contains some dummy information about people. Just are not real people. This is just a dummy information, but it's good start. I have the first name Middle Lane. Some other information in here Yes, I open, for example, the production product. Protect itself as you can see her. The name of the product, the product number. It's like a real world scenario. If I go to the sales in here was a brush chasing, maybe the currency Ray, the sales guesstimate. Let's take a look of the customer. As you can see, I see that there is a customer, I d. And I'm going to sure you how to create these tables. So don't per you will go to great a lot of these tables. I'm going to create relations because so where is this key men in all of this unit is a jury's a prefix in here. You reserve the department human resources of employees. That's not part of the mains, and human resources is my part off the name. This is important. The person in here is not part of the name. The person table that we open. The name cannot be person that person. It's not logical this year in the database of scheme and its grooving, as you can see the table that are related to the prison like the person phoned person itself, the passport email address the country read only contact. Hide had a secret. The same for the production, the same for the rachet saying So it's containing so that when you search for the information about a person, you can find it all together with about the production confined it altogether and etcetera . It's actually very helping. So how can I check that? This is really a schemer. If we right click and go to his properties you see here the columns Kema is persons. As you can see. If I go to the production, Partick should tell me that you have the schema prediction here. It is really interesting, but where the scheme and this database objects in here. OK, go to the security folder on this database, not the security fuller on this. A server itself grew and its database there is another security. There's two different securities in don't get confused. So if you opened this in here. You're going to find that there is a scheme is here is the schema that we're seeing Human resources person production. There's some others created for us. Here it is. This is a sequel server and that of a scheme is in here. But I can come and create my own. A schemer group. My data objects. So how can I do this now? Let's go back to O debates human resources. We've created it in the previous lectures. Go ahead and created. If you don't have a and I'm going to the security under the human resources database and they can see that I have a scheming also. Let's go ahead. Right. Click on Create new Schemer. I'm going to give it a name. Maybe this is going to groove the M please. So any information about the M please is going to be under Z Steam off, please. So how can I create this if I go to tables? I'm trying to create the people. Not where we still didn't explain tables were going to do this in detail the way this is just a test to how to use skim it. So I'm going to maybe. Okay, I d out. Some columns in here is going to be an M. Um, Same. Be very characters Can I said the schemer, if you take a look on the right here, I have the table name, I consider it. Maybe this is Please. Um here. The scheme. I can come in here and I can see the scheme at the lake created. Here it is. It's in keys. No one agreed. This table, I will have the same as the adventure works the name in front of the table name. Let's go ahead and create and you want and see if it will disappear. So I'm going to the human resources that a base stealers. Go ahead, red. Click your scheme and create a new one. Maybe this one would be for human or administrators. Love. Choose administrators. This scheme is going to be used to group administrators, a system administrators, for example. Go ahead back to the table. If we try to open the schemer, we should see that there is an year. What a year it is. It It wasn't the first in here administrator. So it's being added. So if I added, but I'm going to choose, please. So I'm gonna name this table. Basic eso Please go ahead. Name it. Whatever you loved, I'm going to use it right now. It's Cyprus saving here, going to be safe. If I refresh my tables, it's going to be no five year wreck. Blake. Refresh to the view in here. Maybe didn't safe. It's not safe. We refresh the table right now. Here it is. As against your Izzy schema, it's not again part of the name it is. It will please basic em. Foreign, please. This is a name off the table, So that's briefly about schemers. Andi. I think it's it's very good way to organize your database objects, and we're going to see that these objects are going to be a lot of objects. They're going to be really huge on. Sometimes you need to organize them, and this is one of the ways to organize. So do you unseen expect er 11. Exercise Create User: Okay. Time for a small exercise. I need you to go on, create a database user given permission to the database off. The human resource is Andi. I need this user. They have sufficient permissions to create the lead objects inside of the human resources database. I'm going to post a video that you can do it on. After that, I'm going to do it with your okay. I hope it's really Let's go ahead and try to do it together. Throw from the security. Opened the tab. Logan's right. Click you Logan. I'm going to name it. Maybe it's our user. Well, im going to specify that this is a sequel authentication years or I'm going to give it e password. Maybe just make it something really short. I'm going to remove the enforce password policy that I don't need to change it or the other change it the next time that he Logan's don't leave it like this. Then we go to use a mapping, and I'm going to give it e database owner permission on this database so quick. Okay, on it stuff he would just let's go ahead and try to do this could make the server. Let's collect using the new user, which is HR user. The password on Here it is. It's working. If we try to open the resources, it's working if I try to create a new table going to work. If I tried to open the adventure work, it's not going to work because I didn't give permission. This user for the adventure works at debates on that to you in the next lecture. 12. AdventureWorks Database: Okay, lets see. How can we get the Adventure Works database in its told into you Sequels, So I need you to go to this your own. Here. It's kind of long year l get HAB would come slash Microsoft slash sequel dash server dash symbols slash releases slash tag slash adventure works possibly go to the resources of this picture and in confined it in here, this leg or even simpler, you can go ahead from Google and type it. Get hop adventure works. They will learn if the second Lee. So from this link in here, we're going to choose Adventure Works 2017. Don't get confused with the adventure works DW 2017 That's a different thing. We're going to use 2017 without the D. W P W by various towns for data warehouse. So go ahead. And I love this file. I haven't really know loaded in here, so I'm going to go to the sequel server I'm going to connect on. Please make sure that you are connected as an administrator, So if you go into connect as a years or the system administrator, all the looking name from the wind is indication. Make sure that it's an administrator from the databases in here. As you can see, I don't have the adventure works right now. I am going to Klay Quite and choose restore database. This is a backup file, by the way that I'm going to use. Go ahead and choose devices. The voice click on this button Here. Click Add goat words that alerted. I have it into the database. Fuller In here. Here it is. The adventure works. 2017. I'm going to click. Okay, here is a file. Okay? And it's telling me that's what's going to be restored on all the information about the size date to stake in on here. You can find the fire that we talked about the MDF and the L E f. Remember them. Go ahead on the click. OK, on the good work. Here it is that a base adventure work to Southern 16 restored successfully. Very good job. Be open. Hit. As you can see, it displayed in here. I can have access to all the tables right now. I need you to go ahead and play with this database investigating the tables. Ah, there are really interesting options in here. Really interesting data. Just duty for us to work was so That's it about the installing Adventure works database. See you ends in X lecture. 13. Database Tables: we'll come back in this lecture. We're going to discuss database tables. But first, let's define what is the table. A relational database consists of many components, but the most important one is the table sync with as the horrid off database table. Without the idea off tabled, there is no reason for a relation of the debates to exist that the based table is where all the data into database is toward Take a look at this table in here. This is the very symbol table. We have four columns for schooling with the first name. Last name is 2nd 1 certain wanna city force mountainous country. So this is considered to be columns. And here is the actual data. So this is the wrong number one or number tool. Um, screening before on here is the actual data. Sophia Hanson is the data for the first and last name. The N. Altria is the data for the city on country and I can keep adding items like this. Let's go ahead and see how can we great such structures. So I'm going to create this imposed ways using the SQL code and using the management studio which is much easier, but I'm going to start with the I can say it harder one Then we're going to choose where you try the easy way. So right now I'm going to open a new query can go ahead and press this migraine here or on your keyboard. Click or choose control and it's going to open it or just simply coming near a click and choose new. Great. So I haven't choose yours too great. Anything in the sugo we time? Great. Like we have seen with the of course I can type it small. Just a driving it capital to how lead this for me of this is E s Kyoko. So creating here is used as we have seen is used too Great a database to create a table leisure Going see Great of your procedure. Almost anything. Then the next thing is the Devil Rays object all the component which in this case, is the table. And after this we have the name we need to give this object in a So I'm going to name it Persons, is it it for the table? It's not it. We need to specially find the columns as we agreed the whole structure off the table. So I'm going toe open currencies in here on the close them. And between the opening here on the closing, I was hard having the columns. So maybe our medical, um, person I d ending with his next colon person name, then maybe the city, maybe the country. And that's it. But that slap love, as you can see, it's giving me errors in here because the database engine needs to know what is the top of the person idea. What does the data tired that you're going to store in this column and in this cooler and in this war on in this world? And if you have 100 you need to specify the data time or the type off. The data is going to be stored in each color. That's why it's completing. And if we read the error, it's stealing me. This the definition for Colin Person I d. Must include a data type. So did hide. There is a lot of the times in SQL Server we're going to present them, but right now I can think of a person I d at the number. So I had a number. We can simply come in here and shoes and will see the error is going to become ends towns for inter number so it can store 123 Answer. So the person named Obviously this is a text. There is a lot of ways to store text and SQL Server, but we're going to choose right now my favorite one, which is Vora Chore or very little character. All they need to specially for and I'm going to explain what is? The difference is between the character, the text on the variable tractor and other data types in this field. Don't worry about this. So the text or the variable character in here needs to knew how many litter is going to be stored that committed five for 5500 vacancies. I'm going to make it 100 again. The city probably is going to be terrible character, maybe 50 on the country, wearable character and as if you're going have not, is that I need to separate each column in the cold in here with a column. So if I remove the cover is going to give me an error, remove the car. Me never But the last one you must love included coma. Because when you add a Coleman here, SQL is expecting you to add more columns on. This is going to open error. Just remove the last one because this is the end of the statement. All the end of the SQL code in here. Let's go ahead and trying to run this. That's right. Now I don't have any table that seats my data bases empty my tables is empty. Let's go and have to execute this. We simply going here to execute it or press that foot. Here it is. Command completed successfully. Really nice. If I refresh it right now, I can see that. Hey, I haven't you table. It's gold persons. If I opened the blessed signing here and open the columns I can see my columns in here Person i d person named Sidi Country And all the data times that I started find really interesting. But what if I wanted to edit this table destruction of? Maybe I would like to remove a column. Maybe I would like to remove the city. I don't want to know. The city may be the country's enough Or maybe I forget to add one. Call them, and this is probably is happening in real bullets scenarios that you will keep adding columns or items to it tables. So how can I do this? Three. Also straightforward toe older, almost anything in the database. Also, we use the key ward Alter to modify the thing or the object so I can come in here like table because I'm going to alter a table on the table names going to be persons you see it's That's your God is really easy. Not like this. He plus plus, for example, or the teacher before Java. It's really near to our daily talking, So it's like I'm giving compounds toe a robot in year older cable persons, you see. So let's go ahead. I'm trying to do this, or what I'm going to do is to have a column in here. I'm going to add a year. A purse, Colin. So at the cooler just type ad. It's very straightforward. The name of the color years purse and the time, of course. Don't forget the time right now. If I executed this, it's going to execute pulses of Don't excuse this and this and this is going to give me an error because I cannot run this again because it's going to try to great this object again . All this table and they really have it when this is going to give me an error to specify specific lines. And really, you just simply highlighted on relatives cute here, commands completed successfully. I love when I see this in year. So if I refresh this in here, I can see that I have a new column, Cold year first of time and really interesting. So it's so right now it do you make the code in here, but we'll make it bigger. Just clicking here, I have it to hundreds is that you can see American. The default is 100 that's really small for you. Just see anything so I can make it. 200 is good. I guess so Right now. What if I wanted to hold her to remove? Let's let's remove E. Coli From here. I would like to remove the City Cola. My country's enough for me. So how can I do this? That's right again. Let's go ahead. That's interesting. Lighting here, altar table that it seemed before The name of the table persons on What you're going to do is to drop what I'm going to draw biz hold'em. But the name of the column you're going to drug it's sitting, you see? It's OK, let's go ahead. I'm executed again. Unsuccessfully leads. Refresh it. Gender is no city. It's already dilated. As you can see. If I try to run this, let's try and run all of this. Give me an error because the first era is persons object. As I told you already exists, I can not just go ahead and create it again. You see, if I had lead it all this table doesn't exist, then it can go ahead and execute it. Or even I changed its name. Of course. So what else can we do? We can alter the data time off the columns in here so I can come in here, maybe increase the size of this or change it totally so I can come in here and maybe again , alter Go to alter it, able cold persons. And I'm going toe alter. Also, e colon, maybe the country column and I'm gonna make kid were able correct food doing it. As you can see, it's right now. It's only 50 right now. I'm going to change it to 200. I can go ahead and change it to any to the time. And as long as it doesn't contain data, we didn't have any data in our tables right now. This is really important because if you have maybe some text inside your table and you're going to change it from text over able character toe on em, that's not logical. You're going to lose your data. So right now it's empty so I can go ahead and change it to an end. Let's go ahead and write. It's not political, but let's see it. So here it is. It's complete successfully. Let's go ahead. I'm depreciate. That is now the country, isn't it? Let's go ahead and exist to change it to you. Maybe wonder 50 on it. It's refrigerant, and here it is. It's 200 so this is how you can control your table. There is a lot that we can use. We're going to see many other tricks and codes to control your tables using the scale, Kurt, but let's actually right now, trying to do this using the management studio, and it would be much, much easier. So what if they want to grade in you day Will. Was that using all of this code in here? Quick on tables. You table symbol of this. Here it is, sir. Right now, it's asking me about the colon name and the date of times. Let's go ahead and open properties on. They can specify on the table A Maybe our shoes, please go ahead on. Have. Maybe simply it's from of this plea. I d on the data type as against, you know, I have a list Benjamin stood use helping me and telling me which did survive. You'd like to choose. I have money and image. Dermot. Re float. December held really, really cool option in here. So I'm going to choose an end and I have certain columns. Gold allow knows making me a choice. We're going to discuss this debris. If this cooler was going to be no or not, go ahead at the simply name. I didn't choose you variable character. Don't get it was taken and choose the variable finery. That's different. As you can see, it's variable character and in here I can specify the litters or linens Now with Jews City also variable character. He just being positive. Let's go ahead to save their Just go ahead and click on the disc in here. Safer. We're fresh on here. Is all table really nice feature? Its very first. I don't have to read in cold to revive it too. This is a two ways that you can create table. There is other ways we're going to probably see them as well But this is the old classic way. You should learn how to great tables using SQL coaches that you can master it but there is the fast way and in real world scenario or in production. As for myself, all of the other developers or the debates adverse stated that I minute the created using the Wizard. They already know how to create it using the SQL code. But to save time and effort, we used the weather that management studio is giving us and leave the cable. You can't just simply coming here a quick on quick see very straightforward. Also, I can come in here and beat this table. Sir, What if I want to get the creation off a table in here. So let's go have to the adventure works database that we've attached it before so I can go to tables. How can I see the structure off? Maybe this table prisons seeing her is the columns off. Need this table in here? Person. I would like to see how this was created. I would like to see the SQL code behind this table so you can use this amazing feature and management studio. Just go ahead and press right click on. Do you can script convert anything to a script table as great to? You can choose a file, a clipboard or an Asian job. We're going to use the new query editor window. Um, you Some seconds we should have. Here it is. We should have the whole creation or the SQL cold for this table. Here it is. Great table. This is the schemer that we've talked about. We didn't create scheme and all code but your work self created schema for the table. Here we have all the cole looms The times you can include it in this races in here. If you have spaces, you can go ahead on, and probably it's giving you an error. But if you included in here, this is a little advantage. We're going to discuss about this. Just leave it for now. Don't worry, Africans, here is the columns. All the times on there is some kind of strange we're going to discuss gonna strain snakes. So this is how you can get the structure of any table. This is a lot, of course, but we can get it even for our Table two. If I have a table in here, where is our cable? Here it is. I can Maybe if someone else has created it, we'll just go ahead. Right? Click script able as create, too. New query editor Window on here is alteration. As you can see, you tabled creations that we've created really interesting. So that's it about table creation. So now to you in the next picture 14. Data Types: Welcome back. Everyone in this sector we're going to see more without data types how to use other options in the end, on variable characters on how to fill our table with some data. So here I have my persons able, they have the person i d person named country on year purse like tow it one of these columns on ad than you want. I'm going to choose right click on the table design going to do with using the management studio. So here is my table. I'm going to add maybe salary of this simply on for the celery. I'm going to go for a date, a time. All this it will allow me to enter. It takes two information that here, the 1st 1 in the lens of the number will choose 10. It's quiet, clung on and the other number is the members off The point choose to on may be on the column is and service. If this person is in service in the company or not, is still he working or not? To this field in here? Oh Colon will be answered with Onley. Yes, Owner Trooper Falls hung the appropriate dated I for it is cold, which carries only one or 20 false. Okay, I'm going to change the year purse. Not too. And I'm going to call it a year or did off purse. Not your first the whole day that they months and years only the year and I'm going to change it from an end to date. But director no. So right now I'm going to safe. It's not going to give me an era right now. If I save it, no error have been displayed. If I refresh it out of the two columns, is that a problem? It is, as you can see, the celery. It's just a little louder and service on its So what did the deal in here? If I change in this too, I'm gonna leave with the same name so that you know that it's not a freak or something. So I'm going changes as a date on defy. Try to safe again. It's not going to let me say a fetus. Saving changes is not permitted. This is already full. The option is closed or not enable Teoh for us at the first. We need to open it. If he going to main commodification, all altering something that requires recreation of the cable. So this is going to be recreated. Here it is. It's cold in here. This is already knew. It's going to be an added, but by changing mission meant studio is going to alarm. So I need to go to tools to remove this option. I don't like it. Go all the way to designer in here. You can see here is the option. Prevent saving changes that required table recreation. Just remove it so that good of option. So let's go ahead on going to call this date off purse. I'm gonna leave it as a you're having for safe ground. It's safe. We're good to go. It is. You're the first GOP. And today how can I start having elements later in this course? We're going to see how to add data using the insert and a bait. But right now I'm going to use the easy and of course not recommended way from here. Just go ahead, right click on persons table on the lake and it took 200 years. Of course, it's 200 can't be changing, but we were going to change it later. So right now I have my table. They have here NZ columns, a personality name country that a purse, celery and service on. Gonna start having data so I can come in here and that 1 may be the person name miles. Maybe the country UK, maybe the date of birth. How do you think it will accept today? I can go ahead and give it like this. The months on the day from the year maybe on the celery accepted it without any problem. If I just go down, it's going to safe this record into my table. There would safe already see the rid circle role lion was in here. It's dockings safe, pretty into the table. So there is one of the ways I can go ahead and give it into this format. Year does. Maybe that's true. The month women choose 11 on 30 and its safety as well. But if I give it in a fermented, it doesn't accept it. It's going to open air. So let's try for man. It's not good for the SQL Server, so I'm going to use the they It's less demons. I'm the years. No, that's not allowed. Good. This will map. The Madeleine said he didn't recognize it, so I'm just easy. It is worse. So, celery, I'm gonna give it. Maybe 10 7 1000 is the last four fell. It is observable. And as you can see, there is too numbers after the point in here, so I can go ahead and fill them. I can't make me this 50 and maybe this known 200 like a real number or something. If I would like to in Crete the limbs of this in here, I can go ahead to the design and everything. And I'm going to do this in a bit. The service it can take crew. So this simply of this person is in service or fools. But it doesn't take, for example, and a my name in here. So through fools or an easy one one, there's zero to fall so I can have one, which means true zero for fools, but doesn't accept to, for example, see, so only true or falls one goes here. Okay, so let's go ahead and try to make changes to this cooler here. So let's go ahead. Just design again on them going to make it for safe and chewed display. This deficient actually conclude this and again selected. It took 200. Rose, it's taken is going to open. And, general dig this much of the time anyway, so here it is. As you can see now, I have four numbers after the boy in that I think of them as you can see. So this is how you can control the floating point numbers like this. So one of the interesting options as well that we can see and the differences I don't have seen this when we're creating the tables that I can come in here and in the naming here, I'm going to change from variable character 100 to character 100. And I'm going to have a new image going to come in here. We have a new one. This may be the address, and I'm going to choose character Vereker here. I'm gonna have make it, uh, 100. 100 is good. Go ahead and save it. Um, again, just go ahead. Open again. Depends on the speed if your computer So here it is. Right now I have the new colon out the address I can go ahead. I'm type anything. 123 City love. Ah, so it's up in the text without any problem. But did you notice something? This is here, a text. And this isn't texting here, but I don't see this. Don't sing here. This is 100 from the design. The name is 100 the country's even 250. But I don't see this. Not so what does this mean? The difference between them. This is really important between character, that the time on the variable character data time is that the character is going to take the 100 no matter what elements you've added. Maybe this are here 200. But as you can see Evan blinking in here, it's taking the whole 100 letters, which is totally bad for the memory manageable. Here it's 100 ready, but it's taking only five of them. No other options in here. As you can see here that I'm saving other options. Well, others paces in here. See, now it's taking its basis. So it filled the other spaces off the 100 west spaces. That's really, really bad. So that's not commanded When you have a lot of data in your paper, it's going to consume your memory. Yours pace, that's that's really paso variable Cracker is very smart in this situation. Let's go ahead and trying to change this very well. Character. 100 100. Okay, Safe. Well, it's clothed this. I'm sure that it's fresh. Here it is that Let's take a look. These places are already there because they are saved already so I can come in here now and remove it. Remove the spaces should be done in seconds. Hands. It would be no dots. Here is no lots and the records and no spaces, and it's ended. Why here? I can go ahead and keep adding items, but it's not going to consume any extra texting here. That doesn't make any sense. That's a big difference. You can use a character identify if you know that, for example, you would be using only five letters. Go to one or maybe 10 100. If you're sure 100% you're going to use this number of digits, you can use the characters, but otherwise go ahead on Choose the variable character. It's the best way to I can come in here, of course, and keep adding iTunes too, you know, for you say, I don't have any year that worse to citizens. Maybe, Of course, that's not a real letter that just devastated some. Playing was to explaining the idea off having dating here, so just go ahead. Now one on one salary is five thousands and service? No, he's fired. Andi me headdress. I said that I could keep adding items like this. That's about the best way to do it. Of course, we're going to discuss how to insert items, well, data into your Ebel's. So that's it about data types and filling the tables manually. Manage stood you on and see you in the next lecture. 15. Constraints: Welcome back, everyone in this lecture, we're going to discuss something really important and interesting. It is gonna strengths. We used constraints simply in the databases to limit the behavior off the data entered into your table. One of this move, for example. What if I don't like some Coghlan's I d colon, for example not to be duplicated the data inside of it not to be duplicated. I can use a constraint to do this. What if I wanted to make a minimum for the salaries? I could make a constraint for this, but I would like to check some data entered correctly or not, so I can use the constraints. Let's go ahead and see the problem first. So in here, if I entered one and what is really taking full miles in here, I'm going to interment ing. So I'm gonna just leave the others empty. Using the database will accept this or not logically, on the real world scenarios. This is totally un cracked. Ideas in here should not be duplicated. Uses and chip should not be duplicated. Your car license should look. He duplicated any I d should not be duplicated. Must not be duplicated it strong toe haven't implicated. So what you think will happen? It will accept it without any problems. So I have two ones in here. The same ideas taken two times. That's a fatal later depths of disaster. But its behavior this way right now because it doesn't know what to do. We didn't tell it that the i d should not behave this way. So we're going to use constraints to correct this. The air here. I can also make something more dangerous. I can just leave it empties. I'm going to enter a me, for example, without an i. D. Can you believe it? That's totally wrong. So how can we use also this issues? True, Very easy, Keywords. We're going to see the unique keyword. Did not know the chick on the default constraints. Let's go ahead and try to create an you take. I'm going to great table golden, please. Great table. Cool it and please open the currency. And so I'm going to create simply i d. This is the idea of the employing inside the company. For example, it's going to be fine end, and I'm gonna mark this colon as not mold so that no one can leave it. Mo that any they don't like we have seen in here. This called him on them. Gonna also tell her that this is unique. Pull up a chair that it should not be must not be duplicated. So I can also come in here and say that could be name variable character 150 is loved. No, you should. You must enter the data for the empty name. You must give me a name, but it's not going to be unique. So I can have multiple jacks or multiple Jennifer's without any problem. And this is the reward situation. You can have the same names in your organization or in your database is our problem. I'm going to use a salary and going to use. That's a little I'm gonna give it to numbers, but it needs. And I'm going to make that the full. If I didn't specify a salary so that the fold is going to be maybe 3000. See a symbol of this? Yes, I have the fold. Maybe just I'm using a capital. Just Teoh Lestrade, the keywords in here. It's okay if you write it in small letters. So right now I'm going to add the age for the please, for example on them going to choose that it's and I would like to make a constraint in here to make all the employees to make sure that all the time please in my database or a bus 18 . Teoh, I'm going to add this chicken here, check on currencies in here and the age above 18 so it can be 1920 without any problem. But it can be 18 itself all 17. If I would like it to be eating, I can go ahead and make it great is an equal, but I'm going to use only the equals graters amateur. So let's go ahead on runs This If I run this, this here will not execute will be executed. This is called a commended line. Recommended line can be in handy if I would like to leave notes for myself that I am doing right now. Well, maybe notes for your colleagues and the work so I can leave them notes. This table is created. He's created for this reason, and you can write your reason for creating this table and you can go ahead and use even created on. And you could go ahead and avid eight and you can even coming here on. And, uh, this colon added all and give it a date for this reason. So it's very powerful, too. Have commenced in your coat to just leave notes for yourself. If you're going to come back in the future at work with this a script or the SQL code in here, or for your police on the fun thing about this that's not going to be executed. When you go and execute this management studio on sq is going to totally ignore it as long as you have this two dashes in here in front of Let's go ahead on. And actually it's existed in here. I can come in here, comment out this late complained, so I can just come in here. This is one of the tricks now the R that committed, and that's giving me an error because it's let since Dixie right, so coming here can go ahead and avidity. I can even commend my code off table here Really, for him now, let's go ahead and execute this on. We should have ourself on you table here it is in. Please, let's go ahead and open it. Let's try to add some dating here, so I'm going to have the one from the name of wrong to use my name on the salary. I'm gonna give it maybe 4000 on the age. Let's test the our culture demonstrate, sir, if I'm going to add maybe 17. No, it's not going to work. And here, the reason. Conflict occur in database. Human resources Table please Coolum age. As you can see, it's very specific about the Aaron here. If I try, maybe 18 get going to work. But if I try 19 now, it's accepted my record without any problem. Let's try to add a duplicated amply with the same idea in number as mine and going to give it one again. Maybe this will be John, so let's go ahead on that. John will be our someone's on the age of 25 error. Again, let's read the error. Duplicate key value is one. It's duplicated. Cannot concert duplicate key in object. This table. This is an object, sir. I can go ahead and change it again. It's working normally. Let's try another constraint. Let's check the constraint off the nominal and the name. I'm going to leave the name. No, I'm gonna give this complete three thousands only he is 30 now. I have another error. As you can see him here. Colon doesn't allow knows because they have a long here in the complete name should Inter in name. So maybe this is Emily. That's drawing off Force One. And I'm not going to specify a salary for Yeah, you remember from the creation off the table, we have the full salary, which is 3000. So I'm going to choose its salary for him. I'm going to have the edge. Maybe he is 25 I'm going to enter. But as you can see here, there is this red exclamation mark Your here. It's telling me that let's read the message. This road was successfully committed to database, however, probably occurred when attempting to retrieve the data after the commit. Because this is displayed in this room is read only to fix this problem. Please read and agree to rerun the very Let's go to this in here. You see this small little I Come on, go ahead and press on this. Execute or control are only keyboard. Go ahead and run it. Andi, please take a look at the salary on its updated. Let me doing anything. Go on the first over the full salary that I have said for this column on at the It two Jackson problem. But I can change it without any problems. Can come in here and make it 6000. 10,000 Don't any problem. So that's it about constraints for now. See you in the next Lecter. 16. Drop and Truncate Tables: Welcome back, everyone in this lecture we're going to see how can only the elite table old Lee D data inside of a cable using took your words drub on truncate? Let's go ahead and prime to use it. So here we have in please table from the previous lectures containing the the I. D. Name, salary and age. To refresh it, you can just simply click or choose Control are on the keyboard, as you can see it refreshed or from here, go ahead. On prison, this iconic, skewed sq. Okay, let's go ahead and try to clear in the data. So I'm going to write Kate Haven. I'm going to choose and please Now execute this successfully. That's run. I'm now. The data is related. This is one of the way is to delete data inside of table. We're going to see other ways. Now, let's go ahead and try to leave the table from here. So all I'm going to do it's pretty easy. Just type drub table underling, the table, as you can see in here. So what I'm going to do? Let's check. Actually there, on here, Hell, under the table because it doesn't exist in the or you don't have the permission. Just totally ignore this error. Sometimes he what it wants to do can't figure out what's going on. So right now I'm going to Chris execute a so you can see it's successfully solve any problems. Although there is some kind of on a rowing here. If I refresh, the table is gone deleted, and I don't know what it will do. If I try to execute this, it should be not putting. As you can see, Sequel, Execution, Error, as you can see in here. Invalid object, name and please, because the object doesn't exist any more. Okay, there except on cuz next lecture. 17. Exercise Create Tables Employees and Jobs: crying for an exercise. In this exercise, I need you to go ahead and create to jail. 1st 1 is called and keys time the second one's called Jobs. These two tables are going to be the pace for our human resources that obey that we're going to build throughout the course. So the structure of the employees table should be as following should contain the employee i. D. This as a unique number for the plea, it cannot be duplicated on camera G. Empty first name and last name cannot be empty as well. Gender, on the other hand, can be only male or female allowed to be injured in this field. This my need you to go and search for it. I need to go ahead and take a look at it, and we're going to solve it in his video. Don't worry, but I need you to go ahead and try to do it in Europe. On the country. You'll be is a date of purse mobile number, email, address on remarks. I need you to build all of this, feels the second table, which is the jobs table. I should also contain a job I d This is the idea off. The job cannot be duplicated. Cannot be empty. Job title cannot be empty as well. This is the title, Of course. Off the job job minimum salary is the minimum salary for its job on the default value for this field is 1000. On the other hand, the maximum salary default is 10,000. So I need you to create these two fields I needed to great, unique field. This is a job for managerial or not, And the default rate that it's not for manager and create the remarks field. Okay, most of you go ahead and try to do this only on Okay, I hope it just easy. Let's go ahead. I'm trying to do them together. So in here as Frank now I don't have and he will accept for the persons. If you have anyone's go ahead and to lead them, Joe. Right now, I'm going to create table table. I'm gonna call it Well, he's gonna go. But my parents season here. As you can see, I'm going to great First Colon, which is and three I d. Okay, this is going to be an end. Andi, it's going to be unique. I don't want you. I don't want to be duplicated. Of course. So unique is a keyword doing this rust on Not long. I don't want it to be first name going to be variable character. You gonna do 15? Maybe one is not going to be not Noah's well can take this copy, paste it in here and just changes the 1st 2 last. Next we have the gender, the gender. As I told you it, it might be a little bit complicated for you We're now, but we can go ahead on trying to do it together, so it's going to be variable corrector. It's going to contain some letters on them going to choose the keyword check, and the chicken is going to check for it. If the gender equal man or gender equal in May, we're going to discuss a lot of, ah, these logical operators and shakes later in the upcoming licious. Or don't worry about this, but I want you to try and search for something for yourself. Was searching is really, really important. So what's happening here is that I'm asking if the gender equal email and I'm adding a single coats in here, Girls, this assembly a string. If I remove this s hero, the management studio will think that this is a field or a keyword, and that's not the case. The same is a year and them adding before to check its only male or female. Let's go ahead and continue. Don't forget the comma. We have the country variables character don't choose. Maybe 100 for lunch. Okay, the GOP their worst. Going to the date Seen this before. You about, remember, Can be. What are you seeing? Demo pile Number can be the number I'm going to surprise, you know, and I'm going to give her advice. If you're going to use, listen carefully. If you're going to use and number, there's not going to be used for before mid calculation on Make it a string will make it a character again. If you're going to create a seal that is a number or containing a number, you should make it a string or variable character in here. Don't make it a number because there is no use to make it an injure or decimal or float, and you're going to perform mathematical operations on the other hand, if you make it very well characters, we can use the string functions inside of SQL to use it on. This field isn't going to see later, and I'm going to discuss on show you the difference there. Go ahead, cope with me and make it terrible corrected. Although it's good for very meager, sometimes over level's gonna have bashes four spaces or even currencies. So it's better to make it variable care. Hope that you prevents through this e mail. Also going to be irritable character, Have you 50 you can make this. I am. This may be starting Must you limit the space remarks? You Marcus is a good fields. Maybe it like just toward some information that is not from all of this feels in here just very marks again. You can go ahead and make it get set for the first table. Who can go ahead and tested right now secured to it is its credit. Let's go ahead and see it in get a basis. Here it is. Please. There's all the Poland is we've created. Let's go ahead and try to create the jobs, you know, create left, use it capital great table jobs in currencies and close them. Job I D. And again it's unique cause I don't want this number to be duplicated. And later we're going to discuss Why do we need an i d N h. Table on this would be an introduction to primary case. So don't where you're going to discuss this in detail. Let's no, I'm giving you e head Store it about what we're going to discuss next that I hope that you're motivated. Job title, variable character. It's going to be maybe sen Charles, and it's going to be not know Well, we have. Then job minimum salary is going to be dishonorable making. Maybe the lands can be nine come to and the default you remember we had a before very far minimum Could be 1000. Copy this and make the minimum change The name on the value 10 1000. OK, then we have what else is and is your aerial, on the other hand, can be off time bit. If you remember, this can store only true or falls 01 and the default So this is going to be there. Zero world represented. It's not managerial. It's false remarks on the other hands. Going to be very well character on may be 50 you Let's try to execute this under fresh hoarded race on here is our jobs people to I hope you enjoyed this exercise. I'm going to be building mawr tables on more items in or human resource. Is that a base? As we're going See you in the next lecture. 18. Column Properties: Welcome back and this lecture we're going to talk about colon properties. We can set some properties for each column separately, that we're going to see the different properties from here. So inside of the adventure works for any database, actually, to create a new table would be great. It I'm gonna create table cold products, is gonna contain maybe protect my I d is going to be an end God like me. It's not product. They're just leave it as it is going to do much in the table column in here. If you go down, you can see there. Is it Hap called colon properties. These colon privileges contains really some interesting privileges that we can add to our columns. So the 1st 1 of the name that the names forsaken coming here and changing this is the same at this attribute in here. If it's allowing no announcer, if I moved us to going to be known here, as you can see if I said to back, it's going to change ans actually be at the time, of course, and in here I can say that the full value Yehl said that the fold very to any number that I would like okay in here in the table designer. Then we will have the collegian. Fifteen's a collision before in year. I can set a collision. Not only bear a bit of a server or a table, but also Perry call him in here. That's really powerful from the SQL Server. The computer called him a specification attribute, and he I can set e formula on this family. I can make anything from me different columns, for example, on it's not going to be toward. Actually, it's going to be no like a logical attributes. That's not physically. It's toward in the table unless you check. This is persisted in here. Orchid. Yes. Okay, I can come in here also. One of the interesting attributes is a description. I cannot description about the golden here, So maybe our time. This theme, the primary key off the table. It's the product cereal, for example. Remember, so I can keep information for myself or for my cleats in my think. That's really powerful. One of these things, also they love about it is the identity specification that so I have an open it. We're going to explain all of them. Some of them are really advance it. I'm not really and, uh, the time to discuss it now. So let's go to the identity specifications if you enable identity specification in here too . Yes, What's going to do it? This column is going to be unique. That is going to create and numeric sequence best on the increment arm on the seat. So the increment in decades the increment, which is a numeric value, will be used so I can implement it to tools of each time it's creating your own is going to be to all street or any number of blind, So I'm going to leave it as one. So each time I am having a new row. When you record in my table, it's going to be incriminated by one Does. It simply is a value that is assigned to the first trip so I can make it one that seems what you can look and make it maybe 10. Okay, I'm going to save this table right now. And don't forget to mark this, uh, the remember Ricky I'm going to say for this is just e Come, Kordic fresh my table in here. My tables. I conformed by Tim Product in here, I'm going to at some elements to it. I'm going to add anything in the product idea and remember that we have said that this is an identity and it should start from 10 and increment one each time. And if you notice that maybe the color is different, it's not this black or dark grey. It's somehow light gray or silver in here. I'm going to type anything. I'm just going to quite the product may be TV. I'm just going to first enter on here. It is the first number of the 10 that we specified in here on. It's going to increment. By what? So maybe the product, the second product is going to be. Maybe PlayStation. As you can see, it's incremental things out. Me doing anything really noticed? Feature. If you're going to have some serial number, any table on, don't have to worry about having the items. Database itself is going to manage it for you. So that's it about the colon properties. See you in the next lecture 19. Primary Keys: welcome back, and this electoral regard discussed Primary Case, one of the very important concepts in databases. We're going to explain the idea of a primary key and then see how can we apply then? Skills. So how can we be fine equipment? What is E primary key in the first place? Primary? He's a special relation Aleta Base Table Cola. So it's a column, or it can be multiple columns in some advance it situations to uniquely identify all the table rivers. So it's a column, and it's used to identify the record. So the main features off the primary keys that it's a unique for each row of data and it can look contain knows. So the primary key is critical to an efficient relationship databases because it's used and their relation between tables as we're going to see when we have a lot of tables to manage them and make relation between all of this table or different relations. Need primary keys have regard to see all of us are working or using predator keys in our daily life. Without that, we deal with them. For example, all the citizens, no matter what they came three years you must have some sort of unique member or an I D or a Social Security number. Passport numbers, car licenses, bank accounts, probably ID's in companies, all of this or some sorts off primary case. So let's go ahead and see. How can we create primary keys and skills? So we're going to great it using the SQL code for Then we're going to use the management studio features. So let's go ahead. I have a theory table. Cold persons are going to grab it going. Hughes's drop table Persons execute this. That should be dropped. Okay, favor Fresh right now let me drop. Okay. Now, let's great. You aren't cold. Grayson's again Long queues. I'm using this table here or this example have just an example. Okay, We're not building the HR database or the humerus is the debate. Using this person table is just an illustration. So in here, I'm going to grade the columns inside of this tables. I'm going to until it that there would be person I d is going to be tight end on to make this idea in here. The primary key we can simply coming here until it that this is primary. Kate. Melissa. I'm going to bed. Maybe person name. It's variable character breath, Christie. Maybe it's loved. Hello. Well, maybe the age it's not. You can go ahead and place the chicken here for Like what? I'm going to do this right now. So if I'm going to ask you this well, it's refresh. We should see the same table that we just dropped in here persons. But there is something different this time. You see, this year, this icon, if icky. Now SQL Server knows that this is the primary key. And as you can see in here, you see the P keyword and here or the abbreviation for the prime Ricky. So now we know that this is e primary. So this is one of the way that we can create primary keys and SQL Server. Let's go ahead the game and drove the table going to prove it. Now we have no persons table dropped again. Well, then I'm gonna create it using a different way. Maybe this way is a little bit harder, but it's good to know that there is another way, sir. Great table prisons again, I'm going to use the percent of I deed is going to be an end and we're going to Hi. Primary keen here or anything? Then I'm going to Maybe you should commend this in here right now. Ok, so that we don't miss stately Run this. So now let's go ahead and type person name wrong again for the variable corrector you 100 this time Just a number. All in here. The edge me and that's it for the columns Toe had the primary key right now if you think a Fed the primary key is some sort of color strain. Remember the constraints that we used to after sun column So we can come in here and together like two great constrained and I'm going to add a name for this castrate. I'm going to call it que score person, and I'm gonna tell it that this is a primary key constrained on what did they call him that you're going to place be constrained to? It's going to be the person highly. So that's the other way to create Yeah, primary. So let's go ahead and select this on. And when it Okay, it's completed successfully on here it is. If you put it again. Here it is again, The person key. A vision I d with the primary key mark on here. So this were the hard way to curate primary keys. There is a very easy way to created using the management studio. Go ahead and long to believe this. We didn't, um You just come in here New Haven on them going toe the person i d C o B type event only. Person name, variable Director. Leave it 50 on the age I was going to be stipend. So how can I act for America? All you need to do is just coming here, right? Click said primarily it's the first option said Primary Key. It's added, Just go ahead and save the table persons when we should have it again on here it is persons home. The prison i d is a key. So actually knew the SQL code so that you know how to create it, because this is really important. And we're going to work with a spear code and some situations that there is no replacement for a secure killed. So it's really important to learn how to work with SQL code. This will help you also work with other data basis to go to make life easier on. We're going to use also the managements to you as much as we can. We're going to use primary keys, and almost all of the tables were going to create an old haIf. So this is really important for you. So that's it for now to you in the next picture. 20. Foreign Keys: welcome back in this nature we're going to discuss for in case what is the foreign key? Foreign Key is one of more columns in a table that refers to the primary key in our. How simple is that Greinke is used to link two tables together. Hasn't gone to see. So What I'm going to do in this lecture is I'm going to great new tables and I'm going to use primary key. Answering is to link them to Kevin using SQL code on musings of management's Did you to show you pose ways, So let's go ahead on. And if you remember from the in please table that was created earlier, we have the field or attributes or column cold country. Right now it's set to variable character. There is no tables for countries. It would be better if I have separated table for countries so that they can store all the information about our countries and link them together in the employees table or anywhere that I would like to store them. So let's go ahead and create you people called country rate table countries. You just glitters countries, um, the components, all the columns off this table is going to be the country. I'd primary key for the stable. Gregory Key country name. The name of the country. Of course. Going to be variable director, maybe 250. Some countries are long and names on. Maybe we're going to use e country deprivation. Is there is some abbreviation for countries as you see in the in Deutschland for Gambill UK or use a or any other countries deprivation going to use variable character, maybe make it sighs by six, maybe six. Um Well, last colder. Maybe I like I have the capital off this country going to be variable character. Oh, I'm gonna set it to maybe on a grid. That's it. We have a new people sprayed it for countries. If we refresh our table, we're going to see that we have it in here countries. And right now we didn't create any foreign keys or used any foreign keys concept, but to use it, we're going to create a new table Cold cities. And as you may have guessed, there is some kind of relation between countries. Answered is cities are part off conscious, so I'm going to create cities table and link it to the country stable. Let's see. How can we do this? So I'm gonna great cities cable. I'm gonna use city I d. This is the first column is going to be an empty on. This is going to be the primary cape. It's going to be unique and not obligated under. We come here in a city name. Terrible character does going to be 250. Maybe again, let's no on a country. What's in the country country? I d. Now in the search A column here I was starting to have the foreign key on this. Remember for them definition that the foreign key is a column or a key primary key in another table. So in here in the country's the country, I D is a primary key. So that's how it's going to be. Link it together as we're going to see. We don't understand better when we are going to created letter. So country I d. And for in he as you can see now I'm starting to flag it at the foreign key and turn it blue because it's he key word. But that's it. No, we need to tell it where this country i d. Where this colon is reference that troops are going to use the keywords. When you key word preferences, I'm gonna kill it. It's going to the country's table. Um, the coal of its going to have it to we use it is the country i d. So let's revise again. What's we doing right here? He created a new table Cults. It is that's very basic created. See the idea? This is a primary key call. Um, on here we have another attribute called City Name and here is old and You Work. We created a new column called Country I D Stipend on. I slagged it as a foreign key, and I said the references linked it to the country's table to the country. I d. Cola in here. So let's run this anti. What's going? Give us, Go ahead and execute it. It's refreshing tables. It is cities. Let's open the columns. We have the primary key that we have a new ankle in here. It's a key also, but it's different as you can see. So now we successfully created the foreign keys. It's link it to the country's, but here so this is how to created using the S Q. L Okay, right now I'm going to do the same. I'm going to do the foreign key and relations, but using the managements to do rather than using the sq cold. And it's going to be much easier if you're not at that. The in keys table in here doesn't contain any information about the department that the employees working for. So what I'm going to do is I'm going to create and you table that contains in names off the department's on. I'm going to link the department of the impede. So let's go ahead and quickly create the port will table in here Won't be or and I. D Styx peddled the Portland name Variable. Correct. Maybe said it, too. Any 100 Christie on. Don't forget to slag department on the ass point. Keep Granite State Court. It's professional tables we should have being your table departments. And as you can see that as we're going, we're building the human resources database. We have now more columns, more tables, please, jobs, Birdman's country cities, and we're going to link them as we're going. So right now I'm going toe opened in the stable in the design mood on first thing, I'm going to do it. Said the MP idee as a primary Kingo, Save this on. Then I'm going to have the the Portland idee in here. We're gonna send it to the end. But right now, I added the department I D. But there is no relations or connections between this table on this table in here, so I'm going to press right click throughs relationships found in here. I'm going to add a relation. This is a generated name. Leave it it as it is. If you're going to change it, go ahead and change it. There is nothing wrong but this. But I'm going to leave it other days and I'm going to choose the tables on columns specifications. From here, we can make our relations. So the primary key table in here is going to be the departments and I'm going to choose the department I d. And in here foreign tea table is going to be the department I d in the increase table. So the 1st 1 is the department table department. I d the foreign K or the 2nd 1 is going to be in please table that Portman I D. Call. Let's go ahead and click a little. Okay. The tip blessing needs to be done. Just go ahead and save this and go to safe it. Let's go ahead right now. Just go ahead and choose. Yes. Let's refresh the when he's table in here. Over the columns on Here it is. We successfully created the the court Mint I D column and slagged it as a foreign key. Link it to the foreign key and using the management studio. So we have seen how degraded using the SQL. Um, we have seen how to create it using the management studio. I hope this was fun to you in the next picture. 21. Exercise Edit Country Column in Employees Table: time for a small exercise in this exercise, I needed to glide on, edit the country. Call him in the room, please. Table, if you remember from here I have e column. We have a column in here called country. It's off time, variable corrected when we created the police stable. But I need you to do is to link this Cole living here, do this table in here countries table using the foreign. Keep. So I'm gonna pose a video. Go ahead on. Do it when you are. Okay. I was easy. Let's go ahead and try to do it together. So I'm going to use the design mood in here on. I'm gonna change it. Make the name first the I d to match the name here in the able countries country I d on to match the driver. I'm gonna choose, and I mean, here I'm going to select relationships. I'm gonna create a new one termed. I'm gonna call this Maybe what? You're going to change it later, So just leave it as it is. Go ahead and choose able the primary key table, which is the country's earn The column is the country I d on before ing able or the foreign key table. We're going to choose the country I d. That people feel that we just created or edited in years. Go ahead on glee. Close, Safe It? Yes, on we or done. We know Fresh table. All the employees, of course. Here it iss We have the foreign key. Link it to the country's table in here. You primary came here. So those ip do you explain? 22. Execrise Create Table Previous Experience: Okay, It's time for a exercise into sector science. I need you to go ahead and create a new table called previous experience. This table should contains any previous experience any impolite had in the past. The columns of this table should be a start date in date. The company name on the city. I don't need to figure out something on your own in here to make it it better design, actually. So I'm gonna pose a video until you go ahead. I'm trying to do within your own, but we're going to sell it together. It's everything for you. So go ahead on trying to do it. Okay? It's easy. Let's go ahead on trying to create it. So from tables, New table, we're going to add the first of all in the pre. I think I need this here to contain the MP number that they can link it to the tables in pre. It's going to be a five and on. Then I'm going to have these tart date. They got into the date on the second date on the date on. Let's go hunt through the company name variable. Director, Maybe you can go ahead and make it 100 50. I'm gonna choose the city I d and on. Do you think we're good to go? No. We have one problem with this. A structure in here. Let's mark this and the primary key. So maybe you think that this is done, but it's months. There is a problem with this design in here was checking that the city i d. The same name? Yes, that's correct. So what the problem with this design in here? The problem is that narrow with the impunity is the only key or the primary key in here each in plea. We'll have only one recur, and we agreed that primary keys cannot be duplicated. So, for example, of employee number five, I have some experience with the company going to be stored with other problem. But if he had another experience, it's not going to be stored. Destroy this. I'm going to save them like this. So I didn't solve the problem and I didn't bring the relation with the city already. I'm just going to say fit, so I'm gonna call it previous experience. We're going to have you record in here. Previous experience, good at for example, of being a before. If they told you started, I'm gonna leave it Everything on the company name, maybe company X, the city just to be fast. Now I can go ahead and add another MP company. The without a problem. But if I'm trying to add the same simply again Number five, for example. He worked, for example, Amazon on what I am trying to save this. It's not going to work. As you can see, you cannot insert duplicate key because now the primary keys being duplicated. It's a problem. How can I save multiple previous experience? Foreign, please. How can I sold this? It's even. It's time it doesn't want to work. So how can we? Exe cute or perform this type of structure simply couldn't come in here in the design. I just deleted to data in here. So I've been coming here and many another colon cereal on. It's going to be bored of the Keats, so each simply can have any number of experiences, and I can go. Haven't had experience from 1234 10 million. 100. I don't think anyone who reach 100 so go ahead on slick post of them and choose. So if I don't have them in here, I'm going to like this world and this boil and right click and choose both of them said primary key. And now both of them is going to be the primary keys. It's called Composite King. If you remember from the presentation off the primaries, we said that primary keys can be one or multiple columns, and in this case it's going to be in multiple color. So I hope you get it. Let's go ahead and make the relation with the city. I decide I'm going to choose relation, have a new relation on. I'm gonna go to the people I'm gonna choose the cities on. That's actually not what I want. I want this city I d yes. On the city. I d in here. Still of this for now with cereal on, We got to go. Just blow this safe it. Yes, fresh. You should have a new table cold Previous experience you can see and we have a foreign keen here and two primary keys. But I can now go ahead and start having clothe this one. Open it again. I started having any and please liking At 5 to 1. It's another sitting movies, any number one. And now I can go ahead and get them before serial number two on the level 50 maybe 10 on it . Accepted it without any. So Well, this was fun and see you in the next picture. 23. Create Table Using Import Wizard: we'll come back instinctual. We're going to do something different. We're going to create table. You think the import whether and fill it with data. So what does this mean? What I'm going to do is I'm going toe and then you table in here to this tables or this database called status. It's going to contain the status that can be assigned to each impolite, maybe single, engaged, married on etcetera. We're gonna go ahead and even add more. Maybe unknown, as you can see. So how can I get this extent she important in here allocated? He that See this? I'm going to juice tests Data for his next I'm going to to the source is going to be the Excel. I didn't choose it. I have it in here. Steve Stater. It's next on the destination is going to be the sequel server. I'm going to use the Indus indication many to Z Horizonte cation. Copy it right now. It's already in the sheet one. Here it is. I'm going to select this one on. As you can see, you already have done before that we used to Celic on existing people. But right now I'm going to choose and you want. So I'm gonna time its name. Maybe remains this. Okay, well, the mapping to check everything. Yes. As you can see, there is no the lead crews over a bin. Though the table doesn't exist on Lee. I have created destination people. Andi, in here. I'm going to change the time I'm gonna choose. And that is the time for status. I d on in here. I'm going to choose. Very well. Character or the name? Maybe. Change the size. Maybe make it 1200. Okay, go ahead. Are executed. So next. Next on. As you can see in here, everything is created successfully and only have six rovers trans fair to go ahead and fresh it we should have on your table. Are you here? It is a status. Let's go ahead and select the data home. As you can see in here, it created the table for us and I haven't dated. It were in the cell. She was about us doing anything, which is very, very cool. So that's up to you in the next. Like 24. Import and Export Data: we'll come back in this actually going to add some data to our tables using the import feature in the management studio. Although they have been doing in the previous lectures that we have been creating the structure off the debtor baseball, the table. But there is no actually the inside of this tables. So what we're going to do in this lecture is to add some thinking. So here I have an excel sheet Buy prepared. It actually kind of some random information. Here is some cities. Here is the sheets don't Here is the same as for a table. So here we have tables. It is so the same I have she calls it Is it contained the city ideas city name on the country on the This is the idea of the country. If we go to the country sheet, here it is. We can find that one in the United States of America onto is Germany answers. It's just you don't need it Just entered to upload into mind to base As you can see him here from cities from you say something this from Germany since it is from China, Egypt, Australia, Russia, Go ahead and add anything if you will be United States. And he was the capital of each country. On here is a table that contained the department's. Maybe you have production, research and development, tracing, marketing, information technology, human resources, accounting and finance planning and all scenarios. There is maybe more thumb departments more than this. Maybe let them. This is a just the date of the Gasser. The jobs we have here, the Continental Oppressions developer, Engineer Teoh. Security Certain anywhere is the minimum and maximum salary on is managerial. Not on day one of the please. I have a party, if it should be First name the last names of gender D country. And again, this is the four ng t link it to the countries in here. Andi, you get emperors and remember all of this are just random it Okay, so what I'm going to do now is copy this If I can take hobby, But when they're going to use Kobe, of course, we're going to you the import feature off the management studio into I'm going to copy this into the management studio or the tables in here is right now. There are empty but one here. So like anything, it's there is no data. We need to add some data so that we can work with it in the upcoming election. Let's go ahead and try to, you know? Okay, let's go ahead on do it. So from the human resources that are based, choose tasks on go all the way down and can reach import data. Go ahead and select. Nick, this is cold. The SQL Server import on export with it. Go ahead and she's next to right now are being asked to enter the data source where I'm going to copy later with the information. So I'm going to choose one of these sources in here, which is the sale cheat. I haven't cell. She contains the data. We're going to add the past in here. It's tables. You can find it in the resources in this lecture. Go ahead. And Jews next. And this chicken here, If you have the first column, have the names go ahead and chicken. If you don't have names for the columns and the excel sheet, remove it it. But in our case, we have colon name, so I'm going to click it so that it can ignore it when it's uploading the data into my table. So right now I'm going to choose the destination. Where is this data is going to pick up it to on going to choose this nation? It's not going to be an exception. Of course it's going to be sequel server. So in here I can choose sequel servers Indication and give it using him and passwords that's valid on have the enough permissions to these to do this task. Or it can chews when Justification Again was the same enough permissions to do it, so I haven't choose. Next. She was the 1st 1 could be data swim one more tables off years on in here. I'm going to choose. Do you see this in here? This oldie she It's inside of the X Help fund, so I have cities and it's going when I click. It's goingto tell me that it's going to create in the destination a table with this name cities this dollar sign. That's not what I've gone. 21 is to take whatever in this cheat into this table and see the difference. Now it's going to copy all the information and the seat in tow, this table not create a new one. OK, but if I could be this straight now the city is It's going to look for the country already , which I don't have right now. So what I'm going to do is to try this, going to copy the country's first country of the countries on them. Going to could be the departments. Andi, I'm copying right now all the tables. It's not going to be affected by the Foreign Cape, so go and do the job. So the city's is depending on. The countries of the country's has to exist. First on the in pleases existing on the almost all of Hunde countries that apartments on the jobs also. So go ahead, choose next. But before we go ahead and choose likes, I'd like to show you some chicks in here. So, like home edit Manning's I'm choosing in here the jobs table. So in here, as you can see, I have the jump, I d. This is the source. This is the data coming from the tell cheat on this is the data or the columns into the station. So right now it's telling that there is a source i d going toe source. I job I d actually to the job i d on job title going to job title, that the minimum salary doesn't know where to go, so it isn't to ignore. It's going to take, and that's a problem. So how to fix it in our database? It's not cold minimum salary. It's cold. Jump minimum self. That's why it didn't find by its own. So I need to set up the same with maximum. The same managerial there was a spacing were, that's why it couldn't figure out where to go. I think that's really important. But to go ahead and check the departments workings is valid. There was department I D toe the department already depart main to the court, TV countries as boat and resist. Nice option. It's working. As you can see in here. I can't believe the tables, the roads in the destination table where I can feel it to a bent. So if this table does contain data, I can choose to delete it, or I can choose to have been the rose to it. It's up to me, but in our case it doesn't matter because or all tables are empty. Shoes okay, Aren't use next. Next. Humphrys finished. One immediately. Yes. Um, here it is. It's loading. Information. Yes, Successfully. Let's go ahead and check it. Let's shake the country's first your the country's for somebody in our database. Check the departments. Here it is again. Let's check the jobs. Earl. Here It is really nice ankle teacher to have him management studio and very easy to use. Okay, time to add the cities on the employees table in here. But I would like to remind you that the created a column in here the impunity to be generated automatically when we choose the identity specifications. So in this case, I have already on identity or a serial number for the employees. So I'm going to turn this off right now. Andi, I would like to take it from the Excel sheet. Okay, so I'm going to choose No. Now, stone nosed. Okay, safe. It's You are had. Close this. Let's go ahead. Merrigan Choosing Mercer's A database tasks onboard data thanks through the source, which is the Excel sheet. Let's go ahead. Choose it Now the nation is going to be easy. Sequel server. Make sure that you have in a permission. Could be the date is for more on the table. The city's glad and change this to this city's only without the little assigning here of a Gandhian fees. Change it to the he's only on. Please make sure when you're copying there's that all the columns and the source and destinations are corrected. So here I have a problem of by Lum is going to be mobile number when the email address is going to be email have Okay, let's take the cities. It should be fine. Yes is good. Let's go ahead now on Next, All the way. And Chris Fish. Um, here it is. It's carpet successfully. Let's go ahead and check the cities more of the citizens carpet with the foreign key. Link it to the country's, and we're going to see later how to link them together and view all of the cities to their countries. The name that the number chicken please on here is in please, very corrected, regretfully copied without any problems. Don't that's it about the important. If I would like to export some information, I can come in here and revise this separation just like this coming here from the resource is until the tasks export data. I like coming here. Choose next. Now these stores, this is the reverse is going to be not. The Excel Sheet is going to be the sequel. This is the source. This is where I am getting my data. Going to choose also a sufficient permission. Syndication in here allowed to perform this task. Now the destination can be anything can be access. Sell my icicle. I'm going to to seven for this in airier. I'm going to choose a pass right now. I have only this its influence on going from here to create new, great new like this. What's God having created manually? No. Do you have in here? Close Rated here. You smell great. So I'm going to. This is new, but this here it is. It's created choosing your phone. Choose next. What? I'm going to call me. Maybe it's the employees, for example, is go ahead. This is the name of the sheet. I can go ahead and change it, but I'm going to leave it. That's next. When I Now let's let's go ahead and open. You created for all. We should see that. Here we have a new sheet. Colden. He's management studio. Created it on. Here is the data exported from my Davis. Really? Niles Beach. Okay, that's it about the export as well. And the import? Of course. I hope that you like this feature and see you in the next picture. 25. Exercise Primary and Foreign Keys for Status Table: Okay. We have seen how to create table using the import with it. We created the status people. What didn't create the primary armed? The foreign keys. So it's excess on Is I needed to go ahead and create the primary on the floor in keys for the status. People. Go ahead. I'm trying to do it. Okay, I have. It's easy. Let's go have fun. First created the primary key to from the design mode. I'm going simply to like this as the primary key. Safe on this. Done. Now we have a freaky Here it is that sufficient? The street isn't here. Okay, the primary key has been created. Let's go ahead on. See, how can we create Frankie in the M please table. That's where I needed to be. I need to kill the status of a gym. Pleats. I'm going to We've been to disarm mode. I'm gonna that I'm going to other Newfield. Peters, I d not OD. Its 80 hiding on the design is going to be injure on them going to save this home. Start making the relations right, Blake. Relationships winked at a new one. They will and calm specifications untrue. The status table from here through the field status already on. Then change it. Two states I D on. It's safe it make sure everything is OK. Yes, that's refresh our tables. I'm here. It is. We haven't you? Coolum State Society, which is a foreign key, as you can see in here, being linking to these things. That was it you Indian extension. 26. Entity Relationship Diagram (ERD): as you did, a base is growing larger. You might have difficulty trying to track the tables on the relationship between the tables . To solve this issue, we can use something called entity relationship diagram or commonly named Yadi. What is this entity relationship Diagram on the hors D? It's simply a visual form off the relational databases. It showed the tables in the database on the relationships between these tables e all the shows three basic elements the tables attributes on the relationships between the people. Let's go ahead and see How can we generate this BRD in the managements to you through from the human resources database in here, I can come to the database diagram. I could no folder in here, right click and choose Knew that obeys Degla. Great. If you have a problem trying Teoh making you that Graham with the management's, did you just make sure that you wanting it as an administrator? Great. So in here I can choose to add the cities and the countries I can at all of them, though the person's put the person with just created for some testing on day. Some earlier, we were just doing something with the human resources, but it's not actually related to this group of tables. So I'm going to add all of them, except for the persons on them going to I don't even see all of them are being added in here. So the year old added in here, as you can see, willing to organize them. So this is what is meant by a visual form of your database that you can see now I can see the tables in a very nice and decent wait. It's just try to give them. So here is the please or another wall down there. The department's as you can see that you can see that relationships between them just having them together, but is also another one. Yeah, no, just give the MPs on the topping here. Just e matter off. Organizing your tables. Okay. As you can see in here that I have the please on. There is some connection in here, but we in the please and the department using the department already and then please on the department I d in the departments and the same for the city is the country's also goes for the city near as you can see from the previous experience is going to the city. Link it to the city in here, on the state as the newly created people is linked to them. Please, as you can see here. So I figure out that the drops in here they're not connected to any holies. There is no connection in here, so I can go ahead and add it right now at it, using the code. So you see, it's very, very helpful right now. I can visualize all the tables hand see the relationships between them. One good way to display the fiber going select all of them going to choose, right Click Able View Jews standard on Now this town that is going to tell me which one is low, which one is the type of each. So I can see here that the MPs and first name is variable character and that's it. He did sites and they can even come in here. And the change it, for example, the jobs right now doesn't have a point right here. So I can come in here and tell her that I would like to sit the job I d had the primary key and then going to save it. This symbol in here This asterisk doesn't mean the safety. It mean that this is not safe. Get Okay, I'm going to save it. You see the diagram also? Yes. It's telling me that the following table would be safe to your database. Yes. Save it. Okay, now the diagram a safe in here. I can go ahead anytime and displayed on the tables. Figure two jobs. Expecting right now from this cycle. Of course I can do to hide it can make it disappear. Will make it appear The jobs in here columns are now It's a do primary key. Thanks. Very, very powerful. And if I close it right now, I don't have to open it again and gate all the tables. All I have to do is open the diagram and everything will be loaded. So that's it. And see you in the next picture. 27. Select Statement: Welcome back, everyone. And it's like you were going to discuss something very important, Which is this really all this select statement Select statement will enable you select data from our debates. Let's go ahead and see how you get or retrieve also like the date. So in here, I'm going to use the human resources that service. I'm going to press on your query or control. At least make sure that you're using the human resources that a base, as you can see from this tribunal list in here weekends elect their the different innovations that we have on right now, we have only that venture words on the human resources. So I'm going selecting resources and I'm going to So let maybe data from the police table in here. No one that we created earlier, sir. What? I'm going to time. It's very straightforward. You go into time, select, as you can see turning blue, which means that this is a key word and I'm going to use this symbol, the Astra's. This means that ain't going select all the columns, Nothing records. And I'm going to show you how select specific columns in a one I'm going to live. Oh, throng is also a key word. This is a sin, thanks to select data from and needs inspired the table. So I'm going to happening this table. And as you can see, the management studio is helping me. So here it's telling me that there is a table cold in. Please, I'm going. Simply do select the stable. Let's go ahead and try to excuse this to run this command. We're going to press on this fluttering here or Prescott Fires execute on. Here it is. It retrieved all the data and the stable. If there is more, it's going to retrieve it all. All the records to 14 records that we haven't hear. The one to ST John Gems will. Fred. Andi, The last rests here is retrieving all the Golan. The employee I d. The first name. Last name Gender. Country on the ticker. So what if we wanted directory specific columns? I don't want to retrieve all of these so I can simply remove this and start timing. What? I would like to retreat. So I'm going to choose for example Maybe the MPRI Kaindi has it easy. Management studio is helping me first same. We leave you last name. Maybe also the date of birth. Okay. And again from in police. So all everything here is just I removed the symbol off all or the asterisk This sign here just removed it and start specifying which called him and I'm going to display. So added the four columns employee i d. First name, last name data purse and he resists antics off this leg statement from the table from on the table name. If I'm going to change it, it's going to display data from another table. But of course, right now it's going to give me an error because this feel it doesn't exist in us. Table, Let's go ahead and execute this on you. It is now. I have only displayed so cold if I remove one of them display on Lee Street. If I added a different one, maybe the gender that's going to display the gin very easy. So what if I wanted to do something different? Andi return only maybe 10 or four or five columns. Let's go back to the select whole Astra's. I have all of the Coghlan's. Maybe I have 1000 and rewards you now, you would have probably holding 1000 Riggers at least. So what if I want to display? Only 10 of them are like to see only 10. So I can simply coming here. Andi, have this. You were select tub 10 for example. Only the Astra's symbol as it is. So as you can see here we have 14 Rikers right now am peeling. It is like only 10 so it should display until district word. Sergey Here. Let's go ahead and run this. And here it is. We have only 10 records. Very good. But what if we wanted to change it? There may be a like this lately. Directors would like to see only two records of this table. You can see only two. I want to change it to thought. Great Flexpen. So whatever you want to do, remove the after sky. Select Onley. Couple of Collins may be the first name on the country already under a minute. Can I do this? Yes. Just remove the Astra symbol on the impurity They called him to do in life display. For example, the first name. Maybe the country mighty where it is. Maybe the email address. Okay, and again from the country's. So let's go ahead and run this on. Here it is. We have only five acres rose, and we have all these four Coghlan's attributes in the I D. First name. Country 90 on email address. So that's it for this elect statement. For now, we're going to see more as we're calling. There is a lot to talk about on discuss about the select statement to you in the next lecture. 28. Exercise Select Data From AdventureWorks: Okay, let's have a small exercise about this. A late statement. I know that we didn't learn a lot about the select statement for now, but we're going to discuss more in the upcoming lectures. But let's have a small exercise to practise what you learn it. I'm going to ask you to select some data from the adventure works database. The colon little like you to select would be. They predict i d. The name, the product number, the color CellStar date. And these are the exact Colin names in the table that's called Product in the schema production in the Dead. Ray's adventure works. So that was a video. Go ahead. I'm trying to do it and you are Okay, it was easy. Let's go ahead and trying to do it together. So I'm going to click on your query on make sure that we're using Adventure Works Database Later, I'm going to show you how to select from Any databases are changing these. Okay? It's gonna have types of late on the first call. A little large select would be e put it. I d. Then we have the name. Then we have the product number. We hear the color on the sell. Start it. Let's go ahead on. Tell it these colon right now it's giving me an error because it doesn't understand Where is this columns coming from? So I'm going to tell it from and I need to specify this scheme in which is production on that I'm going to choose. As you can see now I have the tables inside of this or related to this, a scheming here. So I'm going to select Park the name the table. As you can see now the red line has gone. That's gonna have excused this on you. It is if the product idea name predict number, color and sell started. Are all of these columns exists in this table? If we go ahead on chicken production product you it is columns on. We can find that we have a lot of information in this table. If I selected something that doesn't exist, it's going off course to give me an airplane vehicle on the need for example. So these columns already exist on you, resident. As you can see, a lot of data. Okay, that's it for this exercise. Next, we're going to continue learning more about the eat select statement to you in the next picture. 29. Where Clause: okay, just that you were going to discuss what are the very important keywords used with select statement, which is the wear clothes funky, where is used to filter records? Four rows based in specific conditions that we can specify. We can use these items in here, for example. That's not colder, the equal. This is the symbol of the equal. Andi not equal. This mean that it's not an equal specific value. Our graters analysts and resent it will lives that are equal between. And this is one of the very core for keywords. It's in between a specific range of values online. We concerns for a specific pattern and conservative analyst value are going to see these on more. So let's go ahead. I'm trying to see out Solich based on a specific condition. What I'm going to do is straighten your query again. I'm going to let from the peace table turned so like, oh, when he's that's going to retrieve older values and think he's table. As you can see now, it's not working because I have here the Adventure Works database. If I change it to the human resource, is some work. If I go back to which works not going to work. I didn't use this in here human resources and especially find the data bay that I'm using. Euro bbo Is the default schemer that it's created for you when you didn't space for a schema? So this is the fold one. As you can see in here, human resource is a year selected. Now I have old table that in the human resources that on the inside of this video's keep okay, I run this Adam, selecting the adventure works. It's going to work. This is how we can select without changing this database in here. That's a very powerful way. One of the ways that we can also select table or data. We can just go ahead and navigate through easy table in easing here, for example. Right, click. Um, choose select one sounds and murder forces. 1000 can change it anytime on. I'm going to show you how to change it. But this is one of the way that we can select on. If we take a look at it, we can see that the managements to you is using this technique. Resource is devoted to peace. I go I'm trying to select from the product and the Adventure Works database. It is select Oh, glances and Berries. As you can see, it's also using adventure works the production, that video Because we have a specific schemer satisfied for this table. The product. Okay, okay, let's continue our discussion about the wear. So now I don't want to have all of this records, for example, and the protective or in the in peace, If I have more of life to select based on a condition, and I don't want to use the top 10 because the top 10 is not actually faltering, it just displaying the top 10 or five or street I would like to select, bathed in a condition, maybe a specific country, gender and name. So let's try to select all the males. Right now we have some bailed on some female, so I'm going to select all the men that I'm going to use where, as you can see, it turned blue Junior, you know the Golan that I'm going to filter based on it equal because I like to send it to a specific value to select If you remember this there, the string variable tractors. The strings of um as you're using strings. We need to add them in the single coat like this because if I didn't add it in a single coat, Management city owes the sequel server is going to sink if it has a keyword or colon, and it's not going to work, as you can see in your invalid colon name main. So I'm going to run. Tragic you this it thinking of it as some. So it was a colder linguine or key word, but it can't figure it out, so to tell that this is a string that I'm selecting or filtering business on it. So we need to add it with a single code, because this is the strength. If I run this right now, we have a leak. 9 11 Living is a serial nine columns on Lee the males. So I filled in my table based on the gender. But if I want to do so like he female, for example, and this is not cases that if I added it this way, man, all in give it a litter, it's going to retrieve the same result man and small letters. It's going also get me this game is all I'm going to a Cheers he man. And here it is. Now we have all the emails using the equal of the zinc goats on the value inside of it. Really interesting. So what if I want to do the late something different? I'm going to commend this your easiest move. Comment outside the lines. We'll take it at the copy, move the commitments. And there we have one line commented. Two lines commented on two are not going to change. What if I wanted Select all the males using the females? Can you figure it out? Yes. I can use you not equal on if you remember from the presentations lives you know people is this symbol in here. I know it's a little bit strange, but this is some languages and programming using this here but with equal server we use this temple in here, so I'm going to run as I'm doing now. I'm killing it that I, like select older gender, that it's not a female, so it's going to get all the males. If I don't last all the not males, it's going to get me all the email and this will work with any other colon. The name mobile number, data burst mail addressed, the Bartman, the country. Any other field ever going to say, Can you also to commit this cover your head? So I would like to do something different. Like to select. He's doing the country there. I'm going to slate country I d works. This is the name of the column equal one. Our analysts run this. It's going to get me all them please with the country off one. But what if he can? G One can go ahead and select from a different table. So it's a late Oh, from that they will that have only country names called countries were going to run this stupid hair. We need to quality old name or changes in here. Actually, you have seen how to do this. I'm going to change it to make it easier. So let's go ahead. As against ET red lines. Gun mean that everything's fine. We're good to go. That's cute. On here it is. All the countries now number one in the United States of America. So now I'm selecting all the American in peace. What if I want to select all the Let's see what we have in countries. Maybe the Russian, Chinese or the Chinese made Russian an Chinese. I'm going to show you pulse of them so But for now, let's go ahead. Actually, Russia is number four. I'm thinking Look, have been number. And he loved this one. This is just an indexing year off these equals rivers. This is the actual value. Don't mistake it by this one. Okay? I'm going to let all the countries both in keys off the country number four, which is Russia. Now we have Vladimir Answer G. That's all right. I can go ahead and play with that. Maybe the mystery. It's really maybe we have Chung from China. Medium five, it's gonna around you notice something that all right round the to select statements in here on display parts of them. So now I have these licked oh, from countries in here and I have all from the and please on now I'm selecting them five which he is Egypt If I change it to two ongoing select from Germany on the last warm Australia. Now I have holding peaceful must Really, If there is more employees gear going to be displayed. Really, very powerful feature. Accused where? So what we wanted to like pains on multiple conditions. Maybe I would like to let BNP's from country number one use 100 like like early. The females know how many female that I have from this country Do this. I need to add another condition, but I'm not going to type where again? That's totally I'm Craig. I'm going to kill it. And on the conditions were already another condition. Gender equal and again you like to specify, Andy, go ahead. On this on you. It is that we have Jessica Jonathan Onley because we have only one seaman, sir. What if I want to display the males? I can go ahead and tell it not equals email. But that's not logical to tell it this way. So I would like to keep it straight. I don't like to display old emails in country number one on who it is. It's brought it again. Only this one President Senior. So we have only the males. James Wilfred on Noah relieved this. So what if I wanted to use it was the date Do this where is she can't. That's recommend is in here. Comey. I'm just leaving this for you so that you can see the commitment on in here. I'm going to filter based on date. So I'm going to choose the column of the date which at the data burst, I mean here, I'm going to use I would like to get, for example, older employees laser a specific year, maybe. Was that Rick? Well, 1970. Well, this more isn't that this is going to work? No. Then going to work. It's wrong. As you can see in here, data is incompatible with this whole end. This is off time. Date. If you remember, as you can see in here. If I hope over it, as you can see now, have the column peak opiates date. I'm giving it in number. If I even have is it in a string? See, it's really, really hopeful that I used it. I have a string, but that's not even very logical to make it more political and readable. This is the date and one of the very powerful feature that I have in SQL Server. I can specify which court of the date that I'm using and in this board is year on that won't have a parent season here. So what a I thought that I would like to you the year off the date, which is the column, the of being here, and I'd like to compare it with this value. So let's go ahead and run this. No, I have here this This is more accurate than the string because the same computation is comparing it as a string. And that's not logically right on that Centex. Innocent ex weight violent. That's the look. Correct. So this is the way. Do that. You can use it. I can even compare it to a month. And I'm going to select maybe 10 so and like to get hold of monkeys in October, as you can see lists than soberly will income move, De, listen on the like to leave it with only 10. As you can see now I have only the from please was your date of birth is months 10 night and go ahead and choose. Maybe November. Yeah, What on? We have only one in key or even can choose and greater SSM. Thanks. For example, as you can see now we have this live in 10 12 line eight on another night. So this is how you can use part of the times. I can go ahead and also tease. They like to even ask for a specific day so I can go ahead. I would like to select only in peas. Well, they burst is maybe one the first day of the months. It's Excuse this and you have stream. Please. We have John Lennon and Miami have 11 and another one. Maybe you can go out and try to I don't know if two does exist in that, but let's figure it out. No, there was no 23 no stream, maybe 10. Anything through is a 10 condemnable 10. Here it is. We have one, probably only on born and they number 10. It's not asking about the months or the year on Lee. Today I can go ahead and ask also about the once so I can go ahead and the months off the Beaubien doesn't matter that you use it in caps, look or small. Well, I'm going to ask the months given to be well, for example so that I have Sergey again. If I run this, we get so even if one change in the months to maybe two February and we I'm not going to get any employees because there is no in please board on this day. As you can see from our data, I'm going to get any employees. So it's really nice to use the day, months and year and the work clothes very powerful feature. It's really very helpful working in rewards vary, so that's it for the work clothes. See your NZ next, but you 30. Exercise Select Data From AdventureWorks Based on Conditions: Okay, Tom, First World exercise in this exercise, anything to go ahead and answer linked data is when the adventure works that varies based on specific conditions. I want you to go ahead and select data from the product table that we've used in the previous exercise. But this time I would like you to filter it by the color. Select any product with the color sliver on the selling Certain months is May, no matter what the year is. So go ahead. Those a video. I'm trying to do it in your own. Okay, it's easy. Let's go ahead and trying to do together. So I'm going to open a new query and then we're going to select all. We're going to specify any columns from the protection schemer in the product. It will side of it on going to specify where the color equal silver and to specify the months off the sale Historic. They'd go ahead and at the monies. Andi cell Stardate, here it is equal months, five, which is may less excuse this on. Here it is. We have a list of products that matches this condition. So here it is. Where is the color here it is the color on them selecting all the data as you can see, if we check the so Stardate here it is. It's going to be always made no matter what the day, no matter what. The year as we can see that we have 7 11 2000 12 years on 13. But it's getting me what I want. So that's it for this exercise to you in the next lecture. 31. Order By: we're gonna discuss about the select statement only where, without mentioning the very important key word, which is affordable. What boy is used to sort of they retrieved from the database in ascending or descending order based on a colon that you can select or multiple columns. And by the fold, it's in an ascending order. Let's go ahead and see this action in here. I'm going to get in. You curry like old in please. Oh, from he's is these are change you into working Antonin Resources. And here it is. There is the 14 becomes that we have in this table. Really wanted Teoh read Sorted, as you can see. No, it's sort of based all this You're in the Chianti. Whatever I wanted to sort of based on the it'll birds or the country or any other field. How can I do this, sir? So it's very simple. I'm in here and until it order by, as you can see it turning blue I have to add this to call in order. But I am. They need specify. He called him. So maybe I would like to use e gender. I would like to see maybe the male first or the females first. It's as you can see, it's shorting it in an alphabetic way. On the F is before the m so that we have older females. Then we have the males. What if I wanted to sort ahead based on the country I d had in here into the country? I d Let's do this. Now we have 123456 Really interesting. What about the date? Randall it the reverse and then we have it sort of all right for me. All this to news the interesting. But what if I wanted the other way around? I would like to see maybe 1990 on the top. Andi. Others down. As you can see if you notice him. Employee I d is changing because now it's not the key or the colon to them sorting it based on. So I'm going to have this symbol keyword, which is an appreciation for descending. It's going to sort of an descending way. As you can see in here Now I have the dates shorted in a descending weight. Very easy to use and very powerful. But what if I wanted to use multiple sorting feels a Kahlo's. Can I do this course? You cancer? I would like to sort it based on the country idea. So I would like to comfortable sorted. First, let's try this country. I d. And then I would like to display the females first. So in here in the country I d number one I have male, male and then female. Then may I would like to display the males, the females. Actually, first, I'm going to add a comment. I'm going to let this play or sore it. Actually, based on the gender tried us on your it is we have number one for then we have the females . What if I wanted to reverse it? I'd like to display the male. The first gonna do this course you can have in here and used descending on. You're going to see that we have the three males first and country number one. Then we have the mail really powerful and they can keep adding elements. I can go ahead and at the universe sorted with the country gender at little burst. It's not going to make any difference right now. He we're going to have very few record, but if you have a lot of fragrance, it's going to make very big change took that's it for the order by for left to you in the next. 32. Create Table From Existing Table: in the sexual regard to see how to great table in a very unique and interesting way. We go into great table from on already existing table. So that's a change. The data and the M pleasing. Fearful. I'm going to select old from please on them going to display them. So maybe a reward scenario out like to create a table only for the employees from each country. Maybe United States. So I'm going to sort it order by country I d been going to stewed There's again So we have all the employees sorted by the country rd on. If we check the table countries so like, oh, countries, we can see that number one is you stay to Germany. So maybe I'm going to create a separate table for the M please from the United States. If you're going to do this in the norm of ways to create table or using measure in studio to going to take a little bit of Luton so we can use very special technique in the SQL servers that will enable us to do this very easy and seeing the light. So let's go ahead and see this, so I'm going to do? I'm going to select That's actually removed. This is that you don't get confused. So select estrus and then they're going to choose from, or any of anything. I'm going to time in tow. Andi, In here after the inter, I'm going to specify the name off the new tables. I'm going to choose you. Say underscore. How? Example In, please. This is the name off the new table on right now. I don't have this table in here, so if I check the table, the refresh it, I don't have it. I have only the empties. There is no use. A so its continue on. And from where? The structure off years eight and please is going to come. It's from the table. Please, Andi, I can go ahead and that The condition. I can just leave it as it is, and it's going to create the same table with the data in it. I'm going toe filter it. Ongoing the country I d equal want. What I've just did in here is I selected the data holes like old records, putting it into a new technical. You say employees, it's going to be created. Trust management studio of the Secret Service going to created for us without us creating any time, it's going to be the same as the structure of the employees and the same data on the filter that I'm going to use is the country I d number one I, Michael, leave it without any shelter. But I'm going. Juice volleyed, you see, And please. So let's go ahead and execute this. A four rows effected, as you can see, never goes the m please from the use A were four. Here it is. We can see that we have a new created table. Cold use am please. If I select the day different is they also select all columns from you say score and please , As you can see now the elements to you can see it that's executed on Here it is. We have a new table graded for us with the same elements, the same to the time to say everything without the keys. It didn't create the keys for us. We should have. We should go and create the keys, our service. But this is a huge at progress for us. I didn't call the data. I didn't create the name the targets created for us in this very small lines with interesting. Let's go ahead and drop the tape. So I'm going to drop it rob table. You see if he's let's try it with begin of the country. So I'm going to fund this same query on I'm going to choose a different country. Maybe country number 400 number four, I guess, is Russia. I'm going to say Russian movies. So I'm going to users right now. It's going to be an error on the table because the table doesn't exist anymore. It is deleted. There is no people called using these. It's excused this so that we can have Russian. Of these separated into table, there are only two, please, from Russia. So here it is. We have Russian room, please. Yes, we check it. Select. Think I go ahead on selected using the management city quick. So like I'm here is, as you can see, the same structure on doing Please only curated for us because that's based on our condition. Let's try to created reality conditions. I'm going to remove this and I'm going maybe undoing some temporary rewards. I'm going to call this temple, please Maybe, um, get I think something or doing anything or any musical business saying on that I'm going to believe. So I'm going to select all into the 10 tables from employees without any condition. So it's going to take all the 14 record that I have on place it in the new created table called Tim Skewed It early. Fresh. Andi here it's 10 employees selected on here is same structure the same that because we didn't have any to I hope that you enjoyed this. See, you consider 33. Exercise Create Table From Another Table: Okay, let's have a quick exercise in this exercise. I need you to go ahead and curated able from another table, and I'm going to tell you the details right now. So I needed to go ahead on great a new table called blaring in please drive from the in, please. Table. This table should contain the inferiority. First name, last name department. I D hand It's going to contain only the Blanning in. Please go ahead on trying to cigarette out in your okay. Who is easy. Let's go ahead and try to solve this together. So first I need to know which number in the blending eso as again seen here. I have number. I don't know which one in the planning. 30804 So I'm going to check it in the department's table seen here. So the blending would be number eight. That's good. Let's go ahead and start building the new table using the technique that I showed you. So I'm going to select the for column. The employee I d First key already then the first name in the last name number. Important idea. Great. So now I'm going to create this into a new table cold, letting underscore and peas from the empty stable. You're going to get the data and the structure from the MP stable, where the apartment I d equal eight so that I can select on Lee the people working for the landing department. Five. That's okay. Let's go ahead and execute this. Let's refresh our tables. We should have a new table here it is planning and believe if we selected, we can see that it contained the four column that we've created. The MP I D. First name, last name, department idea. And only the people working for the Blanning, which are the Lin time. Emily. So you would just swim on t you in the next next? 34. And, Or, Not, Is Null: we'll come back and sticks. You re going to discuss, End or not is Mel's keywords and sequel server so we can introduce the hand as it gets a record if all the conditions separated by the and S two root. So the key word here that all the conditions must be true or, on the other hand, gets record if any off the conditions paraded by the Ori is true. So if I have multiple conditions as we're going to see if any one of them is true, then we can get regular not from its name. It's like the opposite gets regular if the condition over the conditions is not true, is no also from its name gets regularly. If the data is said to know as we're going to see let's go ahead and see this election. Okay, let's go ahead and try to select from again. Oh, and please table late. So he Raisi for team record with me half for now. What if I wanted to display maybe country number 12 on six, for example? How can I do this if I used the and it's not going to work and I need you to focus with me here. So if I'm going to say where country I d equal one deaths pretty easy and obvious, it's going to retrieve the data off the country of one or an employee that had country number one assigned to it. And as I would like to display country number two, you might sing that we should use hand country I d will do. That's not right. If we execute this, we're going to get nothing. Even if I change it to street, it's not going to work cause that's he this conception. If I can say this now, sequel server is going to search for a record that has country I d Will one Ansari for the same record on that's off course group. We can have xem separated by or so I have the country I d one or am Ready Will street So I can now have them. We have your GP one on number streak I'm making coming here and even at more country Heidi equal do, for example, for six. As we said so then we have And these were gonna go ahead, of course, and orders him for coming here. Order by country. I d skewed it on. Here it is. You have someone Please them from country someone The mystery am a six. Okay, let's take it to the next level. Whatever we wanted to select, only the neck is Or is he the next to query would be only males but the females or the female if I want to. How can I do this if I go untie bit like this as you know it sink Now I need to add another condition. So I'm going to use Andi the gender equal and again when you to using coat, for example. Mail going toe work very correctly that Steve. So here we have female in country number one number three, but not number six. Anyone can guess why. So what? What really happened here is now we have one condition. So secret servers taking a look at this. It's it's translating this one point. Run a piece by me. Sir, we have country ie equal one. Then we have another condition, which is people three. So that's the next condition. Then we have country idea. Will six onda gender equal mental. That's a whole condition. It's taking the on and gender equal men was only the country I d. So if I move this from here So I'm here now. The female and country number three, which is one, for example, is not going to be displayed. As you can see, it was It is gone. I and I have female in number six. If I move it up here to country one and this we have one too street for records on. Just get as from country of one. If we execute this, just get is going to disappear. So how can I sold this? How can I make it? Take all these three conditions. I don't think the female I'm not assigning, of course, all of them. So I may be some little thing that I need to come in here at it and here and Anderson here . But that's not logical. Of course it's not going to work like this if you have a loan conditions, so all you have to do is just add the parents. He's starting here and here. Now it's going to validate or perform this condition this whole condition first and then compare it with this. So it's going to get me all the employees without taking a look to the dinner for these three countries or to four countries. If I if I have no whatever condition I have that it's going to filter it again with the second condition that I have, which in this case, is the gender equal man. Let's go ahead and try to execute this. Um, all the females will be gone on defeat. Diversity would like to choose female. And I'm gonna get only the females for this saree countries. Did you get it? Let's go ahead and continue Sing. So going actually talk about the not operator. Let's take a look at how being locked Operator. We have seen how it works in a different way, as we're going to see in a bit. So what if I wanted select old the please without the mail, All the females. So I want to do this Western how to do this? We have seen that we can use the gender and we have seen this symbol here mean that not equal so I can get it like this. Okay, now I'm feeling that I needle the cold items from the n please where the gender is not equal male, which means to me, man Okay, Or I can make it male. And as I told you, have seen this before. But we didn't use them, not keyword. So if I would like to use in, not keyword I consumed, we remove this. Make it normal equal on Adam. Nothing here. So we're not gender equal female. I know it looks a little bit complicated, but this is work. This is giving me the same result. There is no females in here. So if I say mail, let's go ahead and execute. This is going to get me all the females. That is the opposite off the condition. If I remove this to going to get me all the males. Okay, so I added. But again, it's going to get me. All the females hope that you get what are the options also, that we can use that continues. There is some know that that's that's not space that's not empty. It's It's long, it's This is very descriptive. It's Mulder is nothing. It's it's void so I can come in here. It's commend this I'm going can do that. I would like to select ace. Any plea was status. I d is. No. So right now I'm gonna get cold records. Will older record that I have are no in here, the status I d and the remarks. So I cannot quite good. Let's go ahead and take a look at a different table so I can come in here and select from the from the debate. So select old from person. Let's go. Person is in. The adventure would decorate. So I'm going to choose Adventure Works Database person and the world's Jews came Gold prison on display All the records on If you see in here, there is this title Call them and some of the titles are not given its Not so we have Mrs Mr Miss, but some are missing. So I again coming here and simply filter it. So where vital is no symbol of that? Go ahead and run. This on really is We are gonna get all the columns, Old rose with the low values. As you can see, I can go ahead also and reverse this Using is not know. So now it's gonna get me any record with the value as you can see in here. Now we have Mr Mrs Miss Sir Senior. Any title that we have is being displayed with Saudi. No value. So that's it about the four operators. Andi or not is no. I hope that you get it and see unions the next picture. 35. Exercise And, Or, Not, Is Null: Okay, time First full exercise about the operators that we talked about the and that door incinerator. So but I need you to do in this exercise is go ahead and select product I d. Name product number color CellStar date from products table in the adventure Works database . But based on some conditions, go ahead and select any product with the colors. Silver or red Onda selling, Sir. Day would be to 78 old to 7 11 and finally was in boy color on Scylla. Stardate took its go ahead. I'm trying to do it Green. It was easy. That's why I had them trying to do this together. So first I'm going to select the product I d. The name, the product number. He color these cells tart beat from venture works. I'm going for the full pass hits and he's chemical production. If you remember Table called Product, let's just this. So now we have old Baker. Okay, now let's filters. I'm so first I'm going to add the color certain I'm going at appearance. These two groups the colors together, so the color the 1st 1 would be silver and I'm going to use or as we have seen in the previous lectures. Air color Equal read. So now I'm gonna get all the products. Was the silver 100 color as you can see in here. But I don't have the date that I wanted to show is in eight on the details and the lemon. I have £2012 Sirti. And so I don't one of these. So I'm going to on the condition Andi again Currencies year. I'm going to ask about the year off the cells authority. I'm gonna compare it with the value of two citizen and ate, and I'm gonna again on your year. I'm gonna use the cell started again, and I'm gonna compare it with the value to says in 11. Then look, finally, I'm going to order results in order, boy color on a cell toward the Let's take what we have. So here it is that we have now old the Reds. We have only one product mis read into those eight be rest from 2011 then that we have these silver color. There is a lot of products into 2008 trying to expand this great On that note, I say Now we have the correct result. So that's it then to you in the next lecture 36. Like and Wildcards: okay. Announced Time was one of the very part of features of sequel server. It is the like, handing boiled card. So what is like on boil cars like to the minds wizard specific character stream matches a specific pattern or not. A barren can include regular characters on boiled court characters. What is the world cornered character? Wildcard director is used to substitute any other character or characters in a string. There is plenty of ways and Petters, as we going to see. Let's start with these two. The underscore allows you to match Onley single character. The percentage signing here allows you to match any string off any limbs, including the zero. There is a couple more anyone going to discuss them later. But let's start with this. So let's go ahead on. And maybe I would like to select data from a table on day. Let's say that I'm searching for a name for a country name some string, and I don't remember the characters over. I'm searching for any employee whose name ends with an E, for example, How can I do this? So let's try to take it one by one, so I'm going to select. Oh, from And please um let's see the result from this. So here we have all the names without any nothing. You in here? So what if I wanted select all the employees that the last two characters off their name is E and then and it will match on Lee this employee in here? Lim, how can I select this? I cannot come in here until it where? First name Equal E. M. It's not going to work, but I can modify to be like this. Like on here. I am going to use one of the world cracks, which in the underscore, which is going to search for only single character. So if it's in or D or if any names going to have. If we have it, we have only Lynn. If we have Gen, it's going to be displayed. If we have any other name friend than any other name or string that matches this pattern here, it's going to be displayed. So what? I just did it just I'd replaced the equals on here with the like and used the one cards Very powerful. Let's try something, Mom, What if I wanted to get all the in please starts with was DePue litter. So if I add this in here and added the underscored this is going to give me all the first name and please whose first letters w then we have one more correct only one more. And that's not what I want. What I want is to get any name, actually, so I'm going to type the second world current, which I think it's more useful, which is he percentage in here. So I'm going to execute this and see that we have Well, Fred and long. Really interesting. But if I want that change, w again. Yea yeah, valley. If I wanted to get J now we have gone James and Dishy. It's not searching for one directory shooting. So here we haven't join me, have always three letters after the day in James, we have 400 ago. We have more. So it's very powerful to use the like with the percent of signing here. So good night. Compose even more complicated wild cards. Yes, you can. So I can come in here until like to display the improves. The name s no matter. The s is located. Maybe It's in the first of the name. It's in the middle. It's in the last letter. I don't care. I like any name or in string or any first name with the S letter. So I'm going to add another one in here. So also here's going to search this here this better. It's going to search for any number off characters before the S on after. Yes, go ahead and execute this. So here it is. Yes, and James Sophia is on the first letter. Jessica, it's in the middle. 30 here is also in the front, so it's it's really it's giving a lot of options, a lot of search results that you can use and indeed life scenarios. It's used frequently the, like Andi wild card, and we're going to see a little bit more complicated as we going as resource more complicated scenarios with the wild card. So that's it for now, to even see next lecture 37. Exercise Like and Wildcards: Okay, Time for a small exercise about like on wild cards. What I need you to do in this exercise go ahead on and using the Adventure Works database select the table called Address from this key Mac old person in for the adventure Works there debates. So, like the following fields at his i. D address line one city state province idee. Based on this two conditions, the city's Redmond on the address contains the litter. You go ahead most of video trying to do it on your own. Okay, it's easy. Let's go ahead and try to do it. So he right have the adventure works selected. I'm going said, like the elements of the address i d. The address. My one the city. Then we have this late presents I d kids. That's correct. It's going to be removed This red lining here when we select the table toe from the schema person the table address. Now go. Just if we selected now we have the address I d That is the key. This is the address. It sells the city armed, the State province Ivy. Don't. What I'm going to do is tell it that this city equal. Redmond on. Don't forget to add these single colon. Andi, the address line one. It's not going to eat Well, if you remember, we need to. Do you like to use a wild course? And I'm going to add sign and the fund on in the end between ongoing used you let her year . As you know, you have only the city. Rettenmund, how do you have the ADDers line on Lee with the you that you can see here We have Cord Funt in court again. I don't know whether this so here it is. It is done. But let me show you something else. You see here the state Romans pointy. It's an I d. Like we were designing our database. This here This number means something else in another table. So let's take a look at the table of the province in here. So I'm going to go for the tables. Go The person schema we have the Here it is, the person stayed province. Go ahead and select it on. As you can see, each number represent an i. D of the state or the abbreviation for the state and the full name on to territory already . That's in it. As as as we were designing the emotions database. Each number refers to the number in another table using the primary key and the foreign key on it has a full information about it. So if I go to I remember, I believe it's 79. Here it is where Redmond is located. If we planted one, I need to go ahead and select without condition. Let's go, for example. For Seattle, your issue. It's also George Anthony's. Maybe Miami. We have 15. Number 15. Go up on the way. Here it is. It's in Florida. That's how you design relational databases to you in the next Lichter. 38. Advanced Like and wildcards: come back and this next we're going to discuss more about the like and wild corridors. Rafferty told you that I need to display something like this. All the names or the first name starting was a no matter what the letters and the remaining letters off of the A is maybe 123 or even 10 letters on the B, C, D and eat, no matter what other letters after this. Letters on Lee, the You don't want the G at your I Only these letters, for example, I would like to get older name. Starting with this, five letters probably would be doing something like this. Select hold from a please were the first name like a then our wild garden here, just selecting whether what letters are for the A one for the B 14 to see one for the one for the heat. It's working as you can see in here Got me Emily, Ellie and turn. Well, that's not the best way to do with it. It's long. What if I want to get more letters, maybe 10 letter so or even with city that like to do with cities or country? It's it's It's not the best way to do it. So we can use another wild cars. I'm going toe like it. I guess it can go ahead and do this select all sort of, please. Okay, Sam, I'm going to fill it Where he first name. Like Andi. This is old normal. Here we've been using. We can use this. We're bracket inside of the single coat on. Then I'm going to tell it. I would like to go ahead on Suresh from a to E as the first letter is the first letter. So sequel server is going to search for a B, C, D E, then NZ using this other wild card. Let's run this execute on you. It is we have the same result. If I run parts of them to make sure the result is identical if by increased range from E to maybe our I'm going all the way to the are on going to rob us of them than the second query has more than three more than the 1st 1 called. The letters are increasing. I have the day with me. The LDN have the m. If I went toe the Z letter that I'm going all the way to get old. The names as against you know, I have a lot of names, so I hope that you like it. It's it's very elegant on very nice feature that you have. Also, you can go ahead and do the opposite, so I'm back to a and e getting oldest re in, please, which are Chung Ali on Emily. If I don't want to display this three and please for the reason in my query, I can come in here and add Czyz later. I know it's a strange for uses called Carrot. It's on your keyboard. It's on number six. Go ahead, Just impressed. Shift on sixth Sense going to display this letter for you. This is going to get me the opposite. It's likely not or not equal. So now I'm gonna get all the employees, and I have 11 records without the three, which are Chung. Ali and Emily are not here in the bottom query. So that's how can you use more advances, wild currents, as we have seen to you in the next lecture 39. In: Welcome back in this lecture, we have one of the very powerful sequel keywords it is he in. I know it's small, but it's very powerful. Let's go ahead and see it in action. So women, when we uses a like data like this, select all from a please a table. Employees in the human resource is whether country one, 4 to 6 or even four or five or 10. That's not the best way to do it. What if I wanted selecting countries or when he enters its going to be very long lines that the very best way to do it? So how can I improve this? Take me and get the same results as you can see and hear how the countries one do on six. Let's take a look so I can come in here assembly and say, Select all from him Please table where the country And now he made the difference. I'm going to used powerful keyword in open and close. A parent sees on start timing the value that I am seeking, I'm searching for, so I'm going to kill it. Like this example one. And your spray aided by corrupt too six over by country. I d It's cute, Aaron, it is. We have the same result. I couldn't keep adding they don't have to be in the same order. So I can come in here and say five or three and four announced, making all of the hunts removed for one hour. Yes, you do. Now we have all the countries, except for 41235 and six. Using this very small and more technique, I can also use it with enough. I can come in here and make the opposite. So where the country Not in these values. If I run this right now, I'm gonna get only country number four. See how smart it is. So, uh, I can use it in a different ways. I can come in here and select all throat cities. Um, also like the table cities. Remember, Table Cities has names for all the cities that we have our emotions. That race for this six countries that we have. I've been coming here and tell it where the city name you and I'm gonna give it a couple of values. So in here, I can come in here until it you your for example, And as it is a string, I have to include it inside of a single coat. Don't forget this hand separated by commas. Berlin. Since I have the second value that I'm searching for, maybe the serve value would be Kyra. So what I just did in here that I'm asking the SQL server to go on search for Z cities in this table. You, York, Berlin and Kyra. Let's go ahead and run this on. Here it is. We have New York, Berlin on and Cairo on. Also, I can do the opposite thing not in. So I'm gonna get all the countries, all the cities, except for these three cities. As you can see me, I have a lot of the city's, except for a street. So that said for the to you in the next lifter. 40. Between: continue our discussion about the very powerful keywords and sequel server that make us able to perform very professional and very easy and quick sequel. Select statements we have between the Twinkie word enable us to select from a range very easily. So let's take a look. You have the data from the job stable and we have the job I d title, minimum salary, maximum salary and is managerial and marks whatever. He wanted to select the range. Maybe the minimum salary back to select from 2000 to 3 South and I can do this. It got It can be done using the Lizza real reason, but it's easier, much easier using the between keyboard. Let's go ahead and do it so I can come in here until it where Jim minimum salary between. I'm going to give it a value. Maybe 2000 Andi 3000. But I just didn't here and just I added the coal, Um, that I'm going to filter it or make the condition based upon it. Andi, I added the between keyword a minimum value and keyword the maximum value that I want. Let's go ahead and run this. So then we have this. We're gonna go ahead. SportsCenter sorted order by the job. Many moves that considered a better result. Scoot. So here it is. We have 2050 on 3000. I can go ahead out in in largest. Make it for sounding for example. Go ahead and run this. Now we have the 4000 added on this 37 1st 100 As you can see, I can go ahead and even increases more. Now we have still four sounds and 1st 100 I can go ahead and also increase this maybe two ST 1050 on 57 Now we have starting from 3050. That's very easy to do to select some ranges. And I can also do with not so I can come in here. Let's go back to make it easy. £2000 resell. This is probably the most its future. We have this four values. I am coming you until it the non between. So it's gonna display all the values. Not between this range. In here. The two thousands resell on the trailers. So now we're going to start from this 3500 until we rich. Well, 1000 Hey, I can go ahead. Of course, our shoulders you while you may be 3000 on 6000 So it's going to neglect a lot. We have now the 2400. It's lower than its resultant where I'm starting from. And then we have what's higher than these sex cells? Did you get it? It's very powerful and absolutely going to use it. It it comes in handy and really, really useful situation that you're going to use it. That's it for between two unions in next picture. 41. Exercise Between and In: How about an exercise about the between on in keywords. So you to go ahead and select all the columns from the table in, please. And the adventure works, Not the employees and the human resources based on these conditions be hired A from the start of 2010 toward the end of 2011 excluding all the pyre on general jobs from the selection. Go ahead. Those a video trying to do it. Okay, which is easy. Let's go ahead and try to do it. So I'm going to select full from adventure works on going to select. The human resource is there is East key Merkel, human resources. It's love the human resources database. It's a scheming Don't go into left complete. So the where statements going to be like this where the higher date between as we have seen the between before and I'm gonna give it a date on the date. I'm gonna give it inside of a single cards. Okay? So I'm gonna say 2000 10 as we have seen before. The date is taken in here as this format year, months day. So the starters 2010 would be one on one. Okay. And the higher already knew. You say the higher Just go ahead. And at the value, the maximum value or the other value of the between. Going to be 2011 ongoing beat months. Well, which is December 30 is it, sir? You're 31. Guess so. Let's go ahead on and test it right now. Okay. I guess now the values air going Very good. Let's go ahead and ordered by the will be hard eight order by higher late, maybe descending. That's excuse s So here it is years old. Values from only 2010 on 2000 11. Me to add one more As we can see in here, we have some jobs that would like to exclude the buyer on janitor. I would like to remove them from these election. So, you know, I'm going to another hand, the job title Not in. I'm gonna give it the titles where there is plenty of ways to do this. But that's the way that I'm going to do it. Danaher, go ahead and run. It's cute. Here it is. Now the pyre and dinner has bean removed from selection. While we have the range of date that you would like if you would like to distance. So here we have 31 off. May. We can come in here and make it Maybe for a bro. Now, this year is going to This is here. Yeah. Okay. We don't have Sirti. One favorite senior secret service. Very smaller. The error here was mentioning that death. That's wrong. That's the developed date. It's very good. So here it is. Now, the May have gone because we didn't include it in their range. So that's it for the exercise. See you in the next lecture. 42. Distinct: welcome back and this picture we're going to discuss and you keyword called Distinct. This thing's is used with select statement. Doesn't going to see it's used like this. Select distinct. He's used to get only distinct all the different values in table. Probably you would have a public it values in the table, the names or countries or products as we're going to see then that like to retrieve on Lee the unique values. Let's go ahead and see this. So I'm going to select So, like first name from the adventure words database. I'm going to go to just limit person, um, to table person. So I'm selecting only the first name, not only colors. Here it is now. As you can see, I have three cans. There is pillar. Nicknames are locked, unique, as you can see in here. What if I wanted to display I unique names, so if the name is duplicated, I would like it to be displayed on Lee once to maybe know all the names that I'm dealing with. Or, for example, if I'm dealing with cities or countries, I don't want to see duplicate values, so simply we can do this lightness. But first, let me order this order by first name for World is going to be a bit different. Yes, as you can see, we have a lot of errands. Erin Erin, Abby Appiah's agency, Abigail's are I would like to display this, and if you notice he read the records, I can see that that's a lot that's going to change when I am only adding to the stinky word like that. So I'm going to type select, and I'm going to add the key. Work this, I think, as you can see. Turn blue. First name. I'm going. If I would like to display more than the first name, I can go ahead and keep adding them from adventure works. Database. Personal schema, he person table. I'm going toe orders in order. Why First name? It's better that you have it separated into multiple line like this is it's It's more readable. Now let's go ahead and run the one with the distinct I know just Mulkey worried about. It kind of changed a lot, so as again, you know, we have one. Aren one Abby, 1 a.m. Here, one Adam. And if you noticed through the number of the rose and reduce dramatically. Let's compare it with the other one again. Run that. The one that would do this. Think that you can see it's a lot of records. Let us duplicated first names, as you can see in here when it was instinct, it's going to be much, much better if I would like where I'm seeking this kind of very that I would like to see only one name. I don't want to see all of them. If you see Alexander in here, I'm sure that it's going to be different letters. Let's open it neared. Yes, it's Alexander. Alexandra Alexander was a Alexandria, so that's it for the stinky word. It's very powerful, I guess, on to you in the next election. 43. Percent: Okay. This lecture is going to be quick. I'm going to discuss the percent. I can go ahead and display a percentage of the recommended 1/2. So if I'm going to select all the M please, from the in, please table what we use. Either I'll have 14. I can go ahead and add this very symbol. Key word. Tom. Tim Person Den E. Astra's what this is has don't like where is it's going to get 10% off. The record is available for me. If I try to excuse this right out, I'm gonna get 10%. Which is, too. If I try to make it 50 which is half on, we have 14 that. I'm gonna get seven, as you can see on defy, uh, 60. Gonna get £60 cigarette. Not 60 actually, nine. It depends on the rose. The number froze. If let me try it with the city's, for example, If I try to get along the city's because I have a lot of cities on this table, I'm going to choose 100% of it. That's excuse this. As you can see in here, we have 100 45. Let's try to make it on Lee. 10% Which is gonna get me only 15 rows or records in here. So it xlat a big of a deal, but it can be used to limit the records. Would like to retrieve some random records. Percentage off records in your database has told you to go to be quick on That's it to you in the next election. 44. Change The Rule of 1000: okay, in this lecture, we're going to see how to change the rule of the 1000. I know this sounds weird, but don't created and open another course. It's still the sequel server course, and let's go ahead and see this. So here I have the cable city trees and Ready T will go ahead and choose. Anything will let you like. If I choose right click, select a 1000 rows, it's hard to get me. It's up 1000 roses course. We don't have one cells in a row, but if I have them, I'm gonna get all the top one sound. Let's try. It was maybe the person's and the adventure works in here in the scheme a person. I'm gonna get only 1000 and you can see here displaying only one sound record. If I tried to open or maybe 5000 it's gonna get me for life 1000. Does it have a lot of tables? Probably will need to remove. This is be years, if you like, and remove the top keyword also. Ah, here is old and records a lot, So how can I, by default, make it display all the records without me removing it. It's I was saying that it's good for performance if you have a table that has a lot of frequents and you don't want by default to retrieve older rivers. But it's not Yeah case always. So let's go ahead and see. How can we remove this 1000 here, sir, from tools in here Go to options, then go to sequel server Object Explorer. Then we will have. That's mean you in here from the table and views options we can see we have this option value for select top end Rose Koeman on We have 1000 thinking I didn't change it. So maybe 5000 on now when I'm going to select, as you can see select 5000 rows. Gonna get me 57 murders. But what if I wanted to remove it? I don't want to display 10 or five. I just display all of them. Just go ahead on like zero. It's your end. The description specifying a value of zero Return is older rose. Go ahead of him. Choose it. Greg Lake, select on. Here it is. We having a lower records and we don't have your select old rooms will have one sound. 105 sound. That's a small little life street to you in the next life. 45. Union Operator: So let's imagine. What if I wanted to select, for example, the name of em, please, from one table on. Maybe have another database that contains named for employees or customers. And I would like to display the names in one list from the customers on them, please, or from customers and little Bates and customers in another database. How can I do this simply? We can use that the greater hold union. What does the union greater do? It's used to complains results from two or more queries for select statements, but there are some rules are going to follow. Each select statement must have the same number of cold room, so that if the first select statements is getting me five columns, these second sequel statement or select statement must have also five colors. The columns must have also the same data time. Let's go ahead and take a look at this example, so I'm here. I'm selecting from the person table guns, personal schema and adventure works database these names Maya, Vladimir Lenin long on Ali and all of this names does love exist in this table? This is the table that storing information about the persons There is no records for this. And please, all this names in here on this I so like bee's name now, Sofia, John, James, Noah and Emily. Now, as you can see have a lot of records for all of these names on what we have in common in here. Between these names, all of these names are in my table in the human resources of that of a So if I select, let's select the first name on hand. Selecting the first name from the human resource is database Earl, I'm going for the DB o before schema on the time selecting the country is I don't find that all of these names are in here. So can I compile ein the names from here? The ones that doesn't exist in the person table from the in. Please Have they told you maybe Z's our names for a different in police and different elevated under like to prevent them in one report in a query. Or maybe the or names of customers on I and please, for example, on I would like to see them also in one gravy or report. So let's go ahead and see how can we do this so simply, I'm going to build on their self select first name for Complete that. I'm going to the operator union. As you can see, it turned blue to me. It's a keyword that I'm going to select First name also from the adventure works that raise the person is schemer the person that base I'm going to order. Why the first order by first? No good. It's for only the last one. Don't see, But this will get us there simply this year. We have all the names from the person stable in the vigil. Works on the names from its Remember we didn't have Ali. If he goes search for only now we can see that we have. Where are you? Only just expand this little. Yes, here it is. We have Ali and near. If we go search for maybe one go ahead of Florida flight. Amir, if we gold away down to the where are a little more? Here is we have fled ever so old And names now are compliant only that we cannot see all of him because there is a lot of names in here. But all the names that we didn't have in the Burton. Now you're being displayed because what I just did, What union did it just combined? The names from the M. Please add it to the persons. So then we have weary that contains parts of this results. But if you take a look at this, we have only one Erin, one abbey want Ellie? When Adam. What is union actually is doing in selecting the distinct value? Only the different values. If there is implication or duplicated value or a name, it's going to display only one. If I would like to display older, rigorous, for some reason, I'm going to at this multi word old union old. Let's like it again on Sees a Difference and CP count that we have only 1027. Let's excuse this, and we're going to have a lot of records in your other country. We have a lot of errands. A lot of Abby you love ever go on all the other name because now it's not selecting and stinking values, as you may have noticed. Also one of the rule that it follows. If I selected from here the last name and I try to run this. Let's see what's going to happen. It's gonna go go on air. Old inquiries combined, using union or instinct to accept. All of the supporters are going to see them. Must have an equal number off the expression in their target lists. And I told you one of the rule that we need to follow that we need to match the numbers if we have to call them selected. Both of the statements must have to Colin not believe one of them's going in here. I must come and and the last name. I run this right now. It's Kate. Here it is. Now it's running without a problem because we added Bubs. So that's it about the union. For now, it's one of the very important and helpful key words. I think on that it to you in the next picture 46. Exercise Union Operator: Welcome back. We have full xsl is about the union key work this exercise I needed to go ahead and select the State province already the country region code The name from schemer person in a new table. Cold State Province in the adventure works that base. Then go ahead and select the city. I d. Sit name from the city's table in the human resource is the base and go ahead. I'm trying to had both of the results from the select statements using the union operator. Andi, you're going to run into a problem. Go ahead on trying to fix it in your own. Okay? It was easy. Let's go ahead and trying to do this and fix the problem. So I'm going to select in ST Prince Pro fighting the country region curled. Mister, I'm just getting this technique in here. Capitalize the first letter so that it's more readable. Well, then we have the name from the adventurous database. The person s schema on these 28 Let's try this. Great refers I'm saying gets work. Bringing them here is only name the I ds and Region Code. Okay. I have been trying to use union on a guarantee so late this city I d God. Okay, Ally. In this city, I d destiny here in the naming cold delicates earned. Then we going to use city name from the human resource is the rays and the TV Oh, skin man on the city's. If we try only this about running older, very religious. As you can see up selecting only two lines Execute on your is It's working now Let's try both of them. Execute error. You remember that I told you when we were discussing the union creator that the number of columns must be the same. So now we have three columns in the first query on and in the second square we have only two choked. That's a problem. How can we fix this? I'm going to solve it in a very easy way for us so I can come in here. Just remove this, that I don't need form. That's not maybe what you're waiting for. So it's fix it on every agency that we have pulled. This city's combined together. So that's the easy water that let the best one that we can use of a solution. So let's go back at the and those I believe the country you go. Yes, that's correct. Let's display the door again. Here it is so I can come in here on at whatever we won't. Even it's much from a query from the database or a record stole in this survey. So I will add a common here as I'm adding a new called him, and I'm gonna make it fix it. I'm gonna live here. Not available on may be just highlighted was that she says that we can recognize it when we have. So now what I'm doing is that displaying the ivy in here than the region code, then the lane on the in. The second query. I have the idea again, the name. But I don't have the region code. It's a belter. It's good that I'm showing that it's not available. Execute. Here It is, as you can see, that we have see a for a border, but we have a lot of 11 for New York. Good. It's coming from a different table, as you can see in here. So it's one of the ways to fix this problem. Of course, it's one of the best thing in the world. It must be matching that that we have e consistent data, a better battery in for the data. But sometimes you find instead of that you're going to select 10 columns from a table because the structure off the tables are different on your going to added for uniting it with an eight coal query. Over a result of this course, they can go ahead and just of the dashes. Leave it with that available. But it's more appealing to the eye, as you can see here, so they have regulate the trick and see you in the next picture. 47. Intersect and Except: do other operators that we need to mention After we discussed the union, You are the Intersect. On the exam, Insect simply returns. It stink grows that our output by bosses. He left hand the right embodied query, separator. We're going to see this And this town that deeply except returns distinct draws from the left image query that aren't out book by the right in both. Query, What does this mean? Let's take a look at this graph in here. So I can say that this circle the blue one, is select statement from one on the red one or the left morning here would be selected statement number two the middle in between the two circles in here between the select statement one on too. Here this area. This would be the intersection, the common ground between most two circles. Except on the other hand, would be on lee the data in the blue without the red arm that with the intersection, let's go ahead and see this in action. So what I'm going to do right now is I'm going to like the first name Colon on Lee from the MP stable in the human resource of the debates and I'm going to like the first name also from the person table in the adventure Works database on. I'm going to display only the intersection between them on Lee. The common name. Let's go ahead and try to do this. So I'm going to select the first gained from and these traits and I was being displayed because I'm selecting, give it works. I'm going to change it. The movie sources I'm going to choose in, please. Yes, it's correct. And I'm going to select the first name again. Now, this time from the adventure works database on the person s name, a person, even I'm going toe order by the first thing. So right now, these are two separate query. If I run them, skilled server is going to display them as different. Not the same crazier. Two different queries. All I'm going to do is I'm going to try in music and you can see two includes a keyword. Now, sequel server is going to check the common name between this wary in here on the square, in here on this plate. In one query result it's execute. Here is we have only five employees names in common. You are Emily. Jessica, Don Noah. Answer fear. Let's go ahead and try the other one. Going into selective thinking it could be. We'll make it. Caesar is gonna help. Copy and paste this. Now I'm going to change this key Ward too. I'm going to run this only this one. It's here that on here we have the names that are in the increased table. Andi don't exist in the person table, which are Ali Chung. James Leon Mayer. Sergey flooded were warned. And Wilfred. So very powerful symbol Keywords getting us amazing results. There is other ways to do this technique. But this are very easy keywords as we can use. You have that. You get it on to you in the next lecture. 48. CASE Statement: Welcome back and section. We're going to discuss one of the very important concepts and keywords and sequel server. It's called Case Statement. What if I wanted to ask some specific condition and Baines toe own this condition? I would like to display another elephant, maybe a columns. It doesn't exist. What does this mean? Case even waits. A list of conditions on returns. One off, multiple possible Richard expressions. The Centex for the case is going to be a little bit complicated. We're going to break in the quarry, sir. We need to time the keyword case. Then we have the multiple options. So think of it as an if and else if this them displayed this if this condition happened and displayed this album so I need to try a case on here is a conditions when he was the expression that I'm going to compare it with regard. To see then is the key word display this result expression on. We can go ahead and and and else if this condition all multiple conditions didn't happen, then we can use an else. Have it's going to display any dealer expression and we need to earn them. I'm pranking this freeze that so that it doesn't look complicated. So we need to add a case when condition Zen a result Expression on an end. Let's go ahead and see in here. I'm going to braiding your query, going to select old from and please and I'm going to display. So you know, we can see that we have the country i d 61121 etcetera. What if I wanted to This play the name of the country using the case statement? It's not the best way. There is another way that we going to see this how integrate joints between table and trade views to get the name. But right now, I would like to do with using the case statement. So I'm going to ask him Thinking was you right? Now, logically, I'm going to ask if the number six then this place you can feel six if one display the country from one that should be countries. So if its country of one is going to be used A to Germany, China for 34 Russia, five, Egypt, six Australia. So I'm going to do this condition right now, so I'm going to add it as a new column. I'm not going to replace this. I'm going to leave it. So I'm going to leave all of these columns on. I'm going to add a new one. So I'm going to leave this. This is something you I'm going to add the common that I'm going to type anything. So in here what I just did is very simple. I hold the SQL server display all the colon that you have, that I'm going to have another. And you, Colin, I'm going to type Case, as we have seen in the presentation on them go in genetic space is just to make it readable on. I'm going to ask when the country id's at the condition equal one zin keyword display. Use a, you say is a stream. So I have to invent it in e single coat. It's giving unit right now because I didn't include one of the very important people. Tend to forget this and checking the record. It's the end, so I'm going to type it first. So right now there is no errors. Let's take a look before we make it more complicated. Let's run this. Did you notice here that we have a new colon. If I remove this going to have all of this, that's the normal select. We don't have this column here. The last one would be the status I d. I'm going to Hey, sit back and executed then haven't you call them? And there is no name for this column, this column displaying only used a and the others are no, because there is no condition. So let's go ahead and continue the conditions. But before we go ahead and continue it, I'm going to test the Els. I can come in here. If there is no can other conditions, it's had another one will make it more meaningful. So I'm going to add Arctic. There's a copy and them driving in here another when that Another condition they controlled the equal to then display the 2nd 13 on the list is Germany. So right now we don't have any Germany being displayed. If I ask you this right now, we have Germany hears it matches the conditions. Number two. You it is. I can go ahead and order it to make it easy for your order by country. I d turn, and we have the employees from that. You say please from Germany, but no others. I'm going to continue to hiding them. But let me show you how to use the Elser, the elders going to a blurry for any other conditions, not the one love to do. It's going to be for this tree for 10 1000 any conditions. That's like one or two. That's right. So I'm going to type simply. It was not available in a format available. What have a little info? Maybe that's its users. So now all the other countries other than the USA and Germany have this text in here not available info. Let's try to keep adding that it's I had number three. If you check the day differently. Countries in here, that's going to be China, Russia, Egypt, Australia. Let's do them old. So I'm gonna do here China, then Russia again. That's not the best way to do it, to display the name of the country's, but that's a very good case that we can test a later. We're going to see how to display and make joins between the different tables. Going to choose life would be Egypt are changing the numbers on the name, so each number wouldn't match a specific name. Six would be Australia. Let's run right now. Here it is. Then we have older names, you say Germany, China, Russia, Egypt and Australia. Very pregnant, I guess. But we still have one little problem, which is that the colon name has this very ugly looking or name or description. No golden me. How can I specify its displaying this? Because there is no name for it. It doesn't exist in the table. If we take a look at the empty stable right now, there is no colon. Gold is the last one is the status I d. It doesn't recognize it. Simply to fix this or you have to do is use the US as an go ahead on time. The lane, maybe country name. Let's run earn works as magic country name. It's like a colon that's created in our table. I hope that you get it. You can go ahead and do it also with the department's. I can go ahead and create another one. Let me take a common here on going to also do this with the department case. when state spaces. I'm just doing this for better viewing for you. So department I d. Equal one. Then let's check the department stable to see the number one. Which, which department it is was the departments. Here it is like tone and blonde would be production. Okay, you heard downtime it. So it's going to me Pro addiction alone. Going to die residencies. Where's the error in here both to get at the end? I'm not used as the name department name on. I'm going to run all of this. As you can see now in please from the production. Portland's are being displayed. I can go ahead and keep having So when the apartment people to then maybe research and development. Apartment number two. As you can see now it's being displayed on that. So try can keep adding them or had an else Teoh this play something here rather than the no being displayed in here. It's a little bit more. No, let go to display that later so I can go ahead in here. I'm just had else. We have known or known as you can see now unknown Ellen unknown. But we have the research development and production, so I know it's a bit complicated. The rescue of the select statement is getting a bit complicated, but you're going to get familiar with it. It's very important the case, and when it comes in handy, a lot of situations, I hope that you get it on d. C. Unions in expected. 49. Exercise Case Statement: comfortable exercise about the case statement, but I need you to do in this exercise. Go ahead on displayed in months names based on the date of burst column in the M. Please table off each employee on named in you column months name. Using the case statement technique was a video. Go ahead and try to do with on your own. Okay, it was easy. Let's go ahead. I'm do this together, so I'm going to select Oh, from M P's this very basic on before chicken it's going to display older data. So now I'm going to grade and you call them in here containing the name of the months off the data burst of each and pleaser. The 1st 1 should be November than we have Morrish May and the tempera. Let's go ahead and try to do it. So I'm gonna take Come in here and I'm going to start having my case when so case when Months, Two months. You have seen this before from the data burst. So now I'm extracting the months on Lee from this column, which is a date, what I'm going to impair it was one. Then I'm going to display. Gen Worry. That's the 1st 1 I'm gonna take a couple of them opium paste it. 23 They were going to change the name to February paper. Where, Ray? Just then we have marsh. Well, straight right now. But don't forget to add the end, and I'm gonna name it as months. Name. Excuse this. It's going to split. Bristles for these three months us. Execute it on. Here it is. We have Lawrence and January that being displayed in here for decent please. If we check that's months one. That's correct. Let's go ahead and do all of that. I'm gonna ease of the M, paces them for a couple of times that I'm going to change it. So we have a Brill Five would be may. Then we have June. That's number six. You know we have seven. It's going to be July. A little bit boring to do it so a it would be August then nine would be September. Way you need to take the last three months is which is going to be October. Who were 11 would be November when the last one. Yes, we're done. We don't need them else in here because we know all the the possibilities. It must be one of the will months as there is no other possibility. December, we are done. If we run this right now, we should have old and months is in here. You can see how you can come in here, of course. And ordered by the months waterboy the universe, Um, and demons on execute. So here it is. We have January, March, April May or get September death. That's not all the monsters. Because that older mentors are available in our records in this table. So that's it. I know it was a little bit boring, but it's a good technique. So, seeing you in the next lecture 50. Exercise Fill PreviousExpereince Table with Data: we have a lot of exercise. You need a guard on fill the previous experience table with data. You can find the tell sheet that you're going to use to fill the table with data and the resource is go ahead. I'm trying to fill Czyz table. Was they okay? It's easy. We've done it before. Let's go ahead and try to do it again. Going to choose human resources tasks Coordinator on there. I'm going to choose next. The data source would be easy Excel, she crows. He renews previous experience. Chris Next two sequel server it is I'm going to use you in this indication or you can go ahead and use the sequel is indication with user name. Have the specific permission to do this. But I have come into data. I'm going to use cheat number one on and I'm going to fill it or fill the experience stable . The heaven mapping go ahead and lead rose in the distinctions. There is no rose, but just to make sure Thanks. Thanks, fish. And we should have I froze. Not a lot, but we're going to use it. Previous experience select Andi here It is very nice. We have some data and previous experience that we're going to use later. That's it. Stevens in extractor 51. Subquery: Welcome back, everyone in this picture, we're going to discuss one of the very important hand a little bit complicated concepts in SQL Server. It's called sub Query. What is required? So query or colloquially referred to as an equerry or nested query is a queries that has misted inside with the statement. The statement can be select insert as we're going to see. Software is used to return data that will be used in the main query as a condition to restrict the data to be retrieved from the main Very What does this mean under this? Seem that complicated? Let's go ahead. Take a look at this example. So here we have two. Where is I'm selecting all the records from the M please on all the records from the previous experience. The first warm as you can see in here. That's all the employees on the 2nd 1 in here. Your name? These wins experience in the past. What if I wanted to get and you query to select the m please from the employees in here to get all the information about them, the country, the gender they burst or any other information the ones that have experience in the past. How can I do this? I cannot come in here and start timing. For example, where and the I d in on type these in, please. That's not a smart way to deal with its. That's more because these now are four records. But imagine if they were 101,000 from even millions. If you're working with large corporations or huge amount of data, it's not applicable to do with this weigh in Andi giving more off the giving in a fixed way like this, it's It's not marked or even to use it, not equal or equal. And given the numbers or the employees ideas, it's just not right. It's much more it enough. So we have to figure a way to get the data in a dynamic way so I can retrieve all of this from here from the previous experience like this in a separate query and add whatever result is retrieved from this in another condition In the main query, this technique is cold, so great, let's go ahead and see it so that we can feel it more. How can we do this? Stop grazes this complicated sub query. Let's start with a very easy one. So I'm going to and in here a normal where? So I'm selecting old from him. Please. Where the employee I d and the Lord to choose in. But I'm not going to give it like this to three or 11 to match the numbers in here. No, I'm not gonna give it in this way. They told you it's not smart enough. I'm gonna give it in this way. I'm going to select. It's braided. Was landing here. The employee I d from the previous experience. Er let's run this Onley this query Select employees from the previous experience. You query! Excuse me. You know it is So we have 236 and 11 now how can I compile Ein both of them Onley with this , we going toe open appearance Ease and close it. We're done. I can find boss of this query. Sir, what I did he was, um, selecting all them please from the MP stable on them. Adding a condition where the impunity in I'm opening currencies and the closing end on between This parent sees I'm selecting the employee i d from the previous experience. That's take a look at the outfit. Wow, that's that's amazing. So now I have all the employees that have experience in the past based on Lee Query getting back from the previous experience without me saving anything or making anything. Fix it on the high change it any numbers in hero Anyim, please. For example, if I change it this to make it maybe 10. So if I come in here again and run it as you can see the previous run or the very they didn't contain 10 because we just added it. So I'm going to execute again on we're going to have 10. Well, with all the information off emptied of attempt that's very powerful without me having any fixed values, it's going to the previous experience. Getting whatever records is in here or impunity isn't here. Then it's comparing of putting it inside off the main query in here. Get back on, make this to again, Okay, now that Britain is going to be removed because they removed it, it is I'm like can come in here and get all the appears without experience with just having are not in so not in the data getting back from the previous experience executed. Now we have all the employees who have experience in the past. I know this for the first time. This going to look a little complicated. You might need to start the video from this hard to understand what's going on, But trust me, it's very helpful on its very fast and very smart technique. Next, we're going to have an exercise to practise more the query technique to you in the next picture. 52. Exercise Subquery: time was an exercise to enhance our knowledge off The sub query. Need you to stay focused was me. I needed to go ahead and give the city's names that our in please have experience in in the first, starting from 2000 and 15. I need you. I know this is a short line, but I need you to read it very carefully. Give this in his name. That mean that we're going to select from the city's table? I'm gonna give you any more information. Go ahead and try to do this on your own. Okay? I hope it is easy. Let's go ahead and try to break it in here. I'm going to select all from cities that I have all the regulars from the city's without any conditions. Now I need to have the condition to get the imputed experience starting from the date starting from 2016. Let's try to do it. So I'm going to add aware city I d open underclothes appearances. Don't forget AKI in cure. Then I'm going to select the city I d. From the previous experience. I'm going to select this right now. Only this query, the inner query in here, all the sub query itself. So we have these cities. This is the cities that we have our employees have experience in. So I'm gonna need to fool her this. So I'm going to at this where I'm going to have the condition inside the inner query year. So I'm going to subtract year or get the year out of the start date. I'm gonna you know what greater than or equal 2000 and 15. Now we're gonna get all the information that we need. It is now the r three cities. If Ryder, please a city I d with an Astra spigot. Older call. Um, so we can have We can see that to him, please. And the number 22 records on in Denham 61 record. Let's get back to this city. I d. We have to see the idea so that the city I d matches this if I add any, are the colon is not going to work. If I add the asterisk, it's not going to work this city i d or the Coolum in the world condition in here in the main query need to match the one in the inner query or the sub query. This is very important info. Okay, so if I win this right now, we're gonna get the state. Is that we have experience with our own. Please have experience with the starting from 2015. As you can see, we have New York hamburger. Sydney, that's very interesting. I can evening go ahead on filter this. I can have condition of the condition in the main query. Maybe a life is like the country years eight armed country I d equal one line of the I D number one you say execute on then. We have only New York MP that have experience after or equal the 2015 in USA, or we can change it with the same condition. But in Germany, number do would be to Germany, where you can remove it. Unical mention to that said about the exercise. Do you Indy expect? Er 53. Inner Join: Welcome back, everyone. In this lecture, we're going to discuss one of the very important concepts in relational databases. It's called Jones. We're going to cover some of the most common types of joins. We're going to start with type cold inner joint. But first, let's understand what in the idea off a joint, we can say that sequel joint is used to compile Ein Rose from two or more Coghlan's. I can gather information from multiple columns. As I told you before, using joint, of course, paid on a related colon between them. Let's take a look at this diagram in here, sir. The inner join in the area between table A timetable being in here. As you can see, there's other types and doesn't work this way. One is called lift out Joint. That's going to take the data from the left table as you can see him here. Entertainers, older records from the left table, which stable A and matched the records from the right table. It will be the result no from the right side, and there is no matched up, and there is no match to some of the conditions in the table A or For example, if I haven't employees and these table containing different informations from, for example, here I have the names, details for old employees on the table B contains maybe the adverse Ezzell, the cities, all the previous experiences that he had in the past seven. Please may have nothing we have. This example may have not experience have not experienced in the past, so there is no middle ground for them. This cannot work, so it will get the date. I don't looks complicated, but but when we take a look to the examples, it's gonna look easy. Then we have another couple right out to join on its the opposite off the left out. Join all the records from the cable be in here and matching records from the left table, which is a is being returned. The result is no from the left side in here when there is no match. I know that this the doesn't look a little bit complicated. Don't put herself with the left out joint on the right out of John. We're going to discuss them in detailed and you're going to understand them. So let's start with the simple one, which is the inner joint. Okay, here I have something that I would like to do. I would like to display and rafting this plenty of times using different ways, but all of the way that we did it, It's not the best way. I would like to display this first name, last name country I d. I leave from the employee. I would like to display on Lee these basic information from them. Please table We have seen blink of time that we can display the name of the country now. Right now I have 61 to warm three. What if I don't want this to be displayed? We know the decorated this because we you these numbers and the relation between table and for the primary key and foreign key. We have been through all of this. But what if I wanted to play this query already reported like display the name of the country. And I don't one, for example, to use the case. And when, like, we did all the replace I don't want to do these techniques because it takes start on the or not smart. So joins govern handy in this situation as I told you we can comme pine tables from different combines, actually data from different table so I can combine data from the employee stable in here on the countries to display. For example, the country name the deprivation on the capital in here, in this white his pace right now. So let's go and see. How can we do this? I know this. Get on local big confusing, But trust me, with time, it's gonna be very easy for you. And we're going to see an easy way to do this. But card was mean. You I'm going to select and I'm gonna choose m please. Ongoing selecting the table and joints. We need to define the table that we're going to choose from. So I'm going to choose from the M. Please. First name Right now I'm just selecting the normal columns. I'm just adding the name of the table and the front on alert. You the last name on probably I'd be choosing Please, country I d. That's it from please state. This is the same as the apothecary in here. We didn't do anything special. But what if I told you that we can right now? Add this joint toe added joint simply, we go ahead and try bit inert, doing yes, symbol of that. Then, after the energy join statement in here, we need to add the name of the table that we're goingto link our base table in this case, our base table in the employees. And I would like to link the employees or make an energy in with countries. This that's great. They're stable to that. I can extract all of this information. So what else isn't done? No, we need to add if you remember from the definition and related column how it's gonna look into the data MBD in the country's table, it's gonna look based on the east number. So we need to add the condition. The condition is being added as following on, please the table docked country I d. I'm gonna take this into a separate lines that you can see better equal countries dot country I d. What I just did in here just I added the condition. I told her that based on this condition, that the country I d in the employees, which is 612 and four and all of these numbers matches the number in the countries. And if we go to the country's let's go out and selected, we can find that we have these numbers and he's there when it sees a number one, it's gonna get me Old East records for this one here. James, for example. When it sees do for Sofia, it's gonna go for the country number two, which is Germany. And again that get me older data again. If I run this right now, I will not see a much of a difference. I created the inner join, but there is no data simply because I didn't select these columns. Let's go ahead and try to select it. So I'm not gonna select from the in, please. No. Now I have the option to select from countries, Doug. Now that's that's problem. Don't through get become so countries on Dean here. Now it's giving me the option to select from the table countries. I can go ahead and select the name. Go ahead also and select the abbreviation on May be the capital. Isn't this amazing? Let's sees a result. Andi, we're gonna have really, really nice one of the best feature that I like about sequel, server and relation of the basis is joins. Can you believe this? Now? I have old information for John. His first name. Last name the country. Name and code. Of course. The observation and the capital. Let me going through These are minding the case And when, like we did before this luxury sport way Onley using this symbol technique in here, sir, to revise what I just did in here again. I just added the I need to deter mined the name off the table. Because if I give it like this what? You know what? Why I need today select or add the table name. Let's go ahead and fried this If I try to tie this and that's a valid Kohler country party , you know that this is a valid cooler, but it's gonna complain. You know what? Why? It's complaining about its complaining because right now we are selecting not from only one table but from two tables the M please on the other one, which in the countries that we're doing an inner joint, where's on this column does suggest in the increased able Andi in the countries. So this is now ambiguous at the Expression Off Sequel server. If we take a look at this error, it's telling me US Colon name. It doesn't know when to give this column from which table we have to remind whether I would like to get from the countries table. Now the error is gone and it's gonna be hearing in here again. Okay, that's the first where he don't bother it or from them. Please. Again, that's why we need to get a mind which table that we're going to select our fumes from. So that's the change that we've done in here. We are adding the table name, then all I just there that I added this new keywords in your inner joint. As you can see her, it turns grey to keyword on Day added the table that I'm linking to it or adding in relation to it, which in the countries that I have a condition because I cannot leave it without a condition, it's not going to work. It needs a condition. It's just like the relation between the primary key on the foreign key or the relationship in the tables. I need to add the condition. I hope that you like this? I know it looks a little bit confusing, but trust me, this is one of the very, very, very important concept on you. Will be using it differently. Because when you're designing a database your government to get information, not from 1 to 3, you probably gonna get information from, like, timetable or even more based the in your databases. Chema. So that's it for now. Next, we going to see another example for the inner joy before we continue discussing the other types off the joys I have that you like it on that standard and see you in the next lecture . 54. More Inner Joins: okay, We're still discussing about the inner joints and this lecture. We're going to see how to create another in a joint. So what I need to do is I would like to curate a link where joint between the employees on the department's to display the department name rather than this number in here. Let's go ahead and try to do this or we're going again by step. So, like, I'm going to choose the please the table so that we don't have an MP US columns problem. First name going to display. Go ahead and display any colon that you prefer. I'm going to display the basics in here. Um, maybe the please also department I d. So now Okay, on them going to now, right now in the room. So, like the departments, not department name right now. It's not gonna work, but let's go ahead and continue it until it works. From now, I'm selecting the table that we go to perform this query or selection from, and I'm going to specify the inner John on the table departments. Then I'm gonna have a condition on him. Please. Able don't department I d equal the departments the department I d. So we have our selection O R. Columns, all the things that we're selecting here we have the first table. Then we have the second table between and injuries and in a jar, and then we have a condition. On the condition is the employee I d equals employee I d impulse table So that Kim matches at the problem. We run this right now. Here it is. We have the first name listening on the department's name. And if there were any extra information that we would like to display and the stable, we can go ahead and just selected. But unfortunately, we don't have an extra information like the countries, countries contained more information. So that way we displayed more information. So that's it for the joy to you in the next lecture. 55. Multiple Inner Joins: Welcome back, everyone. It's getting interesting. What if I told you that we going to create in multiple inner joins? Why would we need to do this? Obviously, because we going to get more columns from multiple tables using one query, for example. What if I wanted to display the employees with departments were really did this. But what if I wanted to split old of the countries in the same query? Pretty interesting, huh? Let's go ahead and try to do it. So let's go ahead select. And he's I'm going against, like the first name used up last name. Maybe this time of life to add something different. Maybe the please email. Just have an extra information. The email is fun at the comma. Then I'm going to add the in, please going to add the department I d on. Then I'm going to select from the department table departments table the department name. I'm gonna also selecting. Please don't forget the calmer is the country. Andi in the country's table. I'm going to select countries dogs name and maybe the country's no abbreviation is fine. Okay, so what it just did in here, I'm selecting all the column that I would like to display from the different three tables in please departments on countries. Let's go ahead and see How can we create the enter joined photos, Multiple query or multiple that they're done. Don't forget the from an please. That's very important. Then after the fromem, please, I'm going to add my energy in class in here on the departments, and I'm going to use a condition on he's docked department I d Equal department, as we have seen before. Department store department. Heidi, we've done the first join. Andi, if you notice that the red line then here have been removed. Now we have only the red lines in here because sequel server doesn't recognize this yet to go ahead after we done this having love in a joint? Asked Symbolist Glad at table. Did you learn to join? Have the condition from him, please dot country I d equal the countries, not country I d were done on the air own here his gun with the column from the countries. If we run this right now, Wow, we can see that we have all the information that we added from the MP stable on we have the information from the department stable on. We also have the information from the countries table in here. If I added any extra polar, maybe the cabbage goal it's gonna be It's a great capital. It's gonna be added in here without any problems. So we can say that James from Washington, D. C. Or Will Fred from Berlin Really interesting. A very powerful way off proving and integrating things We're not done yet here. What if I told you that I need to filter some of these data I would like to select only day please from country number one. How can I do this? Let's go ahead and do it simply. We're going to use a condition where, as you can see, it's going to accept the problem. I'm just leaving it spacing here just to make you read the wear clothes better. It's not Centex or something. So I can just come in here and AB what do you think? Can I have the country I d directly? No, nothing that the best way to do it because it's gonna give me the air of the ambiguous again doesn't know which called them to filter and this is important. Later, we're gonna see why this is important when we're using the left and the right out joint. Okay, I'm gonna have been here. The peas. Not country already equal. When? Let's go ahead. And you see all of this results when I run this right now, we're gonna have only be united. State of America. M please. Really? Just a symbol. Assets. Can I filter from the country's cable? Not the increase Course you can do this. I'm gonna take this is a copy on them Going to filter based on the column. Countries don't country name. I'm gonna choose. Drew me. Let's run this, Andi. He really is. We have Onley. Germany had only this I can go ahead. Remembers can go ahead. Um, use This is really interesting. It's getting interesting the like. If you remember the like and the wild courts, I can go ahead and choose. Maybe all the country is with our thing with ass. Maybe they are. Let's go ahead and see what this will get us. Any are between here is the countries with the army have Australia, Germany, United States former account. Maybe you can go ahead into the E or you can go ahead and play with the wild cards as we want. I bet you like this letter. It's very important to use the the multiple inner joints because we're going to use N'Dri words in areas. Multiple tables are grouped together to get all of the information that's unique, so that's it to you in the next picture. 56. Exercise Joining Cities and Countries: Okay, How about an exercise in this exercise? I needed to go ahead on and create an in a joint between the table cities on table countries. Table tennis is going to be the base and then going to link it to the country's to extract information about the country's by the country Name abbreviation the capital on added to each city in the cities table. Go ahead. Both the video try to do it. Okay, We'll come back who is easy. Let's go ahead and try to do this together. So first, I'm going to select Michael. Um, so I'm going to select, for example, the cities, the city I d. Then, of course, I'm going to chew. The seat is city name on. And then I'm done with the city's I'm going to choose from the country's so I'm going to choose countries that country i d. Then countries don't don't forget, become a going to like me country dart name and have the country's dogs abbreviation countries. No, maybe capital. That would be enough for now. Go ahead and try to select from the cities Xanadu, the inner Jorn on the table countries on our condition is going to be the country idea. Post tables. So we have the cities, not country on d equal. The country's not country I d on. We are ready to go. God, Excuse this. Yes, As you can see now we have city I d the city name and the country and information about the country's for each city As you can see in here, don't you? You one more saying that they can use the order bite using, for example, Maybe I would like to organize it order by the capital. So I'm going to choose he countries about capital in descending order, for example. Don't know. We have Washington d c being, uh, Now we have Moscow in Cairo. Perlin, it's alphabetically. Go ahead on a sort of using the country. Yeah, I just wanted to show you that the order boys working fine. You just have to add the name off the table, then the name of the field and is going to work without he. Probably money can go ahead remote. The that's sending order on it's gonna be working different. Okay, that's it. And see you in the next lecture. 57. Exercise No of Cities in Each Country: Okay. How about another exercised about the inner join in this exercise? I needed to go ahead and get me the total number of cities in each country. I'm not gonna give you information, but I need this result. I need the country. Name the country abbreviation on the number off the city's all the count off each countries cities in the database. Go ahead and try to display something like this in your SQL server. Okay. I hope it's easy. Let's go. Haven't tried to solve this together. This exercise we're going to need to use in different techniques. So we're going to you the count function to count the city's I'm going to use a group by keyword andan, inner joy. Let's go ahead and see how we going together. All of these different techniques in one statement. So I'm going to use de select countries. I'm going to select the country name to display the name. Then I'm going to choose the country's again the country observation. Then I'm going to choose the count message. I'm gonna count the city's dot city I d. What I'm doing here is yes, time taking into account the city I DS leader. I'm gonna pellets agreement bite a little break The total counters that it doesn't count all the cities but count only the cities in each country isn't going to see I'm gonna name it Total cities, for example. They can go ahead eggs down to that's visible for year and I'm going to chew the it'll the base There was going to be the cities I'm gonna before my inner join with the country's table on our conditions Should be the cities, the country I d equal the country's no country I d. As well we're not done yet. We need to grew by the city ducked country I d. I would like to groom each country to get that I'm going to group buy old sue countries dot country name on one last we're but I'm gonna groove there. The country's don't country of radiation. Let's run this on. Sees a result. Wow, Here it is. We have the name off the country. The innovation on the total count oldest being displayed using the account in a giant on the group by statement, but that you like it on See you in the next lecture 58. Create a View using SQL Script: Welcome back, everyone. Once again, we have one of the very important concepts in relational databases on sequel server. It's called Views and this lecture. We're going to creative you using sequel script. First, let's define what is of you. You is simply a virtual cable. It's the same structure at the table. It contains columns on Droz, but it's not an actual table. It consists off data from multiple table that we're going to see users can queer reviews just as they would in a database table. Let's go ahead and see how to create this view. So assuming in here that I have this square, if you remember this weary, we created them. This traded earlier, we created a multiple inner joins. We have inner join where the department's gonna join with the country's honor and displaying information from the M please departments on countries table. If we take a look at this, this here this information is from different tables. For example, the first name and last name and department I D from the P's apartment name from the departments, the country name aggravation and chemical from the country's table. So how can I gather all of this information into one table or view. How can I do this simply? We can come in here. It's very easy, actually. Integrative view. We're going to use that create. We're going to choose the time off the object that we're going to create. It's a view. I'm gonna give it a name. So maybe please, I would like to show you some folder in here called views. If we go and open it right now, it's empty. It's the same as table. It's a database collection or database. Object its views. We're going to create this view and it's going to be placed in here. Let's go. Having continue creating our veer so create view and please, as on we are them. But we cannot name it in please, because the in please object does exist. There isn't really object named and police you're going to call it and please, maybe full information. Go ahead and choose any name that you're comfortable list on. I'm going to press execute. Um, as you can see in here it completed successfully. If I refresh the tables right now, always views. Actually, we're going to see that we have an object as you can see it's a little bit different in the icon. It's not the same at the icon off the tables, but it does look like it on the tricky barking here with the magic part or the powerful less off the views is that I can go directly, can select from it. So select. Oh, from please, we're tied the whole name. Oh, information. And let's run this. Why did you know possess that we now have this query simplified into this short name. That's not Onley it. I can come in here and start filtering, so I'm going to add maybe the first name equal jump, do something different. Maybe. No, uh, it's execute this. Well, let's try to full for using a wild cord for his name. Like maybe ass in here. Actually, it's the first letters s. As you can see, we have Sophie on, sir. See, it's really, really very powerful option on. And that's it for now. But we're still discussing about views. See you in the next lecture. 59. Modifying The Structure of a View: Okay. We have seen how the creative you. But what if I wanted to modify the structure off this view? How can I do this? Let's go ahead and see. You need to use a key ward Cold Alter. We have seen it before Elder in this course. But right now we're is a view. We can see that the view is here. I can go ahead and select from a Let's go. How? I don't see that select old from him, please. Full information on its working without a problem. But what if I wanted to ask for example the email after the last name? What can I do this? We go into you the older But do I need to alter Onley this field or rewind the whole view? Sadly, you need to rewrite the whole view. But lucky ass, we have this. We can come in here right Click stripped of you as go to the altar command New query Editor Window on here It is you with the query and it's really to be altered Alter view and here in the name of your view. And here's all the Coghlan's and the joint that we've created and we don't have to rewrite it again. All you have to do is just go ahead on time. The column that you need to act. So I'm going to choose And fees don't email. Maybe another one every he's not. I don't know. Maybe the gender. Just go ahead. Don't change anything. Just go ahead and execute Andi. I forget the comma. Don't forget the coma. Now there is a car. Go ahead and execute on it. It's done. Go and select the same selection that we had in here. And take a look at the last name here. And the department. There is two other columns. They're going to be inserted in here. Exe cute on you. It is here with the email on the gender assemble as that. So this is a fast way to older. Have you? Thanks to the management studio to you in the next lecture 60. Create a View using Management Studio: Welcome back, everyone. In this lecture, we don't see how creative you using the management studio, which is going to be much faster and much easier previously, where and how to great view using the ask you a script, something like this. If you remember, if we stripped this, we're here to see the code that we've written here it is, but that's love. Be easy way to do it, but you need to learn how to do with how to read the code. But there isn't much easier way to do it, so I'm going to create the same view from peaceful information. Give it another name, but I'm going to use this time the management studio on using the view options. So I'm going to the view fuller in here or category. I'm going to right click, choose new view, and we going to create the view visually, so I'm going to choose the empty, stable frequency as I'm double clicking on the table. It's being added in here. I'm going to choose the other people are going to choose old for the country's on. Did you see this when I added the countries or the other table it created a relation visually, as you can see him here without me doing anything based on the relation between the primary key on the foreign key, I'm going to add the departments. Oh, this That's very easy. And you know this. It graded the code dress in here. Do you see Solectron, please? In a joint on an adjoining? It's doing the job for us all. What? We have to run Management studio did it for us. I was out taking any time all you have to do, just specifying two things. The first thing is choosing the colon that you would like to this place. So I'm going to choose on. Take a look at here. Hand here. As I'm adding columns, you're being added in your found in the code than the sequel script in here. So I had a the employee I d. It's added here and here was a had a choosy first name again. It's added here on here. Last name gender. Wow, that's That's really powerful, very positive option. Let's go ahead and try to choose the country name again. It's being selected in here Country observation the capital with Portland name work link I d. Well, that's that's really something. So how can I save this all you have to Do you need to type grade view or something. Just gonna have, um, safe it from here. I'm going to the name. Okay. Insult Similar that. I'm going to refresh the colder in here. Oh, the views on your it is we have of you created very easy and very fast. Let's go ahead and try to select some data from it for select from years old. Didn't you last yet? You can go ahead on time, The name, the problem and it's going to work. Excuse this on. He really is. As you can see, all the information that we selected on I can go ahead and sell to read old rhythm going toe order by the department name, for example. Apartment name in sending order. That's experience this on who it is. We have the they sorted by the department name how they can go any time. Onda modified. Just right. Click on and choose. Design will go ahead and keep having columns. May be data, birds, the email, the marks. For example. I'm going to save money to type older statements or anything. And if I girl like Mary on Executed, take a look at the columns. The last column at the department I d. Now we're gonna have some extra columns. The column that we've added the data burst. Email Andi Lawrence. Very mournful option. And also go ahead and enter the view that we created using the sequel. A script if we choose the sun urine is here is a design. Okay, you can go ahead and choose any other colon or remove it. It's working that a problem so you can use these aways any time. It doesn't mean that you created of you using the sequel a script that you cannot. It is using the management studio. No, he can always edited using the management, do you? Well, let's saying that I need to show you here that I can come in here to the you that I just created on and I can click if you're not sin here that they have only inner joined in a joint. How can I change it? For example, a lift out to joy? How can I do this? Well, they have to. Do you see this? I couldn't hear. I couldn't just this diamond. I guess you can just come in here. And if I hover over it, it's showing in a John. If I read fresh light clicking here on day, I have two options. So, like Old Rose from in, please and take a look at the relation of the MPs on the department's in here. It's this one. I'm going to select old employees. Then we have a list of to join from the police to the department's on bacon. Do the same with the It's not logical. It doesn't mean anything to do this relation. But I'm just showing you. So I'm going to choose old forever. Please. We have left to join if I remove the and please and that you, the country's now we have right out of joy. So it's using from table on the left or in the rides using. There's two options in here. Then it's very, very easy to do joints and views using the management studio. They hope that you like that Andi to you in the next lecture