Hello everyone! In this tutorial , we will learn major part of DBMS i.e. SQL. It may me somewhat lengthy. This tutorial consists of DBMS keys, SQL commands

DBMS keys:

A DBMS key is an attribute or set of an attribute which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables.

1.Primary Key: A column or columns is called primary key (PK) that uniquely identifies each row in the table. When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Points to remember for primary key:

• Primary key enforces the entity integrity of the table.
• It always has unique data.
• It length cannot be exceeded than 900 bytes.
• A primary key cannot have null value.
• There can be no duplicate value for a primary key.
• A table can contain only one primary key constraint.

2. Foreign Key: A foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table.

The foreign key constraint is generally prevents action that destroy links between tables. It also prevents invalid data to enter in foreign key column.

3.Composite Key : A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

4.Unique key: A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

5. Alternate keys: Alternate key is a secondary key it can be simple to understand by an example: Let’s take an example of student it can contain NAME, ROLL NO., ID and CLASS. Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys . In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about alternate key, the column may not be primary key but still it is a unique key in the column.

SQL Commands:

SQL commands are mainly categorized into four categories as discussed below:

1.DDL(Data Definition Language) :  It actually consists of the SQL commands that can be used to define the database schema .

Examples of DDL commands:

• CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers)
• DROP – is used to delete objects from the database.
• ALTER-is used to alter the structure of the database.
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
• RENAME –is used to rename an object existing in the database.

2.DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

Examples of DML:

• SELECT – is used to retrieve data from the a database.
• INSERT – is used to insert data into a table.
• UPDATE – is used to update existing data within a table.
• DELETE – is used to delete records from a database table.

3.DCL(Data Control Language) :  It includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL commands:

• GRANT-gives user’s access privileges to database.
• REVOKE-withdraw user’s access privileges given by using the GRANT command.

4.TCL(transaction Control Language) : TCL commands deals with the transaction within the database.

Examples of TCL commands:

• COMMIT– commits a Transaction.
• ROLLBACK– rollbacks a transaction in case of any error occurs.
• SAVEPOINT–sets a savepoint within a transaction.
• SET TRANSACTION–specify characteristics for the transaction.

Go through below document for understanding all the concepts with example. Download

SQL Clauses:

• Where: This clause in SQL is used to fetch records on the basis of some condition.
• Order by: This clause is used to sort the data either in ascending order or in descending order based on the specified column name or column number .
• Group by: It is used to group the common data in result set.
• Distinct : It is used to retrieve unique / different records from a column. It can return records from single or multiple columns both. POINTS: Remove the duplicate records from the result set. It is used with only SQL SELECT Statement.
• Having: It is used to retrieve records on the basis of some condition. It is same like SQL WHERE Clause except that the having clause can be used with Aggregate functions like SUM, AVG and Count etc.

 POINTS to remember about Having:
 *HAVING condition can restrict the data that results using group by clause.  
 *Aggregate function cannot be used in WHERE condition where as HAVING can be used. 
 *WHERE condition filters the data before using Group By Clause. Whereas HAVING filters the data after Group By clause. 
 *WHERE condition can be used for both Grouped and non-Grouped queries 
 * It is not mandatory to use Group By clause for an SQL Select statement  with HAVING condition 
* HAVING allows to perform conditional operations on aggregate values. 

Aggregate Functions:

The following are the most commonly used SQL aggregate functions:

• AVG – calculates the average of a set of values.
• COUNT – counts rows in a specified table or view.
• MIN – gets the minimum value in a set of values.
• MAX – gets the maximum value in a set of values.
• SUM – calculates the sum of values.

That’s all about Day-2.

I