انريكي بتاريخ: 8 نوفمبر 2010 تقديم بلاغ مشاركة بتاريخ: 8 نوفمبر 2010 السلام عليكم جميعاً... هذه بعض التمارين المختصة في مادة سيكوال بارت 1 مع الحل لتعم الفائدة للجميع ... الاسئلة : التمارين.doc الحل : IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TheLibrary') DROP DATABASE [TheLibrary] GO CREATE DATABASE [TheLibrary] ON (NAME = N'Lib1', FILENAME = N'D:\Lib.MDF' , SIZE = 2, MAXSIZE = 5, FILEGROWTH = 10%) LOG ON (NAME = N'LT1', FILENAME = N'D:\LT1_Log.LDF' , SIZE = 2, MAXSIZE = 5, FILEGROWTH = 10%), (NAME = N'LT2', FILENAME = N'D:\LT2_Log.LDF' , SIZE = 3, MAXSIZE = 5, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS GO ALTER DATABASE [TheLibrary] ADD FILEGROUP [Libra] GO ALTER DATABASE [TheLibrary] ADD FILE(NAME = N'Lib2', FILENAME = N'D:\Lib2.NDF' , SIZE = 3, MAXSIZE = 6, FILEGROWTH = 10%) TO FILEGROUP [Libra] GO ALTER DATABASE [TheLibrary] ADD FILE(NAME = N'Lib3', FILENAME = N'D:\Lib3.NDF' , SIZE = 5, MAXSIZE = 7, FILEGROWTH = 10%) TO FILEGROUP [Libra] GO exec sp_dboption N'TheLibrary', N'autoclose', N'false' GO exec sp_dboption N'TheLibrary', N'bulkcopy', N'false' GO exec sp_dboption N'TheLibrary', N'trunc. log', N'false' GO exec sp_dboption N'TheLibrary', N'torn page detection', N'true' GO exec sp_dboption N'TheLibrary', N'read only', N'false' GO exec sp_dboption N'TheLibrary', N'dbo use', N'false' GO exec sp_dboption N'TheLibrary', N'single', N'false' GO exec sp_dboption N'TheLibrary', N'autoshrink', N'false' GO exec sp_dboption N'TheLibrary', N'ANSI null default', N'false' GO exec sp_dboption N'TheLibrary', N'recursive triggers', N'false' GO exec sp_dboption N'TheLibrary', N'ANSI nulls', N'false' GO exec sp_dboption N'TheLibrary', N'concat null yields null', N'false' GO exec sp_dboption N'TheLibrary', N'cursor close on commit', N'false' GO exec sp_dboption N'TheLibrary', N'default to local cursor', N'false' GO exec sp_dboption N'TheLibrary', N'quoted identifier', N'false' GO exec sp_dboption N'TheLibrary', N'ANSI warnings', N'false' GO exec sp_dboption N'TheLibrary', N'auto create statistics', N'true' GO exec sp_dboption N'TheLibrary', N'auto update statistics', N'true' GO use [TheLibrary] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Details_Books]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Details] DROP CONSTRAINT FK_Details_Books GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[books] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Categories] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Details] GO if exists (select * from dbo.systypes where name = N'address') exec sp_droptype N'address' GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RE]') and OBJECTPROPERTY(id, N'IsRule') = 1) drop rule [dbo].[RE] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DE]') and OBJECTPROPERTY(id, N'IsDefault') = 1) drop default [dbo].[DE] GO create default [DE] as 'USA' GO create rule [RE] as @A IN ('SCIENCE','ARTS','ENGLISH') GO setuser GO EXEC sp_addtype N'address', N'varchar (50)', N'null' GO setuser GO CREATE TABLE [dbo].[books] ( [bookid] [int] NOT NULL , [bookName] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Author] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [address] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Categories] ( [Categoryid] [int] NOT NULL , [CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [store_ID] [int] IDENTITY (10, 10) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Details] ( [bookid] [int] NULL , [Cost] [int] NULL , [Qty] [int] NULL , [Categoryid] [int] NULL , [OrderDate] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[books] WITH NOCHECK ADD CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED ( [bookid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [Categoryid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Details] WITH NOCHECK ADD CONSTRAINT [CK_Details] CHECK ([COST] >= 5 and [COST] <= 100) GO setuser GO EXEC sp_bindefault N'[dbo].[DE]', N'[books].[City]' GO setuser GO setuser GO EXEC sp_bindrule N'[dbo].[RE]', N'[Categories].[CategoryName]' GO setuser GO ALTER TABLE [dbo].[Details] ADD CONSTRAINT [FK_Details_Books] FOREIGN KEY ( [bookid] ) REFERENCES [dbo].[books] ( [bookid] ) ON DELETE CASCADE ON UPDATE CASCADE GO اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
انريكي بتاريخ: 8 نوفمبر 2010 كاتب الموضوع تقديم بلاغ مشاركة بتاريخ: 8 نوفمبر 2010 تمارين أخرى .. الأسئلة: تمارين الكلاس.doc الحل : --1 use northwind GO create procedure dbo.storders as select* from orders where requireddate <getdate() and shippeddate is null GO --2 EXEC dbo.storders --4 INSERT MyOrders EXEC storders --6 EXEC sp_help stOrders EXEC sp_helptext stOrders EXEC sp_depends stOrders EXEC sp_stored_procedures SELECT * FROM sysobjects SELECT * FROM syscomments SELECT * FROM sysdepends --7 ALTER procedure FIND(@HDATE DATETIME=NULL) as IF @HDATE IS NULL SET @HDATE =getdate() SELECT* FROM EMPLOYEES WHERE HIREDATE=@HDATE --8 EXEC FIND'5/1/1992' --حل أخر EXEC FIND @HDATE ='5/1/1992' --9 ALTER procedure FIND(@HDATE DATETIME, @CI VARCHAR(20)='LONDON') as SELECT* FROM EMPLOYEES WHERE HIREDATE=@HDATE AND CITY=@CI --10 EXEC FIND @HDATE='5/1/1992',@CI='Seattle' --11 use northwind GO create procedure dbo.comName(@ci varchar(20), @com varchar(50)output) as select @com=companyname from Customers where CustomerID=@ci --12 DECLARE @CN varchar(50) exec comName 'ALFKI',@CN output select @CN --**** DECLARE @CN varchar(50) exec comName @CI'ALFKI',@COM=@CN output select @CN --مثال use northwind GO create procedure dbo.MYEMP (@EID INT,@FNAME varchar(20)output, @LNAME varchar(20)output) AS select @FNAME=FIRSTNAME,@LNAME=LASTNAME from EMPLOYEES WHERE EmployeeID=@EID --الإستدعاء DECLARE @E varchar(50),@L varchar(50) exec MYEMP 1,@E output,@L output select @E,@L --13 use northwind GO create procedure dbo.NewEmp (@F varchar(20),@L varchar(50)) AS INSERT Employees(FirstName,LastName) VALUES (@F,@L) RETURN SCOPE_IDENTITY() GO --14 declare @num int exec @num=NewEmp 'ahmad','salem' select @num --declare @num int --exec @num=NewEmp @f='ahmad',@l='salem' --select @num --15 use northwind GO CREATE procedure GETORDERS(@CI varchar(20)) as SELECT CustomerID,OrderID,EmployeeID FROM Orders where CustomerID=@CI return @@rowcount --16 declare @N INT exec @N=GETORDERS'ALFKI' select @N --17(A) use northwind GO ALTER procedure GETORDERS(@CI varchar(20)=NULL) as IF @CI IS NULL BEGIN RAISERROR('أدخل رقم الزبون',16,1) return END SELECT CustomerID,OrderID,EmployeeID FROM Orders where CustomerID=@CI return @@rowcount --الاستدعاء EXEC GETORDERS --17( use northwind GO alter procedure GETORDERS(@CI varchar(20)=NULL) as IF @CI IS NULL BEGIN RAISERROR('أدخل رقم الزبون',16,1) return END IF NOT EXISTS (SELECT*FROM Orders WHERE CustomerID=@CI) BEGIN RAISERROR('لايوجد سجلات للزبون المدخل رقمة',16,1) return END SELECT CustomerID,OrderID,EmployeeID FROM Orders where CustomerID=@CI return @@rowcount --18 create PROC SPRINT(@CUSID VARCHAR(20)) AS declare @N INT exec @N=GETORDERS @CUSID select @N PRINT('عدد طلبيات هذا الزبون'+ CONVERT(VARCHAR(5),@N)) --19 الأستدعاء EXEC SPRINT'ALFKI' --20 EXEC sp_addmessage @msgnum=50012,@severity=16, @msgtext='you did not provide Customer ID',@with_log='true' --حل أخر EXEC sp_addmessage 50012,16,'you did not provide Customer ID', @with_log='true' --21 use northwind GO alter procedure GETORDERS(@CI varchar(20)=NULL) as IF @CI IS NULL BEGIN RAISERROR(50012,16,1) return END IF NOT EXISTS (SELECT*FROM Orders WHERE CustomerID=@CI) BEGIN RAISERROR('لايوجد سجلات للزبون المدخل رقمة',16,1) return END SELECT CustomerID,OrderID,EmployeeID FROM Orders where CustomerID=@CI return @@rowcount -- EXEC GETORDERS --22 USE Northwind -----------------------إنشاء الجداول------------------------------- GO CREATE TABLE dbo.Student (StudentID Int,StudentName varchar(10)) GO CREATE TABLE dbo.Mark (StudentID Int ,StudentMark int) GO ALTER TABLE dbo.Student ADD CONSTRAINT Ck_St CHECK (StudentID > 10) -------------------------------------------------------------------- create PROC dbo.StudentInformation (@stID int=NULL, @stName Varchar(10)=NULL, @stMark int=NULL) AS begin transaction INSERT Student VALUES(@stID,@stName) if @@error<>0 begin raiserror('failed',16,1) rollback transaction return end INSERT Mark VALUES(@stID,@stMark) if @@error<>0 begin raiserror('failed',16,1) rollback transaction return end commit transaction ---------------------------الاستدعاء------------------------------------- EXEC dbo.StudentInformation @stID=1,@stName='Sandy', @stMark=100 اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.