Students: student-id name (PK) date of_birth address email (Unique) Ne (Not Null

Question

Students: student-id name (PK) date of_birth address email (Unique) Ne (Not Null) Alice Wood 06/15/1993 5637 NW 41 ST awood001@cis.fiu.edu ugrad Henrie Cage 04/24/1994 1443 NW 7 STe001@cis.fiu.edu ugrad John Smith 01/09/1995 731 NW 87 AVE jsmit005@cis.fiu.edu ugrad Franklin Wong 12/08/1995 638 NW 104 AVfwong001@cis.fiu.edu ugrad Jennifer King 11/08/1998 3500 W Flagler ST jking001@cis.fiu.edu ugrad Richard Young 12/05/1995 778 SW 87 AVEryoun001@cis.fiu.edu grad Robert Poore 08/22/1996101 SW 8 ST Joyce English 07/31/19998421 SW 109 AV jengl001@cis.fiu.edu grad (Not Null) 4 rpoor001@cis.fiu.edu grad

Explanation / Answer

-- Query 1)

ALTER TABLE Courses ADD semester VARACHAR(15) NOT NULL;

UPDATE Courses SET semester = 'Spring 2018' WHERE course_id IN ('1','2','3', '6', '7');

UPDATE Courses SET semester = 'Fall 2017' WHERE course_id IN ('4','5');

-- Query 2)

SELECT description FROM Courses WHERE semester LIKE '%2018';

-- Query 3)

SELECT description FROM Courses C JOIN Faculties F ON C.instructor = F.faculty_id WHERE F.name='Steven Garden';

-- Query 4)

SELECT name FROM Faculties F JOIN Courses C ON F.faculty_id = C.instructor WHERE C.semester='Spring 2017';

-- Query 5)

SELECT description, grade FROM (Students S JOIN enroll E ON S.student_id=E.student_id) JOIN Courses C ON C.course_id = E.course_id WHERE S.name = 'Alice Wood';

-- Query 6)

SELECT name FROM Faculties F JOIN Courses C ON F.faculty_id=C.instructor GROUP BY C.instructor HAVING count(*)>1;

-- Query 7)

SELECT name, grade FROM (Students S JOIN enroll E ON S.student_id=E.student_id) JOIN Courses C ON C.course_id = E.course_id WHERE C.description = 'Fundamental of Computer Sys.';

-- Query 8)

SELECT name FROM Faculties WHERE level='grad' && YEAR(date_of_birth)>1970;

-- Query 9)

SELECT name FROM (Students S JOIN enroll E ON S.student_id=E.student_id) JOIN Courses C ON C.course_id = E.course_id WHERE C.level = 'ugrad' && E.grade ='B';

-- Query 10)

SELECT name, MAX(grade) FROM Students s JOIN enroll E ON S.student_id = E.student_id GROUP BY S.student_id;

-- Query 11)

SELECT E.grade, COUNT(*) FROM Students s JOIN enroll E ON S.student_id = E.student_id WHERE S.name='Alice Wood' GROUP BY E.grade;

-- Query 12)

SELECT name, DATEDIFF(NOW(), date_of_birth) AS AGE FROM Students ORDER BY date_of_birth;

-- Query 13)

CREATE TABLE title(

name VARCHAR(100) PRIMARY KEY,

abbrevation VARCHAR(20)

);

INSERT INTO title VALUES('Instructor', 'Instr'), ('Associate Professor', 'AP'), ('Professor', 'Prof');

UPDATE Faculties SET level='AP' WHERE level = 'Associate Professor';

UPDATE Faculties SET level='Instr' WHERE level = 'Instructor';

UPDATE Faculties SET level='Prof' WHERE level = 'Professor';

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---