Need output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need output

Author

Topic

magnusz
Starting Member

2 Posts

Posted - 2011-12-11 : 10:29:41

Hey, im new to the forums, this is my first post, i have these T-SQL commands, all i need is the output.
Here is the sql file.

http://www.megaupload.com/?d=7W823BYN

5.

A.

CREATE PROCEDURE usp_DISP_CUST_CRED
@custnum char(3)
AS
SELECT CUSTOMER_NAME, CREDIT_LIMIT
FROM CUSTOMER
WHERE CUSTOMER_NUM = @custnum

B.

CREATE PROCEDURE usp_DISP_ORDERS
@ordernum char(5)
AS
SELECT ORDER_DATE, ORDERS.CUSTOMER_NUM, CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDERS.ORDER_NUM =@ordernum
AND ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM

C.

CREATE PROCEDURE usp_ADD_ORDERS
@ordernum char(5),
@orderdate datetime,
@custnum char(3)
AS
INSERT INTO ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM)
VALUES
(@ordernum, @orderdate, @custnum)

D.

CREATE PROCEDURE usp_UPD_ORDER_DATE
@ordernum char(5),
@orderdate datetime
AS
UPDATE ORDERS
SET ORDER_DATE = @orderdate
WHERE ORDER_NUM = @ordernum

E.

CREATE PROCEDURE usp_DEL_ORDERS
@ordernum char(5)
AS
DELETE
FROM ORDERS
WHERE ORDER_NUM = @ordernum

6.

A.

CREATE PROCEDURE usp_DISP_PART_CLASS
@class char(2)
AS
DECLARE @partnum char(4)
DECLARE @partdesc char(15)
DECLARE @warehse char(1)
DECLARE @price decimal(6,2)
DECLARE mycursor CURSOR READ_ONLY

FOR
SELECT PART_NUM, DESCRIPTION,WAREHOUSE,PRICE
FROM PART
WHERE CLASS = @class

OPEN mycursor

FETCH NEXT FROM mycursor
INTO @partnum, @partdesc, @warehse, @price

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @partnum+' '+@partdesc+' '+@warehse+' '+@price

FETCH NEXT FROM mycursor
INTO @partnum, @partdesc, @warehse, @price
END

CLOSE mycursor
DEALLOCATE mycursor

8.

CREATE PROCEDURE usp_CHG_PART_PRICE
@partnum char(4),
@price decimal(6,2)
AS
UPDATE PART
SET PRICE = @price
WHERE PART_NUM = @partnum

EXEC usp_CHG_PART_PRICE'AT94','26.95'

9.
A.

CREATE TRIGGER ADD_REP_COMM
ON CUSTOMER
AFTER INSERT
AS

DECLARE @balance decimal(8,2)
SELECT @balance = (SELECT BALANCE FROM INSERTED)
UPDATE REP
SET COMMISSION = COMMISSION + (@balance * RATE)

B.

CREATE TRIGGER UPD_REP_COMM
ON CUSTOMER
AFTER UPDATE
AS

DECLARE @newbalance decimal(8,2)
DECLARE @oldbalance decimal(8,2)
SELECT @newbalance = (SELECT BALANCE FROM INSERTED)
SELECT @oldbalance = (SELECT BALANCE FROM DELETED)

UPDATE REP
SET COMMISSION = COMMISSION + ((@newbalance -@oldbalance) * RATE)

C.

CREATE TRIGGER DEL_REP_COMM
ON CUSTOMER
AFTER DELETE
AS

DECLARE @balance decimal(8,2)
SELECT @balance = (SELECT BALANCE FROM DELETED)

UPDATE REP
SET COMMISSION = COMMISSION - (@balance * RATE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 11:49:07

didnt understand what output you're expecting? can you post that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-12-11 : 12:03:52

FOr getting output..

You need to execute procedures with parameters

Exec Proc_name [parameter value]

magnusz
Starting Member

2 Posts

Posted - 2011-12-11 : 12:28:22

well, I;ve only used PL/SQL commands, i use Oracle, and these 3 hw questions are T-SQL which i have the commands for and i dont have that installed, i cant execute T-SQL in oracle, which is why i need the output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 13:27:20

quote:
Originally posted by magnusz

well, I;ve only used PL/SQL commands, i use Oracle, and these 3 hw questions are T-SQL which i have the commands for and i dont have that installed, i cant execute T-SQL in oracle, which is why i need the output.

you cant execute T-SQL in Oracle. T-SQL is Microsoft SQL Servers proprietary extension for Structured Query Language or simply SQL whereas PL/SQL(Procedural Language/Structured Query Language) is Oracles extension.
Whatever you've shown below are modules which are used inside T-SQL. You need to run and create them first and then use suggestion given by vijays3 for executing them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-11 : 18:27:31

homework ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources