/*---Query 4:

--From the doctors table, fetch the details of doctors who work in the same hospital but in different speciality.

--Table Structure:

create table doctors
(
id int primary key,
name varchar(50) not null,
speciality varchar(100),
hospital varchar(50),
city varchar(50),
consultation_fee int
);

insert into doctors values
(1, 'Dr. Shashank', 'Ayurveda', 'Apollo Hospital', 'Bangalore', 2500),
(2, 'Dr. Abdul', 'Homeopathy', 'Fortis Hospital', 'Bangalore', 2000),
(3, 'Dr. Shwetha', 'Homeopathy', 'KMC Hospital', 'Manipal', 1000),
(4, 'Dr. Murphy', 'Dermatology', 'KMC Hospital', 'Manipal', 1500),
(5, 'Dr. Farhana', 'Physician', 'Gleneagles Hospital', 'Bangalore', 1700),
(6, 'Dr. Maryam', 'Physician', 'Gleneagles Hospital', 'Bangalore', 1500);

/*select * from doctors d1
join doctors d2
on d1.id<>d2.id
and d1.Hospital = d2.Hospital
and d1.speciality <> d2.speciality*/

/*select * from doctors d1
join doctors d2
on d1.id<>d2.id
and d1.Hospital = d2.Hospital*/


/*-- Query 5:

--From the login_details table, fetch the users who logged in consecutively 3 or more times.

--Table Structure:

create table login_details(
login_id int primary key,
user_name varchar(50) not null,
login_date date);


insert into login_details values
(101, 'Michael', current_date),
(102, 'James', current_date),
(103, 'Stewart', current_date+1),
(104, 'Stewart', current_date+1),
(105, 'Stewart', current_date+1),
(106, 'Michael', current_date+2),
(107, 'Michael', current_date+2),
(108, 'Stewart', current_date+3),
(109, 'Stewart', current_date+3),
(110, 'James', current_date+4),
(111, 'James', current_date+4),
(112, 'James', current_date+5),
(113, 'James', current_date+6);


select distinct user_name
from(
select user_name, lead(user_name) over(order by login_id) as first_consecuitve,
lead(user_name,2) over(order by login_id) as second_consecuitve
from login_details)x
where x.user_name = x.first_consecuitve
and x.user_name = x.second_consecuitve
*/

-- Query 6:

--From the students table, write a SQL query to interchange the adjacent student names.

--Note: If there are no adjacent student then the student name should stay the same.

--Table Structure:

create table students
(
id int primary key,
student_name varchar(50) not null
);
insert into students values
(1, 'James'),
(2, 'Michael'),
(3, 'George'),
(4, 'Stewart'),
(5, 'Robin');

select id, student_name,
case when id%2!=0 then lead(student_name,1, student_name)over(order by id)
    when id%2=0 then lag(student_name)over(order by id)
    else student_name
end as new_student_name
from students;