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.