Questions in this section.

1. What is SQL? What are the different types of statements in SQL?
2. What tools are available for me to write SQL code?
3. How do I set the default editor for my SQL *Plus program?
4. What is glogin.sql used for?
5. How do I save output of a SQL statement to a file?
6. How do I spool SQL output in HTML format?
7. What are pseudocolumns?
8. What are joins? What are the different types of joins?
9. What are sub-queries?
10. What are the GROUP BY, ORDER BY and HAVING clauses used for?
11. What is DISTINCT clause used for?
12. What are the set operators available in Oracle SQL?

1. What is SQL? What are the different types of statements in SQL?
   • DML – Data Manipulation Language

   Example: INSERT INTO EMP (EMPNO, ENAME, SALARY, COMMISSION, DEPTNO) VALUES (2231, ‘RAMESH MENON’, 23122, 0, 20);

   • DDL – Data Definition Language
   • A Query

SQL Stands for Structured Query Language. It is used to write code that can be used to modify the data in the database. SQL statements could be any of the following types

A DML is used to modify, add, or remove data from database tables. INSERT, UPDATE and DELETE are the DML statements in SQL.

A DDL is used to change the structure of the database objects. For example, CREATE, DROP, COMMIT, ROLLBACK are all DDL statements. A commit; after the previous statement is an example.

A SELECT statement is an example of a query.

Example: SELECT ENAME, SALARY, COMMISSION
FROM EMP
WHERE DEPTNO=20;

2. What tools are available for me to write SQL code?

SQL can be written using any text editor (notepad, vi). However, to execute the statements, you have to connect to the database using client tools like SQL *Plus, Toad, SQL Developer etc.
SQL code can also be embedded inside other programming languages like Java, C, C++ or Perl. These programs connect to the database using the corresponding driver (for example Java uses JDBC, Perl Uses DBI)
Many DBAs, still prefer to use the SQL *Plus command line interface for their day to day administration activities.

3. How do I set the default editor for my SQL *Plus program?

There are different methods to set the default editor for SQL *Plus.

On Unix, set the environment variable EDITOR as export EDITOR=vi
On Unix and Windows, at SQL *Plus prompt
SQL> define_editor=notepad.exe (Windows)
SQL> define_editor=vi (Unix)

4. What is glogin.sql used for?
   • Serverouput is on
   • Pagesize is set to 24
   • Default editor is vi
   • SQL Prompt reflects the database instance I am connected to

glogin.sql and login.sql are configuration scripts that are executed when a user invokes SQL *Plus utility.

The glogin.sql file must be located in the $ORACLE_HOME/sqlplus/admin directory. The login.sql file could either be in the current
working directory or in a location specified in the SQLPATH environment variable.

These two files, can be used to configure the environment as well as the look and feel of the SQL *Plus interface.

The login.sql file overrides the glogin.sql when it exists.

For example, if I want to set the SQL *Plus environment to the following

 

 

I will set the glogin.sql as follows

set serveroutput on
set pagesize 24
define _editor=vi
set termout off
col instance new_value prompt_dbname
select instance_name instance from v$instance;
set sqlprompt “&&prompt_dbname> ”

5. How do I save output of a SQL statement to a file?

Use the SPOOL command as follows

SQL> SPOOL myfile.log
SQL> SELECT * FROM TRANSACTION_HEADER where TRANSACTION_TYPE=20;
SQL> SPOOL OFF

6. How do I spool SQL output in HTML format?

From SQL prompt

SQL> SET HTML MARKUP ON
SQL> Spool mylog.log
SQL> SELECT * FROM TRANSACTION_HEADER WHERE TRANSACTION_TYPE=20;
SQL> SPOOL OFF
Turn off HTML MARKUP if you don’t want all your output in HTML format.

7. What are pseudocolumns?

Pseudocolumns are columns which behave as part of the table, though it is not stored with the table. These columns are READ ONLY, and cannot be modified in any way.

There are different categories of Pseudocolumns. The most basic examples of pseudocolumns are ROWNUM and ROWID. The rest of the pseudocolumns will be covered in the advanced posts.

SQL> select rowid, rownum, deptno from DEPT;
ROWID ROWNUM DEPTNO
—————— ———- ———-
AAAQ+hAAEAAAAAOAAA 1 10
AAAQ+hAAEAAAAAOAAB 2 20
AAAQ+hAAEAAAAAOAAC 3 30
AAAQ+hAAEAAAAAOAAD 4 40

8. What are joins? What are the different types of joins?
   • Equi joins

   An equijoin combines the rows of two tables that have equivalent values for the specified columns. The two tables are joined using an equality operator “=”. It is also known as simple join or inner join.

   Example:
   SELECT E.ENAME, D.DNAME, D.DEPTNO, E.SALARY, E.HIREDATE
   FROM EMP E, DEPTNO D
   WHERE E.DEPTNO = D.DEPTNO;

   You can also use the INNER JOIN keyword as follows (This is the ANSI SQL format)
   SELECT E.ENAME, D.DNAME, D.DEPTNO, E.SALARY, E.HIREDATE
   FROM EMP E INNER JOIN DEPTNO D
   ON E.DEPTNO = D.DEPTNO;

   • Self joins

   In order to access related data within a table, you can use a SELF Join.
   The oldest example to explain this is the Employee-Manager Relation. When the employee and the managers details need to be listed in the same line, a SELF JOIN can be used as follows.

   SELECT E.ENAME, E.EMPNO, E.SALARY, E.ENAME, M.SALARY, M.HIREDATE
   FROM EMP E,
   EMP M
   WHERE E.MGR = M.EMPNO;

   • Cartesian Products

   When no join condition is specified, Oracle returns a cartesian product. For every row in one table, all the rows in the other table are returned. This can also happen when using a NATURAL join where there are no common columns

   Example
   SELECT * FROM EMP NATURAL JOIN ITEM_MASTER;

   • Inner joins

   Same as an EQUI-JOIN. Also called a simple join.

   • Outer joins
     • Left Outer Join

     A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table. Oracle will return NULL for the columns where no match is found.

     Example

     You want to list all the departments in the DEPT table and corresponding employee details. However, you want to also list those departments where there are no employees assigned yet. This can be done as follows

     SELECT D.DEPTNO, DNAME, ENAME, SAL, HIREDATE
     FROM DEPT D LEFT OUTER JOIN EMP E
     ON D.DEPTNO = E.DEPTNO;

     • Right Outer Join

     A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.

     Example
     The LEFT Outer join query can be re written using RIGHT outer join as follows

     SELECT D.DEPTNO, DNAME, ENAME, SAL, HIREDATE
     FROM EMP E RIGHT OUTER JOIN DEPT D
     ON E.DEPTNO = D.DEPTNO;

     • Full Outer Join

     Unlike the LEFT and RIGHT outer joins, a full outer join also returns non-matching rows from both tables; left and right.
     Use the FULL OUTER JOIN clause to perform a full outer join.

   The outer join query returns rows even when there are no matches through the JOIN critieria on the second table. There are different types of outer joins.

   • Anti joins

   Returns the dataset of the SELECT statement after filtering with NOT IN subquery.

   • Semi joins

   Returns the dataset of the SELECT statement after filtering with IN subquery.

Joins are queries that merge rows from more than one data object like tables, views or materialized views.
The join condition is specified in the WHERE clause of the query. When no join condition is specified, then a cartesian product is returned. For a proper join involving n tables, atleast n-1 columns should be mentioned in the join condition.

The following are the different types of joins.

9. What are sub-queries?
   • Sub query in the WHERE clause
   • Sub query in FROM clause
   • Sub query in the SELECT clause

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Sub queries can also be used in DML statements – INSERT, UPDATE and DELETE.

Examples of Sub queries

SELECT SALE_ID, SALE_AMOUNT
FROM SALES_TABLE
WHERE SALESMAN_ID = (SELECT SALESMAN_ID FROM SALESMAN_MASTER WHERE SALESMAN_NAME = ‘RAMESH MENON’);

SQL> select TotSpace, TotSpace-FreeSpace UsedSpace, FreeSpace
from
(select sum(bytes)/1024/1024 TotSpace
from dba_data_files
),
(select sum(bytes)/1024/1024 FreeSpace
from dba_free_Space
);
TOTSPACE USEDSPACE FREESPACE
———- ———- ———-
1391.5625 1304.625 86.9375

SQL> (select sum(bytes)/1024/1024
from dba_data_files
) totSizeMB,
(select sum(bytes)/1024/1024
from dba_free_space
) totFreeMB
from dual;
TOTSIZEMB TOTFREEMB
———- ———-
1392.5625 91.4375

10. What are the GROUP BY, ORDER BY and HAVING clauses used for?
    • Group By Clause
    • ORDER By Clause
    • HAVING Clause

The GROUP BY clause is used for aggregate functions. For example, if you wish to know the average salary of employees grouped by JOB TYPE, you can use the GROUP BY clause as follows

SQL> SELECT JOB, AVG(SAL) AvgSal
FROM EMP
GROUP BY JOB;
JOB AVG(SAL)
——— ———-
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000

The ORDER BY clause is used for sorting the output of an SELECT statement by certain column or a group of columns in a particular order (asending – default or descending)
Example
SQL>SELECT * FROM EMP
ORDER BY JOB;

The WHERE clause cannot be used to filter aggregate function results. The HAVING CLAUSE is used for the purpose of filtering a set of data based on the value of an aggregate function.
For example, if you want to list all the job categories whose average salary is greater than 2000, you can do the following

SQL> SELECT JOB, AVG(SAL) AvgSal
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) > 2000;

11. What is DISTINCT clause used for?

The DISTINCT clause is used filter duplicate values and display only one value for the occurance of a column data.

Example
Select all distinct values of JOB

SQL> select distinct job from emp;

JOB
———
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

Get count of distinct jobs

SQL> select count(distinct job) from emp;

COUNT(DISTINCTJOB)
——————
5

SQL>

12. What are the set operators available in Oracle SQL?

The following are the operators that ORACLE provides

• UNION ALL

Combines the results of two SELECT statements into one result set.

• UNION

Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

• MINUS

Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

• INTERSECT

Returns only those rows that are returned by each of two SELECT statements (i.e. only common data from both the results)

Example

#Query 1
[UNION | UNION ALL | MINUS | INTERSET]
#Query 2
[UNION | UNION ALL | MINUS | INTERSET]
.
.
#Query n
[UNION | UNION ALL | MINUS | INTERSET]

SET operators are used to combine data from two or more SELECT statements. The result of a query is treated as a SET and the SET operators
work on these SETs of data.