Database Management System Lab Final

143010200006_final_lab.sql

CREATE DATABASE `dbms`;

USE `dbms`;

-- 1
CREATE TABLE `college`(
	`cname` varchar(50),
	`state` varchar(10),
	`enrollment` INT
);

-- 4
CREATE TABLE `student`(
	`sid` INT AUTO_INCREMENT,
	`sname` varchar(20),
	`gpa` INT CHECK(gpa>=0 AND gpa<=4),
	PRIMARY KEY(`sid`)
);

-- 7
CREATE TABLE `apply`(
	`sid` INT AUTO_INCREMENT,
	`cname` varchar(50),
	`major` varchar(10) UNIQUE,
	`decision` varchar(10),
	FOREIGN KEY(`sid`) REFERENCES `student`(`sid`)
);

-- 2
DESCRIBE `college`;
DESCRIBE `student`;
DESCRIBE `apply`;

-- 3
INSERT INTO `college` VALUES(
	'UITS', 'Baridhara', 100
);

INSERT INTO `college` VALUES(
	'Cambrian', 'Baridhara', 500
);

INSERT INTO `college` VALUES(
	'Boston', 'Tangail', 1000
);

INSERT INTO `student` VALUES(
	NULL, 'Ohaidur', 2
);

INSERT INTO `student` VALUES(
	NULL, 'Rafi', 3
);

INSERT INTO `student` VALUES(
	NULL, 'Nafi', 4
);

INSERT INTO `apply` VALUES(
	NULL, 'MIT', 'CSE', 'Yes'
);

INSERT INTO `apply` VALUES(
	NULL, 'Cambrian', 'Science', 'No'
);

INSERT INTO `apply` VALUES(
	NULL, 'Sristy', 'Commerce', 'Yes'
);

-- 5
SELECT * FROM `student` ORDER BY `gpa`;

-- 6
SELECT student.sname, apply.major FROM `student` INNER JOIN  `apply` ON student.sid = apply.sid;

-- 8
SELECT student.sname, student.gpa FROM `student` INNER JOIN  `apply` ON student.sid = apply.sid WHERE apply.major = 'CSE' AND apply.cname = 'MIT';

-- 9
SELECT * FROM `college`;
SELECT * FROM `student`;
SELECT * FROM `apply`;

-- 10
UPDATE `college` SET `cname` = 'Pentagon' WHERE `cname` = 'Boston';

SELECT * FROM `college`;

DROP DATABASE `dbms`;