progress.sql

CREATE DATABASE PROGRESS;

USE PROGRESS;

CREATE TABLE STUDENT(
	Srollno varchar(50),
	Sname varchar(20),
	Sage int CHECK(Sage>0),
	Sgender varchar(20),
	Saddress varchar(50),
	advisor varchar(50),
	PRIMARY KEY(Srollno)
	);
INSERT INTO STUDENT VALUES('S01','Hari',20,'Male','Sreehari','Alfred');
INSERT INTO STUDENT VALUES('S02','Rahul',21,'Male','Manacadu','AnuBonia');
INSERT INTO STUDENT VALUES('S03','Lekshmi',20,'Female','Sreehari','Unni');
INSERT INTO STUDENT VALUES('S04','Ravi',22,'Male','Konchiravila','Vidhya');

CREATE TABLE COURSE(
	courseid varchar(50),
	coursename varchar(50),
	coursecredits int,
	courseduration varchar(50),
	PRIMARY KEY(courseid)
	);

INSERT INTO COURSE VALUES('C01','FLAT',4,'1year');
INSERT INTO COURSE VALUES('C02','DBMS',4,'2year');
INSERT INTO COURSE VALUES('C03','SS',4,'1year');
INSERT INTO COURSE VALUES('C04','MPMC',4,'3year');

CREATE TABLE TEACHER(
	teacherid varchar(50),
	teachername varchar(50),
	specilization varchar(50),
	phoneno BIGINT CONSTRAINT tendigits CHECK(phoneno BETWEEN 1000000000 AND 9999999999),
	PRIMARY KEY(teacherid)
	);

INSERT INTO TEACHER VALUES('T01','Alfred','Proffessor',9656990468);
INSERT INTO TEACHER VALUES('T02','AnuBonia','Asst Proffessor',9447000162);
INSERT INTO TEACHER VALUES('T03','Unni','Assoc Proffessor',9446459948);
INSERT INTO TEACHER VALUES('T04','PPT','Proffessor',9496909948);

CREATE TABLE ENROLLMENT(
	Srollno varchar(50),
	courseid varchar(50),
	grade varchar(50),
	PRIMARY KEY(Srollno,courseid),
	FOREIGN KEY (Srollno) REFERENCES STUDENT(Srollno) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(courseid) REFERENCES COURSE(courseid) ON DELETE CASCADE ON UPDATE CASCADE
	);
INSERT INTO ENROLLMENT VALUES('S01', 'C01', 'A+');  
INSERT INTO ENROLLMENT VALUES('S01', 'C02', 'B');   
INSERT INTO ENROLLMENT VALUES('S02', 'C02', 'B+');  
INSERT INTO ENROLLMENT VALUES('S03', 'C03', 'A+');  
INSERT INTO ENROLLMENT VALUES('S04', 'C04', 'C+');

SELECT * FROM STUDENT WHERE Sgender='male';

SELECT S.Srollno, S.SName FROM Student S JOIN Enrollment E ON S.Srollno = E.Srollno WHERE courseid=NULL; 

 select * from student where Srollno NOT IN (select Srollno from enrollment);

SELECT c.coursename, AVG(Sage) AS average_age FROM STUDENT s  JOIN ENROLLMENT e ON s.Srollno = e.Srollno  JOIN COURSE c ON c.courseid = e.courseid  GROUP BY c.courseid;