SQL is the standard language used for accessing and manipulating data stored in database.A database,as discussed earlier,is a structured collection of records or data so that you can easily access,manage,and update the data.In a database,data is stored in the form of tables.Tables store data in the form of rows and cloumns. Each column of a table represents a different attribute termed as a field. Each row stores entries for all the attribute values for a single record.These records can be stored,indexed,update and organized easily by writing the different SQL query statements.SQL provides separate statements for inserting new records,updating records,and creating new tables and datasets. A dataset is an in-memory representation of data.
If you are working with SQL in C#, you can use SQL statements to access and modify the data in databases. In our case,we work on a database. This database contains different tables,such as the Customer table,which has different fields(Such as CustomerId and Address),the Orders table,which has fields (Such as OrderId),and the OrderDetails table,which has fields (such as Quntity).
The Select Statement
The SELECT statement is used to retrieve values for some or all the fields of a table.For example,you can write the following SQL statement to retrieve all the records from the Customers table by sing the wildcard character *;
SELECT * FROM Customers
The preceding SQL statement returns all the records from the Customers table. You can also use the SELECT statement to retrieve values of specific fields of a table. For example, you can retrieve values from the CustomerId,Address and City fields of the Customers table by using the following SQL statement:
SELECT CustomerId, Address, City FROM Customers
The preceding statement returns the values of the CustomerId, Address , and City field of the Customers table.
The Where Clause
In SQL,you can use the WHERE clause to specify a criteria to retrieve the records. For example, to retrieve all the records from the Customers table,where the value of City field is Mirmee, you can use the WHERE clause,as shown in the following SQL statement:
SELECT * FROM Customers WHERE City ='Mirmee'
As shown in the preceding SQL statement,we have used the = (equal to )operator to specify a criteria in the WHERE clause. In addition to the = operator,you can use the following operators in the WHERE clause:
⦁ < (less than)
⦁ <=(less than or queal to)
⦁ >(grater than)
⦁ >=(grater than or equal to)
The comparison operators,such as < and > ,are used in the same way as the = operator.
Now,let's see how to use the BETWEEN,IN and LIKE operators.
The BETWEEN Operator
You can use the between operator to access values that lie in a specified range. For example, if you want to select all the records from the Customers table,where the first letter of the CUstomerId field is between R and T (which includes the latter R, S and T), you can use the BETWEEN operator, as shown in the following SQL statement:
SELECT * FROM Customers WHERE CustomerId BETWEEN 'R*' AND 'T*'
The IN Operator
In SQL, you can use the IN operator to match the values for a given field of a table with a set of specified values. For example, to select those records for which the City field has a value Mirmee, Phant, you can use the IN operator,as shown in the following SQL statement
SELECT * FROM Customers WHERE City IN('Mirmee', Phant')
The Like Operator
In SQL,you can used to match the values of a field in a table with some specified pattern. You can use the character % to specify the pattern. For example, to select all the records from the Customers table, where the City field matches the string Mi%, you can use the LIKE operator,as shown in the following SQL statement:
SELECT * FROM Customers WHERE City Like ='Mi%'
The preceding SQL statement selects all the records for which the City field contains names that match with the pattern Mi%, such as Mirmee. The three possible combinations of using the character % are as follows:
⦁ Mi% :Matches the values starting with Mi,such as MIrmee
⦁ %Mi : Matches the values ending with Mi
⦁ %Mi% : Matches the values containing Mi,such as MaloMi