IT

Personal notes on software development.
For Java technologies check my dedicated site

Pages

SQL

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL is an ANSI (American National Standards Institute) standard

SQL is a Standard - BUT....

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
(quote: w3schools)


Note: in addition to SQL most database systems also include a proprietary procedural language like "PL/SQL" on Oracle or "T-SQL" on SqlServer;

The "A Relational Database Overview" article, has a section dedicated to general DBMS overview, great to get a quick overview of the main concepts like:
  • Joins;
  • Constraints;
  • Integrity Rules;
  • Referential integrity;
  • Primary key;
  • Foreign key;
  • Result Sets and Cursors: The rows that satisfy the conditions of a query are called the result set. The number of rows returned in a result set can be zero, one, or many. A user can access the data in a result set one row at a time, and a cursor provides the means to do that. A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed. A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing. Most DBMSs create a cursor automatically when a result set is generated. Earlier JDBC API versions added new capabilities for a result set's cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row.
  • Transactions: DBMSs use transactions to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency);
  • Commit and Rollback: a transaction ends with either a commit or a rollback, depending on whether there are any problems with data consistency or data concurrency.
  • Lock: lock is a mechanism that prohibits two transactions from manipulating the same data at the same time;
  • Stored Procedures: A stored procedure is a group of SQL statements that can be called by name. In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method. Traditionally, stored procedures have been written in a DBMS-specific programming language. The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API. Stored procedures written in the Java programming language are bytecode portable between DBMSs. Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database.
  • Metadata: Databases store user data, and they also store information about the database itself. Most DBMSs have a set of system tables, which list tables in the database, column names in each table, primary keys, foreign keys, stored procedures, and so forth. Each DBMS has its own functions for getting information about table layouts and database features. 

SQL commands are divided into categories, the two main ones being [1]:
  • Data Manipulation Language (DML) commands: deal with data, either retrieving it or modifying it to keep it up-to-date. The most common DML commands are:
    • SELECT: used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
    • INSERT: adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
    • DELETE: removes a specified row or set of rows from a table
    • UPDATE: changes an existing value in a column or group of columns in a table
  • Data Definition Language (DDL) commands: create or change tables and other database objects such as views and indexes; The most common DDL commands are:
    • CREATE TABLE:  creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
    • DROP TABLE: deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
    • ALTER TABLE:  adds or removes a column from a table. It also adds or drops table constraints and alters column attributes

SQL Tutorials

Some popular DBMS (Database Management System):


MS SQL Server


TODO


ORACLE


PL/SQL:

PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL


My personal notes on PL/SQL
PL/SQL TUTORIAL - learn pl/sql in a simple way um excelente tutorial directo ao assunto ideal para uma rápida aprendizagem ou revisão dos conceitos base da tecnologia;

References:

[1] - A Relational Database Overview

Posted by amano at 4/13/2010
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)