Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- CREATE DATABASE OOVEO_Salon
- GO
- USE OOVEO_Salon
- */
- -- Create MsCustomer
- CREATE TABLE MsCustomer(
- CustomerId CHAR(5) PRIMARY KEY,
- CustomerName VARCHAR(50),
- CustomerGender VARCHAR(10),
- CustomerPhone VARCHAR(13),
- CustomerAddress VARCHAR(100),
- CONSTRAINT cekIDCust CHECK(CustomerId LIKE 'CU[0-9][0-9][0-9]')
- )
- INSERT INTO MsCustomer VALUES
- ('CU001', 'Franky', 'Male', '08566543338', 'Daan mogot baru Street no 6'),
- ('CU002', 'Ernalia Dewi', 'Female', '085264782135', 'Tanjung Duren Street no 185'),
- ('CU003', 'Elysia Chen', 'Female', '085754206611', 'Kebon Jeruk Street no 120'),
- ('CU004', 'Brando Kartawijaya', 'Male', '081170225561', 'Greenvil Street no 88'),
- ('CU005', 'Andy Putra', 'Male', '087751321421', 'Sunter Street no 42')
- /*1. Create a procedure to view all data on MsCustomer based on specific CustomerId
- (PROCEDURE, EXEC)*/
- CREATE PROCEDURE psc1 @passingId VARCHAR(6) AS
- SELECT *
- FROM MsCustomer
- WHERE CustomerId=@passingId
- EXEC psc1 'CU001'
- /*2. Create a procedure to view all data on MsCustomer based on specific CustomerName,
- if name is odd print 'Name is Odd'(PROCEDURE, IF, LEN, PRINT)*/
- CREATE PROCEDURE psc2 @passingName VARCHAR(50) AS
- IF LEN(@passingName)%2=1
- PRINT 'Name is Odd'
- ELSE
- SELECT *
- FROM MsCustomer
- WHERE CustomerName LIKE '%'+@passingName+'%'
- psc2 'Franky'
- psc2 'Kartawijaya'
- /*3. Create a trigger to show all data before and after update on MsCustomer
- (TRIGGER, UPDATE, INSERTED, DELETED)*/
- CREATE TRIGGER trig1 ON MsCustomer FOR UPDATE AS
- SELECT * FROM deleted
- SELECT * FROM inserted
- UPDATE MsCustomer
- SET CustomerName='Antonius', CustomerAddress='Kelapa Gading Utara'
- WHERE CustomerId='CU001'
- /*4. Create a triger to show CustomerId,CustomerName, CustomerPhone being inserted on MsCustomer
- then insert this data ('CU009','Dani Pedrosa','Male','08161834576', 'Kelapa Gading')
- (TRIGER, INSERT, DECLARE, PRINT)*/
- CREATE TRIGGER trig2 ON MsCustomer FOR INSERT AS
- DECLARE @name VARCHAR(50), @id VARCHAR(6),@phone VARCHAR(12)
- SELECT @name=CustomerName,@id=CustomerId,@phone=CustomerPhone FROM inserted
- PRINT 'Inserted Data : CustomerId ('+@name+'), CustomerId ('+@id+') & CustomerPhone ('+@phone+')'
- INSERT INTO MsCustomer
- VALUES ('CU009','Dani Pedrosa','Male','08161834576', 'Kelapa Gading')
- /*5. Create a triger to show CustomerId being deleted on MsCustomer
- then delete CustomerId CU009
- (TRIGER, DELETE, DECLARE, PRINT)*/
- CREATE TRIGGER trig3 ON MsCustomer FOR DELETE AS
- DECLARE @Id VARCHAR(6)
- SELECT @id=CustomerId
- FROM deleted
- PRINT 'Data deleted: Id('+@id+')'
- DELETE FROM MsCustomer WHERE CustomerId='CU009'
- /*6. Make cursor that can select all data by fetch next,prio,first,last,relative -2, absolute 3 on MsCustomer
- (CURSOR SCROLL, OPEN, FETCH, CLOSE, DEALLOCATE)*/
- DECLARE crs1 CURSOR SCROLL FOR
- SELECT *
- FROM MsCustomer
- OPEN crs1
- FETCH NEXT FROM crs1
- FETCH PRIOR FROM crs1
- FETCH FIRST FROM crs1
- FETCH LAST FROM crs1
- FETCH RELATIVE -2 FROM crs1
- FETCH ABSOLUTE 3 FROM crs1
- CLOSE crs1
- DEALLOCATE crs1
- /*7. Make cursor that print name for all the data on MsCustomer
- (CURSOR, OPEN, WHILE, BEGIN, END)*/
- DECLARE crs2 CURSOR FOR
- SELECT CustomerName
- FROM MsCustomer
- OPEN crs2
- DECLARE @nama VARCHAR(50)
- FETCH NEXT FROM crs2 INTO @nama
- WHILE @@FETCH_STATUS=0
- BEGIN
- PRINT 'Nama= '+@nama
- FETCH NEXT FROM crs2 INTO @nama
- END
- CLOSE crs2
- DEALLOCATE crs2
Add Comment
Please, Sign In to add comment