Making Queries for relaional model

Question

I have some troubles with making queries for this task. anyone please help me.

Cars(License, Brand, Year);
Employees(EID, Firstname, Lastname, Wage);
Customers(CID, Firstname, Lastname, Phone);
OfficeStaff(EID, OfficeNumber);
Own(CID, License);
Mechanic(EID, HourlyPrice);
Repairs(License, EID, PartCost, Hours);

Create SQL statements performing the following tasks:

(a) Create the OfficeStaff-table while taking into account that the OfficeNumber may not be NULL and must be in the range [1..10].

(b) Find the name(s), i.e. firstname(s) and lastname(s), of the owner(s) of the car(s), which have been repaired for the most times.

(c) Find the average number of hours spent (i.e. Repairs.Hours) repairing cars of brand “Opel”.

(d) Update the HourlyPrice to be 20 EUR for all mechanics with a wage of 100 EUR or more.

My tries: (a) Create the OfficeStaff-table while taking into account that the OfficeNumber may not be NULL and must be in the range [1..10].

CREATE TABLE OfficeStaff (
 EID INT PRIMARY KEY,
 Firstname TEXT,
 Lastname TEXT,
 Wage REAL,
 OfficeNumber INT NOT NULL,
 CONSTRAINT CK_OfficeNumber CHECK (OfficeNumber BETWEEN 1 AND 10)

b) no idea?!

(c) Find the average number of hours spent (i.e. Repairs.Hours) repairing cars of brand “Opel”.

SELECT AVG(R.Hours)
FROM Repairs R, Cars C
WHERE R.License = C.License AND C.Brand = “Opel”

(d) Update the HourlyPrice to be 20 EUR for all mechanics with a wage of 100 EUR or more.

UPDATE Mechanic
SET HourlyPrice = 20
WHERE Wage >= 100

(a) Create the OfficeStaff-table while taking into account that the OfficeNumber may not be NULL and must be in the range [1..10]. CREATE TABLE OfficeStaff ( EID INT PRIMARY KEY, Firstname TEXT, Lastname TEXT, Wage REAL, OfficeNumber INT NOT NULL, CONSTRAINT CK_OfficeNumber CHECK (OfficeNumber BETWEEN 1 AND 10) Is this valid for a)??? — The.rabbit.coder, Jun 01 '15 at 18:08
(c) Find the average number of hours spent (i.e. Repairs.Hours) repairing cars of brand “Opel”. SELECT AVG(R.Hours) FROM Repairs R, Cars C WHERE R.License = C.License AND C.Brand = “Opel” — The.rabbit.coder, Jun 01 '15 at 18:10
Please can you post this code to the original post, it can be edited) — Rocketq, Jun 01 '15 at 18:11
(d) Update the HourlyPrice to be 20 EUR for all mechanics with a wage of 100 EUR or more. UPDATE Mechanic SET HourlyPrice = 20 WHERE Wage >= 100 — The.rabbit.coder, Jun 01 '15 at 18:11
I already tried that but my postgre gave me errors. How would you think of a query that could get me that result, just so I can see the result in postgre — The.rabbit.coder, Jun 01 '15 at 18:18
My teacher does not care about the syntax. I have mySQL and postgre. — The.rabbit.coder, Jun 01 '15 at 18:19

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

(a) how to create can look here

CREATE TABLE OfficeStaff (
 EID INT PRIMARY KEY,
 Firstname varchar(100),
 Lastname varchar(100),
 Wage decimal(15,2),
 OfficeNumber INT NOT NULL,
 CONSTRAINT CK_OfficeNumber CHECK (OfficeNumber BETWEEN 1 AND 10)
 )

(b) Not sure about this one, but you have to use rank to get not only 1 value among same values. For this you can look here

    WITH cte AS (
   SELECT a.Firstname, a.Lastname, rank() OVER (ORDER BY count(c.Hours)) AS rnk
   from Customers as a
   join Own as b
   on a.CID=b.CID
   join Repairs as c
   on b.License = c.License
   group by a.Firstname, a.Lastname
   )
SELECT *
FROM   cte
WHERE  rnk <= 1;

(c) join usage here

SELECT AVG(R.Hours)
FROM Repairs R
 join Cars C
 on R.license=C.license
WHERE C.Brand = 'Opel'

(d) update usage here and here

UPDATE Mechanic
    SET HourlyPrice = 20
    from Employees
    WHERE Mechanic.EID = Employees.EID
    AND Employees.Wage >= 100

edited May 23 '17 at 11:51

Community

1
1

answered Jun 01 '15 at 18:24

Rocketq

5,423
23
75
126

Okay, that works perfektly! Thanks, but what about (b) and (d). Those are the ones that annoys me to the limit here. – The.rabbit.coder Jun 01 '15 at 18:27
@genger-man Please read all material I have provided for your own knowledge – Rocketq Jun 01 '15 at 18:56
I am already doing that by the moment. Thank you alot for your time. Really appreciated! – The.rabbit.coder Jun 01 '15 at 19:24

Making Queries for relaional model

1 Answers1