Archive for the ‘SQL Server’ Category
GROUP BY Quirk
It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:
“How would you get all students’ names and for each student the number of courses that the
student has registered for?”
They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.
For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; |
While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.
In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:
SELECT a.studentname
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression |
The correct way to write the GROUP BY is:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
Then, it would return:
Student Name Course IDs ------------------------------ ---------- Montgomery Scott 1 Leonard McCoy 2 James Tiberus Kirk 3 |
For reference, here’s a complete test case for MySQL:
/* Drop table conditionally. */ DROP TABLE IF EXISTS students; /* Create table. */ CREATE TABLE students ( studentID int unsigned primary key auto_increment , studentName varchar(30)); /* Drop table conditionally. */ DROP TABLE IF EXISTS courses; /* Create table. */ CREATE TABLE courses ( courseid int unsigned primary key auto_increment , coursename varchar(40)); /* Drop table conditionally. */ DROP TABLE IF EXISTS registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid int unsigned , studentid int unsigned ); /* Insert into students. */ INSERT INTO students ( studentName ) VALUES ('James Tiberus Kirk') ,('Leonard McCoy') ,('Montgomery Scott'); /* Insert into courses. */ INSERT INTO courses ( coursename ) VALUES ('English Literature') ,('Physics') ,('English Composition') ,('Botany') ,('Mechanical Engineering'); /* Insert into registeredcourses. */ INSERT INTO registeredcourses ( studentid , courseid ) VALUES (1,1) ,(1,3) ,(1,4) ,(2,2) ,(2,5) ,(3,4); /* Check global sql_mode to ensure only_full_group_by is set. */ SELECT @@GLOBAL.SQL_MODE; /* Query with a column not found in the SELECT-list. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* Query consistent with ANSI SQL:1992 */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
and, another complete test case for Oracle:
/* Drop tabhe unconditionallly. */ DROP TABLE students; /* Create table. */ CREATE TABLE students ( studentID NUMBER PRIMARY KEY , studentName VARCHAR(30)); /* Drop table unconditionally. */ DROP TABLE courses; /* Create table. */ CREATE TABLE courses ( courseid NUMBER PRIMARY KEY , coursename VARCHAR(40)); /* Drop table unconditionally. */ DROP TABLE registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid NUMBER , studentid NUMBER ); /* Insert values in student. */ INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk'); INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy'); INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott'); /* Insert values in courses. */ INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature'); INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics'); INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition'); INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany'); INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering'); /* Insert values into registeredcourses. */ INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); /* Non-ANSI SQL GROUP BY statement. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* ANSI SQL GROUP BY statement. */ SELECT a.studentname AS "Student Name" , COUNT(b.courseid) AS "Course IDs" FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
I hope this helps those learning the correct way to write SQL.
Toad Freeware Page
While I posted how to install Toad for MySQL Freeware five years ago, I’m always surprised how few people know about it there and consistently updated and improved. You can download Toad for MySQL Freeware or Toad Freeware for Oracle, SQL Server, Sybase, or IBM DB2 at this web site.
You can also download Toad Data Modeler Freeware Edition. Just two notes, while Toad for Oracle Freeware is an MSI file, Toad for MySQL Freeware is a zip file and limited to only a Windows install.
SQL Like Comparisons
SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.
The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' = 'Tre_t' OR 'Treet' = 'Tre_t'; |
Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=) comparison operator with the LIKE comparison operator. The following query does that:
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' LIKE 'Tre_t' OR 'Treet' LIKE 'Tre_t' |
The same behavior exists for the multiple-character wildcard (%). I hope this helps those looking for this answer.
A T-SQL Table Function
I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they’re available in Microsoft T-SQL User-Defined Functions (UDFs), my students wanted a small example. One of them said they’d tried to do it but couldn’t get it to work because they found the Microsoft web pages difficult to read and use. Specifically, they didn’t like the sparseness of this one on how to create a function.
Here’s a quick definition of a UDF table function that runs in the studentdb schema (created in this post for migrating SQL Server into a MySQL database). The following getConquistador function takes a single string, which acts to filter the result set from a query positioned as the return value of the function. You should note that this is an implementation of Microsoft’s Common Language Infrastructure (CLI).
CREATE FUNCTION studentdb.getConquistador (@nationality AS VARCHAR(30)) RETURNS TABLE RETURN SELECT * FROM studentdb.conquistador WHERE nationality = @nationality; |
Unlike Oracle SQL, where you need to use the TABLE function to read the content of a table result from a function, you don’t need anything other than the function call in the FROM clause of a T-SQL query. Here’s an example of calling the table function:
SELECT * FROM studentdb.getConquistador('German'); |
The complete result from the query would produce these results when run from the sqlcmd command-line interface:
conquistador_id conquistador actual_name nationality
--------------- --------------------- -------------------- ------------
11 Nicolas de Federman Nikolaus Federmann German
13 Jorge de la Espira George von Speyer German
(2 rows affected) |
However, you also have the ability to query only rows of interest without any specialized syntax, like this:
1> USE studentdb; 2> SELECT conquistador AS "Conquistador" 3> , actual_name AS "Name" 4> FROM studentdb.getConquistador('German'); 5> GO |
This produces the following two-column result set:
Conquistador Name --------------------- -------------------- Nicolas de Federman Nikolaus Federmann Jorge de la Espira George von Speyer (2 rows affected) |
Hope this helps those interested in T-SQL UDFs.
Subscribe via RSS