SQL - Left Join and Left Outer Join

SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country

  FROM Customer C LEFT JOIN [Order] O

    ON O.CustomerId = C.Id

ORDER BY TotalAmount

SQL - Cross Join Or Full Join Or Full Outer Join

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, performs a full join. A full join is also called a cross join because each row of each table is crossed with each row in every other table to produce all possible combinations. This is also known as the Cartesian Product.

Joining tables this way has the potential to produce a very large number of rows because the possible row count is the product of the number of rows in each table. A full join between three tables that contain 10, 20, and 30 rows, respectively, could return 10x20x30 = 6 thousand rows.

The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELECT DISTINCT JobTitle

          FROM   HumanResources.Employee

SELECT DISTINCT Gender

          FROM   HumanResources.Employee

SELECT JobTitle,

       Gender,

       COUNT(1) AS NumberEmployees

          FROM HumanResources.Employee

          GROUP BY JobTitle, Gender

WITH cteJobTitle (JobTitle)

AS  (SELECT DISTINCT JobTitle

     FROM   HumanResources.Employee),

cteGender (Gender)

AS (SELECT DISTINCT Gender

    FROM   HumanResources.Employee)

SELECT   J.JobTitle,

         G.Gender

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

ORDER BY J.JobTitle

WITH     cteJobTitle (JobTitle)

AS       (SELECT DISTINCT JobTitle

          FROM   HumanResources.Employee),

         cteGender (Gender)

AS       (SELECT DISTINCT Gender

          FROM   HumanResources.Employee),

         cteCounts (JobTitle, Gender, NumberEmployees)

AS       (SELECT   JobTitle,

                   Gender,

                   COUNT(1) AS NumberEmployees

          FROM     HumanResources.Employee

          GROUP BY JobTitle, Gender)

SELECT   J.JobTitle,

         G.Gender,

         COALESCE (C.NumberEmployees, 0) as NumberEmployees

FROM     cteJobTitle AS J

         CROSS JOIN cteGender AS G

         LEFT OUTER JOIN cteCounts AS C

         ON C.JobTitle = J.JobTitle

            AND C.Gender = G.Gender

ORDER BY J.JobTitle, G.Gender;

Query Employee With Birthdate in the Next 7 days

DECLARE @TODAY INT = DATEPART(DAYOFYEAR, GETDATE())

DECLARE @DAYSINYEAR INT = DATEPART(DAYOFYEAR, DATEADD(DAY, -1 , DATEFROMPARTS(DATEPART(YEAR, DATEADD(YEAR, 1, GETDATE())), 1, 1)))

SELECT [DATE_OF_BIRTH]

FROM EMPLOYEE

WHERE (CASE WHEN DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) < @TODAY

            THEN DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) + @DAYSINYEAR

            ELSE DATEPART(DAYOFYEAR, [DATE_OF_BIRTH]) END)

      BETWEEN @TODAY and @TODAY + 6

SQL - Self Join

Self Join allows you to join a table to itself. It is useful for querying hierarchical data or comparing rows within the same table.

Self Join uses the inner join or left join clause. Because the query that uses self join references the same table, the table alias is used to assign different names to the same table within the query.

Example 1

--SQL self join with inner join example

SELECT

    concat(e.FIRSTNAME, e.LASTNAME) EMPLOYEE,

    concat(m.FIRSTNAME, m.LASTNAME) MANAGER

FROM

    EMPLOYEES e

INNER JOIN

    EMPLOYEES m ON m.EMPLOYEEID = e.REPORTSTO;

--SQL self join with left join example

SELECT

    concat(e.FIRSTNAME, e.LASTNAME) EMPLOYEE,

    concat(m.FIRSTNAME, m.LASTNAME) MANAGER

FROM

    EMPLOYEES e

LEFT JOIN

    EMPLOYEES m ON m.EMPLOYEEID = e.REPORTSTO

ORDER BY MANAGER;

--Compare a row with other rows in the employees table by any column by using self join

SELECT

    e1.FIRSTNAME, e2.FIRSTNAME, e1.CITY

FROM

    EMPLOYEES e1

INNER JOIN

    --Condition matches the city of the two customers

    EMPLOYEES e2 ON e2.CITY = e1.CITY

WHERE

    e1.EMPLOYEEID <> e2.EMPLOYEEID

ORDER BY

    e1.CITY , e1.FIRSTNAME;

--To find employees who locate in the same city as Anne, you add a condition to the  WHERE clause

SELECT

    e1.FIRSTNAME, e2.FIRSTNAME, e1.CITY

FROM

    EMPLOYEES e1

INNER JOIN

    --Condition matches the city of the two customers

    EMPLOYEES e2 ON e2.CITY = e1.CITY

WHERE

    e1.EMPLOYEEID <> e2.EMPLOYEEID AND

    e1.FIRSTNAME = 'Anne'  

ORDER BY

    e1.CITY , e1.FIRSTNAME;

  

--Finds all employees who have the same hire dates

SELECT

   e1.Hire_Date,

  (e1.First_Name || ' ' || e1.Last_Name) Employee1,

  (e2.First_Name || ' ' || e2.Last_Name) Employee2 

FROM

    Employees e1

INNER JOIN employees e2 ON
    --Makes sure that the statement doesn’t compare the same customer

    e1.Employee_Id > e2.Employee_Id 

    AND e1.Hire_Date = e2.Hire_Date

ORDER BY 

   e1.Hire_Date DESC,

   Employee1,

   Employee2;

SQL - Inner Join

Query data from a single table using the SELECT.

Query data from multiple tables to have a complete result set for analysis.

To query data from multiple tables you use join statements.

Types of joins such as inner join, outer join ( left outer join or left join, right outer join or right join, and full outer join) and self join.

1.     One category can have many products.

2.     One product belongs to one and only one category.

Therefore, there is a many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is the categoryid column.

SELECT * FROM SUPPLIERS;

SELECT * FROM CATEGORIES;

SELECT * FROM PRODUCTS;

SQL INNER JOIN – querying data from two tables example

SELECT

    PRODUCTID, PRODUCTNAME, CATEGORYNAME

FROM

    PRODUCTS

INNER JOIN

    CATEGORIES ON CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID;

SQL INNER JOIN – querying data from three tables

SELECT

    PRODUCTID,

    PRODUCTNAME,

    CATEGORYNAME,

    COMPANYNAME AS SUPPLIER

FROM

    PRODUCTS

INNER JOIN

    CATEGORIES ON CATEGORIES.CATEGORYID = PRODUCTS.CATEGORYID

INNER JOIN

    SUPPLIERS ON SUPPLIERS.SUPPLIERID = PRODUCTS.SUPPLIERID

Implicit SQL INNER JOIN

SELECT

    PRODUCTID, PRODUCTNAME, CATEGORYNAME

FROM

    PRODUCTS,

    CATEGORIES

WHERE

    PRODUCTS.CATEGORYID = CATEGORIES.CATEGORYID;

MySQL - Create User and Grant All Privilege

1.      In command line, type the following command, replacing USERNAME with your username and password to connect your database:

            mysql -u USERNAME -p PASSWORD

2.      Create new user in MySQL and give it full access to one database

      To create user in MySQL/MariaDB, use CREATE USER syntax:

            CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';

            GRANT ALL PRIVILEGES ON * . * TO 'admin'@'localhost';