Reading and Creating Tables for Data Analysis in SQL

Name: Reading and Creating Tables for Data Analysis in SQL
Author: Eric Grose

Discover how to create and read tables with performant queries in SQL

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

Course: On-Demand

Hello, my name is Eric, and in these lessons you'll learn about reading, creating, and analyzing tables for data analysis in SQL. In this lesson, I'll show you how to create data tables in SQL. Create a new table, use the following syntax. You will use the create table statement, and replace the table name with whatever you'd like to call it. You will also replace columns 1, 2, and 3, the names you want to give your columns. Again, you'll replace data type with the associated type restored for each column. Some common data types are known as integers, bar char, and date data types. You can add as many or as little columns as you need for your table just by separating each one of them with a comma, and then the last one of your columns does not have a comma. Once you have defined all your columns, go ahead and close it off with another parenthesis, as well as a semicolon. Now what we'll see here is a real world example. If we were creating a table called products, that has a column skew, which is an integer, a description, which is bar char, and again we're giving it a character limit, as well as product cost as integer. Now it's important to know this syntax, but thankfully PHP, my admin, has an easier way to build tables using the graphical user interface. So I'm going to go ahead and click under my tables here in my test database, and you should see an icon here for new to create a new table. I go ahead and hit OK here, and as you can see it has the same structure, but in a graphical user interface. Let's go ahead and call this products, our first column with a skew, and we'll leave that as integer. Our next column was product description, and we will change that to var char for text values, and we'll give that the maximum length available of 255, as well as we'll input product cost, and leave that as an integer as well. Rather than having to write out all that syntax, we can just go ahead and use the tool itself. Saving you a lot of time. We can use the preview SQL here to see exactly as we wrote it, with a little bit more syntax in there. So go ahead and hit save. Now we'll see our products table. Now that you'll have a table, you'll want to insert some rows into your table. To insert rows into a table, use the following syntax. We'll use the insert into statement. We'll define the table of where these rows are going into, and we'll list the columns within that table, as well as the values. So if we're going to insert something into our products table, we'll need to define the column. So skew, product description, and product cost, and we want to add the value of 111, a jacket, and a cost of $30. But thankfully, again, we also have the ability to insert these in a much easier way. So I clicked on my tables here, I see products, and I have the insert ability here. So using the graphical user interface, I can insert that same row 111, product description here of jacket, and a product cost of $30, and simply by hitting the go button here. Okay? We can see that one row has been inserted, and if we go to our products table, we see that row is successfully inputted as expected. Thanks for watching. Stay tuned for the next lesson where I show you how to read data tables in SQL.

Intermediate Provider : Eric Grose 3 Lessons · 9m in English

Course Description

The language of SQL branches into two subjects: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is synonymous with creating tables, and DML is also known as reading tables using SQL. An analyst must first create tables before they can analyze them, so it’s vitally important to understand DDL. Thankfully, many tools allow you to create tables and perform other DDL operations using the User Interface (UI). However, it’s a core competency in SQL to understand how these tables are created.

By the end of these lessons, you will be able to analyze table contents using DML. This is known as manipulation because you are not altering the data; rather, you are constructing the data to fit the question you are trying to answer. You’ll also learn about DML’s ability to include or exclude certain columns, derive custom columns, order your data based on specific criteria, filter your data to match a particular pattern, and more.

What You'll Learn

Write proper DDL syntax to create tables in SQL
Use the User Interface (UI) to create tables
Apply DML to read and analyze table contents in SQL
Include or exclude columns and derive custom columns in queries
Order data based on specific criteria and filter it to match a particular pattern
Write performant queries for data analysis using SQL

Key Takeaways

SQL branches into two subjects: Data Definition Language (DDL) for creating tables and Data Manipulation Language (DML) for reading tables.
An analyst must first create tables with DDL before they can analyze them, making DDL a vital core competency.
Many tools let you create tables and perform DDL operations through the User Interface, but understanding how tables are created remains a core SQL skill.
DML is called manipulation because you construct the data to fit the question you are answering rather than altering the underlying data.
DML lets you include or exclude columns, derive custom columns, order data by specific criteria, and filter data to match a particular pattern.

Frequently Asked Questions

What will I be able to do by the end of this course?

By the end of the lessons you will be able to analyze table contents using DML, write proper syntax to create tables with DDL, use the User Interface to create tables, and write performant queries for data analysis using SQL.

What is the difference between DDL and DML in this course?

DDL (Data Definition Language) is synonymous with creating tables, while DML (Data Manipulation Language) is known as reading tables using SQL. DML is called manipulation because you construct the data to fit your question rather than altering it.

What topics do the lessons cover?

The course includes three lessons: Creating Data Tables in SQL, Reading Data Tables in SQL, and Analyzing Data Tables in SQL.

What skills will I gain from this course?

The course builds skills in Data Analysis, Data Literacy, Data Manipulation, Data Modeling, Database Analysis, and working with Tabular Data.