Question

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.

0 0
Add a comment Improve this question Transcribed image text
Answer #1
The following are the table defined with the specified schema with some data

student table


course table

NB:I can't understad how to represent The day time format.Please make sure that the table you have create includes the day time data as you like

enrolment table


SELECT S.Name FROM Students AS S INNER JOIN Enrollment AS E ON E.SSN = S.SSN GROUP BY S.SSN, S.Name IN (SELECT GradeFROM EnrollmentWHERE Grade ='B')HAVING COUNT(*) > 1 GROUP BY S.Name, S.SSN WHERE S.Name LIKE ('S%') and S.Name LIKE('%M');

In the above SQL statement,

S% and %M are the wild card selection that select a string that starts with S and ends with M respectively

The students table and enrollment table are enough for the above SQL query as both the tabkes have the required  information to get the data.

Add a comment
Know the answer?
Add Answer to:
Given the following relational database schema: Student = (SSN, Name, Major) Course = ( CourseNumber ,...
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...

  • 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...

  • You are given the following relational schema (keys bolded): Employee(SSN, Name) Faculty(SSN) (Faculty(SSN) references Employee(SSN)) Staff(SSN,...

    You are given the following relational schema (keys bolded): Employee(SSN, Name) Faculty(SSN) (Faculty(SSN) references Employee(SSN)) Staff(SSN, ManagerSSN) (Staff(SSN) references Employee(SSN); Staff(ManagerSSN) references Staff(SSN)) Student(PersonNo, Name) Course(CourseNo, Title) Offering(CourseNo, Semester, InstructorSSN, Credit) (Offering(CourseNo) references Courses(CourseNo); Offering(InstructorSSN) references Faculty(SSN)) Enrolls(CourseNo, Semester, PersonNo, Grade) (Enrolls(CourseNo, Semester) references Offering(CourseNo, Semester); Enrolls(PersonNo) references Student(PersonNo)) ---------------------------------------------------------------------------------------------------- Decompile the above schema into an E-R schema representing the same information.

  • The relational schema shown below is part of a student database. The primary and foreign keys are highlighted in bold.

    The relational schema shown below is part of a student database. The primary and foreign keys are highlighted in bold.Student (studNo, studName, address, mobileNo)Course (courseNo, courseName, creditHour, level) Registration (studNo, courseNo, regDate, semester, session)Project (projNo, projName, courseNo)Assignment (projNo, studNo, startDate, dueDate, hoursSpent)Write SQL queries based on the student database given above: 1. Create tables & constraints for the student database.2. Insert some data into the tables to check that the tables created are correct. No limit on how many rows you want...

  • 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.

  • II. Fill in the blanks: (30 The relational database schema of Course Management System are defined...

    II. Fill in the blanks: (30 The relational database schema of Course Management System are defined as follows: Department (ID, Name, Head) Teacher (ID, Name, Position, DeptID) Student (ID, Name, Gender, Birthday, DeptID) Course (ID, Name, Room, TeacherID) Enroll (StudentID, CourseID, Grade) Please fill the blanks in the following SQL statements. 1. Create the Enroll table. CREATE TABLE Enroll ( char (8) NOT NULL StudentID [1] char (4) NOT NULL CourseID [2] int Grade CHECK (Grade IS NULL OR (Grade>-0...

  • Consider following relational database schema and translate the queries ‘a’ and ‘b’ into relational algebra expressions...

    Consider following relational database schema and translate the queries ‘a’ and ‘b’ into relational algebra expressions Teacher(ID, Name, Designation, Phone, Address) Course(CourseCode, CourseName, CreditHour) Taught(TeacherID, CourseCode, Semester) a. SELECT Name, Address FROM Teacher, Taught WHERE Teacher.ID=Taught.TeacherID and CourseCode=’IT344’ b. SELECT CourseName FROM Course, Taught WHERE Course.CourseCode=Taught.CourseCode and TeacherID=200

  • Design an Access database including tables and queries for the model below Relational Model (Project 9)...

    Design an Access database including tables and queries for the model below Relational Model (Project 9) Student SSN Name Address GPA Type SchoolName EnrollDate Current Student SSN ExpectedGraduate Alumni SSN GradDate EmployerName EmployerAddress Position Salary School Name Location Size OfLibrary BudgetForMBA AACSBStatus AACSBDate Curriculum Curriculum Code SchoolName SchoolName Course CourseCode Name Type Curriculum Code Course Topics CourseCode Topic Complete SSN CourseCode

  • 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...

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