数据库,用sql server完成如下操作: a. 检索面板类型为“MVA”的显示器的型号、屏幕尺寸和报价。 b. 检索只有一个经销商报价的显示器型号。 c. 在product表中插入一条显示器信息:型号为“三星 S22B360HW”,屏幕尺寸为“22”,面板类型为“TN” d. 从表distributor中删除没有任何报价的经销商信息。 e. 检索 “世纪蓝博”和“华人方创”都有报价的显示器型号 f. 检索对飞利浦的所有型号都有报价的经销商
时间: 2024-03-20 16:41:07 浏览: 17
好的,以下是针对这些问题的 SQL Server 的操作:
a.
```
SELECT Model, ScreenSize, Price
FROM Monitor
WHERE PanelType = 'MVA';
```
b.
```
SELECT Model
FROM Monitor
GROUP BY Model
HAVING COUNT(*) = 1;
```
c.
```
INSERT INTO Monitor (Model, ScreenSize, PanelType)
VALUES ('三星 S22B360HW', 22, 'TN');
```
d.
```
DELETE FROM Distributor
WHERE NOT EXISTS (
SELECT *
FROM Monitor
WHERE Distributor.DistID = Monitor.DistID
);
```
e.
```
SELECT Model
FROM (
SELECT Model, COUNT(*) AS DistCount
FROM Monitor
WHERE DistName IN ('世纪蓝博', '华人方创')
GROUP BY Model
) M
WHERE M.DistCount = 2;
```
f.
```
SELECT DistName
FROM (
SELECT DistName, COUNT(*) AS ModelCount
FROM Monitor M
INNER JOIN Distributor D
ON M.DistID = D.DistID
WHERE M.Model IN (
SELECT Model
FROM Monitor
WHERE Brand = '飞利浦'
)
GROUP BY DistName
) D
WHERE D.ModelCount = (
SELECT COUNT(*)
FROM Monitor
WHERE Brand = '飞利浦'
);
```
需要注意的是,这些代码只是一个大概的思路,具体实现可能需要根据实际情况进行修改和优化。