asked in 電腦與網際網路程式設計 · 1 decade ago

SQL 的作業題目 請高手大大 幫忙 10點

--1

A

請寫一個預存程序能接受三個參數(前兩個參數為年份,月份,第三個

參數為傳回訂購次數的值),此預存程序可以查詢orders資料表某年份(第一個參數)某月份(弟二個參數)的訂購次數(透過第三個傳回

訂購次數的植)

B 請呼叫此預存程序

答案

1997 5 32

(影響1個資料列)

--2

請寫一個檢視表,可以列出1997/11/27有下過訂單的客戶的Companyname , productname,quantity

(影響8個資料列)

--3

請健一個使用者定量純量函數,此純量函數有三個參數,分別為年,月

客戶代碼(customerid),此純量函數會列書該客戶代碼在某年某月所訂購的次數

請執行此純量函數並使用三個參數值,分別年為1996,月為8,客戶代碼為RATTC

答案

2

影響一個資料列

1 Answer

Rating
  • 1 decade ago
    Favorite Answer

    --第一題--

    CREATE PROCEDURE Exam1(@@Year decimal(4,0),@@Month decimal(2,0) ,@@Num decimal(5,0) OUTPUT)

    AS

    BEGIN

    select @@Num=count(*) from Orders

    where Year(OrderDate)=@@Year

    and Month(OrderDate)=@@Month

    RETURN(@@Num)

    END

    declare @@B decimal(5,0)

    exec dbo.Exam1 1997,5,@@B OUTPUT

    print @@B

    --第二題--

    CREATE VIEW Exam2

    AS

    select Companyname , productname,quantity,* from Customers as a, Orders as b, [dbo].[Order Details] as c ,Products as d

    where a.CustomerID=b.CustomerID and

    b.OrderID = c.OrderID and

    c.ProductID = d.ProductID and

    OrderDate = CAST ( ' 1997/11/27' AS datetime )

    --第三題--

    CREATE FUNCTION Exam3(@Year int,@Month int,@CusID varchar(20))

    RETURNS int

    AS

    BEGIN

    DECLARE @Num int

    select @Num=count(*) from Orders

    where Year(OrderDate)=@Year

    and Month(OrderDate)=@Month

    and CustomerID=@CusID

    RETURN(@Num)

    END

    select dbo.Exam3(1996,8,'RATTC')

    Source(s): 8年程式經驗
Still have questions? Get your answers by asking now.