BANA614-Quiz4-TakeHome (1)

docx

School

California State University, Northridge *

*We aren’t endorsed by this school

Course

614

Subject

Computer Science

Date

Feb 20, 2024

Type

docx

Pages

3

Uploaded by peivandm1360

Report
Last Name: First Name: Score (Prof use): ______________________________ _________________________ BANA 614 Quiz 4 Correlated Subquery and Self Join Using our "old friend" Restaurants table, Find the restaurants whose sales is at least 75% of the average of its own type of service . Pick the correct SQL code (circle), briefly explain the parameter passing (right or wrong, and how) : SELECT RestaurantID, AnnualSales, TypeOfService FROM Restaurants WHERE AnnualSales >= .75* (SELECT AVG(AnnualSales) FROM Restaurants Out_Table WHERE TypeOfService = Out_Table.TypeOfService GROUP BY TypeOfService) Incorrect This query has an issue with parameter passing. The subquery refers to Out table which is not an alias used in the main query. It should use the correct alias (or table name) for correlation. SELECT RestaurantID, AnnualSales, TypeOfService FROM Restaurants Cat WHERE AnnualSales >= .75* (SELECT AVG(AnnualSales) FROM Restaurants WHERE TypeOfService = Cat.TypeOfService GROUP BY TypeOfService) Correct It uses the alias Cat for the outer query, and the subquery correctly refers to the Typeofservice of the outer query for correlation. This ensures that the condition is based on the same Typeofservice SELECT RestaurantID, AnnualSales, TypeOfService FROM Restaurants R WHERE AnnualSales >= .75* (SELECT AVG(AnnualSales) FROM Restaurants Out_Table WHERE TypeOfService = Big_Table.TypeOfService GROUP BY TypeOfService) Incorrect his query has an issue with parameter passing. The subquery refers to big table, which is not an alias used in the main query. It should use the correct alias (or table name) for correlation SELECT RestaurantID, AnnualSales, TypeOfService FROM Restaurants WHERE AnnualSales >= .75* (SELECT AVG(AnnualSales) FROM Restaurants WHERE TypeOfService = Restaurants.TypeOfService GROUP BY TypeOfService) Incorrect is query has an issue with parameter passing. It references the outer table directly in the subquery without using an alias. This could lead to incorrect results as it's not properly aliased for correlation. BBBB BBBB Please explain every one of the four choices BBBB
Last Name: First Name: Score (Prof use): ______________________________ _________________________ Self-join question SELECT M.Name, M. LoanSpecialty, E.Name, E. LoanSpecialty FROM [Loan Officers] M, [Loan Officers] E WHERE M.OfficerID = E.OfficerID AND M.LoanSpecialty = E. LoanSpecialty incorrect This query selects pairs of managers (M) and employees (E) where both have the same officer ID and loan specialty. However, it doesn't specifically enforce the condition that E is managed by M. Therefore, it may retrieve incorrect pairs. SELECT M.Name, M. LoanSpecialty, E.Name, E. LoanSpecialty FROM [Loan Officers] M, [Loan Officers] E WHERE M.OfficerID = E.Supervisor AND M.LoanSpecialty = E. LoanSpecialty incorrect This query attempts to find pairs where the manager's officer ID matches the employee's supervisor ID and both have the same loan specialty. However, it doesn't guarantee that M is the actual supervisor of E, which may lead to incorrect results. SELECT M.Name, M. LoanSpecialty, E.Name, E. LoanSpecialty FROM [Loan Officers] M, [Loan Officers] E WHERE M.Supervisor = E.OfficerID AND M.LoanSpecialty = E. LoanSpecialty incorrect This query compares the supervisor's ID of M with the officer ID of E. It doesn't ensure that M is the supervisor of E, potentially leading to incorrect results. SELECT M.Name, M. LoanSpecialty, E.Name, E. LoanSpecialty FROM [Loan Officers] M, [Loan Officers] E WHERE M.Supervisor = E.Supervisor AND M.LoanSpecialty = E. LoanSpecialty Correct This query correctly uses a Join condition to ensure that E is an employee supervised by M. It compares both the supervisor and loan specialty, ensuring that the manager and employee have the same specialty. This is the suitable query for the specified task. Given the [Loan Officers] table above, create a query that will display the loan specialty of employees who have same specialty as THEIR managers. Pick the correct SQL code (circle) and briefly but clearly explain the join condition : Please explain the join condition (right, or wrong, and why) for each of the four choices B B
Last Name: First Name: Score (Prof use): ______________________________ _________________________ Pledge of academic honesty I, ___Peivand Mehrabadi____________ (Print Last Name, First Name), am hereby pledging academic honesty regarding this take-home quiz: All the work regarding this quiz is my work; I only studied all the materials provided/approved by the professor – - textbook, - PowerPoint slides, - Handouts, - Teaching notes, - My own Web search; I did not obtain helps from any person, no matter within or outside my BANA 614 class. If any collaboration is found, I will accept any disciplinary measure as the result of the collaboration. Name: ___Mehrabadi___________Peivand_____________ ( Last, First ) Signature: _______________ Peivand Mehrabadi___________________________________ Date of Signature:_______12/09/23_______________ ( MM / DD / YYYY )
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

CST8116-Lab Ex2 weeek 3 assignment FINAL.docx
CST8116-Exercise 3( yuvraj singh ).docx
2-more_exercises.pdf
CHCSOH013_AE_CS.docx
ml assignment 5.pdf
Question 1.docx
Hands-on8.docx
Assignment 2.docx
Assignment 3.docx
Assignment 5.docx
Assignment 8.docx
Assignment 7.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS