CREATE TABLE Instructor ( FName VARCHAR2(20), LName VARCHAR2(20), Email VARCHAR2
ID: 3823492 • Letter: C
Question
CREATE TABLE Instructor (
FName VARCHAR2(20),
LName VARCHAR2(20),
Email VARCHAR2(20),
Username VARCHAR2(20),
passwrd VARCHAR2(20),
PRIMARY KEY(Username, Email));
CREATE TABLE RegUsers (
FName VARCHAR2(20),
LName VARCHAR2(20),
Email VARCHAR2(20),
Username VARCHAR2(20),
passwrd VARCHAR2(20),
PRIMARY KEY (Username, Email));
CREATE TABLE Messages (
Dte DATE,
Subject VARCHAR2(20),
Email VARCHAR2(20),
SName VARCHAR2(20),
MSG VARCHAR2(300));
CREATE TABLE Dictionary (
words VARCHAR2(20),
picture BLOB,
video BLOB );
CREATE TABLE Categories (
CatId INTEGER,
CatName VARCHAR2(20),
CatPicture BLOB,
PRIMARY KEY (CatId));
CREATE TABLE Lst (
Lid INTEGER,
Lname VARCHAR2(20),
PRIMARY KEY (Lid) );
/*week entity takes primary keys from other relation_INSTRUCTOR&USERS??*/
CREATE TABLE Comments (
CId INTEGER,
Text VARCHAR2(200),
Cdate DATE,
Username VARCHAR2(20),
Email VARCHAR2(20),
PRIMARY KEY (CId, Username,Email),
FOREIGN KEY (Username, Email) REFERENCES RegUsers
ON DELETE CASCADE);
/*error with TIME DATATYPE*/
CREATE TABLEtest (
testNo INTEGER,
Lv VARCHAR2(20),
PRIMARY KEY (testNo) );
CREATE TABLE Question (
Qid INTEGER,
Qtext VARCHAR2(60),
Canswer VARCHAR2(40),
option1 VARCHAR2(40),
option2 VARCHAR2(40),
option3 VARCHAR2(40),
PRIMARY KEY (Qid) );
CREATE TABLE Answer (
Qid INTEGER,
ActAns varchar(40),
Email VARCHAR2(20),
Username VARCHAR2(20),
PRIMARY KEY (Qid, Username, Email),
FOREIGN KEY (Qid) REFERENCES Question,
FOREIGN KEY (Username, Email) REFERENCES RegUsers);
CREATE TABLE Has2 (
Qid INTEGER,
testNo INTEGER,
PRIMARY KEY (Qid, testNo),
FOREIGN KEY (Qid) REFERENCES Question,
FOREIGN KEY (testNo) REFERENCES test );
CREATE TABLE Has (
CatId INTEGER,
PRIMARY KEY (CatId),
FOREIGN KEY (CatId) REFERENCES Categories );
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE Contact (
);
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE Post (
Cid INTEGER,
PRIMARY KEY (CId),
FOREIGN KEY (CId) REFERENCES Comments );
/*NEED TO CHECK WITH THE TA*/
CREATE TABLE crte (
Lid INTEGER,
PRIMARY KEY (LId),
FOREIGN KEY (LId) REFERENCES Lst );
>>>>>>>>>>>>>>>>>>>>>>>>>>
Parent:
They have the same queries and modifications that the registered users have except for the following:
List all their children (registered users) with their scores in the test.
List all their children (registered users) with all their information that they provided to the application when they registered.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
provide SQL statement as query
Explanation / Answer
List all their children (registered users) with their scores in the test.
SELECT RegUsers.Username, RegUsers.FName, RegUsers.LName, Has2.TestNo, Question.Qid, Question.Qtext, Question.Canswer, Answer.ActAns
FROM Dictionary, Test, Has INNER JOIN Categories ON Has.CatId = Categories.CatId, ((Post INNER JOIN (Has2 INNER JOIN ((Comments INNER JOIN ((Contact INNER JOIN (Answer INNER JOIN Instructor ON (Answer.Email = Instructor.Email) AND (Answer.Username = Instructor.Username)) ON (Contact.LName = Instructor.LName) AND (Contact.FName = Instructor.FName) AND (Contact.FName = Instructor.FName)) INNER JOIN RegUsers ON (Instructor.Username = RegUsers.Username) AND (Instructor.Email = RegUsers.Email) AND (Contact.LName = RegUsers.LName) AND (Contact.FName = RegUsers.FName) AND (Answer.Username = RegUsers.Username) AND (Answer.Email = RegUsers.Email) AND (Contact.FName = RegUsers.FName) AND (Answer.Username = RegUsers.Username)) ON (Answer.Email = Comments.Email) AND (Comments.Email = Instructor.Email) AND (Comments.Email = RegUsers.Email) AND (Comments.Username = RegUsers.Email)) INNER JOIN Question ON Answer.Qid = Question.Qid) ON Has2.Qid = Question.Qid) ON Post.Cid = Comments.CId) INNER JOIN (Crte INNER JOIN Lst ON Crte.Lid = Lst.Lid) ON Contact.LName = Lst.Lname) INNER JOIN Messages ON (Comments.Email = Messages.Email) AND (Messages.Email = RegUsers.Email) AND (RegUsers.Email = Messages.Email);
List all their children (registered users) with all their information that they provided to the application when they registered.
SELECT RegUsers.Username, RegUsers.Email, Instructor.FName, Instructor.LName, Contact.Address, Contact.Mobile, Categories.CatId, Has2.TestNo FROM Dictionary, Test, Has INNER JOIN Categories ON Has.CatId = Categories.CatId, ((Post INNER JOIN (Has2 INNER JOIN ((Comments INNER JOIN ((Contact INNER JOIN (Answer INNER JOIN Instructor ON (Answer.Email = Instructor.Email) AND (Answer.Username = Instructor.Username)) ON (Contact.LName = Instructor.LName) AND (Contact.FName = Instructor.FName) AND (Contact.FName = Instructor.FName)) INNER JOIN RegUsers ON (Instructor.Username = RegUsers.Username) AND (Instructor.Email = RegUsers.Email) AND (Contact.LName = RegUsers.LName) AND (Contact.FName = RegUsers.FName) AND (Answer.Username = RegUsers.Username) AND (Answer.Email = RegUsers.Email) AND (Contact.FName = RegUsers.FName) AND (Answer.Username = RegUsers.Username)) ON (Answer.Email = Comments.Email) AND (Comments.Email = Instructor.Email) AND (Comments.Email = RegUsers.Email) AND (Comments.Username = RegUsers.Email)) INNER JOIN Question ON Answer.Qid = Question.Qid) ON Has2.Qid = Question.Qid) ON Post.Cid = Comments.CId) INNER JOIN (Crte INNER JOIN Lst ON Crte.Lid = Lst.Lid) ON Contact.LName = Lst.Lname) INNER JOIN Messages ON (Comments.Email = Messages.Email) AND (Messages.Email = RegUsers.Email) AND (RegUsers.Email = Messages.Email);