SQL query question

M

Mavix

Senior Member
Joined
Aug 14, 2006
Messages
759
Reaction score
3
Location
Durban
I wrote an IT prac today, and we had one question that puzzled me quite a lot. There's a database with a table of soccer players along with the countries they come from. Now I had to create an SQL statement to get these countries, but they mustn't be repeated. Obviously "SELECT Country FROM PlayersTbl" will return the countries, but what would one add so that countries wouldn't appear more than once?
I've searched through my books but couldn't find anything...
 
Sackboy said:
/ratz - - too slow (at least mine was in CAPS ;-) )
Click to expand...

well that depends on your style, or predescribed syntax ethics (which most places have or should have for coherency)

Code: 
SELECT DISTINCT Countrys
FROM PlayersTbl

vs

Code: 
SELECT
     Distinct Country
FROM
     PlayersTbl

personally 2nd by which i go anyways and who the hell names his tables <tablename>Tbl ? :rolleyes::D
 
Personally I'd rather use GROUP BY instead of distinct as it may cause issues lateron if your query grows a bit more complex. DISTINCT surely will work, but consider:

Code: 
SELECT Country FROM PlayersTbl GROUP BY Country

instead.
 
AcidRaZor said:
Personally I'd rather use GROUP BY instead of distinct as it may cause issues lateron if your query grows a bit more complex. DISTINCT surely will work, but consider:

Code: 
SELECT Country FROM PlayersTbl GROUP BY Country

instead.
Click to expand...

that too, but he was only using a single column from single table without any criteria, but wanted to mention the group by which you have now :)
 
Necuno said:
personally 2nd by which i go anyways and who the hell names his tables <tablename>Tbl ? :rolleyes::D
Click to expand...

People who haven't designed databases? ;)

I usually go: tblSoccerPlayers & tblCountries

My stored procedures usually also follow a specific naming convention:

proc_Soccer_getPlayers
proc_Country_getCountryName
proc_Country_getCountryNameof_SoccerPlayer

I know the lenght of the name *might* make you go "oh noes!" but it sure as hell helps when you're in a team of 5+ developers trying to make sense of what one another did. proc_Module_function/Description
 
Necuno said:
that too, but he was only using a single column from single table without any criteria, but wanted to mention the group by which you have now :)
Click to expand...

Yea, I know, but lets assume that another question would ask him to only select countries with more than 15 players per country (sort of a surplus calculation for, don't know, player trading between countries). Then if he was using GROUP BY he could easily use HAVING and do a count ;)

But yea... if that was the question and there was no other forms of it, DISTINCT would work as well. If it were me (I always read up on subjects before I go to classes anyway, just to **** with the lecturer a bit), I would use GROUP BY and see if I get marked an "incorrect" because they were expecting a DISTINCT ;)
 
AcidRaZor said:
People who haven't designed databases? ;)

I usually go: tblSoccerPlayers & tblCountries

My stored procedures usually also follow a specific naming convention:

proc_Soccer_getPlayers
proc_Country_getCountryName
proc_Country_getCountryNameof_SoccerPlayer

I know the lenght of the name *might* make you go "oh noes!" but it sure as hell helps when you're in a team of 5+ developers trying to make sense of what one another did. proc_Module_function/Description
Click to expand...

i just name tables as SoccerPlayers and Countries since it is tables no need to prefix them as tables.

StoredProcedures as usp<action><name> like for example uspGetCountries or uspSaveCountry (one save does the record initial insert and update)

Functions as f<name>

do you prefix your column names as well like for example <type>Name or something ?
 
AcidRaZor said:
Yea, I know, but lets assume that another question would ask him to only select countries with more than 15 players per country (sort of a surplus calculation for, don't know, player trading between countries). Then if he was using GROUP BY he could easily use HAVING and do a count ;)

But yea... if that was the question and there was no other forms of it, DISTINCT would work as well. If it were me (I always read up on subjects before I go to classes anyway, just to **** with the lecturer a bit), I would use GROUP BY and see if I get marked an "incorrect" because they were expecting a DISTINCT ;)
Click to expand...

/agrees :D
 
Necuno said:
i just name tables as SoccerPlayers and Countries since it is tables no need to prefix them as tables.

StoredProcedures as usp<action><name> like for example uspGetCountries or uspSaveCountry (one save does the record initial insert and update)

Functions as f<name>

do you prefix your column names as well like for example <type>Name or something ?
Click to expand...

Nope, I do't do that, but I do do stuff like:

UserID
SoccerPlayerID (the ID indicating int)

And I use full names, not SPID (<-- Soccer Player ID) or ID

I also use the same naming convention everywhere. So if there's a linking table between SoccerPlayer and Country, I'd have SoccerPlayerID and CountryID in there. You have no idea how many times I've seen guys just go SPID and CID in linking tables and NOT use relationships leaving me to figure out WTF they're trying to do and what these sudden new ID's does.

The reason why I name my stored procedures proc_Module_Action is so it is easier to track specific stuff.

For example, using what you do, you would have


uspSavethis
uspSavethat
uspSaveyourmom
x100

Quickly finding the save function for Soccer Players would be a pain if you have a 100 or so stored procedures (depends on how big the system FYI)

so
usp_SoccerPlayers_EditPlayer
usp_Countries_EditCountry
etc

would help you find what you're looking for quite quickly. Because everything is listed alphabetically, so I only have to look for "Country" and find all related stored procs within one swoop
 
AcidRaZor said:
Nope, I do't do that, but I do do stuff like:

UserID
SoccerPlayerID (the ID indicating int)

And I use full names, not SPID (<-- Soccer Player ID) or ID

I also use the same naming convention everywhere. So if there's a linking table between SoccerPlayer and Country, I'd have SoccerPlayerID and CountryID in there. You have no idea how many times I've seen guys just go SPID and CID in linking tables and NOT use relationships leaving me to figure out WTF they're trying to do and what these sudden new ID's does.

The reason why I name my stored procedures proc_Module_Action is so it is easier to track specific stuff.

For example, using what you do, you would have


uspSavethis
uspSavethat
uspSaveyourmom
x100

Quickly finding the save function for Soccer Players would be a pain if you have a 100 or so stored procedures (depends on how big the system FYI)

so
usp_SoccerPlayers_EditPlayer
usp_Countries_EditCountry
etc

would help you find what you're looking for quite quickly. Because everything is listed alphabetically, so I only have to look for "Country" and find all related stored procs within one swoop
Click to expand...

at least no one on our time go by SPID's. in the previous department there were 2 devs who loved to do that and it was a hellish trip to figure out wtf.

i just stick the ID infront; IDCountry, IDCity and so on.

...so far i havent needed a 100+ sp's, but i can see the categorization uses.
 
OOH! Naming conventions... Has there even been a standard set for naming database entities? I usually name them as follows:

Tables: TableName
Stored Procedures: sp_Entity_Action (i.e. sp_Employee_Create)
Views: vw_Entity_Details (i.e.vw_Employee_ContactDetails)
Functions: fx_Entity_Function (i.e. fx_Employee_CalculateWorkingDays)
 
Last edited:
FarligOpptreden said:
OOH! Naming conventions... Has there even been a standard set for naming database entities? I usually name them as follows:

Tables: TableName
Stored Procedures: sp_Entity_Action (i.e. sp_Employee_Create)
Views: sp_Entity_Details (i.e. sp_Employee_ContactDetails)
Functions: fx_Entity_Function (i.e. fx_Employee_CalculateWorkingDays)
Click to expand...

Views: vw_Entity_Details (i.e. vw_Employee_ContactDetails) ?

though i'm starting to like the <>_Entity_Details method.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X