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

insert into reservedel values

insert into reparation values
(6, '2017-07-10', 6, 2);

insert into resrep values