课程

Writing Functions and Stored Procedures in SQL Server

中级技能水平

更新时间 2024年11月

Master SQL Server programming by learning to create, update, and execute functions and stored procedures.

免费开始课程

SQLProgramming

4 小时

16 视频

57 练习

4,700 经验值

27,223

成就声明

深受数千家公司学习者的喜爱

Training a Team?

Try for Business

课程描述

Take your SQL Server programming to the next level. First, we demystify how to manipulate datetime data by performing temporal exploratory data analysis with the Washington DC BikeShare transactional dataset. Then, you’ll master how to create, update, and execute user-defined functions and stored procedures. You will learn the proper context for each modular programming tool and best practices. In the final chapter, you will apply all of your new skills to solve a real-world business case identifying the New York City yellow taxi utilization for each borough, and which pickup locations should be scheduled for each driver shift.

先决条件

Functions for Manipulating Data in SQL Server

1

Temporal EDA, Variables & Date Manipulation

Learn how to do effective exploratory data analysis on temporal data, create scalar and table variables to store data, and learn how to execute date manipulation. This chapter will also cover the following SQL functions: DATEDIFF( ), DATENAME( ), DATEPART( ), CAST( ), CONVERT( ), GETDATE( ), and DATEADD( ).

Introduction to the course

50 经验值

Transactions per day

100 经验值

Seconds or no seconds?

100 经验值

Which day of week is busiest?

100 经验值

Find the outliers

100 经验值

Variables for datetime data

50 经验值

DECLARE & CAST

100 经验值

DECLARE a TABLE

100 经验值

INSERT INTO @TABLE

100 经验值

Date manipulation

50 经验值

Parameters matter with DATEDIFF

50 经验值

First day of month

100 经验值

2

User Defined Functions

This chapter will explain how to create, update, and execute user-defined functions (UDFs). You will learn about the various types of UDFs: scalar, inline, and multi-statement table-valued. You’ll also learn best practices.

Scalar user defined functions

50 经验值

What was yesterday?

100 经验值

One in one out

100 经验值

Multiple inputs one output

100 经验值

Table valued UDFs

50 经验值

100 经验值

Multi statement TVF

100 经验值

UDFs in action

50 经验值

Execute scalar with select

100 经验值

EXEC scalar

100 经验值

Execute TVF into variable

100 经验值

Maintaining user defined functions

50 经验值

CREATE OR ALTER

100 经验值

Best practices

50 经验值

3

Stored Procedures

Learn how to create, update, and execute stored procedures. Investigate the differences between stored procedures and user defined functions, including appropriate scenarios for each.

Stored procedures

50 经验值

CREATE PROCEDURE with OUTPUT

100 经验值

Output parameters vs. Return values

50 经验值

50 经验值

Use SP to INSERT

100 经验值

Use SP to UPDATE

100 经验值

Use SP to DELETE

100 经验值

Let's EXEC!

50 经验值

EXECUTE with OUTPUT parameter

100 经验值

EXECUTE with return value

100 经验值

EXECUTE with OUTPUT & return value

100 经验值

TRY & CATCH those errors!

50 经验值

Your very own TRY..CATCH

100 经验值

CATCH an error

100 经验值

4

NYC Taxi Ride Case Study

Apply your new skills in temporal EDA, user-defined functions, and stored procedures to solve a business case problem. Analyze the New York City taxi ride dataset to identify average fare per distance, ride count, and total ride time for each borough on each day of the week. And which pickup locations within the borough should be scheduled for each driver shift?

Case study EDA & imputation

50 经验值

Use EDA to find impossible scenarios

100 经验值

SPs vs UDFs

50 经验值

Mean imputation

100 经验值

Hot Deck imputation

100 经验值

Case study UDFs

50 经验值

CREATE FUNCTIONs

100 经验值

Test FUNCTIONs

100 经验值

Formatting tools

50 经验值

Logical weekdays with Hot Deck

100 经验值

Format for Germany

100 经验值

Case study stored procedures

50 经验值

NYC Borough statistics SP

100 经验值

NYC Borough statistics results

100 经验值

Pickup locations by shift

100 经验值

Pickup locations by shift results

100 经验值

Congratulations!

50 经验值

Writing Functions and Stored Procedures in SQL Server

课程完成

获得成就证明

将此证书添加到你的 LinkedIn 档案、简历或履历中
在社交媒体和绩效评估中分享立即注册

加入超过19百万学习者，今天就开始Writing Functions and Stored Procedures in SQL Server！

通过 DataCamp for Mobile 提升您的数据技能

随时随地通过我们的移动课程和每日 5 分钟编程挑战提升技能。