Monthly Archives: June 2015

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 4

Posted on June 18, 2015 by Robert

SELECT 'Average' = AVG(quantity)
 ,'Average #2' = AVG(CAST(quantity as decimal(5,2)))
 ,'Number' = COUNT(*)
 ,'Maximum' = MAX(quantity)
 ,'Minimum' = MIN(quantity)
 ,'Total' = SUM(quantity)
FROM Orderlines;

SELECT OrderDate
 ,'Payment due' = DATEADD(m,1,OrderDate)
 ,'Days ago' = DATEDIFF(dd,OrderDate,GETDATE())
 ,'Today' = GETDATE()
FROM Orders;

SELECT 'Left 3' = LEFT('ABCDE', 3)
 ,'Length' = LEN('ABCDE')
 ,'Lower case' = LOWER('Abcde')
 ,'No leading spaces' = (LTRIM(' ABCDE'))
 ,'Right 3' = RIGHT('ABCDE', 3)
 ,'No trailing spaces' = RTRIM('ABCDE ')
 ,'Upper case' = UPPER('Abcde');

--create function
CREATE FUNCTION fnDoubler (@input INT)
RETURNS INT
AS
BEGIN
 SET @input = @input * 2;
 RETURN @input
END 

--call function
SELECT '16 * 2 = ' = dbo.fndoubler(16);

-- Create the Logging table

CREATE TABLE [dbo].[Logging] (

LogTime smalldatetime

, LogMessage varchar(1000));

SELECT 'Average' = AVG(quantity)

,'Average #2' = AVG(CAST(quantity as decimal(5,2)))

,'Number' = COUNT(*)

,'Maximum' = MAX(quantity)

,'Minimum' = MIN(quantity)

,'Total' = SUM(quantity)

FROM Orderlines;

SELECT OrderDate

,'Payment due' = DATEADD(m,1,OrderDate)

,'Days ago' = DATEDIFF(dd,OrderDate,GETDATE())

,'Today' = GETDATE()

FROM Orders;

SELECT 'Left 3' = LEFT('ABCDE', 3)

,'Length' = LEN('ABCDE')

,'Lower case' = LOWER('Abcde')

,'No leading spaces' = (LTRIM(' ABCDE'))

,'Right 3' = RIGHT('ABCDE', 3)

,'No trailing spaces' = RTRIM('ABCDE ')

,'Upper case' = UPPER('Abcde');

--create function

CREATE FUNCTION fnDoubler (@input INT)

RETURNS INT

BEGIN

SET @input = @input * 2;

RETURN @input

END

--call function

SELECT '16 * 2 = ' = dbo.fndoubler(16);

-- Create the Logging table

CREATE TABLE [dbo].[Logging] (

LogTime smalldatetime

, LogMessage varchar(1000));

Posted in Code samples, MTA 98-364 | Leave a comment

Monthly Archives: June 2015

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 4

Recent Posts

Archives

Categories