1 00:00:05,060 --> 00:00:08,750 Let's start by opening SQL Server Management Studio. 2 00:00:08,780 --> 00:00:12,890 Close any open query windows you might have for this lecture. 3 00:00:12,890 --> 00:00:17,750 We'll be using the Battery code database since it has lots of data for us to query. 4 00:00:17,780 --> 00:00:25,340 Select it and we'll expand it here, and then expand the table so we can see the names of the tables 5 00:00:25,340 --> 00:00:26,480 in the database. 6 00:00:27,430 --> 00:00:32,680 Next, let's open a query window by clicking New Query button in the toolbar. 7 00:00:32,680 --> 00:00:35,740 Make sure Battery Company is shown in the dropdown. 8 00:00:35,740 --> 00:00:40,450 Here we're going to be saving the queries we write in this window into a SQL file. 9 00:00:40,450 --> 00:00:43,150 So I'll begin by showing you how to add comments. 10 00:00:43,150 --> 00:00:46,360 We're going to start with the simplest Select statement. 11 00:00:46,360 --> 00:00:50,860 So I'm going to indicate a comment by doing two dashes. 12 00:00:51,010 --> 00:00:56,050 And we'll just write this is the. 13 00:00:57,450 --> 00:01:00,510 Simplest select statement. 14 00:01:02,190 --> 00:01:06,630 And let's just go ahead and make a number there. 15 00:01:06,630 --> 00:01:08,910 And so you see how the text is green. 16 00:01:08,910 --> 00:01:12,510 This indicates that this text is a comment. 17 00:01:12,540 --> 00:01:18,750 Comments are ignored when the query code is run and is simply there for notes or documentation by the 18 00:01:18,750 --> 00:01:19,410 author. 19 00:01:19,770 --> 00:01:21,630 So we'll go ahead and write our statement. 20 00:01:21,630 --> 00:01:24,570 And this is one that we've done in this course before. 21 00:01:24,600 --> 00:01:28,110 Select star which is a wildcard that means everything. 22 00:01:28,110 --> 00:01:33,390 And then from and then the name of the table we're just going to type out customers. 23 00:01:33,390 --> 00:01:37,020 So notice that select and from are blue. 24 00:01:37,020 --> 00:01:41,820 This is because these are keywords in the SQL programming language. 25 00:01:41,820 --> 00:01:46,740 Things that are black are usually the names of objects or just syntax. 26 00:01:46,830 --> 00:01:48,900 Text that is green is a comment. 27 00:01:48,900 --> 00:01:54,660 And as we've seen before, anything that is enclosed in single quotes is a string and that's red. 28 00:01:54,660 --> 00:01:58,980 So these colors are standard in most development environments. 29 00:01:58,980 --> 00:02:04,650 So now that we have our query we'll just hit the execute button and we'll see our results. 30 00:02:04,650 --> 00:02:10,200 And it gives us all the columns in the order that they're listed in the definition of the table. 31 00:02:10,200 --> 00:02:12,300 But there's more to the select statement. 32 00:02:12,300 --> 00:02:18,030 So the next example let's select column names from a table. 33 00:02:18,540 --> 00:02:22,470 So instead of doing the star we will type select. 34 00:02:22,470 --> 00:02:25,020 And then we can just list the column names. 35 00:02:25,020 --> 00:02:28,290 So f name l name. 36 00:02:28,290 --> 00:02:31,200 And you see that the intellisense brings these up. 37 00:02:31,200 --> 00:02:37,500 So you can use a shortcut when they come up and select them or hit enter we'll do address. 38 00:02:38,420 --> 00:02:43,940 City, state ID and zip. 39 00:02:43,940 --> 00:02:50,270 So we'll exclude the customer ID and phone and then we will do from customers. 40 00:02:50,570 --> 00:02:55,070 Now here's a feature of SQL Server Management Studio that I like. 41 00:02:55,070 --> 00:03:00,920 If I highlight a line of code I can just execute that and it will execute only that code. 42 00:03:00,920 --> 00:03:05,690 If I don't highlight something and hit execute, notice that we get two result sets. 43 00:03:05,690 --> 00:03:13,400 The top one is from our select star query and the bottom one is from our select column names. 44 00:03:13,400 --> 00:03:19,970 In the next example, I'm just going to prove a point that the order of the data in the table or the 45 00:03:19,970 --> 00:03:22,760 columns in the table doesn't really matter. 46 00:03:22,760 --> 00:03:25,850 So I'm just going to do an example called columns. 47 00:03:25,850 --> 00:03:40,160 Order doesn't matter and we will just do select city L name, f name, zip from customers. 48 00:03:41,450 --> 00:03:43,850 And this will execute just fine. 49 00:03:43,850 --> 00:03:50,390 So the order of the columns doesn't matter, and the order of the data as it's inserted into the database 50 00:03:50,390 --> 00:03:51,860 also doesn't matter. 51 00:03:51,890 --> 00:03:54,710 Next, I'll show you another trick. 52 00:03:54,710 --> 00:03:58,010 We don't really like the column names L name and F name. 53 00:03:58,010 --> 00:04:02,090 It'd be great if that said last name, first name and things like that. 54 00:04:02,090 --> 00:04:04,760 So we can use what is called an alias. 55 00:04:04,760 --> 00:04:10,700 So our next example will be column names with alias. 56 00:04:10,700 --> 00:04:16,550 And so I am just going to copy this number two and paste it down here. 57 00:04:16,550 --> 00:04:19,430 We'll do f name and we use the as keyword. 58 00:04:19,430 --> 00:04:22,100 And then we're just going to create a little string. 59 00:04:22,100 --> 00:04:23,870 So we'll use single quotes. 60 00:04:23,990 --> 00:04:29,630 And we'll just put first space name and then l name as. 61 00:04:29,630 --> 00:04:31,550 And then we'll do the same thing. 62 00:04:32,390 --> 00:04:35,360 And the space is okay because it's in quotes. 63 00:04:35,360 --> 00:04:39,980 And then the state ID will do as state. 64 00:04:39,980 --> 00:04:43,460 And we'll just highlight this and hit execute. 65 00:04:43,460 --> 00:04:47,030 And now our column names are more user friendly. 66 00:04:47,030 --> 00:04:49,670 So that's the concept of alias. 67 00:04:50,930 --> 00:04:55,940 Now this next example is pretty neat and it's called select into. 68 00:04:55,940 --> 00:05:03,260 And this allows you to make a backup copy of a tables data into another table. 69 00:05:03,260 --> 00:05:07,040 So it's select star into. 70 00:05:07,040 --> 00:05:12,440 And we're just going to do a table name that doesn't exist say customer backup. 71 00:05:12,440 --> 00:05:14,990 And then you do from customer. 72 00:05:14,990 --> 00:05:17,720 And when we execute this. 73 00:05:18,690 --> 00:05:23,520 It actually will make us a backup copy of the table. 74 00:05:23,520 --> 00:05:28,140 And if we take a look at that select star from. 75 00:05:30,400 --> 00:05:32,350 We can see that. 76 00:05:33,090 --> 00:05:34,650 All of the data is there. 77 00:05:34,650 --> 00:05:40,230 So we could use this to maybe try some updates or some deletes or things like that without worrying 78 00:05:40,230 --> 00:05:43,230 about actually deleting data from our source table. 79 00:05:43,230 --> 00:05:50,010 Now, if we go look at customer backup, notice that there are no primary keys or foreign keys. 80 00:05:50,010 --> 00:05:55,170 If we go look at the design, it does not preserve your primary key here. 81 00:05:55,170 --> 00:05:59,400 So just kind of keep that in mind if you are backing this up. 82 00:05:59,400 --> 00:06:03,960 And then you want to restore the backup to the original table, you'll have to go put your relationships 83 00:06:03,960 --> 00:06:04,710 back in. 84 00:06:04,920 --> 00:06:08,280 Now this last example is also pretty useful. 85 00:06:08,280 --> 00:06:11,670 And it is using the distinct keyword. 86 00:06:12,060 --> 00:06:20,250 And the distinct keyword allows us to list distinct records based on one or more fields. 87 00:06:20,250 --> 00:06:28,530 So let's say that we wanted to know which states we had customers in so we could use select distinct 88 00:06:28,530 --> 00:06:30,450 and then state ID. 89 00:06:32,350 --> 00:06:34,000 From customers. 90 00:06:34,000 --> 00:06:42,670 And what this is going to do is it will just give us a list of the unique states or territories that 91 00:06:42,670 --> 00:06:44,080 we have customers in. 92 00:06:44,080 --> 00:06:46,360 So this could be a pretty useful query. 93 00:06:46,360 --> 00:06:54,160 Later on in the course, we'll probably use it with multiple columns in order to get a set of unique 94 00:06:54,160 --> 00:06:54,820 records. 95 00:06:54,820 --> 00:06:58,480 So we're going to go ahead and save this file. 96 00:06:58,480 --> 00:07:01,720 So we'll do file save as. 97 00:07:02,640 --> 00:07:05,820 And SQL server has a special place to keep these. 98 00:07:05,820 --> 00:07:15,060 You can save these anywhere you want and I will just do basic SQL select examples and I'll hit save. 99 00:07:15,060 --> 00:07:19,650 And these will be part of the assets for this lecture. 100 00:07:19,650 --> 00:07:25,590 And in the next assignment you're going to have some practice doing different types of select statements. 101 00:07:25,590 --> 00:07:27,600 So I'll see you in the next assignment.