Database Tutorial for Beginners | Database Fundamentals Full Course

so the course is broken down into five modules uh the first one that we're gonna do right now is just introducing core concepts we're gonna help you understand what a database is why you might want to actually consider getting a database um going to go from there into some of the basic concepts that make up a database we're going to talk about relational concepts including normalization referential integrity and some other topics there we're going to go from there into how you actually create and use them what kinds of things are in a database we're going to talk a little bit about sql server and how that works that's going to be the platform that we use when we get into creating our database module 4 is going to be really super fun we're going to talk about dml which is data manipulation language we're going to actually show you how you can get data in and out of the database which is probably the most exciting stuff i know you've got like all kinds of really fireworks enabled demos for that and then module five we're going to talk a little bit about sql server specifics we talked about basic administration concepts um that maybe you'll do as an administrator or maybe you'll want to know about so that you can have a good conversation with your database administrator all right so let's dig in with the very first module where we're going to talk about the the core concepts or date with with databases and the ironic thing is a lot of people hear the word database and they don't understand how how much or uh it's it's in that's involved in their life for instance if you go shopping if you go to the store this afternoon and buy some items off a grocery store way back in the old days you would probably go into a grocery store and there'd be aisles blocked off because they're taking an inventory of what's on the shelves what's in the in the stock room so they would know what to order nowadays everything because when we pick up an item off the shelf and soon that we scan it that's being registered in a database so now they can set up and keep track of all of the items that are stored within a store in a database and anytime i purchase anything that item count let's say we have 50 of them now went down to 49. we can set up an alert in the database hey if this gets below 25 we need to reorder so everything is automated if you go online and buy airline tickets that's all information store in the database when you get ready to pick your seats that's all in the database so every day even though we don't hear the term database we are talking and working with databases so that's why this is important for you to understand that you know the concepts of a database and what's going on behind the scenes because it does affect you as a non-i.t person but it may be something that you want to get into as an id person because there is a lot of need for database administrators or dbas so wrong way we'll go back this way here so we'll talk about relational databases and we're going to talk about some database components and terms kind of level the playing field so everyone has an idea when we're referring to something what it is that we're referring to then we're going to talk about a few different types of commands that we'll use when we're talking with and working with with sql server so let's begin with an introduction to a database just to give you a better understanding of what the databases are all about a database and might just see it as a db is an organized collection of data and it's typically stored in electronic format it's really not much different than what we've done in the past but we had a different way to do it we allows us to manage content organize content or categorize it and more importantly allows us to retrieve that content fairly quickly traditional databases are stored in rows or records and then we have columns or attributes or properties you'll value those terms synonymously as it's referring to the type or the amount of content or the information that's stored in a database now you may be thinking it's like oh brian that sounds a lot like one of these good old excel spreadsheet and it is very similar to an express sheet in the fact that we have rows and we have columns and we've used these for for several things so uh pete what's the first what can you remember the first time you used an excel spreadsheet and you thought it's the coolest thing because you could have rows of information and then columns of that information that was relevant to those runs you know it's funny i actually still use one of these today which i'm a little bit embarrassed about but um i use a spreadsheet that's got all kinds of columns to manage my music collection my cds right and i've got a row for every cd in my library and i've all of these columns and i've added so many columns for the person who introduced me to it and the genre of the music and the date that i acquired it's sort of like the high fidelity movie like if you want to sort the thing by the date that you acquired it so you can look at your library in chronological order i do that right so i just i don't want to direct you just to let you know i have the same exact thing for both my cds and my dvds so i mean i still have 300 some odd cds and they all alphabetized and and so i'm not the only weird person this is really good to know and it's funny too i'm actually going to probably use this example throughout the course because it's the perfect thing that could make a really handy small database and even though i've been working with databases for 20 something years i've never actually converted it to a database to a database but we could take that content and convert it into a database and put it in a table this is an example just a brief example of a database table and that's as i mentioned it's a collection of rows and columns it allows us to organize content we're going to focus on specifically we're going to focus on uh relational databases this brings it to an entirely different level pretty much if i took this table here i may as well put this into a spreadsheet because at this point we haven't introduced the idea of creating multiple tables to represent this uh this this information which makes it and we tie relationships and those we're going to talk about all this uh into those uh these different tables so we can actually use this content for querying information and inserting information updating information and keeping this information uh maintained so we're going to take an excel spreadsheet and we're going to jazz it up by bringing it into tables or multiple tables in a database here so relational databases it's a collection of tables now let's have everything stored with 80 columns across in a single row we're going to have maybe fewer columns but we're going to have multiple tables now the relational piece comes into the sense that we have to have a way for us to be able to have those tables talk to each other so we're going to introduce the concept of primary keys and and referential integrity and and foreign keys we'll figure out how all that is how all that works for us but the idea now is taking that single row or single table as multiple columns 80 columns we'll say and busting that out to multiple tables to uh to create an environment that allows us to query that information and only store the information that we need to store and not have redundant information so in that so that's the advantage of moving to a relational database from we'll say an excel spreadsheet this is an example of a relational structure we're going to be using we're going to be referencing throughout here and what this allows us to do is you'll see here that we have a product a table here called products or in the production schema and we've got a a product subcategory id now this is referring to another table and it's a product subcategory id which in that table there is a product category id which is referring to yet another table so this is what's called relational databases and we're going to introduce these terms and discuss these in more detail but this uses what are called primary keys and foreign keys and so this here is a way for us to tie these tables together so our tables can communicate and it's going to allow us to perform some of the the commands that we're going to want to perform and we'll see us performing a little bit later on so that that actually looks a lot like what we were just talking about where my spreadsheet full of cds yeah in that in this example would be the products and the genres in my spreadsheet would sort of map to what you've got here for category yeah yeah perfect for subcategory category like because i have jazz and i have like bebop as a subcategory of jazz and things like that is that kind of what you're talking about this disco in there disco is not a sub category of jazz okay okay some categories jazz disco disco yeah i can't even picture that one happening but but yeah it's exactly a great point yeah so we could take our example of our cds that we have built into uh you know a spreadsheet and we could create a database and use a similar approach to to get this a relational database with these tables so a great example here okay let's make sure everyone understands and we're going as we go through these sessions we want to make sure everyone understands some of the terms that we're going to be using as we're coming down through this first off you may have heard of our of a dbms a database management system so we're going to introduce a couple products like microsoft sql server is what we're going to be focusing on and using as our demonstrations but a dbms is a database management system that's a it's a really it's a several programs that are used that allows the data for us to store the data and and retrieve that data so a dbms is also used for dbas database administrators and this allows them to be able to go ahead and manage the databases to create the databases to add the databases to backup the databases any of those components that we would need it allows us to do that using the dbms so it's a collection of applications that allow us to not only look at the content manage the content within the tables but also the overall the overall administrative tasks those also are managed using this dbms now we can throw another letter in the front of that with a little bit of alphabet soup here we have an rdbms a relational database management system and our dbms takes advantage of the database management system and we make sure that we're taking advantage of the relational aspect when we're managing that content so as i mentioned microsoft sql server is an example of an rdms microsoft access if you worked with that maybe you went from excel spreadsheet and you graduated to access and then the next tier would be going to microsoft sql server even using mysql if you're out there using mysql you have those for relational databases and those are ways for us to manage our content instead of the flat format that we're used to using with our excel spreadsheets now in order for us to be able to install or use this content we have to have what's called a database server a server that's dedicated to hosting our content now it could be a physical server it could be a virtual server but it's used to manage that content but from creating the databases to the content that's stored in those databases this database server can have multiple instances what does that mean i can install sql server several times and manage them separately we have the same tools that we use for managing all those instances but i can install sql server several times one may be hosting my sharepoint content another instance of maybe hosting my line of business applications and i can install that multiple times on a simple as a single server and that's what the idea of an instance is it's just multiple installations that reside on a single server every time i do that installation there's a new copy or version of the sql server.exe that's used for managing that content so often what we'll do is create multiple database servers and the idea of this is to provide for us to provide high availability and improve performance if we have multiple servers we're able to do that so let me just back up for one second sure because i have my spreadsheet full of cds right and now you've gone all the way to the extreme end of things you were practically at big data which we're not going to talk about today but there's this whole range of of things that i can do to get my first database that start with something as simple as access which is a part of microsoft office where i can install it on my local machine i can even export my spreadsheet in or import my spreadsheet into a database and start making the relational sort of connections that you've alluded to these key things all the way up to a database server that's dedicated for a professional environment an enterprise application that runs an instance of sql server or multiple instances of sql server so i just wanted to kind of point out that there's this range that you're talking about from getting from non-relational data that's in a spreadsheet all the way to massive multi-table relational systems on multiple servers and clusters and all that other stuff and for the scope of this conversation we're going to talk really straightforward about the fundamentals of what's in a database but brian's made a really good point that we're at the very beginning of a long journey to using databases that go all the way up to professional database administration database developers business intelligence and and now big data um and even databases in the cloud so there's the spectrum that we're talking about exactly across all of these sort of terms and we're going to the rest of the session is going to be kind of on a smaller scale but will allude to these sort of different opportunities that you have across different business solutions and and in my case my cd library management system i'm expecting knowing you as much as i know you now that it's going to probably be done by the end of the weekend i'll expect to see you i might be able to tell you because then i'll throw up i'm going to design it your database design definitely all right so yeah we are going to keep it on a small scale as far as what we're going to discuss but i was as you know we pointed out this could be to the enterprise level but we are we are not going to go to that level within here what we are going to introduce is because remember we could use access we could use mysql we could use a sql server microsoft sql server so what we're going to do is we're going to work with a utility inside this that's provided with sql server called the sql server management studio so the good thing is you don't need to know a bunch of commands there's a lot that we can do inside of sql server inside specifically in the sql server management studio and this is just a screenshot of it what we're going to do is we're going to go in and demo this and kind of just explore what we can do in this graphical user interface or gui to manage databases manage the implementation of the databases and even down to the table level uh of those of the content in the databases so let's jump out and explore we're going to go into a demo here and let's go into our contoso database if you've gone to any microsoft class you've heard the term contoso we're going to go into i'm going to go to start in all programs and in here you're going to see that i've got microsoft sql server 2012 you'll see i've got sharepoint installed i'm going to go ahead and expand on that you'll see we have several tools we can use in here we have configuration tools if expand on that that's where we can use for some configuration options the one we're most concerned with the one we're going to spend the rest of today in is the sql server management studio so we're going to open this up and when it comes up it asks us to connect to the server so we'll go ahead and perform that connection and it brings us in it brings us in to the left hand side we have what's called the object explorer and the right hand side we have the object explorer details so whatever we click on on the left hand side it gives us more detail about it on the right hand side let's start first off i talked about instances this is an instance a sql server that's running here at the top level i don't have multiple instances if i had i could use the same tool for managing multiple installations of sql server we're going to focus on just one instance here now in there the biggest the next big thing that we work with are databases so if we expand databases you're going to see i've got several different databases here already including system databases that are automatically installed uh for every instance of sql server so if we expand on that you're going to see we have these four system databases we're not going to get into the detail of those but every instance of sql server has these four databases that are used to manage the infrastructure and the logical architecture associated with that instance of sql server now we also now have user databases these the rest of these besides the system databases with the exception of database snapshots all of these here are user databases or databases that we've created we're going to be focusing on utilizing for the most part in this session or in these sessions for this class the adventureworks 2012 database if you've worked with a database before see previous versions of this by any chance you've probably heard the term adventure work so there's a flavor of it uh that you can use now if you're seeing our demos you're like hey i'd really like to explore with this you can actually go up and download adventure works 2012 and install it in your venture works 2012 if you're running sql server 2008 you can install it for there for that as well so if you're looking at this like this would be pretty cool to be able to have this play environment you can do so just explore look for adventure works and download the flavor for your particular version of sql server that you're running and you can be able to use that that information and i think it's also important to point out that what you just showed very briefly in the initial view of sql server management studio it showed that there were some objects i know we're going to talk about that later but every database system even microsoft access has something similar you can open microsoft access you can look at the databases that are there in that case you'll see one at a time but you can see the things that are in that database and there are sample databases for microsoft access just like there's adventure works for microsoft sql server so if you wanted to to try this as you're following along you could load up microsoft access you can find i don't remember what samples shipped with access to these data northwest it's still north northwind forever i'm not sure if it's still there i haven't opened up access in a little while yeah but there are sample databases and there's open source databases and you can see what sort of you can you can explore the relationships and products and categories and things like that are a pretty common thing that you'll find in all of these samples yeah that's a great point so we're going to be working on sql server management studio as our gui or graphical user interface for sql server but that's a great point pete i mean it's like okay well i'm used to i'm used to a spreadsheet right now now what's my next baby step my next baby step would be anyway maybe i try you know access next get the idea of how that works how the relationships work between the tables and then go up to you know you know upgrade to the sql server environment and there is a sql server express flavor available too so you may like hey i'm done with access i really want to go out and play with sql server there's a sql server express version that you can download it's free so you can download that and get most of the functionality that you'll see that we're working with even though we're working with the enterprise version for what we're going to do in this class here that express version will work for you okay so over on the left hand side we've got this here what we're going to be doing a lot of here as well is generating or creating queries so up here i can go in and execute queries in here so i can do a simple query this is the most basic query you can execute and look at the uh the intellisense so it's trying to it's helping me along with this so if you're not quite sure it's helping me along so production dot and then products hopefully we did that right we'll find out invalid object name so i can hover over it so it's all right so i'm not sure so we come over to the left hand side we go to our adventure works we go into our tables here and you're going to see all the tables now all of these tables starting with human resources all the way down here are all used for this adventure works table or database excuse me so i was trying to use production i think you're in master and here's my products uh and so it is an actual i have an x on there and so i'm going to go ahead and then i'm going to hover over it again and you're going to see it's still saying invalid product name now there's two ways what i haven't done is i haven't identified what database i want to use so if i look up over here right now it's using the master database when i connect it automatically connects me to the master database that's one of the system databases i don't want to work with that one so i can do this a couple ways i can come in here and drop this down and find my adventure works now when i hover over this you're going to see oh now we got what you know the item that you're working with now if you like to make sure you know what you're doing another way to do this is to preface this with the use statement and so i could do this as well instead of having to drop this down especially if i'm going to want to write as much of this up as possible i could make that part of the command and add in this the text use adventure works 2012 so we're telling it what database we want to look at now i want to perform this command here so we're going to go ahead and highlight this and this is kind of cool because if i have several statements in there and i want to execute just one of those statements i can do so by highlighting the text that i want to execute and then hit the execute button or hit the f5 key so we'll hit execute up here and we get a bunch of data so here's all my rows of content with the columns and like i said this is the most basic the easiest syntax that you can write is select asterisk which means give me all the columns from wherever i want to review retrieve the content from so we'll be spending some time in here uh again i want to kind of show you that this is the the object explorer and we have the details they're going to be showing up here we're going to spend most of the time in the new query window um executing queries that allow us to retrieve or insert or update content but this is a just a little bit of an introduction that's a sneak preview of module four so it's a sneak preview of module four we're going to do a ton more in here in fact i'm probably going to resize this so you can see some of the commands here that we'll be working with but you're going to see for this database here adventure works we have tables we're going to introduce these views synonyms the fact i said that right the first time it's good uh program oh i spoke too quick programmability um uh storage and security so all of these are per database we're going to talk a bit later on in security so we'll get that a little bit more information and detail with that all right anything else we should show while we're in here just as an intro you think a pete you know i had a question for you if you wanted to go back to that you've you've said a couple of things that i think we should clarify okay um you use the word schema can you talk just briefly about what you meant by schema so really what happens is when you create a pro a table or a view it has to be associated with an owner of that so they're using the word owner or schema is where it resides and who has access to that and how that is stored in that database so the schema or the owner of that item is used this is kind of this is a two-part name so you actually provide the schema and the the object name you can actually go up to more well up to four parts we won't get into again here yet uh actually won't get into in this specific class so it just identifies where inside that database uh that content or that object exists so it sort of references kind of the definition of what that object is yeah exactly okay got it right um and then i think there's one other thing that um we should talk about because we're not talking about it okay um in my example of the spreadsheet we talked about that i have all of these rows and all these columns and it's getting kind of unwieldy to manage and you've introduced this concept of a relational database that has tables that have used rows and columns that relate to records and properties and that whole that whole genre of sort of information we kind of refer to that as data modeling right and we're actually not covering data modeling in this session but it's a really important topic that you guys will want to go read more about that is sort of the technique for how you decide what to do with that giant spreadsheet of information and break it down into the individual parts and we're going to reference that sort of thing along the way but i just want to point out that how you actually do data modeling and how you do the design of a relational database is not specifically covered in this session yeah and you're right there's entire courses on that and that there's uh there's a lot to that piece that database design is huge before you even roll out any any data so taking that very seriously and understanding what you're doing there will be really beneficial for you as you're looking at uh taking your ginormous spreadsheet and trying to get it into a relationship and we'll we'll allude to some of those things as we go along but i just wanted to call out that we're going to talk kind of about the mechanics of what a database is and how the pieces of it work but the next thing you'll want to do when you actually create it is you'll need to understand that data and then do that sort of analysis and design of what the data model needs to be so we'll see some of that along the way i just wanted to point out that's an opportunity for further study yeah perfect yeah and it's a very very important part of the the process here all right so i don't think there's anything else in here we'll be coming back we'll be spending a lot of time inside of these the ssms sql server management studio here so let me go back here oh that's why i didn't want to do this right there and let's jump back into our slides here where i go here what's where we were in powerpoint aren't we yeah we're using that guy okay so that's just a brief introduction to uh the sql server management studio we're gonna just kind of wrap up this module just a little bit of a review remember a database is kind of taking your ginormous excel spreadsheet and just creating multiple tables and setting up relationships all what we're going to talk about but it's simply a way a different way to organize that content is not only different it's going to be better you're going to see that we can generate reports we'll just say or retrieve content and slice and dice that content in ways that we could not do in an excel spreadsheet microsoft sql server access my sql all examples that we can use for that we talked about the idea of a database management system or a dbms so you're going to hear that term a dbms or rdbms which is a relational database management system and really it's just a plethora of applications that allow you to perform different uh tasks associated with that database for you know we talked about right now we looked at the database engine i mean there's reporting services out there there's analysis services out there nothing that we're going to get into but an rdbms has several components that allow you to manage that content that's in your database your database server or servers most likely you're going to have multiple servers whether it be virtual or physical are used for hosting the content managing the databases and making sure we've got high availability we've got information readily available so that's managed using our dbms on multiple servers database servers most likely and then we're going to spend most of our time is in the sql server management studio that's the interface that we're going to use inside of sql server for creating our databases and managing the content within the databases as well as managing content or managing the uh the logical infrastructure the rollout the deployment of sql server so with that said we're going to wrap up on this section here and we are going to go ahead and take a brief break here and then we're going to come back and continue in module 2. welcome back to database fundamentals this is module two of database fundamentals and it is about relational concepts uh in this module we're going to talk about referential integrity and normalization normalization which is another big company definitely that's right and i think we're going to get into a couple of other small topics around constraints i think we should we're probably going to introduce constraints that that's at this point so we have an idea as to you know how they'll help with the referential integrity piece of it but this is model two of a five module course if you've skipped module one we talked about some of the core concepts about databases and we'll be going on into other fireworks demos later on about how you create objects in your database and how you get information in and out of your database and then a little bit about them in administration of your database um but now we're going to get into some of the relational concepts so not quite data modeling yet but we're going to talk about sort of the concepts that are the fundamental pieces you need to know before you can actually go and create a database well you brought up a good point in the in the previous uh session where we talked about the idea of data modeling so this really focuses on that aspect of data modeling in the sense that okay i've got my ginormous excel spreadsheet you guys got me convinced i want to move it into access or sql server how do i go about doing that well how do i decide uh what do i take into consideration when i'm actually going to design that model that i want to use inside of sql server access so what we're going to talk about here is what's called normalization uh and we're going to look at what referential integrity and constraints pretty much what we've already discussed here so the idea of normalization is really it's kind of it helps us decide how we're going to build our our tables out for our content what this does for us is it allows us to use a strategy that's been designed to determine what content is going to be stored where so normalization is the process of organizing that data in a database within tables and establishing relationships between those tables we hinted to that in the previous session uh the process is used to also help eliminate redundant data so if we go back to our dvds we had talked about that we have you know hundreds of dvds and if we have a genre of jaws i might have that title in there 50 times 500 times it depends how many you know dvds i have that are jazz related so the idea of that is just kind of a little bit overwhelming so if you look at a spreadsheet that's got thousands of rows and 30 40 50 columns across if you look at it you're going to look and see a lot of redundancy and one of the things that helps with normalization or normalization helps with i should say is the fact that we can eliminate that redundancy by default there's five normalization forms as you can see here one is eliminating repeating groups all right eliminating redundant data is number two in the third levels eliminate columns that are not dependent on the key we'll introduce what that means in just a little bit four and five are isolating independent multiple relationships and semantically related multiple relationships we're not going to drill into those at all i just want to show out there if you look up normalization you're going to see five levels most databases are designed to three and that's what we're going to focus on here so the first module we talked about we're not going to talk about data modeling and now you're showing me that there are five normal forms of normalization and we're going to talk about just the first couple of them so this is another like this is a huge school of of thought around what normalization means and you go really really academic but yeah we're going to keep it simple and talk about those first couple yes um but it's another opportunity for further study yeah this could be hours and another whole entire class i thought you could talk about normalization for like three days exactly all right so first normalization how do we how do we work with our content the first normal form means the data is in an entity format which means all of these conditions have been met we've eliminated any repeating groups and by the way we have an example of this so you're going to see this with like okay it looks good in print and textbook what does this mean how well we're going to demonstrate this for you create a separate table for each set of related data boy we talked about that with their cds how we're going to create a table that are instead of having stored in excel we're going to create a table for uh you know for our d our artist and maybe for the generals and for the different different types of tables and then we need to kind of tie those together and we're going to identify each set of data within the table we're going to tie that using what's called a primary key and we're going to introduce that as well we're not going to use multiple fields in a single table to store similar data that's pretty much what a spreadsheet does for us we don't want to do that we want to avoid that so we're going to set up this relationships between the tables to allow us to create that and we do this with this is just introducing the first normal form our second number form ensures each attribute or each column describes the entity so again if we go back to our our cd our cd is going to be we're going to have a row for cds we want to make sure that every entity or every column in there is directly associated with that cd so it's going to and we're going to actually tie these tables together we're going to be able to reference one table or cd table to our genera table by using a for what's called a foreign key and again we'll i promise you we'll show you what that means here records should not depend on anything other than the primary key that's in that row or that table and we'll and again we'll expand on that and we can create what are called uh composite keys composite keys in order to make something unique i may have to use multiple columns so we could and we'll explain that as well so we could create what's called composite keys to make sure that we have the uniqueness in those in those rows third normal form checks for what are called transitive dependencies the eliminate fields that do not depend on that key so i might have extraneous information in there that's not relevant and i want to put that in a separate table so i may do that in the moment as part of the third normalization of the the third normal form when i'm massaging that data to make sure it's all in that third normal form we're going to make sure there's no no columns in there that aren't directly related to that cd all right and again we'll we'll show you that here in general if the contents of a group of fields apply to more than a single record those require us a separate table so again just conceptually all right brian you've got me convinced that we want to take our our dv or cds we want to put them in to take them out of spreadsheet put them in a database how do i do that so we're going to look at how we're going to address that using this third normal form as i said there's a couple other ones there's a fourth one called fourth normal form called you know bcnf so i'd have to try to pronounce the names we're not going to worry about those there's a slight risk of not having the perfect design but it shouldn't affect the functionality if we don't implement the fourth and fifth normal forms and that's why we're not even going to address those at this point here all right let's take out this table here this table here is kind of like a spreadsheet at this point but we've got unnormalized table we've got a table here that has student student number to be more precise advisor the advisor's room number where we can find that individual and then for student 1022 they have a class 101-07 class number two is 143-01 159.02 this is an unnormalized table at this point so what we do is over the next few slides we're going to take that content and we're going to normalize that content we're going to do implement first normal form first then second and then third and you'll see what we have for a result of that well if you remember correctly the first normal form as it says here is no repeating groups should be in that table so notice what we've done here we've taken class one two and three which is a repeating entity and we've our group and what we've done is that now we're going to have student so now we have student three times we have student1022 with the same advisor with the same advisor room and the same class number so you can see we got rid of the repeating groups but now we've got some redundancy we're going to address that in the next normal form in the second normal form so we've taken our unnormalized data and we've added some additional rows to it and we've consolidated the number of columns that are in this table all right so we've taken that content and we now have no repeating groups because we've applied the first normal form uh process now as we move into the second normal form we're going to eliminate eliminate redundant data so now what we've introduced we've taken that one table and we've created two tables the table now is called students so for student1022 his advisor or her advisor is jones and that advisor can be found in room 412. student 4123 has a different advisor she can be found in room 216. so that's a separate table now if we go to registrations because we still can't we can't just drop the information about the registrations we created a second table for the student number student number 1022 it's going to be attending class 101.07 as well as 143.01 as well as 159.02 that's a pretty light low don't you think only three classes come on dude slacker here um anyway and then student number four one two three also are taking three classes so we've taken the content we've split them out now after first normal form we've introduced a second table that's going to allow us to uh avoid or eliminate that redundant data let's take it to our next level our third normal form eliminate data not dependent on the key let me go back just one second here we're gonna see here that in the student we have one zero two two we have uh with the class number but we have multiple rows and you can see uh the students that was under registration excuse me the students we have student advisor and advisors room so if we go to our third normal form we've got faculty we've got student number 1022 their advisor is jones now remember if i go back i'm going to put back one more time when i look at this i get student number i've got jones and i've got the advisors room was 4 12. well the main item or the main column is your student number that advisor room is not directly associated with the student number so it does not belong there if we're going to normalize our content hence what we do here is we introduce a third table now we're going to have a student my rows are off a little bit but we're going to have our student which is 10-02 their advisor is jones now i want to figure out where's jones uh where the room number is for jones i'm going to jump over to the the table called faculty and that faculty table is going to have the room number and also we're going to include some additional information about mr jones and that's the department number that they belong to all right so again no redundancy and every table or every row in both these tables so far are directly associated with student number so we've got student number the direct association advisor the advisor the direct association is the room number and the department number now we go to our registrations table we have student number one zero two two that's going to be attending class one zero one zero seven one zero two two will also be detecting 143-01 and as you can see it rolls down that way so we've applied the third normal form by looking at these because now we have our student number as one table and it's mapping to the faculty table using this advisor name and it's also mapping to a student table using the student name so we've taken this spreadsheet or this unnormalized table and then we've created three different tables out and we've applied the third normal form first normal form second third second normal form and third null form to get that to what's called third normal form in the normalization process you you may have just blown my mind a little bit can we go back a couple of slides and look at the original right back here so here's our original yeah so i just want to just talk through that one more time we've got student information we've got the advisor information we've got the advisors room and we've got the schedule for each student in one table that's the unnormalized table on the slide and you just kind of took us through the three levels of normalization and we ended up with a student table an advisor table can you go on to the next slide there that's a little registration table uh right so the the student the faculty and the registration so we took that one unnormalized sort of spreadsheet like table exactly yeah and you broke it up using these basic principles of normalization into three discrete entities of information and just summarize that for me so we went from one big table to three different ones why is that going to be valuable to me in my database well when we see it so what now when we see when we start retrieving this content so first off we're not going to have a bunch of if we go back to our cds we're not going to have a bunch of jaws in there a hundred thousand times or 50 times we're going to eliminate a lot of redundant data so now if i was if i normalized an excel spreadsheet at a thousand rows and 80 columns and was able to normalize it in database tables i'm going to have less data this could be stored in there because i'm not going to have jaws in there 500 times i'm gonna have jazz in there once it's gonna be a one table one time and i'm gonna reference that table every time i have a cd in there i'm gonna reference that table and say yeah jazz this associates to you know general id one two three and i'll reference that so i'm actually creating a cleaner environment less redundant data and all my columns that are associated or within a table the new tables are all relevant to each other they're all associated with each with that with that primary column we're going to talk about that what that means in just a few moments we've already introduced the term the primary key and we're going to actually um roll that out and show you how we do that in just a few moments here so we've kind of cleaned it up we've got more tables you're thinking i went from one table three but now when i get ready to retrieve when we get module four and i want to be able to slice and dice this information it's going to be so much easier for us to do so than what we could do in a normalized data great table thanks yeah that looks really good great thanks all right referential integrity so this process that we just talked about too so let's just review a little bit on the normalization if you're thinking about where do i start you can use the third normal form first second third normal form to try to look at the content that you have in your in your spreadsheet or in your unnormalized data table and start like all right how can i create this environment this object modeling that we were talking about earlier to create this environment that allows us to have this data and implement that cleaner environment by using the third normal up to the third normal form referential integrity is what we use to get those tables to talk to each other so now i've got three tables brian it sounds like it was a lot easier to have one table because they're all in one spot granted it may have been easier for that piece but what we're going to be able to do with this content when we kind of slice and dice it because we're going to see in a couple modules is really blows away the idea of what we can do now with our when we're in excel spreadsheet and we go to the top and we can click the column header and sort it ascending versus descending we're going to have a lot more we can do with that so that's that's the good news referential integrity is getting those columns those tables to talk to each other so this includes or you'll see this reference referential integrity is ri it's a concept to ensure that you have relationships between the tables that we just created ri can ensure that the data is clean and make sure that data that we're adding is valid data so not only have we separated content out we've now created an environment that's going to enforce that when i'm adding data it's going to be valid data and we're going to do that by introducing what are called primary keys and foreign keys so if we go back to let's go back i'm going to go back a couple slides here if we go back to here our student number is a primary key we'll say and what i can do is i can have it or i'm sorry as a foreign key and my bad again my registration is has a student number on it and it also has a students over here in this table here so we're both using one zero two two so what we do is we're gonna reference one table to the other table one table will have the primary key and one table will have the foreign key so the foreign key is used to validate the content or the value that i'm adding is a valid uh valid value kind of sounds a little bit redundant but it's not so if i'm using the students my registration when i go to register it's going to look at 10 0 2 2. when i enter in if i typed in 1 0 2 1 it's going to be bop over because that's a foreign key over the students here it's going to jump over there and say there is no 1 0 2 1. so not only does it enforce uh the referential integrity it enforces that we're adding valid content by making sure a student one zero two two exists or is the student one zero two one exists uh before it allows me to add content so not only gaining the normalization of having the ability to slice and dice it's actually performing data integrity by ensuring that i'm adding content that's that's valuable so if there is no student1021 and i try to add content into registration it's going to say dude i can't work with you because there's no student one zero two one but being there is a student one zero two two when they register for classes it's going to check to see student one zero two two exist in the student's table it's gonna also check to see who the advisor is and if there's information that we need about the advisor we're going to be able to write queries select statements to be able to retrieve that content so that's the idea of primary keys and foreign keys and allowing us to be able to reference the content between tables that's that referential integrity to give us the ability to kind of double check or cross check with the with the tables that are involved to make sure the content that we're adding is valid and to make sure that we're able to retrieve this content as we as we move on down the road and we'll look at some of the some of the select statements now we'll talk more about this we start looking at the keys but with these keys involved we can only have one primary key on a table and there may be other cross checks that i want to perform to make sure they're valid so we can also apply what's called a unique uh con constraint or a unique key constraint what this does for us is allows us to not only just do a cross check on an individual column with a dissociated or composite key associated primary key but also i can do cross checks on what are called unique and that what that does is enforce uniqueness for us triggers can be used to enforce reference and referential integrity as well they require some code a trigger example would be every time i insert a column i mean it may be as simple as sending email to the advisor saying that there's a new student that you that's been assigned to you so it could be that simple when a certain action takes place that trigger performs tasks or multiple tasks it could be as simple as sending an email to the advisor saying student now 1057 has been assigned to you so they receive an email saying that they have another student that may show up at their door looking for some assistance when it comes to the uh performing the advisor role so that's referential integrity p anything you want to add to that it kind of just blew my mind again i'm gonna just back up i gotta quickly talk you went all the way to triggers and what happens with respect to referential integrity when something is added to a table you can invoke a trigger which invokes functionality and i'm still thinking about my cd library i'm trying to imagine how that's going to come into play so that's another super advanced topic we're going to we're going to get a little bit in the next couple of modules we're going to talk about some of the functionality you can do in a database management system where you can actually invoke some of the code some programmatic code on an event or a trigger on a database but i think i think we're going to get into a demo here in a minute where we'll be able to summarize some of this but i think normalization pretty complicated because i went from a whole bunch of columns in a single thing to a whole bunch of smaller pieces that have fewer columns right right so i kind of understand that even though we're not going to talk about creating tables like like actually creating a table we're going to talk about the next module right but now we're conceptually i get the idea that i take this unnormalized collection of data which is my cd library or your original example of the inventory was a good one with a whole bunch of products and a whole bunch of metadata or columns about the product on a shelf at a grocery store right i can see how i might break that down by brand or i might break that down by category um category seems to be a common thread across all of these examples it's pretty good right but it gives us a logical way to put things together so that they're in discrete pieces where i'm going to be able to i think use them in a smart way to answer different questions about my data that i have a really hard time with in this sort of unnormalized spreadsheet like view exactly right okay good yeah i'm tracking you good recap definitely yeah yeah but triggers seriously triggers is like 300 level yeah let's keep it on the level so we'll keep it yeah right here i just want to introduce the fact there are there are ways for tasks like including triggers that can be used to enforce referential integrity we won't get that deep in the in this class but they are out there for us all right so methods for enforcing referential integrity we just introduced primary key foreign key unique constraint indexes we haven't talked about yet and triggers here's the idea of a composite key so i may have a column i may have a table that for instance let's say we have a table that contains driver's license when if i go in my driver's license for arizona is one br one br 549 if anybody remember that reference to something that's an old tv show anyways if my driver's license is br 549 and i live in arizona and pete's driver's license is also br 549 how can we make that a unique key in a table we can't by itself but what we can do is include another column called issuing state so now my composite key would be br 549 a z his would be br549wa so now those two together makes that data unique okay so that's what a composite key the primary key has to be unique it may not be possible by doing it with that one column so we may have to use multiple columns if we use more than two or two columns or more it's considered a composite key all right so if you see that because sometimes you might see where it says something about having a primary key is like well it's got three columns well in order to enforce uniqueness we need three columns so that's what the that's what that's alluding to when you see that composite key option here all right so let's go stay still staying with with referential integrity we're talking about constraints now here's that primary key constraint and i just mentioned this a little bit already kind of jumping ahead uh primary key is on one or more columns it's required to provide the uniqueness in a row uh in a table that helps enforce the referential integrity often it'll be referred to from another table using a foreign key but a primary key is an attribute or a set of attribute attributes that uniquely identifies that row so if it's a set of attributes it's that composite uh primary key that we talked about a table can only have one primary key on it not one column but one primary key that could be a single column or multiple column and a column that participates in this composite primary key or individual primary key cannot allow no values which means content has to be added there so i can't just skip by when i'm adding content so if you're going to make a column part of a primary key constraint then it requires content being added to that and you can enforce that when you're creating the columns and we'll see that a little bit later on so a primary key is used for that the foreign key is used to reference that content so the foreign key is a column or a combination of columns if the primary key uses two states two uh columns like we did with the license number and the state code well the foreign key referencing that has to use that to mention to reference it as well so it references it as a unique constraint that's created over there for that primary key unlike a primary key and foreign key can have null values so be careful on how those two are implemented because they're implemented slightly different but you're going to see that a primary key is going to be accessed by the reference by a foreign key so it's going to allow us to enforce that referential or data integrity by making sure that we have valid content when we're adding our content into our tables so this is the diagram we saw earlier and what i did all we've done here is we introduced earlier on that we had the foreign key which are the blue items and we had the primary key which are the red items so now what happens is when i add a new product into the products table over here when i type in a value for product subcategory id it's going to jump over to the products subcategory table and make sure that this prime this foreign key references a primary key here now inside of this table it's like oh by the way this is a subcategory if i'm going to add a when i add an item to this this product subcategory it needs to make sure there's a a major category at the top category so it's going to check this table down here so see how there's all sorts of cross-checking that's going on as of adding content when i add an item here it's going to double check to make sure that's a valid item here when i add an item here it's going to make sure it's a valid item here this product category id here is a foreign key that references the product category id in the pri in the product category uh table so all this cross-checking is making sure that when i'm adding this content it's all valid content that i'm adding and these tables are tied together the relationship is built between these tables using these keys can we talk through that one more time sure let's go look at that slide so we have three tables so i'm going to invent i'm going to go back to your grocery store example here for a minute sure we've got a spreadsheet full of all the products with categories and subcategories and it's in one thing right and i went through the exercise of denormalizing my data which led me to creating these three tables and then with the referential integrity concepts there are these specific relationships that include primary keys and foreign keys in these tables that kind of manage the relationships between these tables correct right so let's just go i'm going to give you one example and you can i'm going to try and talk through this you can correct me if i'm wrong all right so in my grocery store we sell strawberries okay and so in this example and you've got a whole bunch of columns i'm not going to worry too much about them but strawberry is the product it's got some product id which is unique and i can see there's a key next to it so that's the primary key in the product tables of product id so we'll just say strawberry is number one okay product id one name is strawberry it's got some product number that's involved in my inventory system its color is typically red those kinds of things exist about the product strawberry right and then the product category which is the on the right side of the slide at the bottom uh we're going to say that there's a product category called produce nice right and the name is produce um and it will have a product category id of i don't know maybe produce is 25. okay right so the subcategory of produce for my strawberry is going to be fruit right or berries or something like that right that sounds good yeah fruit what called fruit fruit i don't know strawberry is the fruit and the tomatoes and tomatoes that weird one doesn't get confused tomato i think it's a fruit i think of the vegetables we're going down the whole strawberry yeah so subcategory of fruit has its own primary key uh for fruit but then has a foreign key relationship to the category which is produce correct and then back on my product side when i list the category the subcategory specifically there of fruit it's going to have a foreign key over to the primary key in the product subcategory table exactly so i've got this this list of stuff that includes uh all of my products and i started with a spreadsheet but if i want to get it into a database to denormalize it which means i need to think about all the redundant data and how to simplify it down and then i have to figure out how to have relationships between these tables and include primary keys and for foreign keys so that i can keep my integrity my data all together so that i can uniquely reference things like the category and subcategory and the product name and those kinds of things yeah do i have it is that kind of what you're talking about explanation okay definitely yeah so those guys make sure the data is being added is validated that's good that's what we want to make sure again we take to take it our denormalized table or unnormalized table we've normalized it it requires some additional tables we've got to get those tables to talk to each other they need relationships primary key information and i can start to see where we're going now because in my spreadsheet i have fruit listed in one column for everything that has a row that is a fruit right so i've got strawberries and bananas and everything just says fruit but i don't have any idea how many categories i have because fruit is listed 25 times and you know crackers is listed 25 times and i i don't know but if i do it this way and i break my data up in these tables then i can just like look at how many categories i have because it's only listed one time right in the category table that's back to normalization i've eliminated that redundant data absolutely okay i can totally see how this is going to be a lot easier for me to use yes and we start and we start retrieving this content in another module too the way we can slice and dice and generate information compared to our spreadsheets is going to be phenomenal well now i'm really curious how many genres of music i have in my collection and i want to know like how many records i have by a particular band yeah yeah and right now in my spreadsheet it's really hard to get that kind of information out yeah you can filter you can sort it and stuff like that yeah but then i have to get it i have to count each row exactly complicated definitely all right so hopefully that helps clear up the idea of taking a a table normalizing it using a third normal form taking the table normalizing it and now okay we've got all these tables i got to get those tables to talk to each other so we use primary keys and foreign keys to create relationships uh between those tables so they can talk to each other and i'm able to validate content and i'm able to see or you'll see shortly to be able to retrieve content using table uh content that's stored a day that's stored in multiple tables i i'll be able to retrieve that content so we're going to be able to to go ahead and move that all right do we want to try to do um a little ad hoc let's do it live i think we should run let's i think we should work on my cd library right now okay let's do this so i need to come out of this screen so i can go into one of these deals here so in this case you're going to be my database administrator i'm going to tell you about my data and i'm going to try and guess what sort of normalized form and things yeah i need to do but let's see what are your cds you said right we're going to do the cd library and let's see if we can create just the basic structures that we need for what i think the tables are going to become later on okay and i'll talk you through sort of like the grocery store example but i have this this huge spreadsheet and it's got all of the cds that i've got all the albums okay right so i need um and it's got a whole bunch of columns in it like artist and genre and the date that i got and like um there's there's probably 25 because i keep track of a variety of different ways that i like to sort them cool so but let's just start with those three it sounds like now we're talking about i need something to manage the cds but i don't want the artist listed redundantly right because now that i know about normalization i want to eliminate the redundancy right so i probably need something to track artists okay so let's add a table here so let's start with the first one let's just start with the cd not a table i'm just going to throw a square all i do is square boxes here oh it's got some gold factor on it for some reason let me get rid of no fill here all right this is this is sort of the fundamentals of database design using powerpoint yeah pretty much exactly which we don't necessarily have to do this right there are lots of tools actually that do this in fact vizio has some great um designers for doing relationship modeling and things like that but we're going to stick simple here yeah we're going to just we're going to call this one this first table is what i think we can call this uh cds cds okay although cd nobody buys cds anymore right it's all digital so maybe we should call it albums there you go that's a good actually let's call it albums okay good that way we're modern and ready for you yeah now it's so important for your immediate changes going sounds better that way so now we're going to go ahead and do this we're going to have to add another table because you said you wanted to yeah i think it the the question that i want to answer about my data is how many categories or how many genres okay do i have so i think i can kind of detect that since an album has a genre um one of the things that i want to learn is how many genres i have i think the um one of the possible things that we're going to need here is a genre table all right let's see here i kind of can't get this guy here can you actually make that bigger do you want to just maximize your powerpoint sure let me do that yes it will maybe you can make that slide thing smaller too and we get these boxes a little bigger gotcha sorry that how about that now i can see it good thanks nice yep good call look good to me i'm just sitting right in front of me right this center so that's going to be albums and then that one's probably going to be genres all right i got to figure out here i can just fill a lot of this thing here this is really just a chance for you to demonstrate your power probably my lack my lack of skills and power for all posterity all right so and here we're going to add this is going to be i think you just click and type all right just click the box to select it and just start typing that's going to be genre except now i think you've got white text on a white background yeah you can't see that really i can see that perfectly clear i don't know what to pronounce because it was originally blue and you made it white and now there you go okay great so that's genre the other one is album and then i want one more for uh artist oh yeah because i think it'd be really interesting to figure out how many albums i have from a particular artist who's your favorite artist do you have a favorite really you know what i've been a drummer for as long as i can remember walking so most of my favorite bands have really awesome drummers the last band i saw live was the dave matthews band how's that answer and i have a whole bunch of his records in my collection so okay that might be our example all right and this is gonna be art and that's gonna be artists so i've got this spreadsheet it's got all my albums in it i've got a whole bunch of metadata including artist and genre and the date that i acquired it but now with normalization i can break those things down into these tables so that i can look things up like um the list of artists the list of genres but now we have to do the relationship between them so right let's just type in these boxes then i need primary key for everything right right so that i can give it a unique identity so just click in the box and what would we call the primary key for the album table probably like album id the easiest one i don't know why it keeps defaulting to white text it's because we tricked you and you changed the color of the box the first time but that's fine good so that's going to be album id and is that that's my primary key for that table can you just maybe put in parentheses that's primary yeah that's great so album id is over there and then we probably need primary keys for the other ones also right yeah just put it after artist just hit return there it's uh let me get jump back in here there you go yeah see this guy here doesn't like you need to get your insertion point on it there it goes there you go artist okay this pk is going to be artist id probably right sounds good i'm sensing a theme here i think we're going to probably talk about some naming conventions and stuff like that in the next module but absolutely just conceptually here i have a primary key there and then i've got another one there for genre i'm going to guess this is going to be genre id that looks consistent to me can i spell that correctly i don't know yeah that's right okay good it's good enough for for the example anyway yeah um so primary keys gives us unique identity for all these things but now i need to have a relationship between them so that i actually can do the look up things that i want like um uh how many albums do i have that are in the jazz genre right right so how would i make a relationship between genre and album so we need we need album id to be able to access genre to check for for that album id select check for genre and conversely i probably want a genre id in my album table correct right so i can get typed in so which one do i want that's actually a really good question do i want an album id in my genre or do i want a genre id in my album you want genre id and as a as a foreign key in albums okay it's going to reference the primary key in the genre table got it okay so that becomes a foreign key in the album table right and then we don't have to draw the lines between them like you showed us on the other slide but the genre id in the album table is the same genre id so if if i have dave matthews band live at the gorge which is a which is complicated because it's a three cd album we'll call it one album we're not gonna go there right now how many cds are in that thing but if i go to the dave matthews band album then it'll have a genre of rock but it'll have a genre id in the album table and the genre id will map to the genre name in the genre table correct so that i can say how many albums do i have that are rock i can look up by rock and i can find dave matthews is that roughly what's going on here yeah so i'm going to actually add a name here so yeah genre name is probably a good idea yeah so we do we don't have to do the whole data model yeah and i think we've got a couple of examples now but tying that first example of that giant spreadsheet we've got products we've got categories now we've got our cd library we've got albums and genres and artists and i imagine to just to complete this thought we'd have a foreign key in the album table that is the artist so that not only i could not only can i look up my albums by genre but i could look them up by artist exactly so suddenly my complicated spreadsheet that has you know however many hundred rows in it becomes broken down in a normalized relational model with the referential integrity so that i can look things up between them in a really simple way and we're just totally foreshadowing where we're headed in modules three and four where we're going to talk about how you actually create this in a database look at you trying to draw the lines in powerpoint that's very adventurous yeah yeah yeah i know i think it's like typing right so we're gonna do in module three we're gonna talk about how you actually create these in a database we're gonna use sql server as our example correct and then in module four we're finally going to get to the query fundamentals about how i can do the lookups and do really cool stuff using the t-sql language yeah that's cool i think that's the cool data this is where it really comes together like you're used to being in your excel spreadsheet it's like i've got limited functionality by creating this and doing this the performance data modeling the normalization process creating these tables creating the relationships between these tables when you start looking at some of the complex select statements not real complex but some of the a slightly advanced select statement you're going to see the power of why you would want to do this especially if you have lots of rows of content let's go on to the summary slide and then i think we'll wrap up all right we'll get on to module three so we talked about normalization which we talked about remember there's five normal of five forms and um we talked about five normal forms for that let me go big screen here there we go and uh first first one second one and third one we're worried about those the most that's going to give you uh you know no you're going to eliminate repeating repeating groups eliminate redundant data and it's going to eliminate any content that's in a table that's not relevant has to be associated directly associated with that key so we're going to do that first we're not going to worry about the fourth and fifth normal form referential integrity we talked about that that's what ensures the data that we're adding is valid we saw that with with the cross cross checking when i add content when i add an album id for instance it goes out to make sure it's a valid album id if we go back to the student it goes out with or we have a valid student or we have a valid advisor tools we can use for our financial integrity we showed you a quick demonstration ad hoc kind of worked okay primary key and foreign key constraints um again if you want your uniqueness and other columns besides the primary key you can use a unique constraint and we'll look at indexes a little bit more down the road and we just did a brief explanation of triggers just so you know you have that option that's out there this is a brief explanation of those different types of constraints that we have available or ways for us to enforce referential integrity and we again we demo primary key foreign key unique constraints will probably be very popular when you're trying to enforce uniqueness and you've already used your primary key for a table and a unique index is going to be created for you when you create a unique constraint in fact we'll talk a little bit about indexes not a whole lot about that and then we introduce the idea of triggers can be actually used for um kind of introducing some code that might help with enforcing referential integrity uh by ensuring that someone oh did you really mean to delete this particular item so you can actually use a trigger for that as well so what we looked at here was more conceptual we looked at the idea of normalization we talked about that we looked at introducing constraints and we attempted to draw out a diagram on how you might take your your album collection i like that better than cd or dvds and use something like a database product like sql server or even access to take it out of the spreadsheet and give you that extra flexibility and start introducing the idea of relational databases and tables within those databases so we'll be back and we're going to continue on with our conversation in just a little bit and we'll continue on and continue discussing discussing what we're going to do and how we're going to really utilize the app the options of the referential integrity that we've just introduced and we're going to get more hands-on with what we can do is when the finally create a database yeah we're going to start creating some stock now that we've been kind of setting up with what we how we want to do it we're going to dig in and kind of roll up our sleeves and start doing it welcome back to database fundamentals this is the beginning of module three database fundamentals course uh module three is gonna be a fun one we're actually gonna talk here in in this module about creating databases and database objects so to create something fine we finally actually get to do something real instead of all this theoretical stuff that we've been doing but let's just review in module one we talked about sort of core concepts of databases some terminology things that we've covered then in module two uh we talked about what are we talking about talk about referential integrity and normalization and sort of a little bit of modeling but not quite modeling we didn't get into some of the details of modeling but we talked about referential integrity normalization a little bit about constraints and now here finally in module three we're actually going to create our first database yeah we've only looked at diagrams so far except for your quick demo of of sql server management studio we're going to create databases we're going to create database objects you're going to tell us what kind of objects we're going to create things like that and then in 4 and 5 we're going to head on coming up we're going to talk about how we actually use those objects with the t sql language and module 4 and then administration in module five so some real work some real real stuff now let's create i can't wait to see how this is going to go so yeah credit databases and database objects first one is we're going to talk about data types because before we can create objects specifically a table we need to understand what types of data we're going to store on there and that's really huge about it's huge to understand so we've kind of talked about creating different columns we've talked about creating the artist id and the album id well we need to identify what's uh what type of data is going to be stored in each of these columns so it's not as easy as it is in excel where you just throw a column out there and say yep just throw whatever you want that data in that column we actually have to think about this helps with the data integrity we have to think about the type of data we're going to store there we're going to store numbers in there we're going to store money is it going to be a date field it's going to be a character field and we have all sorts of options so we're going to introduce data types first you have to an idea we have an idea as to the type of data that you're going to store that's something you need to do and think about in that data modeling phase that pete had brought up earlier so we'll look at data types so look at database objects uh so i'm just going to mention just a few of them the most commonly used objects and then we'll look at ddl data definition statements so statements are used for defining new objects so we'll introduce the statements that are available associated with ddl statements and then we'll use a couple of those statements just to show you how they're how they all work together with us so data types is going to be our first category that we're going to talk about and in here we're going to begin with an understanding what that is so we've talked about rows and we've talked about columns well the columns are what we use to identify uh that we assign a data type to which identifies the type of data that's going to be allowed or stored in that particular column uh you have similar data types there might be a couple different data types that are similar the idea is i'll use the data type that has a larger range of values just to make sure that you have a better chance of increased precision and you make sure that you don't have an overflow error by not being i'm not having a data type large enough to be able to store the content some exact numeric data types are int and tiny int and these are probably the most commonly used uh data types because they're storing numeric information and then we have approximate numeric types these include precisions indicated by a letter p and that gives us the total number of digits decimal digits that can be stored both to the left and to the right of the decimal point so all of this has to be considered when you're trying to decide what i'm going to be what columns am i going to create for my new table for instance and what type of data am i storing in there when we talked about these the album id is normally going to be most likely going to be a numeric type value description or title or artist that might be able more of a character field so these are things that we need to think about the other thing we need to think about is what how are we going to use these forms these these these columns are data types for instance if i go out and i create a data type of a character field and i put a number in there i'm not going to be able to use that in a calculation i can't multiply uh in a character field it has to be a numeric field for that that operation to take place so there's some things that we need to be aware of when we're working with our data types we have what are called unicode data types you'll see that often these provide storage for international characters so non-unicode uh data types are pretty much specific to the the the usa and the uh new england or not new england english data types or or characters but if you want to bring in other languages native languages we have to use the unicode data types unicode unicode data types create more or take more storage in the database itself but we have to be careful about that but those two things are a couple concerns that we have to think about when we're looking at data types now there are several categories that are provided with uh with us with sql server 2012 and i mentioned already we have an exact numerics we have big end and we have bit we have decimal int money approximate float and reels date time i mentioned that one character fields unicode character strings then you get that letter and before that means it's unicode that provides support for the international languages binary strings and we have some unique data types other data types like a time stamp or unique identifier some of these are actually taken care of or managed by the by sql server so it actually time stamp prints is it stamps takes a system time it stamps that or applies that to the record as you're applying that and we have a couple other large value data types and large object data types which we'll see for storing images um or specifying the maximum amount of that we want to allow in a particular field so data types are we have to understand why how we're going to use them and understand they're going to enforce data integrity for us by if like for instance if i have a an int or a tiny ant i'm not going to put the letter a in there because it's not the character field so it forces data integrity but with the use of by enforcing the information that's being supplied it also controls what we can do with that content or those values once they've been added so we have to make sure when we're going through this that we think through that as we're creating our data types here so here's a couple common ones we've got money so if you store currency you can control these most of these have options where you can decide how many decimal points that you want to the right or the left of them we have int used to store whole numbers sorry is int short for something short int yes int is short for integer thank you very very kindly yes i see i just blew right past that i blew it right like that a couple of times so the tiny ant is tiny integer that the ant that we see here is integer big int is big integer so some of those uh short names i'll try to be more aware of that thank you for catching that but let's i also want to back up before you finish this slide you've gone to a list of the sort of kinds of data types numeric and and non-numeric and then you've gone into some specific data types that are actual keywords in sql server so two things i wanted to say number one it's specific to the database system that you're using so we yeah right you're talking about sql server which is important because that's what our demo is going to be but there's a different shorter list of data type choices in access and in other database systems right correct and they're very similar but but that's one of the ways that the choice of what database you use what database engine you're going to use that's one of the deciding factors is if you need unique identifier or some of the really advanced data types you might need to use sql server because it may not be available in access or a smaller database engine yeah both like you said most of the relational database products are associated with the iso the standard for databases so if you're wearing one database you don't have to jump over and learn a brand new what data types but microsoft sql server as other products have some unique variations of what is available or supported we'll say via iso so uh great so what does that stand for again uh international standards of organizations international centers yeah that's right trivia question there we go so that's uh all right so we have int short for integer we have float that's often used if you need a more precision uh we've got uh smarter in the scientific community and then often we're looking for dates or times or times and stamps we have a date time field as well so those are a few of the variation of those types of data get them more of the a couple other ones are char short for character and anything can go in a character field so we can put numbers and alphabet in there so it's a alphanumeric characters uh can be stored there variable character so a char which is short for character has a fixed length so if i do a char 20 it's going to allocate 20 spots for 20 bytes i believe uh bites are bits i can't remember that i don't know 20 somethings um char so it's going to allocate that number now if i only use 4 there's going to be 16 unused so it's a fixed length a var char variable character length if i put 20 in there and i store four bits of information then the other 16 bits it's only going to allocate what is needed to store the content that's in that type of field so that's the difference between those two another data type is a bit sometimes you'll see as a boolean this is often just going to return a zero which is false or a one which is true so it may be as simple as you know if i want to go out and check is the the today's does the date field equal today's date it returns yes or a one uh if it doesn't you know we can have some program programmable code or programmatic code that allows us to go out and perform some some other tasks we can do some calculations we can do calculations within uh within the fields themselves just using the the data that the data types that we're creating we can also do use something like the date time offset where i can actually determine the difference between a couple dates or the offset of those dates so some of those some of those some of that functions available to us here's an example of some of the different data types in the storage for instance as you can see the int short for integer can store a number from minus 2 billion 147 million 483 648 to 2 plus that should be plus 2 billion 147 483 000. 647. so it's 2 to the 31st if you look at it that way um you know char for instance fixed length non-unicode data any value from 1 to 8 000 characters the bit we talked about already and the date time offset we talked about already so these are again things that we have to consider when we're creating our new tables when we're trying to create our relationships between those tables what are we going to use the data for we could just easily create everything in char or varchar to save space but when we do that now we have issues if we try to if we need a date a time stamp or if we want to try to perform a calculation we'll have some restrictions with that so we have to think through this when we're going through the data modeling and designing our data our tables for uh for our database so these are some examples of the size of the uh of the information and what's stored here now we may have a situation where we created a data type on a field and we want to perform a calculation on it well we can do ease sql server will do is what's called an implicit conversion so there's implicit conversions and there's explicit conversions where i need to do the conversion myself i need to use either cast or convert either those commands allows me to take any of these data types and convert it i won't say any most data types and convert it to a different data type so if i want to be able to perform a calculation as long as the number was in a character field and i want to convert it to you know an int so i can perform a calculation we can do some data type conversions as part of our program when we're doing some programs and using some of the different objects inside of sql server but the best thing to do is if you can is to create the data types with the appropriate columns with the appropriate data types taking into consideration the type of content or data that's going to be stored in there and any type of calculations you may be performing with that data once it's actually stored in there there's an example here in the slide of casting you know dollar 157.27 is a varchar so it's going to convert that and now store that as a var char and be able to work with that using that new data type here but again not everything's supported for instance an n char cannot be converted to an image data type most everything is just stay within the common data types you're pretty safe but if you're going to get into the some more unique ones like image those you have to be a little more cautious about so we talked about just reintroduce the iso the cost and convert can both be used for doing explicit conversion the cost as you can see it's recommended you use that because it does adhere to iso now microsoft or other products have decided to create another flavor that called convert and they can do so and still take advantage of that and still use it just gives it a little bit of a different variation so data types you have to understand those before you create your objects we're now ready to create our objects here's our table we looked at earlier already collection of rows and columns we have our employee id maybe that get that id has a a tiny int or an int data type last name is a var char first name is a vartar department might be a var char with different links maybe we have a varchar 30 for last name varchar 20 for the first name department of rhr25 we define that when we create these and the employee id we decide hey are we going to go above a certain number if we are we need to decide if it's a tiny end or an int or any of the other types that will hold a numeric value for us now in addition to tables which we're going to use the most often we have what's called a view a view is a virtual table and it consists of columns from either one table or multiple tables uh that we've that we've pulled in and we're going to see how one of these are created a view is not really an object that's stored inside of of the database it's more of a query so whatever when i create when i go out to use a view it's actually going to go out and generate that information on demand so it's not going to populate the content in the view when i create it it's going to take the select statement we'll say and it's going to use that to to populate and display the information that i'm looking for when i'm trying to create a view here and here's some examples of some system views that are provided by microsoft that are part of the sql server world stored procedures these are just a group of transact sql statements that have been compiled and saved so they can be used multiple times so i may go in and run the same same command multiple times uh if i'm doing so what it may what may be easier is to create a stored procedure that i can reuse and every time i want to perform a certain task in this case here we're looking at you know the number of items in inventory going back to talking about what we talked about earlier in a grocery store for instance i want to find out how many items are on hand well i can try to store procedure and having the store instead of having to store procedure hold not being flexible enough to be able to be run against multiple products i can actually have parameters passed in so i can supply a parameter to go look for you know strawberries in the produce department by supplying parameters the next time i can run it and have to go look for you know steaks in the meat department so i can create a stored procedure that can be run multiple times over and over again it actually gets compiled which means it's going to run a little bit faster be a little bit more efficient and we can pass parameters to it so i can run it and reuse it multiple times and be able to get results back based on the parameters that i passed into it so this is just a brief little example of how to use that and uh to pass parameters into a stored procedure we can create udfs or user-defined functions these are routines that also can take parameters it completes a specific operation and returns a result of that operation and there's a few different types and we'll see these as a couple of these at least we'll see as we're going through some of our demonstrations between this module the next module but we have a scalar function which is going to return a single value so we're going to see for instance i want to know the average cost of something so it can return that information to me we have a table value returns a day a table data type we're not going to see that when i'm just listing a few things out here and we have a we have system functions which are functions that are provided by sql server in this case here and we can use those in any of our any of our uh statements that we're writing for instance so we can use that pretty much anywhere we want to return any system type information so a few different types of functions that we have available to us and pretty much for this class we're going to just focus on the scalar function and what we can what kind of information can be returned with that here as i introduced the stored procedures and the functions they sounded similar but here's an example of the difference is in order for me to execute a stored procedure i use the execute or excc statement i can't use join stored procedures i can't join multiple procedures together i can't use a stored procedure for modifying sql server a configuration of it and i can't use statements such as a get date in a stored procedure now use defined functions allows me to perform some of these tasks i can call from within another sql statement so i can use i can call a function from within another sql statement i can have multiple udfs working with each other it cannot be used for sql server configuration stored procedures can the user define functions cannot and if there's a problem with the user defined function it's going to stop the function and it's going to no longer continue on where a stored procedure depending on what kind of error checking you've done will continue to perform so just a few high level differences between stored procedures and user defined functions as you're creating these these different objects now one of the things that we often do is we create objects is we have to have a naming convention now it was a couple of the common ones are listed here the pascal case is where we use the first letter of the identifier and the first letter of every subsequent subsequent concatenated word is capitalized so for instance you see employee table the e and the employee and the t in the table is is uh is capitalized uh that personally this is my preference i use pascal case and you're going to see with the scripts and some of the content that i'm going to be showing you in this module next module i'm totally about pascal case uh camelcase as you can see the difference is with the exception of the first word in the descriptor uh the identifier if the first word is lowercase and then every other word every concatenated word is now uh is capitalized so the first letter is capitalized so whatever your choice is what your preference is mine as i've shared a couple times already is pascal case but whatever you're going to use be consistent so when you're starting to rename your objects if you decide to use pascal case or camel case use the same uh naming convention for everything that you're doing so you don't get confused as to why you used it one place and something different in another place and try to keep that as a standard within your organization so you know you've got that consistency when you're creating these objects that we're we're going to be creating here okay we're going to go in and create a database there's a couple days ways we can do this we're going to show you both ways we can do it inside of sql server management studio we're going to jump over there and we can also do this inside using a transact sql statement we'll show you how both of these work for us here so let me jump out and go into my this is pretty exciting this is our first actual database yeah we've only seen pictures of databases yeah and the pictures weren't that well the quick one you showed us but we we didn't actually uh it wasn't it wasn't that cool yeah well a database can be created very easily i mean it's kind of like i showed you this statement here is probably being the easiest statement you can write select asterisks from production.product that's a pretty straightforward data statement but somebody had to do the creation of the database and then they had to create production.product yeah that's that whole schema that's the table so they went through their spreadsheet and figured out what tables they needed that and we're going to once we create our database we're going to go and we're going to talk about creating a table where we're going to use all that stuff you just taught us about the data types perfect and then this is this is actually the foreshadow to the next module where we'll learn all sorts of different ways to write queries that get the data out of the database exactly right yeah and that's the big thing i mean all the upfront work that we're spending time on that you talk about pete is it's really it's not it's kind of it seems like there's a lot of upfront work and getting the tables and getting the foreign keys and the primary key constraints but once that's all done the big bang for the buck we're going to see in the next module you're going to see how much easier it is and more efficient it is for us to be able to retrieve that content and slice and dice it in several different ways so so let's create the easiest way to create a database is go in here to the sql server management studio and over here where it says databases i'm going to right click and do new database and here's a name right here so i'm going to call this um i'm going to call this class demo db now quite honestly i can stop i can right here find my mouse come back mouse there it is i spent off in the real thing was it over on your screen i don't know lost it on my screen so i'm going to go ahead and this is how simple it is from inside of here now there's options that we can change here but i want to show you how easy it is to create a database i just i'd so i all i just give it a name all right i'm going to click ok and if you look over the left hand side here we've got a new new item here called class demo database that's how easy it is create a database in here now was it efficient did i do everything i could have should have done to make it make you know as optimal as possible absolutely not but i wanted to show you if you want to do something today and say hey create my first database open up sql server management studio if you have access to it remember sql server express is out there if you want to dabble with this and then go in right click database give it a name click ok and go out and have a bad because you just created your first database and you're in the sql server world at that point again a lot more we can do uh to make it a little bit more complex let's say it's like we like to type we want to practice our our typing skills i'm going to uh i'm going to actually go out i created a script file the other day i'm going to open up a script file i'm going to talk a little bit more about scripts i'm going to open this up here we should talk while you're doing that we should probably you've referenced this thing called t sql can you just explain what you mean by t sql t sql is like in golf when you t up it's not like that at all okay so that one i do know that much but t sql is transact sql it's microsoft's flavor we talked a little bit about iso standards just a couple of minutes ago so whether you're using sql server or using your mysql or if you're using access everybody they all have a different flavor of a statements that you're going to use they're all very similar we talked about that there are most of everyone most all of them will adhere to the iso iso standard i know it sounds redundant but saying iso sounds weird by itself so most will all adhere to that and then they'll have all of them most of them will have variations they might like for instance we had they were we had the cast but we have a variation of which was convert or so we can have variations of the the different commands but t sql is transact sql and that's microsoft sql server's flavor of issuing or writing commands as we're going to do so when even doing that select statement that was a t sql statement creating a database as we're about to do is a t sql so anything that we do pretty much anything that we do even inside of a stored procedure inside of a function it's ways for us to query or manage or uh manipulate data inside of sql server is that clear does that make sense yeah that's better because you're about to go ahead and open that file yeah which we're going to use for the demo which is full of these t-sql exact commands so the cool thing is what i've done to kind of to so you guys can see if you trust me pete was watching me type yesterday he fell asleep because i'm fat fingering stuff i don't have the proper quotes or i forget a quote or all right too many quotes or it was just always make a mistake so what i've done to avoid the pain for you folks is i i set up these queries and i'll show you how to execute them and i'll explain what every one of them means as we're going through them so i just showed you an easy way as easy as it gets to create a database i go into sql server management studio i right click on databases i do new i click i add a name i click ok done i create a new database now if you like to practice your typing skills here's another way we could have done that all right a little bit more complex but let's walk through this this is a transact sql statement this is going to create a database called sales and then on and we're going to give it a logical name called sales underscore dot and whenever you create a database it gets two files that are created on the drive and i'm happy to store these on the sql on the c drive and my sales.mdf file is created and that's going to store my content and the size of that's going to be 10 the max i'll let that size of that file go is 50. if it needs to stop and grow it's going to grow i call baby increments five megabytes at a time so what this does for me when i create this file that's going to store my content the size is going to be 10 if i'm at nine i try to add a four megabyte file i'm short nine and four third i'm short three megabytes so what this does like well i've got auto growth on so it's going to grow it's gonna stop it's gonna grow by five megabytes now it'll let me to continue to add that content the bad thing is it's gonna keep doing that every time i need to add content so different story but uh we can actually change these and set these to be a bit more appropriate values but i think specifically you showed us you could right click and do create database and you alluded to a whole page full of options that i could have set yeah yeah and your script is using the create t sql statement to create a database that's exactly and it's specifying a variety of those options in code so you can do it with the graphical user interface perfect or you can do it with code it's just a different way to create the database yeah i just showed you the easiest way to create the database was right click bam you create the database you're exactly right if i went in there look at options and i looked at files i could change that so the the size equal 10 the max size equal 50. i could specify the file name the file location i just wanted to show you just to give you the opportunity to create a database that was the quickest easiest way to do that and i think this is an opportunity to call out we've got a bunch of training about sql server specifically and in the database administration course that we have you can actually learn all about the different ways to create a database for optimal growth and minimal size and those sorts of things that you're calling out in your script here yeah optimizing databases again this is the db fundamentals so try not to go too deep hope keeping me on track with that because i start digging deep and next thing i know i'm like well the concept the concept is sound right in access we can create a database by doing file new because it's more of a it's a desktop it's not a database server product like sql server and in mysql there's a way to create a new database and they all have these kinds of parameters about how big should it be and what's what's the file going to be on disk and those sorts of things but it's the concept is it's a simple create statement sql server uses this query language called t-sql that you can use create or you can just right click and sql server management studio to create the database yeah so it's i mean it spends feel if you get paid by the hour you'll want to learn how to type these in if you get paid you know if it's just getting the work done show us that it runs now i don't believe i don't i want to see it happen because you did one with the graphical user face right so this is uh i also want to see you've got a whole long page of scripts there i can't wait to see what's going on i got some other cool stuff in here this one's a small one compared to the next module so this is the the uh the mdf the data file it also uses something called an ldf file which is a transaction log again all the most related and i'll say most relational databases use a usage called a transaction log style so every data modification not retrievable modification is stored in a transaction log file in this case here in sql server stored in a transaction log file and then it's written eventually it's written into the mdf file so it's just a temporary location it's strictly there it's primarily there i'll say for data disaster recovery or data recovery so we need to supply that it built that for me when i use the right click new database it promise you if i go out there that that that content is stored there i didn't get to specify where it went like i can hear though that stuck in this in the default location which is way deep in the c drive somewhere i i this i've created this to show you that you can specify where you want these files and what the name of these files when you're using this command all right let's see if this thing works now the cool thing is it's back in the query uh in the query analyzer query up window here it will only execute content that you highlighted so you notice i've got some commands i don't want these to run down here yet so i'm going to highlight this content up here and i'm going to go ahead now and i'm going to choose the execute up here or i can press f5 so either way will work and it's going to go out it's going to execute this command hopefully it will execute this command and create a new database called sales all right i'm going ahead i've got it highlighted i'm going to press f5 on my keyboard it comes back and tells me command completed successfully well if i go over to databases was it called sales i don't see it here so let me right click and do a refresh and here's my new database called sales and if i want to look at the properties of this real quick and let's be real quick you'll see just go with files for instance here's my 10 and my five and here's my auto growth and again as pete pointed out i could have done all this when i was doing the window when i was going through the graphical user interface i chose to show you just the quickest way for you to create a database and that was the quickest way and this is the this is another way if you get paid by the hour to create a database is to write scripts the cool thing about scripts too i you know i'm making fun about getting paid for it but scripts are beautiful in the sense they're reusable so this is another reason you may want to script some of this content here all right let's see if i get this window back i don't want this window so big here let's get this back down a little bit okay so we have a database out there now remember i talked about the use option i need to add a table to that so the idea that we talked about just previously was hey when you create a new table you need to understand the type of content you're going to store in there so this table i'm going to create is called products1 we're going to call we're going to have a field in there called product id we're using an integer value all right so i can store whole numbers in there we're going to have product name varchar20 we're going to have unit price using the money data data type and we're going to product description using varchar 50. remember if it's char it's so it's that if that was a char 50 versus a var chart even if it uses 10 characters it's going to allocate space for 50. so it's a fixed length where a varchar said only says only allocate whatever you need to store the content so this product description is only 10 characters it's going to have storage for 10 not 50. so not a lot so won't be as much unused space so what we're going to tell it to do is use the database we just created called sales and create this new table in there so if i go over under tables over the left right now you're going to see i have system tables file tables those are just tables loaded by default let's create a new table here so i'm going to go in now i'm going to highlight this because i can just highlight what i want and i'm going to execute this and command completed successfully let's do a double check over here let's go back to sales and do a refresh and go back to our tables and you'll see we have our new table here called dbo.products1 and if i expand on that you'll see our columns and you'll see the four columns that we specified here were also created there so easy enough now we've created a database we've talked about what data types are we've decided the type of content we're going to store in that database we're going to store in this products1 table and we've created a table now that will allow us to uh to be able to enter content in there we're going to talk about doing that a little bit later on though remember we're talking about right now we're talking about a data definition or ddl statement so we're talking about creating creating objects and modifying objects so it created a database we added a table to that database something else we can do and so notice when i created a database i used to create statement i created a table i use the create statement i create a view i create a stored procedure whatever i create if it's an object inside a sql server i use the create statement so it makes it simple for me if i want to change the database or i want to change a table or a procedure or a view i use the alter statement so i create a database called sales i want to change that database name to sales forecast by the way that little green line that says right there what i'm doing that's a comment so anything that's got dash dash and it highlights it notice the color coding by the way the blue is saying these are actual statements these are are the fields these are values that are supplied by uh by microsoft sql server the dash dash says this ignore everything to the right of that it's just a comment so you have to document people always document late but they always document their what's going on here so this is a reminder to me oh i want to change the database i can highlight it and try to execute it nothing's going to execute because it's a comment but we're going to alter the database called sales we're going to call it sales forecast we'll hit execute up here all right the database name sales forecast has been set let's refresh and we'll go over here we'll see now our database now is sales forecast so now we've changed the name of our database now if i want to delete the database i've been asking microsoft for years to change this from drop to toast i like the command toast i want to toast a database i want to toast a table it's still not toast you can write a stored procedure to do that though you can't write a store procedure they'll do the same thing as drop and i've done that before but we won't go there this would have stayed with standard stuff here so i want to drop the database or i want to drop a table or i want to drop a view i use this statement right here toast the database i'm going to highlight this so remember over here we've got sales forecasts we're going to execute who cannot drop because it's currently in use i must be connected to it somewhere modify name toast database let's see here i know another way we can do this come over to the right and i can do a delete here now what's happened is i have a connection to it so because that failed i'm going to come over here there's a connection to it so my guess is if i do okay here it's in progress it's already taken too long it's going to come back it should give me this pretty much the same type of deal it can't do it and they're gonna take this long or else i wouldn't have done this piece but it's gonna come back but i'm almost positive it's because there's a connection established to it and so what i'm gonna do okay now it fails drop failed for database let me do this let me click this guy right here close connection and doing okay hopefully that will do it you know why they don't call it toast by the way why because toasting the database would make it more delicious oh that's true usually it's that's true that is true all right we got rid of our database if you look over here we got rid of it here i couldn't do the drop database there is there are parameters you can supply with that to drop existing connections so we could have done it that way but because i had established a connection to it it wouldn't let me drop that database so it looks like a lot of the things that you can do with the user interface in sql server you can also just write script to do in scripts we're going to talk a little bit more about scripts this is kind of an introduction to them because i want to bring these in so you didn't have to watch me fat finger everything but scripts are really powerful especially if there's redundant tasks that you're performing and let's say i wanted to hand this off to pete so we can go out and create the same database he i just sent him this script he could pop it into his sql server management studio he runs it he's got the same database with the tables and the alteration of it if he wants to so it's easy enough to redo information or tasks that we're performing here all right let's jump back out into our slide deck that's not where i wanted to be here i need to drop this guy this way no yes am i going too far here i'm coming way out i know what i need to do i'm coming out of my connection i need to minimize that here we go and let's go back into our powerpoints let's now get closed we're in what module three yep yeah all right come on now work with me there we are all right so that's creating a database class demo db that's the one we created using the interface and the one that we created um that we're going to create we also created one using a script and we deleted that one after we had added a table to it ddl statements that's kind of what we just did so we actually did the create of the database all right and we did to create uh the table remind me what ddl stands for data definition language so this one so we talked data manipulation language earlier where we do the inserts the updates and deletes well actually no we're going to talk about that we're going to talk about that yeah we'll do that next sorry so we did a ddl as a data definition we're actually creating objects or altering objects or dropping objects so so it's a create creates new objects alters modifies them and drops and or toast any any objects and that's for database all the way down to views or stored procedures or anything like that so the create statement you saw it's used for database table default any of these can use the create statement here's that example that i supplied for you already this is what i showed you for creating that database and the table here's the create new table and this is the one i demoed for you i think i did the demo first and now i'm showing you the content that's pretty funny went in the wrong order i supposed to create the database just inside the gui we were both pretty excited about actually i wanted to do some database i want to do something scripts or with you know queries so this is what we just saw if you look at here it's not all color code and all that cool stuff but it worked you saw that it worked and alter the statement as you saw i took the sales and change it to sales forecast and drop statement whether it's a database or table view role whatever it is it's simple for ddl it's create alter and drop if you understand those three that's the bulk of what you're going to need to understand for ddl so data definition language statements to be able to manage the objects inside a sql server this is where i was supposed to do the demo that we just did so i did a dual demo but this is where let's go back and actually talk about the graphical user interface and scripts show me the cool feature in sql server here about how i can how i can get a script you want a script here yeah we can do that easy enough so what we could do this is one of my favorite parts of sql server because you can kind of cheat because the the lesson here is that there's a graphical user interface way to do things and there's a script way to do things but you don't actually need to memorize how these scripts are created because sql server has this great feature that that brian's going to show us yeah so the cool thing what you're talking about is i created this database using the gui now i didn't populate anything in there or anything yet but what i could do is after i created object using the graphical user interface i can come in here if i can grab it here and i can go into script database as and i can cr i can have it generate that create statement for me so that that's create statement i use to create the sales database i can take this in any object whether it be a view a stored procedure a table and i can have it generate this by creating using the create to now the other thing is is the drop and create two this is kind of cool because what it does it adds at the top of the script it adds to it let me just go ahead and kick this off to the clipboard hopefully that'll open up for me let me do a file so i know i can find it and we'll call this uh uh class demo see even out here i use the pasco case script all right let's go save it as a dot sql file that's my mouse again come back so i'll open that up for you just a few moments in fact what i'll do is i'll do it just one sec here i'm gonna go over here i'm gonna go in here up into where did save it uh it did not save it to the desktop that's all i know i didn't actually catch the package i thought i always say with the desktop because i'll lose it so i'm gonna do it again i think you can just when you go into the script database can't you just do drop and create to a new query editor window and it'll just open it right up yeah okay we'll just do it that way i just wanted to show we could save it out and ship it and then you can save this file this is just a document system where you can save the sql file yeah and this is cool because look at that it already said use master because as to update this content master database this is another way for comments do a forward slash asterisk and then you close it out with a asterisk for slash over here so let me just because you just blew my mind again i just want to make sure we talk through what just happened you right clicked on the database correct you said drop and create into the new query window right there generate scripts drop and create too and then you said new creator window and sql server generated this whole file full of stuff that starts with the use and then it says drop the database class demodb so if you wanted to make changes to your database structure or say a table that you did you could just use the generate script command exactly you'd get a window like this you could make the changes that you wanted and then you could run the commands here right to make the the alter or modify or create changes that you wanted yeah maybe i don't like to you know like you said it's going to drop the database that's right so you can run this multiple times i create it yep not quite the way i want it create it not you know and come back and change it i don't like the location i can change the location here and run it and create it again got it multiple times and the cool thing is it's scripted i can create it i like it it looks good pete likes it i i send it off to him he can execute it inside of his his sql server so i just kind of blew up your whole logic about getting paid by the hour and writing scripts because you just showed me how quick it is yeah to generate a whole bunch of scripts that i can just go modify yeah so your your manager is going to like that right because here i'm saying you know my manager is going to be like what really i'm all about getting it done faster no i am too definitely and here's some other alter database statements so it generates a bunch of items for you these are all the parameters we're not going to worry about but the idea is we can generate scripts again that could have been a table it could be a view anything that you have for objects right click generate script for and there's often they'll have the drop you i always put the drop there um that way in the event it already exists it won't because without that drop if i tried to run this it's going to blow up say the dot the object already exists so to avoid that you can just actually go out and drop that the other thing i wanted to point out is i think this is a really great way to learn how sql servers is doing things because sometimes you can page through the graphical user interface to discover all the options but you can also if you like to read the the script code a lot of people are into the sort of programming aspect of databases you can use this generate script command and actually get a big file of t-sql yeah and then you'll discover all kinds of features that sql server has as you parse through that script and see what's in there yeah good good example of that too is uh if you you want to write your first store procedure i mean like you know it's like i know brian said you could pass parameters there's a ton of stored procedures coming out of the box right click generate a script you get that for a basis now and you can use that for credit for writing your own uh your own stored procedures now all right so is that all i wanted to do in here yep okay good yep i think it's i think it's about time to wrap up the module i think it is definitely so let's see where we are i mean we've covered a tremendous amount of ground in this line we went from fundamentals to suddenly we have our own database with a table and we didn't really create all of the the details yeah um of the table just yet but we're on our way to doing that yeah yeah so we got to talk about data types the different ones there how to create the database both the sql ssms the create alter and drop objects pasco case or camel case choose one of the two all right make sure that you do that don't leave it don't don't mix them up um you already know my preference i won't go in there again you know what we should do before we wrap is we should talk through let's just go back to the original example that we use let's go to the you want to use the grocery store one or the the cd library the cd library so the album one we started with the spreadsheet my giant spreadsheet of all of the records and then we did some normalization on that we decided that we were going to start with three tables and albums table an artist table and a genre table and what we learned in this module is that to really actually create those tables we need data types for those things right right and then let's just talk through the album one if it has an album id as a primary key right then what would you think a data type would be for that that would probably be an int integer an integer because we know that integer goes up to two thirty two thousand seven sixty seven thirty two thousand two thousand issues so for my database that has a few let's say hundred albums in it that's probably a limit that's appropriate so i'd make a choice for the data type for my primary key because i don't think i'll ever go over thirty two thousand albums in that yeah might actually i think i just i think that's a tiny emphasis they're tiny at their small and yeah so the integers are huge and the point is you'll think about the data types that you have and you'll you'll think about the data that you're going to put into that table and you'll make an appropriate data type decision right based on the data that's going to be in that table so album name we'd need to decide what we think the longest possible name of an album is and then make a data type decision based on the data so this is more of that data modeling stuff we're not going to give you lots of guidance about how to do that but these are the kinds of questions that you'll have to think about when you're actually creating you'll have to first understand your data and then understand that there are data types to make choices about that when you create the table you'll be able to make a smart choice about what data type to use for those things i just wanted to kind of reiterate that that's how we hooked it together from the first couple of modules to now what we've just learned about we create the database we know it's gonna have a bunch of tables the tables are gonna have these data types these fields with different data types that are appropriate for the data that's in them right exactly and then the next model we're going to look at some of the awesome commands that we had to be able to go and actually grab this data and slice and dice the information so all right so this wraps up and creating the database and the objects we're going to look forward to taking a peek at the how we manage that data down let's let's do it okay thanks welcome back to database fundamentals this is module four module four no yeah it's gone by really quickly it is yeah but it's been fun we uh we started out with uh kind of the very conceptual module one uh where we looked at some of the core concepts to creating a database or to databases and why we needed them we started with our big spreadsheet in module two we talked about relational concepts like normalization and referential integrity um and we just got out of the module three where we learned how to actually create databases and create tables and that that was um something that we called ddl data definition language yeah exactly and now here we are in module four which is it even has dml in the title which is data manipulation language correct is that right manipulation language i was getting confused about these acronyms dml and ddl and dcl which we're going to look at later but in in this module we're going to actually take a look at finally using data we're going to take a look at we're going to start i think with how you actually use the select statement which kind of the bread and butter of querying a database and then we're going to talk about some of the um other dml statements for doing inserts we'll actually look at how we got the data into the database in the first place and how to do updates and deletes as well but uh take it away i can't wait this is this is probably the big module for the course and i know that you've got all sorts of cool demos for us yeah the cool thing about this is we talked about the the importance of data types and being like we've been saying the excel spreadsheet was nice if you had just a few rows you weren't worried about slicing and dicing your data but if you get into these tables inside the database and with these relationships defined this is where you can really the magic happens by being able to use the select statement it's that simple it's really uh using a dml select statement so we're going to talk about that here uh specifically we're going to start talking about the select and then we're going to talk insert update delete real briefly there let's skipped a few slides at you so select statement here we go here uh with this it's it's used to retrieve rows and columns from a table i showed you the most basic select statement there is select asterisk which means all columns from and whatever table name that's as easy as it gets so with that said though you may not want all columns you may want specific columns that what we're going to do is we're going to introduce different ways for us to really start slicing and dicing the the information because right now if i do a select asterisk from a table name to me i'm doing what i call a table dump i'm just dumping every row and every column and so i might as well have a spreadsheet it's not quite that bad but um but what this does now i've got choices i can choose what columns i want to display and i can choose what rows i want to display columns are chosen by instead of doing the select asterisk you specify the column names instead of doing not supplying a where clause you supply a where clause and that introduces what term what what the criteria is on those rows that you that has to be met for those rows so now you're narrowing your results down both uh in width and in in in height because now we're getting only certain columns and only certain rows because of what we supplied this is an example of a it's still a fairly simple select statement but now we've taken it down and we've supplied three column names instead of the asterisks and instead of dumping everything out from the employee table we said where business entity id is less than or equal 50. so we're actually supplying a where clause that's saying i want to see everything from one to fifty without where business entity is you know one to fifty and this is what i wanna see i wanna see job title and i wanna see gender so that's what we're doing we're specifying the actual columns and we're specifying the rows the rows are driven by the where clause the columns are specified right after the select clause as to what columns that you want to see so this is taken from a simple select statement to now i'm getting a little bit more picky about what i want returned to me i can get pickier i can add multiple uh multiple criteria to the the where clause so notice here now i'm still doing a select on these three options business ntid job title and vacation hours now from the table still have that now my where clause where job title equal designer engineer and oh by the way gender has to equal f and oh by the way their higher date has to be greater than or equal to january 1st of 2000. so now i'm really narrowing down what's returned to me in my result set i'm specifying three bits of criteria they all have to this each record that's going to be returned has to me they have to be a design engineer they have to be gender female and they have to be have been hired later than january 1st 2000 so i'm getting very specific can you do that in excel i can't i was actually just thinking i can't i don't i don't i'm no excel group but i'm pretty sure you can't even do this and what's awesome is that right away here in module 4 you've solved the thing that i was wondering about all the way back with my spreadsheet which is how am i going to figure out how many jazz albums i have yeah right or which are the jazz albums of my collection you just showed me what this criteria this where clause right on um the select statement with all the referential integrity that we've got i just need to reference where genre equals jazz and i'm going to get just those jazz records out of my or your disco i know you keep talking guys but i still think you get us it's a very simple you get a slew of different things i don't think you did disco no there was there's a lot there's a good time for that um so the next one instead of using an ann claus we're using an or clause here yeah select business identity entity job title vacation hours from the same table where vacation hours is greater than 80 or so it could be anybody has more than 80 hours available to them or their business entity id is less than 50. so pretty much i'm going to get everybody 1 through 50 and then anybody else that has more vacation hours than 80 hours are also going to be displayed so now i've increased the number of rows compared in comparison to the other one so my n clauses it has to meet all three criteria my or says it's going to meet either of these criteria and i'll get that returned to me okay by the way i'm going to demo all these for you and we're just going to walk through them so you understand what they're all about but we'll see these all in action in just a little bit the between clause instead of specifying the ends or if i want to specify between a certain date range or between a certain this case here vacation hours very very similar syntax select the column names from the what from the employee table where vacation hours is between 75 and 100. now i could have done this a different i could have said where vacation hours is greater than or equal to 75 and vacation hours is less than and equal to 100 that would have worked but this is a between this makes a little bit simpler so now i'm going to say if the vacation hours are between 75 and 100 i want to see those rows so it's just another way for us to massage or slice and dice the data as it's being displayed now the bad thing is right right now is we're getting data dumped out to us but we want we might want some sort of order to it what we're going to do now is sort the result set using the order by clause so what we're going to do is we're going to take the same content with the same command we had in a previous slide select business id job title and vacation hours from employees where vacation hours between 75 and 100 and order the vacation hours order the content that's displayed to me by vacation hours so the default is ascending so anybody who has 75 hours is going to show up first on the list 76 77 all the way down to 100. so i'm going to actually specify how i want that content returned to me you can reverse that i use the same exact command on the bottom of the slide here and on the ver on the reverse it all i did is i added this d-e-s-c short for descending so now instead of seeing 75 at the top of the display down to 100 i'm going to flip that and see 100 down to 75. so it gives me an opportunity to sort and decide what order i want to sort them and it doesn't have to be vacation hours it could be by date it could be by alpha alphabetically we can choose what column that we want to order that by so now we're getting some more we're getting some order no pun intended to the how the content is displayed to me it's not just dumping the data out because it met the criteria we're telling it how we want to see that data displayed to us using this order by clause so it gives us a little bit more control of how that content is displayed so that's saying how we want to grab content what if there's content we don't want to see we've got the not clause so what this is i'm going to grab this three items business entity id job title and gender for columns from the employee table where not gender equal m kind of weird we just say it we're not gender but where not gender equal m so when i run that i should only see gender equal f because i don't want to see i'm telling it i don't want to see gender equal m's i just want to see items where now i could have expanded on this i could have said where not gender equal m and title equal design engineer or something like that so again i can expand on some of these commands more so than what we're showing here i'm just showing you different flavors and the power of relational day tables in a database that we didn't have in our spreadsheet because now we can we have control over what data we want to see how we want to see it what data we don't want to see and i really want to be more restrictive on i want my disco d you know cds i would that's got celebrate on it or something like that from a certain from a certain uh artist do you have that one i'm just curious it was just just curious you're bound and determined to get me to disclose which disco which disciplines are in my collection yeah we'll find out we've got we've got we got a little time okay the union clause what if i have content that's in two different tables yeah we can do that so right now what we've been looking at we've been looking at all right you're telling me i can grab content from a table what about if i have content from two tables well this is what i can do here as long as the columns are matched so as it sets up here it allows you to combine the rows return from multiple select statements into a single result set so we're doing select business entity id job title and hire date we're selecting that from the employee table where we're still supplying a job type where a where clause we're a job title equal design engineer now we want to union that we want to i want to use the word merge because there's actual statement merge we're going to add we're going to take that content and we're going to have content from the second select statement again the columns have to match business entity id job title higher date from the employee table where higher date between 2005 january 1st and uh 2005 and and december 31st of uh 31 january december 31st 2005. these dates are weird for me i'm not used to having the year first so now we're going to union we're going to take two select statements we're going to populate the content into one result set but there's actually criteria from two different statements and we're going to return the same column so we'll see how we can take i have a statement that returns columns based on two separate pretty much two separate where clauses and two separate select statements this could be a different table if i could go i could go out and do select business entity id job title hire date from x humanresources.com if i wanted to uh where higher date or fire date was you know a certain date you can do that as well as long as the columns match up you can retrieve that content from different different tables here so this is the union clause and again we'll we'll see how how that operates for us here the accept and the intersect clause so the accept clause returns distinct values distinct means unique in the sense that i want i don't want any repetitive data so now i'm saying okay so if i did a search i wanted to sage i want to do a search of my of my cds where you know gender ids would equal disco and then i've got the same i've got the same artist i may i want i may not want to see the same artist i want to see all my disco albums and i think that's probably a hundred ish that you have it's at least it's at least a hundred it's at least 100 so um so anyway so if but i don't want to see the same artist each time so i want distinct i want unique artists that would do the genre of disco so what i do is select product id from the pro a product table accept and select product id from production.work order so it's going to return both of them for me all right but anything that's matched it's not going to return that so it's not going to return anything it's going that's that's a match it's going to just do uh it's going to do the distinct items the intersect clause down here returns any distinct values returned by both the query on the left and on the right so what this one's going to do the left being the first query by the way they determine as as left and right it's the first query it's left on the intersect statement let's put it that way so this is going to say returned so the first one up top let me go back there that look from the left query that are not found in the right query so they accept is return distinct values from the the products table that are not found in the work order table this one down here says returned by both the individual information distinct values again not everything but distinct by both information that's distinct in the left and the right sides of the intersect operand so anything that's distinct in product and work order will show up in that second one all right and again i'll show you a result set of that as well but it's just ways for me to uh to be able to slice and dice that content that we just were impossible to do with a thousand or so rows in a uh in a spreadsheet but like being about being able to create relational tables inside a database we're able to manage that data much easier the join clause is uh it's a little bit more advanced we're just going to introduce the concept of a join here there's a few different flavors of joints but it allows me to use uh content from two different tables so an inner join uses a comparison operator to match rows from two tables based on a value that exists in both tables so product id we'll go to product id i could go to do a join a product id from one table and do a search or select on a product id from another table and with an inner join it's going to look for matches on that product id and give me that content okay so it's a match outer joins if it's a left outer join versus a right versus a full but if it's a left outer join it's going to include rows from one or both tables even if they don't have matching values so if it's a left outer to left join outer join it's going to give me everything in that first table all right we'll say it's a product from the products table and then any match from that second table so that's what that's what we'll do for that so if it was a right outer join it'd be everything from the right table and only the matches from the left table cross joins is also i think this is also uh you know the cross join is going to return all rows from the left table and all rows from the right table so it's pretty much a table dump for both of them unless you supply a where clause if you supply a where clause then it'll narrow down what's being returned otherwise give me everything from the left hand will give me everything from the right table it's kind of a good way to generate a bunch of table content into a temporary table and you can use that for testing content or testing other statements that you want because you want to generate 30 40 000 rows of content so joins allow you to combine related data from multiple tables and we're going to kind of leave it at that it's more of an advanced topic but just want to let you know because we're creating multiple tables doesn't mean i can't grab data from multiple tables i can certainly do that and do that using one of these different types of joins that we can look at and you can maybe experiment with this seems like it's a great opportunity to plug another microsoft virtual academy session that you're doing we're actually recording it tomorrow um that's a whole session about the t-sql querying language that you're doing with to be a ternstrom uh that's going to go really deep into these topics so keep your eye out for more content on on microsoft virtual academy uh if you want to go deep on the query yeah so some of these that we're just doing the high level approach on the fundamentals this is querying sql server query microsoft sql server 2012 that's going to be recorded it'll be out shortly on the virtual academy and you'll be able to review that over tobias tonight so great point thanks for bringing that up and reminding me because you kind of blew my mind with joins are complicated on their own and then you went into inner joins and outer joins and it gets super complicated for the purpose of my cd library it's way out of scope yeah yeah but i can see where i'm going to want to go get more information about that when i start like hooking up to the xbox live music service figuring out which music i have that's also on the streaming service things like that once i get really into my application that i'm going to build yeah okay i'm waiting for the i'm going to monday i'm going to be your tester on that yeah you'll send me a sql run this this is an example of a winner called aggregates and so what we have done here is i don't want all the tables i need some average information i want i need some some uh some aggregate information is aggregated so what this does is provide you summary of data so this example does a select count distinct sales order as unique orders so what this as does is just gives it a different column header just want to throw that id in there for you so select count so it's just going to do a count of all distinct sales order id so if i got a sales order id of one two three it's only going to count and i got 10 line items in that it's only going to count at once that's that unique sales order id then by the way for that sales order i want the average unit price not sorry not for that sales order in addition to that sales order the sales orders i want an average unit price i want to know the minimum order quantity that was ordered in any of the items in that sales order detail table and i want to know the max line total so what's the maximum amount one item or one line item uh was what's charged what was the maximum amount what the sale on one line item so this is taking content instead of dumping all the detail out it's actually providing you summarize a summarization of this content and showing you how this content would be displayed and what you're in or what you're at max and what your average was and what the total number of records are for a particular sales order id so again i will demonstrate that for you so you have an idea so what that looks like those are really really powerful um for instance pete may want to go in and say well how many disco cds do i have or you know albums i'm trying to call them albums he could go in he could do a count where he could do select count distinct uh gen from the genre would that be where it would i would be and where type where name equal uh disco and so it would give me a total number of items no i actually wouldn't do it that table we'd have to do it and actually output an album table select the distinct count yeah and then we give them all the disc golf so then we truly know how many different albums he has definitely because he doesn't want to tell us but we'll see that one in action just a little bit here how about right now in fact i'm pretty sure for christmas you're going to get me the complete works i've done a summer anyway and then the whole well i wasn't sure what i was going to do now but i don't know just go i might already have that well let me know so i don't buy it again for you because i'd hate to do that okay let's go back into management studio here ssms i'm over on my desktop here i'm going to open up another query you know all the students really wish that you had my cd library data for all of these examples we're going to go find out you know like who did celebrate sadly i think we're going to use adventure works now aren't we is that where they no we're still yeah i'm sorry we're staying with adventure we're going to stick with adventure work so can you just give me a quick overview one more time uh for the people who are just joining us yes adventure works tell me about the data cause i think the samples we've been using make a lot of sense because it's pretty easy to wrap your head around a music library or around a grocery store but what's adventure works and what kind of data is in there before we get too deep into these samples that's great great so adventure works is a sample database that's provided that you can download install if you want to test this in your sql server world and it provides us sample data and as you can see here i'm just going to open up the tables it's got wow human resources got employee department so they've already already done the normalization they've already done all the relationships for you it's got an entire area two four six just in human resources a dozen or so on person notice they have a person address that's separate than address type they have a contact type they have a password they have a person and then a person phones then they have the person phone type i mean they've really normalized this data to uh to the nth degree and then the production all this information about production counter or product category product photo product uh product product photo not quite sure what that would be product reviews scrap reasons reasons they got rid of content or our product and then we go and we've got purchasing information all right we got product vendor vendor order detail vendor order header i mean it's just all been totally normalized and there's as you can see but dozens and dozens of tables in here it's interesting because adventure works is a huge enterprise database and the hypothetical company adventure works sells sort of adventure products but they have data in their database that manage the the business of running adventure work so all the people and hr stuff yeah but it's also got the product catalog and things like purchasing sales all of the sales and it's all in that one database which is how they chose to do the the structure and we can we can say that adventure works is a little bit contrived so that it makes a really concise sample that people can use right but there's lots and lots of data in there split up into a variety of different sort of business data that we that we're going to use in in the sample and so if we just had someone chat into us and say that the longest album title ever is 89 words long words i thought it would be 89 words we would need a full character i mean i would know we have to figure out the character the average length of the words i don't think we have enough information really yeah not enough to make a decision on how we create that field but it's information like that it'd be the 89 words i would have never guessed that so if the adventure works product catalog had album titles in it then the product name field would need a data type to accommodate an 89 word name yes exactly but stuff like that you have to think about while you're actually designing your database because i would have never guessed that i'd have probably gone 50 60 characters a hundred max i would have got 100 max of that just out of the off the cuff and now i'm thinking you know that's not anywhere near enough so well thanks for chatting that in that was great information so now if you could find out who did celebrates because pete won't tell us that would be awesome cool in the gang wow that was quick that one came in somebody's got that in your collection okay all right adventure works i'll do four we're back in adventure works thanks for reminding us to kind of just show what it looks like sure okay someone joined us here a little bit later on we talked about this table down remember this is pretty straightforward we do a select asterisk and again this is all scripted so i could just cut and not cut paste i can highlight uh actually it's notice up here i'm in the wrong interface or database so i'm going to grab use adventure works remember i try to get in the habit of just using or supplying the use adventure works only so i know that i'm always in the right database you saw a couple times doing a demo it didn't work like oh wrong database so if you can include that on most of your statements especially if you're bebop and between the two different a couple different databases it'll be helpful for you so let's do our basic statement we're going to go ahead and execute this baby and you're going to see we got pretty much a table dot we got business entity national id and i'll scroll over here trying to get you dizzy i'll scroll slower and this is pretty much everything there notice there's 290 rows you can see way down the bottom there's 290 rows that were retrieved from that table called employee all right and you can see you saw all the columns as i scrolled from the left to the right i'm going to scroll back so we just pretty much did a data dump now again you've seen all these slides so all these slides are now we're going to show you how these uh these slides that contain the uh the uh the text on the statements on there now we're going to show you how they work so then we went from the table dump to say now let's decide what row what columns we want and how we want we're going to throw a where clause in there so we're going to execute that one now so now we're going to see business entity idea equal 50 or less and three columns instead of all of these columns so we'll execute this one here's our business entity id here's our job title and here's our gender now if we scroll down we should see call a business entity id 1 through 50 and i just hit bottom and you'll see over here whoops 50 rows and we just got important information into us 360 characters for that 89 word title so our we would have to have a var char have to be of our char because that's going to be huge compared to everything else of 360 characters that's i mean who'd have thunk i'd have been 100 i had no idea i need it and celebrate celebrate that all right so that's that's cool now we've narrowed down the rows and the columns let's go in here with our multiple where clauses get this back down a little bit here this is where we had where they were that the same thread we had two columns here the third one of vacation hours from the employee table now uh still job title equal design engineer and gender equal f and higher date is greater than january 1st 2000. so let's see what kind of content this will return to us and like i said it's okay if you highlight the the comment it's just going to ignore that anyway and you'll see we have two people who meets the criteria that are design engineers have vacation hours of what do you say oh we didn't care about that a business entity is five um and that was supposed to be i could have changed this one a little bit well the where the where clause is showing you what you got back we just didn't include the columns and the where clause and that's pretty interesting as queries go yeah because uh because we can assume since we have and gender equals f that everybody there has f so we didn't really need to return as a row yeah query or as a column let's do what's on the fly here let's change this to higher date and let's change it to sneaky gender you want to see it anyway just to prove that your query works yes gender okay business entity id no we want job title we don't care about this let's get this guy out of here and we'll get the title all right let's try and run this baby again so job title is design engineer gender is female and hired on 2 6 and 2 18 2 16 of 2002 right and we would have we would have kept the employee id in there just so that we could figure out who they were so that we could go and use that information in our application exactly there we go so we can find out who they are because that's the key in the employee table that is now how do i find out their name this is where it would be helpful to for me to now remember this is going to be a primary key called business entity id somewhere out there and we can probably go see it real quickly tables under human resources we're coming from employee we'll probably maybe person person person why don't we go look at the fields that are in the employee table person person person.person so i would map that business entity id but hang on a minute you're jumping ahead though all we're doing is we're looking in the employee table how do we know it's not there oh i thought i just showed you that if we open that right let's just reiterate here is the employee table so i want to just stress that the foreign key that you're implying okay right so here's this so there is there is nothing in here there's job title but the name of the person is not there right so how do we get from there to person so we get there from there to the person we get into one of those joins and then that would map to and we won't get into that that would use that business entity id business entity id that would map to this guy over here for this 5 and 15. got it and we could grab the first name and last name from there as well got it so it's both the primary key and has a foreign key relationship to the other table yeah got it yeah cool stuff all right so we got creative with that one on the fly is good most the time we'll do this one now this is that multiple where parameters with the or so this is the and so this is where we want vacation hours is greater than 80 or business identity id is less than 50. so now we're just kind of deciding i forgot one of the this thing so it's a there we go for our little dashes so now we've got these folks here business entity id job title what we're looking for vacation hours greater than 80. or business entity id is less than 50. so notice this one here doesn't meet the criteria of vacation hours is greater than 80 but the business end to the idea is less than 50. so we still get that return to us now i thought did i do it the other way yeah and here's a between oh yeah that's later uh so here's the between clause that we talked about now we want vacation hours between 75 and 100. so this met two different criteria we got rose returned to us this is going to say we're going to get the same content we're going to get content returned to us um where vacation hours is between 75 and 100. so these are the people that need to go on vacation before they lose i don't know why i'm not listed in hours i should have a vacation so notice we have 99 80 82 79 98 but there's no order i mean the only thing by default it kind of dumped it out by business entity id but that's just you can't count on that i mean it may do it pretty consistently but if you really want it to be displayed in a certain order you're going to want to use the order by option so i'm going to use that same command i just used up here but i'm going to order it by vacation hours instead and by default it's going to be ascending so around that see now instead of storing up my business entity id it's sorting it by 75 vacation hours 78 79 86 all the way down up to 99 vacation hours so now it's i'm controlling how the contents being sorted remember i said we could go in reverse order or descending i'm taking the same exact command i'm adding the desc on the end of it for descending and i'll execute it now the 99 start up at the top and then go down in the 70s in the bottom if i scroll down all right so we can control that and it tells you how many rows by the way if you're not quite sure there's there's always down here it tells you the number of rows if you quickly jump over here it tells you how many rows so if you're curious in some of these commands like how many rows did that affect jump over the messages or if you happen to to peek at it it happens to be down on the right-hand side it'll show you how many rows are affected and there's some commands coming up i think we're going to hit thousands that might work so it'd be good to know all right using the not clause we showed you this is where i want to see anybody where their gender is not male from the employee table we'll execute that so they have 84 rows for the employee table and then we're going to go in and we're going to do a union this is where we can get content from two different tables or to two different uh options so we're going to combine data from multiple tables it's actually the same table but we're going to do this we're going to business entity job title and hired eight same as down here two different select statements with a union in the middle from the employee table we're job title design engineer and hire date between 2005 january 1st 2005 and january 31st 2005. now what's a way we could have done that differently did i really need to use a union i could have with aware job title equal design engineer i'm trying to do a union or higher date between 2005 one you know one one two thousand five 12 31.5 i could have done that in a single statement uh unions are probably a little bit more powerful if you go two different tables it's difficult to find two tables that have repetitive columns in them though you might have a choice you might have like an employee happens to be a customer so they're in there as a job as a first name last name maybe city um but it's sometimes difficult if you've done all the normalization to find columns that are uh that don't match the actual data the values in there but to match the the columns required for in this case here business entity id job title higher date let's use first name last name and city i could probably find that in a couple different tables and then just join those that way as well here's the returning distinct values from the left query not found in the right query and we'll execute this one so product id so it search product table and it starts to work order a table and it found 266 rows let me click this guy here that had unique values in them between the two tables all right i could have added more information as far as you know what table came from and other information about the product uh maybe description but um just to show you that we can grab this is going to be the intersect versus the accept this should be bigger so we're going to highlight that we're doing the same thing but we swapped out except for intersect and we got no we got 238 rows actually so three you see distinct values returned by both queries what was this one back oh these are ones not found so these are really just turned in from one query and these are from both queries i think there have been more in that one what was this one let me run this one again this is an example because i'm not familiar with the work order data that when you're looking at the results the only really way the only real way to know if it's working is if you really have a fundamental understanding of the data that's in those tables so that you can logically deduce if you're getting results that make sense yeah yeah that data modeling part you were talking about that's right understanding the data types why they're there what to expect from those data types so exactly this is this is the adventure works unless you've studied it and if you're writing the query and you don't know then you have to go find the person who owns the data that's in there right to say this is what i think i got exactly is this what you expected or not what should i expect from you right yeah perfect here's my aggregate function sample we've got the count of distinct sales orders unit price order quantity average unit price sorry minute minimum order quantity and maximum line total so we're going to go ahead and run that it's all aggregate functions so we're going to get one line unique orders 31 465. average unit price is 465 and some odd minimum order quantity is one i would hope so otherwise it wouldn't be an order and the maximum line total and one of all the items in the sales order detail is 27 893. so hang on let's dig into this for one second you did a select count of distinct sale orders uh and you added a bunch of aggregate functions right so basically in one t sql statement that is one two three five lines of code you summarize all of the sales in my uh in my system yeah try that in excel that's pretty amazing it's really cool i mean i know right now that the average price what we have we have this these are unique orders we just do 31 000 orders that's that's unique orders let's take this baby out let's just grab every order so let's take that and run that now now it's 121 000 orders and the average unit price of each order of the orders all of the all of that 121 i know 375 they're selling but i like it um and still the max that's not going to change this probably changed because we have more column more rows in there yep but so again just a it's just removing distinct dropped it was at 38 000 rows and something run up to 120 000 rows just by removing that one claw so that distinct cost is really really important um i just want to show okay so what we're going to do now uh this is inserting data we're going to do dallas go back to powerpoint that's what we're going to do so don't get too demo happy again oh i keep doing that i'll let this guy come down here all right let's go back in so those are select statements so some of the stuff we just showed you would be impossible to do with with excel and it's just all we had to do is that it's the upfront work it's the data modeling it's setting up the relationships once you have that done there's just so much easier for you to slice and dice the data so that's the select statement that's again that's just a piece of the select statement that we're that we're looking at here so um and there's more again that querying sql server microsoft sql server 2012 class we're going to dig much deeper in those joins and a bunch of other items tomorrow or when we record that all right we need to add data so in order to add data we can use the insert statement so we do an insert into and we supply the table name all right and the schema the values all right so i'm going to add into now notice i'm not supplying any columns i'm going to just say if i know this if i understand the columns that are in there i don't need to put the column names in there so i'm going to say insert into production.unit measure value the first field is going to be ft the second field is going to be feet and the third field is going to be a date okay now uh what this is going to do and again i'll go back out and execute this it's going to add a row into the unit measure now if i want to add three rows i could go into something similar insert into production.unit measure here's the first one i thought i'd change oh i changed it in the script and here's another one so i'm adding square feet i'm adding square tried to change it can't do that i'm going to add yards and i'm going to add cubic yards my my script is slightly different so when i go out and show you that this is going to be slightly different i changed it because i wanted to show you something because right now i'm hard coding a date that's really kind of unrealistic what i'm going to show you in the script is why don't i just throw in today's date so i'm going to change i change this a little bit so it's going to actually go out retrieve the system date and populate that instead of this hard coded date so that's how i can add content insert into either individual row or multiple rows using insert into if i have a flat file just a file a txt file for instance and i want to add content from that i can use what's called a bulk insert and there's a specified format specific format you need to use but i'm just thinking what if you have an excel spreadsheet you dump it out to a flat file with common delimited can i get that into sql server you can and the easiest ways to do that is to help sql server understand the format of how that data is stored in the next in the text file using a format file and then using a bulk insert command it'll take all the rows in that in that file flat file and move them into a table that's all i want to say about that but it's another way to add a bunch of data or if you've exported data from somewhere else you need to get it into sql server that's going to be your choice for doing so it'd be a lot faster again unless you get paid by the hour where you want to do individual insert intos go ahead and do it that way but a bulk insert would be much more efficient all right the update statement we're going to update salesperson again i changed this query slightly to stay with our theme uh what we're working with but update you specify the table set is the keyword here set bonus equals 6000 commission percentage equal 10 and sales quality equal no so it would what this would do it would set those values on those three fields where sales dot salesperson dot business entity equal 289 that's a huge problem if you don't put a where clause on an update statement if that where clause wasn't there what would happen just now everybody would have gotten a bonus everybody would have gotten a six thousand dollar bonus a commission rate of ten percent and a sales quota would have been set to null that means they don't really have to work that to me was what i call an rpe everybody heard of an rpe that's and i teach the admin class for sharepoint and sql server and rp is a resume producing event we want to avoid resume producing events i think everybody getting a six thousand dollar bonus uh executing this command without a where clause could be an rpe so we want to be careful with those all right delete statement delete from the table we're production.unitmeasure.name equal feet again if i did a delete from without a where clause what did i just do deleted everything in the table wrong i toasted everything in the table because i want to use the toast command but yes you're right i deleted everything on the table so when using updates and deletes where clause are where clauses word i don't know they're essential because you can really mess up the data if you don't do that if you don't supply a where clause a delete statement without a where clause will cause all rows to be deleted that's my point i'm making there and i got slightly different uh commands in the in the query is there anything bonding on this it's okay okay so let's go take a look at this now let's go back into these statements here and we're going to pick up where we left off first off i want you to see what we have for information in the unit measure so i'm just going to query that kind of do a table dump and what i want you to notice is that we don't have anything in there for f2 to get an f3 but no no no square foot square feet cubic foot no square miles all right so nothing in there for miles for m3 so i just want to show you that now what we're going to do is we're going to insert a row of information and i'm going to insert this one and now notice what i also do differently instead of hard coding the date i'm going to insert into this unit measure ft and feet and then get date it's going to populate today's date in there instead of a hard-coded date so i'm going to execute that one row is affected i'll go back and query that table in just a moment remember i could do multiple statements so here's that one ft2 so remember ft now should be in there ft2 m2 and y2 are being added now notice i intentionally called this square foot in case you're thinking like dude don't you know how to talk that's that's on purpose i'm going to execute that three rows affected let's go back and query that all right we're going to f5 this now we're going to look for ft and our ft2 just scroll down here here's my ft i just added here's my ft2 with a square foot i added on purpose that was wrong why did i do that because i want to show you the update statement we're going to change we're going to do update product dot measure or product production.unit measure we're going to set name to square feet name is right here instead of foot where production unit measure dot unit measure code equal ft2 so this duder right here should end up with square feet so we're going to execute this guy all right one row affected and then we're gonna go back and execute this command to find out what's out there see if we have a square feet instead of square foot we'll go down ft two and notice now it updated just that one column again without this where clause every item in here would have been square feet so we got to be careful about that we can delete a row delete from again we need that where clause we're going to delete the ft you'll see it's right there simple statement delete from the table and then you specify the criteria execute that we'll re re-uh query see if ft is gone and you'll see that ft should be gone here and you'll also see so i didn't point that out to you the first time ft is gone you'll see now that square feet has today's date instead of that 2008 hard coded date it actually won that grou out and grabbed the system date and time and populated that for us all right so that's that's the dml statements other than the the select statement that's the other items inserting just a just a brief introduction to inserting updating and deleting content inside of sql server we get just a couple more items that we're going to introduce we're not going to go in depth on these but these are indexes and triggers i briefly mentioned the triggers already indexes we talked about we have either clustered or non-clustered index the big thing about those is you can only have one clustered index indexes are very powerful notice the first line allow you to speed up the retrieval of data stored within a table review okay that's the primary reason we add an index is to speed up the retrieval there's a bit of overhead involved with inserting content if you have indexes turned on especially a clustering index because it physically sorts if i have in a clustered index on the last name every time i add another last name in there it's going to shuffle that data around to make sure it's alphabetized or if it's an id it fits in that id in that row so they're used to speed up your retrieval of content we'll just leave it at that we have clustered index we have non-clustered index you can only have one clustering index because the data can only be stored one way physically in one order and we have any number of several several non-clustered indexes and remember i talked about the unique index if you're looking for uniqueness on columns you want to enforce uniqueness primary keys you can only have one per table your unique index would provide you that that functionality creating a trigger we have triggers are used to enforce a business rule and then we'll just wrap this up yeah i got one more quick little demo on the trigger so you know what that does you don't think i'm just making up that word that's not the name of your horse no roy wouldn't let me was it roy roy rogers was that trigger whose horse was trigger roy rose look at how old i am huh okay so we got this off this content here all this trigger is doing is called insert success this isn't doing much except for i just wanted to show you what a trigger does this is a dml trigger this is saying whenever anything gets inserted into the unit measure it's called raise an error even though it's not an error that's just the term they use it's called raise and error and it's just saying i just want to get a quick little message saying this was added remember this could be something like hey when you insert an item here update an item over there talk about it for referential integrity or what if you change the last name and employee table because someone gets married well if we change the last name there well we need to employ also update it on the a payroll table so i can have a trigger that fires off when you do an update on this table also update this table in this table so that's how they'd be helpful in referential integrity we're going to execute this i didn't get my crate trigger on product after insert oh that creates it all right and we're going to test it and i don't have a command to test it so just let's see we're going to instance we want to do an update so where's our update statement up here this guy right here we'll change this guy to square feet just so we know we've changed it and we'll execute this guy here i still didn't get update production you're going to measure should have gotten just a baby little command oh i didn't insert okay we need to do an insert that was in an update let's do this so we're going to change this trigger instead of after insert i did this on purpose you know what we could do we're going to change this to update instead and then we're going to go up change this to square footage that's not how you spell that footage feet feet footage is cool but i don't think that's what you're square footage now we'll do one more update on this baby right here execute that name equal square footage where near f2 did i not grab that last quote maybe i didn't let me see about it probably didn't oh i still need to get my trigger though oh the trigger was already created on oh i okay so what went wrong there is i had a trigger created for insert that's already on the table all i did is try to change this i created another trigger for update which probably failed because there's already one out there should have gotten an error on that so i would all this would have done it down here instead of one row affected it would have said unit measure is successfully added it's not pretty but it's a way to let you know a better way for using triggers in the real world for it referential integrity i make an update on the last name column in the employee table it needs to be updated in three other tables that trader fires and it updates it in accounts payable and everywhere else that last name needs to be updated all right cool module on select statements and working with select statements and all the cool things for slicing and dicing the content we went through all that with you we talked about the different arguments the between the not we saw all of these in action except for join just want to introduce those to you and then we talked about dml commands insert bulk insert update delete we saw all these in action we looked a little bit that the cluster index and the non-clustering index nothing to be too concerned about in the unique index and we talked about the use of triggers now triggers can be used for enforcing referential integrity if i make a change in one table it can go out and update changes in another table in our next section we are going to talk about the the fundamentals of databases management more of a if you're a dba i'm going to talk about securing objects we're also going to talk about backing up restoring content because that's one of the big tasks that you'll perform as a database administrator well hello we are at module 5 of the database fundamentals course on microsoftvirtualacademy.com wow so if you've been with us for the first four modules already we started back with core concepts of what a database uh is and why they're important we went on from there to a second module that was about uh normalization normalization and referential integrity kind of a little bit of design fundamentals yeah then in module three we did how we actually create databases and the objects that live inside of them and then we just got out of module 4 where we learned all kinds of things about using data manipulation language to get data out and then we talked about insert update and delete to manipulate the data that's in a database which is a lot of information to cover in just these four modules but in module five we're going to just wrap the course with a little bit of information about how to do basic administration and this is very specific to sql server which we've been using as the example database management system for the course and you're going to help us understand kind of a little bit of security fundamentals and some data management um backing up and restore type functionality for keeping our database um administered nicely nicely yeah that's a good way to put it exactly what we're gonna do is one of the things we've talked about is how much data we can store inside of sql server how we want to design that data and how to add the data and retrieve the data but now we need to protect that data so we're going to look at how sql server implements a security infrastructure and the other piece we're going to look at in this module is making sure that that database uh the data is backed up regularly in the event that something happens you need to recover that data so we're going to give it the basics on doing backups and restore so securing the data and then backing up and restoring that data in the event that you need to do so so we're going to talk about that in this module we're going to begin with sql server security really that when you look at that there's three components of security we have what are called securables which is the server the database and any object that stored procedures and the tables and the views that we looked at we also use the term principles the principles are the individuals or the groups whether it be windows groups um or individuals logging into into sql server that are granted access to sql server and then the last piece are permissions what type of permissions are we going to give someone to access the content stored in a table or stored in a in a view so it's the secure the securables the principles and the permissions and this graphic here shows you a kind of a high level how that works you've got the principles over in the left hand side and you have windows level security because you can have two types of security you have windows authentication or you can have what's called mix mode which is a combination of windows authentication and sql server logons so the windows level we can actually use a windows group a domain login or a local login at the sql server level we have fixed or user defined server roles we'll introduce those we have a sql server login so first off you have to log into sql server before you can access the database before you can access objects in the database so it's kind of a three-tiered approach that we're using here at the database level we have fixed database roles not server roles anymore but database roles we also have user-defined database roles that we can create we're going to talk to those and then once you get inside of the database you've got these items tables views functions procedures different information that we want to protect so to me i always like to introduce this as a three-tiered approach give the individuals access to sql server give them access to the databases because they don't need to get access to every database give them access to the databases that require access uh to uh that contain objects they require access to and then give them access to the particular objects and i can give them different types of access i can give them just the ability to query information from a table or to be able to update information in a table as an example so the three tiers are accessing sql accessing the database and accessing the objects within those databases and we talked about the different terms available for us the permissions the securables and the principles for doing so here's our three-tier approach we need to log in it could be a sql server login there could be a pass-through login using windows authentication to get access to sql server we then need to give you a user account in the database that you need access to and lastly we need to apply permissions to the user account which oh by the way it's mapped to the login that's at the sql server level level so we have our sql server login we can map a database user to that login and every database that you need access to and then we decide within that database what permissions that you are going to get access or what objects you're going to get access to and what permissions you have with those uh with that with that object we have a couple different types of authentication we have windows authentication or pass-through authentication so it takes your credentials that you use when you're logged into the domain and it uses those to authenticate you and give you permissions to log into sql server we also have a group you could be in a windows security group and that security group if you're a member of that security group if that security group has been applied given granted permissions you're going to have access for that and so that's windows authentication we have what's called mix mode which is a combination of windows and sql logins so if you don't have a windows authentication a way to pat to a login we can create a login for you inside a sql server this is great for individuals like vendors that are external to the organization that don't get authenticated by an active directory environment or windows environment so we have to give them a sql login and what we can do with these logins is we use these logins to map them to either the server roles fixed or user defined or the database roles fixed or user defined and that's what's going to decide what permissions you get inside of the database that you get access to now by default sql server provides several different server fixed server roles to define a few of the key ones are sysadmin you get full party rights to do whatever you want you can uninstall sql server so you want to be really really careful about giving that one out the db creator allows you to manage databases so i can create a database and manage that entire database that's another fixed server role security admin allows you to manage who gets access to sql server who gets thought you know access to the logins can manage the logins so these three are just three of several that are out there are available to you out of the box sql server 2012 introduces user-defined server roles these roles may not meet your needs for what you want your users to do so you can actually create user-defined server roles that have special permissions depending on what you want the users to uh to be able to perform once they connect to sql server what you do is when you create the login you identify what roles you want the individuals to be in and that's going to determine what permissions they get so you have your fixed database roles i'm sorry your fixed server roles and then you also have your fixed database roles and within that that's where we start talking about so we've talked a little bit about connecting to sql server using a login from windows or sql but we also look at securing sql server databases and the objects within those databases and as we've i've just briefly mentioned once we get access to tier one which is the sql server level tier two is access to the database level or whatever principle you want them to be able to access i can map any of my logins whether it be a sql login or windows login to different databases depending what they need access to once i give them access to the database i can define what roles whether they be user defined or databased fixed database roles i can define which roles i want them to apply to and there's a special role that's out there people always see it so i want to bring it up if you happen to manage permissions there's a special role called public you can't delete it you can't modify it you can't move people in and out of it it's just there and so what happens if i happen to attach or try to connect to a database it automatically once i get permissions to that it automatically allows me it populates me into that public role so nothing nothing no no management it's actually used if you want to connect to a to a database and let's say you want to print something it kind of gives you some of the default permissions you've had you would have or want inside of a sql server the fixed database rules just a few of these to mention these are the fixed ones db owner which means within that database remember we had db creator that was at the fixed that's a server level they can do anything they want they have full party rights db owner has full party rights for that one database so if i'm db owner for sales database and pete's db owner for the adventure works database i can't do anything in his database he can't do anything in my database unless he gives me permission so your db owner for a specific database or specific databases that you have permissions to another popular role for the fixed database roles is a db db data reader so anyone who's in that role can read any data inside that database so it's kind of like granting read access to everything in the database and another one is db data writer for individuals who actually want to be able to perform those inserts and updates and deletes that we walked through in the previous module so those are available to us as a few of the fixed database roles similar to fixed server roles we can create user-defined database roles it's highly recommended for us to create fixed our user-defined server roles and user-defined database roles because it gives us a more granular a way to provide more granular permissions to our users we have more control over what our our users are getting for permission permissions if we use our user-defined roles versus the out-of-the-box define fixed database roles or server roles so either way however you decide to do it use these roles whether it be the use defined or fixed roles to provide permissions to the users inside of a sql server let's just jump out real briefly here and we're going to jump back into our base our sql management here and so remember that first here i said we need to get into security so we've been talking about databases primarily and here under security we have a thing called logins the logins is where you want to be able to map their users to give them access to sql server it's pretty straightforward of course there's transact sql statements for this but we're going to do the windows way because we don't get paid by 4 by the hour so we're going to go and create a new date a new login called contoso linda and then we're going to go ahead default database they're going to map to get my mouse back disappeared on me i don't know what screen it's on is it on yours over there oh here it is really i do have a mouse in here so there it is okay so i'm going to say the map soon when this person logs in linda logs in she maps to adventure works 2012. i can actually go up to the the fixed server roles these are the ones that i introduced to you already uh i'm going to trust that she can take error security so i'm going to make her part of security admin here all right now i could also go into the user mappings here at this point and here i'm going to say i'm going to give her access because although i said i want her default database to be adventure works i've not even given her access to do that yet so what i need to do is check this box and say for adventure works linda has permissions to access that's that second tier that we're that we're providing and being and i'm going to make her db owner it picked up the fact that she had security admin by default it added db owner for a fixed database role i could change that to just be a db data reader if i wanted to so when you create the login which is mapping your windows in this case here mapping your windows authentication login to sql server it provides you the capability to also at that point decide what databases she's going to get access to i can even go down to securables here and and here i can actually search inside databases and assign permissions at this point i'm going to do that a little bit separately i just wanted to show you at this point here what i can do is first off map her account her windows account to sql server gives her that first tier access secondly and assign what server role permissions she gets thirdly assign what database she gets access to so i can go ahead and do that and i'll just go ahead and click ok on that and i'm going to get that failed uh columnist name is missing entered for select and two statements let's see here so maybe i probably need to do this let me try that one no great failed for login so we're gonna already exist okay so let's see if that's true let me go over to adventureworks the user start over under security over here users oh she is already there all right so we're going to toast that right here we're going to delete it and okay all right now let's go back and try that again so i was yelling because i was trying to add her to to adventure works as a user and it was she was already in there as a user so let's go back to our login box cfo quit yelling at me try another okay still thinks it's there let me do a quick refresh refresh usually helps with this okay i don't want to create a diagram i promise let me refresh this let me go check security and users not to be found anymore all right let me try one more time here see if it'll add or this time here if not i'm going to switch to a different database if it yells this time all right so i'm going to do is i'm going to give her access to our class demo database instead being i still think she has access to that let me check everything i've done here we go back up to general contoso linda server roles security admin user mapping will give her access to class demo enrollment and we'll just go ahead and click ok and someone already exists all right so maybe it's up even further maybe let's go back to here refresh all right let me delete it from here all right let me try this one more time it seemed to see you're in security logins already so let's try this one more time here so let's check again contoso linda server rolls security admin class demo okay that time it worked for some reason it's still had her in login so let me just refresh okay she shows up there and then we go to the class demo database that we created we'll go into security there and you'll see under users that she has access to there now so using a quick pretty easy and straightforward interface where i can create an account windows account map it to sql server give her access to whatever server roles whatever she's going to do at the server level which is security permissions and then give her access to databases in whatever database and whatever permissions she'll have within those databases so we should just talk about what just happened sure in context of the rest of the course that we've been through when we started out really simply with the cd library example that might just be a single use database well we got up to something like adventure and in that case you wouldn't necessarily need elaborate administration if you were in access you might grant it to one other user but you might want to lock it down so that lots of people didn't have access to your library in the case of the adventure works it's an enterprise class database with all kinds of different tables in there to manage different parts of the application in that case you want to think about the security model and the administration of the security to provide sort of the least privilege for the users so that people can't go get to any of the data that they may not have the business access to see and that's kind of what's happening here with respect to the securities you're you're thinking about the business use of the data you're thinking about the users that are going to get into that data and you're trying to create a security model that that provides that sort of business rule for who can get into the data that's exactly right so yeah just to the the event that you want to protect that data you've got ways to protect that data like i said if we're just worried about our you know our albums may not worry be too worried about that but this is just more of a heads up as to what you're going to be or you would be concerned about if this was in a bigger environment that's exactly what we're doing there's a guest account that's created every database it's and what how this is used is if let's say i want to give everyone access to this database it's going to be read-only database i want to give everyone access to that i can do that by just making them just enabling the guest giving them access to sql server as being they have access to the database to sql server if i haven't specifically give them access to the database it will allow them to come in as a guest now by default guest doesn't have anything for permissions so you have to assign permissions to that guest account it's enabled by default you can disable it if you want to disable it using this command here revoke connect from guest and that will prevent people from being added into that so if you're in a real high secure environment we don't anybody seeing anything unless you give them specific permissions you want to be careful about that guest account because it is enabled by default and is available for use by default if someone happens to be create a login didn't deny their permissions to a database they do by default have permissions to whatever the guest account has in there so they can at least connect to it and be able to establish that connection now we manage the permissions um it's supposed to be two lines three lines here we manage permissions using three commands and these commands here are grant so i want to grant the select permission on a particular table to pete so i would do grant select on table xyz to pete and then i could do revoke so my revoke is going to reverse the last permission that i provided to an individual the reason i'm saying that while i jump down revoke and over deny is because i could do an explicit denied to pete on a particular table and how i get take that deny away is to revoke it so it's got always the revoke is always going to reverse the last permission that you applied either a grant or a deny now why would i use a deny a deny is available if i want to explicitly say that i don't want pete in looking at this content now pete can come in through an individual account he could log into sql server that way but pete's also a part of five windows groups as well if i do an explicit deny it doesn't matter if he's part of 500 windows groups i've explicitly said do not let this person into this this but this database or look at this object so your grant permissions you apply permissions using grant you revoke them i put them with like i said i call it neutral mode i haven't said you can touch it but i haven't said you can't touch it by using deny so you grant and then you revoke or you deny and then you provoke so you use these these terms or these statements to manage our permissions in sql server specifically at the object level now what i can apply for permissions i can grant select to for pete so you can come in and view content i could grant select and insert so you can view content and add content i could grant select and delete he can grant and delete content so i could be very granular as to what permissions the the individual or the group can have on a particular object inside inside a sql server and we're talking about the dri data referential integrity that's the reference when we use that for the foreign key back to the primary key we talked about earlier and the execute is for executing stored procedures that's a short term for that is exec instead so those are different types of permissions that we can apply to tables or stored procedures or views depending on the type of object we'll determine the type of permission that we apply to to those objects but they're managed using the the the uh the grant the deny and the revoke is that thing is that enough you think to i think that's great and there's this is another that we've said this several times during the course there's another opportunity for a lot more further study we have an entire course just on database administration that goes in depth on sort of the theory for uh database security as well as all of the implementation details for how you set it up yeah i think the thing we want to do is just make sure you're comfortable with the idea that you know we're not just because you created these databases and tables you do have to be aware of the fact we can lock these down we can secure them not worried about if it's just our artist collection but um if we are if we're in more of an enterprise level or bigger environment where we are concerned about security there are ways through using what i call a three-tiered approach to sequel to the database to the to the objects that we can control what what uh what do your users get access to so just a more of a high-level overview now one of the other things that we have to be concerned with is protecting our data because as one of the if you are a dba or aspiring to be a dba one of the things one of the probably the biggest things that you're responsible for is protecting the data and making sure it's available in the event where you have a power failure you lose on disk drive anything any possible uh possible situation where you might lose lose data so what we're going to just do is briefly introduce just so you have an idea because after we get questions saying okay brian i'm going to put all this data in there and i'm going to store it in there but what happens if i lose that data okay like what happens if i lose my excel spreadsheet well we have backup and restore capabilities inside of a sql server we're going to use the gui to keep it simple but we can write scripts we can script this all out so that way we can run these at night and the advantage of running writing scripts so the gui i'm about to show you again if you get paid by the hour you can set the alignment two o'clock you go up two o'clock in the morning you log in and you perform a backup using the graphical user interface and ssms but if you don't get paid by the hour you can write a script that performs the same task i'm about to perform and trigger it to kick off at a certain time so it's running while you're sleeping or just getting home depending what you're doing in your nightlife so let's go take a look at the database backup it's actually performed in the event that you need to restore that content somebody may accidentally delete some content or we may accidentally a hard drive may crash and the content that we were i was on that hard drive gets lost we need to be able to recover that content so what we can do is use a database backup that will allow us to restore that content now we can only restore it depending on your and we won't get close to being able to explain all this but we can only restore the content as good as the last backup so if you do backups once a week and it's on sunday night and you do have a system crash or you lose a hard drive on friday you've lost a week's worth of data so there's a whole strategy on place on what how many backups and how often and the different types of backups that you need to be aware of and there's really just three key backups that can come up with a decent strategy for you those are full backup where you back up everything regardless of its change we could do a full backup every night if we wanted to if i got 30 gigs of content and if i do a full backup every night and it takes three hours when only 10 of that content changed maybe i don't want to do a full backup maybe i want to do a differential backup differential backup i also call it a cumulative backup it backs up the content that on that change so again we did a full backup on sunday it backs up all the changes from monday i do a differential on tuesday it backs up the changes from monday and from tuesday i do a different show on wednesday it's cumulative that's why i'm saying that monday tuesday wednesday they're all backed up the reason that we like doing differential backups is because when we get ready to restore content we want to minimize the downtime if i have one full database backup that i have to restore and one differential backup that i have to restore it's going to shorten the time it takes me to recover and that's going to be a good thing so differential backup is a cumulative backup that keeps track of all the changes since the last time that we did a full backup an incremental backup is also a transaction log those are used anonymously so an incremental slash transaction log backup let's go back to our scenario sunday night we did a full backup monday we did a transaction log backup not a differential tuesday we did transaction log backup wednesday we did a transaction log back up now if i lose content on thursday what do i have to do i have to restore the full backup from sunday the transaction log from monday then the transaction log from tuesday then the transaction log from wednesday could take me a lot longer to perform the three stores of those transaction logs so differentials are helpful some people do full backups we'll say once a week differentials every night incrementals or transaction logs every hour during the day your strategy will depend the bottom line is you always have to answer the question how much data can you afford to lose if the answer is none you need to be very particular about what type of backups that you perform so you can recover that content in the event that you need to just to let you know these are the three primary backup types that you need to be familiar with there are others that we're not going to get into detail so if you're looking around at this because you're going to be excited about sql server when we finish today if you're looking around at this there are other types of backups that you can perform take a peek at those but these are the three primary ones that you'll be using here so let's go ahead and back up a database just to show you how easy it is to do this because we're going to use the gui and back in the sql manager studio let's back up and see what's in class demo class demo nothing let's go i thought we created a table in there did we not over there well we created that one in sales let's go and create let's do this uh let's do a new table class demo try to click that so i showed you how to create a table using transact sql you can also create a table inside the gui it will happen so we're going to call this column one real uh definitive as to what it is here's my data type the default is ncr10 and we allow those so for sure you're going to start creating the cd library database artist [Laughter] we'll do the artist you're going to do the artist table interesting we'll do the artist one yeah i know i didn't want to go over to i keep losing my mouse i didn't want to get over to the disco one you know the uh genre so artists what we had well we would have artist id actually artist id and probably artist name maybe yeah artist name would be good probably a picture no artist name i need to do notice i'm going to use that pascal birthday birthday i'm going to keep giving you columns until you stop so sell as many as you want let's change this because now we found out these guys can be crazy with names we need to drain this back to an int for artist id because that'll be your key look at us sort of reviewing everything we just did yeah this is going to be our int there and we're not going to allow no's and let's do one more here we'll reach so birthday maybe birthday because i'm sure that you and donna summer have the same birthday yeah well we had different schools together so and once that would give us a chance to use i just have no idea what my mouse just disappears every once in a while as soon as i get my mouth there first it just disappears jumping out of the vm that's all right and we're gonna put date but this is cool this is a third data type in this table yeah so we have an int and our chart and a date the bad thing is we're gonna toast it anyways but not really but all right so we have a new table here so we're going to save this table out and we're going to call this artist and i feel i think we should have done a genre so i could put it just type the word disco in i mean we talked about it all week all day all right so we have a new table so we'll come over here to our tables we'll refresh it we see our new table here all right now the reason i did that because i'm going to back this up i'm going to go to tasks and i'm going to use the backup option and it's going to backup class demo and by default it dumps it way down here and you know we're going to get rid of that i've got another easier directory to remember to chase it down afterwards and we're going to back this up to it's always no i won't even say it i was going to make a joke but if someone really doesn't understand they might think i'm serious and i'm like no i'll leave it alone we're going to back up sql backups and we're going to call this um class demo db it'll use the dot bak extension so i'll just let that go i'll let that go there it comes back so we're gonna back it up to there and we'll go ahead and do an okay and then we'll click off on okay on this one and the backup database class demo completed successfully all right cool so that's done and we know in there that we should have the dblo dbo artist now there's a slide on restore so i'm going to stay in the demo but let's pretend that somehow this dbl artist gets toasted you've been waiting to toast something for the entire class i know you should do i think you should do that i know i should have actually what i should have done is prepare and written procedure called toast yeah exactly i'm going to work actually once equal command for drop yeah i'm going to definitely write that so i'm going to delete this this could be somebody accidentally deletes it or they run that delete command i told you that without a where clause or an update maybe just the date is bad because someone ran an update without a where clause and this faster way to get it back is to go do a backup so i'm going to go ahead and toast this and i'm gonna do a refresh over here in class demo you'll see under tables now the the table's gone gone all right toast all right so how do we cover it class demo go back to task and we have the restore option and database and we're just going to keep this the simple method it knows what's been backed up it's telling me right now the only thing i have for you is a full backup if there were differentials and incrementals it would list them all out it would make sure i re restored in proper order it wouldn't let me jump around and stuff because that'd be a badness so it knows we backed it up here's the date and the time for the backup so we know it's good and let's see here i'm just going to leave it just at this like this attempt to restore and during the restore here it's actually what it's doing it's actually looking at the information that's in the backup and determining exactly where that needs to go to do that restore and looks like it's okay well how can we test we'll go back see if our table is there let's do a refresh okay because it's not showing up still not showing up sometimes the refresh has to go all the way to the top here let me do that one uh let's see what am i doing this guy here i'm still not there all right let's try another restore because that didn't seem to pick it up so i did the backup i toasted it so it should be there all right let me try it one more time all right tasks restore database you know what it didn't come back up and say it was it was restored it should come up and say it was restored and i didn't see that pop-up box so let me try this again so we've got this check but the backup is still on disk so it should be totally fine right correct because it's listed right there this ends right here it should say restore completed successfully i didn't see that last time and it really shouldn't take that long because there's really no data in there i still think this might be a ploy see if you can create the genres table i really want to type in discount no see it's not it's restored databases failed um all right let's try this let's do the verified backup or just real quick so that's okay all right let me do cancel this let me try one more time tasks restore database and class demo db is the database database there database full backup we just did let's see if there's any options i'm not trying to move it is this similar is there somebody connected to it is that that problem um it may be let me try this closing because it's internet it's just mainly just a single user let's try that just in case it is because it should be a nice right there that's what it was because i was still connected to it you were still connected to it yeah i thought we were going to have to do some tap dancing or something to yeah entertain the audience while that was happening yeah seriously let's see if it worked though it's going to make sure it's over here yeah there we go got it yeah i was still connected to it right so i didn't get it it was it needed sync it needed to be in single user mode so i did that on purpose you guys can see it's not all peaches and cream to try to do a restore there can be issues even though it looks pretty straightforward there's little things like connections are out there already established you need to make sure brings it during the restore it brings it into a certain mode so it actually has exclusive rights to be able to do recovery which is why there's a whole course that has like three modules on backup and responsibility strategy good design implementation yeah disaster recovery and i mentioned the uh the term earlier about rpes resume producing events dr is going to be the most likely reason you have to generate a new resume because just to not enough um the backups weren't done or people don't performing a backup and they didn't realize the backup wasn't working which is actually pretty cool because we've gone from the very beginning of the day we went from a spreadsheet which you can have a backup strategy for you can copy it to multiple locations you can do cloud backup for a single file things like that we've gone all the way to i need to take a class and just how to do like a strategy for creating incremental transactional backups for my enterprise class database yeah i mean the backups of class and backups a class on data modeling or designing your database would be another one once you get the database design and out there how do i what's the best way what kind of queries can i perform how can i add content update content to it so all sorts of options that we've been been through today and a high level overview but um just it's the real world for what it needs to what needs to happen so we did it so very similar to backups we had the complete the differential restore we would perform those in order and once we did those in order that would make it easier for us to get that complete database back up get the differential backup and then get the transaction log a backup restored and already did the restore because i was already out there so objects that we need to secure a three-tier approach giving them access to sql server whether it be through windows authentication or sql login if it's someone external to the sql world we introduced the three terms of securables principles and permissions the second area was giving them permissions to the databases we saw that we can do that as we create the login and the third one was giving them permissions to the objects using the grant revoke and deny permissions to to manage those permissions so login database user and then permissions to the objects or the ways that we're going to control our information or security permissions to our information and with that said pete i think like you said we started out with a basic old spreadsheet this morning and now we've gone through it's like how we can create a relational database with tables and enforce referential integrity by using primary key constraints and foreign key constraints and we talked about how we can protect the data by implementing security how we can use select statements for retrieving the content and slicing and dicing it several different ways different ways for adding content deleting content and updating content and then we wrapped up by talking about ways for securing that content and protecting that content by performing backups and restores is there anything you'd like to add i think i think we've nailed it i think we've covered everything we wanted and we still don't know what disco albums you had so i'll no we did learn though that there was was a 360 character title name out there so that was going to totally resign sixty word no it's 89 words exactly know your data yeah thanks very much folks thanks for watching thank you

Channel: Nerd's lesson

Views: 7,490

Rating: 5 out of 5

Keywords: database administrator tutorial for beginners, sql database tutorial for beginners, database tutorial, #database part 2, #database part 1, #databases, relational database, sql database youtube, sql database 101, database, tutorial, for beginners, basics, overview, database management system, explanation, how to, erd, entity relationship diagram, Database Design Tutorial, Database Design, Relational Database Design, Database Design Overview, Database Normalization

Id: kDiZkmAzevY

Length: 207min 27sec (12447 seconds)

Published: Fri Jun 18 2021