1 00:00:01,160 --> 00:00:06,450 My name is Matthew Graham and this is the fourth in a series of six modules. 2 00:00:06,450 --> 00:00:09,430 This is on SQL, the basics. 3 00:00:09,430 --> 00:00:10,990 In this module and the next module, 4 00:00:10,990 --> 00:00:16,950 we will be considering the, SQL, the main technology that is used 5 00:00:16,950 --> 00:00:22,020 to work with relational databases which we introduced in the previous module. 6 00:00:23,740 --> 00:00:26,610 In this particular talk, we're talking about the basics. 7 00:00:26,610 --> 00:00:30,330 Next talk, we will be talking about some more advanced features of SQL. 8 00:00:31,670 --> 00:00:34,200 SQL stands for Structured Query Language. 9 00:00:35,210 --> 00:00:38,190 The first appeared in 1974 from IBM. 10 00:00:38,190 --> 00:00:42,720 First standard was, was published in 1986. 11 00:00:42,720 --> 00:00:49,240 And there have been updates since, with the most recent in 2008. 12 00:00:51,450 --> 00:00:57,330 Each variant of the standard is, is named by SQL and then the year after it. 13 00:00:57,330 --> 00:01:01,960 And, SQL 92 is taken to be the default standard. 14 00:01:03,550 --> 00:01:09,170 There are different flavors of SQL even though they are all supposed to be 15 00:01:09,170 --> 00:01:10,680 attached to the standard. 16 00:01:10,680 --> 00:01:14,390 And those will vary from the different types of 17 00:01:14,390 --> 00:01:18,580 relational database management system that you are actually using. 18 00:01:18,580 --> 00:01:24,030 So, Microsoft and Sybase use a flavor called transact SQL. 19 00:01:24,030 --> 00:01:32,380 MySQL uses MySQL, Oracle uses PL/SQL, and PostgreSQL uses PL/pgSQL. 20 00:01:32,380 --> 00:01:39,990 The call syntax is the same, but in, the differences are ones of minor syntax, 21 00:01:39,990 --> 00:01:46,210 or maybe additional functionality, but they have over that default core standard. 22 00:01:48,560 --> 00:01:53,306 The variant we're talking about here is mainly featured on the, on the core. 23 00:01:53,306 --> 00:02:02,119 So, the fundamental argument in a SQL statement. 24 00:02:03,130 --> 00:02:06,370 An SQL statement that you sent to the database to carry out 25 00:02:06,370 --> 00:02:10,480 operation is the SELECT statement. 26 00:02:10,480 --> 00:02:18,750 And you see here the syntax, SELECT, a, a list of, variables. 27 00:02:18,750 --> 00:02:22,920 A list of, column identifiers that you want returned. 28 00:02:22,920 --> 00:02:29,830 From a list of tables in the database where some condition is satisfied, 29 00:02:29,830 --> 00:02:34,640 some predicate, and then possibly some ordering criteria. 30 00:02:36,080 --> 00:02:42,310 So in our first example, let's consider that we have a table of stars. 31 00:02:42,310 --> 00:02:46,050 It has columns called name and constellation. 32 00:02:46,050 --> 00:02:47,570 The table itself is called star. 33 00:02:48,590 --> 00:02:52,300 There's also a some columns in there of positional information, 34 00:02:53,530 --> 00:02:58,390 right ascension and declination, and magnitude information. 35 00:02:58,390 --> 00:03:00,170 So the first statement there. 36 00:03:00,170 --> 00:03:04,606 SELECT name, constellation FROM star WHERE dec > 0 ORDER by vmag, 37 00:03:04,606 --> 00:03:09,380 will return the name and constellation of each star, for 38 00:03:09,380 --> 00:03:13,440 those stars which are above a declination of zero and it will present that 39 00:03:13,440 --> 00:03:18,800 information in, ordered in terms of increasing v magnitude. 40 00:03:20,770 --> 00:03:26,920 If we want all the columns from from a table. 41 00:03:26,920 --> 00:03:29,140 We can use the asterisk wild card. 42 00:03:30,940 --> 00:03:38,250 So, that second statement there, will return all the data from the start table, 43 00:03:38,250 --> 00:03:45,410 where the position variable RA has a value between not and 90. 44 00:03:45,410 --> 00:03:49,320 So in those two where condition statements they can 45 00:03:49,320 --> 00:03:54,300 see where we can put in logical constraints on them. 46 00:03:54,300 --> 00:03:59,770 Or if we want to do between a range we could say RA is greater than zero and 47 00:03:59,770 --> 00:04:03,790 RA is less than 90 but there is this word between and an, 48 00:04:03,790 --> 00:04:08,170 that we can use in the rest statement to put those together. 49 00:04:10,270 --> 00:04:15,340 If we wanted, if in our table of information we have 50 00:04:15,340 --> 00:04:19,630 information that's repeated, and we just want the unique values of that, 51 00:04:19,630 --> 00:04:24,060 we can use the distinct key word, before our selection list. 52 00:04:24,060 --> 00:04:27,490 So in this case we're doing SELECT DISTINCT constellation FROM star 53 00:04:27,490 --> 00:04:32,740 that will return the list of unique constellation values from that table. 54 00:04:33,990 --> 00:04:43,130 And, in this particular format if we want this is my SQL variant syntax. 55 00:04:43,130 --> 00:04:48,910 If we just want the top five from a list, just to make sure that we want to see 56 00:04:48,910 --> 00:04:54,928 the type of data that's being returned, we would do SELECT name FROM star LIMIT 5 and 57 00:04:54,928 --> 00:04:58,900 ORDER BY vmag because we still want it in magnitude case. 58 00:05:00,240 --> 00:05:02,590 So that is the select statement. 59 00:05:02,590 --> 00:05:06,720 I would argue that 90% and probably more than that of the types of queries you 60 00:05:06,720 --> 00:05:11,910 will send to your database will be of this format. 61 00:05:11,910 --> 00:05:16,350 Now it may be that you have information that is in two different tables and 62 00:05:16,350 --> 00:05:19,770 you want to construct a query across those tables. 63 00:05:19,770 --> 00:05:24,400 That is what's known as a join, or a number of types of joins. 64 00:05:24,400 --> 00:05:28,650 You have inner joins which combine related rows, and you have 65 00:05:28,650 --> 00:05:33,250 outer joins where the rows do not neither matching row between the two tables. 66 00:05:34,760 --> 00:05:39,820 Inner joins, the first example here will return all the information from 67 00:05:39,820 --> 00:05:40,560 a table star. 68 00:05:42,640 --> 00:05:45,160 Joined with a table called stellarTypes. 69 00:05:48,060 --> 00:05:54,650 And then you give it a constraint, satisfied to, to specify the nature 70 00:05:54,650 --> 00:06:01,270 of the join, so in our star table, we have a column called stellarType. 71 00:06:01,270 --> 00:06:04,880 In our stellar types table we have a column ID and 72 00:06:04,880 --> 00:06:08,540 what we're saying is that we're joining on those two columns. 73 00:06:08,540 --> 00:06:13,620 So that matching values between those two columns, between those two tables will 74 00:06:13,620 --> 00:06:17,590 be associated with each other such that that information and 75 00:06:17,590 --> 00:06:20,210 that information will be joined together. 76 00:06:20,210 --> 00:06:22,450 There are two ways of specifying the syntax for 77 00:06:22,450 --> 00:06:25,620 this particular type of operation and all these are given. 78 00:06:25,620 --> 00:06:29,720 One formally uses the inner join construction. 79 00:06:29,720 --> 00:06:33,000 The other one just says select from this table and 80 00:06:33,000 --> 00:06:38,970 this table where table S alias S which is the star table. 81 00:06:40,150 --> 00:06:45,810 The stellar type column there has the same value as the ID column in table T, 82 00:06:45,810 --> 00:06:48,720 where we've defined T to be the stellar types table. 83 00:06:50,150 --> 00:06:54,690 In the outer join, we can say that we want 84 00:06:56,300 --> 00:07:00,279 all those which match plus all those either in the first table. 85 00:07:01,280 --> 00:07:05,160 Which won't have a match on the second table or, all those entries on 86 00:07:05,160 --> 00:07:08,630 the second table which don't have a match on the first table. 87 00:07:08,630 --> 00:07:13,340 Or, the join plus all the missing ones from either and 88 00:07:13,340 --> 00:07:19,040 those are either the left outer join or the right outer join or a full outer join. 89 00:07:19,040 --> 00:07:25,090 So it may be that you want all the columns, all the rows from one table, and 90 00:07:25,090 --> 00:07:28,300 the matched information from the other table as well, but 91 00:07:28,300 --> 00:07:31,470 you, you have to leave out blanks where there aren't matches. 92 00:07:31,470 --> 00:07:34,380 And in that case you would do, like the left outer join or 93 00:07:34,380 --> 00:07:36,600 the right outer join as appropriate. 94 00:07:39,900 --> 00:07:45,780 In SQL you have aggregate functions counting, averaging, 95 00:07:45,780 --> 00:07:48,850 getting the minimum value, the maximum value, or a summation. 96 00:07:50,150 --> 00:07:55,700 This will work on a group of, the group of information as defined by the way 97 00:07:55,700 --> 00:08:00,130 a predicate or the, the, particular column name that you've given it. 98 00:08:00,130 --> 00:08:04,760 So if I want to know how many rows I have in my database table, 99 00:08:04,760 --> 00:08:08,540 I would do select count, and either, a column name or 100 00:08:08,540 --> 00:08:12,530 I could just use the wild card, the asterisk from my table name. 101 00:08:12,530 --> 00:08:14,100 That first operation there. 102 00:08:15,710 --> 00:08:18,820 Second operation there will give me the, the mean value, 103 00:08:18,820 --> 00:08:22,280 the average value, of a particular column in my data. 104 00:08:23,850 --> 00:08:25,330 I haven't specified a where predicate so 105 00:08:25,330 --> 00:08:27,410 it'll just be all the values in the column. 106 00:08:27,410 --> 00:08:31,200 I could restrict that with a where predicate to limit it to a, 107 00:08:31,200 --> 00:08:33,450 smaller set of that column. 108 00:08:36,900 --> 00:08:40,360 If I have multi value data, I can group by it, 109 00:08:40,360 --> 00:08:44,720 and then apply the aggregate functions to those individual groupings. 110 00:08:44,720 --> 00:08:50,260 So, say I have five or six different values for my stellar type column. 111 00:08:51,590 --> 00:08:57,530 I can group all of those of, of type A, all of those of type B, all of those of 112 00:08:57,530 --> 00:09:02,630 type K, all of those type M, for example, and then get the minimum and 113 00:09:02,630 --> 00:09:07,930 maximum magnitude values for those individual groupings. 114 00:09:07,930 --> 00:09:14,088 And that's the use of the third one, example given there the GROUP BY keyword. 115 00:09:16,205 --> 00:09:21,026 If I'm using aggregate functions, then instead of using the where clause, 116 00:09:21,026 --> 00:09:21,841 I might use, 117 00:09:21,841 --> 00:09:28,440 there's a no turner construction, which is the having clause which applies to data. 118 00:09:28,440 --> 00:09:34,960 So, for this last example, we'll return the, the stellarTypes. 119 00:09:34,960 --> 00:09:40,660 The average magnitude for each stellar grouping and also how many 120 00:09:40,660 --> 00:09:45,500 are in each of those groupings where each grouping is 121 00:09:45,500 --> 00:09:49,800 constrained to have objects where the v magnitude parameter is greater than 14. 122 00:09:49,800 --> 00:09:52,697 Now that is the construction you'd use for that. 123 00:09:57,123 --> 00:10:03,304 If I want to create a new database or create a new table in my database, 124 00:10:03,304 --> 00:10:08,742 I would use the CREATE command, syntaxes CREATE DATABASE, 125 00:10:08,742 --> 00:10:13,859 databaseName or CREATE TABLE, the name of my table, and 126 00:10:13,859 --> 00:10:21,020 then the column name and the data type for the column as a list given. 127 00:10:21,020 --> 00:10:23,810 So the example there, create table star, so 128 00:10:23,810 --> 00:10:27,710 I'm creating a table called star, and it's going to have four columns, 129 00:10:27,710 --> 00:10:31,860 the column names are name, ra, dec, and v magnitude. 130 00:10:31,860 --> 00:10:34,500 And the data types associated with each, with each of 131 00:10:34,500 --> 00:10:39,650 those are variable character string up to 20 in length and then three float values. 132 00:10:40,700 --> 00:10:42,580 Number of data types are supported. 133 00:10:43,600 --> 00:10:50,936 I can have a billion properties, int properties, real float, double decimal,. 134 00:10:50,936 --> 00:10:58,720 Those can be both, 32-Bit or 64-Bit, there are a variety of, string data types. 135 00:10:58,720 --> 00:11:01,960 Depending on the length of my, of my Data type that I want to support. 136 00:11:01,960 --> 00:11:07,410 And then, there are typically, time and date Data types as well that I can put in. 137 00:11:10,280 --> 00:11:15,060 I can specify further constraints on particular columns when I'm using my 138 00:11:15,060 --> 00:11:16,160 CREATE TABLE. 139 00:11:16,160 --> 00:11:18,020 I can do CREATE TABLE star. 140 00:11:18,020 --> 00:11:21,320 I say that the, the name field has to have a value. 141 00:11:22,350 --> 00:11:23,560 It cannot be nullable. 142 00:11:23,560 --> 00:11:25,640 It has to have a value associated with it. 143 00:11:25,640 --> 00:11:29,310 If a value is not given when I'm putting data into my database, a, 144 00:11:29,310 --> 00:11:30,970 a constraint will be raised. 145 00:11:30,970 --> 00:11:34,400 I can put a default value for a particular column. 146 00:11:34,400 --> 00:11:35,140 In this case, 147 00:11:35,140 --> 00:11:39,070 in the RA column, I'm saying that it's taking into full value of, of zero. 148 00:11:39,070 --> 00:11:43,300 And I had to refer the constraints I can put on. 149 00:11:43,300 --> 00:11:46,360 Particularly useful feature is those of keys. 150 00:11:46,360 --> 00:11:51,290 Keys identify important columns in a particular table, and 151 00:11:51,290 --> 00:11:56,370 they will typically be used to identify a column in one table that I want to link 152 00:11:56,370 --> 00:12:01,885 to a column in another table, that will be used for the purpose of doing joins. 153 00:12:03,030 --> 00:12:06,370 A table typically, normally has what's called a primary key. 154 00:12:06,370 --> 00:12:11,890 This is a unique identifier for a row and automatically has to be null. 155 00:12:13,080 --> 00:12:18,950 When I'm creating my table I can identify the column or set of columns that I want 156 00:12:18,950 --> 00:12:23,210 to be used to construct the primary key by using the particular syntax given here. 157 00:12:23,210 --> 00:12:29,640 So in this case I'm saying that, the name, column will be the primary key. 158 00:12:29,640 --> 00:12:34,870 This is automatically a clustered index on this particular table, 159 00:12:34,870 --> 00:12:40,760 it means that when the data is written to disk on the computer, 160 00:12:40,760 --> 00:12:46,590 the DBMS will make sure that sequential records 161 00:12:46,590 --> 00:12:51,200 according to the ordering of the primary key are put next to each other. 162 00:12:51,200 --> 00:12:56,380 So you will always get fastest retrieval from your primary key or 163 00:12:56,380 --> 00:12:58,210 queries against your primary key. 164 00:13:00,600 --> 00:13:03,960 I can associate, two columns in 165 00:13:03,960 --> 00:13:09,349 two different tables with each other by creating of, a foreign key constraint. 166 00:13:11,190 --> 00:13:13,470 And saying this is a, a formal constraint and 167 00:13:13,470 --> 00:13:19,650 that will help for, for doing joints, involving those two tables. 168 00:13:19,650 --> 00:13:23,940 It also means that if I'm putting data into the database, 169 00:13:23,940 --> 00:13:28,870 then I need to make sure that I'm putting the relevant information in. 170 00:13:28,870 --> 00:13:34,010 Because it will check for, it will check keys if they exist between those 171 00:13:34,010 --> 00:13:39,090 tables to make sure those, that values are put in for, for those, for that data. 172 00:13:41,560 --> 00:13:46,730 If I want to see how many tables I have in my relational database, 173 00:13:46,730 --> 00:13:50,140 what the table names are, I do the SHOW TABLES. 174 00:13:50,140 --> 00:13:55,910 If I want to see if I have indexes, or, in my, on a particular table, 175 00:13:55,910 --> 00:13:59,630 which columns may be index, what type of indexes there are on those columns, 176 00:13:59,630 --> 00:14:02,270 I'll do the SHOW INDEXES in, and the table name. 177 00:14:03,420 --> 00:14:06,230 If I've done something wrong, by putting data in, 178 00:14:06,230 --> 00:14:09,950 or, or, an operation and I'm getting a warning message. 179 00:14:09,950 --> 00:14:12,250 I can seal up the warnings on my SHOW WARNINGS. 180 00:14:14,030 --> 00:14:17,880 If I want to, if I am looking a a particular table and 181 00:14:17,880 --> 00:14:23,760 I cannot remember what the structure of the table is, there's this describe and 182 00:14:23,760 --> 00:14:28,690 then the table name, operation which will give me the table structure then, and 183 00:14:28,690 --> 00:14:31,610 I will be able to see what the columns are, and what the column data types are. 184 00:14:34,820 --> 00:14:40,740 If I want to put data into my database, I use the insert keyword insert operation, 185 00:14:40,740 --> 00:14:48,020 so the syntax is insert into table name and then values, and the values. 186 00:14:50,350 --> 00:14:54,200 So if I'm putting all the values for a particular row in the first operation 187 00:14:54,200 --> 00:15:00,030 inserted my table star the values and then the the name the values for 188 00:15:00,030 --> 00:15:07,590 each of the columns in the appropriate order serious RA deck, magnitude. 189 00:15:07,590 --> 00:15:11,020 If I only want to put information in for a certain number of columns, 190 00:15:11,020 --> 00:15:16,930 then I need to specify what those columns are, before the value's key word. 191 00:15:16,930 --> 00:15:20,420 So that second operation chain there Insert into star, and 192 00:15:20,420 --> 00:15:22,020 it's only the name and the v magnitude. 193 00:15:23,560 --> 00:15:27,290 Columns that I'm putting values into in this case it's coming up as m-0.72 will go 194 00:15:27,290 --> 00:15:28,100 into that. 195 00:15:30,030 --> 00:15:33,980 I can populate a table with an insert statement as the result of 196 00:15:33,980 --> 00:15:39,300 having done a select operation on another table or set of tables. 197 00:15:39,300 --> 00:15:41,500 So, there's a query that I'm going to run, 198 00:15:41,500 --> 00:15:43,820 which is doing several joins on several tables. 199 00:15:43,820 --> 00:15:45,860 I write that as a select statement. 200 00:15:45,860 --> 00:15:48,740 And then I can by making sure that the select statement is 201 00:15:48,740 --> 00:15:53,740 returning the correct values, use that as the input into an insert statement and 202 00:15:53,740 --> 00:15:55,990 combine the two and run that as a single operation. 203 00:15:59,750 --> 00:16:03,850 If I want to do a bulk load of data into a table I can, 204 00:16:03,850 --> 00:16:07,380 in my SQL use the load data statement. 205 00:16:07,380 --> 00:16:11,810 And the syntax here is as, as specified and loading data into the file and 206 00:16:11,810 --> 00:16:17,690 then I specify what the path to the file on my hard drive is INTO TABLE 207 00:16:17,690 --> 00:16:21,390 with the tableName, which is being already created with appropriate fields. 208 00:16:21,390 --> 00:16:24,740 FIELDS TERMINATED BY delimiter, that's saying that the fields in 209 00:16:24,740 --> 00:16:29,870 the table that I've got on my hard drive as separated by a particular syntax. 210 00:16:29,870 --> 00:16:34,600 So, in this case, LOAD DATA INFILE, I've got a comma separated variable data file, 211 00:16:34,600 --> 00:16:38,409 INTO TABLE star FIELDS TERMINATED BY commas. 212 00:16:40,070 --> 00:16:42,460 similarly, I can put data out. 213 00:16:42,460 --> 00:16:49,130 I can dump it onto a file in, on my hard drive, select a star enter out file, 214 00:16:49,130 --> 00:16:53,480 fields terminated by columns from star where v magnitude is greater than 16. 215 00:16:53,480 --> 00:16:57,130 So that will select everything from the table star where v magnitude is 216 00:16:57,130 --> 00:17:01,280 greater than 16 and write it into a comma separated variable file on my hard drive. 217 00:17:03,810 --> 00:17:05,070 Finally, if i want to get rid of, 218 00:17:05,070 --> 00:17:10,350 of, information I can do a delete statement and delete from 219 00:17:10,350 --> 00:17:13,775 table name where some condition is specified that where condition. 220 00:17:13,775 --> 00:17:17,790 Again if I want to get rid of all information in the table but 221 00:17:17,790 --> 00:17:22,020 keep the table empty, I would do truncate table, table name. 222 00:17:22,020 --> 00:17:25,940 If I want to completely get rid of the table and all its contents, 223 00:17:25,940 --> 00:17:28,060 then I drop the table tableName. 224 00:17:28,060 --> 00:17:30,840 So, the first example there DELETE FROM star WHERE name is Canopus, 225 00:17:30,840 --> 00:17:33,120 we'll just get rid of that row. 226 00:17:33,120 --> 00:17:37,980 If I want to get rid of all stars where the name begins with the letter C and 227 00:17:37,980 --> 00:17:43,080 there's maybe an N in it, I would do that second syntax. 228 00:17:43,080 --> 00:17:46,020 This is making use of the like construction in the way I predicate. 229 00:17:48,090 --> 00:17:52,640 Or if I want to specify a range, Delete from Star where vmag is greater than 230 00:17:52,640 --> 00:17:57,620 zero or dec is less than zero, brilliant construction or I can use the between 231 00:17:57,620 --> 00:18:01,610 construction to say, I want to get rid of that information from the Star table, 232 00:18:01,610 --> 00:18:06,640 where the v magnitude is between a particular set of constraints. 233 00:18:08,350 --> 00:18:12,490 Finally the UPDATE statement, if I want to update information in my database, 234 00:18:12,490 --> 00:18:14,350 I can do UPDATE tableName. 235 00:18:14,350 --> 00:18:19,220 And then I SET the column value to a value name WHERE condition is, is met. 236 00:18:19,220 --> 00:18:24,020 So upstates are, if the v magnitude is wrong by an offset. 237 00:18:24,020 --> 00:18:28,480 I can do a global update by saying UPDATE start SET v 238 00:18:28,480 --> 00:18:32,520 magnitude equals magnitude plus 0.5. 239 00:18:32,520 --> 00:18:35,980 I can do an individual entry and 240 00:18:35,980 --> 00:18:41,910 kind of change the v magnitude for the row where the star is called serious. 241 00:18:41,910 --> 00:18:45,300 UPDATE star SET vmag is one, minus 1.47. 242 00:18:45,300 --> 00:18:51,370 Where a name like Sirius or I can do an update as the result of a join with 243 00:18:51,370 --> 00:18:56,970 another table to maybe update a column with information from that other table. 244 00:18:56,970 --> 00:19:01,250 This third example, update star, I'm doing inner join and when you're 245 00:19:01,250 --> 00:19:04,619 doing the update statement, to use, you need to use the inner join syntax. 246 00:19:06,090 --> 00:19:10,070 You specify the, the two star, the two table per, 247 00:19:10,070 --> 00:19:12,820 column instead of being used, men say set. 248 00:19:12,820 --> 00:19:19,740 The v magnitude in the star table equal to the magnitude value in the temp table. 249 00:19:19,740 --> 00:19:21,700 And that is the end of this module.