Tuesday, May 26, 2015

SQL - Basic of SQL

In this window, we'll learn about the SQL and how can we use it to get the information from the database.

Before knowing the SQL, we need to know about the database and its components. In term of the reality, database is the most important part of any application, business or for anything.

For example, if you want to start your business to sale some goods then you need to create a structure for your business such as -
1) Goods (Products)
2) Customers (Buyers of the products)
3) Vendors (Product Sellers)
4) Sales Details (Combination of product, customers etc)

Now, you need to keep all the above records somewhere to get the information in future uses such as in your laptop, your notebook or in your knowledge.

In term of the technology, this storage of the information is known as the database. So, we can say that a database is a well organized collection of the information which can be easily accessed, managed and updated.

If you are creating the database for your application then you should need to know about the Database Management System (DBMS). DBMS is nothing but it is a collection of the programs which provides you a simple way to store, modify and exact information from your database.

The best example of the DBMS is our cell phone. In the cell phone, you can view the contact list and modify them whenever you want. Apart of this, you can check the call history of any contact and delete them easily. In your cell phone, you can store audio, videos, images and eBooks etc. You can create space or directory to store specific information. All these information store in your cell phone database.

SQL (Structured Query Language) Command Types

The SQL is used to communicate with the database to perform the requested tasks such as insert new data into the database, update the existing data or get the information from the database.  To do all the activities into the database, SQL commands are categorized into following categories based on their purpose- 

  • Data Definition Language (DDL),
  • Data Control Language (DCL),
  • Data Manipulation Language (DML)

SQL Data Definition Language (DDL) is a computer language used to create and modify the structure of database objects in a database. DDL commands can be used to add, remove, or modify tables within in a database. DDLs used in database applications are considered a subset of SQL, the Structured Query Language. However, a DDL may also define other types of data, such as XML. Following clauses are the part of DDL commands –

             CREATE Statements 
             ALTER Statements
             DROP Statements
             TRUNCATE Table

Example 1: Create Command- If user wants to create a table for employees like following structure –
EmpId
EmpName
EmpSalary
EmpAge
100
Amit Gupta
15000
32
101
Anil Sharma
15500
22
102
Bill Smith
18000
25
103
Chris Gray
17500
28
104
David King
17500
22
105
Eliza Roy
15500
25
106
Flash God
15500
23

 Then user needs to run the following create command to create the above table structure -
       CREATE TABLE Employees
       (
              [EmpId] [int] NOT NULL,
              [EmpName] [varchar](50) NOT NULL,
              [EmpSalary] [int] NOT NULL,
              [EmpAge] [int] NOT NULL
       )

Example 2: Alter Command- If user wants to add a new column (Department) in the Employee table to store the department of the each employee then Employees table will be like as –
EmpId
EmpName
EmpSalary
EmpAge
Department
100
Amit Gupta
15000
32
IT
101
Anil Sharma
15500
22
Finance
102
Bill Smith
18000
25
HR
103
Chris Gray
17500
28
Sales & Marketing
104
David King
17500
22
Procurement
105
Eliza Roy
15500
25
Admin
106
Flash God
15500
23
Others

Then user needs to run the following alter command on the existing Employees table-
           ALTER TABLE Employees ADD Department Varchar(50)

Example 3: Truncate Command – If user wants to remove all the data from the employees table and reset the blank structure of the table then needs to run the following SQL command –
EmpId
EmpName
EmpSalary
EmpAge
Department
                     TRUNCATE TABLE Employees
Example 4: Drop Command – If user wants to remove any data table from the database then he needs to run the following SQL command

                     DROP TABLE Employees
SQL Data Manipulation Language (DML)This is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database.
             1)      The INSERT command in SQL is used to add records to an existing Employees table as given below –
 INSERT INTO [dbo].[Employees]
(EmpId, EmpName, EmpSalary,E mpAge, Department)
VALUES  (100,   'Amit Gupta', 15000, 32,    'IT'), 
       (101,    'Anil Sharma',15500, 22,    'Finance'),
       (102,    'Bill Smith', 18000, 25,    'HR'),
      (103,    'Chris Gray', 17500, 28,    'Sales & Marketing'),
        (104,    'David King', 17500, 22,    'Procurement'),
      (105,    'Eliza Roy',  15500, 25,    'Admin'),
      (106,    'Flash God',  15500, 23,    'Others')
       2)    The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. The command shown below retrieves all of the information contained within the Employees table-
SELECT   [EmpId]
       , [EmpName]
                     , [EmpSalary]
                     , [EmpAge]
                     , Department
              FROM [dbo].[Employees]
Outputs
EmpId
EmpName
EmpSalary
EmpAge
Department
100
Amit Gupta
15000
32
IT
101
Anil Sharma
15500
22
Finance
102
Bill Smith
18000
25
HR
103
Chris Gray
17500
28
Sales & Marketing
104
David King
17500
22
Procurement
105
Eliza Roy
15500
25
Admin
106
Flash God
15500
23
Others
3)     The UPDATE command can be used to modify information contained within a table, either in bulk or individually. Each year, our company gives all employees a 3% cost-of-living increase in their salary. The following SQL command could be used to quickly apply this to all of the employees stored in the database:
              UPDATE Employees SET EmpSalary = EmpSalary * 1.03

New Structure of the table after update the salary of the each employee in the Employees table –
EmpId
EmpName
EmpSalary
EmpAge
Department
100
Amit Gupta
15450
32
IT
101
Anil Sharma
15965
22
Finance
102
Bill Smith
18540
25
HR
103
Chris Gray
18025
28
Sales & Marketing
104
David King
18025
22
Procurement
105
Eliza Roy
15965
25
Admin
106
Flash God
15965
23
Others
4)     The DELETE command with a WHERE clause can be used to remove his record from the Employees table:
DELETE FROM Employees WHERE EmpId=106

New Structure of the table after run the delete command for empid =106 in the Employees table –
EmpId
EmpName
EmpSalary
EmpAge
Department
100
Amit Gupta
15450
32
IT
101
Anil Sharma
15965
22
Finance
102
Bill Smith
18540
25
HR
103
Chris Gray
18025
28
Sales & Marketing
104
David King
18025
22
Procurement
105
Eliza Roy
15965
25
Admin
SQL Data Control Language (DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges. Privileges are of two types,
           
            System: This allows the user to CREATE, ALTER, or DROP database objects.
           
            Object: This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.
            
            DCL defines two commands such as -
GRANT to grant a privilege to a user and the syntax will be like as –
            GRANT privilege_name  ON object_name
            TO {user_name |PUBLIC |role_name}
            [WITH GRANT OPTION];
privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.            
object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.            
user_name is the name of the user to whom an access right is being granted.                user_name is the name of the user to whom an access right is being granted.
            PUBLIC is used to grant access rights to all users.
            ROLES are a set of privileges grouped together.

REVOKE to revoke (remove) a privilege from a user and the syntax will be like as –
           REVOKE privilege_name
           ON object_name FROM {user_name |PUBLIC |role_name}
Note:
A Primary Key constraint and a Clustered Index is not really the same in SQL Server. By default SQL Server enforces the Primary Key constraint with a Unique Clustered Index.

2 comments:

  1. Databsae is a storehouse of all informations about am organization,s transactions.

    ReplyDelete
  2. Great article...keep posting

    ReplyDelete

Popular Posts