Introduction to Information Technology/SQL

Introduction

Structured Query Language (SQL) is a programming language designed for managing data held in the relational database management system. The SQL is divided into six language elements:

• Clauses - They are components of the statements and queries.
• Expressions - They produce either scalar values or tables that consist of columns and rows of data.
• Predicates - They specify conditions, used to limit the effects of the statements and queries, or to change the program flow.
• Queries - Based on given criteria, they retrieve data.
• Statements - They control transactions, program flow, connections, sessions, or diagnostics.
• Insignificant whitespace - This is usually disregarded in SQL statements and queries.

SQL was originally based on relational algebra and tuple relational calculus, and consists of a data definition language, data manipulation language, and data control language.

SQL became an accepted standard of the American National Standards Institute (ANSI) in 1986, and also of the International Organization for Standardization (ISO) in 1987. Although it has accepted as the standard, it has been revised to include a larger set of features. Even with the existence of the standards, most code is not fully portable among database systems without adjustments.

Major SQL Statements

Operators

Operator	Description	Example
=	Equal to	Author = 'Alcott'
<>	Not equal to (many DBMSs accept != in addition to <>)	Dept <> 'Sales'
>	Greater than	Hire Date > '2012-01-31'
<	Less than	Bonus < 50000.00
>=	Greater than or equal	Dependents >= 2
<=	Less than or equal	Rate <= 0.05
BETWEEN	Between an inclusive range	Cost BETWEEN 100.00 AND 500.00
LIKE	Match a character pattern	First_Name LIKE 'Will%'
IN	Equal to one of multiple possible values	DeptCode IN (101, 103, 209)
ISorIS NOT	Compare to null (missing data)	Address IS NOT NULL
IS NOT DISTINCT FROM	Is equal to value or both are nulls (missing data)	Debt IS NOT DISTINCT FROM - Receivables
AS	Used to change a field name when viewing results	SELECT employee AS 'department1'

Other operators have at times been suggested and/or implemented, such as the skyline operator (for finding only those records that are not 'worse' than any others).

SQL has the case/when/then/else/end expression, introduced in SQL-92. In its most general form, called a "searched case" in the SQL standard, it works like Conditional (programming) else if in other programming languages:

CASE WHEN n > 0
          THEN 'positive'
     WHEN n < 0
          THEN 'negative'
     ELSE 'zero'
END

SQL tests WHEN conditions in the order they appear in the source. If the source does not specify an ELSE expression, SQL defaults to ELSE NULL. An abbreviated syntax—called "simple case" in the SQL standard—mirrors switch statements:

CASE n WHEN 1
            THEN 'one'
       WHEN 2
            THEN 'two'
       ELSE 'I cannot count that high'
END

This syntax uses implicit equality comparisons, with SQL CASE|the usual caveats for comparing with NULL.

For the Oracle-SQL dialect, the latter can be shortened to an equivalent DECODE construct:

SELECT DECODE(n, 1, 'one',
                 2, 'two',
                    'i cannot count that high')
FROM   some_table;

The last value is the default; if none is specified, it also defaults to NULL. However, unlike the standard's "simple case", Oracle's DECODE considers two NULLs equal with each other.

Data Definition Language (DDL)

A statement that defines the different structures of objects in a database. Its action can create, change, or delete database objects in a database. The commands for DDL are:

•CREATE - creates an object for the database, such as index or table

•ALTER - remodel the structure of objects already in the database, like adding row to a table

•DROP - eliminates an object in the database

•RENAME - used to rename an object in a database

•TRUNCATE - eliminates all data inside of a table without deleting the table

Data Manipulation Language (DML)

A statement that lets database users manipulate data and database. Database users can manipulate data in a variety of ways. The commands for DML are:

•SELECT - retrieves data from a table

•INSERT - add rows to an existing table

•UPDATE - updates a set of existing table rows

•DELETE - removes existing rows from a table

The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data:

• Insert (SQL)|INSERT adds rows (formally tuples) to an existing table, e.g.:

INSERT INTO example
 (field1, field2, field3)
 VALUES
 ('test', 'N', NULL);

• Update (SQL)|UPDATE modifies a set of existing table rows, e.g.:

UPDATE example
 SET field1 = 'updated value'
 WHERE field2 = 'N';

• Delete (SQL)|DELETE removes existing rows from a table, e.g.:

DELETE FROM example
 WHERE field2 = 'N';

• Merge (SQL)|MERGE is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called "upsert".

 MERGE INTO table_name USING table_reference ON (condition)
 WHEN MATCHED THEN
 UPDATE SET column1 = value1 [, column2 = value2 ...]
 WHEN NOT MATCHED THEN
 INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Transaction Control Language (TCL)

A statement that manages the changes made in a database. The actions database users can accept, undo, or mark their changes in the database. The commands for TCL are:

•COMMIT - makes data changes permanent

•ROLLBACK - discards any data changes made before the last COMMIT or ROLLBACK statement

•SAVEPOINT - saves the database at the current point

Data Control Statement (DCS)

A statement used to create a privilege that allows users gain access and manipulate data in the database. Database administers can configure the security to control access to the database objects in a database. The commands for DCS are:

•GRANT - gives authorization to users to be able to perform operations on objects

•REVOKE - takes away authorization

Defining a Database

To build a new table in Access by using Access SQL, you must name the table, name the fields, and define the type of data that the fields will contain. One must use the CREATE TABLE statement to define the table in SQL.

Adding Data

There are two different methods for adding data to a relation. One way for adding data one at a time and one for adding a lot of data at one time, both cases use the INSERT INTO clause at the start. To add one record you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. To add many records to a table at one time,you must use the INSERT INTO statement along with a SELECT statement.

Viewing Data

To view data in a table using SQL, use the SELECT statement to retrieve data from the database tables, and the results are usually returned in a set of rows made up of any number of columns, then you must use the FROM clause to designate which table or tables to select from. To view all column headings in a table without any actual rows of data, use the SHOW statement. Other statements such as SUM, COUNT, AVG, MAX, MIN, and ORDER BY can be used to sort data in different order, get data averages and/or count specific items within a table.

Modifying Data

There are multiple ways that you can modify data in a table using SQL. One way is the ALTER TABLE statement which allows you to add, delete, or modify columns in a table. Another statement is UPDATE statement which allows you to update records in a table. These statements allow the user to input data into a table and also change current data within a table. You can also use the INSERT statement to put new data within a table.

Deleting Data

To delete data that is already inside of a data table, you must use the DELETE statement. The DELETE statement does not remove the table itself, it only deletes the data that is currently being held by the table structure.

SQL Injection Attacks

An SQL injection is an insertion of malicious attack to SQL statements in which they can gain control of the web server. It can provide the attacker with personal information and unauthorized access to other sensitive material. It can also be used to gain access to the authentication and authorization mechanisms and gather all the information in a given database. It can also be used decrease the integrity of the database as well.