dbms lab 3
CREATE DATABASE dblab;
USE dblab;
/* CREATING A TABLE STUDENT */
CREATE TABLE student(
RollNO CHAR(8) PRIMARY KEY,
SName VARCHAR(20),
Gender CHAR(1),
City VARCHAR(20)
);
CREATE TABLE Course(
CCode CHAR(5) PRIMARY KEY,
CName VARCHAR(20),
Credit SMALLINT
);
CREATE TABLE enrolled(
RollNo CHAR(8),
CCode CHAR(5),
YoE INT
);
ALTER TABLE enrolled ADD FOREIGN KEY(RollNo) REFERENCES student(RollNo) ON DELETE CASCADE;
ALTER TABLE enrolled ADD FOREIGN KEY(CCode) REFERENCES Course(CCode) ON DELETE SET NULL;
/* 1A) adding colums to table */
ALTER TABLE Course ADD COLUMN CType VARCHAR(20) ;
ALTER TABLE student ADD COLUMN Email VARCHAR(30) UNIQUE;
DESCRIBE Course;
/* 1B) FIND THE NAME OF ALL COURSE WITH "CS" CODE */
/* WE NEED TO INSERT SOME VALUES TO COURSE TABLE IN ORDER TO SEARCH */
INSERT INTO Course VALUES('CS354' , 'DBMS' , 4 , 'Core');
INSERT INTO Course VALUES('CS355' , 'ENGLISH' , 3 , 'Elective');
INSERT INTO Course VALUES('CS356' , 'PROBABILITY' , 4 , 'Core');
INSERT INTO Course VALUES('CS357' , 'COA' , 4 , 'Core');
INSERT INTO Course VALUES('CS358' , 'DATA SCIENCE' , 3 , 'Open Elective');
INSERT INTO Course VALUES('ME304' , 'MATERIAL SCIENCE' , 4 , 'Core');
INSERT INTO Course VALUES('ME305' , 'PHYSICE OF MATERIALS' , 3 , 'Core');
INSERT INTO Course VALUES('ME306' , 'AI' , 3 , 'Open Elective');
INSERT INTO Course VALUES('ME307' , 'STD' , 3 , 'Core');
INSERT INTO Course VALUES('CS367' , 'ENVIRONMENTAL' , 1 , 'Oprn Elective');
INSERT INTO Course VALUES('ME309' , 'HISTORY' , 1 , 'Open Elective');
INSERT INTO Course VALUES('CS554' , 'PSP' , 3 , 'Core');
INSERT INTO Course VALUES('CS558' , 'LA' , 3 , 'Elective');
INSERT INTO Course VALUES('CS555' , 'DSD' , 4 , 'Core');
INSERT INTO Course VALUES('CS557' , 'COA' , 3 , 'Core');
INSERT INTO Course VALUES('ME506' , 'MS' , 2 , 'Elective');
INSERT INTO Course VALUES('ME507' , 'SOCIOLOGY' , 1 , 'Open Elective');
SELECT * FROM Course WHERE CCode LIKE 'CS%';
/1C) FIND THE NAME OF MALE STUDENT WHO NAME HAS TWO AS/
INSERT INTO student VALUES('2020CS91' , 'MAHIDHAR REDDY' , 'M' , 'KURNOOL' , '[email protected]' );
INSERT INTO student VALUES('2019CS91' , 'MOHITH' , 'M' , 'CHENNAI' , '[email protected]' );
INSERT INTO student VALUES('2020CS92' , 'SUCHITHRA' , 'F' , 'SELUM' , '[email protected]' );
INSERT INTO student VALUES('2020CS96' , 'KIRAN' , 'M' , 'KURNOOL' , '[email protected]' );
INSERT INTO student VALUES('2018CS90' , 'NISHITHA' , 'F' , 'BANGALORE' , '[email protected]' );
INSERT INTO student VALUES('2020ME91' , 'SURENDHRA RAO' , 'M' , 'CHENNAI' , '[email protected]' );
INSERT INTO student VALUES('2021ME95' , 'SURESH KUMAR' , 'M' , 'KADAPA' , '[email protected]' );
INSERT INTO student VALUES('2020ME71' , 'SUMITHRA' , 'F' , 'CHENNAI' , '[email protected]' );
INSERT INTO student VALUES('2018ME41' , 'MADHAVAN' , 'M' , 'SELUM' , '[email protected]' );
INSERT INTO student VALUES('2017CS91' , 'MAHESH' , 'M' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('1901CS91' , 'MURALI' , 'M' , 'CHENNAI' , '[email protected]' );
INSERT INTO student VALUES('2020ME01' , 'ROOMA' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020ME02' , 'HARINI' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020ME03' , 'HARSHITHA' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020ME04' , 'HARSHITHA REDDY' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020CS04' , 'PRIYANKA' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020CS05' , 'ANUSHKA' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020CS06' , 'SARAWATI' , 'F' , 'DELHI' , '[email protected]' );
INSERT INTO student VALUES('2020CS07' , 'SARAWATI NAIDU' , 'F' , 'DELHI' , '[email protected]' );
SELECT * FROM student WHERE Gender = 'M' AND SName LIKE '%A%A%';
/*1D) FIND THE STUDENTS WHO HAVE NOT ENROLLED IN BETWEEN 2018 - 2019 */
INSERT INTO enrolled VALUES('2020CS91' , 'CS354' , 2020);
INSERT INTO enrolled VALUES('2020CS91' , 'CS355' , 2020);
INSERT INTO enrolled VALUES('2020CS91' , 'CS356' , 2020);
INSERT INTO enrolled VALUES('2020CS91' , 'CS357' , 2020);
INSERT INTO enrolled VALUES('2020CS91' , 'CS554' , 2020);
INSERT INTO enrolled VALUES('2018CS90' , 'CS358' , 2018);
INSERT INTO enrolled VALUES('2018CS90' , 'CS357' , 2018);
INSERT INTO enrolled VALUES('2018CS90' , 'CS356' , 2018);
INSERT INTO enrolled VALUES('2020CS92' , 'CS355' , 2020);
INSERT INTO enrolled VALUES('2020CS92' , 'CS354' , 2020);
INSERT INTO enrolled VALUES('2020CS92' , 'CS356' , 2020);
INSERT INTO enrolled VALUES('2019CS91' , 'CS357' , 2019);
INSERT INTO enrolled VALUES('2020ME71' , 'ME306' , 2020);
INSERT INTO enrolled VALUES('2018ME41' , 'ME306' , 2018);
INSERT INTO enrolled VALUES('2017CS91' , 'CS354' , 2017);
INSERT INTO enrolled VALUES('2021ME95' , 'ME307' , 2021);
INSERT INTO enrolled VALUES('2019CS91' , 'CS358' , 2019);
INSERT INTO enrolled VALUES('2019CS91' , 'CS355' , 2019);
INSERT INTO enrolled VALUES('2019CS91' , 'CS354' , 2019);
INSERT INTO enrolled VALUES('2019CS91' , 'CS356' , 2019);
INSERT INTO enrolled VALUES('2020CS91' , 'CS554' , 2020);
INSERT INTO enrolled VALUES('2018CS90' , 'CS558' , 2018);
INSERT INTO enrolled VALUES('2020CS92' , 'CS555' , 2020);
INSERT INTO enrolled VALUES('2020CS91' , 'CS557' , 2020);
INSERT INTO enrolled VALUES('2020ME71' , 'ME506' , 2020);
INSERT INTO enrolled VALUES('2018ME41' , 'ME506' , 2018);
INSERT INTO enrolled VALUES('2017CS91' , 'CS554' , 2017);
INSERT INTO enrolled VALUES('2021ME95' , 'ME507' , 2021);
INSERT INTO enrolled VALUES('2020ME01' , 'ME306' , 2020);
INSERT INTO enrolled VALUES('2020CS04' , 'CS354' , 2020);
INSERT INTO enrolled VALUES('2020ME02' , 'ME307' , 2020);
INSERT INTO enrolled VALUES('2020CS05' , 'CS354' , 2020);
INSERT INTO enrolled VALUES('2020ME03' , 'ME306' , 2020);
INSERT INTO enrolled VALUES('2020CS04' , 'CS355' , 2020);
INSERT INTO enrolled VALUES('2020ME04' , 'ME306' , 2020);
INSERT INTO enrolled VALUES('2020CS06' , 'CS355' , 2020);
INSERT INTO enrolled VALUES('2020CS07' , 'CS355' , 2020);
INSERT INTO enrolled VALUES('1901CS91' , 'CS554' , 2019);
INSERT INTO enrolled VALUES('1901CS91' , 'CS354' , 2019);
INSERT INTO enrolled VALUES('1901CS91' , 'CS355' , 2019);
INSERT INTO enrolled VALUES('1901CS91' , 'CS558' , 2019);
SELECT * FROM enrolled WHERE YoE > 2019 OR YoE <2018;
/*1E) FING THE COURSE WITH LEAST CREDIT */
SELECT MIN(Credit) FROM Course;
/*1F) FIND THE NAME OF COURSE WHICH IS NOT ENROLLED BY ANY STUDENTS IN 2020 */
SELECT CCode FROM Course WHERE CCode NOT IN(SELECT CCode FROM enrolled WHERE YoE = 2020);
/* 1G) FIND THE TOTAL NO STUDENTS CITY WISE */
SELECT COUNT(City) , City FROM student GROUP BY City;
/* 1h) SELECT STUDENTS WHO HAVE ENROLLED FOR GREATER THAN 5 COURSES */
SELECT * FROM student WHERE RollNo IN (SELECT RollNo FROM enrolled GROUP BY RollNo HAVING COUNT(DISTINCT CCode)>=5);
SELECT RollNo , COUNT(DISTINCT CCode) FROM enrolled GROUP BY RollNo;
/* 1I) FIND TOTAL NO COURSES ENROLLED BY STUDENT */
SELECT COUNT(DISTINCT CCode) FROM enrolled WHERE RollNo = '1901CS91';
/* 1j) display all courses with starts with 5 */
SELECT * FROM Course WHERE CCode LIKE '%5__';
/* 1k) count the no of students having cs as rooll no */
SELECT COUNT(RollNo) FROM student WHERE RollNo LIKE '%CS%';
/* 1L)FIND THE ROLL NO OF STUDENTS WHO HAVE GREATER THAN 4 COURSE IN 2020 */
SELECT RollNo FROM student WHERE RollNo IN(SELECT RollNo FROM enrolled WHERE YoE = 2020 GROUP BY RollNo HAVING COUNT(DISTINCT CCode)>=4);
/* 1M FEMALE STUDENTS THEY ARE IN DELHI SELETS MAXIMUM COURSE CODE */
SELECT CCode FROM enrolled WHERE RollNo IN (SELECT RollNo FROM student WHERE Gender = 'F' AND City = 'DELHI') GROUP BY CCode ORDER BY COUNT(DISTINCT RollNo) DESC LIMIT 1;
/* 1N) STUDENTS HAVING DOMAIN NAME AS FOLLOWS */
SELECT SName FROM student WHERE Email LIKE '%GMAIL.COM' OR '%YAHOO.COM';