Untitled

CREATE DATABASE IF NOT EXISTS school;

USE school;

DROP TABLE IF EXISTS students;

DROP TABLE IF EXISTS departments;

CREATE TABLE departments (

Dept_ID INT NOT NULL AUTO_INCREMENT,
Dept_Name VARCHAR(30),
Head_Name VARCHAR(30),

PRIMARY KEY(Dept_ID)

);

CREATE TABLE students (

Student_ID INT NOT NULL AUTO_INCREMENT,
Student_Name VARCHAR(30),
DOB DATE,
Mobile_No VARCHAR(11),
Gender VARCHAR(20),
Dept_ID INT,

PRIMARY KEY(Student_ID),
FOREIGN KEY (Dept_ID) REFERENCES departments(Dept_ID)


ALTER TABLE departments ADD ( Dept_Desc VARCHAR(30) );
INSERT INTO departments (Dept_Name, Head_Name, Dept_Desc) VALUES ("Science", "Mr Wotsit", "Science facilities") , ("Dance", "Mx Blake", "Dance hall");

INSERT INTO students (Student_Name, DOB, Mobile_No, Gender, Dept_ID) VALUES ("John Wick", 19770921,01902324043, "Male",1), ("Charlie Bucket", 19900423, 00000000000, "Male",2), ("Rick Wendigo", 19680101, 98993845943, "Female",1);
");