Database2

create database DIU

create table basic(id int, name varchar(25),gender varchar(25),birthdate varchar(25),hometown varchar(25))
 insert into basic(id,name,gender,birthdate,hometown)
values(10121,'mamum','male','22-01-1995','rajshahi'),
(10453,'nitin','male','24-05,1997','feni'),
(10124,'sadia','female','15-09,1996','netrokona'),
(10563,'salma','female','02-03,1997','manikgonj'),
(10673,'shoriful','male','14-04,1996','comilla')


create table waiver(id int,semester varchar(25),result float,waiver float )
insert into waiver(id,semester,result,waiver)
values(10453,'summer19',3.94,30),
(10121,'fall20',3.88,20),
(10793,'fall20',3.00,50),
(10124,'spring20',3.93,35),
(10047,'summer19',2.56,0.0),
(10673,'spring20',2.85,00)



create table course(serial int ,course varchar(25),credit int)
insert into course(serial,course,credit)
values(10124,'pps',3),
(10058,'pps Lab',2),
(9799,'pps',2),
(10563,'pps',2)



select * from basic
select * from waiver
select * from course


select UPPER(name)as name,UPPER (hometown) as distict from basic

select name as first_name from basic
select name +' is '+gender+' from '+hometown from basic

select COUNT(*) as count_Operation from waiver where result>3.5

select *  from waiver where result>=2 and result <=3.5
select *  from waiver where result between 2 and 3.5

select max(waiver) as sumation from waiver
select sum(waiver)from waiver
select min(waiver)from waiver
select avg(waiver)from waiver
select waiver from waiver where waiver< (select avg(waiver)from waiver)
select credit from course where course='pps' and credit=(select max(credit)from course)

select max(result)from waiver where semester='fall20'
select semester,max(result) as result from waiver group by semester order by result asc

select name,waiver,result,birthdate from basic,waiver,course where basic.id=waiver.id and basic.id = course.serial
select * from waiver,basic,course where basic.id=waiver.id and basic.id = course.serial



select * from basic  where name like 'm%m'
select * from basic,waiver where basic.id=waiver.id and basic.id=10121 or(waiver=30 and name='nitin')


select  name ,basic.id ,result as gpa from basic,waiver where basic.id=waiver.id and  name in('nitin','sdia')
select waiver from waiver where waiver< (select avg(waiver)from waiver)

select * from waiver,basic,course where basic.id=waiver.id and basic.id = course.serial and (basic.id=10121 or name='sadia')

select * from waiver where (waiver< (select avg(waiver)from waiver)or waiver.id=10124)