SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SQL Data Manipulation Language (DML)

SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.
These query and update commands together form the Data Manipulation Language (DML) part of SQL:
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table

What is SQL?

• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL is an ANSI standard computer language
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is easy to learn


SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.
Unfortunately, there are many different versions of the SQL language, but to be in compliance with the ANSI standard, they must support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

How can I restore passwords ?

grant connect to SCOTT identified by passwords GOBBLEDYGOOK;

How do I order a union ?

select DEPTNO, DNAME from DEPT
union
select EMPNO, ENAME from EMP
order by 2;

How do control which rollback segment I use ?

In SQL, you may need to control the rollback segment used as the default rollback segment may be too small for the required transaction, or you may want to ensure that your transaction runs in a special rollback segment, unaffected by others. The statement is as follows:
SET TRANSACTION USE ROLLBACK SEGMENT segment_name;
On a related note, if all you are doing are SELECTS, it is worth telling the database of this using the following:
SET TRANSACTION READ ONLY;
Both these statements must be the first statement of the transaction.

How do I get a top ten ?

select a.ordered_column, a.other_stuff
from table_name a
where 10 > (
select count(1)
from table_name b
where b.ordered_column
< a.ordered_column )
order by a.ordered_columnl;

What are these V$ tables?

There are a number of tables/views beginning with V$ that hold gory details for performance monitoring. These are not guaranteed to be stable from minor release to minor release and are for DBAs only.
There are usually no real underlying tables (unlike SYS.OBJ$) and are dummied up by the RDBMS kernel software in much the same way that UNIX System V.4 dummies up the files in the /proc or /dev/proc directories.
If you have any code depending on these (and the widely used tools supplied by Oracle but unsupported are in this category) then you need to verify that everything works each time you upgrade your database. And when a major revision changes, all bets are off.

How can I interpret a two digit year after 2000 ?

When converting to dates from characters when you only have two characters for the year, the picture format "RR" will be interpreted as the year based on a guess that that date is between 1950 and 2049.

Is there a formatter for SQL or PL/SQL ?

There are a number of "beautifiers" for various program languages. The cb and indent programs for the C language spring to mind (although they have slightly different conventions). As far as I know there is no PD formatter for SQL available.
Given that there are PD general SQL parsers and that the SQL standards are drafted in something close to BNF, maybe someone could base a reformatter based on the grammar.
Note that you CANNOT use cb and indent with Pro*C as both these programs will screw up the embedded SQL code.

How can I discover what tables, columns, etc are there ?

Oracle maintains a live set of views that you can query to tell you what you have available. In V6, the first two to look at are DICT and DICT_COLUMNS which act as a directory of the other dictionary views. It is a good idea to be familiar with these.
Not all of these views are accessible by all users. If you are a DBA you should also create private DBA synonyms by running $ORACLE_HOME/rdbms/admin/dba_syn.sql in your account.

How can I get a name for a temporary table that will not clash ?

How can I get information on the row based on group information ?

Imagine we have the EMP table and want details on the employee who has the highest salary. You need to use a subquery.
select e.ENAME, e.EMPNO, e.SAL
from EMP e
where e.SAL in (
select max (e2.SAL)
from EMP e2
);
You could get similar info on employees with the highest salary in their departments as follows
select e.ENAME, e.DEPTNO, e.SAL
from EMP e
where e.SAL = (
select max (e2.SAL)
from EMP e2
where e2.DEPTNO = e.DEPTNO
);

Can I implement Tree Structured Queries ?

Yes! This is commonly asked by those migrating from non-RDBMS apps. This is definitely non-relational (enough to kill Codd and then make him roll in his grave) and is a feature I have not seen in the competition.
The definitive example is in the example SCOTT/TIGER database, when looking at the EMP table (EMPNO and MGR columns). The MGR column contains the employee number of the "current" employee's boss.
You have available an extra pseudo-column, LEVEL, that says how deep in the tree you are. Oracle can handle queries with a depth up to 255.
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
You can get an "indented" report by using the level number to substring or lpad a series of spaces and concatenate that to the string.
select lpad(' ', LEVEL * 2) || ENAME ........
You use the start with clause to specify the start of the tree(s). More than one record can match the starting condition.
One disadvantage of a "connect by prior" is that you cannot perform a join to other tables. Still, I have not managed to see anything else like the "connect by prior" in the other vendor offerings and I like trees. Even trying to doing this programmatically in embedded SQL is difficult as you have to do the top level query, for each of them open a cursor to look for lower level rows, for each of these.......
soon you blow the cursor limit for your installation.
The way around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
Note that you can't trick Oracle by using CONNECT BY PRIOR on a view that does the join.

Can I remove duplicate rows ?

Yes, using the ROWID field. The ROWID is guaranteed unique. There are many variations on this theme, but the logic is to delete all but one record for each key value.
delete from EMP E
where not E.ROWID = (
select min(F.ROWID)
from EMP F
where F.EMP_ID = E.EMP_ID
);

Can I Update From Another Table ?

Yes. For example, if we had a table DEPT_SUMMARY, we could update the number of employees field as follows:
update DEPT_SUMMARY s
set NUM_EMPS = (
select count(1)
from EMP E
where E.DEPTNO = S.DEPTNO
);

Workaround for snapshots on tables with LONG columns

You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:
COPY TO SCOTT/TIGER@REMOTE -
CREATE IMAGE_TABLE USING -
SELECT IMAGE_NO, IMAGE -
FROM IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.

How does one find the next value of a sequence?

Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache. This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER SEQUENCE ... CACHE" to restore caching.
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.

How can I change my Oracle password?

Issue the following SQL command: ALTER USER IDENTIFIED BY
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".

Can one rename a column in a table?

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Other workarounds

1. -- Use a view with correct column names...
rename t1 to t1_base;
create view t1 as select * from t1_base;

2. -- Recreate the table with correct column names...
create table t2 as select * from t1;
drop table t1;
rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;