Skip to main content

9. Basic keywords and Its uses

Image
By

SELECT

SELECTcolumn_name,column_name
FROMtable_name;
and
SELECT*FROMtable_name;

DISTINCT

Used to return only distinct (different) values.
SELECTDISTINCTcolumn_name,column_name
FROMtable_name;

WHERE

Used to extract only those records that fulfill a specified criterion.
SELECTcolumn_name,column_name
FROMtable_name
WHEREcolumn_name operator value;

AND & OR Operators

Used to filter records based on more than one condition.
SELECT*FROM Customers
WHERE Country='Germany'
AND City='Berlin';

SELECT*FROM Customers
WHERE City='Berlin'
OR City='Mnchen';

SELECT TOP Clause

Used to specify the number of records to return.
SELECTTOPnumber|percentcolumn_name(s)
FROMtable_name;

LIKE Operator

Used to search for a specified pattern in a column.
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameLIKE pattern;

Examples

§ Selects all customers with a City starting with the letter "s":
SELECT*FROM Customers
WHERE City LIKE's%';

§ Selects all customers with a City ending with the letter "s":
SELECT*FROM Customers
WHERE City LIKE'%s';

§ Selects all customers with a Country containing the pattern "land":
SELECT*FROM Customers
WHERE Country LIKE'%land%';

§ Selects all customers with Country NOT containing the pattern "land":
SELECT*FROM Customers
WHERE Country NOTLIKE'%land%';

Wildcard Characters

§ Wildcard characters are used with the SQL LIKE operator.
§ Wildcards are used to search for data within a table.

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.
§ Syntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameIN(value1,value2,...);

§ Example
SELECT*FROM Customers
WHERE City IN('Paris','London');

BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
§ Syntax
SELECTcolumn_name(s)
FROMtable_name
WHEREcolumn_nameBETWEEN value1 AND value2;

§ Example
SELECT*FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Operator

The NOT BETWEEN operator selects values except that range.(outside of the range specified)
§ Example
SELECT*FROM Products
WHERE Price NOTBETWEEN 10 AND 20;

BETWEEN Operator with IN Example

Selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:
§ Example
SELECT*FROM Products
WHERE (Price BETWEEN 10 AND 20)
ANDCategoryIDNOTIN(1,2,3);

BETWEEN Operator with Text Value Example


Selects all products with a ProductName beginning with any of the letter BETWEEN 'C' and 'M':
§ Example
SELECT*FROM Products
WHEREProductNameBETWEEN'C'AND'M';

NOT BETWEEN Operator with Text Value Example


Selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':
§ Example
SELECT*FROM Products
WHEREProductNameNOTBETWEEN'C'AND'M';

BETWEEN Operator with Date Value Example


Selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':
§ Example
SELECT*FROM Orders

WHEREOrderDateBETWEEN #07/04/1996# AND #07/09/1996#;




















<<Prev                                                                          Next>>









Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                               ...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more