An epigrammatic account of SQL

The time gone by of SQL begins in an IBM laboratory in San Jose, California, where on earth SQL was urbanized in the late 1970s. The fundamental pose for Structured Query Language and the language itself is time and again referred to as "sequel." It was in the inauguration built-up for IBM's DB2 item for consumption as a basic criterion of a relational database management system, or RDBMS.. In fact, SQL creates an RDBMS achievable. SQL is a nonprocedural language, in disparity to the procedural or third-generation languages such as COBOL and C that had been created up to that time. The quality that categorizes a DBMS from an RDBMS is that the RDBMS provides a set-oriented database language. For most RDBMSs, this set-oriented database language is SQL. Two standards association, the American National Standards Institute and the International Standards Organization, currently prop up SQL standards to exchange. The ANSI-92 standard is the customary for the SQL used throughout this article. Although these standard-making bodies systematize standards for database system designers to tag along, all database products differ from the ANSI standard to some degree. In addition, most systems provide some proprietary extensions to SQL that extend the language into a true procedural language. We have used various RDBMSs to prepare the examples in this article to give you an idea of what to expect from the common database systems.

It was an inquiring feeling whether there is a modest background on the evolution of databases and database conjecture would facilitate us value the workings of SQL. Database systems stock up in sequence in every feasible business environment. From outsized pathway databases such as airline proviso systems to a child's baseball card collection, database systems store and hand out the data that we depend on. Until the last few years, large database systems could be run only on large mainframe computers. These machines have traditionally been expensive to design, purchase, and maintain. However, today's generation of powerful, inexpensive workstation computers enables programmers to design software that maintains and distributes data quickly and inexpensively.

Model of Database

1.      The largest part of popular data storage model is the relational database, which was bedded on  a formative paper named  "A Relational Model of Data for Large Shared Data Banks," written by Dr. E. F. Codd in 1970. SQL steps forward to service on the conception of the relational database introduced by  Dr. Codd who had promulgated such new exploration for creating and building object orient programming software to be based on the 13 rules,  referred to as Codd's 12 Rules, for the relational model which are the basic milestone in RDBMS concept.:

2.      The following rules have been explored by Dr. Codd which are basically known as ‘Dr. Codd’s  Database rules.

1.  All information in a relational database (including table and column names) is represented explicitly as values in tables.
2. Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.
3. The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.
4. The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.
5. At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.

6. All views that are theoretically updatable can be updated through the system.

7. The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.

8. Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.

9. Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.

10. The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.

11. Application programs and ad hoc requests are logically unaffected when data is first circulated or when it is reallocate.

12. It ought not to be potential to get around the integrity rules defined through the database language by using lower-level languages.

A good number database has had a "parent/child" relationship; that is, a parent node would contain file pointers to its children. This method has several advantages and many disadvantages. In its favor is the fact that the physical structure of data on a disk becomes unimportant. The programmer simply stores pointers to the next location, so data can be accessed in this manner. Also, data can be added and deleted easily. However, different groups of information could not be easily joined to form new information. The format of the data on the disk could not be arbitrarily changed after the database was created. Doing so would require the creation of a new database structure. Codd's idea for an RDBMS uses the mathematical concepts of relational algebra to break down data into sets and related common subsets. Because information can naturally be grouped into distinct sets, Dr. Codd organized his database system around this concept. Under the relational model, data is separated into sets that resemble a table structure. This table structure consists of individual data elements called columns or fields. A single set of a group of fields is known as a record or row. For instance, to create a relational database consisting of employee data, you might start with a table called EMPLOYEE that contains the following pieces of information: Name, Age, and Occupation. These three pieces of data make up the fields in the Job holder table.

Job holder table.

Name	Age	Occupation
Mehedi	12	Electrical engineer
Gias	44	Museum curator
Kaium	42	Assistant Chef
Abdul Karim	29	Student
Mohammad	32	Game programmer
Kamruzzaman	46	Singer

The six rows are the records in the EMPLOYEE table. To retrieve a specific record from this table, for example, Dave Davidson, a user would instruct the database management system to retrieve the records where the NAME field was equal to Dave Davidson. If the DBMS had been instructed to retrieve all the fields in the record, the employee's name, age, and occupation would be returned to the user. SQL is the language that tells the database to retrieve this data. A sample SQL statement that makes this query is

SELECT *

FROM EMPLOYEE

It is important to note that the exact syntax is not important at this point. Due to the fact that the various data items can be grouped according to obvious relationships, the relational database model gives the database designer a great deal of flexibility to describe the relationships between the data elements. Through the mathematical concepts of join and union, relational databases can quickly retrieve pieces of data from different sets (tables) and return them to the user or program as one "joined" collection of data. The join feature enables the designer to store sets of information in separate tables to reduce repetition.

 Duty table.

Name	Duties
Skender	Cook
Lily Huq	Teacher
Shovon	Dancer
Idiorty	Superintendent

Designing the Database Structure

The vital decision for a database designer, after the hardware platform and the RDBMS have been preferred, is the structure of the tables. Decisions made at this stage of the design can affect performance and programming later during the development process. The process of separating data into distinct, unique sets is called normalization.

Modern Database Panorama

Computing technology has made a permanent change in the ways businesses work around the world. Information that was at one time stored in warehouses full of filing cabinets can now be accessed instantaneously at the click of a mouse button. Orders placed by customers in foreign countries can now be instantly processed on the floor of a manufacturing facility. Even though 20 years ago much of this information had been transported onto corporate mainframe databases, offices still operated in a batch-processing environment. If a query needed to be performed, someone notified the management information systems (MIS) department; the requested data was delivered as soon as possible. In addition to the development of the relational database model, two technologies led to the rapid growth of what are now called client/server database systems. The first important technology was the personal computer. Inexpensive, easy-to-use applications such as Lotus 1-2-3 and Word Perfect enabled employees (and home computer users) to create documents and manage data quickly and accurately. Users became accustomed to continually upgrading systems because the rate of change was so rapid, even as the price of the more advanced systems continued to fall.

The second important technology was the local area network (LAN) and its integration into offices across the world. Although users were accustomed to terminal connections to a corporate mainframe, now word processing files could be stored locally within an office and accessed from any computer attached to the network. After the Apple Macintosh introduced a friendly graphical user interface, computers were not only inexpensive and powerful but also easy to use. In addition, they could be accessed from remote sites, and large amounts of data could be off-loaded to departmental data servers.

During this time of rapid change and advancement, a new type of system appeared. Called client/server development because processing is split between client computers and a database server, this new breed of application was a radical change from mainframe-based application programming. Among the many advantages of this type of architecture are

• Reduced maintenance costs
• Reduced network load (processing occurs on database server or client computer)
• Multiple operating systems that can interoperate as long as they share a common network protocol
• Improved data integrity owing to centralized data location

In putting into practice Client/Server figure, Bernard H. Boar classifies client/server computing as follows:

Client/server computing is a processing model in which a single application is partitioned between multiple processors (front-end and back-end) and the processors cooperate (transparent to the end user) to complete the processing as a single unified task. Implementing Client/Server Computing A client/server bond product ties the processors together to provide a single system image (illusion). Shareable resources are positioned as requestor clients that access authorized services. The architecture is endlessly recursive; in turn, servers can become clients and request services of other servers on the network, and so on and so on. This type of application development requires an entirely new set of programming skills. User interface programming is now written for graphical user interfaces, whether it be MS Windows, IBM OS/2, Apple Macintosh, or the UNIX X-Window system. Using SQL and a network connection, the application can interface to a database residing on a remote server. The increased power of personal computer hardware enables critical database information to be stored on a relatively inexpensive standalone server. In addition, this server can be replaced later with little or no change to the client applications.

Implementation Level

Oracle Corporation released the first commercial RDBMS that used SQL. Although the original versions were developed for VAX/VMS systems, Oracle was one of the first vendors to release a DOS version of its RDBMS. (Oracle is now available on more than 70 platforms.) In the mid-1980s Sybase released its RDBMS, SQL Server. With client libraries for database access, support for stored procedures (discussed on Day 14, "Dynamic Uses of SQL"), and interoperability with various networks, SQL Server became a successful product, particularly in client/server environments. One of the strongest points for both of theseSQL Server powerful database systems is their scalability across platforms. C language code (combined with SQL) written for Oracle on a PC is virtually identical to its counterpart written for an Oracle database running on a VAX system.

An Overview of SQL

SQL is the de facto standard language used to manipulate and retrieve data from these relational databases. SQL enables a programmer or database administrator to do the following:

• Modify a database's structure
• Change system security settings
• Add user permissions on databases or tables
• Query a database for information
• Update the contents of a database

According to Dr. Codd, the term SQL can be confusing. The S, for Structured, and the L, for Language, is undemanding enough, but the Q is a little misleading. Q, of course, stands for "Query," which--if taken literally--would restrict you to asking the database questions. But SQL does much more than ask questions. With SQL you can also create tables, add data, delete data, splice data together, trigger actions based on changes to the database, and store your queries within your program or database. Unfortunately, there is no good substitute for Query. Obviously, Structured Add Modify Delete Join Store Trigger and Query Language (SAMDJSTQL) is a bit cumbersome. In the interest of harmony, we will stay with SQL. However, you now know that its function is bigger than its name. The most commonly used statement in SQL is the SELECT statement (see Day 2, "Introduction to the Query: The SELECT Statement"), which retrieves data from the database and returns the data to the user. The EMPLOYEE table example illustrates a typical example of a SELECT statement situation. In addition to the SELECT statement, SQL provides statements for creating new databases, tables, fields, and indexes, as well as statements for inserting and deleting records. ANSI SQL also recommends a core group of data manipulation functions. As you will find out, many database systems also have tools for ensuring data integrity and enforcing security (see Day 11, "Controlling Transactions") that enable programmers to stop the execution of a group of commands if a certain condition occurs.

Popular SQL Implementations

This section introduces some of the more popular implementations of SQL, each of which has its own strengths and weaknesses. Where some implementations of SQL have been developed for PC use and easy user interactivity, others have been developed to accommodate very large databases (VLDB). This sections introduces selected key features of some implementations. In addition to serving as an SQL reference, this article also contains many practical software development examples. SQL is useful only when it solves your real-world problems, which occur inside your code.

Microsoft Access

We use Microsoft Access, a PC-based DBMS, to illustrate some of the examples in this text. Access is very easy to use. We  can use GUI tools or manually enter your SQL statements.

Oracle7 Language

In view of the above discussion, it is obvious that in creating an object oriented programming software, the importance SQL is inevitable. In integrating parent-child relationship, SQL is very important. From SQL, Insert, Update and Delete Command are helpful. In a nut shell, we exercise Oracle7, which represent the larger corporate database world, to demonstrate command-line SQL and database management techniques. These techniques are imperative for the reason that the days of the standalone machine are drawing to an end, as are the days when expressive one database or one in commission system was enough. In command-line, simple stand+[cedilla]one SQL statements are entered into Oracle's SQL*Plus tool. This tool then returns data to the screen for the user to see, or it performs the apposite action on the database. Most examples are directed toward the beginning programmer or first-time user of SQL. We begin with the simplest of SQL statements and advance to the topics of transaction running and stored procedure programming. The Oracle RDBMS is circulated with a full complement of development tools. It comprises a C++ and Visual Basic language library (Oracle Objects for OLE) that can link an application to a Personal Oracle database. It also comes with graphical tools for database, user, and object administration, as well as the SQL*Loader utility, which is used to introduce internally and send abroad data to and from Oracle.