GO
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = 'qrComputer') DROP VIEW dbo.qrComputer
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
CREATE VIEW dbo.qrComputer AS

SELECT 

   -- Сборка компьютера
   cmp.Computer_ID,
   
   -- Материнская плата
   cmp.Board_ID,
      brd.Board_Manufacturer,
      brd.Board_Model,
      brd.Board_Chipset,
      Board_FormFactor = brd.FormFactor_ID,
         frm.FormFactor_Code,
      -- Дублирование полей в сборке и в материнской плате,
      -- поэтому добавлен уточняющий префикс Board_
      Board_SocketType_ID = brd.SocketType_ID,
      Board_RAMType_ID = brd.RAMType_ID,
      Board_SATAType_ID = brd.SATAType_ID,
      brd.SATA_Quantity,
      brd.PATA_Quantity,
      brd.Board_Power,
      brd.Board_Count,
      brd.Board_Price,         
   -- Процессор
   cmp.CPU_ID,
      cpu.CPU_Manufacturer,
      cpu.CPU_Model,
      cpu.SocketType_ID,
      cpu.CPU_Clock,
      cpu.CPU_FSB_Clock,
      cpu.CPU_Power,
      cpu.CPU_Count,
      cpu.CPU_Price,
   -- Память
   cmp.RAM_ID,
      ram.RAM_Manufacturer,
      ram.RAM_Model,
      ram.RAM_Kit,
      ram.RAMType_ID,
         ram_typ.RAMType_Code,
      ram.RAM_Clock,
      ram.RAM_Timings,
      ram.RAM_Power,
      ram.RAM_Count,
      ram.RAM_Price,
   -- Диск
   cmp.HDD_ID,
      hdd.HDD_Manufacturer,
      hdd.HDD_Model,
      hdd.Capacity_ID,
      hdd.CacheType_ID,
      hdd.RPMType_ID,
      hdd.SATAType_ID,
      hdd.HDD_Power,
      hdd.HDD_Count,
      hdd.HDD_Price,
   -- Видео
   cmp.VGA_ID,
      vga.VGA_Manufacturer,
      vga.VGA_Model,
      vga.VGA_Length,
      vga.VGA_Power,
      vga.VGA_Count,
      vga.VGA_Price,
   -- Case совпадает с ключевым словом SQL, 
   -- поэтому корпус должен быть назван любым другим синонимом
   cmp.Chasis_ID,
      ch.Chasis_Manufacturer,
      ch.Chasis_Model,
      ch.ChasisType_ID,
      ch.FormFactor_ID,
      ch.Chasis_Power_Module,
      ch.Chasis_Count,
      ch.Chasis_Price,
   -- Блок питания
   cmp.Power_ID,
      pwr.Power_Manufacturer,
      pwr.Power_Model,
      pwr.Power_Power,
      pwr.Power_PATA,
      pwr.Power_SATA,
      pwr.Power_Count,
      pwr.Power_Price,
      
   -- Расчет цены      
   Computer_Price = 
      ISNULL(brd.Board_Price,  0 )
      + ISNULL(cpu.CPU_Price,  0 )
      + ISNULL(hdd.HDD_Price,  0 )
      + ISNULL(vga.VGA_Price,  0 )
      + ISNULL(ch.Chasis_Price,  0 )
      + ISNULL(pwr.Power_Price,  0 )
   
FROM 

   dbo.Computer cmp
   LEFT OUTER JOIN dbo.Board brd ON brd.Board_ID = cmp.Board_ID
   LEFT OUTER JOIN dbo.FormFactor frm ON frm.FormFactor_ID = brd.FormFactor_ID
   LEFT OUTER JOIN dbo.CPU cpu ON cpu.CPU_ID = cmp.CPU_ID
   LEFT OUTER JOIN dbo.RAM ram ON ram.RAM_ID = cmp.RAM_ID
   LEFT OUTER JOIN dbo.RAMType ram_typ ON ram_typ.RAMType_ID = ram.RAMType_ID
   LEFT OUTER JOIN dbo.HDD hdd ON hdd.HDD_ID = cmp.HDD_ID
   LEFT OUTER JOIN dbo.VGA vga ON vga.VGA_ID = cmp.VGA_ID
   LEFT OUTER JOIN dbo.Chasis ch ON ch.Chasis_ID = cmp.Chasis_ID
   LEFT OUTER JOIN dbo.Power pwr ON pwr.Power_ID = cmp.Power_ID
--------------------------------------------------------------------------------
GO