1 00:00:07,060 --> 00:00:09,720 Hello, and welcome to the CREATE TABLE Statement. 2 00:00:10,420 --> 00:00:14,160 In this video, we will learn how to create a relational database table. 3 00:00:14,700 --> 00:00:15,620 At the end of the video, 4 00:00:15,620 --> 00:00:16,940 you will be able to distinguish between 5 00:00:17,440 --> 00:00:22,660 data definition language statements and data manipulation language statements, 6 00:00:22,660 --> 00:00:25,880 and explain how the entity name and attributes 7 00:00:25,880 --> 00:00:29,200 are used to create a relational database table. 8 00:00:31,120 --> 00:00:35,740 SQL Statements are used for interacting with Entities (that is Tables), 9 00:00:35,880 --> 00:00:44,160 Attributes (that is Columns) and their tuples (or rows with data values) in relational databases. 10 00:00:44,900 --> 00:00:48,160 SQL statements fall into two different categories: 11 00:00:48,280 --> 00:00:53,500 Data Definition Language statements, and Data Manipulation Language statements. 12 00:00:53,500 --> 00:01:02,640 Data Definition Language (or DDL) statements are used to define, change, or drop database objects such as tables. 13 00:01:03,200 --> 00:01:08,800 Common DDL statement types include: CREATE, ALTER, TRUNCATE and DROP. 14 00:01:09,520 --> 00:01:14,000 CREATE: which is used for creating tables and defining its columns 15 00:01:14,500 --> 00:01:21,840 ALTER: is used for altering tables including adding and dropping columns and modifying their datatypes 16 00:01:22,280 --> 00:01:27,400 TRUNCATE: is used for deleting data in a table but not the table itself 17 00:01:28,120 --> 00:01:31,160 DROP: is used for deleting tables 18 00:01:32,060 --> 00:01:38,640 Data Manipulation Language (or DML) statements are used to read and modify data in tables. 19 00:01:38,900 --> 00:01:43,060 These are also sometimes referred to as CRUD operations, 20 00:01:43,280 --> 00:01:47,980 that is, Create, Read, Update and Delete rows in a table. 21 00:01:48,700 --> 00:01:54,140 Common DML statement types include: INSERT, SELECT, UPDATE and DELETE . 22 00:01:54,420 --> 00:02:00,300 INSERT: is used for inserting a row or several rows of data into a table 23 00:02:00,660 --> 00:02:05,300 SELECT: reads or selects row or rows from a table 24 00:02:05,760 --> 00:02:09,220 UPDATE: edits row or rows in a table 25 00:02:09,220 --> 00:02:13,640 And DELETE: removes a row or rows of data from a table 26 00:02:14,180 --> 00:02:17,880 Now let’s look at the most common DDL statement – CREATE. 27 00:02:18,340 --> 00:02:21,600 The syntax of the CREATE table is shown here: 28 00:02:21,960 --> 00:02:27,580 You start with “CREATE TABLE” followed by the name of the table you want to create 29 00:02:27,920 --> 00:02:33,660 Then enclose rest of the statement inside a pair of parenthesis or round brackets. 30 00:02:34,000 --> 00:02:40,520 Each row inside the parenthesis specifies the name of a column followed by its datatype 31 00:02:40,520 --> 00:02:45,060 and possibly some additional optional values that we will see later. 32 00:02:45,580 --> 00:02:50,220 Each attribute or column definition is separated by a comma. 33 00:02:51,100 --> 00:02:56,900 For example, if we want to create a table for provinces in Canada you would specify: 34 00:02:56,900 --> 00:03:10,260 CREATE TABLE provinces (id char(2) Primary key not null comma name varchar(24) close parenthesis 35 00:03:10,740 --> 00:03:18,640 In this example, the data types used are: CHAR which is a character string of a fixed length, in this case 2. 36 00:03:18,820 --> 00:03:22,720 And VARCHAR, which is a character string of a variable length. 37 00:03:23,120 --> 00:03:28,140 In this case, this variable character field can be up to 24 characters long. 38 00:03:28,900 --> 00:03:33,780 Issuing this statement would create a table in the database with 2 columns. 39 00:03:33,780 --> 00:03:42,900 The first column id for storing the abbreviated 2 letter province short codes such as AB , BC, etc. 40 00:03:43,180 --> 00:03:51,360 And the second column called name for storing the full name of the province, such as ALBERTA, BRITISH COLUMBIA, etc. 41 00:03:51,840 --> 00:03:56,440 Now, let’s look at a more elaborate example based on the Library database. 42 00:03:56,740 --> 00:04:02,500 This database includes several entities such as AUTHOR, BOOK, BORROWER, etc. 43 00:04:02,820 --> 00:04:06,060 Let’s start by creating the table for the AUTHOR entity. 44 00:04:06,780 --> 00:04:10,160 The name of the table will be AUTHOR, and its attributes 45 00:04:10,320 --> 00:04:17,160 such as AUTHOR_ID, FIRSTNAME, LASTNAME, etc. will be the columns of the table. 46 00:04:17,560 --> 00:04:23,640 In this table, we will also assign the Author_ID attribute as the Primary Key, 47 00:04:23,640 --> 00:04:26,500 so that no duplicate values can exist. 48 00:04:26,880 --> 00:04:34,540 Recall, the Primary Key of a relational table uniquely identifies each tuple (or row) in a table. 49 00:04:35,440 --> 00:04:39,760 To create the Author table, issue the following command: 50 00:04:39,960 --> 00:04:53,840 CREATE TABLE author ( author_id CHAR(2) PRIMARY KEY NOT NULL, lastname VARCHAR(15) NOT NULL, 51 00:04:54,040 --> 00:05:10,020 firstname VARCHAR(15) NOT NULL, email VARCHAR(40), city VARCHAR(15), country CHAR(2) ) 52 00:05:11,960 --> 00:05:15,840 Note that the Author_ID is the Primary Key. 53 00:05:15,840 --> 00:05:19,760 This constraint prevents duplicate values in the table. 54 00:05:20,300 --> 00:05:25,420 Also note that Last Name and First Name have the constraint NOT NULL. 55 00:05:25,420 --> 00:05:32,420 This ensures that these fields cannot contain a NULL value, since an author must have a name. 56 00:05:33,080 --> 00:05:34,620 Now you know that: 57 00:05:34,620 --> 00:05:39,840 DDL or Data Definition Language statements are used for defining or changing objects 58 00:05:39,920 --> 00:05:42,800 in a database such as tables. 59 00:05:42,800 --> 00:05:50,540 DML or Data Manipulation Language statements are used for manipulating or working with data in tables. 60 00:05:50,880 --> 00:05:56,320 CREATE is a DDL statement for creating Entities or tables in a database. 61 00:05:56,740 --> 00:06:03,120 The CREATE TABLE statement includes definition of attributes of columns in the table, including 62 00:06:03,120 --> 00:06:05,040 Names of columns 63 00:06:05,040 --> 00:06:06,620 Datatypes of columns 64 00:06:06,740 --> 00:06:11,880 And other Optional values if required such as the Primary Key constraint 65 00:06:12,440 --> 00:06:14,440 Thanks for watching this video.