Output all repairs in SQL

Question

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 18-Nov-21 1:45am

Megan2000

Updated 18-Nov-21 2:04am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2021-11-18T01:57:00

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%'
;

CHill60 · Answer 2 · 2021-11-18T02:04:00

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

Output all repairs in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0