إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

تمارين مع الحل لمادة Sql Part 1


1 رد (ردود) على هذا الموضوع

#1 انريكي

انريكي

    عضو نشط

  • الأعضــاء
  • 335 مشاركة
  • البـلـد: Country Flag
  • الاهتمامات:Programming

تاريخ المشاركة 09 November 2010 - 12:48 AM

السلام عليكم جميعاً...


هذه بعض التمارين المختصة في مادة سيكوال بارت 1 مع الحل


لتعم الفائدة للجميع ...


الاسئلة :

ملف مرفق  التمارين.doc   33كيلو   172 عدد مرات التحميل


الحل :

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



#2 انريكي

انريكي

    عضو نشط

  • الأعضــاء
  • 335 مشاركة
  • البـلـد: Country Flag
  • الاهتمامات:Programming

تاريخ المشاركة 09 November 2010 - 12:58 AM




تمارين أخرى ..

الأسئلة:

ملف مرفق  تمارين الكلاس.doc   31.5كيلو   50 عدد مرات التحميل


الحل :

--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(<img src='http://www.araboug.org/ib/public/style_emoticons/<#EMO_DIR#>/cool.png' class='bbc_emoticon' alt='B)' />
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