Creating Temporary Tables

Loading...

来自 University of California, Davis 的课程

SQL for Data Science

516 个评分

University of California, Davis

SQL for Data Science

516 个评分

As data collection has increased exponentially, so has the need for people skilled at using and interacting with data; to be able to think critically, and provide insights to make better decisions and optimize their businesses. This is a data scientist, “part mathematician, part computer scientist, and part trend spotter” (SAS Institute, Inc.). According to Glassdoor, being a data scientist is the best job in America; with a median base salary of $110,000 and thousands of job openings at a time. The skills necessary to be a good data scientist include being able to retrieve and work with data, and to do that you need to be well versed in SQL, the standard language for communicating with database systems. This course is designed to give you a primer in the fundamentals of SQL and working with data so that you can begin analyzing it for data science purposes. You will begin to ask the right questions and come up with good answers to deliver valuable insights for your organization. This course starts with the basics and assumes you do not have any knowledge or skills in SQL. It will build on that foundation and gradually have you write both simple and complex queries to help you select data from tables. You'll start to work with different types of data like strings and numbers and discuss methods to filter and pare down your results. You will create new tables and be able to move data into them. You will learn common operators and how to combine the data. You will use case statements and concepts like data governance and profiling. You will discuss topics on data, and practice using real-world programming assignments. You will interpret the structure, meaning, and relationships in source data and use SQL as a professional to shape your data for targeted analysis purposes. Although we do not have any specific prerequisites or software requirements to take this course, a simple text editor is recommended for the final project. So what are you waiting for? This is your first step in landing a job in the best occupation in the US and soon the world!

从本节课中

Getting Started and Selecting & Retrieving Data with SQL

In this module, you will be able to define SQL and discuss how SQL differs from other computer languages. You will be able to compare and contrast the roles of a database administrator and a data scientist, and explain the differences between one-to-one, one-to-many, and many-to-many relationships with databases. You will be able to use the SELECT statement and talk about some basic syntax rules. You will be able to add comments in your code and synthesize its importance.

Module Introduction1:00

What is SQL Anyway?7:26

Data Models, Part 1: Thinking About Your Data5:37

Data Models, Part 2: The Evolution of Data Models3:47

Data Models, Part 3: Relational vs. Transactional Models5:44

Retrieving Data with a SELECT Statement4:56

Creating Tables7:42

Creating Temporary Tables4:18

Adding Comments to SQL5:12

与讲师见面

Sadie St. Lawrence
Data Scientist at VSP Global
Founder and Executive Director, Women in Data (WID)

So in our last lesson, we went over how to create tables in a database using SQL.

Another option we have is to create a copy, essentially of another table or

pull a subset from another table.

We can create a whole table from this,

or we can create what is called a temporary table.

And that's what we're going to go over in this video.

After this lesson, you should be able to create temporary tables,

describe the limitations of temporary tables and discuss some strategies for

researching syntax for particular database management systems.

First of all, the most important thing to know about these temporary tables is

that these will be deleted when the current client session is terminated.

That's why they're called temporary tables.

However, these are really good to use because they are lot faster than creating

a real table.

So if you have complex queries and you want to simplify it a bit by

creating a subset and then joining to that subset and

driving a new calculation from that, then temporary tables are a great option.

For this, we use the statement create temporary table.

Then we put the name of the temporary table we want to create.

Then this I'm pulling in as a subset from another table.

I'm going to say add, then in my brackets,

I'm going to put my select statement of where I want to get this from.

In this case, I'm pulling a subset of data from my shoes table.

Right here I am just creating a temporary table that is the shoe type sandals.

Now I create an individual table which is just going to have the shoes that

are sandals.

As mentioned before, the way you create tables, update them and insert them is

heavily dependent on the relational database management system you're using.

In this class we're using SQLite.

However, you do not have write privileges to this.

You won't be able to write any data to this.

But I think it's important to realize you need to look up this information

on your own based on the company you're at, and

the type of relational database management system they're using at that time.

This graph depicts the flow of what you need,

in order to create a temporary table.

So as you can see, there's a little bit more detail here.

What I would highly recommend is looking up the Relational Database Management

System you're using, and then finding the syntax detail for how to create the Table,

with the Relational Database Management System you're using.

It's really great if you can become your own researcher and

trouble shooter when working with SQL.

The key is to really never stop learning, learning how to optimize your queries or

how to work with relational database management systems.

For this, I really want to teach you the core basics and the fundamentals so

that you would know how to think logically about these problems and

work through them.

At the end of the day, you're going to be your own best teacher.

For this, I recommend googling or using stackoverflow,

which is a site dedicated to asking and answering coding questions.

You'll be able to get a lot of great information there.

As this language is dependent on the database management system you're using,

make sure that when you're typing or researching these items,

that you're typing the name of the database management system in as well.

This will be helpful because you may find the solution to your problem implemented.

There may be different syntax based on the relational database management system

your using.

I'd like to challenge you to research some of this things on your own based on

the relational database management system you're using at work.

Go ahead and look up the statements for updating tables and deleting tables and

see if there's anything related to the database management system you're using.

So to wrap up, in this lesson we learned how to create temporary tables and

write data to them.

We also covered some limitations of using temporary tables.

Chiefly, that they are temporary, and we talked about

some strategies to research different syntax for SQL statements.

I really encourage you to do some research in this regard.

You will always learn a lot when you go out and explore these topics on your own.

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2018 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取