top of page

MySQL Assignment Help

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 ;



22 views0 comments

Recent Posts

See All

Comments


bottom of page