How to limit the ammount of data entered in my SQL Database?

[papuccino1]

Hello.

I'd like someone to help me figure this out.

I have a small database: Country, State, City. Three tables.

I have to check that none of the names repeat themselves. Meaning, no two USA's, no two Brazil's, etc.

My professor also gave us the following requirement:

You can't delete a City entry that has a boolean set to true. God knows how to do this one. :P

Also:

Generate a list showing all the Cuntries you have with the respective population. So I'd have to add all the population in the cities that are in the countries states that are in the cuntrie. I guess I could do this by generating a table with only two columns.

I know, I know. This probably looks like a cheap attempt to have my homework done for me, but I assure you this little project is for 7 days from now so no rush. :P I've been a member here for quite some time, so I'm not gonna "hit it 'n' quit it".

If possible can someone give me a tecnical name for this sort of things in SQL. Or even better a link explaining some of them.

Thank you. :D

[LizR]

1. Well the obvious answer is to look see if its there.
2. See 1
3 yes.

Your statements sound like you have already been shown/explained answers.

So Start with the first problem you need to find out if the data is new data or not..
Go with that.. attempt some answers, post your ideas..

[papuccino1]

Liz, once again your answers are of no use to anyone but your ego.

What I'm asking for is generic pseudo-code. Your crappy list hardly answers any single one of my questions.

Now back on topic.

I've discovered I can use a for loop to check every cell in a column and see if the names collide BEFORE sending the SQL Command to the Database. I guess that is more efficient than programming the duplicate check in the SQL Command.

What are you thoughts guys?

[sknake]

First off this is in the wrong forum :( -- Please post these question to SQL in the future.

Secondly what version of SQL is this? In SQL 2005 they introduced "Delete Top" so the mechanism I would use for going about this task is quite different depending on the version.

Third, can you post sample data? If not at least give the table structure. A unique row identifier may come in to play here.

Once you get #2/#3 answered i'll give it a go.

[papuccino1]

I'm using SQL Server 2008.

**Country**
IDCountry : int
Name : varchar(25)

**City**
IDCity : int
Name : varchar(75)
Autonomous : bit (boolean)
IDCountry : int (Foreign Key)

**Province**
IDProvince : int
Name : varchar(75)
Population : int
IDCity : int (Foreign Key)

Mostly I'm looking for an answer to sum up the population of any given country, generating a two column table, COUNTRY and POPULATION respectively. I'm guessing using the Sum() function? I'm lost here fellas :P

[sknake]

Mostly I'm looking for an answer to sum up the population of any given country, generating a two column table, COUNTRY and POPULATION respectively. I'm guessing using the Sum() function? I'm lost here fellas :P

Something along the lines of this should work

Select Max(Country.Name) As CName, Sum(Province.Population) As TotalPop
From Country Left Join City On (Country.IDCountry = City.IDCountry)
             Left Join Province On (City.IDCity = Province.IDCity)
Group By Country.IDCountry
Order By CName

(I chose the grouping off the ID because i assumed the ID's were unique and not specified in the table structure you posted. The way I chose to group the records will show you two records for "USA" in the Country table instead of aggregating them together, that way you can visually see if a country has a repeating name...if my assumption is correct)

The problem here is that if you have duplicate records on the 2 parent tables and have to delete them, then this could possibly change the values you get for the total population.

Given the parameters of your assignment my *guess* is that none of the countries repeat themselves. I am willing to bet that the cities do repeat themselves, and all of the population records in the Province table are linked to the City table on records that have the "master bit" set true, that way you can't accidently delete the duplicate record that has associated child records. By doing this the professor knows exactly what the totals should look like regardless of what record you deleted, because you have to maintain the records with the bit set....

[LizR]

Liz, once again your answers are of no use to anyone but your ego.

What I'm asking for is generic pseudo-code. Your crappy list hardly answers any single one of my questions.

Well perhaps its your ego thats got the problem..
You showed NO effort at all, but now feel its fine and dandy to poke at someone else.. This is an exceptionally childish attitude

You didnt ask for pseudo-code, you didnt really ask for anything.
I asked you to try something, for which you didnt bother but come back with insults.

my "crappy" list gives you more info than you put in effort for your first post.

[sknake]

Well perhaps its your ego thats got the problem.. You showed NO effort at all, but now feel its fine and dandy to poke at someone else.. This is an exceptionally childish attitude

You didnt ask for pseudo-code, you didnt really ask for anything. I asked you to try something, for which you didnt bother but come back with insults.

my "crappy" list gives you more info than you put in effort for your first post.

After looking at his thread history I agree