Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL as used by most database systems.
This chapter contains these topics:
Introduction to SQL Server. While it's core function is that of an RDBMS, SQL Server has become much more than that. SQL Server 2016 includes built-in business intelligence tools, as well as a range of analysis and reporting tools. This is on top of the database management tools such as database creation, backup, replication, security, and more.
History of SQL
Dr. E. F. Codd published the paper, 'A Relational Model of Data for Large Shared Data Banks', in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of the ACM. Codd's model is now accepted as the definitive model for relational database management systems (RDBMS). The language, Structured English Query Language ('SEQUEL') was developed by IBM Corporation, Inc., to use Codd's model. SEQUEL later became SQL (still pronounced 'sequel'). In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.
SQL Standards
Oracle Corporation strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.
The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:
- ANSI X3.135-1999, 'Database Language SQL', Parts 1 ('Framework'), 2 ('Foundation'), and 5 ('Bindings')
- ISO/IEC 9075:1999, 'Database Language SQL', Parts 1 ('Framework'), 2 ('Foundation'), and 5 ('Bindings')
See Also: Appendix B, 'Oracle and Standard SQL' for a detailed description of Oracle's conformance to the SQL:99 standards
How SQL Works
The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:
- It processes sets of data as groups rather than as individual units.
- It provides automatic navigation to the data.
- It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as 'persistent stored modules' (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.
Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.
SQL provides statements for a variety of tasks, including:
- Querying data
- Inserting, updating, and deleting rows in a table
- Creating, replacing, altering, and dropping objects
- Controlling access to the database and its objects
- Guaranteeing database consistency and integrity
SQL unifies all of the preceding tasks in one consistent language.
Common Language for All Relational Databases
All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.
Embedded SQL
Embedded SQL refers to the use of standard SQL statements embedded within a procedural programming language. The embedded SQL statements are documented in the Oracle precompiler books.
Embedded SQL is a collection of these statements:
- All SQL commands, such as
SELECTandINSERT, available with SQL with interactive tools - Dynamic SQL execution commands, such as
PREPAREandOPEN, which integrate the standard SQL statements with a procedural programming language
Embedded SQL also includes extensions to some standard SQL statements. Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.
Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:
- Pro*C/C++ precompiler
- Pro*COBOL precompiler
See Also: Pro*C/C++ Precompiler Programmer's Guide and Pro*COBOL Precompiler Programmer's Guide for a definition of the Oracle precompilers and embedded SQL statements
Lexical Conventions
The following lexical conventions for issuing SQL statements apply specifically to Oracle's implementation of SQL, but are generally acceptable in other SQL implementations.
When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle evaluates the following two statements in the same manner:
Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.
Tools Support
Most (but not all) Oracle tools support all features of Oracle SQL. This reference describes the complete functionality of SQL. If the Oracle tool that you are using does not support this complete functionality, you can find a discussion of the restrictions in the manual describing the tool, such as SQL*Plus User's Guide and Reference.
Before we learn about database , let's understand -
What is Data?
In simple words data can be facts related to any object in consideration.
For example your name, age, height, weight, etc are some data related to you.
A picture , image , file , pdf etc can also be considered data.
What is a Database?
Database is a systematic collection of data. Databases support storage and manipulation of data. Databases make data management easy. Let's discuss few examples.
An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.
Your electricity service provider is obviously using a database to manage billing , client related issues, to handle fault data, etc.
Let's also consider the facebook. It needs to store, manipulate and present data related to members, their friends, member activities, messages, advertisements and lot more.
We can provide countless number of examples for usage of databases .
What is a Database Management System (DBMS)?
Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of data .
It also helps to control access to the database.
Database Management Systems are not a new concept and as such had been first implemented in 1960s.
Charles Bachmen's Integrated Data Store (IDS) is said to be the first DBMS in history.
With time database technologies evolved a lot while usage and expected functionalities of databases have been increased immensely.
Types of DBMS
Let's see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.
There are 4 major types of DBMS. Let's look into them in detail.
- Hierarchical - this type of DBMS employs the 'parent-child' relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.
- Network DBMS - this type of DBMS supports many-to many relations. This usually results in complex database structures. RDM Server is an example of a database management system that implements the network model.
- Relational DBMS - this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.
- Object Oriented Relation DBMS - this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.
What is SQL?
Structured Query language (SQL) pronounced as 'S-Q-L' or sometimes as 'See-Quel'is actually the standard language for dealing with Relational Databases.
SQL programming can be effectively used to insert, search, update, delete database records.
That doesn't mean SQL cannot do things beyond that.
In fact it can do lot of things including, but not limited to, optimizing and maintenance of databases.
Relational databases like MySQL Database, Oracle, Ms SQL server, Sybase, etc uses SQL ! How to use sql syntaxes?
SQL syntaxes used in these databases are almost similar, except the fact that some are using few different syntaxes and even proprietary SQL syntaxes.
SQL Example
What is NoSQL ?
NoSQL is an upcoming category of Database Management Systems. Its main characteristic is its non-adherence to Relational Database Concepts. NOSQL means 'Not only SQL'.
Concept of NoSQL databases grew with internet giants such as Google, Facebook, Amazon etc who deal with gigantic volumes of data.
Sql Programming Language Pdf
When you use relational database for massive volumes of data , the system starts getting slow in terms of response time.
To overcome this , we could of course 'scale up' our systems by upgrading our existing hardware.
The alternative to the above problem would be to distribute our database load on multiple hosts as the load increases.
This is known as 'scaling out'.
NOSQL database are non-relational databases that scale out better than relational databases and are designed with web applications in mind.
They do not use SQL to query the data and do not follow strict schemas like relational models.With NoSQL, ACID (Atomicity, Consistency, Isolation, Durability) features are not guaranteed always
Why it makes sense to learn SQL after NOSQL ?
With the advantages of NOSQL databases outlined above that scale out better than relational models, you might be thinking why one would still want to learn about SQL database?
Well, NOSQL databases are sort of highly specialized systems and have their special usage and limitations. NOSQL suit more for those who handles huge volumes of data. The vast majority, use relational databases and associated tools.
Relational databases have the following advantages over NOSQL databases;
- SQL(relational) databases have a mature data storage and management model . This is crucial for enterprise users.
- SQL databases support the notion of views which allow users to only see data that they are authorized to view. The data that they are not authorized to see is kept hidden from them.
- SQL databases support stored procedure sql which allow database developers to implement part of the business logic into the database.
- SQL databases have better security models compared to NoSQL databases.
Oracle Database Introduction To Sql Pdf
a demand for professionals who can handle relational databases. Thus learning databases and SQL still holds merit.Oracle Database 12c Introduction To Sql Ed 1.1 Pdf
Summary
|