FANDOM


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 Edit


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)Edit

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


&nbsp 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:Edit

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:Edit

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:Edit

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:Edit

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


“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

http://www.w3schools.com/sql/default.asp

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.