LEARN MORE...

Sampdb Queries

Content type

User Generated

Subject

MySQL

School

American University

Course

ISM 641 Database Design and Management

Type

Homework

Uploaded By

ghpxrearnffn00

Pages

6

Showing Page:

1/6

Sign up to view the full document!

Showing Page:

2/6

Sign up to view the full document!

Showing Page:

3/6

Sign up to view the full document!

End of Preview - Want to read all 6 pages?

Unformatted Attachment Preview

Question 1 INNER JOIN Question 1 OUTER JOIN Question 1 SUBQUERY Question 2 Question 3: Query Optimizer The query optimizer is useful in the situation where the database is too large and can result in slow performance if query optimization is not utilized in database design. To be able to determine the amount of time and resources needed to execute a query, ‘explain’ statement can be prepended to the query. The information returned shows how MySQL executes a given query. This information can be helpful in figuring out fields that need to be indexed in a database. An illustration shown above shows that the first query in question 1 has got a key ‘student_id’ in scores table which references the same key in the student table and only 2 rows can be searched to return the required results. Therefore, MYSQL will first execute the inner query in order to efficiently return the expected results. It is a good idea to index all the columns found under GROUP BY, WHERE and ORDER BY clauses to be able to query the database more efficiently. Subsequently, the following screenshot shows that ‘student_id’ in scores table has an index hence only 1 row will be searched against 173 rows in the second table. On the contrary, the search will go through 173 rows in the subquery in order to return the needed result as shown below. In order to improve on performance, an index has to be created on the score field of the scores table. The following query will take considerably m ...
Purchase document to see full attachment

Tags: MYSQL database design phpmyadmin

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Studypool

4.7

Indeed

4.5

Sitejabber

4.4

24/7 Study Help

Stuck on a study question? Our verified tutors can answer all questions, from basic math to advanced rocket science!

Post question

3 pages

1. Compare the means of Total Directors' Cup Points between private schools and state schools. Only include schools who sc ...

1. Compare the means of Total Directors' Cup Points between private schools and state schools. Only include schools who scored points in the ...

Option #1: Queue Class Data Structures

In Week 8, the components left to complete for your Portfolio Project are the Lessons Learned Reflectionand the Final Prog ...

Option #1: Queue Class Data Structures

In Week 8, the components left to complete for your Portfolio Project are the Lessons Learned Reflectionand the Final Program. Carefully review the requirements below:Lessons Learned Reflection:Write a two- to three-page summary that outlines the lessons you have learned in this programming course. Reflect on how these lessons can be applied towards more effective coding.Final Program:Write a program that creates a Person class that contains strings that represent the first and last name of a person and their age. You will need to create a Queue class that will store each person in the queue and can sort the queue based on last name or age.Prompt the user of the program to add five people to the queue. Your program should provide the contents of the queue and then sort the queue using the quick sort in two ways:Descending order by last name.Descending order by age.Assemble your Lessons Learned Reflection, your source code, and screenshots of the application executing and results into a single document. Submit your completed Portfolio Project by the posted due date.

CIS 365 Arizona State University Trigger and Procedures MySQL Task

Q1. (10) Trigger: assignhousekeepingWhen a new condo is added to the database, automatically add a new record to the clean ...

CIS 365 Arizona State University Trigger and Procedures MySQL Task

Q1. (10) Trigger: assignhousekeepingWhen a new condo is added to the database, automatically add a new record to the cleaning table.The new condo will be cleaned by Janice Avery the day it is added to the condo table; use today's date as the DateCleaned. Show screenshots of your trigger code (no hard-coding) along with screenshots of:The record has been correctly added to the cleaning table.The record has been correctly added to the condos table. Use this insert statement to test your trigger: Insert into condos values (99, 'Z', '101', 1030, 2, 1, 999.00);Q2. (16) Procedures to - CalculateMcLeanFinalInvoiceThe McLean family is challenging their total bill. Retrieve all invoice data (including details) for the McLean family for 2019, invoice 20065 (see data required on EERD). Use a sales tax of 8% when calculating the total billed. Hint: Create a series of procedures to create base tables to store intermediate calculations:Calculate and store the McLean activity totals,

SDEV 300 UMUC Python Programming Codes Lab Report

I have the solution of the lab. I just want it to be plagiarism free and I also need a test documentation for it.

SDEV 300 UMUC Python Programming Codes Lab Report

I have the solution of the lab. I just want it to be plagiarism free and I also need a test documentation for it.

CPT 200 Ashford University Week 3 Programming Languages Report

Critically review your classmate’s program to check for defects, code redundancy, and/or missing comments that your peer ...

CPT 200 Ashford University Week 3 Programming Languages Report

Critically review your classmate’s program to check for defects, code redundancy, and/or missing comments that your peer failed to address in their code. Identify any missing elements in their description of developing the program.Response number 1 Quy NguyenMar 18, 2021 at 5:37 PMHello Class,This week's interactive assignment was a challenge for me, but I managed to complete the task. My biggest issue was creating a loop to cycle my program once executed so my users can restart within, rather than closing and re-launching it. I tested my loop() code which works on its own, works with selecting the count down, but errors when running the factorial. Any insight?def loop(): repeat = input("Would you like to try again (Yes/No)? ").lower() if repeat == "yes": loop() else: print("Goodbye!") exit()loop()I started my process by defining the countdown and factorial functions, utilizing the while loop, if-else, and elif statements to compute and execute the countdown from whichever integer is inputted along with its factorial. I find it quite frustrating how particular Python is with spacing and that every variable is case-sensitive. Chapters 5 and 6 have a lot of terminologies that I'll have to revisit and fully understand.Response number 2 Garrett MillerMar 18, 2021 at 7:06 PMHello class,For the interactive assignment for week 3, we were specifically asked to use loops and functions for this program, so I started with chapters 5 and 6 from our text. Before the loop was put in place, it was necessary to build and define functions for the program. Since we wanted a simple countdown process, I did use a loop for the countdown function. There was not much help that I could find about this function online. I did remember in our text back in chapter 2 about subtracting and adding numbers, so I built the small while loop to subtract then print the numbers until it got to zero. When I first did this, I wrote: ‘while ( n >= 1):’ but, when the numbers would count back, it would stop at one, so I tried ‘while (n > -1):’ which worked, but it looked odd to me, so I used ‘while (n >= 0):’ which looks better and works just the same.For the factorials, I built a small while loop that takes the factor of 1 only as a place holder until the user inputs the number of their choice. Then the loop will take the integer inputted, multiply it by the fact value, then subtract 1, and replace the fact value with that new number. This will continue until it gets to zero; hence, ‘while (n > 0): The return statement is used to keep the function running until it is complete.After writing the choices function, I could not get the program to run at all. It would immediately quit. After lots of research, I found a few examples of functions that closed with the function name, so I thought I’d try that, and it worked. I honestly do not know exactly what it does or what it is called, but it seems that in order to get a function to operate, you either have to make a return statement or close it out with the name of the function. It wasn't easy to find much on this. I am still having trouble getting a program to repeat itself, so any help and input is appreciated! Thank you!

Similar Documents

Python Assignment

1. What are library modules? Provide an example of how might you use a module as a programmer?1. What is List Comprehensi...

How to Attach a File of CSS to HTML Discussion

How to attach a file of css to html?...

COP3084- Intermediate Java Programming

All instructions are in the images below. This must be done through NetBeans and the zip file must be completely filled ou...

Solve the following programming problem: A robot moves in a plane starting from the point (0,0). The robot can move UP, DOWN, LEFT and RIGHT with a given number of steps, for example: UP 5 DOWN 3 LEFT 3 RIGHT 2

Solve the following programming problem: A robot moves in a plane starting from the point (0,0). The robot can move UP, D...

University of Cambridge Create a Coding Program Computer Science Task

The distant planet X is orbited by two moons, Alpha and Beta. The planets calendar year has 157 days, and it features two ...

C sharp assignment i need your help

hello brother how are you doing ?............................................................................................

Javascript Snippet

Plag Rep Digital Evidence Firewalls

Ai Data Analysis

Figure 20: Using Neural Network, Stochastic Gradient Descent and Regression .................................................

Study help

Our tutors provide high quality explanations & answers.

Post question

3 pages

1. Compare the means of Total Directors' Cup Points between private schools and state schools. Only include schools who sc ...

1. Compare the means of Total Directors' Cup Points between private schools and state schools. Only include schools who scored points in the ...

Option #1: Queue Class Data Structures

In Week 8, the components left to complete for your Portfolio Project are the Lessons Learned Reflectionand the Final Prog ...

Option #1: Queue Class Data Structures

In Week 8, the components left to complete for your Portfolio Project are the Lessons Learned Reflectionand the Final Program. Carefully review the requirements below:Lessons Learned Reflection:Write a two- to three-page summary that outlines the lessons you have learned in this programming course. Reflect on how these lessons can be applied towards more effective coding.Final Program:Write a program that creates a Person class that contains strings that represent the first and last name of a person and their age. You will need to create a Queue class that will store each person in the queue and can sort the queue based on last name or age.Prompt the user of the program to add five people to the queue. Your program should provide the contents of the queue and then sort the queue using the quick sort in two ways:Descending order by last name.Descending order by age.Assemble your Lessons Learned Reflection, your source code, and screenshots of the application executing and results into a single document. Submit your completed Portfolio Project by the posted due date.

CIS 365 Arizona State University Trigger and Procedures MySQL Task

Q1. (10) Trigger: assignhousekeepingWhen a new condo is added to the database, automatically add a new record to the clean ...

CIS 365 Arizona State University Trigger and Procedures MySQL Task

Q1. (10) Trigger: assignhousekeepingWhen a new condo is added to the database, automatically add a new record to the cleaning table.The new condo will be cleaned by Janice Avery the day it is added to the condo table; use today's date as the DateCleaned. Show screenshots of your trigger code (no hard-coding) along with screenshots of:The record has been correctly added to the cleaning table.The record has been correctly added to the condos table. Use this insert statement to test your trigger: Insert into condos values (99, 'Z', '101', 1030, 2, 1, 999.00);Q2. (16) Procedures to - CalculateMcLeanFinalInvoiceThe McLean family is challenging their total bill. Retrieve all invoice data (including details) for the McLean family for 2019, invoice 20065 (see data required on EERD). Use a sales tax of 8% when calculating the total billed. Hint: Create a series of procedures to create base tables to store intermediate calculations:Calculate and store the McLean activity totals,

SDEV 300 UMUC Python Programming Codes Lab Report

I have the solution of the lab. I just want it to be plagiarism free and I also need a test documentation for it.

SDEV 300 UMUC Python Programming Codes Lab Report

I have the solution of the lab. I just want it to be plagiarism free and I also need a test documentation for it.

CPT 200 Ashford University Week 3 Programming Languages Report

Critically review your classmate’s program to check for defects, code redundancy, and/or missing comments that your peer ...

CPT 200 Ashford University Week 3 Programming Languages Report

Critically review your classmate’s program to check for defects, code redundancy, and/or missing comments that your peer failed to address in their code. Identify any missing elements in their description of developing the program.Response number 1 Quy NguyenMar 18, 2021 at 5:37 PMHello Class,This week's interactive assignment was a challenge for me, but I managed to complete the task. My biggest issue was creating a loop to cycle my program once executed so my users can restart within, rather than closing and re-launching it. I tested my loop() code which works on its own, works with selecting the count down, but errors when running the factorial. Any insight?def loop(): repeat = input("Would you like to try again (Yes/No)? ").lower() if repeat == "yes": loop() else: print("Goodbye!") exit()loop()I started my process by defining the countdown and factorial functions, utilizing the while loop, if-else, and elif statements to compute and execute the countdown from whichever integer is inputted along with its factorial. I find it quite frustrating how particular Python is with spacing and that every variable is case-sensitive. Chapters 5 and 6 have a lot of terminologies that I'll have to revisit and fully understand.Response number 2 Garrett MillerMar 18, 2021 at 7:06 PMHello class,For the interactive assignment for week 3, we were specifically asked to use loops and functions for this program, so I started with chapters 5 and 6 from our text. Before the loop was put in place, it was necessary to build and define functions for the program. Since we wanted a simple countdown process, I did use a loop for the countdown function. There was not much help that I could find about this function online. I did remember in our text back in chapter 2 about subtracting and adding numbers, so I built the small while loop to subtract then print the numbers until it got to zero. When I first did this, I wrote: ‘while ( n >= 1):’ but, when the numbers would count back, it would stop at one, so I tried ‘while (n > -1):’ which worked, but it looked odd to me, so I used ‘while (n >= 0):’ which looks better and works just the same.For the factorials, I built a small while loop that takes the factor of 1 only as a place holder until the user inputs the number of their choice. Then the loop will take the integer inputted, multiply it by the fact value, then subtract 1, and replace the fact value with that new number. This will continue until it gets to zero; hence, ‘while (n > 0): The return statement is used to keep the function running until it is complete.After writing the choices function, I could not get the program to run at all. It would immediately quit. After lots of research, I found a few examples of functions that closed with the function name, so I thought I’d try that, and it worked. I honestly do not know exactly what it does or what it is called, but it seems that in order to get a function to operate, you either have to make a return statement or close it out with the name of the function. It wasn't easy to find much on this. I am still having trouble getting a program to repeat itself, so any help and input is appreciated! Thank you!