VARCHAR2(20), LName VARCHAR2(20), Email VARCHAR2(20), Username VARCHAR2(20), pas

Question

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 TABLE Exam (
    ExamNo INTEGER,
    Lv VARCHAR2(20),
    PRIMARY KEY (ExamNo) );

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,
    ExamNo INTEGER,
    PRIMARY KEY (Qid, ExamNo),
    FOREIGN KEY (Qid) REFERENCES Question,
    FOREIGN KEY (ExamNo) REFERENCES Exam );

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).
Registered users:
Find a list of words that:
Start from a specific alphabetic (for example words start with w).
In specific category (for example words in family category).
Can list the exams they have taken with their scores.
Can list all their comments in the application.
Can view the list of words that they have been created previously.
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 exams.
List all their children (registered users) with all their information that they provided to the application when they registered.

.......

provide SQL statmenet as quirey

Explanation / Answer

1. Select * from RegUsers where FName = 'Sara' or LName='Mohammad' ;

2.Select * from RegUsers;

Find a list of words that start from a specific alphabetic

select words from Dictionary where words='w%';

List all the users with their actual answering

Select RegUsers.Username,Answer.ActAns from RegUsers,Answer where RegUsers.Username=Answer.Username and RegUsers.Email=Answer.Email;

Can list all their comments in the application.

Select Text ,Username from Comments ;

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.