Use SQL to write the following queries: 1. a) Find the last names of students born after January 1, 2000 b) Find the student last names, dates and grades of exams they passed, where a grade was greater than or equal to 8 c) Find the first and last names of students who took to at least one exam from "Computer Science" programme d) List professor IDs of top 5 professors who teach the most courses e) Calculate the average credits for courses in each programme f) Find the surnames of students who have never taken an exam from "Mathematics" programme

Use SQL to write the following queries: 1. a) Find the last names of students born after January 1, 2000 b) Find the student last names, dates and grades of exams they passed, where a grade was greater than or equal to 8 c) Find the first and last names of students who took to at least one exam from "Computer Science" programme d) List professor IDs of top 5 professors who teach the most courses e) Calculate the average credits for courses in each programme f) Find the surnames of students who have never taken an exam from "Mathematics" programme

CMPTR

3rd Edition

ISBN:9781337681872

Author:PINARD

Publisher:PINARD

Chapter17: Creating A Database

Section: Chapter Questions

Problem 4QY

See similar textbooks

Similar questions

Material : Relational Algebra Course : Database Systems Create a Relational Algebra From Entity Relational Diagram (ERD) Below for:a) Displays books published in the last 5 years from 2020.b) Displays details of customers who have done shopping (using the Join operator)c) Displays email customers who have done shopping (using the Set operator)d) Displays the ISBN book that has never been purchased (using the Set operator)
Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,
write the schema that descibes the following relations: 1- The course relation course id title dept.name credits Intro. to Biology Biology Biology Biology Comp. Sci. Comp. Sci. Comp. Sci. Comp. Sci. Comp. Sci. Elec. Eng. BIO-101 4 BIO-301 BIO-399 CS-101 CS-190 CS-315 CS-319 Genetics 4 Computational Biology Intro. to Computer Science Game Design Robotics 3. 4. 4 Image Processing Database System Concepts Intro. to Digital Systems Investment Banking World History Music Video Production CS-347 ЕЕ-181 FIN-201 3 3 Finance HIS-351 MU-199 PHY-101 History Music Physical Principles Physics 4
SQL DDL corresponding to this schema (i.e. the CREATE TABLE statements Database Schema:branch ( branch_name, branch_city, assets )customer ( ID, customer_name, customer_street, customer_city )loan ( loan_number, branch_name, amount )borrower ( ID, loan_number )account ( account_number, branch_name, balance )depositor ( ID, account_number )
You are given the following schema of a University, where primary keys of each table areunderlined and symbol (#) symbolises foreign keys:Student (student_id, first_name, last_name, date_of_birth)Course (course_id, #professor_id, course_name, credits, programme)Exam (#student_id, #course_id, exam_date, grade)Professor (professor_id, first_name, last_name, research_field) -Use DOMAIN or TUPLE RELATIONAL CALCULUS to write thefollowing queries:a) Find the last names of professors who have taught a course with 6 creditsb) Find all student IDs of students who are born before January 1, 1999 or have passed at leastone exam with a 10c) Find the last names of professors who have never taught a coursed) Find the last name(s) of the professor(s) who teach the most courses
The following four tables form part of a library database: • Book (isbn, title, author, genre, price) • Copy (copyId, isbn) • Loan (userId, copyId, loanDate, dueDate) • User (userId, name, address, email) Where: • Book contains details of books and isbn is the primary key• Copy contains details of individual copies of books and copyId is the primary key• Loan contains details of loans and (userId, copyId, loanDate) forms the primary key • User contains details of library users and userId is the primary key 3.1 Identify the primary in the above schema. 3.2 Identify the foreign keys in the above schema. 3.3 Draw an Entity relationship model (ER-model) for the above schema, including: • all attributes• correct notation for primary and foreign keys • relationships• multiplicities 3.4 Draw sample tables for the above relations, ensuring relational integrity rules are met. 3.5 Explain the Entity Integrity constraint with reference to the Book relation.
You are given the following schema of a University, where primary keys of each table areunderlined and symbol (#) symbolises foreign keys:Student (student_id, first_name, last_name, date_of_birth)Course (course_id, #professor_id, course_name, credits, programme)Exam (#student_id, #course_id, exam_date, grade)Professor (professor_id, first_name, last_name, research_field) -Use RELATIONAL ALGEBRA to write the following queries:a) Find names and surnames of professors, who teach a course in "Biology" programmeb) Find the student IDs and names of students who have not taken any exam (from any course)c) Find student IDs of students who have passed every exam from "Data Science" programmed) Find the course IDs and names of courses that have been passed by at least two differentstudents (If a course was taken, it means that a student wrote an exam on that course)
Computer Science Create a SQL Code for the following ER diagram/Schema. choose any data type you think is appropriate for each of the attributes (int, numeric, varchar, etc.). Make sure that your SQL code includes all the constrains depicted in the schema! User Follows Follows u id follower id u id username u id birthyear User username birthyear Event Participates Participates u id e id e id topic day e id month day Event month topic (a) (b)
CUSTOMER (Cust#, Cname, City)ORDER (Order#, Odate, Cust#, Ord_Amt)ORDER_ITEM (Order#, Item#, Qty)ITEM (Item#, Unit_price)SHIPMENT (Order#, Warehouse#, Ship_date)WAREHOUSE (Warehouse#, City) Given the database model above, propose a solution for the below business requirement:Business requirement: Assume this database belongs to an e-commerce application and the company has several warehouses in different cities. The company wants to optimize its item shipment time. You can propose a series of SQL queries. Explain your answer in details.
An E-learning website needs your help to design its database. They need to store information about teachers and online tutorials. Each teacher has a unique ID, name, and at least one field of specialty. Teachers form groups to prepare online tutorials. Each group has unique name and at least one teacher as a member. Each online tutorial has a title, date and organized by exactly one group. The title of the tutorial is unique for the group. Each tutorial consists of several sessions, described by their titles and durations. Naturally, each session is presented by one teacher and belongs to exactly one tutorial. In addition, sessions on the same tutorial have different titles. Design an ERD for this application. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.
BOOK Book_id Title Publisher_name BOOK AUTHORS Book_id Author_name PUBLISHER Name Address Phone BOOK_COPIES Book id Branch_id No_of_copies BOOK LOANS Book_id Branch_id Card_no Date out Due_date LIBRARY_BRANCH Branch_id Branch_name Address BORROWER Card_no Name Address Phone Consider the LIBRARY relational database schema shown in Figure 8.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 8.14. Write down relational algebra expressions for the following: A) Find the total number of books published by Macmillan Publishers B) Find the address of the borrower whose card number is '101' C) List the names of the publishers of all the books authored by Dan Brown D) List the names of all the authors and the total number of books each of them authored
Convert the following Relation into INF, 2NF, and 3NF and show the functional dependencies. StudentID StudentName Major CourselD CourseTitle InstructorName InstructorLocation Grade D-Building A-Building C-Building C-Building A-Building D-Building A-Building 111 Kevin CS CS201 Bob Computer Marketing Accounting Accounting Jeff 111 Kevin CS МАЗ3 Jeff 111 Kevin CS АCC234 Sarah 222 Alex BA АСC234 Sarah B. 333 МАЗ33 Marketing Computer Marketing Noor MA 444 CS201 Bob Luay Adam BA A 555 AC МАЗ33 Jeff