SQL Introduction.

What is SQL?

  • SQL is acronym for "Structured Query Language".
  • SQL is a high-level language for defining, querying, modifying, and controlling the data in a relational database.
  • SQL is not case sensitive (the verb SELECT is the same as verb SelEcT).
  • In 1970, Dr. EF Codd at IBM Research Laboratory in San Jose, California proposed the relational model for database management.
  • In 1981, IBM announced its first commercial SQL-based product, SQL/DS.
  • During the early 1980s, Oracle, Relational Technology, and several other vendors also announced SQL-based relational database management systems.
  • By 1989, there were more than seventy-five SQL or SQL-like database management systems running on computers of all sizes.

What is Relational Database Management System (RDBMS)?

  • The target of RDBMS is to store and manipulate information or more precise :
    1. Represent all information in the database as tables.
    2. Keeps the logical representation of data independent from its physical storage characteristics.
    3. Use one high-level language (SQL) for structuring, querying, and changing the information in the database.
    4. Supports the most important relational operations (selection, projection, join) and set operations such as union, intersection, difference and division.
    5. Supports new views option, which allows the user to specify alternative ways to view data in tables.
    6. Provide a method for differentiating between unknown values (nulls) and zero or blank handling.
    7. Support mechanisms for security and authorization.
    8. Protect data integrity through transactions and recovery procedures.

The Relational Model?

  • It's All Tables :
  • The model is a set of related tables that forms a database.
  • There are two types of tables in a relational database:
    1. user tables.
    2. system tables (system catalogue).
  • The system tables are updated by the DBMS for consistency, but you can access them just like any other table.

Independence Forever?

  • Lets applications change without affecting database design.
  • Lets database design change without affecting applications.
  • Independence on two important levels:
    1. Physical data independence means that the representation of the data—the user's eye view—is completely independent of how the data is physically stored.
    2. Logical independence means that relationships among tables, columns, and rows can change without to impairing the function of application programs and ad hoc queries.

The High-Level Language, SQL?

  • SQL is used for three kinds of operations:
    1. SQL Data manipulation using the data manipulation language (DML).

      Data retrieval: finding the particular data you want.
      Data modification: adding, removing, or changing the data.
      Insert a row into a table example:
      insert into bookstore values
      ('0201703092','The Practical SQL, Fourth Edition'
                  ,'Judith S. Bowman','Addison Wesley',39)
      Delete a row in a table example:
      delete from Bookstore where isbn_no='0201703092';
      Update a row in a table example:
      update Bookstore set publisher='Wrox' where isbn_no='0201703092';
    2. SQL Data definition using the data definition language (DDL).

      Creating or removing objects like tables, indexes, and views.
      Create a table example:
      create table test (id  int, name char (15));
      Remove a table example:
      drop table test;
    3. SQL Data administration.

      That is SQL statements, which are for control of data in the DB.
      Grant access to a table example::
      grant select on test to public;
      It Gives members of the public group (which includes all users) permission to select data from the table, test.

The main Relational Operations?

  • Three specific data retrieval (or query) operations are part of the definition of a relational database management system
    1. SQL Projection selects columns.

      With projection operation you can select which columns you want to see.
      select ISBN_NO, PRICE from bookstore;
    2. SQL Selection (also called restriction) chooses rows.

      With the selection operation you can retrieve a subset of the rows in a table.
      select ISBN_NO, PRICE from bookstore
                  where ISBN_NO='0471777781';
    3. SQL Join brings together data in related tables.

      Join, works on two or more tables at a time, combining the data so that you can compare and contrast information in your data.
      select Last_name, First_name, Telephone_type , Telephone_number
               from customers , telephones
               where customers.customer_id=telephones.customer_id ;

Alternatives for Viewing Data?

  • View, is an alternative way of looking at the data in one or more tables.
    (Views are sometimes called virtual tables or derived tables)
  • It is important to realize that a view is not a copy of the data in another table.
  • When you change data through a view, you change the real thing.
    Create a view example:
    Create view CustomOrders (customerId, FirstName, LastName,
    OrderNumber, OrderDate, DeliverDate)
    as
    select c.customer_id, first_name, last_name, order_number,
    order_date, deliver_date from orders o, customers c
            where o.customer_id=c.customer_id ;
  • You can now use select statement on that view as it was a table.
    Select from a view example
    select * from CustomOrders;

RDBMS should differentiate between unknown values.

  • Using the concept of NULL to handle missing information.
  • Indicate that a value is unknown, unavailable or inapplicable.

RDBMS should handle Security.

  • RDBMS needs to control which user that can use what data and for what purpose.
  • It is possible to control access and data modification permissions on different levels for both tables and columns.
  • The privileged user forgiven these permissions are the owners of databases and database objects (tables and views).
  • Most multi-user systems designate another privileged user, higher on the totem pole than the owners. These users are named as system administrator or the database administrator.

RDBMS should handle Integrity.

  • This is about the consistency of the data in the database. (System failure—a hardware problem, a software bug, or a logical error in an application program—can introduce one kind of inconsistency).
  • The processes that control the consistency is often called transaction management.
  • Another kind of integrity, entity integrity, is a design issue. Entity integrity requires that no primary key can have a null value.
  • A third variety of data integrity, referential integrity, is consistency among pieces of information that repeats in more than one table. (It is vital that when information change in one place, it will change in every other place it appears.)
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.