Experimenting with humans and elfs

Everybody its on holiday, just arrived and It seems like I am the only muppet at the office today.
Oh, cool some body left a designs patterns book on the desk. Hehehe...

I guess it was that communist elf again.  See? that little man even forgot his hat!  


Wait, hold on a sec... ARGGGHHH...! WednesdayThursdayFriday!! My banana case its empty?????? The elf ate my banana!!!

Ok let's calm down its just a banana, the poor guy had to go back to the north pole so probably toke it for the way back.

Anyway, what I do to day?  Let's see:

- Pile of tech debt waiting for a miracle. 
    Uhm... I wonder if Jesus would be able to unblock me if I get started with any of this today.

- Second pile of tech debt. 
     Mayyybe, but wait! there are no story numbers there, better I don't mess up with the process ;)

- Swarm with the tester that just arrived. 
     Regression testing you said?? Yes, yes... sure, all the suffering will end one day, yep... Ok lets continue this conversation next week.

- Pick new story
     Opsy, board is empty! No BA's around, Well, I better mind my own business then.



All right! let's have a look at this book the elf left for me. 

Strategy, Template, Builder... Wow so many patterns which one I read about?

Oh screw the reading!, lets just do some coding. 

Ok, what I code about? Its xmas, let's think about some xmas related code...
...

Oh screw that too, lets just do some human experimentation or something. 

Yes, thats it! Let's experiment with humans! No, no better, let's experiment with elfs hahaha.... 
Im so evil sometimes!

So, what about this one?: Imagine I catch that elf and I torture him in a machine that plays christmas carols infinitely and I log all his reactions, like if it was a proper scientific research.

Mmmm... sounds like an events driven system to me. 

Ok! 

So let's refresh my mind, how did I do events in Java some time ago? 

Let's spike that a little bit for now, this book may be handy I will keep it close.


I said I would create a person, and I would put it in a machine and see how it reacts to the sounds the machine plays. In order to react, to sounds, I better make sure that person has ears(the listener). 

public class Main {
    public static void main(String[] args) {

        SomeListener ears = new Ears();

        Person person = new Person();
        person.addListener(ears);

        SoundsMachine soundsMachine = new SoundsMachine(person);
        soundsMachine.generateSound(new RainSound());
        soundsMachine.generateSound(new CreepyDoorKnobSound());

    }

}

Ok, cool. I remember now. 

So the important thing here its the person because for it to react, it will need to have different types of listeners. But those listeners, may react to specific events only. Well, the events could also be generic something like an interface(function pointer).

Yes, let's keep it simple. I think that is better since the specifics of each reaction could be programmed separately in each different listener...

public class Person {

    private List<SomeListener> listeners = new ArrayList<SomeListener>();

    public void addListener(SomeListener someListener) {
        listeners.add(someListener);
    }

    public void noticeWhatItsGoingOn(SoundEvent soundEvent) {
        for (SomeListener someListener: listeners) {
            someListener.perceive(soundEvent);
        }
    }
}

Fine, so let's then look closer at the ears listener Im using in this example.

public class Ears implements SomeListener {

    public void perceive(SoundEvent soundEvent) {
        System.out.print("Uhm, I hear something: ");
        soundEvent.produceSound();
        System.out.println("I think its " + determineSound(soundEvent) + "!");
        lineSeparator();
    }

    private String determineSound(SoundEvent soundEvent) {
        if (soundEvent instanceof RainSound)
            return "rain";
        else if(soundEvent instanceof CreepyDoorKnobSound) {
            return "a door opening. Hopefully I will be out of here soon";
        }
        return "I can't recognize that sound";
    }
}

So that implements the Listener interface which is basically a functional interface.

public interface SomeListener {
    public void perceive(SoundEvent soundEvent);
}

And that's it then the only thing its just to have a look at the implementation of the events that are sent.

public interface SoundEvent {
    public void produceSound();
}

public class RainSound implements SoundEvent {

    public void produceSound() {
        System.out.println("plop, plop, plop....");
    }
}

public class CreepyDoorKnobSound implements SoundEvent {
    public void produceSound() {
        System.out.println("KnJjjeeeeee...");
    }
}

I will sketch this to summarise:

The listening object(Person) is used by the application code which is what uses it and also where the action of registering the listeners its done(Main and SoundMachine). 

Also that listening object holds all the listeners in a list and acts as a gateway, to where the events are sent (noticeWhatItsGoingOn() method).

This was Javing's last post of 2015

Merry Xmas and prosperous new Year!

                              

When it’s the 5th one who tells me ‘see you next year’ when leaving the office

Consensus decision making

I was just tidying up room, moving things here and there and in an old shoe box I found an old paper script I had for long time. It's  around 25 pages,  a good friend of mine gave it to me time ago and told me to read it carefully. I did read it long ago, I was reading it again tonight and I thought that It would be interesting to just share some parts of it in the blog.
Is not related to software at all, but it describes in great detail the process of consensus decision making which I consider an extremely important thing to understand by every person in a self-directed team or organisation.

As you probably know, the "Agile Manifesto" in one of its clauses says that the best work its done by self directed teams. Unfortunately in many organisations today "Agile" is just a buzz word and doesn't go beyond the developers desk...

So without saying much more, I will just paste here some parts of that text, which I hope you find interesting and also help you get a grasp of what it means to decide in consensus.

CONSENSUS DECISION MAKING

Consensus decision making is a creative and dynamic way of reaching agreement between all members of a group. Instead of simply voting for an item and having the majority of the group getting their way, a consensus group is committed to finding solutions that everyone actively supports – or at least can live with. This makes sure that all opinions, ideas and concerns are taken into account. By listening closely to each other, the group aims to come up with proposals that work for everyone. Consensus is neither compromise nor unanimity – it aims to go further by weaving together everyone’s best ideas and most important concerns – a process that often results in surprising and creative solutions, inspiring both the individual and the group as whole.

At the heart of consensus is a respectful dialogue between equals. It’s about everyone working together to meet both the individual’s and the group’s needs – working with each other rather than for or against each other, something that requires openness and trust. Consensus is looking for ‘win-win’ solutions that are acceptable to all - no decision will be made against the will of an individual or a minority. Instead the group adapts to all its members’ needs. If everyone agrees to a decision they will all be much more committed to making it happen. Consensus decision making is based on the idea that people should have full control over their lives and that power should be shared by all, not just concentrated in the hands of a few. It’s about having the freedom to decide one’s own course in life and the right to play an equal role in creating a common future. This is why it is used widely in groups working towards a more just and equitable society such as small voluntary groups, co-operatives and campaign networks.

CONDITIONS FOR CONSENSUS

Common Goal: Everyone in the group needs to share a clear common goal and be willing to work together towards it. Work out together what your goals are and how you will get there. If differences arise later, revisit the common goal to help to focus and unite the group. 

Commitment to reaching consensus: Everyone needs to be willing to really give it a go. This means being deeply honest about what it is you want or don’t want, and properly listening to what others have to say. Everyone must be willing to shift their positions, to be open to alternative solutions and be able to reassess their needs. 

Trust and openness: We need to be able to trust that everyone shares our commitment to consensus and that everyone respects our opinions and equal rights. It would be a big breach of trust for people to manipulate the process of the meeting in order to get the decision they most want. Part of this is to openly express both our desires (what we’d like to see happening), and our needs (what we have to see happen in order to be able to support a decision). If everyone is able to talk openly then the group will have the information it requires to take everyone’s positions into account and to come up with a solution that everyone can support.

Sufficient time: for making decisions and for learning to work by consensus. Taking time to make a good decision now can save wasting time revisiting a bad one later. 

Clear Process: It’s essential to have a clear process for making decisions and to make sure that everyone has a shared understanding of how it works. 

Active participation: In consensus we all need to actively participate. We need to listen to what everyone has to say, voice our thoughts and feelings about the matter and pro-actively look for solutions that include everyone.

Good facilitation: When your group is larger than just a handful of people or you are trying to make difficult decisions, appoint facilitators to help your meeting run more smoothly. Good facilitation helps the group to work harmoniously, creatively and democratically. It also ensures that the tasks of the meeting get done, that decisions are made and implemented. If in a small group, you don't give one person the role of facilitator, then everyone can be responsible for facilitation. If you do appoint facilitators, they need active support from everyone present.

KEY SKILLS FOR CONSENSUS

Active Listening: When we actively listen we suspend our own thought processes and give the speaker our full attention. We make a deliberate effort to understand someone’s position and their needs, concerns and emotions. 

Summarising: A succinct and accurate summary of what’s been said so far can really help a group move towards a decision. Outline the emerging common ground as well as the unresolved differences: “It seems like we’ve almost reached agreement on that bit of the proposal, but we need to explore this part further to address everyone’s concerns.” Check with everyone that you’ve got it right.

Synthesis: Find the common ground and any connections between seemingly competing ideas and weave them together to form proposals. Focus on solutions that address the fundamental needs and key concerns that people within the group have. 

FACILITATION

In most meetings, there are one or more facilitators. Their role is to ensure that the tasks of the meeting get done: that decisions are made and implemented. They also help the group to work harmoniously, creatively and democratically. The facilitators might take steps to keep the meeting focused, or make sure a few people don’t dominate the discussion. They might suggest a break when people are getting tired; they might have prepared an agenda and process that will help the group achieve its goals. The facilitators shouldn’t have any more power than anyone else and should stay neutral on the issues under discussion. They’re not there to make all the proposals and decide things for a group. They can only do their job with everyone’s support and co-operation. If a small group doesn’t give anyone the role of facilitator, then everyone can be responsible for making the process of the meeting work.

GUIDELINES

• If you don’t understand something, don’t be afraid to say so.
  
•  Be willing to work towards the solution that’s best for everyone, not just what’s best for you. Be flexible and willing to give something up to reach an agreement.
  
• Help to create a respectful and trusting atmosphere. Nobody should be afraid to express their ideas and opinions. Remember that we all have different values, backgrounds and behaviour and we get upset by different things.
  
•  Explain your own position clearly. Be open and honest about the reasons for your view points. Express your concerns early on in the process so that they can be taken into account in any proposals.
  
• Listen actively to what people are trying to say. Make an effort to understand someone’s position and their underlying needs, concerns and emotions. Give everyone space to finish and take time to consider their point of view.
  
• Think before you speak, listen before you object. Listen to other members’ reactions and consider them carefully before pressing your point. Self restraint is essential in consensus – sometimes the biggest obstacle to progress is an individual’s attachment to one idea. If another proposal is good, don’t complicate matters by opposing it just because it isn’t your favourite idea! Ask yourself: “Does this idea work for the group, even if I don’t like it the best?” or “Does it matter which one we choose?”
  
• Don’t be afraid of disagreement. Consensus isn’t about us all thinking the same thing. Differences of opinion are natural and to be expected. Disagreements can help a group’s decision, because with a wide range of information and opinions, there is a greater chance the group will find good solutions. Easily reached consensus may cover up the fact that some people don’t feel safe or confident enough to express their disagreements.

AGREEMENT AN DISAGREEMENT

At the decision stage people have several options:

Agreement with the proposal. 

Reservations: You are willing to let the proposal go ahead but want to make the group aware you aren’t happy with it. You may even put energy into implementing it once your concerns have been acknowledged.

Standing aside: You want to object, but not block the proposal. This means you won’t help to implement the decision, but you are willing for the group to go ahead with it. You might stand aside because you disagree with the proposal, or you might like the decision but be unable to support it because you don’t have the time or energy. The group may be happy to accept the stand aside and go ahead, or they may work on a new proposal, especially if there are several stand asides. 

A block: always stops a proposal from going ahead. It expresses a fundamental objection. It isn’t “I don’t really like it,” or “I liked the other ide a better.” It means that you cannot live with the proposal. The group can either start work on a new proposal, or look for amendments to overcome the objection.

 In an ideal consensus process a block wouldn’t happen since any major concerns about a proposal should be addressed before the decision stage. However, sometimes people aren’t able to express their concerns clearly enough, or aren’t heard by the group. In such situations the block acts as a safeguard to ensure that decisions are supported by everyone.

 Being able to block is an integral part of consensus, but it comes with a big responsibility. A block stops other people from doing something that they would like to do, and it should therefore only be used if serious concerns are unresolved.

 Make sure everyone understands the different options for expressing disagreement. Often people are confused and block when they’d actually be happy to stand aside. Sometimes people are scared of blocking, even if they are deeply unhappy and use a milder form of disagreement instead. 

THE WORKFLOW OF CONSENSUS

There are many different ways of reaching consensus.

This model outlines the common stages and will work well with up to about 20 people. 

Step 1: Introduce and clarify the issue(s) to be decided Share relevant info. What are the key questions? 

Step 2: Explore the issue and look for ideas. 

• Gather initial thoughts and reactions. What are the issues and people's concerns? 
•  Collect ideas for solving the problem write them down. 
• Have a broad ranging discussion and debate the ideas. What are the pros and cons? Start thinking about solutions to people's concerns. Eliminate some ideas, shortlist others. 

Step 3: Look for emerging proposals Look for a proposal that weaves together the best elements of the ideas discussed. Look for solutions that address people’s key concerns. 

Step 4: Discuss, clarify and amend your proposal Ensure that any remaining concerns are heard and that everyone has a chance to contribute. Look for amendments that make the proposal even more acceptable to the group. 

Step 5: Test for agreement Do you have agreement? Check for the following: 

• Blocks: I have a fundamental disagreement with the core of the proposal that has not been resolved. We need to look for a new proposal. 
• Stand asides: I can't support this proposal because ... But I don't want to stop the group, so I'll let the decision happen without me. 
• Reservations: I have some reservations but am willing to let the proposal pass. 
• Agreement: I support the proposal and am willing to implement it. 
• Consensus: No blocks, not too many stand asides or reservations? Active agreement? Then we have a decision!

If test for agreement fails, go back to Step 3

Step 6: Implement the decision Who, when, how? Action point the tasks and set deadlines.

This text in my opinion is really good, I just pasted there some of the key important things but there are so much more to it such as: how to deal with disruptive behavoir, larger groups, quick tips for emergency decision making and much more... 

Being capable of consensus decision making is probably one of the greater skills a team or a company can have. If you are interested on the topic, I would suggest you to search online on the topic, because there are plenty of stuff. A true agile team is transparent, has an spirit of shared ownership and responsibility and is capable of self organising. 

Selenium automation design pattern - The Journey Pattern

Browser automation is becoming more and more popular. Many people use it today: IT companies, automation testers, hackers, etc ...

Its possible to do lots of things with it; testing a website, scrap some latest changes, social media bots, and much more...(I stop there, Im not wearing my black hat today, no worries ;) )

You probably know, that there is a tool out there called Selenium for those who like Java and want to do browser automation. As per what I've seen so far from working in large companies, automation tasks are not always done by programmers, more and more often testers are writing pseudo-java code to do this tasks.

For the purpose of testing, Selenium code should always be treated in the same way as every other Java code. By this I mean, that clean coding and Object Oriented principles are still important.
A clean and understandable test harness is extremely important for being able to scale automation testing.


Unfortunately testers that are given automation tasks often lack the Object Oriented background that Java developers have and unless the company is willing to provide the proper programming training or the task is actually done by a programmer alongside, the chances of ending up with an unmaintainable UI automation test harness are high.


When it comes to using selenium for browser automation, the most widely used pattern is the PageObject. Basically page objects are just classes that represent each screen in the webapp. The PageObject its a good pattern that its being around for long time, but unfortunately the practice shows us that following this patter we often create duplication and we are not always capable of reusing the page objects.

A colleague not too long ago, told me to have a look at the Journey Pattern.
The idea behind the Journey pattern is that the automation is done around journeys. Every journey is composed of actions such as Enter text into, Click...

So lets have a look at 2 sample journeys:

1 2 3 4 5 6 7 8 9 10 11 12

//Just preparing selenium WebDriverBackedSelenium selenium; WebDriver driver = new FirefoxDriver(); selenium = new WebDriverBackedSelenium(driver, START_URL); selenium.open(START_URL); selenium.waitForPageToLoad("2000"); // Journey 1 - to verify login works (assertions omitted) LogIn.as(driver, "myUsername", "myPassword"); // Journey 2 - write a comment on the news headlines WriteComment.in(driver, HeadlinesPage.commentBox);

The driver is passed around and its state is modified during each journey. In this example, we choose to create 2 separate journeys, where one Journey starts when the other ends. Since we pass the driver around, we don't need to worry about the user not being logged in before writing a comment(DRY). Each journey is composed of different action, and their components may be reusable across journeys:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

//Login is the Journey public class LogIn { public static void as(WebDriver driver, String username, String password) { Enter.textInto(driver, username, LoginScreen.usernameBox); Enter.textInto(driver, password, LoginScreen.passwordBox); Click.on(driver, LoginScreen.loginButton); } } //Write a comment is a Journey public class WriteComment { public static void in(WebDriver driver, By commentBox) { Enter.textInto(driver, "test comment", commentBox); Click.on(driver, HeadlinesPage.commentButton); } }

Each of this Journeys as you can see reuse actions, such as Click, Enter, etc ...

1 2 3 4 5 6 7 8 9 10 11

public class Enter { public static void textInto(WebDriver driver, String text, By selector) { driver.findElement(selector).sendKeys(text); } } public class Click { public static void on(WebDriver driver, By selector) { driver.findElement(selector).click(); } }

And finally, the actual pages just contain selectors to get access to the elements of the page:

1 2 3 4 5 6 7 8 9 10 11

public class LoginScreen { public static By usernameBox = By.cssSelector("#username"); public static By passwordBox = By.cssSelector("#password"); public static By loginButton = By.cssSelector("#loginButton"); } public class HeadlinesPage { public static By commentBox = By.cssSelector("#commentBox"); public static By commentButton = By.cssSelector("#commentButton"); }

The journey pattern is very simple to learn, flexible and also scalable. If you are a tester with some notions of Java and Selenium, play with it, give it a try,

Using pictures in your Acceptance Tests - (Yatspec + Selenium tip)

 Today's article mainly dedicated to testers that often work with selenium and would like to learn how to add pictures to their selenium acceptance tests.

Some time ago I posted an article about an acceptance testing framework called Yatspec(Yet Another Test Specification) which is becoming very popular among the Java community: http://javing.blogspot.co.uk/2015/03/yet-another-blog-article-about.html
It is definitely worth researching deeper into this framework because it offer lots of interesting options to make beautiful live specifications.

The example I will present its very trivial and does not cover how to write an acceptance test, it just covers how to add images to an already existing Yatspec test.

Imagine that you had a method that whenever you called it at any point in the test, it takes an screen shoot for you and adds it to the report.

In order to demostrate, how it works, I created a class called GuiSeleniumAndYatspecTest that contains all the necessary Yatspec code and also a way of taking screen shoots.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

@RunWith(SpecRunner.class) public class GuiSeleniumAndYatspecTest extends TestState implements WithCustomResultListeners { private static final String START_URL = "http://www.google.com"; protected WebDriver driver; protected static WebDriverBackedSelenium selenium; @Before public void setupSelenium() throws InterruptedException { driver = new FirefoxDriver(); selenium = new WebDriverBackedSelenium(driver, START_URL); selenium.open(START_URL); selenium.waitForPageToLoad("2000"); } @After public void closeSelenium() throws Exception { selenium.stop(); } public void takeScreenshoot() throws Exception { String imageData = selenium.captureScreenshotToString(); this.capturedInputAndOutputs.add(ScreenshootHolder.INTERESTING_GIVENS_KEY, new ScreenshootHolder(imageData)); System.out.println("Captured screen shoot"); } @Override public Iterable<SpecResultListener> getResultListeners() throws Exception { String testName = this.getClass().getSimpleName(); return Arrays.asList((SpecResultListener) new HtmlWithScreenshootResultListener(testName)); } }

There are some interesting things to notice in this class which will enable the usage of images in the yatspec acceptance test. Let's have a look at each:

• The takeScreenshoot() method, does 2 things, the first its to transform an image taken via selenium into an String, and then passing that String to a class called ScreenshootHolder, that will transform it into a byte[] to be understood by Yatspec.

1 2 3 4 5 6 7 8 9 10 11 12 13

public class ScreenshootHolder { public static final String INTERESTING_GIVENS_KEY = "Screenshot"; private final String base64PngDataString; public ScreenshootHolder(String base64PngImageData) { this.base64PngDataString = base64PngImageData; } public byte[] getPngImageData() { return org.apache.commons.codec.binary.Base64.decodeBase64(base64PngDataString); } }

• Notice that the test case is implementing an interface called withCustomResultListener.
  By using this interface, Yatspec will force us to override the method getResultListeners(), which is a mechanism to pass to Yatspec all the custom implementations for things we want our test to do, such as recoding images. The important thing in that method, is the usage of the class HtmlWithScreenshootResult, which is the implementation we need to create, to support the screenhoots.
  
  

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  public class HtmlWithScreenshootResultListener implements SpecResultListener { private final ScreenshootRenderer screenShotRenderer; private HtmlResultRenderer delegate; public HtmlWithScreenshootResultListener(String testName) { delegate = new HtmlResultRenderer(); screenShotRenderer = new ScreenshootRenderer(testName); delegate.withCustomRenderer(ScreenshootHolder.class, screenShotRenderer); } @Override public void complete(File yatspecOutputDir, Result result) throws Exception { screenShotRenderer.setYatspecOutputDir(yatspecOutputDir); delegate.complete(yatspecOutputDir, result); } }

• Finally the Listener from before uses is an SpecResultListener, by implementing this interface, we can reach the output directories and the test result, via the complete(). The class ScreenshootRenderer, needs to be created to be able to set the output directory of the test and write the images to the temporary folder in the file system.
  
  

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

  public class ScreenshootRenderer implements Renderer<ScreenshootHolder> { private File yatspecOutputDir; private String testName; public ScreenshootRenderer(String testName) { this.testName = testName; } @Override public String render(ScreenshootHolder screenshootHolder) throws Exception { if (yatspecOutputDir == null) { throw new IllegalStateException("You must use screenshootssupport.HtmlWithScreenShootResultListener in your test to use ScreenShotRenderer"); } else { String imageFilename = yatspecOutputDir + File.separator + getImageName(); try (FileOutputStream fos = new FileOutputStream(imageFilename)) { fos.write(screenshootHolder.getPngImageData()); } System.out.println("Rendered screenshot to " + imageFilename); return String.format("<div class='nohighlight'><img src=\"%s\" alt=\"%s\"></img></div>", imageFilename, imageFilename); } } public void setYatspecOutputDir(File yatspecOutputDir) { this.yatspecOutputDir = yatspecOutputDir; } protected String getImageName() { String timestamp = Long.toString(System.currentTimeMillis()); return String.format("test-%s-%s.png", testName, timestamp); } }

    

It is useful to organise this implementations in a separate package, since they are specific just to the capture of images.


The acceptance tests now will display screenshots as a captured input or output:

You can download this code examples from: https://github.com/SFRJ/yatspecscreenshoots

Queries to explore a database you don't know much about - Oracle SQL quick ref 4

This is the last of the posts I will do for now on Oracle quick refereces.
I decided to write this 4 blog articles to save me time using Google in the future.  So this last one is a bit peculiar, there are just a bunch of SQL commands that can be very useful for exploring a new database you don't know much about.

-- Finds all the columns in those tables that contain the string ESS
-- in the table name and orders them alphabetically
SELECT table_name, column_name
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'MY_SCHEMA'
ORDER BY table_name;

-- Same as the previous one but includes more data and excludes the owner SYS
SELECT *
FROM ALL_TAB_COLS
WHERE table_name LIKE '%ESS%'
AND owner != 'SYS';

-- All the constraints for the tables in all schemas in the database
SELECT * 
FROM all_constraints;

-- All the constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA';

-- All the foreign key constraints for the tables in all an specific schema
SELECT * 
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R';

-- This finds the primary key of a table
SELECT UPPER(constraint_name) 
FROM all_constraints
WHERE constraint_type IN ('P', 'U')
AND UPPER(table_name) LIKE UPPER(:r_table_name)
AND UPPER(owner) LIKE 'MY_SCHEMA';
   
-- Using the previous command as input to the following, allows us to find all of the
-- children of a particular table(Have as FK the PK of their parent)
SELECT table_name, constraint_name, status, owner
FROM all_constraints
WHERE UPPER(r_owner) LIKE 'MY_SCHEMA'
AND UPPER(constraint_type) LIKE 'R'
AND UPPER(r_constraint_name) IN
 (
   SELECT UPPER(constraint_name) 
   FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(table_name) LIKE UPPER(:r_table_name)
   AND UPPER(owner) LIKE 'MY_SCHEMA'
 )
ORDER BY table_name, constraint_name;

--Find the parents of a table
SELECT * 
  FROM all_constraints
   WHERE constraint_type IN ('P', 'U')
   AND UPPER(owner) LIKE 'MY_SCHEMA'
   AND UPPER(constraint_name) in
   (
    SELECT UPPER(r_constraint_name)
    FROM all_constraints
    WHERE UPPER(table_name) LIKE UPPER(:r_table_name)
    AND UPPER(constraint_type) LIKE 'R'
   );
   
--Finding tables that contain a word in their name   
SELECT table_name, owner
FROM all_tables
WHERE UPPER(table_name) LIKE '%something%'; 

Subquery basics - Oracle SQL quick ref 3

A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. A sub-query is also called an inner query or inner select, while the statement containing a sub-query is also called an outer query or outer select.

Depending on the amount of results a sub-query returns, we distinguish 2 types of sub-queries:

• Single row sub-queries: Queries that return only one row from the inner SELECT statement
• Multiple row sub-queries: Queries that return multiple rows from the inner SELECT statement

In the following single sub-query example, the AVG function returns just one result which is used by the outer query.

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary;


In the next example a multiple row sub-query is used to return some departments which the outer query will use in its IN clause.

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
WHERE location_id=2897); 

It is important that both single row and multiple row sub-queries are correctly used in a context that either expects a single result or a multiple result respectively.
Its important to mention that the Oracle server will always execute sub-queries first.

Sub-queries can be used in the WHERE clause, but also in the HAVING clause.
This example returns job with the lowest average salary.

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                           FROM employees 
                                           GROUP BY job_id);


The difference between HAVING and WHERE, is that WHERE does not work with aggregated functions, such as AVG, COUNT...

Using Joins - Oracle SQL quick ref 2

NATURAL JOIN is a type of join that relies on both tables involved on the join to have a common column name. For example if an employee table has a foreign key called department_id and the departments table has a primary key called department_id, then it is possible to do a natural join because in both sides there is a column with the same name.

SELECT employee_id, last_name, department_id, department_name
FROM employees NATURAL JOIN departments;

Here another example, where both the locations and departments table, have location_id:

SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE department_id IN (20, 50);

There is a type of join, that uses the USING keyword. This Join, requires that the column that is used for the join is explicitly mentioned in the USING clause.

SELECT employee_id, last_name, location_id, department_id
FROM employees JOIN departments
USING (department_id);

When we use the USING keyword, we are allowed to use aliases if we want but the only limitation is that the WHERE clause cannot use aliases. For example, WHERE d.location_id... would not be valid.

SELECT l.city, d.department_name
FROM locations l JOIN departments d
USING (location_id)
WHERE location_id = 1400;


SELECT first_name, d.department_name, d.manager_id
FROM employees e JOIN departments d
USING (department_id)
WHERE department_id = 50;


The ON keyword is the most commonly used way of doing joins in this type of does not mandate that the columns in both sides have the same name, also there are no limitations in the usage of aliases in the WHERE clause.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id);


The JOIN clause has an AND part, which is equivalent to the WHERE clause, so WHERE is not needed but can also be used along side.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
AND e.manager_id=149;


SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
WHERE e.manager_id=149;

It is possible to have as many JOIN clauses as desired in a query.

SELECT e.last_name, e.job_id, e.department_id, d_depatment_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
WHERE l.city = 'London';

Sometimes its necessary to perform self joins. In this example a non normalized table of employees, has a row to represent that the worker is a manager. So if we wanted to see who is the manager of who, we would have to do something like this.

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker
JOIN employees manager ON (worker.manager_id = manager.employee_id);

Also in SQL, there are other types of joins called OUTER and INNER  that are useful when we want to widen or narrow the result set. INNER JOIN  will only return rows, if both tables in the join have associated data in the other side of the join. OUTER JOIN is not that strict, they will still return rows even if they have no associated data in the other side of the join. While there is one type of INNER JOIN, the OUTER JOIN cam come in 3 flavours(LEFT,RIGHT, FULL).

The LEFT OUTER JOIN will include all employees and their department name even if they don't have an associated department.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

The RIGHT OUTER JOIN will include all the departments that are associated with employees, but also all the departments that are not associated with employees at all.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);


The FULL OUTER JOIN will return all the departments even if they are not associated with employees and also all the employees even if they are not associated with departments.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);


The INNER JOIN will return all results that have employees with departments and all departments with employees.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e  INNER JOIN departments d
ON (e.department_id = d.department_id);

Grouping data - Oracle SQL quick ref 1

Oracle SQL group functions operate on sets of rows to give one result per group of inputs(In other words, many inputs, one output): AVG,COUNT,MAX,MIN,SUM...

SELECT MAX(salary) max, MIN(salary) min, SUM(salary) sum, ROUND(AVG(salary),0) avg
FROM employees;

SELECT COUNT(*) numberOfEmployees
FROM employees;

Sometimes the rows of data, may contain duplications that we don't want to be taken into account.
Then the DISTINCT keyword, can be useful to suppress the duplicates.

SELECT COUNT(DISTINCT department_id)
FROM employees

By default group functions will ignore null values but if we want to not ignore then, we should use the NVL function.

SELECT NVL(AVG(commission_pct))
FROM employees;

It is possible in sql to select different groups/sub sets of data, from one same table, this is know as grouping. The GROUP BY keyword allows us to divide the rows of a table into groups and optionally latter apply a group function to act upon each of those groups.
For example, we could get the average salary for each department using the group by function:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; 

Important!: If a SELECT statement contains a GROUP BY clause, all non aggregate functions(department_id) that are defined in the SELECT, need to also be in the GROUP BY clause.

When working with GROUP BY and we want to add additional restrictions to the groups, we are not allowed to use the WHERE clause, instead we must use the HAVING clause.
For example lets say that we want to see the departments and their max salaries, but only if the max salary of the department is greater than 10000:

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;