How do I write a query involving averages without using SQL functions?

Question

How do I write a query involving averages without using SQL functions?

Answer 1

seems such as you ought to only use a collection by capacity of question and the mixture function sum(). decide on sum(INV_PRICE) * sum(INV_QOH) 'entire' from inventory the place ITEM_ID = 5 you relatively do no longer ought to apply a collection by capacity of, when you consider which you're only coping with a single merchandise. to verify all of them indexed by capacity of ITEM_ID, you will: group by capacity of ITEM_ID and upload ITEM_ID interior the decide on clause.

Answer 2

Assuming your DBMS has a scripting language (PL/SQL for Oracle. T-SQL for SQL Server, etc.), you can do this using what's known as database cursors...this is a method for treating the resultset of a query as a temporary file and retrieving each row of that resultset sequentially for processing. While exact syntax would depend on what DBMS you use, the method is:



1) Define cursor A against SELECT salary FROM works WHERE company_name = "First Bank Corp"

2) Initialize salary_total, employee_count to 0

3) For each row of cursor A fetched, add salary to salary_total and add 1 to employee_count

4) Divide salary_total by employee_count to get target_average_salary

5) Close cursor A

6) Define cursor B against SELECT DISTINCT company_name FROM works WHERE company_name <> "First Bank Corp"

7) For each row of cursor B

a) Define cursor C against SELECT salary FROM works WHERE company_name =

b) Initialize salary_total, employee_count to 0

c) For each row of cursor C fetched; add salary to salary_total and add 1 to employee_count

d) Divide salary_total by employee_count to get test_average_salary

e) If test_average_salary > target_average_salary, display company_name fetched from cursor B

f) Close cursor C

8) Close cursor B



Without a scripting language and disallowing use of SQL functions, I can't think of another method offhand.

Answer 3

Obviously your first step is to find the average salary of employees at "First Bank Corp". To do this you will need to query the WORKS table retrieving the salary for each employee, adding this to a running total, incrementing a counter and filtering on "First Bank Corp". When there are no more records to fetch you can divide the total salary by the count to get your average.



Your second step is then just a simple query of the WORKS table again, retrieving company names for each employee whose salary is greater than the value calculated in step 1. If you can't use any SQL functions then you will (most likely) get duplicate company names so you will have to order the results by company_name and your program will have to detect when the company name changes and then print out the name.



If you're using Oracle you can get some SQL tutorials at http://www.asktheoracle.net/basic-sql-commands.html

Answer 4

What this question is asking you to do is write a an SQL query working out average salaries for each company, and then return the names of the companies that have a higher average salary than the company named "First Bank Corp". This is very easy to do using sql functions such as SUM(), AVG(), COUNT() etc, but the second part clearly states we cannot use these.



I played around with it for a while and it seemed my queries were getting more and more complicated, by creating new tables with numbered rows etc - which seems well beyond the difficulty level of this question. Are you sure you typed the exact wording? Are you sure you gave all the available information? Can you re post this question with part a. which you left out?



A small bit of information like "no two companies are in the same city" can make a heap of difference; since we know no too employees have the same name, and no one employee works for two companies (as employees is primary key in both tables)



Remember, with SQL, we cannot incrementally number a row without using count(). Except if the tables themselves had an identity column incrementing by 1, which these don't. And since it is non procedural, we cannot ensure a row value has a number one higher than the previous row without using at the least a count() function.



------------

In response to additional the details, Have you been taught about cursors yet? That is, would you like an answer involving a cursor? It can be done that way. But if you haven't that will be a useless answer to you. I'll keep thinking about it for a more simple and clean answer....

This is quite a tough one but a good question, I need to find this textbook!

Answer 5

You'll have to write a program that steps through your works table and figures the average salary for each company, then list out which ones are higher than First Bank Corp without using SQL.



How you do it depends on what language you are using.