SQL Concepts

 

What is a SQL? 
It is Structured Query language. It is a non procedural language and it is a database language.
Categories in SQL commands 
There are 3 broad categories in SQL commands. They are namely
1.Data Definition language (DDL) 
2.Data Manipulation Language (DML) 
3.Transaction Control Commands 
Under each category there are many SQL commands. Let us see each in brief.
Commands under Data Definition language (DDL) 
CREATE TABLE is used in creation of table(s).
ALTER TABLE: If you want to modify a table definition we can use this command. This command is used to alter column definition in table.
DROP TABLE: This command is used to remove an existing table permanently from database.
Commands under Data Manipulation Language (DML) 
INSERT: This command is used to insert rows into the table.
SELECT: This is used to select values or data from table.
DELETE: In order to delete rows from a table we use this command.
UPDATE: This SQL command is used to modify the values in an existing table.
Commands under Transaction Control Statements 
ROLLBACK – This SQL command is used to undo the current transaction
SYNTAX: ROLLBACK;
SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.
SYNTAX: SAVEPOINT savepoint_identifier;
COMMIT: This command is used to make all changes permanent in database and also marks the end of transaction.
SYNTAX: COMMIT;
Always it is better to commit changes to database at regular intervals since it will help loss of data or loss of work done when computer shuts due to unavoidable reasons.
Constraints Handling in SQL: 
Constraint Handling can be done in two levels namely:
Column Level 
Table Level 
Some of the Constraints used in SQL are:
The keyword NOT NULL marks a column that the particular column cannot contain null. By default column contains null unless we define the constraint NOT NULL. This is a column level constraint as it can be defined for columns only.
PRIMARY KEY 
A column or a group of columns together can be defined as PRIMARY KERY. If a column or a group of columns are defined as a PRIMARY KEYS then the value of the primary key cannot appear more than once in the table. Also those columns defined as primary keys cannot have null values in it.
REFERENTIAL INTEGRITY 
The table on which the column or a combination of columns is defined by this constraint is called the foreign key is called child table. When this constraint is defined a relation is defined between this table which has the foreign key and a table that has the primary key in relationship with this. This is called as referenced key. The table which has the primary key or in other words the referenced key is called as parent table.
VIEW 
This is an important concept in SQL. A View is nothing but a window of an existing table. In other words it is a logical representation that is created from one or existing base table. After creation of view it can be used just as a base table. That is one can query or select values of data’s from views and it also possible to update a view.
Views are created by suing CREATE VIEW command in SQL
SYNTAX: CREATE VIEW viewname AS SELECT statement
One of the important points to consider while a user uses views is that we have seen that vies are created from a base table and if suppose the base table is deleted after which if a user tries to use or access the view error will occur.
SYNONYMS: 
It is possible to refer a table with a different name and this done by using CREATE SYNONYM. It is possible to create synonym for tables as well as views.
SYNTAX: CREATE synonym name FOR username.tablename;
[What’s procedural and non-procedural ?
Programming languages are procedural if they use programming elements such as conditional statements (if-then-else, do-while etc.). SQL has none of these types of statements.]
What’s Database Normalization ?
Normalization is the process where a database is designed in a way that removes redundancies, and increases the clarity in organizing data in a database.
1st Normal Form or 1NF:
Each Column Type is Unique.
2nd Normal Form or 2NF:
The entity under consideration should already be in the 1NF and all attributes within the entity should depend solely on the entity’s unique identifier.
3rd Normal Form or 3NF:
The entity should already be in the 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.
If such an entity exists, move it outside into a new table.
Now if these 3NF are achieved, the database is considered normalized. But there are three more ‘extended’ NF for the elitist.
These are:
BCNF (Boyce & Codd):
The database should be in 3NF and all tables can have only one primary key.
4NF: 
Tables cannot have multi-valued dependencies on a Primary Key.
5NF: 
There should be no cyclic dependencies in a composite key.
Let’s look at various Data Types under MySQL.

 

Type

Size in bytes

Description

 

TINYINT (length)

1

Integer with unsigned range of 0-255 and a signed range from -128-127

SMALLINT (length)

2

Integer with unsigned range of 0-65535 and a signed range from -32768-32767

MEDIUMINT(length)

3

Integer with unsigned range of 0-16777215 and a signed range from -8388608-8388607

INT(length)

4

Integer with unsigned range of 0-429467295 and a signed range from -2147483648-2147483647

BIGINT(length)

8

Integer with unsigned range of 0-18446744 and a signed range from
-9223372036854775808-9223372036854775807

FLOAT(length, decimal)

4

Floating point number with max. value +/-3.402823466E38 and min.(non-zero) value +/11.175494351E-38

DOUBLEPRECISION(length, decimal)

8

Floating point number with max. value +/- -1.7976931348623157E308 and min. (non-zero) value +/-2.2250738585072014E-308

DECIMAL(length, decimal)

length

Floating point number with the range of the DOUBLE type that is stored as a CHAR field type.

TIMESTAMP(length)

4

YYYYMMDDHHMMSS or YYMMDDHHMMSS or YYYYMMDD, YYMMDD. A Timestamp value is updated each time the row changes value. A NULL value sets the field to the current time.

DATE

3

YYYY-MM-DD

TIME

3

HH:MM:DD

DATETIME

8

YYYY-MM-DD HH:MM:SS

YEAR

1

YYYY or YY

CHAR(length)

length

A fixed length text string where fields shorter than the assigned length are filled with trailing spaces.

VARCHAR(length)

length

A fixed length text string (255 Character Max) where unused trailing spaces are removed before storing.

TINYTEXT

length+1

A text field with max. length of 255 characters.

TINYBLOB

length+1

A binary field with max. length of 255 characters.

TEXT

length+1

64Kb of text

BLOB

length+1

64Kb of data

MEDIUMTEXT

length+3

16Mb of text

MEDIUMBLOB

length+3

16 Mb of data

LONGTEXT

length+4

4GB of text

LONGBLOB

length+4

4GB of data

ENUM

1,2

This field can contain one of a possible 65535 number of options. Ex: ENUM(‘abc’,’def’,’ghi’)

SET

1-8

This type of field can contain any number of a set of predefined possible values.

 

What are Triggers ? 

A trigger is a pre-programmed notification that performs a set of actions that may be commonly required. Triggers can be programmed to execute certain actions before or after an event occurs. Triggers are very useful as they they increase efficiency and accuracy in performing operations on databases and also are increase productivity by reducing the time for application development. Triggers however do carry a price in terms of processing overhead.

 


What are Procedures ? 

Like triggers, Procedures or ‘Stored’ Procedures are productivity enhancers. Suppose you needed to perform an action using a programming interface to the database in say PERL and ASP. If a programmed action could be stored at the database level, it’s obvious that it has to be written only once and cam be called by any programming language interacting with the database. 

Procedures are executed using triggers.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

Entity Integrity : There are no duplicate rows in a table.

Domain Integrity : Enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity : Rows cannot be deleted, which are used by other records.

User-Defined Integrity : Enforces some specific business rules that do not fall into entity, domain, or referential integrity.

Comments

comments

Leave a Comment

Time limit is exhausted. Please reload CAPTCHA.