DISTINCT issues: First off, your examples make use of silent joins and unnormalized design, which makes everything look worse than it is. Normalization + Explicit Joins eliminate the problem or make it very obvious why it happens. It happens every time you select rows of one table, then jump across an many-to-{many|one} relationship to another table and pull out column data from there. A person versed in SQL will know to always use DISTINCT in this case, and that it is unneccesary in any other case assuming a normalized design. It is arguable that the semantics of SELECT should be that it automatically DISTINCTs the results in these cases, as the case is easily machine-detectable. But I don't think that makes all of SQL broken. It's just a repetitive idiom SQL users have to learn, like most languages have.

NULLs: Quite simply, for most designs, most columns should be set to "NOT NULL". End of problem. Enabling NULL values in a column without thinking through the implications (which are indeed precarious) will of course cause issues down the line. The same can be said of many ill-thought-out design-time decisions.