introduction- SQL means structure query language.To access the database, we execute a Structured Query Language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating upon relational database. The language contains a large set of operators for partitioning and combining relations. the database can be modified using the SQL statements.
Non-Procedural language: A computer language that does not require writing traditional programming logic. Also known as a “declarative language,” users concentrate on defining the input and output rather than the program steps required in a procedural language such as C++, COBOL or Visual Basic. For example, a command, such as LIST, might display all the records in a file on screen, separating fields with a blank space.
Procedural Language: A programming language that requires programming discipline, such as C/C++, JAVA, COBOL , FORTRAN, Perl and JavaScript. Also called an “imperative language,” programmers writing in such languages must develop a proper order of actions in order to solve the problem, based on knowledge of data processing and programming. In a procedural language, all the logic for inputting each record, testing for end of file and formatting each column on screen has to be explicitly programmed.
Structured Query Language -:SQL allows we to communicate with the database and has the following advantages:
– Efficient
– Easy to learn and use
– Functionally complete (SQL allows you to define , retrieve, and manipulate data in the tables.)
Relational Model-: The principles of the relational model were first outlined by Dr. E.F. Codd in a June 1970 paper called “A Relational Model of Data for Large Shared Data Banks.” In this paper, Dr, Codd proposed the relational model for database systems.
The more popular models used at that time were hierarchical and network, or even simple flat file data structures.
Components of the Relational Model
– Collections of objects or relations that store the data.
– A set of operators that can act on the relations to produce other relations.
– Data integrity for accuracy and consistency(redundancy).
Definition of a Relational Database-:A relational database uses relations or two-dimensional tables to store information. For example, you might want to store information about all the employees in your company. In a relational database, you create several tables to store different pieces of information about your employees, such as an employee table, a department table, and a salary table.
Properties of Relational Database-:– Represents data in the form of tables.
– Does not hard-core relationships between tables.
– Provides information about its content and structure in system table.
– Supports the concept of NULL values.
Terminology Used in a Relational Database-: A relational database can contain-one or many tables. A table is the basic something structure of an RDBMS. A table holds all the data necessary about something in the real world-for example, employees, invoices, or customers. The key components of a relational database are
- Entity
A thing of significance about which information needs to be known. Examples are departments, employees, and orders.
- Attribute
Something that describes or qualifies an entity. For example, for the employee entity, the attributes would be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional, this state is called optionality.
- Relationship
A named association between entities an entities showing optionality and degree. Examples are employees and departments, and orders and items.
The following tables are used ti explain different SQL commands. While using Oracle (till 9i version ) you can find these tables in the default database provided by Oracle:
– EMP table, which gives details of all the employees.
– DEPT table, which gives details of all the departments.
– SALGRADE table, which gives details of salaries for various grades.
The example shows the contents of the EMP table or relation. The numbers indicate the following:
- A single row or tuple representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant.
- A column or attribute containing the employee number, which is also the primary key. The employee number identifier a unique employee in the EMP table. A primary key must contain a value.
- A column that is not a key value. A column represents one kind of data in a table, in the example, the job title of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved.
- A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in another table. In the example, DEPTNO uniquely identifies a department in the DEPT table.
- A field can be found at the intersection of a row and column there can be only one value in it.
- A field may have no value in it. This is called a null value. In the EMP table, only employees who have a role of salesman have a value in the COMM (commission) field.
SQL Statements-:Oracle SQL complies with industry-accepted standard. Oracle Corporation ensures future compliance with evolving standards by actively involving key personnel in SQL standards. Industry-accepted committees are the American National Standards Institute(ANSI) and the International Standards Organization(ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases.
SQL is a English-like language and can be used by a range of users, including those with little or no programming experience. It is a non-procedural language. It reduces the amount of time required for creating and maintaining systems.
SQL statements are not case sensitive. The statements can be written on one or more lines. Keywords cannot be abbreviated or split across lines and clauses are usually placed on separate lines for readability & ease of editing.
For executing a SQL statement place a semicolon (;) at the end of the last clause.
SQL statements can be divided into four major categories:
- Data Manipulation Language
- Data Definition Language
- Data Control Language
- Transaction Control Language