Create Student Project Database with insert , select, update, and delete. Part I
ID: 3922021 • Letter: C
Question
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)
Explanation / Answer
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;