SQL Join Types: Right (Outer) Join with Exclusion

Tables

Table 1: Employees

EmployeeID

LastName

FirstName

BirthDate

Photo

Callahan 

Laura 

1/9/1958 

EmpID8.pic 

Dodsworth 

Anne 

7/2/1969 

EmpID9.pic 

10 

West 

Adam 

9/19/1928 

EmpID10.pic 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10255 

68 

7/12/1996 

10262 

65 

7/22/1996 

10263 

20 

7/23/1996 

10268 

33 

7/30/1996 

Note: There is no record with EmployeeID = 10 (West Adam).

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

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

Result

OrderID

LastName

FirstName

NULL

West

Adam 

Share

SQL Join Types: Right (Outer) Join

Tables

Table 1: Customers

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

37 

Hungry Owl All-Night Grocers 

Patricia McKenna 

8 Johnstown Road 

Cork 

 

Ireland

77 

The Big Cheese 

Liz Nixon 

89 Jefferson Way Suite 2 

Portland 

97201 

USA 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10308

2

7

1996-09-18

3

10309

37

3

1996-09-19

1

10310

77

8

1996-09-20

2

10365 

1996-11-27 

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  RIGHT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result

OrderID

CustomerName

OrderDate

10308 

Ana Trujillo Emparedados y helados 

9/18/1996 

10365 

Antonio Moreno Taquería 

11/27/1996 

10310 

The Big Cheese 

9/20/1996 

Share

SQL Join Types: Left (Outer) Join with Exclusion

Tables

Table 1: Customers

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

37 

Hungry Owl All-Night Grocers 

Patricia McKenna 

8 Johnstown Road 

Cork 

 

Ireland

77 

The Big Cheese 

Liz Nixon 

89 Jefferson Way Suite 2 

Portland 

97201 

USA 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10308

2

7

1996-09-18

3

10309

37

3

1996-09-19

1

10310

77

8

1996-09-20

2

10365 

1996-11-27 

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Orders.CustomerID IS NULL
  AND Customers.CustomerName 
    IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
         , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName; 

Result

OrderID

CustomerName

OrderDate

OrderID

 NULL

Alfreds Futterkiste 

 NULL

 NULL

Share

SQL Join Types: Left (Outer) Join

Tables

 

Table 1: Customers

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

37 

Hungry Owl All-Night Grocers 

Patricia McKenna 

8 Johnstown Road 

Cork 

 

Ireland

77 

The Big Cheese 

Liz Nixon 

89 Jefferson Way Suite 2 

Portland 

97201 

USA 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10308

2

7

1996-09-18

3

10309

37

3

1996-09-19

1

10310

77

8

1996-09-20

2

10365 

11/27/1996 

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste).

Query:

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result:

OrderID

CustomerName

OrderDate

 NULL

Alfreds Futterkiste 

 NULL

10308 

Ana Trujillo Emparedados y helados 

9/18/1996 

10365 

Antonio Moreno Taquería 

11/27/1996 

10310 

The Big Cheese 

9/20/1996 

 

 

 

Share

Some Trade Tools for Design, Development, QA and Dev Ops

Whimsical

A rapid wire-frame  and flow chart. It allows for collaboration.
If  you and your friends can work with up to 4 diagrams, then its free.

Sketch + Craft Plugin

Sketch allows you to design websites, iOS and Android interfaces easily.

Craft, a plugin made by InVision, provide some useful features to Sketch, such as:

  • Collaboration in real time
  • Allow to build prototypes based on design files
  • Allow for the synchronization of your design environment
  • Allows to load real data to your design
  • Its cloud enabled for sharing design assets
  • Allow for the clone of design elements.

Invision + Zeplin

Invision is another design platform, as well as the creators of the Craft plugin for Sketch.
It allows to design, document, and prototype websites, iOS, Android and more.
You need to use an email account in order to start the free version.

The Zeplin plugin provide accurate specs, assets and even code snippets made from the designs.
In our case, we are using the Zeplin plugin for Sketch.
It allows to export designs into Sketch, Adobe Photoshop CC, Figma and Sketch.
It provides extensions, some which you can even create yourself.
Create component libraries, do checklist to build every component, and tag people if needed.
If you are only working on one project at a time, you can do it by free by creating an account.

Framer

Note: Framer is for Mac OS only; however, they promise to bring a client version for Windows soon.

Framer allows you to design with code by using React. It provides a transitional and responding layout.

Adaptive layouts, design toolkit with logos and icons libraries, reusable and iterative components, video players, in-app icons and UI elements, and more powered by React.

Apiary

Apiary is an Oracle tool which allows you to design an API without requiring to write code. Then, you can share the designed API.
It allows for the use of Markdown, tests using mock servers, validation proxies and much more. Plus, it gives you code examples in different languages.
If you are a Command Line guy, there is a Apiary CLI Gem available.

Here is a Oracle session showing the best practices using this tool:

CircleCI

CircleCI is a continuous integration software. It will allow your teams to focus on working in incremental “stories”, each story is a code that will be merged incrementally into your shared repository software. The software can be setup to automate tests, deploys and much more. CircleCI is the equivalent of Jenkins or GitLab CI.

TestRail

TestRail is a test case management software which allows you to manage, design and run your test cases. It provide an insight of your testing progress; as well as to allow you to track issues and perform automation tests with your CI systems. It works with JIRA, Visual Studio,  Bugzilla, and up to 33 tools out there.

Here is a video example which shows how to add a test plan in TestRail:

FastLane

FastLane is a Continuous Delivery tool which allows you automate the building and release mobile apps. It is Open Source and design for the Android and iOS deployment.

With FastLane, there is no need to take screenshots, spend hours in code signing, figure out how to distribute beta builds, and such. This software is supposed to take care of all that.

Here is a video explanation made by Feliz Krause in MCE3 about deploying an iOS app in the App Store using FastLane:

Firebase Analytics

Firebase is a Google product. It provides all types of analysis data for web apps, mobile apps, Unity apps and more. It comes with multiple features such as authentication, database, cloud, hosting, analytics, remote config, performance monitoring and more. Below is a playlist of one of the features, Google Analytics for Firebase

One of the interesting things about Firebase is that includes Crashlytics. Crashlytics is a tool that allow you to keep track of the crashes in your apps and was made by Fabric.io. Google decided to ditch their own crash analytics tool and provide Crashlytics instead.

Its my understanding that Fabric was purchased by Google, so there is a whole promotion to move from Crashlytics to Firebase since Crashlytics seems to be included in Firebase. There is Crashlytics for iOS and Crashlytics for Android. By the time I am writing this post, I haven’t yet made the transition from Crashlytics to Firebase since I have to sit down and read all the policies, licenses and user agreements. I will try to keep you updated.

 

Share