Note Creating a schema does not automatically make that schema the current default schema. If you have created a new schema and want your subsequent statements to execute within that schema, you must also execute the SET SCHEMA statement.
Example
-- Assume the userID is SMITH. CREATE SCHEMA FINANCE; CREATE TABLE EMPLOYEE (EMP_ID INTEGER); SET SCHEMA FINANCE; CREATE TABLE EMPLOYEE (ID INTEGER); SELECT ID FROM EMPLOYEE; -- In this case, the table is qualified to FINANCE.EMPLOYEE SELECT EMP_ID FROM EMPLOYEE; -- This will give an error as the context is with FINANCE and -- table is resolved to FINANCE.EMPLOYEE -- The following statements are valid schema statements: one with -- a schema context, the other without. SELECT ID FROM FINANCE.EMPLOYEE; SELECT EMP_ID FROM SMITH.EMPLOYEE -- The following statement will resolve to schema SMITH without -- a schema context SELECT EMP_ID FROM EMPLOYEE;