SQLTeam.com | Weblogs | Forums

Questions for mySQL assignment problems

Hi, I'm new here and I have only 1 day left to deliver my assignment.

I have 5 following questions: And how do I write the code for it?

-- Q6: Ask a query, which shows, which model (in this case "Opel Kadett") is mentioned more than once in the attribute model in bil-table.

-- Q7: one of the mechanics is a veteran. can you find out who is it?: HINT: He worked with most cars that are more than 25 years old.

-- Q8: List all cars, based on their mean price for each reparation.

-- Q9: The Employer in the workshop will like to give a bonus to this mechanic, who earned the most money. Which one should have the bonus?

-- Q10 Poul Wanted a list of other mechanics, who worked in the one quarter of a year, but he is interested only in the first and the last quarter of the year.

Here is my code for mySQL for my assignment.

create table kunde (
kunde_id int not null,
navn char(50) not null,
adresse char(50) not null,
mail char(25) not null,
tlf char(8) not null,
primary key (kunde_id)
);

create table bil (
bil_id int not null,
registreringsnr char(7) not null,
model char(20) not null,
aargang int not null,
kunde_id int not null,
primary key(bil_id),
foreign key (kunde_id) references kunde(kunde_id)
);

create table mekaniker(
mekaniker_id int not null,
navn char(40) not null,
primary key (mekaniker_id)
);

create table reparation(
reparation_id int not null,
dato date not null,
bil_id int not null,
mekaniker_id int not null,
primary key (reparation_id),
foreign key (bil_id) references bil(bil_id),
foreign key (mekaniker_id) references mekaniker(mekaniker_id)
);

create table reservedel(
reservedel_id int not null,
navn char(40) not null,
pris int not null,
primary key (reservedel_id)
);

create table resrep(
resrep_id int not null,
reservedel_id int not null,
reparation_id int not null,
primary key (resrep_id),
foreign key (reservedel_id) references reservedel(reservedel_id),
foreign key (reparation_id) references reparation(reparation_id)
);

insert into kunde values
(1,'Søren Frederiksen','Vindingevej 19 5230 Odense M','sf@hotmail.com','66109131'),
(2,'Henrik Hemmingsen','Karetmagerstien 5 5210 Odense NV','hs@gmail.com','68110916'),
(3,'Asger Johansen','Andedammen 19 5000 Odense C','aj@hotmail.com','75120948'),
(4,'Rasmus Iversen','Kirkestien 5 5250 Odense SV','ri@gmail.com','86198248');

insert into bil values
(1,'MA39604','Renault Clio',2006,1),
(2,'KX78097','Opel Kadett',1998,2),
(3,'MA45647','Renault 4CV',1961,3),
(4,'XP37918','VW Up!',2012,4),
(5,'MH40136','Opel Kadett',2001,3),
(6, 'JS14151', 'Aston Martin DBS', 1956, 4);

insert into mekaniker values
(1,'Palle'),
(2,'Poul'),
(3,'Per');

insert into reservedel values
(1,'Udstoedningsroer',1300),
(2,'Baathorn',600),
(3,'Daek',300),
(4,'Bremser',950),
(5,'Tandrem',6000),
(6,'Olie',300);

insert into reparation values
(1,'2018-10-18',2,1),
(2,'2019-11-23',3,2),
(3,'2019-12-06',5,3),
(4,'2019-12-11',4,2),
(5,'2020-01-02',3,1),
(6, '2017-07-10', 6, 2);

insert into resrep values
(1,2,2),
(2,3,5),
(3,2,5),
(4,1,3),
(5,6,4),
(6,6,6);