EXERCISE TOPIC▼
- Access exercises (91)
- C# exercises (79)
- Excel exercises (278)
- Power Apps exercises (13)
- Power Automate exercises (18)
- Power BI exercises (139)
- Power Platform exercises ()
- Python exercises (28)
- Report Builder exercises (141)
- SQL exercises (198)
- SSAS exercises (51)
- SSIS exercises (46)
- SSRS exercises (99)
- VBA exercises (85)
- Visual Basic exercises (46)
SQL EXERCISES▼
SQL EXERCISES▼
- Simple Queries (4)
- Setting criteria using WHERE (5)
- Calculations (7)
- Calculations using dates (4)
- Basic joins (8)
- More exotic joins (2)
- Aggregation and grouping (8)
- Views (5)
- Subqueries (5)
- Stored procedures (5)
- Variables (8)
- Parameters and return values (11)
- Testing conditions (1)
- Looping (3)
- Scalar functions (6)
- Transactions (5)
- Creating tables (5)
- Temporary tables and table variables (9)
- Table-valued functions (6)
- Derived tables and CTEs (13)
- Dynamic SQL (4)
- Pivots (2)
- Triggers (2)
- Archived (70)
SQL | Scalar functions exercise | Create a function to show the reign of the various Doctors
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
Software ==> | SQL (198 exercises) |
Version ==> | Any version of SQL Server |
Topic ==> | Scalar functions (6 exercises) |
Level ==> | Relatively easy |
Subject ==> | SQL training |
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
First (if you haven't already done so) run the script shown above to generate the Doctor Who training database.
This exercise uses the DateDiff function, which calculates the difference between two dates in days, months, years, etc, and takes 3 arguments:
Argument | What to put |
---|---|
Interval | The time period to use (use day for this exercise) |
Start date | The start date |
End date | The end date |
For example, the following would give 365 as the answer:
-- this would give 365 as the difference
SELECT DateDiff(day,'20150101','20160101')
Write a function called fnReign which uses the DateDiff function to return the number of days between any two given dates. Here's a quick reminder of scalar function syntax:
CREATE FUNCTION NameOfFunction(
@Parameter1 datatype,
...
@Parametern datatype,
)
RETURNS datatype
AS
BEGIN
RETURN something
END
Call your squeaky clean new function within a query to show the relative duration of each doctor's "reign":

If the end date is null for a doctor your function should substitute the current date. This may mean that Peter Capaldi appears higher up the list when you run your query than is shown here (since he is the only doctor for whom the last episode date is null).
Optionally, save your query as Baker wins.sql, then close it down.
One possible answer:
CREATE FUNCTION fnRegion(@FirstDate DATE,@LastDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @Date_Diff INT
SELECT @Date_Diff=DateDIff(day,@FirstDate,ISNULL(@LastDate,Getdate()))
RETURN @Date_Diff
END
***************************************************************************************************************************
select DoctorName,dbo.fnRegion(FirstEpisodeDate,LastEpisodeDate)[Reign in days] from tblDoctor
****************************************************************************************************************************
We don't normally post answers (apart from anything else, it removes the incentive to solve the exercise), but I'll make an exception for this!