PDA

View Full Version : مشکل در حل این query ها



shgroup
سه شنبه 12 بهمن 1389, 10:34 صبح
سلام دوستان
روز قبل جزوه ای به دستم رسید که فکر می کنم مربوط به درس بانک اطلاعاتی کارشناسی ارشد بود و انتهای این جزوه چندین سوال داشت.
سعی کردم به این سوالات جواب بدم اما در هر کدوم به یه مشکلی برخورد کردم !
گفتم این سوالات رو اینجا قرار بدم که هم جواب بگیریم هم تمرینی باشه برای همه - سوالاتش جالبه:
اول ساختار جداول:

Company(id,title,city,street,post,postcode)1
Product(id,title,company_id,buyprice,sellprice)2
Customer(id,code,firstname,lastname)3
invoice(id,cutomer_id,code,date)4
invoicedetail(id,invoice_id,product_id,quantity)5
Phonetype(id,title)6
Phone(id,Phonetype_id,company_id,number)7

سوالات:

1) نام پرفروشترین محصول را به دست آورید؟
2) فهرست تعداد محصولات خریداری شده توسط هر مشتری(هر مشتری از هر محصول چه تعدادی خرید کرده)؟
3) نام و نام خانوادگی مشتریانی که از شرکت Microsoft هیچ محصولی را خریداری ننموده اند؟
4) مشخص کنید هر شرکت از هر محصولش چه تعدادی فروخته است؟
5) گرانترین محصول هر شرکت چه نام دارد؟
6) محصولی با نام Sql Server و کد 00005 که متعلق به شرکت Microsoft است و قیمت خرید آن .... و قیمت فروش آن .... می باشد را به جدول Product اضافه کنید؟ (دستور Insert)


اگر از سوالات خوشتون اومد باز هم در این جزوه نمونه سوال داره بعدا قرار می دهم/
موفق باشید

m_omrani
سه شنبه 12 بهمن 1389, 22:50 عصر
موضوع جالبیه. برای کمی خستگی در کردن هم خوبه.

من یه دیتابیس نمونه برای این جداول درست کردم و بعد از تعریف رکوردهای پایه ای اولیه (نظیر شرکت ها، محصولات و مشتریان) یه اسکریپت برای درج تعداد زیادی سفارش به طور تصادفی نوشتم.

اسکریپت ایجاد دیتابیس آزمایشی

use master;
go
IF DB_ID (N'mytest') IS NOT NULL
drop database mytest;
GO
create database mytest;
go
create table mytest.dbo.Company
(
id int identity(1,1) not null,
title varchar(50) not null,
city varchar(50) null,
street varchar(50) null,
post varchar(200) null,
postcode varchar(100) null,
constraint Company_PK primary key clustered(id asc)
)
go
create table mytest.dbo.Product
(
id int identity(1,1) not null,
title varchar(50) not null,
company_id int not null,
buyprice real,
sellprice real,
constraint Product_PK primary key clustered(id asc),
constraint FK_Product_Company foreign key(company_id) references mytest.dbo.Company(id)
)
go
create table mytest.dbo.Customer
(
id int identity(1,1) not null,
code varchar(20) null,
firstname varchar(50) null,
lastname varchar(50) null,
constraint Costumer_PK primary key clustered(id asc)
)
go
create table mytest.dbo.invoice
(
id int identity(1,1) not null,
customer_id int not null,
code varchar(10) not null,
[date] datetime default getdate(),
constraint invoice_PK primary key clustered(id asc),
constraint FK_invoice_Customer foreign key(customer_id) references mytest.dbo.Customer(id)
)
go
create table mytest.dbo.invoicedetail
(
id int identity(1,1) not null,
invoice_id int not null,
product_id int not null,
quantity int,
constraint invoicedetail_PK primary key clustered(id asc),
constraint FK_invoicedetail_invoice foreign key(invoice_id) references mytest.dbo.invoice(id),
constraint FK_invoicedetail_Product foreign key(product_id) references mytest.dbo.Product(id)
)
create table mytest.dbo.Phonetype
(
id int identity(1,1) not null,
title varchar(50) not null,
constraint Phonetype_PK primary key clustered(id asc)
)
go
create table mytest.dbo.Phone
(
id int identity(1,1) not null,
Phonetype_id int not null,
company_id int not null,
number varchar(15) not null,
constraint Phone_PK primary key clustered(id asc),
constraint FK_Phone_Phonetype foreign key(Phonetype_id) references mytest.dbo.Phonetype(id),
constraint FK_Phone_Company foreign key(company_id) references mytest.dbo.Company(id)
)
go

اسکریپت درج رکوردهای پایه ای:

use mytest;
insert into mytest.dbo.Company(title)values('Microsoft')
insert into mytest.dbo.Company(title)values('Sony')
insert into mytest.dbo.Company(title)values('Apple')
insert into mytest.dbo.Company(title)values('Dell')
insert into mytest.dbo.Company(title)values('Nokia')
insert into mytest.dbo.Company(title)values('Reseller I')
insert into mytest.dbo.Company(title)values('Reseller II')
insert into mytest.dbo.Company(title)values('Reseller III')

declare @id int
declare @fk int

select @id = id from mytest.dbo.Company where title='Microsoft'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2005',@id,0,4000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2008',@id,0,5000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2005',@id,0,6000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2008',@id,0,8000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2010',@id,0,9000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('XBox',@id,0,1000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows XP',@id,0,2000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows 7',@id,0,3000)

select @id = id from mytest.dbo.Company where title='Sony'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop I',@id,0,2500)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop II',@id,0,2300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop III',@id,0,2800)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.LCD I',@id,0,2500)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.LCD II',@id,0,2200)

select @id = id from mytest.dbo.Company where title='Apple'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,0,800)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Mac I',@id,0,2000)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPad',@id,0,600)

select @id = id from mytest.dbo.Company where title='Dell'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop I',@id,0,1200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop II',@id,0,1300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop III',@id,0,1800)

select @id = id from mytest.dbo.Company where title='Nokia'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N95',@id,0,650)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N96',@id,0,700)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N97',@id,0,800)

select @id = id from mytest.dbo.Company where title='Reseller I'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('SQL Server 2005',@id,4000,4200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Visual Studio 2005',@id,6000,6200)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Windows XP',@id,2000,2100)

select @id = id from mytest.dbo.Company where title='Reseller II'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop I',@id,2500,2600)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('XBox',@id,1000,1100)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop II',@id,1300,1450)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop III',@id,1800,1950)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,800,850)

select @id = id from mytest.dbo.Company where title='Reseller III'
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Sony.Laptop II',@id,2300,2450)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('Dell.Laptop I',@id,1200,1300)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPod',@id,800,900)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('iPad',@id,600,650)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N95',@id,650,680)
insert into mytest.dbo.Product(title,company_id,buyprice,sellp rice)values('N97',@id,800,850)

insert into mytest.dbo.Customer(code,firstname,lastname)values ('001','Hamed','Karimi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('002','Ali','Saberi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('003','Reza','Mahmoodi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('004','Pedram','Shayan')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('005','Naser','Kiani')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('006','Behnoosh','Ahmadi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('007','Kazem','Razavi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('008','Hojat','Yazdani')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('009','Taher','Forooghi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('010','Bita','Nazemi')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('011','John','White')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('012','Edward','Brown')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('013','Tom','Fox')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('014','Jerri','Perkins')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('015','Susan','Daiton')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('016','Katrin','Albert')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('017','Adam','Mckey')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('018','Nick','Luke')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('019','Deniz','Oldman')
insert into mytest.dbo.Customer(code,firstname,lastname)values ('020','Ben','Howard')


اسکریپت درج تعدادی سفارش به صورت تصادفی:


use mytest;
declare @MAX_INV_COUNT int
declare @MAX_QUANTITY int

declare @customerCount int
declare @productCount int
declare @start_date datetime
declare @enddate datetime
declare @currentdate datetime
declare @customer_id int
declare @invoice_id int
declare @invoice_code varchar(10)
declare @invoice_count int
declare @product_id int
declare @quantity int
declare @row int
declare @counter int

set @MAX_INV_COUNT = 6
set @MAX_QUANTITY = 10
set @start_date = '2010-06-01'
set @enddate = '2011-01-01'
select @customerCount = COUNT(id) from mytest.dbo.Customer
select @productCount = COUNT(id) from mytest.dbo.Product

set @currentdate = @start_date
while @currentdate < @enddate
begin
-- step 1. choose a random customer
set @row = ROUND(rand() * (@customerCount - 1),0) + 1;
with cte as
(
select ROW_NUMBER() over(order by id) as Row,id from mytest.dbo.Customer
)
select @customer_id = cte.id from cte where cte.Row = @row

-- step 2. create an invoice
-- 2.1. choose a random invoice code which does not already exist in the invoice table
while 1 = 1
begin
set @invoice_code = 'INV' + right('00000' + cast(round(RAND() * 10000,0) as varchar(10)),5)
if not exists(select 1 from mytest.dbo.invoice where code = @invoice_code) break;
end
-- 2.2. create the invoice
insert into mytest.dbo.invoice(code,customer_id,[date])values(@invoice_code,@customer_id,@currentdate)
select @invoice_id = id from mytest.dbo.invoice where code = @invoice_code

-- step 3. order a random number of random products
-- 3.1. choose number of orders
set @invoice_count = ROUND(rand() * @MAX_INV_COUNT,0)
set @counter = 1
while 1 = 1
begin
-- 3.2. choose a random product
set @row = ROUND(rand() * (@productCount - 1),0) + 1;
with cte as
(
select ROW_NUMBER() over(order by id) as Row,id from mytest.dbo.Product
)
select @product_id = cte.id from cte where cte.Row = @row

-- 3.3. choose a random quantity
set @quantity = ROUND(rand() * @MAX_QUANTITY,0)
-- 3.4. create invoice detail
insert into mytest.dbo.invoicedetail(invoice_id,product_id,qua ntity)values(@invoice_id,@product_id,@quantity)

set @counter = @counter + 1
if @counter > @invoice_count break;
end

set @currentdate = DATEADD(DAY,1,@currentdate)
end

m_omrani
سه شنبه 12 بهمن 1389, 22:51 عصر
و کوئری های سوالات مطرح شده:


use mytest;
declare @question int
set @question = 5

if @question = 1
-- I. top most 5 sold products
select top 5 p.title,SUM(p.sellprice * id.quantity) as TotalSell
from invoicedetail id
inner join invoice i on id.invoice_id = i.id
inner join Product p on id.product_id = p.id
where i.[date] between '2010-06-01' and '2011-01-01'
group by p.title
order by SUM(p.sellprice * id.quantity) desc

if @question = 2
-- II. customer product list
select c.firstname,c.lastname,p.title,SUM(id.quantity) as QuantityCount
from Customer c
inner join invoice i on c.id = i.customer_id
inner join invoicedetail id on i.id = id.invoice_id
inner join Product p on id.product_id = p.id
where i.[date] between '2010-06-01' and '2011-01-01'
group by c.firstname,c.lastname,p.title

if @question = 3
-- III. customers who does not buy anything from Microsoft
select c.firstname,c.lastname
from Customer c
left join
(
select i.customer_id from invoice i
inner join invoicedetail id on i.id = id.invoice_id
inner join Product p on id.product_id = p.id
inner join Company cp on p.company_id = cp.id
where cp.title = 'Microsoft' and i.[date] between '2010-06-01' and '2010-07-01'
) q on c.id = q.customer_id
where q.customer_id is null

if @question = 4
select cp.title,p.title,SUM(id.quantity) as QuantityCount
from Company cp
inner join Product p on cp.id = p.company_id
inner join invoicedetail id on p.id = id.product_id
inner join invoice i on i.id = id.invoice_id
where i.[date] between '2010-06-01' and '2011-01-01'
group by cp.title,p.title
order by cp.title,p.title

if @question = 5
select cp.title,
(
select top 1 p.title
from Product p
inner join invoicedetail id on p.id = id.product_id
inner join invoice i on i.id = id.invoice_id
where p.company_id = cp.id and i.[date] between '2010-06-01' and '2011-01-01'
group by p.title
order by SUM(p.sellprice * id.quantity) desc
) as TopProduct
from Company cp

m_omrani
سه شنبه 12 بهمن 1389, 22:58 عصر
اسکریپت کامل رو از اینجا می تونید دانلود کنید:

http://s000.tinyupload.com/index.php?file_id=63689759748570881376

فایل مزبور شامل 4 فایل است:

mytest_create_db.sql: دیتابیس آزمایشی و جداول مورد نیاز رو ایجاد می کنه.
mytest_insert_basic_data.sql: رکوردهای پایه ای (لیست شرکت ها، محصولات و مشتریان) رو ایجاد می کنه.
mytest_generate_random_data.sql: تعدادی سفارش به طور تصادفی ایجاد می کنه. به این ترتیب که در یک حلقه برای یک بازه زمانی که من از 2010-06-01 تا 2011-01-01 تعیین کردم، برای یک مشتری تصادفی، به تعداد تصادفی، تعدادی کالا به طور تصادفی و با تعداد سفارش تصادفی ثبت می کنه.
mytest_queries.sql: و در این اسکریپت هم کوئری های سوالاتی که مطرح شده قرار داده شده. یه متغیر به نام @question وجود داره که با مقداردهی اون از 1 تا 5 و اجرای اسکریپت می تونید پاسخ هر سوال رو ببینید.

m_omrani
سه شنبه 12 بهمن 1389, 23:02 عصر
توضیحی در مورد کوئری ها:

اگرچه تمام سوالات مطرح شده قابل پاسخگوییه و پرس و جوهای مربوط به هر کدوم قابل نوشتن و داده های مورد نیاز به هر حال قابل استخراجه، ولی از لحاظ عملی با تعیین یک بازه زمانی معمولاً گزارشات بهتری در این خصوص می شه تهیه کرد. مثلاً سوال اول (پر فروش ترین محصول) تبدیل می شه به این که پر فروش ترین محصول در ماه جاری، در ماه گذشته، در شش ماه گذشته یا مثلاً در سال گذشته چه محصولی بوده. به همین دلیل من برای تمامی پرس و جوهایی که نوشتم یه بازه زمانی تعیین کردم. اگه حذفش کنیم می شه جواب سوالات مورد نظر.

shgroup
چهارشنبه 13 بهمن 1389, 16:09 عصر
m_omrani عزیز:
بسیار متشکرم از وقتی که گذاشتی و پاسخ درست به سوالات.
دوستان من جواب سوالات رو با توجه به پاسخ جناب m_omrani اینجا قرار میدهم
باز هم سپاسگزارم


(1
select top 1 Product.title,SUM(Product.sellprice * InvoiceDetail.quantity) as TotalSell
from InvoiceDetail
inner join Invoice on invoice_id = Invoice.id
inner join Product on product_id = Product.id
group by Product.title
order by SUM(Product.sellprice * InvoiceDetail.quantity) desc

2)
select Customer.firstname,Customer.lastname,Product.title ,SUM(InvoiceDetail.quantity) as QuantityCount
from Customer
inner join invoice on Customer.id = Invoice.customer_id
inner join invoicedetail on InvoiceDetail.invoice_id = Invoice.id
inner join Product on InvoiceDetail.product_id = Product.id
group by Customer.firstname,Customer.lastname,Product.title

3)
SELECT c.firstname, c.lastname
FROM dbo.Customer AS c LEFT OUTER JOIN
(SELECT i.customer_id
FROM dbo.Invoice AS i INNER JOIN
dbo.InvoiceDetail AS id ON i.id = id.invoice_id INNER JOIN
dbo.Product AS p ON id.product_id = p.id INNER JOIN
dbo.Company AS cp ON p.company_id = cp.id
WHERE (cp.title = 'Microsoft')) AS q ON c.id = q.customer_id
WHERE (q.customer_id IS NULL)

4)
select cp.title,p.title,SUM(id.quantity) as QuantityCount
from Company cp
inner join Product p on cp.id = p.company_id
inner join invoicedetail id on p.id = id.product_id
inner join invoice i on i.id = id.invoice_id
group by cp.title,p.title
order by cp.title,p.title

5)
select cp.title,
(
select top 1 p.title
from Product p
where p.company_id = cp.id
group by p.title
order by SUM(p.sellprice) desc
) as TopProduct
from Company cp

6)
insert into Product(title,code,company_id,sellprice,buyprice) values ('Sql Server',00005,4,80000,90000)