Tag Info

Hot answers tagged

66

Whether or not database query writing should be a core requirement depends on the job, but relational databases are ubiquitous in current technology. So, if I met a programmer that didn't know how to write database queries, I would expect one of two things: They are generally inexperienced. They are highly specialized in another field (e.g. embedded ...


35

C. J. Date goes into detail about this in Chapter 7 and Appendix B of SQL and Relational Theory. You're right, there's nothing in relational theory that prohibits an attribute's data type from being a relation itself, as long as it's the same relation type on every row. Your example would qualify. But Date says structures like this are "usually--but not ...


34

I'm basing most of this on just trying to get the "right" answer, so you may discover there are some performance issues. No point in speeding up a an incorrect query. Understand the table relationships - most will be one to many. Know the "many" table. Identify the fields required for your joins. Think about LEFT join scenarios - Select all the employees ...


24

Indentation would be the first thing to do, if you're not doing it already. Not only it's useful with even simple queries, but it is crucial when it comes to joins and queries a bit more complex than a select top 1 [ColumnName] from [TableName]. Once indented properly, nothing forbids to add comments inside the query itself, when appropriate. Don't overuse ...


23

Any software engineer should have a basic understanding of databases and how to store and retrieve data using SQL, at least to the level where they have an understanding of what this can be used for (and with that I would include an understanding of keys, views, stored procedures and triggers). Not every software engineer needs to be an expert, and the ...


17

There are some areas of expertise (embedded systems for example) where database knowledge is not needed. But most business applications use a database of some kind and if you don't thoroughly understand how to use it properly, you can create a performance mess that is extremely difficult to fix. Refactoring databases can be a complex and difficult process ...


15

The politically correct answer: It depends. SQL knowledge has no value whatsoever if the developer never works with relational databases (and in this day and age of NoSQL applications, that's actually quite likely). Second, when there's a DBA or full-time query writer (whatever the title is), then understanding is also of lesser importance. It's only ...


11

Some of the earliest database systems were based upon the Hierarchical Database model. This represented data in a tree like structure with parent and children, much like you are suggesting here. HDMS were largely superseded by databases built upon the relational model. The major reasons for this were that RDBMS could model "many to many" relationships which ...


9

I suppose that your question really is centered at the fact that while databases are based on a solid logic and set theroretic basis and they do a very good job storing, manipulating and retrieving data in (2-dimensional) sets while ensuring referential integrity, concurrency and many other things, they don't provide an (additional) feature of sending (and ...


7

Instead of temporary views, use the WITH clause. This makes it much easier to break down large queries into more readable smaller parts.


7

Check out this wikipedia introduction to Computer Programming: Computer programming (often shortened to programming or coding) is the process of designing, writing, testing, debugging / troubleshooting, and maintaining the source code of computer programs. This source code is written in a programming language. The purpose of programming is ...


7

First, a bit about the lingo. Insert, update are not queries. A query in RDBMS is strictly a SELECT (Or a a sub-statement having WHERE clause). The set of verbs: SELECT, INSERT, UPDATE, DELETE are action statements in Data Manipulation Language (DML). Most answers so far revlove around how to achieve your objective, but your question is: Should I have a ...


6

A good software engineer with a background in enterprise and business applications (EDIT: specifically in projects that utilize an RDBMS) should have expert knowledge of writing relational database queries in the standard format. Further they should be able to understand complex schema and propose schema designs of at least moderate complexity. Extremely ...


6

Others have already answered your question about database queries. Database design is a particular type of design. It's not that hard to learn, but the typical database designer doesn't get that many opportunities to design a database. The place I'm working now has the same database design that it had in 1970. We've moved the database from IDMS to DB2, ...


6

I am not able to answer with a proper, argumented answer, so feel free to downvote me into oblivion if I am wrong (but please correct me so we can learn something new). I think that the reason is that relational databases are centered on the relational model, which in turn is based on something I know nothing about called "first order logic". What you may ...


6

A bit of a shot in the dark here, but if you are writing lots of temporary views perhaps you haven't realized yet that most places you could put a table in an SQL statement, that table can be replaced by a query. So, rather than joining table A to temporary view B, you can join table A to the query that you have been using as temporary view B. For ...


5

Actually Oracle supports what you want but you need to wrap the sub-query with "cursor" keyword. Results are fetched via open cursor. In Java, for example comments would show up as result sets. More on this see Oracle's documentation on "CURSOR Expression" SELECT id, content, cursor(SELECT * FROM comments WHERE post_id = 7) AS comments FROM posts WHERE id = ...


5

I think you overestimate the importance of databases in software. Many classes of application are not database-centric. Do we need a DBMS in word processors and image editors now? What about speech recognition and computer vision systems do these contain a lot of database queries? And what of linear video editors and video game physic engines?


5

I'm quite frankly amazed that so many of us think that every development revolves around a database, and an SQL database at that. Others have mentioned the many ways in which we can avoid the nitty-gritty of SQL in our jobs, even when we are working (indirectly) with databases, but what about all the developers that write the firmware for the 101 electrical ...


4

I don't think query writing should be a core requirement for programmers. Having said that, I believe that a programmer who can write queries and design databases would be more valuable to an organization. However, if this programmer can only write "select * from tblxxxx" type queries I would not consider this programmer to be an expert. Likewise, if the ...


4

I would expect a generalist developer to have at least an awareness of database technologies (relational or otherwise) and be able to discuss the pros and cons of using them. Otherwise, I'd be afraid all they know how to do is stuff data into flat files.


4

Selecting means choosing some records from a table and leaving others out. Projecting means choosing some columns from each record and leaving others out. Therefore, your query performs selection (records with name='zeus' are chosen, but others are rejected) but not projection (those records that are chosen are returned with all of their columns). ...


4

.NET already does this; it's called Linq. Linq is basically SQL for object collections. In C#, it looks like this: var q = customers. where(c => c.City == "Montreal"). select(c => c.CompanyName); and in Smalltalk, it looks like this: q := customers where: [ :c | c city = 'Montreal' ] select: [ :c | c companyName ]. or this: ...


3

Looking around in our department, it depends: Our desktop/web/server developers. At least required to write basic to advanced crud statements depending on their specialty. For optimization we have a few specialized DB admins. Our embedded programmers. Quite a few never got past "select * from mytable". However, that also changed in these last few months ...


3

I know at least SQLServer does support nested queries when you use FOR XML. SELECT id, content, (SELECT * FROM comments WHERE post_id = posts.id FOR XML PATH('comments'), TYPE) AS comments FROM posts WHERE id = 7 FOR XML PATH('posts') The problem here is not the lack of support from the RDBMS, but lack of support of nested tables in tables. Beside, what ...


3

Best practice is not to write such a lib at all. Use an OR mapper like MS entity framework or any of the available lightweight micro-ORMs (for example, see here http://stackoverflow.com/questions/5829891/which-micro-orm-to-use)


3

Python lambda expressions are real, formal untyped λ-calculus lambda expressions. They fit the formal definition; they can only represent one python expression, based on variables (free or otherwise) and references to other functions (abstract symbols). Python uses parenthesis in expressions too. You use them wherever a lambda is more suitable and ...


3

tl;dr I think you have it exactly backwards. A knowledgeable developer constructs his queries in the order you mention there because that is the order of dependencies - You don't know what relations you need to follow until you know what entities you need, you don't know what restrictions are necessary without first identifying your relations, and you ...


3

In this particular scenario, I would consider using a join on the Passenger table and a where clause to filter the results. Something like the below: SELECT pid FROM Reservation INNER JOIN Passenger ON Passenger.pid = Reservation.pid WHERE class = 'AC' and age > 65 For me this is a clearer version and it is easier to understand what the query is ...


2

With torpedoquery your query could look like this public List<User> findUsers() { User from = from(User.class); City city = innerJoin(from.getCity()); with(city.getCode()).in("one", "two").or(city.getCode()).notIn("three", "four"); District district = innerJoin(city.getDistrict()); with(district.getCode()).notIn("exclude1", ...



Only top voted, non community-wiki answers of a minimum length are eligible