Data manipulation Language (DML) is most commonly used to create/drop tables.
SQLite supports most of the SQL92 standards with the exception of the ALTER
statement. SQLite is a very effective and efficient database engine for
the analysis and storage of significant amounts of data. SQLite is in the public
domain.
More information about SQLite can be found at the SQLite web site
at the SQLite Home Page.
The internal auditor uses DML in order to exploit the audit software capabilities available when using the SQLite database. Internal auditors are increasingly turning to audit software which enables database data manipulation language and SQL queries in order to complete their review of large systems.
Example screen shot:
Proc SQL - DML Syntax
proc SQL data=DATAFILE db=DBNAME;
connect CONNECT;
DML;
Proc SQL - DML Parameters used
There are three primary parameters for the Proc SQL DML command:
CONNECT - the name of the SQLite file containing the database
DBNAME - always SQLITE when running a select against an SQLite database
DML - the text of the DML command
Proc SQL - Example script
*
* sqlite dml1.ezs - run Proc SQL to drop a table
* SQLite data manipulation language (DML) function
* drop a table (if it exists);
*;
proc sql db=sqlite;
connect 'C:\ezs\db\test.db';
drop table test;
The Proc SQL procedure is part of EZ-R Stats for Windows.
Analysts interested in Proc SQL may also be interested in Proc Means, Proc Outlier, Proc Benford and Proc Univariate.
A description of audit uses for SQLite is available for download. Further examples of SQL are available for the National Drug Codes.