How to output accept items for repair


Joined
Nov 18, 2021
Messages
1
Reaction score
0
Can you help me to solve this :

To bring out all accepted items for repair, the deadline for issuance of which is 2 days
Thats the code:

CREATE TABLE Employee (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
);

CREATE TABLE Orders (
ID INT IDENTITY NOT NULL PRIMARY KEY,
Date_of_acceptance DATE,
Date_of_deadline DATE,
Status_order NVARCHAR(50) NOT NULL,
Type_of_repair NVARCHAR (50) NOT NULL,
Price DECIMAL (20),
EmployeeID INT,
CustomerID INT,
ItemsID INT
);


CREATE TABLE Customer (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(20)

);


CREATE TABLE Items (
ID INT IDENTITY NOT NULL PRIMARY KEY,
ItemsName NVARCHAR(50) NOT NULL,
Status_items NVARCHAR(50) NOT NULL
);

CREATE TABLE CompServises (
ID INT IDENTITY NOT NULL PRIMARY KEY,
ServisesName NVARCHAR(50) NOT NULL,
PriceOfServise DECIMAL(30) NOT NULL
);


INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Change of battery', 55);
INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Upgrade motherboard', 550);
INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Repair printers', 45);


ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Items FOREIGN KEY (ItemsID)
REFERENCES Items(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;


INSERT INTO Employee( FirstName,LastName)
VALUES('Yavor', 'Dimitrov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Aleko', 'Ivanov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Boris', 'Petrov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Alex', 'Georgiev');


INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Nikolai', 'Georgiev','0889-125-521' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Viktoriya', 'Dimitrova', '0899-187-784' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Aneliya', 'Petkova', '0885-748-748' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Petyr', 'Stoyanov', '0887-878-777');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Elena', 'Dimitrova', '0895-741-547' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Dimityr', 'Angelov', '0897-546-345');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Alexandra', 'Dimova', '0885-321-764');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Viktor', 'Hristov' , '0874-436-987');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Petya', 'Dimitrova', '0889-765-743');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Atanas', 'Petkov', '0888-463-675');


INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Printer', 'Accept for the repair ');
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Computer', 'Unclaimed');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Laptop', 'Accept for the repair');
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Monitor', 'Unclaimed');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Battery' , 'Unclaimed' );
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Computer' , 'Accept for repair' );


INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price, EmployeeID, CustomerID, ItemsID)
VALUES('2021-11-16', '2021-11-18','Accept','Broken screen of laptop', 100, 1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-10-9', '2021-10-15','Submitted','Broken printer',90,1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-9-3', '2021-9-23','Waiting for delivery of part','Rеplacing the motherboard',500,1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-8-2', '2021-8-26','Accept','Repair a broken computer',600, 2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES('2021-7-12', '2021-7-14','Accept','Change the laptop battery', 120,2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-6-10', '2021-6-18','Submitted','Update windows',30,2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-2-6', '2021-2-21','Waiting for delivery of part',' Rеplacing the motherboard ',500,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-4-3', '2021-4-25','Waiting for delivery of part',' Virus scan',25,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-5-16', '2021-5-30','Accept',' Change the laptop battery',120,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-3-5', '2021-3-5','Submitted','Update windows',30,4,4,4);
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top