1. Aprender
    2. /
  2. Cursos
    3. /
  3. Writing Functions and Stored Procedures in SQL Server
  • 1

    Temporal EDA, Variables & Date Manipulation

    Gratuito

    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( ).

  • 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.

  • 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.

  • 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?

Initializing

Exercicio

CREATE FUNCTIONs

Create three functions to help solve the business case:

  1. Convert distance from miles to kilometers.
  2. Convert currency based on exchange rate parameter.
    (These two functions should return a numeric value with precision of 18 and 2 decimal places.)
  3. Identify the driver shift based on the hour parameter value passed.

Instruções 1/3

undefined XP
  • 1

    Use CREATE FUNCTION to accept @Miles input parameter & return the distance converted to kilometers.

  • 2

    Create a function which accepts @DollarAmt and @ExchangeRate input parameters, multiplies them, and returns the result.

  • 3

    Create a function that returns the shift as an integer: 1st shift is 12am to 9am, 2nd is 9am to 5pm, 3rd is 5pm to 12am.