Sql queries are displaying incorrect data

Question

Hi i have the following database scheema

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)

I need to do the following but according to my trainner they are wrong.

Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

SELECT company-name, city
FROM company
UNION ALL
SELECT company-name, city
FROM company
WHERE company-name = ‘Small Bank Corporation’

Find all employees who earn more than the average salary of all employees of their company.

SELECT employee-name
FROM works
WHERE salary > ( SELECT AVG(w1.salary) 
FROM works w1
INNER JOIN works w2
ON w1.company-name = w2.company-name)

Find the company that has the most employees.

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) = ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

Find the company that has the smallest payroll.

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) < ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

Answer 1

Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

    SELECT company-name,city FROM company 
    WHERE city IN(SELECT city  FROM company 
WHERE company-name='Small Bank Corporation');

Answer 2

That is a lot to tackle for a single answer, so I'll lay out where you went wrong in the first example, how to fix it, and how you can use that strategy for the remaining examples. You may want to split the remaining problems into new questions here on Stack Overflow. As you can see from the answers given last week, people will be reluctant to answer the entire request.

So, the problem:

Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

Step 1: Reading the Prompt Correctly

I speak with complete sincerity when I say that this is often the hardest part of solving these sorts of problems. Language isn't always as specific as we would like it to be, and different people can interpret the same sentence multiple ways. Be sure to communicate with your instructor if there is any ambiguity in a prompt, and ask them to clarify what you will need to write a query for.

Step 2: Reviewing What Went Wrong

So the query you wrote doesn't solve the problem that was asked. Why not? We can start to answer that question by figuring out what exactly your query does. We can start by breaking it up into bits:

SELECT company-name, city
FROM company

The code above will get the company name and city from every row in the table called "company."

SELECT company-name, city
FROM company
WHERE company-name = ‘Small Bank Corporation’

The code above will get the company name and city from every row in the table called company where the company name is 'Small Bank Corporation.'

UNION ALL

The line above will create a union of the selections before and after it. So you created a union of all of the rows in the table, and all of the rows with company name of 'Small Bank Corporation.' This not only does not solve the query as given above, but also gives us additional, redundant rows that we did not ask for. We can create a mockup table company to demonstrate exactly what this does.

company-name            |  city
--------------------------------
Small Bank Corporation  |   A
Silly Bank Corporation  |   B
Large Bank Corporation  |   C

In this example, we can see that Small Bank Corporation is the only company in city A. So it would be the only row returned when we specify that with our WHERE statement. So when we union the results, we should get as follows below.

company-name            |  city
--------------------------------
Small Bank Corporation  |   A
Silly Bank Corporation  |   B
Large Bank Corporation  |   C
Small Bank Corporation  |   A

Which isn't really helpful. So how do we go about fixing this?

Step 3: Understanding the problem

The prompt asks to find all companies located in every city in which Small Bank Corporation is located. My interpretation of that statement is that in every city where Small Bank Corporation exists, we want the list of every bank in town. Our final result will be a list of all banks in every town where Small Bank Corporation exists. A bank might, for example, want this information if they want to see a full list of their competition.

Step 4: Revisions

Now we can begin to work towards a correct query. You know which variables you want to select, so we don't need to change that first line. There are many ways to go about writing the query, so for the benefit of your increased exposure to SQL I'll offer an alternative to existing answers.

SELECT A.company_name
FROM company A, company B 
WHERE A.city = B.city AND B.company_name = 'Small Bank Corporation'
GROUP BY company_name;

Allow me to break down what is happening here:

• A table that is the cross product of company and company is created. (company A x company B)
• From that table we want to project all rows where city A and city B are the same, and where company B is Small Bank Corporation.
• From that projection, we want to select for company name.
• Use group by to remove redundant company names (multiple rows with the same company name can exist in this problem, because the same company can exist in different cities).

Try working out the rest of your prompts with the steps I've provided, and feel free to open more specific questions here if you continue to have difficulty.

Answer 3

Find all employees who earn more than the average salary of all employees of their company.

SELECT employee-name
FROM works
WHERE salary > ( SELECT AVG(salary) 
FROM works )

Find the company that has the most employees.

select company-name from works group by company-name having count(*) 
= (select max(count(*)) from works group by company-name);

Answer 4

Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

    SELECT company-name,city FROM company 
    WHERE city IN(SELECT city  FROM company 
WHERE company-name='Small Bank Corporation');

It cannot work correctly, any line match will return result, the problem is located in every city

The following are my answers: Assume that the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.

SELECT company_name
FROM company
WHERE not exists (
SELECT city
FROM company
except
SELECT city
FROM company 
WHERE company_name = 'Small Bank Corporation'
);