What is SQL(Structured Query Language)?

Based on this SQL Language and relational database system is one of the most Important foundation technology in the Computer industry today. In the last decade, The popularity of SQL has exploded, and today it stands as a standard computer language Really hundreds of database products now support SQL, running Personal Computer with mainframe and even on handheld systems equipment.

An official international SQL standard has been adopted and extended twice. Virtually every major enterprise software product depends on SQL for its data management, And SQL is in the root of two of Microsoft and Oracle's database products The world's largest software companies From its obscure beginning as IBM The research project, SQL has jumped to prominence as an important computer Technology and a powerful market force.

What is SQL? why is it important? What can this do, and how does it work? If SC Actually, there is a standard, why there are so many different versions and bids? How to do Do popular SQL products like SQL Server, Oracle, Informix, Sybase, and DB2 compare? How

SQL is related to Microsoft standards, such as ODBC and COM? How JDBC Links SQL and Java in the world of object technology? Really scales with the SQL mainframe Handling equipment? What really needs high-volume performance transaction processing? How to use SQL, and how to use your computer Can you make the most of this important data management tool?

The SQL Language

A tool is to organize, manage, and retrieve data stored by SQL computers. Database The name "SQL" is an abbreviation for the structured query language. for The historical reason, SQL is usually called "sequel", but the alternate pronunciation "S.Q.L." Is also used. As the name implies, SQL is a computer language you use Interact with a database. In fact, SQL works with a specific type of database, which is called a relational database.

The computer system in the shape has a database It stores important information. If the computer system is in a business, then the database Store inventory, production, sales or payroll data. On a personal computer, The database can store the checks, people's list and data about them that you write
Phone numbers, or data removed from a large computer system computer program The database controller is called a database management system or DBMS.

When you need to recover data from the database, you use the SQL language to create Request. DBMS processes the SQL request retrieves the requested data, and He lets you return This process of requesting and retrieving data from the database The results are called a database query - hence name structured query language. Name Structured Query Language is actually some misnomer. first of all,
SQL is far more than a query tool, though its original purpose and recovery were Data is still one of its most important tasks. SQL is used to control all functions A DBMS provides for its users, which includes:

Data Definition Allows the SQL used to define the structure and organization of the archived Data and connection between stored data items.
Data retrieval Allows user or application programs to retrieve SQL stored data From the database and use it.
data manipulation. Allows to update SQL user or application program Databases by adding new data, removing old data, and modifying data already stored Data
Access Control Using SQL can be used to restrict the ability to retrieve, add, and modify a user Data protection against data collected against unauthorized access. data sharing. SQL is used to ensure data sharing by concurrent users That they do not interfere with each other.
Data Integrity Defines integrity constraints in the SQL database, which prevents it Corruption due to incompatible updates or system failures.

Thus SQL is a comprehensive language for control and interaction with a database management system.

Second, the SQL is not exactly a complete computer language, such as COBOL, C, C ++, or Java. There is no IF statement for test conditions in SQL, and there is no GOTO, DO or FOR Statement for Program Flow Control Instead, SQL is a database sublink, which includes Approximately forty statements specifically for database management tasks These SQL Extensions can be embedded in another language, such as COBOL or C That language to use in database access Alternatively, they can be clearly sent to one Database Management System for Processing through a Language-to-Call Level Interface Such as C, C ++, or Java...

Finally, SQL is not a particularly structured language, especially when compared to excessive Structured languages like C, Pascal, or Java. Instead, the SQL statements are similar English sentence completed with "noise words", which does not mean Statement but read it more naturally. There are a lot of discrepancies in There are some special rules for SQL language, and to prevent you from producing it SQL statements that look completely legal, but it does not make any sense.

Despite the inaccuracy of its name, SQL has emerged as the standard language for using Relational database SQL is a powerful language and is one that is relatively easy Learn.

SQL itself is not a database management system, nor is it a stand-alone product. you Can not go to the computer store and "buy SQL." Instead, SQL is an integral part Database Management System a language and a tool to communicate with DBMS.

The database engine is the heart of DBMS, which is actually responsible for the structure, Store and retrieves data in the database. It accepts SQL requests from the other DBMS components, such as a form feature, report writer or interactive query feature, From user-written application programs, and even from other computer systems. Inform of The picture shows, SQL plays several different roles:

SQL is an interactive query language. Users type an SQL command in an interactive The SQL program provides a convenient way to retrieve and display on the screen, Easy-to-use tools for ad hoc database queries
SQL is a database programming language. Programmers embed SQL commands Use the data in a database to program their applications. Both user-written Program and database utility programs (such as report writers and data entry tools) Use this technique to access the database.
SQL is a database administration language. Database Manager Responsible Uses SQL to define a database for managing minicomputers or mainframe database Structure and control access to the stored data.
SQL is a client/server language. Personal computer programs use SQL Communicate on a network with a database server that stores shared data. this The client/server architecture has become very popular for enterprise-class applications.
SQL is an internet data access language. Internet web server with whom to interact Corporate data and Internet application servers all use SQL as a standard language To access corporate databases.
SQL is a distributed database language. Distributed database management system Use SQL to help deliver data in many connected computer systems. On each system, DBMS software uses SQL to communicate with other systems, Send request for data access
SQL is a database gateway language. In a computer network with a mix of different DBMS products, SQL is often used in Gateway, which allows a brand of DBMS to Communicate with other brands

SQL has thus become a useful, powerful tool for connecting people, computer programs,
And computer systems for data stored in a relational database.

Features and Benefits of SQL Language

SQL is a comprehensive tool for easy-to-understand language and management. Data Here are some key features of SQL and market forces. It was successful:

Vendor Freedom
Portability in computer systems
SQL standard
IBM endorsement (DB2)
Microsoft Commitment (ODBC and ADO)
Relational basis
High-level, English-like structure
Interactive, ad hoc questions
Programmatic database access
Many views of the data
Complete database language
Dynamic data definition
Customer/server architecture
Extensibility and Object Technology
Use of Internet database
Java Integration (JDBC)

These are the reasons that SQL has emerged as the standard tool for data management Personal computer, minicomputer, and mainframe They are described in the sections Which follow.

Vendor Independence

SQL is offered by all major DBMS vendors, and there is no new database product The last decade has been highly successful without the SQL support. A SQL-based database And programs that use it can be moved from one DBMS to another vendor's DBMS With minimal conversion effort and the fewer return of personnel. PC database tools, such as As a query tool, report to authors, and app generators do many different things Brands of SQL databases Thus the seller's independence provided by SQL was one The most important reason for its initial popularity and remains an important feature today.

Portability Across Computer Systems

SQL-based database products run on mainframes on computer systems and Midrange systems for personal computers, workstations, and even handheld devices. They work on computer systems alone, in the departmental local area network. And in the enterprise-wide or internet-wide network SQL-based applications that begin Single-user systems can be moved to large server systems because they grow. data from Corporate SQL-based databases can be removed and departmental or downloadable Personal database Finally, affordable personal computers can be used for prototype SQL-based database application before going into an expensive multi-user system.

SQL Language Standards

An official standard for SQL was initially published by the American National Standards Institute (ANSI) and International Standards Organization (ISO) 1986 and was Expanded in 1989 and again in 1992. SQL is the U.S There is also federal information processing Standard (FIPS), this is an important requirement for large government computer contracts.

In these years, other international, government and vendor groups have led Standardization of new SQL capabilities, such as call-level interfaces or object-based Extension. Many of these new initiatives have been included in ANSI / ISO Standard over time. Work as the official seal of the approved standard approval Speeded up SQL and its market acceptance.

IBM Endorsement (DB2)

SQL was originally invented by IBM researchers and has since become a strategic Products for IBM based on its major DB2 database. SQL support is available at all From IBM's flagship product, Personal Computer to Midday System (AS / 400) The IBM mainframe running both the MVS and VM operating systems are RS / 6000. IBM's initial work provided a clear indication of IBM's direction for other databases and systems Vendors to follow quickly in the development of SQL and relational databases. Later, IBM Commitment and widespread support triggered the market acceptance of SQL.

Microsoft Commitment (ODBC and ADO)

Microsoft has long considered database access to be an important part of its Windows Personal
Computer software architecture Server versions of both desktop and Windows provide both Standardized relational database access through Open Database Connectivity (ODBC), A
SQL-based call-level API. The leading Windows software application (spreadsheets, words Processors, databases, etc.) from Microsoft and other vendors, ODBC, and all Major SQL databases offer ODBC access. Microsoft has extended ODBC support High-level, more object-oriented database access layers as part of its object Linking and embedding techniques (OLE DB), and recently active / as part of X (Active or X data object, or ADO).

SQL is a language for relational databases, and it has become popular Relational database model A relational database has a tabular, row/column structure Intuitive for users, the SQL language is simple and easy to understand. There is also a strong theoretical foundation in the relational model which has guided development And implementation of relational databases. About the ride's acceptance wave With the success of the relational model, the SQL database language has become
Relational database

High-Level, English-Like Structure

SQL statements look like ordinary English sentences, making SQL easy to learn and understanding. This part is because the SQL statement describes the retrieval of data, Instead of specifying the data to search for. Tables and columns in a SQL database Long, descriptive names are, As a result, most SQL statements say "what they meant" And can be read as clear natural sentences.

Interactive, Ad Hoc Queries

SQL is an interactive query language that gives ad hoc access to data stored to users. Interactively using SQL, the user can get answers in minutes or even complex questions Second, unlike days or weeks, it will take the programmer to write Custom Report Program Due to ad hoc query power of SQL, data is more accessible And one organization can be used to help make better, more informed decisions. Ad for SQL The hoax query capability was an important advantage on your non-relevant database before Has been continuing as a major benefit on the development and recent pure object-based Database.

Programmatic Database Access

SQL is also a database language used to write applications used by the programmer A database The same SQL statement is used for both interactive and programmatic Access, therefore, database access parts of a program can be tested first with interactive SQL and then embedded in the program. In contrast, traditional databases were provided A set of tools for programmatic access and a separate query feature for ad hoc Without any synergy between request, access two approaches.

Multiple Views of Data

Using SQL, a database creator can differentiate different users of the database
Its structure and view of the scene. For example, the database can be created so that
Each user only sees the data of their department or sales area. Also, with data
Many different parts of the database can be added and the user can be presented as one
Simple Row / Column Table In this way the SQL view can be used to increase security
Tailor it to the databases and special needs of individual users.

Complete Database Language

SQL was first developed as an ad hoc query language, but its powers now go far further Data retrieval SQL database provides a complete, consistent language for creating, Manage its security, update its content, retrieve data, and share data Many concurrent users. SQL concepts that are learned in a part of the language can be implemented on other SQL commands, which can make users more productive.

Dynamic Data Definition

Using SQL, the structure of the database can be dynamically altered and extended. While users are accessing database contents. This is a major advance on static data Definition languages that prevent access to the database, while its structure was Being changed SQL provides maximum flexibility in this way so that the database can be optimized Changing requirements during the continuation of online applications.

Client/Server Architecture

SQL is a natural vehicle for applying applications using a distributed, client/server Architecture In this role, SQL acts as a link between "front-end" computer systems. User interaction for the database and optimized for the "back-end" system Management allows each system to do whatever it does best. SQL also allows personalization To work as the front end of the computer network server or to the larger minicomputer and Mainframe database provides access to corporate data from personal computers Applications.

Extensibility and Object Technology

The key challenge for SQL dominance of the database has come as a database standard From the onset of object-based programming and the introduction of an object-based As the expansion of broad market trend towards database object-based technology. SQL-based database vendors gradually expanded and responded to this challenge Increasing SQL to include object features These "objects / relational" databases, which Continuing to be based on SQL, has emerged as a more popular option for "pure" Object "can ensure the database and the continued dominance of SQL for the next decade.

Internet Database Access

With the popularity of the Internet and World Wide Web, and their explosions Based on the standards, SQL got a new role in the form of an internet data in the late 1990s Access Standard At the beginning of web development, developers had a need. Obtain and submit database information on web pages and use SQL as a normal Language for the database gateway Recently, the emergence of the three-tier Internet Architecture with separate thin clients, application servers and database server layers
Established in the form of standard links between SQL applications and database levels.

Java Integration (JDBC)

One of the major new areas of SQL development is SQL integration with Java. Looking at the need to add Java language to an existing relational database, Sun Microsystems (creators of Java) have Java database connectivity (JDBC), a Standard API which allows Java programs to access SQL for database access. many of Major database vendors have also announced or implemented Java support within them Database system allows Java to be used as the language for stored procedures and Business logic in the database itself This trends toward integration between Java and SQL will ensure the continuous importance of SQL in a new era of Java-based Programming.

An Overview of Tour of SQL Language

A Simple Database

Examples of quick visits are based on a small simple relational database Distribution Company. The database stores the necessary information To implement a small order processing application. Specifically, it stores the following Information:

Customers who buy products of the company,
The orders were given by those customers,
Sellers who sell products to customers, and
Sales office where those salespeople work.

This database, like other people, is a model of "real world". Data stored in The database represents the actual institutions - the customer, the order, the seller, and the office. There is one Separate table of data for each different type of unit. Database requests that you create Using SQL language parallel real-world activities, as customers, cancel, and Change the order, as you salute and set the fire, and so on. Let's see how you can use it To manipulate SQL data

Retrieving SQL Data

First of all, list the sales offices, showing the city where each one is located and its year- date of sale. The SQL statement that retrieves data from the database is called Choose. This SQL statement retrieves the data you want:

SELECT statements ask for three pieces of data - city, office number, and Sales - For each office. It also specifies that the data comes from the office table, Which stores data about sales offices. The results of the query appear in the tabular form, Immediately after the request

The SELECT statement is used for all SQL queries. For example, here is a query that is listed
Each vendor's name and year-to-year sales in the database It also shows Quota (sales target) and office number where each person works. in this matter, The data comes from the SALESREPS table:

SQL also asks for the results you've calculated. For example, you can ask to calculate with SQL
The amount by which each seller is less or more than the quote:

Requested data (including calculated differences for sales and quota) Each vendor appears once again in the row/column table. Maybe you would like to Focus on those salespersons whose sales are lower than their quotas. SQL lets you retrieve The kind of selective information very easily, by adding mathematical comparison Previous request:

The same technique can be used to list and find large orders in the database The customer ordered, which product was ordered, and in what quantity You too can Ask SQL to sort order based on order amount from SQL:

Summarizing SQL Data

SQL not only retrieves data from the database, but it can also be used to summarize the database
Content as well. What is the average size of an order in the database? This request asks
To see all the orders of SQL and get the average amount:

You can also ask for the average amount of all orders held by A. Special Customers:

Finally, let's explore the total amount of orders placed by each customer. For doing this,
You can tell SQL to group commands together by customer number and then the total
Order for each customer:

Adding Data in SQL Database

SQL is also used to add new data to the database. For example, you just say
A new Western area sales office with a sales target of $ 275,000 opened in Dallas. As told here
INSERT statement that adds a new office to the database, as Office number 23:

Similarly, if Mary Jones (Staff Number 109) signs a new customer, Acme
This statement of industry, INSERT, connects the customer to the database as a customer
Number 2125 with $ 2525 credit limit:

Deleting SQL Data

Just as the SQL INSERT statement adds new data to the database, in the same way, SQL DELETE
The details remove data from the database. If Acke Industries decides after a few days
To switch to a competitor, you can remove them from the database with this statement:

And if you decide to end all salespersons whose sales are lower than their quota, then you
They can be removed from the database with this DELETE description:

Updating the Database

SQL language is also used to modify previously stored data in the database. for
For example, to increase the credit limit of $ 75,000 for First Corp, you will use SQL
Update Statement:

The UPDATE statement can make several changes to the database at one time. for
For example, this update statement raises quotas for all sellers of $ 15,000:

Protecting SQL Data

An important role of the database is to protect access to data stored by unauthorized
Users. For example, suppose that your assistant named Mary was not previously authorized
To put data about new customers in the database This SQL statement grants him a grant
That permission:

Similarly, the following SQL statement allows Mary to update the data
To obtain customer data with customer and SELECT statement:

If Mary is no longer allowed to add new clients to the database, then this REVOKE
The statement will reject it:

Similarly, this REVOKE statement will cancel all of Mary's privileges to reach the customer
Data in any way:

Creating a Database

Before you can store data in a database, you must first define the structure of the data.
Assume that you want to expand the sample database by adding a table of data
Products sold by your company. For each product, the data to be stored include:

A three-character constructor ID code.
A five-character product ID code,
Details of maximum thirty characters,
Price of the product, and
Amount currently on hand.

This SQL CREATE TABLE statement defines a new table for storing data of products:

Although much more esoteric than previous SQL statements, creation table statement Still quite straightforward. It specifies and specifies the name products in the new table The name and the type of data stored in each of its five columns.

Once the table is created, you can fill it with the data. Here is an INSERT statement For a new shipment of 7 sizes of 250 sizes (Product ACI-41007), which costs $ 225.00 One piece:

Finally, if you later find that you no longer need to store the data for the products
In the database, you can erase the table (and all data contained in it) with drop tables
Statement:

Summary

This quick tour of SQL showed you what SQL can do and illustrate the style of SQL Languages using eight of the most commonly used SQL statements In summary:

SQL is used to retrieve data from the database, using the Select statement. You can do this Retrieve all or part of the collected data, sort it, and ask to summarize the data using SQL Yoga and Average.
SQL is used to update the database, by adding new data to the INSERT statement, Removing data with DELETE statement, and modifying existing data with UPDATE Statement.
SQL is used to control access to databases, specific and granting With privilege and report statements for specific users.
SQL is used to create a database by defining and dropping the structure of new tables. Do not need tables when creating and using DROP statements.

Also Read:

Monday 4 February 2019

What is SQL? Structured Query Language

No comments:

Post a Comment

Recent

Follow Us

Topics

Popular

Blog Archive

Contact Form