3.Widlcards,Clauses,Joins with Examples.

#Wildcards:In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

Wildcard %  - A substitute for zero or more characters

Ex:The following SQL statement selects all customers with a City starting with "sam":

SELECT * FROM customers
WHERE City LIKE 'sam%';

The following SQL statement selects all customers with a City containing the pattern "lin":

SELECT * FROM customers
WHERE City LIKE '%lin%';

Wildcard '_' A substitute for a single character.

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

Example

SELECT * FROM customers
WHERE City LIKE '_erlin';

[charlist] Sets and ranges of characters to match

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

The following SQL statement selects all customers with a City starting with "a", "b", or "c":

Example

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

[^charlist]
or
[!charlist] Matches only :

Ex:The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

#Cluases:

Group By Cluase:The Oracle GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

Ex:This Oracle GROUP BY example uses the SUM function to return the name of the product and the total sales (for the product).

SELECT product, SUM(sale) AS "Total sales"
FROM order_details
GROUP BY product;

Having Clause : The Oracle HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Ex:SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 25000;

Distinct Clause:The Oracle DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used with SELECT statements.

Ex:SELECT DISTINCT state
FROM customers
WHERE last_name = 'Smith';

Order By Clause:The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement. The sort order is defaulted to ASC or ascending order.

Ex:SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;

#JOINS:

Oracle INNER JOIN (or sometimes called simple join)
Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)