Gatsby Serverless Functions And The International Space Station

 Gatsby recently announced the launch of “functions”. In this article, Paul Scanlon explains how to get the current location of the International Space Station (ISS) as it orbits the Earth in realtime using Gatsby Functions and then display it 

a 3D interactive globe using React Three Fibre. 

Gatsby recently announced the launch of Functions which opens up a new dimension of possibilities — and I for one couldn’t be more excited! With Gatsby now providing Serverless Functions on Gatsby Cloud (and Netlify also providing support via @netlify/plugin-gatsby), the framework that was once misunderstood to be “just for blogs” is now more than ever, (in my opinion) the most exciting technology provider in the Jamstack space.

Getting Started #

With Gatsby Functions, you can create more dynamic applications using techniques typically associated with client-side applications by adding an api directory to your project and exporting a function, e.g.

|-- src
  |-- api
     -- some-function.js
  |-- pages

// src/api/some-function.js
export default function handler(req, res) {
  res.status(200).json({ hello: `world` })
}

If you already have a Gatsby project setup, great! but do make sure you’ve upgraded Gatsby to at least version v3.7

npm install gatsby@latest --save

If not, then feel free to clone my absolute bare-bones Gatsby starter repo: mr-minimum.

Before I can start using Gatsby Functions to track the International Space Station, I first need to create a globe for it to orbit.

Step 1: Building The 3D Interactive Globe #

I start by setting up a 3D interactive globe which can be used later to plot the current ISS location.

Install Dependencies #

npm install @react-three/fiber @react-three/drei three three-geojson-geometry axios --save

Create The Scene #

Create a new file in src/components called three-scene.js

// src/components/three-scene.js
import React from 'react';
import { Canvas } from '@react-three/fiber';
import { OrbitControls } from '@react-three/drei';

const ThreeScene = () => {
  return (
    <Canvas
      gl={{ antialias: false, alpha: false }}
      camera={{
        fov: 45,
        position: [0, 0, 300]
      }}
      onCreated={({ gl }) => {
        gl.setClearColor('#ffffff');
      }}
      style={{
        width: '100vw',
        height: '100vh',
        cursor: 'move'
      }}
    >
      <OrbitControls enableRotate={true} enableZoom={false} enablePan={false} />
    </Canvas>
  );
};

export default ThreeScene;

The above sets up a new <Canvas /> element and can be configured using props exposed by React Three Fibre.

Elements that are returned as children of the canvas component will be displayed as part of the 3D scene. You’ll see above that I’ve included <OrbitControls /> which adds touch/mouse interactivity allowing users to rotate the scene in 3D space

Ensure ThreeScene is imported and rendered on a page somewhere in your site. In my example repo I’ve added ThreeScene to index.js:

// src/pages/index.js
import React from 'react';

import ThreeScene from '../components/three-scene';

const IndexPage = () => {
  return (
    <main>
      <ThreeScene />
    </main>
  );
};

export default IndexPage; 

This won’t do much at the moment because there’s nothing to display in the scene. Let’s correct that!

Create The Sphere #

Create a file in src/components called three-sphere.js:

// src/components/three-sphere.js
import React from 'react';

const ThreeSphere = () => {
  return (
    <mesh>
      <sphereGeometry args={[100, 32, 32]} />
      <meshBasicMaterial color="#f7f7f7" transparent={true} opacity={0.6} />
    </mesh>
  );
};

export default ThreeSphere;

If the syntax above looks a little different to that of the Three.js docs it’s because React Three Fibre uses a declarative approach to using Three.js in React.

A good explanation of how constructor arguments work in React Three Fibre can be seen in the docs here: Constructor arguments

Now add ThreeSphere to ThreeScene:

// src/components/three-scene.js import React from 'react'; import { Canvas } from '@react-three/fiber'; import { OrbitControls } from '@react-three/drei'; + import ThreeSphere from './three-sphere'; const ThreeScene = () => { return ( <Canvas gl={{ antialias: false, alpha: false }} camera={{ fov: 45, position: [0, 0, 300] }} onCreated={({ gl }) => { gl.setClearColor('#ffffff'); }} style={{ width: '100vw', height: '100vh', cursor: 'move' }} > <OrbitControls enableRotate={true} enableZoom={false} enablePan={false} /> + <ThreeSphere /> </Canvas> ); }; export default ThreeScene; 

Not very exciting, ay? Let’s do something about that!

Create The Geometry (To Visualize The Countries Of Planet Earth) #

This next step requires the use of three-geojson-geometry and a CDN resource that contains Natural Earth Data. You can take your pick from a full list of suitable geometries here.

I’ll be using admin 0 countries. I chose this option because it provides enough geometry detail to see each country, but not so much that it will add unnecessary strain on your computer’s GPU.

Now, create a file in src/components called three-geo.js:

// src/components/three-geo.js
import React, { Fragment, useState, useEffect } from 'react';
import { GeoJsonGeometry } from 'three-geojson-geometry';
import axios from 'axios';

const ThreeGeo = () => {
const [isLoading, setIsLoading] = useState(true);
  const [geoJson, setGeoJson] = useState(null);
 
  useEffect(() => {
    axios
      .get(
   'https://d2ad6b4ur7yvpq.cloudfront.net/naturalearth-3.3.0/ne_110m_admin_0_countries.geojson'
      )
      .then((response) => {
        setIsLoading(false);
        setGeoJson(response.data);
      })
      .catch((error) => {
        console.log(error);
        throw new Error();
      });
  }, []);

  return (
    <Fragment>
      {!isLoading ? (
        <Fragment>
          {geoJson.features.map(({ geometry }, index) => {
            return (
              <lineSegments
                key={index}
                geometry={new GeoJsonGeometry(geometry, 100)}
              >
                <lineBasicMaterial color="#e753e7" />
              </lineSegments>
            );
          })}
        </Fragment>
      ) : null}
    </Fragment>
  );
};

export default ThreeGeo;

There’s quite a lot going on in this file so I’ll walk you through it.

1. Create an isLoading state instance using React hooks and set it to true. This prevents React from attempting to return data I don’t yet have.
2. Using a useEffect I request the geojson from the CloudFront CDN.
3. Upon successful retrieval I set the response in React state using setGeoJson(...) and set isLoading to false
4. Using an Array.prototype.map I iterate over the “features” contained within the geojson response and return lineSegments with lineBasicMaterial for each geometry
5. I set the lineSegments geometry to the return value provided by GeoJsonGeomtry which is passed the “features” geometry along with a radius of 100.

(You may have noticed I’ve used the same radius of 100 here as I’ve used in the sphereGeometry args in three-sphere.js. You don’t have to set the radius to the same value but it makes sense to use the same radii for ThreeSphere and ThreeGeo.

If you’re interested to know more about how GeoJsonGeometry works, here’s the open-source repository for reference: https://github.com/vasturiano/three-geojson-geometry. The repository has an example directory however, the syntax is slightly different from what you see here because the examples are written in vanilla JavaScript not React.

Combine The Sphere And Geometry #

Now it’s time to overlay the geometry on top of the blank sphere: Add ThreeGeo to ThreeScene

// src/components/three-scene.js
import React from 'react';
import { Canvas } from '@react-three/fiber';
import { OrbitControls } from '@react-three/drei';

import ThreeSphere from './three-sphere';
+ import ThreeGeo from './three-geo';


const ThreeScene = () => {
  return (
    <Canvas
      gl={{ antialias: false, alpha: false }}
      camera={{
        fov: 45,
        position: [0, 0, 300]
      }}
      onCreated={({ gl }) => {
        gl.setClearColor('#ffffff');
      }}
      style={{
        width: '100vw',
        height: '100vh',
        cursor: 'move'
      }}
    >
      <OrbitControls enableRotate={true} enableZoom={false} enablePan={false} />
      <ThreeSphere />
+      <ThreeGeo />
    </Canvas>
  );
};

You should now be looking at something similar to the image below.

Exciting Three.js sphere displaying the countries of the world (Large preview)

Now that’s slightly more exciting!

Step 2: Building A Serverless Function #

Create A Function #

This next step is where I use a Gatsby Function to request data from Where is ISS at, which returns the current location of the International Space Station.

Create a file in src/api called get-iss-location.js:

// src/api/get-iss-location.js
const axios = require('axios');

export default async function handler(req, res) {
  try {
    const { data } = await axios.get(
      'https://api.wheretheiss.at/v1/satellites/25544'
    );

    res.status(200).json({ iss_now: data });
  } catch (error) {
    res.status(500).json({ error });
  }
}

This function is responsible for fetching data from api.whereistheiss.at and upon success will return the data and a 200 status code back to the browser.

The Gatsby engineers have done such an amazing job at simplifying serverless functions that the above is all you really need to get going, but here’s a little more detail about what’s going on.

• The function is a default export from a file named get-iss-location.js;
• With Gatsby Functions the filename becomes the file path used in a client-side get request prefixed with api, e.g. /api/get-iss-location;
• If the request to “Where is ISS at” is successful I return an iss_now object containing data from the Where is ISS at API and a status code of 200 back to the client;
• If the request errors I send the error back to the client.

Step 3: Build The International Space Station #

Creating The ISS Sphere #

In this next step, I use Gatsby Functions to position a sphere that represents the International Space Station as it orbits the globe. I do this by repeatedly calling an axios.get request from a poll function and setting the response in React state.

Create a file in src/components called three-iss.js

// src/components/three-iss.js
import React, { Fragment, useEffect, useState } from 'react';
import * as THREE from 'three';
import axios from 'axios';

export const getVertex = (latitude, longitude, radius) => {
  const vector = new THREE.Vector3().setFromSpherical(
    new THREE.Spherical(
      radius,
      THREE.MathUtils.degToRad(90 - latitude),
      THREE.MathUtils.degToRad(longitude)
    )
  );
  return vector;
};

const ThreeIss = () => {
  const [issNow, setIssNow] = useState(null);

  const poll = () => {
    axios
      .get('/api/get-iss-location')
      .then((response) => {
        setIssNow(response.data.iss_now);
      })
      .catch((error) => {
        console.log(error);
        throw new Error();
      });
  };

  useEffect(() => {
    const pollInterval = setInterval(() => {
      poll();
    }, 5000);

    poll();
    return () => clearInterval(pollInterval);
  }, []);

  return (
    <Fragment>
      {issNow ? (
        <mesh
          position={getVertex(
            issNow.latitude,
            issNow.longitude,
            120
          )}
        >
          <sphereGeometry args={[2]} />
          <meshBasicMaterial color="#000000" />
        </mesh>
      ) : null}
    </Fragment>
  );
};

export default ThreeIss;

There’s quite a lot going on in this file so I’ll walk you through it.

1. Create an issNow state instance using React hooks and set it to null. This prevents React from attempting to return data I don’t yet have;
2. Using a useEffect I create a JavaScript interval that calls the poll function every 5 seconds;
3. The poll function is where I request the ISS location from the Gatsby Function endpoint (/api/get-iss-location);
4. Upon successful retrieval, I set the response in React state using setIssNow(...);
5. I pass the latitude and longitude onto a custom function called getVertex, along with a radius.

You may have noticed that here I’m using a radius of 120. This does differ from the 100 radius value used in ThreeSphere and ThreeGeo. The effect of the larger radius is to position the ISS higher up in the 3D scene, rather than at ground level — because that’s logically where the ISS would be, right?
100 has the effect of the sphere and geometry overlapping to represent Earth, and 120 for the ISS has the effect of the space station “orbiting” the globe I’ve created.

One thing that took a bit of figuring out, at least for me, was how to use spherical two dimensional coordinates (latitude and longitude) in three dimensions, e.g. x,y,z. The concept has been explained rather well in this post by Mike Bostock.

The key to plotting lat / lng in 3D space lies within this formula… which makes absolutely no sense to me!

x=rcos(ϕ)cos(λ)
y=rsin(ϕ)
z=−rcos(ϕ)sin(λ)

Luckily, Three.js has a set of MathUtils which I’ve used like this:

• Pass the latitude, longitude and radius into the getVertex(...) function
• Create a new THREE.Spherical object from the above named parameters
• Set the THREE.Vector3 object using the Spherical values returned by the setFromSpherical helper function.

These numbers can now be used to position elements in 3D space on their respective x, y, z axis — phew! Thanks, Three.js!

Now add ThreeIss to ThreeScene:

import React from 'react';
import { Canvas } from '@react-three/fiber';
import { OrbitControls } from '@react-three/drei';

import ThreeSphere from './three-sphere';
import ThreeGeo from './three-geo';
+ import ThreeIss from './three-iss';

const ThreeScene = () => {
  return (
    <Canvas
      gl={{ antialias: false, alpha: false }}
      camera={{
        fov: 45,
        position: [0, 0, 300]
      }}
      onCreated={({ gl }) => {
        gl.setClearColor('#ffffff');
      }}
      style={{
        width: '100vw',
        height: '100vh',
        cursor: 'move'
      }}
    >
      <OrbitControls enableRotate={true} enableZoom={false} enablePan={false} />
      <ThreeSphere />
      <ThreeGeo />
+     <ThreeIss />
    </Canvas>
  );
};

export default ThreeScene; 

The poll function will repeatedly call the Gatsby Function, which in turn requests the current location of the ISS and re-renders the React component each time a response is successful. You’ll have to watch carefully but the ISS will change position ever so slightly every 5 seconds.

The ISS is traveling at roughly 28,000 km/h and polling the Gatsby Function less often would reveal larger jumps in position. I’ve used 5 seconds here because that’s the most frequent request time as allowed by the Where is ISS at API

You might have also noticed that there’s no authentication required to request data from the Where is ISS at API. Meaning that yes, technically, I could have called the API straight from the browser, however, I’ve decided to make this API call server side using Gatsby Functions for two reasons:

1. It wouldn’t have made a very good blog post about Gatsby Functions if i didn’t use them.
2. Who knows what the future holds for Where is ISS at, it might at some point require authentication and adding API keys to server side API requests is pretty straightforward, moreover this change wouldn’t require any updates to the client side code.

Step 4: Make It Fancier! (Optional) #

I’ve used the above approach to create this slightly more snazzy implementation: https://whereisiss.gatsbyjs.io,

In this site I’ve visualized the time delay from the poll function by implementing an Svg <circle /> countdown animation and added an extra <circle /> with a stroke-dashoffset to create the dashed lines surrounding it.

Step 5: Apply Your New Geo Rendering Skills In Other Fun Ways! #

I recently used this approach for plotting geographical locations for the competition winners of 500 Bottles: https://500bottles.gatsbyjs.io. A limited edition FREE swag giveaway I worked on with Gatsby’s marketing team.

You can read all about how this site was made on the Gatsby blog: How We Made the Gatsby 500 Bottles Giveaway

In the 500 Bottles site I plot the geographical locations of each of the competition winners using the same method as described in ThreeIss, which allows anyone visiting the site to see where in the world the winners are.

Closing Thoughts

Gatsby Functions really open up a lot of possibilities for Jamstack developers and never having to worry about spinning up or scaling a server removes so many problems leaving us free to think about new ways they can be used.

 

 

Further Reading #

• If you’re interested in learning more about Gatsby Functions, I highly recommend Summer Functions, a five week course run by my good chum Benedicte Raae.
• In a recent FREE Friday night Summer Functions webinar we created an emoji slot machine which was super fun:
• Build an emoji slot machine with a #GatsbyJS Serverless Function · #GatsbySummerFunctions
• You might also be interested in the following episode from our pokey internet show Gatsby Deep Dives where Kyle Mathews (creator of Gatsby) talks us through how Gatsby Functions work:
  
• Gatsby Serverless Functions 💝 — Are we live? with Kyle Mathews

How To Design A Brand Logo (With Ease)

Great logo design can do incredible things for the company behind it. Set the tone for the brand. Improve memorability. Help users emotionally connect to the brand. Create consistency between all marketing channels. Set it apart from the competition. But as a web designer, do you know how to tackle the lofty request of logo design when a client brings it to you?


As a web designer, you may find yourself in a position where a client wants you to design their brand logo. If you’re feeling up to the task, there are some things you need to know first.
For instance:

• How do you translate the value of a brand into a logo?
• Where can you go for logo design inspiration?
• Which tools should you use to create a logo (like Wix Logo Maker)?
• How do you piece together a logo from a bunch of ideas?

Make no mistake: designing a logo is just as complex a task as building a website. Not only do you need to understand what goes into making a great logo, but you need the right tools to create it.
If you’re thinking about doing this, the following guide will answer the four above questions for you.

Tips For Defining Your Brand With Great Logo Design

Let’s say I invited you over to my apartment and you got a gander at my workspace.

Even if you’re not an Apple user, you’d instantly know that I was working from one of their laptops. While Apple’s distinctive product design might be the clue you pick up on first, the apple icon would certainly seal the deal.
The Apple logo design is memorable and leaves a lasting impression — even without having to spell out the company name.
This is what you want to accomplish when designing a brand logo for your client. And here is how you should go about piecing it together:

Make no mistake: designing a logo is just as complex a task as building a website.

“

Tip #1: Ask The Right Questions

Your client might come to you with logo designs that they like the look of and tell you to run with it. Or they might simply suggest you take the company name and make it look “cool”.
But that’s not how great logos are built.
Just as you dive deep into a client’s business, its mission, and what kind of value they bring to their target user before building a website, you’ll have to do similar research for a logo.

Which Questions Do You Need To Ask?

What’s nice about logo design and web design is there’s a lot of overlap in what you need to ask clients to get started. So, consider building a questionnaire that tackles both pieces if you’re building both the branding and site for your clients.
For now, let’s look at the logo questions:

Why did you start your company?	If you can get to the heart of why they went into business, you’ll be able to create a logo that feels more genuine to that original mission.
What does your company do?	Identifying this is important as you don’t want to design something that looks great, but is all wrong for the market they’re working in.
Who do you serve?	While the client’s opinion of the logo matters, you need to focus on building something that looks good from the eyes of their audience.
Why do customers choose your company over others?	Their unique selling proposition will help you differentiate their logo from the competitors’ designs.
Who are your top 3 competitors? How do you feel about their logos?	This is a great question to ask if you want to get a raw, emotional response as opposed to the more buttoned-up responses the other questions may elicit.
What are your company’s values?	A good way to identify a unique edge for a brand is by looking at what they prioritize. Integrity? Customer commitment? Green initiatives?
How would you describe the company’s personality in 3 words?	You’ll be able to get a sense for the company’s “voice” when you talk to the business owner. But it’s always good to see how they describe it, too.
Do you have a slogan?	You won’t necessarily build the words into the logo (especially since there’s no room for it on mobile). But a slogan can help inform the design.
Where do you see your company in 5 years?	Rather than design something that looks good for the company on Day 1, you should create a logo that captures who they plan to be in the near future.
Who are your heroes and why?	Take a gander at the companies they look up to. Even if they don’t have a clear vision for their company yet, who their heroes are will give you a clue as to where they’re heading.

One last thing to ask is whether or not they’ve selected brand colors or fonts yet. The likelihood of this is slim if they’re asking you to design a logo, but it’s better to ask now than to find out later and have to rework it.

How Do You Get The Answers You Seek?

There are a couple of ways to do this. You could always schedule a call with your client and run through the questions with them. The only problem with that is it could either end up feeling like storytime or like pulling teeth.
In my opinion, the better option is to automate this with an online questionnaire. (This also allows you to scale your logo design efforts). Google Forms is always a great option as it’s easy to use and it’s free.

But if you’re already using something like Content Snare to collect files and information from web design clients, you might as well use it for this as well.

Tip #2: Get Inspired By Other Logos

Once you have a clear idea of who your client is and the message they want to convey, it’s time to start designing. But it won’t be as easy as writing out the name of the company and then adding graphical elements to it.
Take some time to do research on the competitive landscape and to gather outside inspiration for your client’s logo.
For the purposes of this post, let’s pretend that your new logo client is opening a dog grooming business in New York City.

Note the following:

• Seal-shaped logo would work well across all channels (especially social).
• Simple and clear graphics can easily be repurposed for favicon or standalone pictorial mark.
• Additional info and strong design choices lend credibility to the brand.

Note the following:

• Cute design uniquely plays on the NYC skyline.
• Color palette is striking enough where it’ll stand out no matter how big or small it appears (like in the favicon).
• The geometric design and strong sans serif font suggest stability within the brand.

Iffy color choice aside, note the following:

• The negative space design is unique and works well with the designer’s use of the recognizable Scottish Terrier dog breed.
• The star is a good choice as it can be used on any channel and would be instantly recognizable.
• The star symbol is a nice way to give this salon an air of luxury (like a VIP club).

Based on the information provided by your client, it might also be beneficial to look outside their industry for inspiration — especially if they’re looking up to titans of other industries. By starting with the competition though, you’ll get a good idea of what the logos and brands all have in common (if anything).
For example, in this particular space, we see a lot of pictorial logos with animal-related images. But in terms of colors, fonts and even shapes, the personality of the business and the unique selling proposition have led to wildly different designs.
As you survey existing logo designs, consider how closely you want your logo design to align with or how far it should deviate from them. At this point, you know your client fairly well, so when you see something that’s more in the vein of what you’re trying to do (or not do), you’ll immediately know it.

Tip #3: Find The Right Tool To Create Your Logo

When it comes to designing a logo for your client, you should be using a tool that makes your job easier. But that “job” really depends on what your client is paying and what their expectations are.

For Pre-made Logo Design

Are you going to compile a batch of premade logos — personalized with their info, of course — and let them choose the one they like best?
In that case, a free logo maker tool would work fine. So long as there’s a variety of logo styles to choose from in your client’s niche, you should be able to find plenty of designs to share with them.
Best for:

• Very small budgets;
• Really short timelines (i.e. a day or two);
• Temporary logos.

For Custom Logo Design

Are you planning to create 10 custom logos and run through series after series of “Which do you like?” questions until you whittle it down to one winning design?
In that case, you’ll want to design it with your Adobe Illustrator or Sketch software. The process will take a while, but if your client has the money to spend and wants something truly unique, this is the best way to do it.
Best for:

• Price is no object,
• Client envisions something very specific,
• Enterprise clients in very high-profile positions who can’t afford to have their logo look like anyone else’s.

The Best Of Both Worlds (Custom Design Without The Hassle)

Would you prefer something between the two options above, where you could wield your creative influence to create something custom without all of the hassle?
In that case, the Logo Maker that Wix offers is your best bet. You can use this tool for a wide range of clients — those that want something done quickly and cheaply all the way up to clients who want a personalized logo.
Best for:

• Any budget;
• Any timeline;
• Logos you don’t want to spend too much time designing graphics for, but still want to be able to customize the key elements (like colors and layout).

Why Is the Wix Logo Maker The Right Tool For You?

Similar to the Wix site builder platform, Wix Logo Maker aims to make light work of what would otherwise be time-consuming and tedious work.
It’s an easy, intuitive way to design the perfect logo for your client and spares you the trouble of having to start from scratch while still creating something custom (if that’s what you need).
Let’s take a closer look.

The Logo Questionnaire

Similar to the client questionnaire I recommended you create above, Wix takes you through a series of questions as well.


Here are some things to think about as you work on your logo:

Colors

There’s more to color selection than how good it looks. Color comes with a lot of meaning and it can have an effect on customers’ emotional responses as well. It also can impact the readability and accessibility of your site. So, before you jump to using any particular set of colors, brush up on color theory and make sure you have the right ones picked out.
Although this is something you’re probably thinking about from the beginning, it might be a good idea to save it to the end after you’ve dealt with the structure of the logo.

Typography

If the company name and/or slogan is to appear in the logo, typography is something you’ll need to spend a lot of time considering. Again, this isn’t just an aesthetic choice. It can have a serious impact on memorability, emotionality, and accessibility.

Iconography

Icons can do a lot of good for logos. They can add balance. They can provide insight into what the business is about if the name is obscure. They can bring more personality to the design. They can also serve as a stand-in in smaller spots, like the browser favicon or as a social media profile image.
What’s nice about this Logo Maker is that you don’t just get to personalize the icon, but also the shapes within or behind the logo. So, you can play with depth, geometry and layout when designing your logo as well.

You have two options:

• Download (or print) all logo PNG files, resizable SVGs, and 40+ social media logos.
• Get the standard logo PNG files in a range of colors.

As you can see, everything’s been thought of. So, it’s less time you have to worry about exporting your logos in the right file formats, sizes, and colors. You just need to focus on creating something your client will love.

The Value Of A Well-Designed Logo

A lot of a brand’s success hinges on how great their logo design is. So, of course, you want to design a professional and modern logo that will serve your clients well. By taking the time to learn about your client’s business, creating a design around their vision, and then using the right tool to bring it all together, you’ll be able to do just that.

Oracle Set Operators

 

About 

The set operators are used to combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

Setting up sample tables

We created two new tables departments and employees for the demonstration using Sample-tables.sql script. The SQL script is under followed.

-- Create DEPARTMENTS table
CREATE TABLE DEPARTMENTS (
  DEPARTMENT_ID   NUMBER(2) CONSTRAINT DEPARTMENTS_PK PRIMARY KEY,
  DEPARTMENT_NAME VARCHAR2(14),
  LOCATION        VARCHAR2(13)
);

-- Create EMPLOYEES table
CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID   NUMBER(4) CONSTRAINT EMPLOYEES_PK PRIMARY KEY,
  EMPLOYEE_NAME VARCHAR2(10),
  JOB           VARCHAR2(9),
  MANAGER_ID    NUMBER(4),
  HIREDATE      DATE,
  SALARY        NUMBER(7,2),
  COMMISSION    NUMBER(7,2),
  DEPARTMENT_ID NUMBER(2) CONSTRAINT EMP_DEPARTMENT_ID_FK REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);

-- Insert data into DEPARTMENTS table
INSERT INTO DEPARTMENTS VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPARTMENTS VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPARTMENTS VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPARTMENTS VALUES (40,'OPERATIONS','BOSTON');

-- Insert data into EMPLOYEES table
INSERT INTO EMPLOYEES VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMPLOYEES VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMPLOYEES VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMPLOYEES VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMPLOYEES VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMPLOYEES VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMPLOYEES VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMPLOYEES VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMPLOYEES VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMPLOYEES VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMPLOYEES VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMPLOYEES VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMPLOYEES VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMPLOYEES VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

UNION

The UNION set operator returns all distinct rows selected by either query. That means any duplicate rows will be removed.

In the example below, notice there is only a single row each for departments 20 and 30, rather than two each.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID <= 30
UNION
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING
           20 RESEARCH
           30 SALES
           40 OPERATIONS

4 rows selected.

UNION ALL

The UNION ALL set operator returns all rows selected by either query. That means any duplicates will remain in the final result set.

In the example below, notice there are two rows each for departments 20 and 30.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID <= 30
UNION ALL
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING
           20 RESEARCH
           20 RESEARCH
           30 SALES
           30 SALES
           40 OPERATIONS

6 rows selected.

INTERSECT

The INTERSECT set operator returns all distinct rows selected by both queries. That means only those rows common to both queries will be present in the final result set.

In the example below, notice there is one row each for departments 20 and 30, as both these appear in the result sets for their respective queries.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID <= 30
INTERSECT
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           20 RESEARCH
           30 SALES

2 rows selected.

MINUS

The MINUS set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator EXCEPT DISTINCT.

In the example below, the first query would return departments 10, 20, 30, but departments 20 and 30 are removed because they are returned by the second query. This leaves a single rows for department 10.

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID <= 30
MINUS
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING

1 row selected.

Note: The ORDER BY clause is applied to all rows returned in the final result set. Columns in the ORDER BY clause can be referenced by column names or column aliases present in the first query of the statement, as these carry through to the final result set. Typically, you will see people use the column position as it is less confusing when the data is sourced from different locations for each query block.

SELECT EMPLOYEE_ID, EMPLOYEE_NAME
FROM   EMPLOYEES
WHERE  DEPARTMENT_ID = 10
UNION ALL
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM   DEPARTMENTS
WHERE  DEPARTMENT_ID >= 20
ORDER BY EMPLOYEE_ID;

EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
         20 RESEARCH
         30 SALES
         40 OPERATIONS
       7782 CLARK
       7839 KING
       7934 MILLER

6 rows selected.

Restricted products, don’t let them blow up your Amazon account.

 

When you’re a third-party Amazon seller, it’s easy to make poor assumptions on restricted products.

For example, you might assume that you can sell anything that you can find on Amazon.com. Or, you might assume that you can sell all legal products. However, there are restricted products you need to be aware of.

Both of these assumptions would be quite false:

• Don’t fall into the trap of uploading large catalogs for drop-shipping. These might include prohibited – or even illegal – items for sale.
• Just because it’s legal, doesn’t make it ok. For example, vaping accessories cannot be sold on Amazon. Nor can many supplements – even some you might find at the local drug store.
• Amazon reserves some dangerous items for itself. Amazon.com retail sells tasers, but it doesn’t allow its third-party sellers to list them.

Turning Mundane Subjects Into Exciting Visual Communication

Years ago, I wished I could work on advertising projects for household names because I thought that above-the-line work would bring creative satisfaction. I’ve been lucky to work with many well-known businesses and charities, but looking back, my smaller projects were the most satisfying creatively.
Often, big brands have already established guidelines which mean there’s less room for me to experiment and exercise my creative muscles. I’m not saying brand guidelines are unimportant, but I prefer to work on projects where I feel I add the most value and a little of myself.
These days, product companies seem more interested in refining interfaces and simplifying user experiences. I value those things when I use a product, but I find working on these projects less rewarding. Well-known clients still have a certain allure — and having logos in my portfolio has been good for business — but I now look for projects which offer me the freedom to develop my creative interests.
I’m fascinated by how design can tell engaging stories about products and services, even those which might be considered mundane by some. I enjoy exploring how images, layout, and typography can be used to communicate messages in visually distinctive ways. Above all, I love using my experience and interests in art direction and graphic design to help businesses, charities, and sometimes individuals, who might otherwise be exposed to them.

“I do not attempt to speak on behalf of the machines. Instead, I have tried to make them speak for themselves, through the graphic presentation of their elements, their operations and their use.”

— Giovanni Pintori

Even highly regarded, well-known designers spent time working with mundane subjects and produced iconic work. After moving from Switzerland to the United States, Erik Nitsche for magazines including Harper’s Bazaar, Life, and Vanity Fair. But it’s his work for General Dynamics which became his most recognized. In his five years as an art director at the aerospace and defence company, Nitsche developed an information design system which resulted in annual reports, posters, technical data, and Dynamic America, a 420-page book tracing the company’s history.
Italian designer Giovanni Pintori worked for business products manufacturer Olivetti for 31 years where the simple style and geometric shapes he applied to advertisements, calendars, and posters developed into the company’s design vocabulary.