Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Creating and Modifying Database Tables

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Data Types in SQLite

4:40 with Ben Deitch

In this video we'll learn more about data types in SQLite. We'll also learn how to drop tables and how to add a primary key to a column!

INSERT INTO CONCERTS VALUES (1, '2018-02-15', 'Nashville', 'TN', 'Nissan Stadium'); INSERT INTO CONCERTS VALUES (2, '2018-02-20', 'Indianapolis', 'IN', 'Lucas Oil Stadium'); INSERT INTO CONCERTS VALUES (3, '2018-02-23', 'Chicago', 'IL', 'Soldier Field'); INSERT INTO CONCERTS VALUES (4, '2018-02-26', 'Columbus', 'OH', 'Ohio Stadium'); INSERT INTO CONCERTS VALUES (5, '2018-03-01', 'Philadelphia', 'PA', 'Lincoln Financial Field'); INSERT INTO CONCERTS VALUES (6, '2018-03-04', 'Houston', 'TX', 'NRG Stadium'); INSERT INTO CONCERTS VALUES (7, '2018-03-09', 'Pasadena', 'CA', 'Rose Bowl'); INSERT INTO CONCERTS VALUES (8, '2018-03-15', 'Portland', 'OR', 'Moda Center'); INSERT INTO CONCERTS VALUES (9, '2018-03-19', 'Seattle', 'WA', 'CenturyLink Field');

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    We just tried to recreate our CONCERTS table with our new data types, but 0:00
    it turns out you can't create a table that already exists. 0:04
    To fix this, we just need to remove the table before we try and recreate it. 0:09
    Which in SQL, is called dropping the table. 0:14
    Let's add a line above our create statement and 0:18
    then type drop TABLE CONCERTS. 0:21
    And don't forget the semicolon, now if we run these it will first drop the table, 0:26
    removing it from the data base and then add it back in with the create statement. 0:33
    Lets click run and then let's check out the CONCERTS table. 0:39
    Notice that this time, our columns have data types. 0:46
    Awesome, right? 0:50
    Let's try inserting some values. 0:52
    Back in our query, below our CREATE TABLE statement, let's add an insert statement. 0:54
    Let's type INSERT INTO and 1:01
    then the name of the table, which is CONCERTS, followed by VALUES, 1:06
    and then in parenthesis, values for each of the columns. 1:13
    Let's use 1 for our ID. 1:18
    And for the date, remember we need to pass it in as a string using the right format. 1:20
    So just to make something up, let's go with New Year's Day of 2018. 1:26
    2018-01-01. 1:32
    Finally, since the rest of columns are varchars, 1:39
    let's just add three short strings like A, 1:41
    B, and C and then, finish with a semicolon. 1:47
    Then, let's run it again and check out the CONCERTS table. 1:52
    Looks good, but let's try it one more time with some more extreme values. 1:58
    For the ID, let's pass in 100,000 instead of 1. 2:04
    Remember that small INT only goes up to about 32,000. 2:09
    So this number should be too big, but 2:13
    if we run it, it gets inserted just fine. 2:17
    Remember how I mentioned that SQLite doesn't really use data types, 2:23
    that's what's going on here. 2:27
    In SQL Server these data types would mean something, but here in SQLite 2:29
    they're basically just documentation and don't have any functional purpose. 2:34
    In fact, in SQLite if you want to create a column with the type of happy birthday, 2:39
    you'll get a column with the happy birthday data type. 2:47
    However, even though SQLite doesn't use data types, most SQL implementations do. 2:51
    To make sure you are well prepared we'llcontinue 2:58
    to use the data types from SQL Server. 3:00
    Starting by changing happy birthday back to small SMALLINT, awesome. 3:03
    It may seem weird to allow data types and then not do anything with them. 3:11
    But it's really a compatibility feature letting you reuse code 3:15
    between different types of SQL. 3:19
    If you've gotta CREATE statement from another version of DSQL, 3:21
    regardless of what data types it uses, SQLite will create the table just fine. 3:25
    Now before we move on to the other two tables, 3:30
    there's one more thing we need to do with the concert's table. 3:33
    For each column, after you specified a data type, you can also add a constraint. 3:37
    One of the most common constraints is to mark a column as a primary key. 3:43
    Letting SQL know that each value in this column uniquely identifies a record. 3:48
    To add a primary key constraint to the ID column, 3:53
    let's add a space after SMALLINT and then type PRIMARY KEY. 3:56
    Then to make sure it works, 4:02
    let's copy our INSERT statement and paste it on the next line. 4:04
    And if we run it, perfect! 4:11
    It won't let us insert a duplicate ID. 4:15
    That should do it for the CONCERTS table, for 4:18
    our last step, let's add some more realistic data. 4:21
    Let's delete the INSERT statements. 4:24
    And then copy and paste in some new ones from the teacher's notes below. 4:29
    Then let's run it. 4:33
    And now we're ready to start creating the ticket-holders table. 4:36

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up