MYSQL QUERIES: NEED 3 QUERIES MYSQL 1. Find the full names of the students who h

Question

MYSQL QUERIES: NEED 3 QUERIES MYSQL

1. Find the full names of the students who have taken a CIS course or a History course

2. Find the full names of the students who have taken both a CIS course and a History course

3. Find the full names of the students who have taken a CIS course but have not taken a History course

Metadata:

CREATE TABLE student(

   sid   varchar(10),
   fName   VARCHAR(30) NOT NULL,
   lName   VARCHAR(30) NOT NULL,
   gpa   double,
   dob date,
   CONSTRAINT sailor_pk PRIMARY KEY(sid)
   )ENGINE = INNODB;

-- NOT NULL auto_increment
CREATE TABLE course(
   crn   INT ,
   cname   VARCHAR(30),
   cDiscription   VARCHAR(10),
   dept varchar(5),
   cNumber int,
   cSection int,
   credits int,
   cyear char(4),
   CONSTRAINT course_pk PRIMARY KEY(crn)
   )ENGINE = INNODB;

CREATE TABLE registration(
   registration_num INT NOT NULL auto_increment,
   sid   varchar(10),
   crn   int,
   csection int,
   rdate   TIMESTAMP,
  
   CONSTRAINT registration_pk PRIMARY KEY(registration_num),
   CONSTRAINT registration_fk1 FOREIGN KEY (sid) REFERENCES student(sid),
   CONSTRAINT registration_fk2 FOREIGN KEY (crn) REFERENCES course(crn)

Explanation / Answer

Solution1:

SELECT (s.fname,s.lname) as FULL_NAME from student s, course crs, registration reg WHERE reg.sid==s.sid and crs.cname==History or cname==CIS GROUPBY crs.cname;

Solution2:

SELECT (s.fname,s.lname) as FULL_NAME from student s, course crs, registration reg WHERE reg.sid==s.sid and crs.cname==History and crs.cname==CIS;

Solution3:

SELECT (s.fname,s.lname) as FULL_NAME from student s, course crs, registration reg WHERE reg.sid==s.sid and crs.cname==History and crs.cname!=CIS or crs.cname==CIS and crs.cname==History GROUPBY crs.cname;

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

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