class_work2.sql

create database class_work;
use class_work;
create table student(
student_id int primary key auto_increment,
firstname varchar(20),
lastname varchar(20),
email varchar(15),
major varchar(10));
create table course(
department_id int,
course_id int primary key auto_increment,
coursename varchar(10),
credits int(5),
foreign key(department_id) references department(department_id)
);
create table instructor(
department_id int,
instructor_id int primary key auto_increment,
first_name varchar(20),
last_name varchar(20),
email varchar(15),
foreign key (department_id) references department(department_id)
);
create table department(
department_id int primary key auto_increment,
department_name varchar(10),
building varchar(10));
create table enrollment(
enrollment_id int primary key auto_increment,
semester int(3) zerofill,
student_id int, foreign key(student_id) references student(student_id),
grade int(2) zerofill,
course_id int, foreign key (course_id) references course(course_id),
instructor_id int, foreign key (instructor_id) references instructor(instructor_id)
);
insert student values (null, "trishna", "bhattarai", "shna@gmail.com", "computer");
insert course values (null, null, "DBS", 002);
insert instructor values (null, null, "rukesh sir", "maharjan", "rush@gmail.com");
insert department values (null, "BCS", "aeroplane");
insert enrollment values (null, 2, null ,12, null, null);