Introduction
If we are working with MS SQL server in C#, we use SQL commands to access and modify the data in databases. Suppose that we are working with a database, named Northwind, which comes with Ms-SQLserver. This database contains a table named Customers that holds customer IDs, addresses, etc. and a table named Orders that holds customer orders. We will now see how basic SQL commands can be applied to these two tables.
1. The SELECT Statement
SELECT statement is used to retrieve values for some or all of the fields from a table.
For example, we can apply the following SQL command to retrieve all of the records from customers table, using the wildcard charecter * :
- SELECT * FROM Customers
This returns all the records from Customers table. We can also use the SELECT statement to select values for specific fields from a table, for example, we can select CustomerID, Address, and city fields of all the records from the Customers table with the following SQl command:
- SELECT CustomerID, Address, City FROM Customers
This returns CustomerID, Address, and City fields of all the records from the Customers table.
2. The WHERE Clause
In SQL, we can use the where clause to specify a criteria that we want records to meet.
For example, to select all the records in the Customers table, where the City field value is 'Los Angeles' we can use the following SQL command:
- SELECT * FROM Customers WHERE City = 'Los Angeles'
In addition to the = operator, we can use the following operators in WHERE clause:
- < (less than)
- <= (less than or equal to)
- > (greater than)
- >= (greater than or equal to)
- BETWEEN
- IN
- LIKE
3.The BETWEEN Clause
We can use BETWEEN clause to access values that lie in a specified range. For example, if we want to select all the records from the Customers table where the range for the CustomerID field is specified as field for which the first letter lies in the range from R to T (which includes letters R,S and T), we will use the following SQL command:
- SELECT * FROM Customers WHERE CustomerID BETWEEN 'R*' and 'T*'
4. The IN Clause
In SQL we can use the IN clause to match the value for a given field of the table, from a set of specified values.
For example, to select those records for which City field has a value 'Los Angeles', 'New York', or 'California', we will use the follwing SQL command:
- SELECT * FROM Customers WHERE City IN ('Los Angeles', 'New York', 'California')
In SQL, the LIKE clause is used to match value of a field(having the datatype as string)in a table, with some specified pattern. We use the records For example, to select all records from the Customers table where the City field matches the wild card string 'Lo%' we will use the follwong SQL command:
- SELECT * FROM Customers WHERE City LIKE 'Lo%'
- LO% : This pattern is used for matchiing the values of the fields starting with LO, such as 'London' or 'Los Angeles'
- %LO : This pattern is used for matchiing the values of the fiields ending with LO such as 'Sao paulo'
- %LO% : This pattern is used for matching the values of the fields containing LO anywhere in the field, such as 'Barcelona'
Sometimes a database has duplicate values in the fields of the records of a table. We use DISTINCT clause for selecting the values in the felds without duplication. For example, several customers are may belong to the same city, so they would have the same value in City field. To retrieve the list of all cities without duplication we will use the following SQL command:
- SELECT DISTINCT city FROM customers
We can also use logical operations on the clauses in our SQL statements. For example, we are specifying two criteria: first, the City field cannot be either 'Los Angeles' or 'New york', and second, there must be some value in the FAX field. To retrieve the records on the basis of these two criteria, we will use the following SQL command (to test that a field contains some value or not, we use NULL keyword):
- SELECT * FROM customers WHERE city NOT IN ('Los Angeles', 'New york') AND fax IS NOT NULL
We can use all of the three logical operators AND, OR, and NOT in our where clause. When using AND, both conditions in the WHERE clause must be True; when using OR, at least one of the conditions should be True; and when using NOT, the condition should be False.
We can arrange the records in an order in the dataset, using ORDER BY Clause. For example, we can use the following SQL command which is having the ORDER BY Clause, to retrieve the records arranged in the order of customerID, from the Customers table:
- SELECT * FROM customers ORDER BY CustomerID
- SELECT * FROM customers ORDER BY CustomerID Desc
When we accessing records from a table, we can refer the fields with some more descriptive names using AS clause. For example, referring a field of a table as Name might be more descriptive to the user than referring that field as au_lname. We can alias a fields name with the as clause, for example, we can use the following SQL command for retrieving ContactName field values from the customers table, with the field name ContactName changing to just Name:
- SELECT ContactNmae As Name FROM customers
SQL has some built-in functions that are very useful in performing arithmetical operations. Following list explains what these built-in functions are and where they are used:
- COUNT(): It returns the number of total records.
- SUM(): It returns the sum of values of a specified field
- MIN(): It returns the minimum of the values of a specified field
- MAX(): It returns the maximum of the values of a specified field
- AVG(): It returns the average of the values of a specified field
For example, we can use COUNT function in an SQl statement as follows:
- SELECT count(*) FROM customers
The above query will return the count of total numbers of records in customers table.
We can group records using GROUP BY clause, for example, we can use the following SQL command for retrieving all the records from the customers table, groped by city field:
- SELECT * FROM customers GROUP BY City
We can use HAVING clause with GROUP BY clause. This clause is same as the WHERE clause, but is only used with GROUP BY. With this clause, we can specify addtional criteria for the records we want to retrieve. For example, to select those records from the Customers table for which city field value begins with 'Los', we will use the following SQL command:
- SELECT * FROM customers GROUP BY City HAVING City LIKE 'los%'
DELETE statement is used to delete records from a table. For example, to delete all the records from Customers table foe which city field value is neither 'Los Angeles' nor 'New York', we will use the following SQL command:
- DELETE * FROM customers WHERE City NOT IN ('Los angeles', 'New York')
We can use the UPDATE statement to update a database. For example, we can use the follwing SQL command for changing the city field value to 'California' in all records where its value is 'New york':
- UPDATE customers SET city = 'California' WHERE City = 'New York'
We can also work with relational databases in SQL. Relational databases are used to relate data in multiple tables. Suppose we want to retrieve customer names from the Customers table and the IDs of the items ordered by the customers from the orders table, for this purpose we have to relate these two tables. We can relate these two tables with field customerID, which is present in both the tables. This field is the primary key in the first table and it is a foreign key in the second table. We will use the following SQL command, to retrieve the records according to the above mentioned criteria:
- SELECT customers.ContactName, Orders.OrderID FROM Customers, Orders WHERE Customers.CustomersID = Orders.CustomersID
Comments Post a Comment