A programming language is only as clever as the person using it. Nearly all programming languages have a vast number of keywords, but it is still up to the developer to utilize these keywords correctly and efficiently. The more a developer utilizes a programming language, the more experience they gain, and the more their knowledge of the language and its keywords grows.
Transact Standard Query Language (T-SQL) is a perfect example of a language that fits this model. Most developers, regardless of their skill level would know the commands for the basic CRUD (Create, Read , Update, Delete) operations in SQL These include the Create, Update, Insert, and Delete commands. From this article you will be presented with ten other SQL commands that you really should be using, that you might not be as familiar with. These Commands are:
- ALTER TABLE
- AS
- EXCEPT
- GROUP BY
- HAVING
- LIKE
- MERGE
- ORDER BY
- SELECT DISTINCT
- UNION
ALTER TABLE
The ALTER TABLE SQL Statement is quite versatile as it has multiple functions. With the ALTER TABLE command, you can do the following:
Add columns to a table. For example:
ALTER TABLE TableName ADD ColumnName DataType
You specify the table name that you want to modify, then specify the ADD keyword and give the column a name and a data type. The next example Adds a column named StudentName to the Students table.
ALTER TABLE Students ADD StudentName VARCHAR(50)
Remove columns from a table. For example:
ALTER TABLE TableName DROP COLUMN ColumnName
You specify the table name that you want to modify, then specify the DROP COLUMN keywords and supply the column a name you want to delete. The next example removes a column named StudentName from the Students table.
ALTER TABLE Students DROP COLUMN StudentName
Change the data type of a column. For example:
ALTER TABLE TableName ALTER COLUMN ColumnName NewDataType
You specify the table name that you want to modify, then specify the ALTER COLUMN keywords and give the column a name and a new data type. The next example changes the column named StudentName’s data type to VARCHAR(20) of the Students table.
ALTER TABLE Students ALTER COLUMN StudentName VARCHAR(20)
AS
The AS SQL command renames a column or table with an alias temporarily. This is particularly useful when a table or column has a very long name or a nonsensical name. With the AS command you can do the following.
Provide a sensible name for a column, as shown below:
SELECT StudentRegistrationID AS ID, StudentName AS Name FROM Students
The StudentRegistrationID column name is quite long. This can become cumbersome and error-prone. By providing an alias for a long column name saves time, input errors, and frustration. The StudentRegistrationID gets an alias of ID, and the StudentName field gets an alias of Name.
Provide a sensible name for a table:
SELECT StudentRegistrationID AS ID, StudentName AS Name FROM StudentRegistrationTable AS Students
The table’s name (StudentRegistrationTable ) is way too long, as you will surely agree. Again, by having long names, your queries can become problematic. By providing an alias for a long table name prevents errors and frustration. Another example could be:
SELECT reg AS ID, nm AS Name FROM tbl AS Students
In the above query the columns and the table had nonsensical names. This is a problem as the developer now must figure out what each column does and each table. This is done by interrogating the data inside the columns and table. This wastes time and causes frustration.
Provide a name for combined columns:
SELECT StudentName + ‘ ‘ + StudentSurname AS FullName FROM Students
The StudentName and StudentSurname columns gets concatenated to become one column named FullName.
Provide a name for a result of a subquery:
SELECT StudentName, (SELECT CourseName FROM Courses WHERE CourseID = Students.CourseID) AS Course FROM Students
This one is a bit more complicated. A sub query (a query within a query) is included. This sub query obtains the CourseName from the Courses table where the Course’s CourseID field value matches up with the Student’s CourseID field value. The result gets an alias of Course.
EXCEPT
The SQL EXCEPT operator combines two SELECT statements and returns rows from the left (first) SELECT statement that are not returned by the right (second) SELECT statement. In other words, EXCEPT returns only the rows which are not available in the right (second) SELECT statement. Here’s a basic example:
SELECT StudentName FROM Students EXCEPT SELECT StudentName FROM AbsentStudents
The above example will only return the students that aren’t available in the AbsentStudents table but do exist in the Students table.
GROUP BY
The GROUP BY SQL statement is mostly used in conjunction with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group a result-set by one or more columns. The syntax for a basic GROUP BY statement looks like:
SELECT ColumnName FROM TableName WHERE Condition GROUP BY ColumnName
Here is a better example:
SELECT StudentName, AVG(StudentMarks) FROM Results WHERE StudentID = 123 GROUP BY StudentName
In the above example, the StudentName field gets selected along with the Average of StudentMarks by the SQL query for the student which has a StudentID value of 123. AVG is an aggregate function which performs a calculation on a certain field. GROUP BY groups the results by the StudentName field.
HAVING
The SQL HAVING Clause allows you to specify filtering conditions for group results. Because the WHERE statement in SQL cannot be used with aggregate functions, the HAVING statement has been added to SQL.
SELECT ColumnName FROM TableName WHERE condition GROUP BY ColumnName HAVING condition
Consider an example:
SELECT StudentName FROM Results GROUP BY StudentName HAVING AVG(StudentMarks) > 75
The StudentNames are selected from the Results table where the StudentMarks field has an average greater than 75.
LIKE
The SQL LIKE operator is used with a WHERE statement to search for a specified pattern in a column. It makes use of two wildcard symbols to find patterns:
- % – Represents zero, one, or multiple characters
- _ – Represents a single character
These wildcard symbols can also be used in conjunction with each other. The following table provides simple examples of the LIKE Operator
| LIKE Operator | Description |
| WHERE StudentName LIKE ‘Han%’ | Finds any values that start with ” Han” |
| WHERE StudentName LIKE ‘%nes’ | Finds any values that end with ” nes” |
| WHERE StudentName LIKE ‘%ann%’ | Finds any values that have ” ann” in any position |
| WHERE StudentName LIKE ‘_a%’ | Finds any values that have “a” in the second position |
| WHERE StudentName LIKE ‘H_%_%_%_%_%’ | Finds any values that start with “H” and are at least 6 characters in length |
| WHERE StudentName LIKE ‘H%s’ | Finds any values that start with “H” and ends with “s” |
Table 1 — LIKE Operator
SELECT * FROM Students WHERE StudentName LIKE 'H%'
This example obtains all the students with names starting with “H”.
MERGE
The MERGE SQL Statement runs insert, update, or delete operations on a target table by using the results of a join of a source table. This synchronizes the two tables by inserting, updating, or deleting rows in one table based on the differences that exist in the other table. The syntax is a bit more complicated, let’s have a look with an example:
MERGE INTO ExistingStudents WITH (HOLDLOCK) AS target
USING PreviousStudents AS source
ON target.StudentID = source.StudentID
WHEN MATCHED THEN
UPDATE SET target.EnrollDate = source.EnrollDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (StudentID, EnrollDate, StudentName)
VALUES (source.StudentID, source.EnrollDate, source.StudentName)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
The above query interrogates the existing info from the PreviousStudents table. If there is a match in both PreviousStudents and ExistingStudents, it sets the EnrollDate to the PreviousStudent’s EnrollDate field. If there is no match in the ExistingStudents table, it gets inserted from the PreviousStudents table. If there are no matches at all, the target’s unmatching rows get deleted
ORDER BY
The ORDER BY SQL keyword sorts the result-set in either ascending or descending order by a given column or columns.
SELECT Column1, Column2, FROM Table ORDER BY Column1, Column2, ASC|DESC
ORDER BY sorts Ascending by default, so you normally do not have to supply ASC. To sort results descending you have to specify the DESC keyword.
SELECT DISTINCT
The SELECT DISTINCT SQL statement returns only distinct (or different) values. Sometimes a column contains duplicate values, but you simply want to list the distinct values. Here is the Syntax
SELECT DISTINCT StudentName, StudentSurname FROM Students
If there are many “Hannes du Preez’s” then the result will only return one, for this example specifically.
UNION
The UNION SQL statement combines the result-set of multiple SELECT statements; each of which must have the same number of columns, these columns must also have similar data types and these columns must also be in the exact same order. The syntax is as follows:
SELECT StudentID, StudentName FROM PreviousStudents UNION SELECT StudentID, StudentName FROM Students
This query will produce one output result-set as it makes use of the UNION Operator to combine data from two tables.
Conclusion
SQL has many statements, operators and keywords; these are just a few of the most commonly used and important ones. It is worth taking the time to explore the little detail of the programming languages you know, as you might not know some hidden or more subtle statements that could change your way of coding forever!
