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;

Exposing the data layer of your app using REST

The more we sepparate the concerns of our system, the more mainteinable it becomes.

It is very common to find applications written in such way that the data access mechanisms(SQL files, JDBC client code, ORM mappings...) are located just next to(coupled/interdependant) the service/bussiness logic. This often makes finding bug, making a change, etc.. harder.

Calculating a result and storing it, are different things. So why not sepparating those 2 responsibilities among different applications?

One would be responsible of making sure the results are calculated and the other will just provide data management support.
In my opinion the result of doing this is a system that is more understandable, maintainable and upgrade friendly.

In many companies, the data is often managed by database engineering teams which have: schedules, goals and even different managers than the development teams. In this type of organization, delays, missunderstandings, conflicts of interests and work de-synchronization are very common. So to make the most of a decoupled system, we not just need a good software approach, but also a process and team structure that are compatible with it(But this may be a topic for another post). This type of decoupling will not just make maintenance easy for the developers but also, it will probably encourage discussion about the process and the teams structure.

In my example I decided expose 2 persistent services via 1 url and persisting simultaniously in 2 types of databases(a sql and a no-sql DB).

This is the implementation of the no-sql adapter

 public class NoSqlAddressInsertAdapter implements CreateService {  
   private final MongoClient mongoClient;  
   @Inject  
   public NoSqlAddressInsertAdapter(MongoClient mongoClient) {  
     this.mongoClient = mongoClient;  
   }  
   @Override  
   public void create(Address address) {  
     DBCollection collection = mongoClient.getDB("radadata").getCollection("address");  
     collection.insert(toNoSqlAddress(address));  
   }  
   private AddressNoSql toNoSqlAddress(Address address) {  
     AddressNoSql addressNoSql = new AddressNoSql();  
     addressNoSql.append("firstline", address.getFirstLine());  
     addressNoSql.append("secondline", address.getSecondLine());  
     addressNoSql.append("postcode", address.getPostcode());  
     addressNoSql.append("persons", address.getPersons().stream().map(toNoSqlPersons()).collect(toList()));  
     return addressNoSql;  
   }  
   private Function<Person, PersonNoSql> toNoSqlPersons() {  
     return person -> {  
       PersonNoSql personNoSql = new PersonNoSql();  
       personNoSql.append("firstname", person.getFirstName());  
       personNoSql.append("secondname", person.getSecondName());  
       return personNoSql;  
     };  
   }  
 }  

This is the implementation of the sql-adapter

 public class SqlAddressInsertAdapter implements CreateService {  
   @Inject  
   public SqlAddressInsertAdapter() {  
   }  
   private static SessionFactory getSessionFactory() {  
     return HibernateUtil.getSessionFactory();  
   }  
   private Session session;  
   @Override  
   public void create(Address address) {  
     session = SqlAddressInsertAdapter.getSessionFactory().getCurrentSession();  
     session.beginTransaction();  
     Set<ORMPerson> ormPersons = address.getPersons().stream().map(toOrmPersons()).collect(toSet());  
     ORMAddress ormAddress = new ORMAddress();  
     ormAddress.setFirstLine(address.getFirstLine());  
     ormAddress.setSecondLine(address.getSecondLine());  
     ormAddress.setPostcode(address.getPostcode());  
     ormAddress.setOrmPersons(ormPersons);  
     session.save(ormAddress);  
     session.getTransaction().commit();  
   }  
   @Override  
   public void create(Person person) {  
     //  
   }  
   private Function<Person, ORMPerson> toOrmPersons() {  
     return person -> new ORMPerson(person.getFirstName(),person.getSecondName());  
   }  
 }  

Note that both adapters use their specific domain objects, one uses ORM(Those ORMClasses are hibernate entities) and the other doesn't.

This is a sample REST endpoint will allow access to those services simultaniously

 @Service  
 @Path("insertperson")  
 public class InsertAddressResource {  
   private final services.nosqlcrud.CreateService noSqlcreateService;  
   private final services.sqlcrud.CreateService sqlCreateService;  
   @Inject  
   public InsertAddressResource(services.nosqlcrud.CreateService noSqlcreateService,  
                  services.sqlcrud.CreateService sqlCreateService) {  
     this.noSqlcreateService = noSqlcreateService;  
     this.sqlCreateService = sqlCreateService;  
   }  
   @POST  
   @Consumes({"application/json"})  
   public void insert(Address address) {  
     noSqlcreateService.create(address);  
     sqlCreateService.create(address);  
   }  
   /*  
     A Sample Json to POST:  
     URL: http://localhost:9998/insertperson  
     Content Type: application/json  
     {  
      "firstline": "street bla bla",  
      "secondline": "town of bla bla",  
      "postcode": "ble ble ble",  
      "persons": [  
         {"firstname":"Armin","secondname":"Josef"},  
         {"firstname":"Johan","secondname":"Uhgler"}  
       ]  
     }  
   */  
 }  

This snippets of code are just part of a demo app I wrote some days ago to show how to expose the data layer via REST.
The rest of the project, can be found at: https://github.com/SFRJ/Rest-Approach-to-Data-Persistence-R.A.D.A-