What are some useful SQL statements / usage patterns that should be known by all developers who may

Posted by Jian Lin on Stack Overflow See other posts from Stack Overflow or by Jian Lin
Published on 2010-04-23T19:36:33Z Indexed on 2010/04/23 20:03 UTC
Read the original article Hit count: 245

Filed under:

sql

|

backend

|

database

|

database-queries

What are some useful SQL statements that should be known by all developers who may touch the Back end side of the project?

(Update: just like in algorithm, we know there are sorting problems, shuffling problems, and we know some solutions to them. This question is aiming at the same thing).

For example, ones I can think of are:

Get a list of Employees and their boss. Or one with the employee's salary greater than the boss. (Self-join)
Get a list of the most popular Classes registered by students, from the greatest number to the smallest. (Count, group by, order by)
Get a list of Classes that are not registered by any students. (Outer join and check whether the match is NULL, or by Get from Classes table, all ClassIDs which are NOT IN (a subquery to get all ClassIDs from the Registrations table))

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

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about backend

Appears to be "randomly" switching between the acl matched backend and the default backend

as seen on Server Fault - Search for 'Server Fault'
I have HAProxy acting as a proxy in front of: An NGinx instance An in-house load balancer in front of multiple dynamic services exposed with socket.io (websockets) My problem is that from time to time my connections are proxied correctly to my socket.io cluster, and then randomly it fallsback… >>> More
nginx: SSI working on Apache backend, but not on gunicorn backend

as seen on Server Fault - Search for 'Server Fault'
I have nginx in front of an Apache server and a gunicorn server for different parts of my website. I am using the SSI module in nginx to display a snippet in every page. The websites include a snippet in this form: For static pages served by nginx everything is working fine, the same goes for the… >>> More
Varnish 3.0.2 to Apache2 sometimes return error 503

as seen on Server Fault - Search for 'Server Fault'
Hey guys I hope you can help me out here. I have an Ngingx parsing http and https to a varnish cache(3.0.2). From the varnish it is sent to apache2. Now I have for some time been tracking some strange 503 errors. But I cant seem to find the silver bullet. Currently I am logging the 503 errors through… >>> More
Mysql hosting for application backend

as seen on Super User - Search for 'Super User'
I've been asked to help set up a way for a volunteer animal rescue organization to use an application to keep track of animals they've rescued. This application already exists, and can use it's own local database or connect to a MySQL database server. Since there are several volunteers spread out… >>> More
joomla: editing the mvc package for joomla component development

as seen on Stack Overflow - Search for 'Stack Overflow'
hi! i m new to joomla component development i hv just downloaded bunch of files from some jooomla mvc generater website.which llok like smthng like this hello.xml - frontend/index.html - frontend/hello.php - frontend/controller.php - frontend/models/index.html - frontend/models/hello… >>> More