GROUP BY:
calculate the total sales for each store
First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores.

select * from Store_Information 

SELECT store_name, SUM(Sales) as store_sales
FROM Store_Information 
GROUP BY store_name 

The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithematic operator appears in the SELECT statement. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithematic operator. 

SELECT store_name, SUM(Sales) as store_sales, store_code
FROM Store_Information 
GROUP BY store_name, store_code



Union:
The purpose of the SQL UNION command is to combine the results of two queries together. 
The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.
when using the UNION command all selected columns need to be of the same data type.

select * from Suppliers
select * from Shippers

select CompanyName from Suppliers
union
select CompanyName from Shippers

select * from Customers
select * from Employees

select ContactName  from Customers
union
select FirstName + ' ' +  LastName  from Employees



Subqueries:

select * from Employees
select * from Orders

select EmployeeID, LastName + ' ' + FirstName as EmployeeName, Title
from Employees 
where EmployeeID not in (select EmployeeID from Orders)

select * from [Order Details] order by ProductID
select * from Products

select  tb1.ProductID,  tb2.ProductName,  tb2.UnitPrice
from [Order Details] tb1, Products tb2
where  tb1.ProductID =  tb2.ProductID 
and tb2.UnitPrice >
  (select  AVG(UnitPrice) from [Order Details])
group by  tb1.ProductID,  tb2.ProductName,  tb2.UnitPrice
order by tb1.ProductID



Table Joins:

A primary key is a column or set of columns that uniquely identifies the rest of the data in any given row
A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key.

referential integrity
The purpose of these keys is so that data can be related across tables, without having to repeat data in every table


inner join:
where we select rows common to the participating tables to a join.

select CustomerID from Orders

select CustomerID, ContactName, CompanyName, Address from Customers

select o.CustomerID, o.OrderDate, c.ContactName from Orders o inner join Customers c
  on c.CustomerID = o.CustomerID
order by o.CustomerID


Left Outer Joins:
selecting elements in a table regardless of whether they are present in the second table

To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. 

select * from authors 
select * from publishers

SELECT a.FirstName, a.LastName, p.publisherName
FROM authors a LEFT OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.publisherName ASC, a.LastName ASC, a.FirstName ASC


Right Outer Joins:
To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. 

SELECT a.FirstName, a.LastName, p.publisherName
FROM authors AS a RIGHT OUTER JOIN publishers AS p
   ON a.city = p.city
ORDER BY p.publisherName ASC, a.LastName ASC, a.FirstName ASC