CSE 3110, Lab 4

143010200006.sql

-- 1
CREATE TABLE `teachers` (
  `teacher_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `designation` VARCHAR(50) NOT NULL,
  `department` VARCHAR(50) NOT NULL,
  `phone_number` INT NOT NULL,
  PRIMARY KEY(`teacher_id`)
);

-- 1, 4, 5
CREATE TABLE `students` (
  `student_id` INT NOT NULL CHECK(student_id>1207001 AND student_id<1207060),
  `name` VARCHAR(50) NOT NULL,
  `year` TIMESTAMP NOT NULL DEFAULT NOW(),
  `department`VARCHAR(50) NOT NULL DEFAULT 'CSE',
  `cgpa` VARCHAR(10) NOT NULL,
  PRIMARY KEY(`student_id`)
);

-- 1, 3
CREATE TABLE `course` (
  `course_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL UNIQUE,
  `credit` INT NOT NULL,
  PRIMARY KEY(`course_id`)
);

-- 1
CREATE TABLE `tea_cou` (
  `teacher_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  FOREIGN KEY(`teacher_id`) REFERENCES `teachers`(`teacher_id`) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY(`course_id`) REFERENCES `course`(`course_id`) ON UPDATE CASCADE ON DELETE CASCADE
);

-- 1
CREATE TABLE `std_cou` (
  `student_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  FOREIGN KEY(`student_id`) REFERENCES `students`(`student_id`) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY(`course_id`) REFERENCES `course`(`course_id`) ON UPDATE CASCADE ON DELETE CASCADE
);

-- 2
DESCRIBE `teachers`;
DESCRIBE `students`;
DESCRIBE `course`;
DESCRIBE `tea_cou`;
DESCRIBE `std_cou`;

-- 6
INSERT INTO `teachers` VALUES(
  NULL, 'Imtiaz', 'Professor', 'CSE', 01234567
);

INSERT INTO `students` VALUES(
  1207002, 'Khan', NOW(), 'CSE', '2.5'
);

INSERT INTO `course` VALUES (
  NULL, 'DBMS', 3
);

INSERT INTO `tea_cou` VALUES (
  1, 1
);

INSERT INTO `std_cou` VALUES (
  1, 1
);

--7
SELECT * FROM `teachers`;
SELECT * FROM `students`;
SELECT * FROM `course`;

-- 8
SELECT * FROM `students` ORDER BY `cgpa` DESC;

-- 9
SELECT `name` AS `Professors` FROM `teachers` WHERE `department` = 'cse';