Video: Manipulating Database Data

Presenter:Bob Tabor (from LearnVisualStudio.NET)

SQL Server beginners will learn about ADO.NET objects and execution of SQL statement from ADO.NET. Explore SQL and learn about insert, update, and delete statements.


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Although this point we been looking at how to query relational database tables in this lesson and the Mets will be looking at how to retrieve and manipulate data in our databases than the previous lessons whenever we wanted to perform any crud operations are to create read update or delete out what we typically did was open a visual Web developer 2005 express edition we got to the database Explorer navigated through an until it s on the table that we re looking for right clicked on it and is selected something like show at the table data and then we began to work with it in a very deep Excel spreadsheet type of format within the main area of our IDE however is the start of builder applications there s really two ways that we will interact with data and the first method is that we ll use
Minute 1
Retrieve manipulate and navigate through tables rows and columns and will highlight this in the last video in our series of another method that we can use is actually the execution of special commands are called SQL statements from within radio net with more he uses of the term SQL or I think in one case I called a transact SQL several times up to this point in our lesson series but what is that really mean SQL stands for structured query language and is a different kind of programming language is much that was created for very specific purpose and vessel lie to retrieve insert update and delete records from database tables know SQL or SQL is also pronounced is a standard language across many different database vendors however Microsoft is at its impartiality to standard SQL that provide a bunch of additional features of the
Minute 2
Database vendors can t or won t support so sometimes when talking about the brand of SQL that Microsoft supports you might hear the term key SQL or more commonly transact SQL therefore pursues these are one and the same the sequel is a very rich robust programming language is getting a lot of power and a tremendous number of options but it is a look at all like Visual Basic or see shark remember it was created to solve a very specific problem which is how to effectively retrieve data from relational database tables sort of split up the topic matter to two videos in this lesson read a look at selecting data from a database and the next lesson will focus on serving up the updating and deleting data using SQL state s for examples really need to get you set up so you can follow along there are two resources that you need first to ballroom to performing ad hoc queries and
Minute 3
Top query allows us to interact with the database without having to create an entire application we can execute a one remorse statements directly from within a special tool that allows us to compose statements executed him and then view the results in the data that gets returned back to us is in a tabular style format which you can then save the text file or to a spreadsheet or copied to the Windows clipboard about this is different than consuming the data in a Windows forms application or all Web forms application on those applications we might format the data nicely and allow some sort of interactivity with the Datastore users can interact us in a safe sort way but an ad in ad hoc queries the datum s back out rather plainly but that s exactly what we want we just want to quickly perform some operational reviews some database values without the overhead of creating entire application just for that purpose
Minute 4
So facilitate this room you to install companion tool to SQL Server 2005 express edition called SQL Server management studio express soul among some of the administration and data management capabilities of this will give us were also be able to perform some ad hoc words which is really what will I do so only to do nor he had installed it as a browser and go to http MSDN Microsoft com express and want to get to the express homepages will go to the overview for SQL Server express will pop up menu the additional menu on the left inside and only to scroll down to little ways and currently he can see that under free management tool that we can download SQL Server management studio express this will begin the download process and I ll let
Minute 5
Of their documentation give you instructions on how to install this of it on your computer but if you governed solely software to treasury forward now was to have the software up and running the mixing that we wanted you it is install the sample database that I created it and the download for the sample database should be in either a zip file or MSI file and located if the same place for you download this video so once you download it to your hard drive if it s as if file then you need to open it up and then full wanted to his cocky these two files into a folder of your hard drive so her to open up all programs excess reuse it is over and navigate to our computer see drive noses happens we were uninstalled the SQL Server management studio express
Minute 6
Can go to bikers are SQL Server MS SQL 1 and a SQL data day you see there s already some sample databases here are going to copy our own to databases from here and drop them into here and pay a step one is all that down there to open up SQL Server management studio express what are we to this dialogue will go Hensler connects it and we are going to expand the databases node in right click on databases and select attach this will allow us to attach an existing database optionally we can create a brand new database from scratch the summer to select attach result in
Minute 7
Attached databases dialogue will select the add button and then it will give us a file dialog allows to select the name of our database in this future case we want to select my company MDF and click okay you can see the my company database details are listed here we find both the MDF and the LVF files and fortunately the screen wasn t it to run at 800 x 600 song of two and move around older here we get to the okay button at the bottom of great site to see listed under my databases in the summary tab and also under databases within my object explore and as we open up and take a look at our tables we can see that we
Minute 8
Out five tables in our database I created a diagram so that we can take a look at the relationship of these tables are not the job of upgrading database diagrams in the series of videos however it s for easy to do and it gives you an overview of all the relationships between our table and size this will do smaller so we can see it all one time so we can see that we have first of all customers table and customers are related to orders each order can have one or more order items each order item is tied to a specific product in each product has is associated with the product type to qualify it and this is although very simple table structure is very similar to other table structures that your problem to find or need to create for yourself and others nothing special about
Minute 9
This is just a way for me to exercise so the structure query language by selecting a data from one or more tables but I wanted you to be aware that there are five tables and that they are old related tables if you re following along with me I would recommend taking the time to go through each of the tables right clicking and selecting open table so that you can review the of the data that s contained within each of the tables and kind of look at the relationships for example how many orders would we expect for example though Brown customer ID number four to half while we send them to your orders table and browse through all the customer IDs that are foreign key have a 40 relationship with the customer table so here s one you see customer IDs for here s another one and so on
Minute 10
So I really encourage you to pause in the awry here and take some time to review the database table structures okay our goal here is to write ad hoc a query is so that we can begin looking at some simple select statements to retrieve data from our database tables so in order to a facilitate this were going to select the new query button on our toolbar and this will give us a blank page within our main area that will allow us to writes and executed some are queries so let s start with a very basic select statement from script haste in our data here noticed first of all that I have two little dashes this means that this is old comment of code comment it appears in
Minute 11
E en and this will not get executed whenever I choose to execute this query using execute button or F5 on the keyboard but as you can see I have a simple select statement select star from customers the star represents all the columns us go and execute as Kerry and take a look at what we get noticed first of all that I get an error message invalid object name customers the reason this occurs is because I have not selected database that I want to work with even though I may have a selected the object Explorer when you to select which database for querying from on the toolbar so missed a good case for Bush select my company and now her to execute the square to get in this honey can see that we have a tabular format the retrieval of all columns
Minute 12
All rows from our customer table and what if we don t want all of the data returned to us will this is where we have a kind of options for sample in this particular case were able to select just the first name column and the last name column from customers instead of selecting the execute statement on to highlight the commands that I want to execute song with a mouse pressure roper hold on my left mouse button and drag across the select statement and then click the execute button this will only execute the line that I have highlighted which is a way for me to write a number of SQL statements yet only execute the ones that I currently won t work with as you see after I hit the execute button I got the results first name and last name still retrieving all the rows of data but only two columns were
Minute 13
Notice of the column names and the results are the exact the same as what we have is the column names in the table and we are winning more format this so that we can save this information off by selecting file old saved results as and then selecting either a CSV file separated value file or text we may want to format these results will do differently so if this particular case was paced in another statement go ahead and auto hide this we can see our complete statement here you see in this particular case I m going to alias the column name so that it s printed off a little more friendly all doing here is changing the column name as it is producing the results from the first name ofin
Minute 14
Oldest insert a friendly space to make it more readable and will select our line is executed and concealed in a change was that the column names are different continuing on with this idea let s go ahead and concatenate the content instead of having two separate columns worth of data were going to concatenated format the values that are returned so will take first name whatever it is in this case Jim will add a space using to FDR to take parks with an empty space in between at another string concatenation character has been the lasting column and will call this new column customers may go ahead and select all this and then click execute notice now we have a column called customer name
Minute 15
And all of our names now have the first space last name right to say we want to limit the number of results are returned back for God to us that we don t want every record in the table returned in that particular case we can change of our statement to include a where clause now really working with the products table and so what we want to do is select a single record from our products table in the condition that would want to satisfy is where it products IDE And select the entire line and select execute and now we can see that there s just one row returned to us where the product ID we can use a where clause that are select statement to define any number of conditions that of the data that must be satisfied to return back
Minute 16
To us the results for example here s a slightly more complicated example here were selecting every column from products where the product price is greater than 30 so as long as the product price column is greater than 30 will return those rows you can see that in this particular case all three of these rows satisfy that requirement can perform the opposite as well and only select those rows for the price is less than 30 agency at this point were only selecting those items where the price is less than we can use other operators as well such as equals or in this particular case
Minute 17
Greater than or equal to 20 to 95 you can see that all the items are either greater than or in this case equal to 2995 and while we can use the can also check for any qualities to make sure that we return any product for the product I type ID is greater than or less than two soul as long as it s not equaled to return those rows so as you see as we move off the right hand side were only selecting those products with the type ID of one or three not to and not only does this work with numerical data but also works with character data we can make sure to not select or on any records where the product name is equal to and Smart are with us or put another way s
Minute 18
But all the products as long as the product name is not smart at which it s in see we are missing record number for which would indeed deviance bar by which it for a to confirm the Sims can copy and paste this and change the operator to equal and you can see the opposite then would be to select only the row where the product name is equal to its part to its and not only does this work with with numeric data stream data but also works with data data in this particular case were taking sure that from the orders table were only selecting those rows were the older order daytime is greater than 110 2006
Minute 19
Now you might say to the fourth row this return is actually on 110 2006 but because we didn t specify a time midnight is assumed so that is why on the 10 and order place at 11 49 a m is still greater than midnight of 110 2006 thou want to add one other feature as well and that is to add a second condition using tea and statement so we re going to check and make sure to only select those orders were the order daytime is greater than 110 2006 and is less than 113 2006 eventual innate potential we are all one row why because again this assumes midnight and we would expect to see her ID number one not
Minute 20
And its results illustrate sure by selecting click execute and indeed that particular road data has been removed similar to the 10 statement is he or statements which means that either the rows have dissatisfied with this condition or they have to satisfy this condition and requires that old conditions be satisfied or statement requires that either one has to be satisfied on select this so we read this correctly would expect to not see new records between midnight of 19 2006 and midnight of 112 2006 that Time would not be included in our results if we scroll through will see that indeed
Minute 21
Is the case now that some are queers are getting a little longer we can rewrite our queries in a more readable format elites in my opinion and that is to put each of the major pieces of our query on a separate line so this line of code is equivalent to the lines of code was a lot of start talking about some functions that we can use within our applications and these are analogous to functions that we might using Visual Basic or see shark or some methods that are available there for example we can use this some function to aggregate all of the values in the tax column from our orders table so this would give us a report of all the tax has been collected from our orders table
Minute 22
Account function gives us the total number of records within a given of the select clause so Mr Sigler case let us know how many records are in the orders table 17 we can also perform mathematical equations within our select statements down this particular case legally separate this on multiple lines here were going to select a product ID and then the product price ever an alias list of new name regular price and then we re going to create a second language column which is taking the call of the product price and subtracting five dollars from it and really call this the sale price to notice that were performing
Minute 23
This equation in Linux organ select these two lines and click execute and notice that we get a regular price of 2995 and its five dollar discount would be 2495 all by just subtracting five from whatever values returned from the product price equal similarly we can give a 10 discount by multiplying the product price times 9 as the sale price using the menu function we can find out what the least expensive item we have in our products table is board using the tax function and find out with the most expensive item in our product table is
Minute 24
Or and find out what the average price of all the items are for table is not only are there functions to help us with math but also there are functions that help us out with geeks so in this particular case we want to extract out the actual name of of the month if we were to was created as a different let s do this as well fleshly sorrier and students on the fly so for social order ID they will show the full order daytime and then we re just to use among function to tell me what the month number is in the day function tell me just what the Day part of our our date is to concede
Minute 25
In the multicolumn it s all in January 1 and in the Day column it depends 13 to 12 the fifth to seventh to ninth and so on we can use he did take function looks to get day function to return the current daytime which is useful whatever were doing comparisons between now and how many days it s been since a particular record has been in the database and will show how that could be useful in moment here would you use special function call date name to retrieve a friendly date name instead of the numerical value in this particular case we want to select a month of the order daytime from the orders table you can see instead of returning one it returns a stream story
Minute 26
And are listed just a quick help reference here that we can select just the year to the day the hour and minute or the weekday Sunday to Saturday scrutiny and this is similar to what we just accept it once again the Day part as opposed to date name returns is just the number the numeric value of this case want representing January TV date diff function will give us the difference between two dates in this particular case for using that same will chart the soul nomenclature here to designate what are we comparing in this particular case we want to find out how many days separate today from the original order daytime from Egypt orders in the orders to tell tall old into the orders are you can see that they really range but they re within the 45 to 50 a day difference
Minute 27
And just as we we can perform mathematical equations with our select statements within also add days and subtracting his years or months or whatever I specified by the particular on the part in this particular case was an ad five daisies to the order daytime was compare that to the original order daytime to concede there is indeed a five day difference between two this tell you this value for instance just as we can use functions to help us manipulate dates and math can also use for strings as well in this particular case I am using the substring function in order to start at the third character all of my product description field within my database
Minute 28
And retrieve the next five of characters from that third position so we ll get some pretty funky funky looking results from this you can see those make a lot of sense but that indeed starts at the third position and selects five characters similarly you can use either the right function to select with the rightmost five characters for the left function to select just the leftmost characters in this particular case five can also make sure all the text is an uppercase or all in lowercase and possibly my most
Minute 29
Are one is the reverse function which is I know it doesn t look to be very i first let Cubs fan like Kerry Kerry Asher he could make good use of this sloughs were useful is the life clause this allows us to search against the pattern in this case the pattern is that we want to find all first names that start with the letter J and we use of as the wildcard indicate that any characters that come after that are just fine so as to execute this and as you can see we have just those customers that have the first name that start with the letter J similar to the life clause is the in clause by using the in clause we don t have the
Minute 30
Drained a bunch of or clauses together like select a where state provenance equals INO or state provenance equals Kay as for state provenance equals P8 or and so on instead we can just make a list of those values that we want to satisfied with our where clause and it ll only give us those in return social execute and as you can see we ve only return those rows for the state provenance or a column equals I tell you see we have several rose that it matched out or Kay asked me of a couple that matched out or PA posted look at how to order the results that we get returned back to us and we can order the results by using the order by clause
Minute 31
In this case we will order all the customers by their last name TASC clause means I will order by ascending order school had run this so can see stars of DL ER ER jail and so on or we can perform essentially the exact same statement at this time changing to DSC or rather ascending order so in this case TA YTPS USC and so on or we can designate that we want to order first by last name and then secondly by first name so in this particular case if you ll take a look at the last name Smith we had to Smith s but we say we want to
Minute 32
Erred by first name in Angelo comes to forge an of the selects become little more interesting whenever we can use some of the group by statements soar 40 looked at the psalm which allows us to get the total of only given column but if we were to group those summations by another field in this particular case the customer ID we can determine what the total cost of shipping was each customer paid across all their orders and we conclude one other piece of information here as well I think this will work now we can see how many orders it in this represents in this particular case customer ID number three had three orders for a total of 29 85 worth of shipping
Minute 33
Similarly we can use to having statements to further her but reduced the number of rows are returned so this looks very similar to previous example but we ve included one last statement having count greater or equal to three so show me all those customers who have three or more orders and give me a customer ID and the amount that they pay for shipping so only three customers have three or more orders and this is the total that they pay for shipping a crosspost reorders prequel bouquets and now things are getting a lot more interesting because up to this point I ve only been selecting records from a single table but what if you want to select data from two related tables
Minute 34
Porky relationship between the stable will to do that we can use were called joins us different types of joins the only joints were to talk about in this series are inner joins so you as you can see on the select a product ID field from the products table the product name for products table and the product type name from the product types table so the product types in the products table or a related but from statement is going to provide some information to SQL on how these two tables are related and what a SQL should attempt to join those fields on so we re going to select from the products table books were going to make sure to inner join on the product types table in the way really do that is by setting the product type
Minute 35
DC for products table the foreign key SQL 2 the product types product type ID primary key in the prototypes able to download see what we get here whenever we execute this two lines where he would get the product ID the product name and instead of the numeric value representing the product type or able to retrieve the full type name of the product books movies music and so on a but wait it gets better we had a type out quite a bit of information every time we wanted to reference a given column we wanted to behead two of a predicated with the taste only but we can shorten this using aliases so select paid up product ID keyed a product name in PT org type name from products
Minute 36
And here s where I create the alias piece of PE represents products inner join product types PT so PT will stand for product types tables with archetypal thing out in her join on peanut product type ID equals PT company and will give the exact same results now again just with as we did in the previous examples were rewritable use all sorts where clauses you can do that which ones as well in this particular case or adding a where clause to our previous statement there were no selects all of the columns from the products table and the product type name from the product type table but only return those records were the product price is greater than 2995 and so that only return
Minute 37
Was three rows of data but you can see here that we have both the product type ID and the product type name and finally you can create some pretty convoluted SQL statements by just continuing to use the same nomenclature of inner joins so if we were to go back to our dire grant from what if we wanted to find all of the products that a given customer has ordered we had to go through the orders table your items table and then finally to the products table in order to accomplish that but we certainly can t publish that because all these tables are related so for a given customer we can find out all the products that they ve ever ordered the way that we would do that is through a number of inner joins so we want in this particular case select the last name
Minute 38
The order ID and the product name additionally were to order of the values that are returned by first call by last name in and buy order ID so let s go ahead and run this monster queries so you can see a customer blessing the black has ordered in order ID one to products order ID five to products and order number 14 was products you can see the product names for each of his each of his purchases same thing with Briscoe Brown Johnson LeBlanc and so on very cool and honestly all of these statements all of the permutations that we just discussed here for the past few moments came off the top of my head you get to the point working with SQL enough that it becomes second nature
Minute 39
You just as Visual Basic seashore Weber will become second nature to you as well so while this might seem a little overwhelming at first want to spend a few days from leaks inside of the database you ll begin to understand the lingo and be comfortable with how to get to the information you re trying to get to buy first admittedly I had to by books in an read articles and religious point and was straw poll with with trying to get to the data that I needed so I encourage you to take a look at the SQL hell is provided with the with SQL Server and then maybe find a good reference book on sequel that will help you at to work your way through in getting into the data that you need for your particular application so that wraps it up for this particular lesson in the next lesson really continue this thought every move into some more advanced selects a selects and
Minute 40
And insert update and delete syntax for structured the structured query language and please visit www learnVisualStudio net and download over fiber screen cam video tutorials on many topics related to net seashore Visual Basic ASP net and more picky