In SQL I am trying to Create an inline table function. I will give you my script

Question

In SQL I am trying to Create an inline table function. I will give you my script first then the instructions.

CREATE DATABASE Computer_Inventory_System

CREATE TABLE Brands
(
   BrandKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   Brand varchar(40) NOT NULL,
   Active bit DEFAULT(1) NOT NULL
)

CREATE TABLE ComputerTypes
(
   ComputerTypeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   ComputerType varchar(25) NOT NULL
)

CREATE TABLE ComputerStatuses
(
   ComputerStatusKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   ComputerStatus varchar(50) NOT NULL,
   ActiveStatus bit NOT NULL --an indicator of if this status means the computer is available or not
)

CREATE TABLE CPUTypes
(
   CPUTypeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   CPUType varchar(40) NOT NULL
)

CREATE TABLE Computers
(
   ComputerKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   ComputerTypeKey int NOT NULL,
   BrandKey int NOT NULL,
   ComputerStatusKey int NOT NULL DEFAULT(0),
   PurchaseDate date NOT NULL,
   PurchaseCost money NOT NULL,
   MemoryCapacityInGB int NOT NULL,
   HardDriveCapacityinGB int NOT NULL,
   VideoCardDescription varchar (255),
   CPUTypeKey int NOT NULL,
   CPUClockRateInGHZ decimal (6, 4)
)

SET IDENTITY_INSERT Computers ON
INSERT Computers (ComputerKey, ComputerTypeKey, BrandKey, ComputerStatusKey, PurchaseDate, PurchaseCost, MemoryCapacityInGB,
   HardDriveCapacityinGB, VideoCardDescription, CPUTypeKey, CPUClockRateInGHZ) VALUES
   (1, 1, 1, 0, '1/1/2017', 1999.99, 32, 1024, 'Nvidia 1080', 1, 3.5),
   (2, 2, 4, 0, '1/1/2017', 2399.99, 16, 512, 'Nvidia GeForce GT 650M', 1, 2.5)
SET IDENTITY_INSERT Computers OFF

CREATE TABLE Departments
(
   DepartmentKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   Department varchar(255)
)

SET IDENTITY_INSERT Departments ON
INSERT Departments (DepartmentKey, Department) VALUES
   (1, 'CEO'),
   (2, 'Human Resources'),
   (3, 'Information Technology'),
   (4, 'Accounting')
SET IDENTITY_INSERT Departments OFF

CREATE TABLE Employees
(
   EmployeeKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   LastName varchar(25) NOT NULL,
   FirstName varchar(25) NOT NULL,
   Email varchar(50) NOT NULL,
   Hired date NOT NULL,
   Terminated date NULL,
   DepartmentKey int NOT NULL,
   SupervisorEmployeeKey int NOT NULL --CEO/Top of hierarchy should have their own EmployeeKey
)

SET IDENTITY_INSERT Employees ON
INSERT Employees (EmployeeKey, LastName, FirstName, Email, Hired, DepartmentKey, SupervisorEmployeeKey) VALUES
   (1, 'Ceo', 'John The', 'JCeo@thiscompany.com', '1/1/2017', 1, 1),
   (2, 'Brother', 'Big', 'BBrother@thiscompany.com', '1/1/2017', 2, 1),
   (3, 'Geek', 'Major', 'MGeek@thiscompany.com', '1/1/2017', 3, 1)
SET IDENTITY_INSERT Employees OFF

CREATE TABLE EmployeeComputers
(
   EmployeeComputerKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   EmployeeKey int NOT NULL,
   ComputerKey int NOT NULL,
   Assigned date NOT NULL,
   Returned date NULL
)

SET IDENTITY_INSERT ComputerStatuses ON
   INSERT ComputerStatuses (ComputerStatusKey, ComputerStatus, ActiveStatus) VALUES
      (0, 'New', 1),
      (1, 'Assigned', 1),
      (2, 'Available', 1),
      (3, 'Lost', 0),
      (4, 'In for Repairs', 0),
      (5, 'Retired', 1)
SET IDENTITY_INSERT ComputerStatuses OFF

SET IDENTITY_INSERT CPUTypes ON
INSERT CPUTypes (CPUTypeKey, CPUType) VALUES
   (1, 'AMD'),
   (2, 'Intel'),
   (3, 'Samsung'),
   (4, 'Apple'),
   (5, 'Qualcomm')
SET IDENTITY_INSERT CPUTypes OFF

SET IDENTITY_INSERT ComputerTypes ON
INSERT ComputerTypes (ComputerTypeKey, ComputerType) VALUES
   (1, 'Desktop'),
   (2, 'Laptop'),
   (3, 'Tablet'),
   (4, 'Phone')
SET IDENTITY_INSERT ComputerTypes OFF

SET IDENTITY_INSERT Brands ON
INSERT Brands (BrandKey, Brand) VALUES
   (1, 'Apple'),
   (2, 'Samsung'),
   (3, 'Sony'),
   (4, 'HP'),
   (5, 'Acer'),
   (6, 'NVidia')
SET IDENTITY_INSERT Brands OFF

ALTER TABLE Computers
   ADD CONSTRAINT FK_ComputerComputerTypes
   FOREIGN KEY (ComputerTypeKey)
   REFERENCES ComputerTypes (ComputerTypeKey)

ALTER TABLE Computers
   ADD CONSTRAINT FK_ComputerBrands
   FOREIGN KEY (BrandKey)
   REFERENCES Brands (BrandKey)

ALTER TABLE Computers
   ADD CONSTRAINT FK_ComputerComputerStatus
   FOREIGN KEY (ComputerStatusKey)
   REFERENCES ComputerStatuses (ComputerStatusKey)

ALTER TABLE Computers
   ADD CONSTRAINT FK_ComputerCPUType
   FOREIGN KEY (CPUTypeKey)
   REFERENCES CPUTypes (CPUTypeKey)

ALTER TABLE Employees
   ADD CONSTRAINT FK_EmployeeDepartment
   FOREIGN KEY (DepartmentKey)
   REFERENCES Departments (DepartmentKey)

ALTER TABLE Employees
   ADD CONSTRAINT FK_EmployeeSupervisor
   FOREIGN KEY (SupervisorEmployeeKey)
   REFERENCES Employees (EmployeeKey)

ALTER TABLE EmployeeComputers
   ADD CONSTRAINT FK_EmployeeComputerEmployee
   FOREIGN KEY (EmployeeKey)
   REFERENCES Employees (EmployeeKey)

ALTER TABLE EmployeeComputers
   ADD CONSTRAINT FK_EmployeeComputerComputer
   FOREIGN KEY (ComputerKey)
   REFERENCES Computers (ComputerKey);

CREATE TABLE ComputerAssignments
(
   ComputerAssignmentKey int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   ComputerStatusKey int NOT NULL,
   Added bit DEFAULT(1) NOT NULL,
   Assigned bit DEFAULT(1) NOT NULL,
   Reassigned bit DEFAULT(1) NOT NULL,
   Lost bit DEFAULT(1) NOT NULL,
   StartDate datetime NOT NULL
);

SET IDENTITY_INSERT ComputerAssignments ON
INSERT ComputerAssignments (ComputerAssignmentKey, ComputerStatusKey,
    Added, Assigned, Reassigned, Lost, StartDate) VALUES
    (0, 0, 1, 1, 0, 0, '1/1/2017'),
    (1, 1, 0, 1, 0, 0, '1/13/2017'),
    (2, 2, 1, 0, 0, 0, '2/1/2017'),
    (3, 3, 0, 0, 0, 1, '1/13/2017'),
    (4, 4, 0, 1, 0, 0, '5/8/2017'),
    (5, 5, 0, 0, 0, 0, '6/10/2017')

SET IDENTITY_INSERT ComputerAssignments OFF

ALTER TABLE ComputerAssignments
ADD CONSTRAINT FK_ComputerStatusKeyAssignment
FOREIGN KEY (ComputerStatusKey)
REFERENCES ComputerStatuses (ComputerStatusKey)

SET IDENTITY_INSERT EmployeeComputers ON
INSERT EmployeeComputers (EmployeeComputerKey, EmployeeKey, ComputerKey, Assigned, Returned)
VALUES
(1,1,1, '1/1/2017', NULL),
(2,2,2, '2/1/2017', NULL),
(3,3,2, '4/18/2017', '07/22/2017'),
(4,3,1, '3/18/2017', NULL),
(5,3,1, '4/10/2017', NULL),
(6,2,1, '1/13/2017', '05/12/2017'),
(7,1,1, '10/13/2017', NULL),
(8,1,2, '10/13/2017', NULL),
(9,1,1, '11/03/2016', '02/02/2017')

SET IDENTITY_INSERT EmployeeComputers OFF

-- run this part by itself
CREATE VIEW ComputersForRepair
AS

SELECT B.Brand, CT.ComputerType, CS.ComputerStatus, (E.LastName + ', ' + E.FirstName) AS Employee,
   E.Email, DATEDIFF(dd,EC.Assigned,GETDATE()) as DaysforRepair, C.MemoryCapacityInGB, C.HardDriveCapacityinGB
FROM Computers C INNER JOIN Brands B
   ON C.BrandKey = B.BrandKey
   INNER JOIN ComputerTypes CT
   ON CT.ComputerTypeKey = C.ComputerTypeKey
   INNER JOIN ComputerStatuses CS
   ON CS.ComputerStatusKey = C.ComputerStatusKey
   INNER JOIN EmployeeComputers EC
   ON EC.ComputerKey = C.ComputerKey
   INNER JOIN Employees E
   ON E.EmployeeKey = EC.EmployeeKey
WHERE CT.ComputerTypeKey = '4'

Create an inline table function that accepts a computer brand (or brand key) and returns available computers of that brand back.

Explanation / Answer

Inline Function

Query:

create function fn_computers(@brand nvarchar(10))
RETURNS TABLE
AS
RETURN (SELECT count(BrandKey) FROM computers WHERE BrandKey=@brand )

Fro above in line function use this query to get output

select * from fn_computers ('1')

OR

You can use this below query to get avilible brands

Query

select br.Brand,count(cp.BrandKey) as cc from brands br left join computers cp on br.BrandKey=cp.BrandKey group by cp.BrandKey,br.Brand

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.