How to Create Tables in SQLite

ACCESS the FULL COURSE here: https://academy.zenva.com/product/data-science-mini-degree/?zva_src=youtube-datascience-md

TRANSCRIPT

So in this video What we’re going to do is learn how we can create our own data structure to go inside of a SQLite database, so far we’ve only looked at an existing database and can run all kinds of queries and select, select statement against a dataset that has already existed in the columns, tables are all defined. All the tables themselves are also defined. But now we are gonna look at how we can create our own database, create our own tables, and whatnot. So we are gonna be looking at how we can do that. And so in this video particularly, we are gonna learn how we can create tables, what is the syntax, what are the different data types, and what are some other kind of idems, what are some other kind of actuaries so they can assign to columns. So you will notice that I don’t have the terminal yet. That’s because I just have a text editor here the one that I am using is called Visual Studio Code. You can install it as part of the Anoconda but really any text editor will work. So I already have the file created called Create-tables.sql. And it’s in the same folder as, it’s just in a particular folder, senquote as everything else, but we’re not gonna run against chinook.db we’re gonna create our own database and then run it as well, so we’ll see how to do that. So the first thing that we’re gonna do is create a table and we’re gonna create a table called student, and we’re gonna give it some columns and then we are gonna call columns sunlight attribute. Create a table of students and by the way to do comments in SQL it’s just either two hyphens or you can do this forward slash star and then star forward slash it’s called c style comments and these are multi line. Anyway we’ll just create table student so syntax to create a table is just create table and then the name of the table and I have to do this and I’m gonna split the side across multiple lines because it’s easier to read.

There’s one other thing that we can do that I usually like to do when we’re creating new data structures and in the case you’re gonna run this code over and over again if you’re doing any kind of debugging, is what you can do is say create table, if not exists, and what adding this if not exists clause, does exactly what you think it does. It will create this table student unless there already is a table called student, inside a database, in which case the statements the entire statements is ignored. And we’ll just ignore it. Okay, so I’ve just had that there just as a syntax feature that we can look at. So now we need to add columns to our database. And remember earlier when I mentioned that databases are highly structure data, well that means that all the columns have a datatypes associated with them. So we are gonna discuss some of the, we are gonna discuss three of the main datatypes. There’s a fourth one but we’re not gonna get into that one.

Okay, so let’s, before we get into the datatypes let’s first think about what kind of things you would need if you were managing suppose, like a university database system. So, we would need students, and one thing I’m gonna leave at the end is a challenge for you guys to write the create table statement for professors. We’ll say create table for students so we’ll need to give each of the students a unique student ID. So we’ll create a column called student id. Okay, and this is, we’re gonna add the datatypes later, but student ID is good thing to start with. And we’ll do first name, we’ll have to do names alright so, first name, middle name, if they have one, and then last name. And then some other information that might be useful to know is GPA, and email, their university email that is. So each student when you enroll will get assigned student id and then a unique university email. So, it’ll be easier to find the columns, so these are the columns that we’ll define here. So for student id, there are a couple of options that we can do for this one, but one of the most straightforward options to do for student id is to just say give it an integer, and sequentially as new students join in you assignment them the next available number. So we can do that. So one of the data types is integer, and it’s just exactly what you think it is, like in any other programming language, it’s just an integer, and then note that size of this integer might be however many bytes is required for this particular column. Okay, so that’s one of the data types, integer.