CREATE TABLE Instructor ( FName VARCHAR2(20), LName VARCHAR2(20), Email VARCHAR2
ID: 3823606 • 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 );
>>>>>>>>>>>>>>>>>>>>>>>>>>
Instructor:
Can list all the users that have a specific first name or last name or both for example;
All users with last name Mohammad.
All users with first name Sara.
Select from table records, all the information about the registered users (first name, last name, email, username and password).
Find the highest scores that the registered users have in exam.
List all the users with their actual answering.
Select the categories that consist of subcategories.
Find all the parents, and list their children names under them.
List the recent comments.
Find a list of words that start from a specific alphabetic (for example; a list of words that start with w).
>>>>>>>>>>>>>>>>>>>>>>>>
provide SQL statement as query
Explanation / Answer
Can list all the users that have a specific first name or last name or both for example;
All users with last name Mohammad.
select * from RegUsers where LName ='Mohammad'
All users with first name Sara.
select * from RegUsers where FName ='Sara'
Select from table records, all the information about the registered users (first name, last name, email, username and password).
select fname, lname, email, username , password from RegUsers
List all the users with their actual answering.
select r.fname, r.lname,a.ActAns, a.Qid from RegUsers r inner join Answer a on r.username = a.username and r.email = a.email
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.