Tables

Name: Tables
Author: Daniel Hanks

Explore the use of tables and how to work with them

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

In this is lesson we're gonna talk about tables. We've touched on tables already and defined them but we're gonna dive into a more in depth look at them, though. So as you can see I pulled up the documentation from Microsoft's website. So this page is everything to do about tables and it gives you your definition, our different types of tables, and it also gets into the structural elements of tables. Where it states here, you can have 1024 columns, but the number of rows is actually limited only by the storage capacity of your server. So it's an interesting fact to know. Now, if we scroll down to the bottom here you'll get common table tasks, and this is a great resource because it just goes over everything that you'd ever wanna do with a table. Creating, deleting, renaming, gets into column tasks as well. So if I go ahead and click on create tables, this will go into the table designer for SSMS, which we've already used once. And you can just follow the path that it gives you when you're creating a table. So if you start creating columns and you have questions, going off this instructions you can go right into the table column properties and you'll learn about these data types and column names. And also, we talked about creating relationships between two tables and creating keys. And this tells you how to create relationships right in the table and create primary primary keys right there. I've switched over to my management studio here and here's the customer database we already built. And so I'm actually gonna make two tables because I want it to match what we had before, with the customer table and the TV programming table. In order to do that, I'm gonna keep this. I could easily just create a new one but I'm gonna show you this as a script table as, like we looked at before, and we're gonna do a drop and create. Simply because you can't just rename a column after you've already created the table in the schema. That's why it's so important to know what fields you're gonna use ahead of time and know what kind of structure you want to use. List out your keys. You want all that documented before you build it because you don't wanna have to come in here and blow away your tables all the time, 'cause then you blow away your data as well. So luckily we don't have very much data going on right now. But if we look at this script, it's just saying use the customer table, which easy enough. Then this is a simple if statement to test if the table exists and we know it exists, so that's not gonna be an issue. And if it does exist it's telling it to drop that table or delete it. And then once it deletes it, it comes down and it creates a new table and it's building the same exact table but we're gonna modify that a little bit. And we're gonna use that ID so we can link up our two tables like we did before, in our establishing relationships lesson. So let's go ahead and execute that. Completed successfully. Let me close this out. We don't need to save this. I'm gonna refresh this. Look at our columns here and you can see we now have TV ID there. So if we go, we'll see we have no data anymore, which is fine. So now we have the customer table. So we need a TV programming table, I believe is what we called it. We can do it script as, but we can also just right click on tables and create a table this way. And we'll name our column name E. This is where we get in those integer types. And integer is fine for an ID. I'm not gonna allow nulls because we don't. We always want an ID. We want an actual number to always be there. And then title, I could just use VARCHAR for that. We'll just do same thing for type and that'll be what type of programming it is. We're gonna allow nulls on those. And we're gonna go over here and save this table. I'll just name it TV programming. Close it and refresh. And we have our two tables now. So that's gonna do it for this lesson.

Beginner Provider : Daniel Hanks 4 Lessons · 17m in Arabic, German, English, Spanish, French, Portuguese, Urdu, Chinese

Course Description

In this course, you’ll explore and define tables. You’ll learn about the structural elements of tables and data types. You’ll also learn about primary and foreign keys. You’ll have the opportunity to create multiple tables and then create data types for them.

You’ll also work with common examples of data types such as integers, characters and date and time. You’ll then get to establish primary and foreign keys to create relationships and enforce data integrity between tables. Finally, you’ll learn how to import a table from external sources, such as spreadsheets, text files, or other database formats.

What You'll Learn

Understand table structures and the relationships between tables
Create a table and set appropriate data types
Work with common data types such as integers, characters, and date and time
Establish primary and foreign keys to create relationships and enforce data integrity
Import tables from external sources such as spreadsheets, text files, or other database formats

Key Takeaways

Tables are defined by structural elements and data types that organize how information is stored.
Data types such as integers, characters, and date and time determine the kind of values a column can hold.
Primary and foreign keys create relationships between tables and enforce data integrity.
Tables can be imported from external sources, including spreadsheets, text files, and other database formats.

Frequently Asked Questions

What will I learn in this course?

You will explore and define tables, learn about table structural elements and data types, understand primary and foreign keys, and learn how to import tables from external sources such as spreadsheets, text files, or other database formats.

What topics are covered in the lessons?

The course covers Table Structures and Relationships, Create a Table and Set Data Types, Primary and Foreign Keys, and Importing Tables.

What data types does the course cover?

It covers common examples of data types such as integers, characters, and date and time.

Will I get hands-on practice?

Yes. You will have the opportunity to create multiple tables, set data types for them, and establish primary and foreign keys to create relationships and enforce data integrity between tables.

What skills does this course help build?

It helps build skills in database modeling, database programming, relational databases, foreign keys, external tables, and linked tables.