assienment 3

create database Durjoy3


create table Course(
CID nvarchar(10) not null primary key,
CName nvarchar(20) not null,
Semester nvarchar(20) not null
)

create table Student(
SID nvarchar(15) not null primary key,
SName nvarchar(30) not null,
CGPA float not null,
Age int not null
)

create table Takes(
SID nvarchar(15) foreign key references Student(SID),
CID nvarchar(10) foreign key references Course(CID)
)

select * from Course
select * from Student
select * from Takes

insert into Course values('CSE101','DATA','SPRING 2020')
insert into Course values('CSE102','MATH','SPRING 2020')
insert into Course values('CSE103','ART OF LIVING','SPRING 2020')
insert into Course values('CSE104','COMPUTER-I','SPRING 2020')
insert into Course values('CSE105','COMPUTER-II','SPRING 2020')
insert into Course values('CSE106','DATA SCIENCE','SPRING 2020')

insert into Student values('181-15-11017','Alimozzaman Durjoy',3.6,23)
insert into Student values('181-15-11018','Azizul Islam',3.46,17)
insert into Student values('181-15-11019','Didar',3.00,19)
insert into Student values('181-15-11020','Anika Hossain',3.89,22)
insert into Student values('181-15-11021','Md Hasan',3.3,24)
insert into Student values('181-15-11022','Asaduzzaman',3.8,19)

insert into Takes values('181-15-11017','CSE101')
insert into Takes values('181-15-11018','CSE105')
insert into Takes values('181-15-11019','CSE102')
insert into Takes values('181-15-11020','CSE101')
insert into Takes values('181-15-11021','CSE101')
insert into Takes values('181-15-11022','CSE106')

#1

alter table student add Email nvarchar(50) check(len(Email)>(10))

#2
select count(SID) as NumberOfStudent from Student where CGPA>3.5 and age>18

#3
select SName,CGPA from Student where CGPA > (select CGPA from Student where SName= 'Azizul Islam')

#4
select avg(Age) as Avarage_age from Student where SName like 'A%'