71–80 of 151 posts
Still dont get why sql uses all caps for keywords. It's one of the few languages that has it as a best practice. I can read C/JavaScript/go just fine without all caps. I do it anyway to conform.
Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…
I only slightly disagree with a couple of your points. 1) They aliased it to show how it should look. This is a contrived example, and you normally wouldn't alias these. 2) Aliasing tables is essential when you have join tables that end up being over 30 characters long, or you need to self join for something. Short of using CTEs (which aren't available everywhere), I don't know of a way to self join without using ali…
This is 80-90% of my queries because we do a lot of cross schema joining in my work. Also there's a lot of tables replicated from the mainframe so the table names are T___ so their names aren't useful at all. Add in that a lot of queries are reporting queries so we're pulling in 20-30+ columns from these long named tables from different schemas and not aliasing them would add a a small novel worth of text to the query.
* Use consistent and descriptive identifiers and names.
* Make judicious use of white space and indentation to make code easier to read.
* Try and use the same column/table naming convention as your programming language. If you language uses camelCase, use that. If it all possible, names should be the same everywhere to prevent confusion.
* Table names should always be in the singular: "Person" instead of "People", "Invoice" instead if "Invoices". This removes all pluralizing ambiguity and makes sense when mapping to class names, etc.
* Concatenate two table names together to create the name of a relationship table (especially in the case of many-to-many) unless a more appropriate name makes sense.
* Use TableNameId as the primary key column name.
---
* Do not use descriptive prefixes or Hungarian notation such as sp_ or tbl.
* Do not plurals -- don't even use collective names
* Avoid quoted identifiers
* Avoid underscores -- except for use a poor-mans namespacing
* Avoid simply using id as the primary identifier for the table.
* Avoid aliasing as much as possible unless joining the same table multiple times. In that case, use a descriptive name. Never use short one or two character aliases.
* Avoid putting keywords in all caps -- use an editor with syntax highlighting instead.
Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…
Aliasing tables is very useful. Aside from giving shorter names and helping you to be explicit when selecting fields, they also end up being useful for working with IntelliSense (or the equivalent in the tool you're using). Plus, you can make them memorable enough (e.g. in the first example you shared, could use 'S' for staff and 'ST' for students) for the query you're working on and shortening the query code makes i…
This isn't memorable: staff and students both start with st!
Regarding lower-case/hungarian notation etc, the membership table in my database is: tblMember --------- MemberID MemberUsername MemberDateJoined etc Is this very non-standard formatting for SQL?
I have often seen commas placed at the start of each line when columns are being listed. I'm a little surprised this guide doesn't follow that convention. It would fit nicely with the established pattern of lining everything up: SELECT first_name , last_name , email_address FROM users Can anyone speculate why it's not done this way in the guide? I think the reasoning behind it was to make later modification easier be…
Regarding lower-case/hungarian notation etc, the membership table in my database is: tblMember --------- MemberID MemberUsername MemberDateJoined etc Is this very non-standard formatting for SQL?
The primary column is always id, something like "memberID" would be some id created by an external system.
> If there is already a correlation with the same name then append a number. And then applies it for this query: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; I think that's terrible. s1 and s2 sound to me like they're instances of the same table that you had to JOIN with itself for some reason.
The databases I deal with are mostly for storing time series data taken from measurement instruments (i.e Load Cells, Thermocouples, Flow transmitters, Pressure gauges etc). We seem to use prefixes to distinguish columns i.e.
MS_ (Mass in Tonnes, MS_KG_ etc. Is used If not stored as Tonnes) VL_ (volume, in cubic meters) RT_FL_ (is Flow rate in normal cubic meters per hour) TP_ (is temperature in degrees Celsius) PR_ (is gauge pressure in KPa)
I've seem some databases which didn't use a prefix convention and in my experience it was a real nightmare trying to keep track of units etc.
There is an ISO standard for naming conventions ISO:11179 - My work's databases are (apparently) configured based on this but I'm sure the DBA's extended it in a bunch of ways.
Weird, I really dislike a lot of the suggestions. In particular, an example: SELECT first_name AS fn FROM staff AS s1 JOIN students AS s2 ON s2.mentor_id = s1.staff_num; We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things rig…
I believe AS is required for self-joins. A common expository query is to find all employees who earn more than their managers, which can be expressed via "select name from staff as employee join staff as manager on (employee.manager_id = manager.id) where employee.salary > manager.salary".
Also, SQL allows a sub-select to be listed in the FROM clause. But in such cases, the sub-select must be given a name (tables have a name, but sub-selects don't).
As an aside, the keyword "AS" is allowed by not required.