SQL : Overview

Databases have been a staple of business computing from the very beginning of the digital era. In fact, the relational database was born in 1970 when E.F. Codd, a researcher at IBM, wrote a paper outlining the process. Since then, relational databases have grown in popularity to become the standard. Originally, databases were flat. This means that the information was stored in one long text file, called a tab delimited file. Each entry in the tab delimited file is separated by a special character, such as a vertical bar (|). Each entry contains multiple pieces of information (fields) about a particular object or person grouped together as a record. The text file makes it difficult to search for specific information or to create reports that include only certain fields from each record. Here’s an example of the file created by a flat database:
Id, Lname, FName, Age, Salary|110,Smith, John, 30, 6000|114,Doe, Jane, 32, 7200..
Relational databases use tables to store information. The standard fields and records are represented as columns (fields) and rows (records) in a table. For example:

Id FName Lname Age Salary
110 John Smith 30 6000
114 Jane Doe 32 7200

A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database.

SQL is a standard language for accessing and manipulating databases. Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language.However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Schema : is a collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A schema has the name of the user who controls it.

DDL: Data Definition Language. It is used to create and modify the structure of database objects in database.Examples: CREATE, ALTER, DROP statements.
DML: Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements.
DCL: Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements.
TCL: Transactional Control Language. It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK statements.

SQL Data Types

Data types and ranges for Oracle, MySQL and SQL Server etc. Each column in a database table is required to have a name and a data type. SQL developers have to decide what types of data will be stored inside each and every table column when creating a SQL table. The data type is a label and a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

However, different databases offer different choices for the data type definition. The following table shows some of the common names of data types between the various database platforms:

Data type Oracle MySQL
boolean N/A N/A
integer Number Int
Integer
float Number/ Float Float
currency N/A N/A
string (fixed) Char – 1 default, 2000 max Char
string (variable) Varchar – 2000
Varchar2 – 4000 bytes max
Varchar
binary object Long (Character data of variable length up to 2 gigabytes) or CLOB
Raw
Blob
Blob
Text


Views and Materialized views
:

Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly. Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query resultset has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed. Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables.

Differences between Stored Procedures and Function :

  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input/output parameters for it whereas functions can have only input parameters.
  • Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  • Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
  • We can go for transaction management in procedure whereas we can’t go in function.

SQL constraints are used to specify rules for the data in a table. It enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of Constraints:

  • NOT NULL – Indicates that a column cannot store NULL value
  • UNIQUE – Ensures that each row for a column must have a unique value. It allows only one null value.
  • PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly.
  • FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table.
  • CHECK – Ensures that the value in a column meets a specific condition.
  • DEFAULT – Specifies a default value when specified none for this column.

Indexes can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries. If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Database normalization is the process of organizing data to minimize data redundancy (data duplication) and undesirable characteristics DML anolomies. There are 6 normal forms, most databases are in Third normal form (3NF).

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

A package will have two mandatory parts:

  • Package specification
  • Package body or definition

Benefits of Packages

  • Logical Grouping – Methods that work together can be put into a cohesive unit rather than just logically coupled but physically separate.
  • Secure Private Methods – Functions and Procedures can be made private to the package and only be used within it. This makes the public surface simpler and more secure.
  • Privilege Management – Permissions can be granted once for a group of procedures that work together rather than separately for each procedure/function required.
  • Secure Wrapping – Wrapped packages are more difficult to unwrap than wrapped functions/procedures.
  • Simplified Naming – A larger namespace allows names that are simpler and can be re-used in other packages.
  • Better Performance – Packages can be compiled and are loaded into memory in entirety rather than piecemeal as other methods. This benefit if it exists at all is minimal compared to the other benefits.
  • Reduced Invalidation – Changing a package body does not invalidate dependencies as changing a function or procedure does.
  • Unique Features – Package Variables, Package Constants, Initialization, Session State, Package Comments, and Overloaded Methods.

Data Definition Language (DDL)

Following are few  SQL Data Definition Language (DDL) commands that are supported by “mysql”. They are listed below with short descriptions:

  • “CREATE dataObjectType dataObjectName” – Creates new databases, tables, views, triggers, indexes, and other data objects.
  • “RENAME dataObjectType dataObjectName” – Renames existing databases, tables, views, triggers, indexes, and other data objects.
  • “ALTER dataObjectType dataObjectName” – Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.
  • “DROP dataObjectType dataObjectName” – Drops existing databases, tables, views, triggers, indexes, and other data objects.
  • “TRUNCATE [TABLE] tbl_name” –  TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but truncate also removes all the spaces allocated for the records previously.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: