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

متابعين

بتاريخ: 8 نوفمبر 201015 سنة

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

هذه بعض التمارين المختصة في مادة سيكوال بارت 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 نوفمبر 201015 سنة

كاتب الموضوع

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

الأسئلة:

تمارين الكلاس.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

اقتباس

تقديم بلاغ

انضم إلى المناقشة

يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.

متابعين

عودة لقائمة الموضوعات

تسجيل دخول

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

Featured Replies

انضم إلى المناقشة

برجاء الإنتباه

Account

Navigation

البحث

إعداد إشعارات المتصفح الفورية

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)