Question

31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course (courselD, title, hour, department) Enrollment (StudentID, courselD, date Write the SQL statements to perf orm the following operations: List all the students information for those majoring in Computer Science. (2 pts) List the majors, and the numbers of the students in every major. (2 pts) List the departments, and the total number of hours of the courses offered by every department. (2 pts)
List all the students IDs of those students taking course title Data Structure (3 pts) List all the titles of the courses taken by a student whose ID is 900123456 (3 pts) List all the names of the students who enroll in course titled Programming in C++
0 0
Add a comment Improve this question Transcribed image text
Answer #1

1. SELECT * FROM Student WHERE major = 'Computer Science';

2. SELECT major, count(*) FROM Student GROUP BY major;

3. SELECT department, sum(hour) FROM Course GROUP BY department;

4. SELECT StudentID FROM Enrolment WHERE courseID IN (SELECT courseID FROM Course WHERE title = 'Data Structures');

5. SELECT DISTINCT title FROM Course WHERE courseID IN (SELECT courseID FROM Enrolment WHERE StudentID = '900123456');

6. SELECT name FROM Student WHERE StudentID = (SELECT StudentID FROM Enrolment WHERE courseID IN (SELECT courseID FROM Course WHERE title = 'Programming in C++'));

Add a comment
Know the answer?
Add Answer to:
31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

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
  • Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = (...

    Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: 1. List every two CourseNumber and their titles which have the same prerequisites. 2. List the name of every student and SSN who has completed...

  • Subject : computer science -web development Qus: using the database tables below write the SQL queries:...

    Subject : computer science -web development Qus: using the database tables below write the SQL queries: ( SELECT; FORM; WHERE; ORDER BY; DISTINCT; GROUP BY; COUNT(); SUM(); AVG(); MAX(); MIN(); JOIN; ON) Student (StudentId, Major, GPA) Transcript( Studentid, CourseNo, Gread); Course( CourseNo, Title, Credits) Display the title of the course(s) that are 3 credits? Display the average GPA of the students Majoring in ‘CSC’ List non duplicated major(s) of students who have received a grades of ‘A’ in CSC350 List...

  • Please do these carefully. Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title,...

    Please do these carefully. Student = (SSN, Name, Major) Course = ( CourseNumber, PrerequisiteCourseNumber, Course Title, NumberUnits) Section = ( CourseNumber, Quarter, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00PM. Enrollment = (SSN,CourseNumber, Quarter, Grade)// Grade is either Null or a letter grade. Express the following queries using appropriate SQL statements with a minimum number of operations: 1. List the name of every student and SSN who has not taken any course more than once. 2. List every...

  • Consider the following relations for course-enrollment database in a university: STUDENT(S-ID,S-Name, Department, Birth-date) COURSE(C-ID, C-Name, Department)...

    Consider the following relations for course-enrollment database in a university: STUDENT(S-ID,S-Name, Department, Birth-date) COURSE(C-ID, C-Name, Department) ENROLL(S-ID, C-ID, Grade) TEXTBOOK(B-ISBN, B-Title, Publisher, Author) BOOK-ADOPTION(C-ID, B-ISBN) (a) Draw the database relational schema and show the primary keys and referential integrity constraints on the schema. (b) How many superkeys does the relation TEXTBOOK have? List ALL of them. (c) Now assume each COURSE has distinct C-Name. (i) If C-ID is a primary key, what are the candidate keys and the unique keys...

  • Consider the following relations for a database that keeps track of student enrollment in courses and...

    Consider the following relations for a database that keeps track of student enrollment in courses and books adopted for each course. -------------------------------------------------------------------------------------------------------- STUDENT(Ssn,Name,Major,Bdate) COURSE(Course#,Cname,Dept) ENROLL(Ssn,Course#,Quarter,Grade) TEXTBOOK(Book_isbn,Book_title,Publisher,Author) BOOK_ASSOC(Course#,Quarter,Book_isbn) ------------------------------------------------------------------------------------------------------------------------------- Having that a relation can have zero or more foreign keys and each foreign key can refer to different referenced relations. Specify the foreign keys for this schema.

  • Name Use complete sentences if possible The following StudentGrade relation (table) stores the Student, Major, and Course information Each student has unique StudentID. Assume that a student has...

    Name Use complete sentences if possible The following StudentGrade relation (table) stores the Student, Major, and Course information Each student has unique StudentID. Assume that a student has only one major. One student can take multiple courses in one SemesterYear. One course can be taken by many students in each SemesterYear. Given values for StudentID, Course Number, and SemesterYear, one can determine all the rest of values for a record. However only Grade is functionally dependent on StudentlD, CourseNumber and...

  • 2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST...

    2. Consider the following database and answer the questions below SPORT ENROLLMENT STUDENT COURSE ST ST TITLE 313 1313 50s0 Basketbal 8989 Basketbal 007 COMP203A 7007 COMP20e 007 ames Bond 302 Database Systems COMP203 Computer Or n Brown 00 CoMP302 A o0 3un SmuCOMP42daedDatabase Systems 3 13 COMP 209 70 5050 Susan Smith COMP447 nced Database S 1313 COMP2O3A 1313 COMP302 5050 COMP20 C 1313 COMP42 7007 Write the SQL statement that displays the names of the students who have...

  • 10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve...

    10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve the names of all senior students majoring in ‘CS’ (Computer Science). b. Retrieve the names of all courses taught by Professor King in 2004 and 2005. c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. d. Retrieve the name and transcript of each senior student (Class =4) majoring in CS. A...

  • Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course.

    Question: Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. 1- Draw an ER diagram of ONLINE COURSE REGISRATION database, that captures all the given below requirements. Specify key attribute(s) of each entity set. For each relationship set, specify structural constraints and participation constraints. a. For each USER, the portal maintains user ID, Name, E-mail. Each user has a unique ID. Name is a Composite attributes with Frame, Midname,...

  • Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber ,...

    Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber , CourseTitle, NumberOfUnits, RoomNumber, DayTime), where DayTime is of the form MW 1:0-2:00. Enrollment = (SSN , CourseNumber, Grade) Express the following queries using AQL statements with minimum number of tables and operations. List the name of every student whose name starts with the letter S and ends with the letter M, and has earned the grade B in at least one course.

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
ADVERTISEMENT