sql

create database JHGL
use JHGL EXEC sp_changedbowner ‘sa’
USE JHGL
CREATE TABLE Product(
ProductID nvarchar(10) PRIMARY KEY,
ProductName nvarchar(10),
Specification nvarchar(20),
Unit nvarchar(3),
Price MONEY
)
CREATE TABLE Manufacturer(
ManufacturerID nvarchar(10) PRIMARY KEY,
ManufacturerName nvarchar(10),
ContactName nvarchar(10),
MAddress nvarchar(10),
Telephone nvarchar(10)
)
CREATE TABLE Import(
ImportID nvarchar(10) PRIMARY KEY,
ManufacturerID nvarchar(10) references Manufacturer(ManufacturerID),
ProductID nvarchar(10) references Product(ProductID),
Number INT,
Batch INT,
ImportDate DATE
)

insert into Product(ProductID,ProductName,Specification,Unit,Price) values (‘P001’,‘电视机’,‘液晶英寸’,‘台’,‘4000’),
(‘P002’,‘空调’,‘1.5P变频空调’,‘台’,‘2500’),
(‘P003’,‘手机’,‘大屏手机’,‘部’,‘4500’),
(‘P004’,‘手机’,‘中屏手机’,‘部’,‘2200’),
(‘P005’,‘空调’,‘1P空调’,‘台’,‘2000’),
(‘P006’,‘微波炉’,‘小型’,‘台’,‘600’),
(‘P007’,‘电视机’,‘液晶英寸’,‘台’,‘5600’)
insert into Manufacturer(ManufacturerID,ManufacturerName,ContactName,MAddress,Telephone) values (‘M001’,‘海尔公司’,‘张三’,‘山东省青岛市’,‘78726637’),
(‘M002’,‘美的公司’,‘李四’,‘广东省中山市’,‘45342323’),
(‘M003’,‘华为公司’,‘王五’,‘广东省深圳市’,‘56342534’),
(‘M004’,‘小米公司’,‘丁一’,‘广东省深圳市’,‘76564564’),
(‘M005’,‘格力公司’,‘周二’,‘广东省珠海市’,‘67868678’)
insert into Import (ImportID,ManufacturerID,ProductID,Number,Batch,ImportDate) values (‘I001’,‘M001’,‘P002’,‘2300’,‘2’,‘2019-10-10’),
(‘I002’,‘M002’,‘P006’,‘3400’,‘3’,‘2020-02-01’),
(‘I003’,‘M003’,‘P004’,‘1000’,‘4’,‘2020-05-08’),
(‘I004’,‘M004’,‘P003’,‘1200’,‘5’,‘2020-06-01’),
(‘I005’,‘M005’,‘P005’,‘5434’,‘6’,‘2020-07-01’),
(‘I006’,‘M002’,‘P002’,‘3412’,‘8’,‘2020-02-01’),
(‘I007’,‘M002’,‘P005’,‘6600’,‘9’,‘2020-08-01’),
(‘I008’,‘M001’,‘P001’,‘4400’,‘10’,‘2020-09-09’)

select ProductName,Specification
from Product
where Price>2000

select sum(Number*price)
from Import join Product on Import.ProductID=Product.ProductID
where ManufacturerID=‘M002’

select AVG(price)
from Product
where ProductName=‘手机’

select ManufacturerID,COUNT(distinct ProductID) as 产品种类个数
from Import
group by ManufacturerID
having COUNT(distinct ProductID)>2

select ManufacturerName,MAddress
from Manufacturer join Import on Manufacturer.ManufacturerID=Import.ManufacturerID join Product on Product.ProductID=Import.ProductID
where ProductName=‘手机’

select ImportID,ProductName,SUM(Number)
from Manufacturer join Import on Manufacturer.ManufacturerID=Import.ManufacturerID join Product on Product.ProductID=Import.ProductID
where ImportDate<‘2020-01-01’
group by ImportID,ProductName

select ManufacturerName,ContactName,Telephone
from Manufacturer
where MAddress=(select MAddress
from Manufacturer
where ManufacturerName=‘华为公司’)
select top 1 ProductID,ProductName,Price
from Product
order by Price desc

select ProductID
from Import
where ManufacturerID=‘M002’
except
select ProductID
from Import
where ManufacturerID=‘M005’

select ProductName,Specification,price
from Product
where Price>2000
order by Price desc

update Import
set ImportDate=dateadd(day,7,ImportDate)
where ManufacturerID in (select ManufacturerID
from Manufacturer
where ManufacturerName=‘华为公司’)
select ProductName,ImportID,Number,ManufacturerName
from Manufacturer join Import on Manufacturer.ManufacturerID=Import.ManufacturerID join Product on Product.ProductID=Import.ProductID
where Price>2000