top of page

MySQL Assignment Help

Updated: Jun 30, 2020

In this blog, we will write some MySQL queries to find the missing value in the given interval.

Here list of integer values are given values:


1, 2, 5, 6, 7, 10, 11, 12


Query to create the table and insert values into this tables:


Query 1 :


mysql> create table input(

id int not null primary key

);

insert into input(id) values (1),(2),(5),(6), (10), (11), (12);


Solution:

It displays missing value with a range between the last value.


mysql> select start, end from (

select m.id + 1 as start,

(select min(id) - 1 from input as x where x.id > m.id) as end

from input as m

left outer join input as r on m.id = r.id - 1

where r.id is null

) as x

where end is not null;


Output:


3 4

7 9


It displays missing value with the range including last set(any default)value(16).


Solution:


select x.start, ( case when (x.end is null) then 16 else x.end end) as end from (

select m.id + 1 as start,

(select min(id) - 1 from input as x where x.id > m.id) as end

from input as m

left outer join input as r on m.id = r.id - 1

where r.id is null

) as x;


Output


3 4

7 9

13 16


Query 2 :


Overlapping order in the given time periods


In this query, we will find the:

mysql> create table order(id integer, item varchar(100), starttime time, endtime time); insert into order(id, item, starttime, endtime) values(1, "Cake", 083000, 113000); insert into order(id, item, starttime, endtime) values(2, "Chicken", 110000, 123000); insert into order(id, item, starttime, endtime) values(3, "Chicken", 114000, 010000); insert into (id, item, starttime, endtime) values(4, "Steak", 080000, 100000); select * from order;


Solution:


mysql> update order set endtime = TIME_FORMAT('12:59:00', '%H:%i:%s') where id = 3;

select t1.id,(select count(*) from order as t2 where ( (t2.cooktime >= t1.order) and (t2.starttime <= t1.endtime) and (t2.id <> t1.id) ) or ( (t2.endtime >= t1.starttime) and (t2.endtime <= t1.endtime) and (t2.id <> t1.id) ) ) as maxcount from order as t1 ;



Comments


bottom of page