HomeworkLib
Question

CREATE TABLE Majors major VARCHAR(12), description VARCHAR, PRIMARY KEY (major) ); CREATE TABLE Course ( courseMajor VARCHAR(Given the schema, write a query and subquery to do the following:

Find the Cpts major students who passed a course but failed the prerequisite of that course, i.e., got a grade lower than

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Solution==> Please do comment in case of any query.

Step1:- First we need to select all the courses from table Enroll where courseMajor = "CptS". SQL for this is :-

  select * from enroll where courseMajor ="CptS";

Step2:- Now we need to perform inner join with table Prereq . I have created dummy database for your better understanding. SQL for this is :-

select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo;

Number of Records: 3 courseMajor courseNo SID grade preMajor preCourseNo Cpts 255 12583589 Cpto 155 Cpts 231 12583589 Cpto 13

Step 3 :- Now we need to find grade in preMajor Courses. SQL for this:-

select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ;

Number of Records: 3 courseMajor courseNo SID preMajor preCourseNo grade Cpts 255 12583589 Cpto 155 Cpts 231 12583589 Cpto 13

Step 4:- Now we need to get the name of the students.SQL is :-

  select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID;

sName SID courseMajor courseNo Alice 12583589 Cpts 255 Alice 12583589 Cpts 231 Alice 12583589 Cpts 223

Step 5:- Filter on the basis of grade.SQL is:-

select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo where e.courseMajor = "CptS" ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID where C.grade < 2;

Number of Records: 2 sName SID courseMajor course No Alice 12583589 Cpts 223 Alice 12583589 Cpts 231

Result SQL:-

select D.sname, C.sID , C.courseMajor, C.courseNo from ( select A.courseMajor, A.courseNo, A.sID, A.preMajor, A.preCourseNo, B.grade from (select * from enroll e inner join Prereq p on e.courseMajor = p.courseMajor and e.courseNo = p.courseNo where e.courseMajor = "CptS" ) A inner join enroll B on A.preMajor = B.courseMajor and A.preCourseNo = B.CourseNo ) C inner join Student D on D.sID = C.sID where C.grade < 2;

Hope you will like the answer.

Thanks.

0 0
Add a comment
Know the answer?
Add Answer to:
Given the schema, write a query and subquery to do the following: CREATE TABLE Majors major...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • /* I am executing following SQL statement for the below code but I am getting error....

    /* I am executing following SQL statement for the below code but I am getting error. Pls, upload screenshot after running it. SQL Statement: SELECT OfferNo, CourseNo FROM Offering WHERE OfferTerm = 'Sum' AND OfferYear = 2012 AND FacSSN IS NULL; */ CREATE TABLE STUDENT( StdSSN INT NOT NULL PRIMARY KEY, StdFName VARCHAR2(50), StdLName VARCHAR2(50), StdCity VARCHAR2(50), StdState VARCHAR2(2), StdZip VARCHAR2(10), StdMajor VARCHAR2(15), StdYear VARCHAR2(20) ); CREATE TABLE FACULTY( FacSSN INTEGER NOT NULL PRIMARY KEY, FacFName VARCHAR(50), FacLName VARCHAR(50), FacCity...

  • Write an SQL query to return the users who posted the most number of jokes on...

    Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...

  • Create a new database and execute the code below in SQL Server’s query window to create...

    Create a new database and execute the code below in SQL Server’s query window to create the database tables. CREATE TABLE PhysicianSpecialties (SpecialtyID integer, SpecialtyName varchar(50), CONSTRAINT PK_PhysicianSpecialties PRIMARY KEY (SpecialtyID)) go CREATE TABLE ZipCodes (ZipCode varchar(10), City varchar(50), State varchar(2), CONSTRAINT PK_ZipCodes PRIMARY KEY (ZipCode)) go CREATE TABLE PhysicianPractices (PracticeID integer, PracticeName varchar(50), Address_Line1 varchar(50), Address_Line2 varchar(50), ZipCode varchar(10), Phone varchar(14), Fax varchar(14), WebsiteURL varchar(50), CONSTRAINT PK_PhysicianPractices PRIMARY KEY (PracticeID), CONSTRAINT FK_PhysicianPractices_ZipCodes FOREIGN KEY (ZipCode) REFERENCES Zipcodes) go CREATE...

  • Given the mySQL tables created below...    Create a mySQL solution to return the itemIDs of...

    Given the mySQL tables created below...    Create a mySQL solution to return the itemIDs of items posted by user X, such that all the reviews are “Excellent” or “Good” for these items CREATE TABLE Users (    userId varchar (30) NOT NULL,    pass varchar (30),    fname varchar (50),    lname varchar (50),    email varchar (50),    gender char(1),    age integer,    banned boolean,    PRIMARY KEY (userId),    UNIQUE(email)) CREATE TABLE FavSellers (    userId...

  • CREATE TABLE person ( pid INTEGER NOT NULL ,pname VARCHAR(30) NOT NULL ,PRIMARY KEY (pid) );...

    CREATE TABLE person ( pid INTEGER NOT NULL ,pname VARCHAR(30) NOT NULL ,PRIMARY KEY (pid) ); CREATE TABLE organization ( oid INTEGER NOT NULL ,oname VARCHAR(30) NOT NULL ,PRIMARY KEY (oid) ); CREATE TABLE venue ( vid INTEGER NOT NULL ,area CHAR(1) NOT NULL ,capacity INTEGER NOT NULL ,PRIMARY KEY (vid) ); CREATE TABLE calendar ( vid INTEGER NOT NULL ,date DATE NOT NULL ,price NUMERIC(6,2) NOT NULL ,PRIMARY KEY(vid,date) ,FOREIGN KEY(vid) REFERENCES venue(vid) ); CREATE TABLE event ( eid...

  • The following SQL DDL script creates a database for a social network application. create table userProfile(...

    The following SQL DDL script creates a database for a social network application. create table userProfile( id char(10) primary key, firstName varchar(20), lastName varchar(20), dob date, email varchar(30) ); create table foaf( userid char(10), friendID char(10), timeEstablished date, constraint pk primary key(userid, friendID), constraint fk1 foreign key(userid) references userProfile(id), constraint fk2 foreign key(friendID) references userProfile(id) ); create table activity( actID char(20) primary key, topic varchar(20), description varchar(100), location varchar(20), ActivityDate date, hostUser char(10), foreign key(hostUser) references userProfile(id) ); create table...

  • SQL Query Question: I have a database with the tables below, data has also been aded...

    SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...

  • CREATE TABLE Gender ( gender CHAR(1), description VARCHAR(10), PRIMARY KEY (gender) ); CREATE TABLE People (...

    CREATE TABLE Gender ( gender CHAR(1), description VARCHAR(10), PRIMARY KEY (gender) ); CREATE TABLE People ( ID INT, name VARCHAR(50), gender CHAR(1), height FLOAT, PRIMARY KEY (ID), FOREIGN KEY (gender) REFERENCES Gender (gender) ); CREATE TABLE Sports ( ID INT, name VARCHAR(50), record FLOAT, PRIMARY KEY (ID), UNIQUE (name) ); CREATE TABLE Competitions ( ID INT, place VARCHAR(50), held DATE, PRIMARY KEY (ID) ); CREATE TABLE Results ( peopleID INT NOT NULL, competitionID INT NOT NULL, sportID INT NOT NULL,...

  • Based on reference below. Write a relational algebra expression to return those users who have posted...

    Based on reference below. Write a relational algebra expression to return those users who have posted “excellent” reviews but never “poor” reviews. CREATE TABLE Users (             userId varchar (30) NOT NULL,             pass varchar (30),             fname varchar (50),             lname varchar (50),             email varchar (50),             gender char(1),             age integer,             banned boolean,             PRIMARY KEY (userId),             UNIQUE(email)) CREATE TABLE FavSellers (             userId varchar (30),             sellerId varchar (30),             PRIMARY KEY (userId, sellerId),...

  • CREATE TABLE Users (               userId varchar (30) NOT NULL,               pass varchar (30),     

    CREATE TABLE Users (               userId varchar (30) NOT NULL,               pass varchar (30),               fname varchar (50),               lname varchar (50),               email varchar (50),               gender char(1),               age integer,               banned boolean,               PRIMARY KEY (userId),               UNIQUE(email)) CREATE TABLE FavSellers (               userId varchar (30),               sellerId varchar (30),               PRIMARY KEY (userId, sellerId),               FOREIGN KEY(userId) references Users,               FOREIGN KEY(sellerId) references Users(userId)) CREATE TABLE Items (               itemId integer,               title varchar (50),              ...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT