Sometimes, a database has duplicate values in the fields of a table. In such situation, you can use the DISTINCT clause to select values without duplication. For example, several customers may belong to the same city, so the table will have the same value in the city field for those customers. In such a scenario, to retrieve the list of all the cities without duplication, you can use the DISTINCT clause, as shown in the following SQL statement.
You can also use logical operators on the clauses used in SQL statement. For example, you can use logical operators when you are specifying two criteria in a SQL statement. First, the City field cannot be Mirmee or Phant, and second, there must be some value in the Fax field. To retrieve the records on the basis of these two criteria, you can use the following SQL command:
SELECT * FROM Customers WHERE City NOT IN ('Mirmee', Phant') AND Fax IS NOT NULL
In the preceding queerly, we are using the NULL keyword to test whether the City field in the Customers table contains a value or not.
The different logical operators that you can use with the WHERE clause are as follows:
⦁ AND: Specifies that both condition in the WHERE clause must be True
⦁ OR: Specifies that at least one of the condition in the WHERE clause should be True
⦁ NOT: Specifies that the condition in the WHERE clause should be False
You can also arrange the records in an order in the output by using the ORDER BY clause. For example, to retrieve the records in an ascending order of CustomerId from the Customers table, you can use the ORDER BY clause, as shown in the following SQL statement:
SELECT * FROM Customers ORDER BY CustomerId
You can also retrieve the records in descending order of CustomerId from the Customers table by using the DESC keyword, as shown in the following SQL statement:
SELECT * FROM Customers ORDER BY CustomerId Desc
If you are not providing the DESC keyword with the ORDER BY clause, by default, it returns results in an ascending order.
When you access records from a table, you can refer to the fields with some more descriptive names by using the AS clause. For example, referring to the Name field of a table as Name might be more descriptive for the user to understand than referring to the same as au_Iname. You can alias a field name with the AS clause. For example, you can use the following SQL statement to retrieve values of the ContactName field from the Customers table with the field name ContactName changed to Name:
SELECT ContactName AS Name FROM Customers
SQL provides various built-in functions that help in performance arithmetical operation. These functions make it easier for you to query the data or perform operations on it. Some built-in functions provides by SQL are as follows:
⦁ COUNT( ): Returns the number of total records
⦁ SUM( ): Returns the sum of the values of a specified field
⦁ MIN( ): Returns the minimum of the values of a specified field
⦁ MAX(): Returns the maximum of the values of a specified field
⦁ AVG( ): Returns the average of the values of a specified field
For example, you can use the COUNT function in a SQL statement as follows:
SELECT COUNT (*) FROM Customers
The preceding query returns the count of the total number of records in the Customers table.
The GROUP BY clause is used to group some records according to the field of a table. You can group records in SQL with the GROUP BY clause, as shown in the following SQL query statement:
SELECT OrderId, SUM(Quantity) FROM OrderDetails GROUP BY OrderId
In the preceding query, we have grouped the records in the OrderDetails table by the field Orderd. This means that means we have grouped all the records that have the same OrderId.
The HAVING clause is used with the GROUP BY clause. The functionality of this clause is the same as that of the WHERE clause, but is used only with the GROUP BY clause. Using the HAVING clause, you can specify additional criteria for the the records you want to retrieve. For example, to retrieve those records from the OrderDetails table for which the value in the Quantity field is less than 100, you can use the HAVING clause, as shown in the following SQL statement:
SELECT * OrderId, SUM(Quantity) FROM OrderDetails GROUP BY OrderId HAVING SUM(Quantity) < 100
The DELETE statement is used to delete records from a table. For example, to delete all the records from the Customers table for which the value in the City field is not New York, you can use the DELETE clause, as shown in the following SQL statement:
DELETE FROM Customers WHERE City NOT IN ('New York')
You can the UPDATE statement to update a database. In other words, you can update the existing records in one or more locums of a table as and when required by using the UPDATE statement. For example, to change the value in the City field to California in all the records where its value is New York, you can use the UPDATE statement, as shown in the following SQL statement:
UPDATE Customers SET CIty = 'California' WHERE City = 'New York'
You can also work with relational databases in SQL Relational databases are used to relate data in multiple tables. Suppose you want to retrieve customers names from the Customers table and the IDs of the items ordered by the customers from the Orders table. For this purpose, you have to relate or join these two tables. You can relate these tables by using the CustomerId field, which is present in both the tables. This field acts as the primary key in the Customers table and as the foreign key in the Orders table. You can use the following SQL statement to retrieve the records according to the preceding mentioned criteria:
SELECT Customers.ContactName, Orders. OrderId FROM Customers ,Orders WHERE Customers. CustomerId=Orders. CustomerId
Now, before knowing how to use the basis SQL statement in ADO.NET to retrieve and manipulated data, let's get an overview of ADO.NET and the basis operations performed in ADO.NET.