SQL overview
It is very important to have good knowledge and experience with SQL. SQL is short for Structured Query Language, and it is commonly used to handle and manage database records. Microsoft SQL Server has its own type of SQL called T-SQL or Transact-SQL. In this chapter we are going to use SQL Management Studio, which is installed along with Microsoft SQL Server.
The main commands of this language are Select, Update, Delete, and Insert.
Select
This command is used to query records from the database.
The syntax used is as follows:
Select field1, field2 from table Select field1, field2 from view
We use the filtered views as follows:
Select name, address1_stateorprovince from FilteredAccount
Tip
Downloading the example code
You can download the example code files for all Packt Publishing books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
This will produce the results shown in the following screenshot:
Tip
In this book we are using the CRM sample data, which you can install by going to Settings | Data Management | Sample Data | Install Sample Data. Having the sample data installed will produce very similar results to what are described in the samples.
If you want to get all the fields, you can just use the * char as follows:
Select * from table
For example, see the following query:
Select * from FilteredAccount
This will produce the results shown in the following screenshot:
If you want to get a number of records only, you need to use the TOP keyword. For example, if we only want to get the first two records, we use the following command:
Select TOP 2 name, address1_stateorprovince from FilteredAccount
This will produce the results shown in the following screenshot:
There are also some text transformation functions you can use in your select command; for example, if you want to return all the values of a field in uppercase or in lowercase as shown in the following command:
Select UPPER(name), LOWER(address1_stateorprovince) from FilteredAccount
This will produce the results shown in the following screenshot:
As you can see in the previous screenshot, when using functions, the column names are missing; so we will need to add a column alias as follows:
Select UPPER(name) as name , LOWER(address1_stateorprovince) as address1_stateorprovince from FilteredAccount
Notice that you can omit the as keyword; the following code will produce the same result:
Select UPPER(name) name , LOWER(address1_stateorprovince) address1_stateorprovince from FilteredAccount
Note
Even though you can apply text transformations in your SQL query, it might not be recommended to do that here because, as we will see later in this book, you will also be able to apply text transformations on your report.
Update
The Update command is used to edit the data stored in the database. The syntax is as follows:
Update table set field = value, field = value
Notice that if you don't specify a WHERE condition, all the records will be updated; for example, the following command will update the country field with the U.S. value to all records in the Account table:
Update FilteredAccount set address1_country = 'U.S.'
Note
It is not recommended to perform the Update, Delete, and Insert operations on the CRM database from SQL directly as it is an unsupported method. When you need to perform these operations, always use the web services so the plugins and workflows that might be associated with those operations can be fired.
Delete
The Delete command removes records from the database. The basic command is as follows:
Delete from table
Note that when using this command, if you don't specify a WHERE condition, it will delete all the records; be careful and use a WHERE clause when using this command, as follows:
Delete from table where field = value
Insert
The Insert command adds records to a table. The syntax is as follows:
INSERT INTO tablename (field1, field2) VALUES (value1, value2)
Have in mind that inserting records to the CRM tables is not an easy task because it requires inserting records in at least two different tables. We should never insert records into the CRM tables manually with SQL code; however, we will find the Insert command useful when working with temporary tables, as you will see later in this chapter in the SQL advanced section.
WHERE
Most of the time we want to filter the query results in such a way that they return a set of records but not all the records that exist in the table; this is when we use the WHERE clause.
Select * from FilteredAccount WHERE name = 'damian'
You can also use the AND operator to filter by another column as follows:
Select * from FilteredAccount WHERE name = 'damian' AND telephone1 = '9999'
With the AND operator, both name and phone number need to match the returned records. If you want to filter by a field or another field, we use the OR operator as follows:
Select * from FilteredAccount WHERE name = 'damian' OR telephone1 = '9999'
If you want to filter by a part of a string, similar to the contains operator in the Advanced Find, you use the like operator with the % character at the beginning and the end of the string; for example:
Select * from FilteredAccount WHERE name like '%damian%'
This will return records with account names such as the following:
Damian Mr Damian Sinay Damian Sinay Mr Damian
If you want to filter by a string that begins with another string, use the like operator with the % character at the beginning of the string; for example:
Select * from FilteredAccount WHERE name like '%damian'
This will return records with account names such as the following:
damian Mr Damian
If you want to filter by a string that ends with another string, use the like operator with the % character at the end of the string; for example:
Select * from FilteredAccount WHERE name like 'damian%'
This will return records with account names such as the following:
damian Damian Sinay
ORDER BY
To sort the results, we use the ORDER BY clause; for example:
Select name from FilteredAccount ORDER BY name asc
This command will generate the results shown in the following screenshot:
By default, the order will be ascending, so we can omit the word asc at the end, as shown in the following code, and yet it will generate the same result:
Select * from FilteredAccount ORDER BY name
To get the results in the descending order, we change asc by desc as shown in the following command:
Select * from FilteredAccount ORDER BY name desc
This sentence will generate the results shown in the following screenshot:
group by
To group records, we use the group by statement. There is an important consideration when using this statement; the fields we select will also need to be included in the group by statement, and if not included we will need to use one of the following aggregate functions:
AVGMINCHECKSUM_AGGSUMCOUNTSTDEVCOUNT_BIGSTDEVPGROUPINGVARGROUPING_IDVARPMAX
An example of this is as follows:
Select address1_stateorprovince from FilteredAccount group by address1_stateorprovince
This will produce the results shown in the following screenshot:
Using the aggregate functions, we can show how many records of each state are there, as follows:
Select address1_stateorprovince, count(*) from FilteredAccount group by address1_stateorprovince
This will produce the results shown in the following screenshot:
As we can see, the column that shows the number of records per state doesn't have a name, so it will be good to name this column using an alias, as follows:
Select address1_stateorprovince, count(*) as state_count from FilteredAccount group by address1_stateorprovince
The keyword as can be omitted; the following code will have the same result:
Select address1_stateorprovince, count(*) state_count from FilteredAccount group by address1_stateorprovince
We can now see the result with the state_count name on the second column as shown in the following screenshot:
join
Sometimes we want to show fields from different tables. This is when we need to use the join clause. Notice that there need not be a relationship between the tables to join them in a SQL command, but the performance will be better if they are related. For example, suppose we want to show the name of the contacts as well as the name of the parent account; in this case, we can use a query as follows:
Select FilteredContact.fullname, FilteredAccount.name from FilteredContact inner join FilteredAccount on FilteredContact.accountid = FilteredAccount.accountid
This query will produce the results shown in the following screenshot:
The same query can be written in a shorter manner by using table aliases as follows:
select C.fullname, A.name from FilteredContact C inner join FilteredAccount A on C.accountid = A.accountid
It is best practice to use table aliases when using the join clause.