Create Student Project Database with insert select update an

Create Student Project Database with insert, select, update, and delete.

Part I

- Create Student Table (Student) 10 Students

STUDENT NO TEXT (3) PRIMARYKEY

STUDENT_NAME TEXT (10)

STUDENT_DOB DATE

STUDENT_DOJ DATE

- Create Project Table (Project) 3 projects, Name, C++/Java/VB

PRJ_NO TEXT (3) PRIMARY KEY

PRJ_NAME TEXT (15)

PRJ_PLATFORM TEXT (10)

- Create Student Project Table (StudentProject) Student#, Project#, Role(programmer, manager, or analyst)

STUDENT_NO TEXT (3)

PRJ_NO TEXT (3)   

DESIGNATION TEXT (10)

PRIMARYKEY (STUDENT_NO,PRJ_NO,DESIGNATION)

FOREIGN KEY(STUDENT_NO)

FOREIGN KEY(PRJ_NO)

Solution

CREATE TABLE Student  
(   
STUDENT_NO CHAR(3) NOT NULL PRIMARY KEY,
STUDENT_NAME CHAR(10),
STUDENT_DOB DATE,
STUDENT_DOJ DATE
)

CREATE TABLE Project  
(   
PRJ_NO CHAR(3) NOT NULL PRIMARY KEY,
PRJ_NAME CHAR(15),
PRJ_PLATFORM CHAR(10)
)

CREATE TABLE StudentProject  
(   
STUDENT_NO CHAR(3) REFERENCES Student,
PRJ_NO CHAR(3) REFERENCES Project,
DESIGNATION CHAR(10),
PRIMARY KEY(STUDENT_NO,PRJ_NO,DESIGNATION)
)

INSERT INTO Student VALUES (\'AB0\',\"Paul\",\'1992-05-12\',\'2016-02-12\');
INSERT INTO Student VALUES (\'AB1\',\"Sam\",\'1992-06-11\',\'2016-03-11\');
INSERT INTO Student VALUES (\'AB2\',\"Raul\",\'1992-03-11\',\'2016-04-05\');
INSERT INTO Student VALUES (\'AB3\',\"John\",\'1992-07-09\',\'2016-05-12\');
INSERT INTO Student VALUES (\'AB4\',\"Mike\",\'1992-02-06\',\'2016-06-02\');
INSERT INTO Student VALUES (\'AB5\',\"Ram\",\'1992-01-03\',\'2016-07-06\');
INSERT INTO Student VALUES (\'AB6\',\"Jin\",\'1992-05-12\',\'2016-08-03\');
INSERT INTO Student VALUES (\'AB7\',\"Frodo\",\'1992-08-02\',\'2016-09-12\');
INSERT INTO Student VALUES (\'AB8\',\"Bill\",\'1992-09-01\',\'2016-11-04\');
INSERT INTO Student VALUES (\'AB9\',\"Tom\",\'1992-06-12\',\'2016-12-12\');

SELECT * FROM Student;

UPDATE Student
SET STUDENT_DOB=\'1992-03-04\',STUDENT_DOJ=\'1991-03-04\'
WHERE STUDENT_NO=\'AB5\';

INSERT INTO Project VALUES (\'XY0\',\"C++\",\"Coding\");
INSERT INTO Project VALUES (\'XY1\',\"JAVA\",\"Theory\");
INSERT INTO Project VALUES (\'XY2\',\"VB\",\"Design\");

DELETE FROM Project WHERE PRJ_PLATFORM=\"Design\"; //It Deletes row number 3 from Project table

SELECT * FROM Project;

INSERT INTO StudentProject VALUES (\'AB0\',XY0,\"Programmer\");
INSERT INTO StudentProject VALUES (\'AB1\',XY1,\"Manager\");
INSERT INTO StudentProject VALUES (\'AB2\',XY2,\"Analyst\");
INSERT INTO StudentProject VALUES (\'AB3\',XY0,\"Programmer\");
INSERT INTO StudentProject VALUES (\'AB4\',XY1,\"Manager\");
INSERT INTO StudentProject VALUES (\'AB5\',XY2,\"Analyst\");
INSERT INTO StudentProject VALUES (\'AB6\',XY0,\"Programmer\");
INSERT INTO StudentProject VALUES (\'AB7\',XY1,\"Manager\");
INSERT INTO StudentProject VALUES (\'AB8\',XY2,\"Analyst\");
INSERT INTO StudentProject VALUES (\'AB9\',XY0,\"Programmer\");

SELECT * FROM StudentProject;