Basic SQL Commands Part 2:

SQL IN OPERATOR and SUBQUERIES

 

The IN operator allows you to specify multiple values in a WHERE clause.

 

/* SQL IN and Subqueries */

 SELECT *FROM Customers WHERE Country IN ('UK','Mexico');

 

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A subquery cannot be immediately enclosed in a set function.
• The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.
   
   
  SELECT *FROM Customers
   WHERE ID IN ( SELECT ID FROM Customers WHERE Salary > 4500);
   
  
   
  GROUP BY CLAUSE
  The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
  /*Group By */
   SELECT Name, SUM(Salary) AS Total FROM Customers GROUP BY Name;
   
  
   
   SELECT Customers.Name, Employee.LName,
   COUNT(Orders.OrderID) AS NumberOfOrders
   FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID)
   INNER JOIN Employee
   ON Orders.EmployeeID = Employee.EmployeeID)
   GROUP BY Name,LName;

ORDER BY CLAUSE

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

SELECT *FROM Customers ORDER BY Name,Salary

 

SELECT *FROM Customers ORDER BY  Country;

 

SELECT *FROM Customers ORDER BY  Country DESC;

 

SELECT *FROM Customers ORDER BY Country ASC, Name DESC;

 

HAVING CLAUSE

The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

 

 /*Having Clause*/

 

SELECT Employee.LName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM (Orders INNER JOIN Employee ON Orders.EmployeeID =Employee.EmployeeID)

GROUP BY LName

HAVING COUNT(Orders.OrderID) > 1;

 

SQL JOINS

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

 

/*Join Example */

SELECT ID,Name,Age,Amount FROM Customers,Orders WHERE Customers.ID = Orders.CustomerID;

 

 

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.

SELECT Orders.OrderID, Customers.Name, Orders.Date FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.ID ORDER BY Customers.Name;

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

/*Left Join */

SELECT ID,Name,Date,Amount FROM Customers LEFT JOIN OrdersON Customers.ID = Orders.CustomerID ORDER BY Customers.Name;

 

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

 

/*Right Join*/

SELECT ID,Name,Date,Amount FROM Customers RIGHT JOIN Orders ON Customers.ID = Orders.CustomerID ORDER BY Customers.Name;

 

FULL JOIN

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

 

/*Full Outer Join */

SELECT Customers.Name, Orders.OrderID FROM Customers FULL OUTER JOIN Orders

ON Customers.ID = Orders.CustomerID ORDER BY Customers.Name;

 

 

 

UNION CLAUSE

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

/*Union and Left Join Right Join Example*/

SELECT ID,Name,Date,Amount FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.CustomerID

 

UNION

 

SELECT ID,Name,Date,Amount FROM Customers RIGHT JOIN Orders

ON Customers.ID = Orders.CustomerID

UNION ALL

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.The same rules that apply to UNION apply to the UNION ALL operator.

/*Union all Example*/

SELECT ID,Name,Date,Amount FROM Customers LEFT JOIN Orders

ON Customers.ID = Orders.CustomerID

 

UNION ALL

 

SELECT ID,Name,Date,Amount FROM Customers RIGHT JOIN Orders

    ON Customers.ID = Orders.CustomerID

NULL VALUES

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

SELECT ID,Name,Age,Address,Salary  FROM Customers WHERE Salary IS NOT NULL

 

 

 

SELECT ID,Name,Age,Address,Salary  FROM Customers WHERE Salary IS  NULL

 

VIEW

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depend on the written SQL query to create a view.Views, which are kind of virtual tables, allow users to do the following:

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables which can be used to generate reports.
  Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation.
  /*Create View */
  CREATE VIEW Customers_Viewtest AS SELECT Name,Age
  FROM Customers;
   
  SELECT *FROM Customers_Viewtest;
  
   
  CREATE VIEW Customers_Viewage AS SELECT Name,Age FROM Customers
  WHERE Age IS NOT NULL WITH CHECK OPTION;
   
  SELECT *FROM Customers_Viewage;
  
   
  UPDATE Customers_Viewage SET Age = 35 WHERE name = 'Ramesh'