Click here to Skip to main content
15,178,226 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Can you help me to solve this query: Perform any repairs performed by a technician.

CREATE DATABASE Computer_servise;

USE Computer_servise;

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
);


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);


What I have tried:

SQL
SELECT Type_of_repair, Price, FirstName,LastName
FROM Orders, Employee
WHERE FirstName  LIKE 'Yavor%'
AND Orders.ID=Employee.ID


How can I see which row from this :
SQL
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);

to whose employee he corresponds.
Posted
Updated 18-Nov-21 3:04am

Aside from the fact that you're using an old-style join, you're joining on the wrong field. You should be joining Orders.EmployeeID to Employee.ID, but you're actually joining Orders.ID to Employee.ID instead.
SQL
SELECT
    O.Type_of_repair,
    O.Price,
    E.FirstName,
    E.LastName
FROM
    Orders As O
    INNER JOIN Employees As E
    ON E.ID = O.EmployeeID
WHERE
    E.FirstName Like 'Yavor%'
;
   
Comments
CHill60 18-Nov-21 8:05am
   
Good spot (which I missed!) - 5'd
You and what I can only assume is your classmate Snitch=Stich need to study how to use JOIN
See Visual Representation of SQL Joins[^]

Note: The way you are currently joining is very old-fashioned and does not cater for left outer joins. Instead of
SQL
SELECT Type_of_repair, Price, FirstName,LastName
FROM Orders, Employee
WHERE FirstName  LIKE 'Yavor%'
AND Orders.ID=Employee.ID
use
SQL
SELECT Type_of_repair, Price, FirstName,LastName
FROM Orders 
inner join Employee  ON Orders.ID=Employee.ID
WHERE FirstName  LIKE 'Yavor%';
So to get the employee information for 'Broken screen of laptop' just change your WHERE clause
SQL
SELECT Type_of_repair, Price, FirstName,LastName
FROM Orders 
inner join Employee  ON Orders.EmployeeID=Employee.ID
WHERE Type_of_repair = 'Broken screen of laptop';
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900