dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 3, 2019

Ten SQL Commands You Should Be Using

By Hannes DuPreez

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:

  1. ALTER TABLE
  2. AS
  3. EXCEPT
  4. GROUP BY
  5. HAVING
  6. LIKE
  7. MERGE
  8. ORDER BY
  9. SELECT DISTINCT
  10. 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:

  1. % - Represents zero, one, or multiple characters
  2. _ - 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 1LIKE 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!



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM