-- Select the full name of all employees. SQL SERVER.

SELECT FirstName + ' ' + LastName
FROM Employees;

-- Select the full name of all employees. Oracle.

SELECT FirstName || ' ' || LastName
FROM Employees;

-- Select the full name of all employees. MySQL.
SELECT CONCAT(FirstName, ' ', LastName)
FROM Employees;

/*
If the cost of freight is greater than or equal to $500.00,
it will now be taxed by 10%. Create a report that shows the
order id, freight cost, freight cost with this tax for all
orders of $500 or more.
*/

SELECT OrderID, Freight, Freight * 1.1
FROM Orders
WHERE Freight >= 500;

SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal
FROM Orders
WHERE Freight >= 500;

-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;

-- Find the Total Number of Units Ordered of Product ID 3

/******************************
SQL Server
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM "Order Details"
WHERE ProductID=3;

/******************************
Oracle and MySQL
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM Order_Details
WHERE ProductID=3;

-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

-- Find the Earliest and Latest Dates of Hire

SELECT MIN(HireDate) AS FirstHireDate,
 MAX(HireDate) AS LastHireDate
FROM Employees;

--Retrieve the number of employees in each city

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City;

/*
 Retrieve the number of employees in each city 
 in which there are at least 2 employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1;

/*
 Find the number of sales representatives in each city that contains 
 at least 2 sales representatives. Order by the number of employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING COUNT(EmployeeID) > 1
ORDER BY NumEmployees;

/*
Find all the distinct cities in which Northwind has employees.
*/

SELECT DISTINCT City
FROM Employees
ORDER BY City

/*
Find out in how many different cities Northwind has employees.
*/

SELECT COUNT(DISTINCT City) AS NumCities
FROM Employees

/*
Select freight as is and
freight rounded to the first decimal (e.g, 1.150 becomes 1.200)
from the Orders tables
*/

SELECT Freight, ROUND(Freight,1)  AS ApproxFreight
FROM Orders;

/*
Select the unit price as is and
unit price as a decimal with 2 places to the right of the decimal point
from the Products tables
*/
/******************************
SQL Server and MySQL
******************************/
SELECT UnitPrice, CAST(UnitPrice AS Decimal(8,2))
FROM Products;

/******************************
Oracle
******************************/
SELECT UnitPrice, TO_CHAR(UnitPrice,'999.99')
FROM Products;

/*
Select first and last name from employees in all uppercase letters
*/
SELECT UPPER(FirstName), UPPER(LastName)
FROM Employees;

-- Select the first 10 characters of each customer's address

/******************************
SQL Server and MySQL
******************************/
SELECT SUBSTRING(Address,1,10)
FROM Customers;

/******************************
Oracle
******************************/
SELECT SUBSTR(Address,1,10)
FROM Customers;

-- Find the hiring age of each employee

/******************************
SQL Server
******************************/
SELECT LastName, BirthDate, HireDate, DATEDIFF(year,BirthDate,HireDate) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
Oracle
******************************/
SELECT  LastName, BirthDate, HireDate, FLOOR((HireDate - BirthDate)/365.25) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
MySQL
******************************/
-- Find the hiring age of each employee
-- in versions of MySQL prior to 4.1.1
SELECT LastName, BirthDate, HireDate, YEAR(HireDate)-YEAR(BirthDate) AS HireAge
FROM Employees;

-- In MySQL 4.1.1 and later, DATEDIFF() returns the number of days between
-- two dates. You can then divide and floor to get age.
SELECT LastName, BirthDate, HireDate, FLOOR(DATEDIFF(HireDate,BirthDate)/365) AS HireAge
FROM Employees
ORDER BY HireAge;

-- Find the Birth month for every employee

/******************************
SQL Server
******************************/
SELECT FirstName, LastName, DATENAME(month,BirthDate) AS BirthMonth
FROM Employees
ORDER BY DATEPART(month,BirthDate);

/******************************
Oracle
******************************/
SELECT FirstName, LastName, TO_CHAR(BirthDate,'MONTH') AS BirthMonth
FROM Employees
ORDER BY TO_NUMBER(TO_CHAR(BirthDate,'MM'));

/******************************
MySQL
******************************/
SELECT FirstName, LastName, DATE_FORMAT(BirthDate, '%M') AS BirthMonth
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate);