SQLize | PHPize | SQLtest

create table cars ( model_id int primary key, model_name varchar(100), color varchar(100), brand varchar(100) ); insert into cars values(1,'Leaf', 'Black', 'Nissan'); insert into cars values(2,'Leaf', 'Black', 'Nissan'); insert into cars values(3,'Model S', 'Black', 'Tesla'); insert into cars values(4,'Model X', 'White', 'Tesla'); insert into cars values(5,'Ioniq 5', 'Black', 'Hyundai'); insert into cars values(6,'Ioniq 5', 'Black', 'Hyundai'); insert into cars values(7,'Ioniq 6', 'White', 'Hyundai'); insert into cars values(8,'Ioniq 6', 'White', 'Hyundai'); insert into cars values(9,'Ioniq 6', 'White', 'Hyundai'); insert into cars values(10,'Ioniq 6', 'White', 'Hyund'); --remove duplicate record delete from cars where model_id in (select max(model_id) from cars group by model_name, color, brand); select * from cars; --- Q3 : Find actual distance --- --drop table car_travels; create table car_travels ( cars varchar(40), days varchar(10), cumulative_distance int ); insert into car_travels values ('Car1', 'Day1', 50); insert into car_travels values ('Car1', 'Day2', 100); insert into car_travels values ('Car1', 'Day3', 200); insert into car_travels values ('Car2', 'Day1', 0); insert into car_travels values ('Car3', 'Day1', 0); insert into car_travels values ('Car3', 'Day2', 50); insert into car_travels values ('Car3', 'Day3', 50); insert into car_travels values ('Car3', 'Day4', 100); select * from car_travels; select cars, days,cumulative_distance, lag(cumulative_distance,1,0) over(partition by cars order by days asc) as cd from car_travels order by cars,days; --q2higest and lowest employee record drop table if exists employee; create table employee ( id int primary key, name varchar(100), dept varchar(100), salary int ); insert into employee values(1, 'Alexander', 'Admin', 6500); insert into employee values(2, 'Leo', 'Finance', 7000); insert into employee values(3, 'Robin', 'IT', 2000); insert into employee values(4, 'Ali', 'IT', 4000); insert into employee values(5, 'Maria', 'IT', 6000); insert into employee values(6, 'Alice', 'Admin', 5000); insert into employee values(7, 'Sebastian', 'HR', 3000); insert into employee values(8, 'Emma', 'Finance', 4000); insert into employee values(9, 'John', 'HR', 4500); insert into employee values(10, 'Kabir', 'IT', 8000); select *from employee order by dept; select id, name, dept, salary from (select * ,max(salary) over (partition by dept order by salary desc) mx, min(salary) over (partition by dept order by salary asc) mn from employee) e where salary=mx or salary=mn order by dept;