SQL Question - Need to find the names and counts of a certain region

[shyy]

Hi everyone,

I have these tables but having trouble getting the SQL commands to work.

1. What are the names of customers in NorthEast? I tried connecting the 3 tables but not dice. The program I am using doesn't return the error in english but an error code so I have no way of knowing what I am doing wrong.
2. How many customers is their in NorthEast?

Thanks

The schema are:
CUSTOMER (Customer_ID,Customer Name, CustomerAddress, CustomerCity,CustomerState, Postal Code)
DOES_BUSINESS_IN_T ( Customer_Id, Territory_Id)
TERRITORY_T (Territory_ID, Territory_Name )

 

[shyy]

Figured out the first

SELECT Customer_Name
FROM CUSTOMER_T
WHERE Customer_ID IN (SELECT Customer_ID
FROM DOES_BUSINESS_IN_T
WHERE Territory_ID IN (SELECT Territory_ID
FROM TERRITORY_T
WHERE Territory_Name='NorthEast'))

 

[shyy]

Figured out the second

Figured out the first

SELECT COUNT(Customer_Name)
FROM CUSTOMER_T
WHERE Customer_ID IN (SELECT Customer_ID
FROM DOES_BUSINESS_IN_T
WHERE Territory_ID IN (SELECT Territory_ID
FROM TERRITORY_T
WHERE Territory_Name='NorthEast'))

 

[SydneyGeek]

You could also try these:

SELECT c.Customer_Name
  FROM CUSTOMER_T c,
       TERRITORY_T t, 
       DOES_BUSINESS_IN_T d
 WHERE c.Customer_ID = d.Customer_ID
   AND d.TERRITORY_ID = t.TERRITORY_ID
   AND t.Territory_Name = "NorthEast"
   
SELECT COUNT(c.Customer_Name) Customer_Count
  FROM CUSTOMER_T c,
       TERRITORY_T t, 
       DOES_BUSINESS_IN_T d
 WHERE c.Customer_ID = d.Customer_ID
   AND d.TERRITORY_ID = t.TERRITORY_ID
   AND t.Territory_Name = "NorthEast"

Denis

 

[shyy]

Thanks! I just had to make the quotation single quote and it worked

You made this look so easy

 

[shyy]

Sydney Geek,

How would I re-write this to match your format?

SELECT Employee_Name
FROM EMPLOYEE_T
WHERE Employee_ID IN (SELECT Employee_Id
FROM EMPLOYEE_SKILLS_T
WHERE Skill_Id IN (SELECT Skill_Id
FROM SKILL_T
WHERE Skill_Description = '12in Band Saw'))
AND
Employee_ID NOT IN (SELECT Employee_Id
FROM EMPLOYEE_SKILLS_T
WHERE Skill_Id IN (SELECT Skill_Id
FROM SKILL_T
WHERE Skill_Description <> '12in Band Saw'))

 

[shyy]

Sorry here is an image of both question 6 and 7.




Uploaded with ImageShack.us

 

[SydneyGeek]

I usually approach it like this:
1. Write the FROM clause -- any tables, views etc used in the query. Each table/view/SQL statement gets its own alias (often a single letter)
eg:

FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T j,
SKILLS_T s

2. Then use the WHERE clause to define the joins

FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T j,
SKILLS_T s
WHERE e.EMP_ID = j.EMP_ID
AND j.SKILL_ID = s.SKILL_ID

3. Then put in the fields that you want to display; alias these as required (eg, if you have used SUM or COUNT)
4. If any fields are aggregated (SUM, GROUP, MIN, MAX, etc) you will need a GROUP BY clause for the other fields. Also, ORDER BY to sort them.
5. Any other filters required? Go back to the WHERE clause, and insert AND lines for the filters.
6. If you need to filter any aggregated fields, you use HAVING between GROUP and ORDER BY.

The final structure looks like this:
No grouping:
SELECT [Fields]
FROM [Tables]
WHERE [Joins and conditions]
ORDER BY [Sorting] -- optional

Grouping:
SELECT [Fields]
FROM [Tables]
WHERE [Joins and conditions]
GROUP BY [non-aggregated fields]
HAVING [Filters for sum, count, etc fields] -- optional
ORDER BY [Sorting] -- optional

There's a really good book called Simply SQL by Rudy Limeback, available as a PDF. If you'll be working a lot with SQL, get a copy.

Denis

 

[shyy]

Thanks for the info but I am still at a lost how to incorporate the AND <>.

This is what I have so far.

SELECT Employee_NameFROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description = '12in Band Saw'
AND e.Employee_ID <> s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description <> '12in Band Saw'

 

[SydneyGeek]

This bit covers the first part of the query -- all employees with the Band Saw skill:

SELECT Employee_NameFROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description = '12in Band Saw'

The second bit is all skills other than Band Saw, and you can get that by slightly adjusting the first:

SELECT Employee_ID FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description <> '12in Band Saw'

Now you need to add the second part as another criterion for the main query:

SELECT Employee_NameFROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description = '12in Band Saw'
AND e.Employee_ID NOT IN
(SELECT Employee_ID FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description <> '12in Band Saw')

Denis