سوال از پایگاه داده فروش فرش

سلام دوستان گرامی

یه پایگاه داده دارم به شکل زیر

http://upload7.ir/imgs/2014-11/44390...1988_thumb.png

من این جدول ها رو با یه سری داده پر کردم

حالا 2تا سوال هست که باید بهشون جواب بدم

اما بلد نیستم

امیدوارم شما کمک بفرمایید


سوال اول: دستوراتی باید بنویسم که کد مشتری رو از ورودی بگیره و مجموع مبلغ فرش های خریداری شده رو حساب کنه اینو تا یه جاهایی نوشتم اما درست کار نمیکنه

create proc  de

@code int

as

set @code=1

select c_price from curpet

join buy

on buy.c_code=curpet.c_code

join sell

on sell.c_code=curpet.c_code

join customer

on sell.cu_code=customer.cu_code

where c_price is not null

 group by customer.cu_code



print sum(buy_num*c_price)



exec de

سوال دوم: کد فروشنده ای رو از ورودی دریافت کنه اگر تاریخ فاکتور وی مربوط به 3 ماه گذشته بود تعداد فرش های خریداری شده از وی را نمایش دهد در غیر این صورت فیلد(t_type) را null کند.

اینو که بلد نبودم

پیشاپیش سپاس بابت کمکهای شما

چرا buy.buy_num توی select ننوشتی؟ منظورت از درست کار نمی کنه چیه؟خطا میده ؟ یا خروجی اشتباهی میده ؟خروجی چی میده؟
اینم برای سوال دومت امتحان کن ببینم جواب می ده؟

create proc  de2

as

select buy.buy_num from seller

join buy

on buy.seller_code=seller.seller_code

join factor

on factor.f_ID=buy.f_ID

where factor.f_date<  DATEPART(yyyy, DATEADD(m, -3, getdate())) 

 group by seller.seller_code

 

print sum(buy_num)

 

exec de

نقل قول:

---

نوشته شده توسط ashkufaraz

چرا buy.buy_num توی select ننوشتی؟ منظورت از درست کار نمی کنه چیه؟خطا میده ؟ یا خروجی اشتباهی میده ؟خروجی چی میده؟
اینم برای سوال دومت امتحان کن ببینم جواب می ده؟

---

سلام دوست گرامی سپاس بابت جوابتون

خب بله error میده کدی که من نوشتم،اما به احتمال زیاد اشتباه هم هست اخه باید کد مشتری رو با تک تک کدها مقایسه کنه و اگر فرشی که خریداری شده مربوط به اون مشتری هست تعدادش رو درقیمت ضرب کنه و باهم جمع کنه تا تعداد کل محاسبه بشه و در نهایت چاپ کنه

این خطای کدی که شما گذاشتین هست

نقل قول:

---

Msg 128, Level 15, State 1, Procedure de2, Line 11
The name "buy_num" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

---

.

اما این که کدی رو از ورودی نمیگیره

برای سوال دوم هم من میخوام کد فروشنده رو بگیره از طریق یه متغیر ورودی و با تک تک فروشنده های موجود مقایسه کنه

میدونم که باید از if استفاده کنم اما نمیدونم چطوری !

اسکریپت یتابیس رو قرار بده تا حداقل روی دیتای واقعا کد بنویسیم
این جوری نمیشه
فهمید چی به چیه!

ممنونم،بفرمایید

USE [master]

GO

/****** Object:  Database [Farsh]    Script Date: 10/27/2014 20:21:41 ******/

CREATE DATABASE [Farsh] ON  PRIMARY 

( NAME = N'Farsh', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Farsh.mdf  ' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'Farsh_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Farsh_log  .ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [Farsh] SET COMPATIBILITY_LEVEL = 100

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Farsh].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [Farsh] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Farsh] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Farsh] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Farsh] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Farsh] SET ARITHABORT OFF

GO

ALTER DATABASE [Farsh] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Farsh] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [Farsh] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Farsh] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Farsh] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Farsh] SET CURSOR_DEFAULT  GLOBAL

GO

ALTER DATABASE [Farsh] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Farsh] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Farsh] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Farsh] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Farsh] SET  DISABLE_BROKER

GO

ALTER DATABASE [Farsh] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Farsh] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Farsh] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [Farsh] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [Farsh] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Farsh] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [Farsh] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [Farsh] SET  READ_WRITE

GO

ALTER DATABASE [Farsh] SET RECOVERY FULL

GO

ALTER DATABASE [Farsh] SET  MULTI_USER

GO

ALTER DATABASE [Farsh] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [Farsh] SET DB_CHAINING OFF

GO

EXEC sys.sp_db_vardecimal_storage_format N'Farsh', N'ON'

GO

USE [Farsh]

GO

/****** Object:  Table [dbo].[factor]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[factor](

    [f_ID] [int] NOT NULL,

    [f_date] [date] NULL,

    [karmozd] [int] NULL,

    [t_type] [varchar](20) NULL,

 CONSTRAINT [PK_factor] PRIMARY KEY CLUSTERED 

(

    [f_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[customer]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[customer](

    [cu_code] [int] NOT NULL,

    [cu_name] [varchar](20) NULL,

    [cu_addr] [varchar](30) NULL,

 CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED 

(

    [cu_code] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[curpet]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[curpet](

    [c_code] [int] NOT NULL,

    [c_color] [varchar](20) NULL,

    [c_price] [int] NULL,

    [c_size] [int] NULL,

    [c_model] [varchar](20) NULL,

 CONSTRAINT [PK_curpet] PRIMARY KEY CLUSTERED 

(

    [c_code] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[seller]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[seller](

    [seller_code] [int] NOT NULL,

    [seller_name] [varchar](20) NULL,

    [seller_addr] [varchar](30) NULL,

 CONSTRAINT [PK_seller] PRIMARY KEY CLUSTERED 

(

    [seller_code] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[store]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[store](

    [c_code] [int] NULL,

    [c_num] [int] NULL

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[sell]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[sell](

    [sell_ID] [int] NOT NULL,

    [c_code] [int] NULL,

    [cu_code] [int] NULL,

    [f_ID] [int] NULL,

    [sell_num] [int] NULL,

 CONSTRAINT [PK_sell] PRIMARY KEY CLUSTERED 

(

    [sell_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[buy]    Script Date: 10/27/2014 20:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[buy](

    [buy_ID] [int] NOT NULL,

    [f_ID] [int] NULL,

    [c_code] [int] NULL,

    [seller_code] [int] NULL,

    [buy_num] [int] NULL,

 CONSTRAINT [PK_buy] PRIMARY KEY CLUSTERED 

(

    [buy_ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  ForeignKey [FK_store_curpet]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[store]  WITH CHECK ADD  CONSTRAINT [FK_store_curpet] FOREIGN KEY([c_code])

REFERENCES [dbo].[curpet] ([c_code])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[store] CHECK CONSTRAINT [FK_store_curpet]

GO

/****** Object:  ForeignKey [FK_sell_curpet]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[sell]  WITH CHECK ADD  CONSTRAINT [FK_sell_curpet] FOREIGN KEY([c_code])

REFERENCES [dbo].[curpet] ([c_code])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_curpet]

GO

/****** Object:  ForeignKey [FK_sell_customer]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[sell]  WITH CHECK ADD  CONSTRAINT [FK_sell_customer] FOREIGN KEY([cu_code])

REFERENCES [dbo].[customer] ([cu_code])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_customer]

GO

/****** Object:  ForeignKey [FK_sell_factor]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[sell]  WITH CHECK ADD  CONSTRAINT [FK_sell_factor] FOREIGN KEY([f_ID])

REFERENCES [dbo].[factor] ([f_ID])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[sell] CHECK CONSTRAINT [FK_sell_factor]

GO

/****** Object:  ForeignKey [FK_buy_curpet]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[buy]  WITH CHECK ADD  CONSTRAINT [FK_buy_curpet] FOREIGN KEY([c_code])

REFERENCES [dbo].[curpet] ([c_code])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_curpet]

GO

/****** Object:  ForeignKey [FK_buy_factor]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[buy]  WITH CHECK ADD  CONSTRAINT [FK_buy_factor] FOREIGN KEY([f_ID])

REFERENCES [dbo].[factor] ([f_ID])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_factor]

GO

/****** Object:  ForeignKey [FK_buy_seller]    Script Date: 10/27/2014 20:21:43 ******/

ALTER TABLE [dbo].[buy]  WITH CHECK ADD  CONSTRAINT [FK_buy_seller] FOREIGN KEY([seller_code])

REFERENCES [dbo].[seller] ([seller_code])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[buy] CHECK CONSTRAINT [FK_buy_seller]

GO

--

insert into curpet values (1,'red',1000,6,'500s')

insert into curpet values (2,'green',2000,9,'600s')

insert into curpet values (3,'blue',3000,12,'700s')

--

insert into customer values (12,'ali','arak')

insert into customer values (13,'reza','arak')

insert into customer values (14,'hossein','arak')

--

insert into factor values (21,'12-1-2014',500,'cach')

insert into factor values (22,'12-2-2014',600,'peyment')

insert into factor values (23,'12-3-2014',700,'cach')

--

insert into seller values (31,'ahmad','arak')

insert into seller values (32,'nima','arak')

insert into seller values (33,'sina','arak')

--

insert into store values (1,100)

insert into store values (2,100)

insert into store values (3,100)

--

insert into sell values (41,1,12,21,10)

insert into sell values (42,2,13,22,20)

insert into sell values (43,3,14,23,30)

--

insert into buy values (51,21,1,31,61)

insert into buy values (52,22,2,32,62)

insert into buy values (53,23,3,33,63)

سلام
سوال یک :

declare @CustomerCode int

set @CustomerCode=12





select a.cu_Code [کد مشترک],sum(IsNULL(a.price,0))[جمع فروش],sum(Karmozd) [جمع کارمزد] , count(*) [تعداد فروش]

from 

(select *

,(select c_price from curpet c where c.c_code=s.c_code ) Price

,(select karmozd from Factor f where f.f_id=s.f_id ) Karmozd

 from sell s





where cu_code=@CustomerCode

)a

group by a.cu_Code

سوال دو هم واضح نیست