Object Query Language (OQL)

Object Query Language (OQL) is a query language standard for object-oriented databases modelled after SQL. OQL was developed by the Object Data Management Group (ODMG). Because of its overall complexity no vendor has ever fully implemented the complete OQL. OQL has influenced the design of some of the newer query languages like JDOQL and EJB QL, but they can't be considered as different flavours of OQL, and should be treated separately.

The key Differences Between OQL and SQL :
- OQL supports object referencing within tables. Objects can be nested within objects.
- Not all SQL keywords are supported within OQL. Keywords that are not relevant to Netcool/Precision IP have been removed from the syntax.
- OQL can perform mathematical computations within OQL statements.

General Rules of OQL :
- All complete statements must be terminated by a semi-colon.
- A list of entries in OQL is usually separated by commas but not terminated by a comma.
- Strings of text are enclosed by matching quotation marks.

OQL was designed to be object-oriented. Queries are specified using objects and their attributes (data-members). Similarly, queries return sets of objects. The complex relationships in an object model can be easily navigated, using the same class-member paradigm, used by object-oriented programming languages. This can often lead to increased performance over SQL, where resource-consuming join processes are necessary to capture relationships. Another big advantage is, that table names and column names are not necessary in the query strings, because queries are formulated using class names and attribute names and no mapping knowledge is necessary in the application.
OQL may be used as an embedded language or as a standalone query language. Both of these are supported by OpenAccess. As an embedded language, OQL queries can be used directly in your application programs. Programs can embed OQL queries, and receive results in the native data types of the programming language being used. OQL statements are simply text strings, which means that the standard string representation of your programming language is used to express the query.

Simple query :
The following example illustrates how one might retrieve the CPU-speed of all PCs with more than 64MB of RAM from a fictional PC database:
SELECT pc.cpuspeed
FROM PCs pc
WHERE pc.ram > 64

Query with grouping and aggregation :
The following example illustrates how one might retrieve the average amount of RAM on a PC, grouped by manufacturer:
SELECT manufacturer, AVG(SELECT part.pc.ram FROM partition part)
FROM PCs pc
GROUP BY manufacturer: pc.manufacturer
Note the use of the keyword partition, as opposed to aggregation in traditional SQL.

Overview of Database Security

Database security is the set of systems, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. Database security is also a specialty within the broader discipline of computer security. The database is the entity where all the data is stored, so protecting it from unauthorized access and change is extremely critical.
Traditionally databases have been protected from external connections by firewalls or routers on the network perimeter with the database environment existing on the internal network opposed to being located within a demilitarized zone.
Database security can begin with the process of creation and publishing of appropriate security standards for the database environment. The standards may include specific controls for the various relevant database platforms; a set of best practices that cross over the platforms; and linkages of the standards to higher level polices and governmental regulations.
One of the easiest steps to take is regarding passwords. Default or weak passwords are still often used by enterprises to protect an online asset as important as a database, but it's a problem that's easy to fix. The remedy is enforcing a strong password policy; that is, passwords must be changed regularly and be at least 10 digits long and contain both alphanumeric characters and symbols.
SQL Injection attacks pose tremendous risks to web applications that depend upon a database back-end to generate dynamic content. In this type of attack, hackers manipulate a web application in an attempt to inject their own SQL commands into those issued by the database. To prevent this type of attack, it is essential to ensure that all user-supplied data is validated before letting it anywhere near your scripts, data access routines and SQL queries, and preferably use parametrized queries. Another reason to validate and clean data received from users is to prevent cross-site scripting (XSS) attacks, which can be used to compromise a database connected to a Web server.
A database security program should include the regular review of permissions granted to individually owned accounts and accounts used by automated processes. The accounts used by automated processes should have appropriate controls around password storage such as sufficient encryption and access controls to reduce the risk of compromise.
The software used for the database, for the middle layers and for all other layers should be updated regularly with patches, updates and fixes. Falling behind in this task is pretty painful if you end up exposing holes in the software to attackers (and attackers know that a number of companies do not upgrade their systems on an immediate basis).

Learn software concepts: Structured Query Language (SQL)

The structured query language (SQL) is the language used to query and manipulate information within a SQL Server database. SQL is actually an ISO and ANSI standardised language. However, a lot of RDBMS software use their own proprietary extensions within their own Transact-SQL (T-SQL) variant of SQL.

The basic building block of the structured query language is the SQL statement. Using statements, information in a database can be manipulated and queried.
* CREATE - a data structure.
* SELECT - read one or more rows from a table.
* INSERT - one or more rows into a table.
* DELETE - one or more rows from a table.
* UPDATE - change the column values in a row.
* DROP - a data structure.

Language Structure :
SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.
The other lexical elements of SQL statements are:
* names -- names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a - z) and may contain digits (0 - 9) and underscore (_)
* literals -- quoted strings, numeric values, date time values.
* delimiters -- + - , ( ) = < > <= >= <> . * / || ? ;
Basic database objects (tables, views) can optionally be qualified by schema name. A dot -- ".", separates qualifiers: schema-name . table-name
Column names can be qualified by table name with optional schema qualification.

Syntax of Simple SELECT : SELECT column FROM tablename
- Using "Where"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;
- Compound Conditions
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY < 40000 OR BENEFITS < 10000;
- Using "IN"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE POSITION IN ('Manager', 'Staff');
- Using "Between"
SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY BETWEEN 30000 AND 50000;
- Using "LIKE"
SELECT EMPLOYEEIDNO
FROM EMPLOYEEADDRESSTABLE
WHERE LASTNAME LIKE 'L%';