Sql 2

As a name suggest SQL Wildcard Characters are used to substitute one or more characters in a string. It is used with LIKE operator.

*SELECT FROM Customers WHERE CustomerName LIKE ‘a%’;**

It is supported only by oracle databases

Wildcard Characters

This characters in some cases can be used as pre or post operator in Like operator.

ANother simple yer powerful is IN operator. It allows us to specify multiple values in WHERE clause. It is shorthand for multiple OR conditions.

*SELECT FROM Customers WHERE Country IN (‘Germany’, ‘France’, ‘UK’);**

This will return all customers with country Germany or France or UK

• Also NOT IN operator can be used Similarly.

Another operator is BETWEEN which gives values within given range

*SELECT FROM Products WHERE ProductName BETWEEN ‘Carnivorous’ AND ‘Omnivorous’ ORDER BY ProductName;**

For a numeric array single comma can be replaced by #.

As we have done aliasing while importing library such as matploylib.pyplot in python we can alias column or tableof SQL database as:

SELECT CustomerID AS ID FROM Customers; For column

For multiple space character it must be aliasing array must be surrounded by Square bracket ot double quotes

**SELECT ***

FROM Customers AS Persons;For table

Aliases can be useful when:

• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined together\

SQL Joint is used to combine rows from two or more table based on related column between them

Let’s explain with an example

Order Table

Customer table

As the CustomerID column in both reder to one another. So it is now joined by SQL(INNER JOINT) statement as:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

It is good practice to specify table name otherwise it can also be written as:

SELECT OrderID, CustomerName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

This will give following joined table

there are generally 4 types of joins in sql

• (INNER) JOIN: return record having matching values in table.

• LEFT (OUTER) JOIN: return record having matched values in table+ records from the left table

• RIGHT (OUTER) JOIN: retun all record having matched value in table+recordds from the right table

• FULL (OUTER) JOIN: return all records where there is match in either left or right table.

JOIN and INNER JOIN in syntax will return same result

For LEFT JOINT Syntax will look like:

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right Join also works similarly and eg willl look like:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;

Full join which sometime referred as Full outer join is written as:

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

Another join called SQL SELF JOIN is used to join table with itself.

Another operator which can be used for combining set of two or more SELECT statement is called UNION operator. it automatically removes duplicated but the requirements are:

. Every SELECT statement within UNION must have same no of col

. Column datatype must be matching

SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;

Where as similar operator UNION ALL is used to combine result set of two or more SELECT statemnt with all rows incluuding duplicate

SELECT City, Country FROM Customers WHERE Country=’Germany’ UNION ALL SELECT City, Country FROM Suppliers WHERE Country=’Germany’ ORDER BY City;

Another important statement used with COUNT(), MAX(), MIN()etc is GROUP BY used to group rows having same value into summary rows.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

lETS end with a sql comment.

Sql can be comment with — on starting or text between the line