|
The following
assesses experience with beginning to intermediate SQL techniques
and should be asked of candidates that are Database Administrators,
Database Developers and anyone using SQL. The topic is best
suited for Developers and Database Administrators having
some experience or training with SQL. The techniques discussed
are useful in a variety of situations.
SQL and Relational database systems have developed sophisticated
tools and techniques to affect changes to a database.
Our
first set of questions address the broad categories of database
changes.
Question
1:
What are the three broad categories of SQL statements that
affect database changes and how do they differ? (Hint: The
acronyms are DCL, DDL and DML)?
Answer
1:
The acronyms
stand for Database Control Language, Database Definition
Language and Database Manipulation language (DCL, DDL, DML)
respectively. In the SQL92 standard, DCL was part of DDL,
so DCL is a relatively new classification.
DCL, or Database
Control Language statements control what various users may
or may not do to database objects. In general terms, we
might call these rights or permissions.
DDL, or Database
Definition Language statements create and change the definition
of database objects.
DML, or Database
Manipulation Language statements are those that change,
retrieve and remove data within database objects.
Question
2:
What are some of the SQL statements associated with each
of these categories?
Answer
2:
DCL, or Database
Control Language statements include GRANT and REVOKE. DDL,
or Database Definition Language statements include CREATE,
ALTER and DROP. DML, or Database Manipulation Language statements
include SELECT, INSERT, UPDATE, DELETE.
BONUS QUESTION 1:
In
which of these categories do RDBMS Vendors depart from the
SQL standard, mostly by adding functionality extensions to
SQL statements?
BONUS
QUESTION 1:
DDL, or Data Definition
Language statements. These are implemented to include many
proprietary extensions that address storage, parallelism and
other attributes above the SQL standard.
BASIC SQL TRANSACTIONS
Transactional capabilities
are crucial to database integrity. All major Database vendors
include robust transaction control.
Question 3:
What
is a transaction?
Answer
3:
A transaction is
a group of SQL statements that succeed or fail as a unit.
Question
4:
What
are some SQL statements that control transactions?
Answer
4:
The basic statements
are COMMIT and ROLLBACK. We will discuss the details of these
and others in a future column.
Example of an SQL transaction.
The following is a classic example of a transaction.
Simplified code
for Transferring $50 from checking to savings:
Insert
into checking account transaction ("withdrawal", -$50.00);
Insert into savings account transaction ("deposit", $50.00)
Without transactional
control, there is the potential that one statement will fail
while the other succeeds. This would invalidate the integrity
of the account. The account would be in error by positive
or negative $50.00.
Question
5: (BONUS):
What
might cause one statement to succeed while the other fails?
Answer
5: (multiple answers)
The computer
and/or database crashes suddenly.
One table is locked while the other is not.
One table is inaccessible (because of disk problems).
Each table is on a different system, and one is inaccessible.
However, we can
minimize the risks of invalidating the account integrity by
placing these statements within a transaction. For example:
(PSEUDO
CODE)
ON
ERROR GOTO ROLLBACK
Insert
into checking account transaction ("withdrawal", -$50.00);
Insert into savings account transaction ("deposit", $50.00)
COMMIT WORK
There is much
more to transaction control in the SQL standard, some of which
may be discussed in a future column.
About
the author
Mike Wilkins is an Oracle Master DBA with more than twenty
years of IT experience in a variety of technologies. Mike's
roles include, Database Designer/modeler, Programmer-Analyst,
Database Administrator, UNIX Systems and Network Administrator,
Network Designer/Analyst, and others. Mike has performed 300+
technical interviews in the last five years.
|
