Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
luxmraj
Starting Member
1 Post |
 Posted - 2008-10-05 : 22:45:07
|
| Hi, I am still new to the basic SQL programming. I have two question regarding SQL.a. I have two table customer and state. Now I need to show for each state, the state and the number of customers in that state an the output should have an order from largest number of customers to the smallest. Just want to make sure if its right----I wrote this code SELECT STATE, COUNT (NAME) FROM CUSTOMER ORDER BY NAME DESC;b. Secondly, I have to show the result of joining the two tables CUSTOMER and STATE on the two-letter state abbreviation, which is called State in the CUSTOMER table and Abbreviation in the STATE table. I have to do this by showing only the customer name, address, city, the full state name (not the abbreviation), and zip code. (Note that since both tables have a Name attribute, you will have to refer to them as CUSTOMER.Name and STATE.Name to avoid ambiguity.)-- I wrote SELECT CUSTOMER.Name, Address, City, STATE.NAME, Zip FROM CUSTOMER INNER JOIN STATE ON ( CUSTOMER.State=STATE.Abbreviation); orSELECT CUSTOMER.Name, Address, City, STATE.Name, Zip FROM CUSTOMER, STATE WHERE CUSTOMER.State=STATE.Abbreviation;Please let me know if I am doing it right. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 00:02:40
|
a. should beSELECT STATE, COUNT (NAME) AS NameCount FROM CUSTOMER GROUP BY STATEORDER BY NAME NameCount DESC; b. looks fine. But its better to use ANSI join syntax as the other syntax wont be supported in future versions of SQL ServerSELECT CUSTOMER.Name, Address, City, STATE.Name, Zip FROM CUSTOMERJOIN STATE ON CUSTOMER.State=STATE.Abbreviation; |
 |
|
|
|
|
|
|
|
Basic SQL