SQL is the dominant query language for database access. This is a fourth generation language that can be used to dynamically query data and display the data that was retrieved. SQL standards were first developed and published in 1986. The SQL standard has been modified several times since then. These standards have been approved by both the ISO and ANSI organizations. Even though these two organizations back the standards, there are many commercial implementations of SQL. While syntax will change across each product, the basic structure remains the same.
SQL and sub-languages[]
SQL contains 4 common sub-languages each with their own usage and implementation
- •Data Manipulation Language (DML)
- SELECT,INSERT, UPDATE, DELETE,MERGE
- •Data Definition Language (DDL)
- CREATE, ALTER, DROP,RENAME,TRUNCATE
- •Data Control Language (DCL)
- GRANT, REVOKE
- •Transactional Control Language (TCL)
- COMMIT, ROLLBACK,SAVEPOINT
Data Manipulation Language (DML)[]
DML statements in SQL are concerned with querying and manipulation physical data stored inside a database table.
- SELECT Statement
- SELECT allows the user or program to read the data and populate results based on certain criteria. The basic structure of a select statement looks like:
SELECT {field-list | * | DISTINCTROW field}
FROM table-list
WHERE expression
GROUP BY group-fields
HAVING group-expression
ORDER BY field-list;
- SELECT is a non-destructive command. In other words, it cannot be used to destroy or alter data already stored in the database. It can be used in conjunction with table creation, or copying data from one table to another.
- For example:
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
- SELECT can also be used to join tables together. To do this, you specify multiple tables in the FROM clause and explicitly define the field names.
SELECT EMPLOYEE.EMPNUM, OFFICENUM FROM EMPLOYEE, DIRECTORY WHERE EMPLOYEE.EMPID = DIRECTORY.EMPID;
- INSERT Statement
- INSERT is used to modify a table and add additional tuples.
INSERT INTO table (field1, field2, field3...) VALUES (value1, value2, value3,...)
INSERT INTO EMPLOYEE (EMPID, EMPNUM, EMPNAME) VALUES (AK001, 2200, ALAN KIRK)
- UPDATE Statement
- UPDATE modifies existing table records and changes them accordingly.
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
- DELETE Statement
- DELETE removes existing records from a table
DELETE FROM tablename WHERE column=value
 
DDL’s primary goals are to delete and create tables. The major commands in DDL are CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX. The CREATE TABLE command will do exactly what is sounds like it will create a table. The syntax looks like
CREATE TABLE employees (
id INTEGER PRIMARY KEY, first_name CHAR(50) null, last_name CHAR(75) not null, date_of_birth DATE null
);
This command will create the employees table with the following fields id, first_name, last_name, date_of_birth. The DROP TABLE command drops a table from the database. If you type in DROP TABLE employees then the employees table will be dropped from the database along with all of the records associated with it. The CREATE INDEX command is used to create indexes to make searching the database faster it’s like looking in a index of a book. The generic syntax is CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME). DROP INDEX does exactly what it sounds like it will drop the index you made from the database. With DDL you can create and destroy databases and their objects. The DCL is the third part of the SQL structure. This portion of the structure helps to control all the data and integrity within the database. DCL grants the ability to create primary, secondary, and other keys in the database tables. Another aspect of the DCL portion is the ability to control the data that is being manipulated within a table. With one of the most important features being the rollback command to restore data that was removed before the changes were made permanent.</nowiki>
Creating Views in a Database:[]
Views provide a way to easily access commonly used data for the end-user. Creating a view is as simple as writing a SELECT statement - all you need to do is save that statement for re-use with the CREATE VIEW statement and name it. For example:
CREATE VIEW view_name AS SELECT CHARGE.chrg_num, CHARGE.chrg_type, CHARGE.amount, CHARGE.time_stamp, BILL.invoice_num FROM CHARGE, BILL WHERE BILL.invoice_num = CHARGE.invoice_num ORDER BY CHARGE.time_stamp;
Storing Images in a Database:[]
There is conflicting advice on how to store and use images in a database. Some people believe they should be stored directly in the database using directories while others think that simply the file path should be stored because storing the image files themselves take up too much disk space. Picking the method in which best works for you depends on your situation. Storing the files themselves is good if you really need the images backed up and in a secure place. If you simply just need to reference the image files storing the path might be better and more efficient. If it doesn't matter than you might want to go with the file path method because it is easier to set up within the database and sometimes you can run into issues with accessing image formats in your DBMS. The following code example use’s BFILE’s; other image formats can be used like BLOB’s or LOB’s.
Using a Directory with BFILE’s in Oracle:[]
id /*
Creation of the directory: dir_temp is the name and ‘c:\temp’
is the folder where the image files exist
*/
---------------------------------------------------------------------------
CREATE OR REPLACE DIRECTORY dir_temp AS 'c:\temp';
---------------------------------------------------------------------------
/*
The population of the directory is as simple as creating a table that contains a column
with data type that can hold images in this case it is a column that is a BFILE type.
When your inserting the data, when you get to the BFILE column you reference the BFILE
by first stating the directory that you created earlier, and then inserting the file name
*/
--------------------------------------------------------------------------------
INSERT INTO EXAMPLE_TABLE VALUES('0000000001', 'John', 'B', 'Doe',
BFILENAME('dir_temp','0000000001.jpg');
--------------------------------------------------------------------------------
Storing the file path:[]
id /*
Storing the file path is as simple as creating or altering an existing table to hold
the file paths. In the following example an existing table is altered by the creation
of a new column
*/
---------------------------------------------------------------------------
ALTER TABLE EXAMPLE ADD EX_PATH VARCHAR2(500);
---------------------------------------------------------------------------
/*
Population of these fields is very simple as well. You just insert the entire file
path into path field.
*/
---------------------------------------------------------------------------------------
INSERT INTO EXAMPLE VALUES('0000000001', 'John', 'B', 'Doe', 'C:\temp\0000000001.JPG');
---------------------------------------------------------------------------------------
/*
Now when you are reporting and want to display the images you can create a image field,
set the control source to the EX_PATH, and set the property to linked rather than embedded
*/
References
"SQL tutorial." W3Schools. 2 Dec 2007 <http://www.w3schools.com/sql/default.asp>.
"SQL Tutorial" 1Keydata. <http://www.1keydata.com/sql/sql.html >.
"SQL Insert command." Plus2Net. 2 Dec 2007 <http://www.plus2net.com/sql_tutorial/sql_insert_set.php>.
"SQLCourese - Advanced Online SQl Training." SQLCourse2.com. 2 Dec 2007 <http://sqlcourse2.com/select2.html>. Italic text
"Introduction to Oracle SQL": 4 Mar 2018 <https://www.databasestar.com/introduction-oracle-sql-course/>
“Creat directory in Oracle” Rene Nyffenegger. 12 Dec 2010.< http://www.adp-gmbh.ch/ora/sql/create_directory.html>
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands