What are some helpful SQL statements / usage patterns that should be known to all developers who might touch the back of the project?

Question

What useful SQL statements should be known to all developers who might touch the back of the project?

( Update: as in the algorithm, we know that there are problems with sorting, shuffling problems, and we know some solutions for them. This question is aimed at the same thing).

For example, I can think of:

Get a list of classes that are not registered by any student. (Outer join in and check if the match is NULL or the Get from Classes table, all class identifiers that are not IN (a subquery to get all ClassIDs from the Registration Table))

Are there any SQL statements that should be under the control of all developers that might touch the back end data?

Answer 1

Hmm, summarizing what types of queries you should write.

• First select a direct choice without using affix (and not select *)
• You need to know how to combine two or more tables and get records that are in all tables.
• You need to know how to combine two or more tables and get records that are in all tables, but only one record from the table is returned from a many one-to-many relationship
• You should be able to get records in one table, but not in a related table
• You should be able to summarize the data for the report.
• You should be able to insert one record into the table.
• You should be able to update one record in a table.
• You should be able to delete one entry in the table.
• You should be able to insert a group of records into a table without a cursor
• You should be able to update a group of records in a table without a cursor
• You should be able to delete a group of records in a table without a cursor.
• You should be able to perform several actions in one transaction and handle error capture
• You must create a union of records and know when to use them. UNION vice UNION ALL
• You should be able to change data for one field based on some criteria (using CASE)
• You should be able to write an IF Statement.

Well, that's what comes to mind immediately. Of course, this is for a beginner SQL developer. This includes nothing that I would consider as advanced.

Answer 2

Developers should learn the principles of databases and SQL. No specific SQL statements as required SQL statements will change depending on what is stored in the database and the database structure.

Update: Your updated question is interesting. I think the SQL statements as a whole are pretty simple. Therefore, they should not be remembered. If they are complex, then they are tied to a specific problem and again do not deserve to be remembered.