You are a database engineer and the client wants you to solve the following business problem for a database system: "For each course find the number of students that got a grade of 90% or better. Show the title of the coursesand the average GPA of all the students in those courses (taking into account only students with GPA greater than 3.0)*After analyzing the problem, you came up with two possible queries that may lead to the correct output:"SELECT Course.title as Course, avg(Student.gpa) as StudentGPA FROM Student, Enrollment, Course WHERE Course.id = Enrollment.course_id AND Student.id = Enrollment.student_id AND Course.name = "CSC675" ANDEnrollment.grade >= 90 GROUP BY Course.title HAVING StudentGPA > 3.0";"SELECT Course.title as Course, avg(Student.gpa) as Student GPA FROM Student JOIN Enrollment ON Student.id = Enrollment.student_id JOIN Course ON Course.id = Enrollment.course_id WHERE Course.name = X ANDEnrollment.grade >= 90 AND StudentGPA > 3.0 GROUP BY Course.title";Which of the queries is the one that will output the correct data for this business problem?a. None of themb. Both of themc. The first queryd. The second query

Business Problem Recap:For each course, find the number of students that got a grade of 90% or…

Answered: You are a database engineer and the…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

INFO 2303 Database Programming Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.
In OES4 database, we can verify the values stored in subtotal field by using the following 1: col gross_sales for 99999999 col subtotal for 99999999 select o.order_no,sum(qty*Unit_cost) Gross_Sales, sum(o.subtotal) subtotal from orders o, orderline ol, product p where o.order_no=ol.order_no and ol.product_no=p.product_no group by o.order_no; II: col gross_sales for 99999999 col subtotal for 99999999 select o.order_no,sum(qty*Unit_cost) Gross_Sales, sum(o.subtotal) subtotal from orders o, orderline ol, product p where o.order_no=ol.order_no and ol.product_no=p.product_no group by o.order_no; I & II: are correct I & II: are not correct | || I & II: are not correct I & II: are correct
In Oracle database, if we have any field such as Job_Title field in any of our table then we can use DECODE function to sort the result based on our own numerical value. This can be done by including the following in ORDER BY clause: 1: ORDER BY DECODE(job-title,'Manager',3,Assistant',2,'Sales Rep', 1) DESC II: ORDER BY DECODE(job-title,'Manager', 3,'Assistant',2, Sales Rep', 1) ASC III I and II are correct IV I and II are incorrect ||| IV || 1
Please answer all my questions by using sql
Question:1 Show all data from employee table where name should be in ascending and salary in descending order. Question:2 Show the total length of (eno, deptno), joined it and rename by function from the employee table? Question:3 Show the ename first 3 alphabets of manager? Question:4 Write a query that show the manager data like this.ename jobJONES ***MANAGER BLAKE ***MANAGER Question:5 Calculate the percentage of any student and round to 0 digit? Question:6 Show the last date of December 2014? Question:7 Show the date of next data base class and rename by cs-400? Question:8 Convert today date to character?
True or False below: Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
Composite main keys have prospective keys.
I would like to create a function to sum up total order amount within the date range, but i don't know how to write the right query. The query below is for counting the total orders, but I want to change to sum up total order amount. The Database table is attached. Please assist. ************************************** USE SQLBook; DROP FUNCTION IF EXISTS GetDateRangewithordersAmountGO CREATE FUNCTION GetDateRangewithordersAmount( @startDate DateTime, @endDate DateTime)RETURNS TABLE ASRETURN ( select [State], count (*) as [TotalOrderAmount] FROM [SQLBook].[dbo].[Orders] WHERE [OrderDate] between @startDate AND @endDate group by [State])GO SELECT * FROM GetDateRangewithordersAmount('2015-09-01','2015-10-31');GO
This is for SQL Give me a list of all of our vendors (by their name) and, if they have an invoice, give me the total number of invoices we have from them and the invoice date of their first invoice. The invoice date results should be provided in a way that shows the date only in the following format: mm/dd/yyyy (e.g. 10/04/2021)
What is the use of sub queries?
In a relational database, you may narrow down the number of columns in a table that match your criteria by using the select operation.Is it the right or wrong answer?
SQL Database Write PL/SQL or T-SQL procedures to accomplish the following tasks: Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_ LAST_NAME. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME. Add a row to the GUIDE table. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME. Delete the guide whose number is stored in I_GUIDE_NUM

SEE MORE QUESTIONS

Recommended textbooks for you

Database System Concepts

Computer Science

ISBN:

9780078022159

Author:

Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:

McGraw-Hill Education

Starting Out with Python (4th Edition)

Computer Science

ISBN:

9780134444321

Author:

Tony Gaddis

Publisher:

PEARSON

Digital Fundamentals (11th Edition)

Computer Science

ISBN:

9780132737968

Author:

Thomas L. Floyd

Publisher:

PEARSON

Database System Concepts

Computer Science

ISBN:

9780078022159

Author:

Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:

McGraw-Hill Education

Starting Out with Python (4th Edition)

Computer Science

ISBN:

9780134444321

Author:

Tony Gaddis

Publisher:

PEARSON

Digital Fundamentals (11th Edition)

Computer Science

ISBN:

9780132737968

Author:

Thomas L. Floyd

Publisher:

PEARSON

C How to Program (8th Edition)

Computer Science

ISBN:

9780133976892

Author:

Paul J. Deitel, Harvey Deitel

Publisher:

PEARSON

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781337627900

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Programmable Logic Controllers

Computer Science

ISBN:

9780073373843

Author:

Frank D. Petruzella

Publisher:

McGraw-Hill Education

SEE MORE TEXTBOOKS