Inserting data into SQL table

Go through Create table script in SQL post to know about how to create the table.

In order to to insert the data into table we will use INSERT command.
Now, we are going to insert data into Employee table that we have created in Create table script in SQL post.

There are alternative ways to insert data into table:
Alternate1:
  INSERT INTO dbo.Employee 
  (
[EmpID]
,[FirstName]
,[LastName]
,[Salary]
  )
  VALUES 
  (
1
,'Anil'
,'Kumar'
,10000
  )
This is the best way to insert data into our table.
Because with this script we can identify which data we are inserting into which column.
We can also change the order of columns in insert script and can insert data accordingly.
Alternative2:
  INSERT INTO dbo.Employee 
  VALUES
  (
1
,'Anil'
,'Kumar'
,10000
  )
In this way we should know what is the ordering of columns inside the table in advance. 
As per the ordering of the columns it will insert the data into table.
Alternative3:
INSERT INTO dbo.Employee 
SELECT 
[EmpID]
,[FirstName]
,[LastName]
,[Salary] 

FROM dbo.Employee 

Inserting multiple rows at a time:
If you want to insert multiple values at a time them you can do it as shown below:
INSERT INTO dbo.Employee 
  VALUES
  ('1', 'Anil', 'Kumar', 10000)
  ,('2', 'Suresh', 'T', 10000)
  ,('3', 'Kumar', 'Anil', 10000)

In this way, you are selecting all the data available in Employee table and then reinserting it into Employee table.
Here i have taken the example of selecting data from Employee table but you can select from any table and insert it into Employee table.

If any column in Nullable then you can choose to ignore that column in your script, it will automatically insert NULL values in those columns but we should definitely insert some data into NOT NULL columns.

Create table script in SQL

In order to create table in SQL we have to use CREATE keyword.

To create a table we need to have below details in hand with us:

a. You should know what should be the table name

b. What are all the columns required in your table

c. Data types of all those columns

d. Constraints of every column, we will learn about different constraints available in SQL in separate post. As of now I will mention whether column accept NULL values or not

Syntax:

CREATE TABLE <<tableName>>

(

<<columnName>> <<dataType>> <<constraint>>

)

Find below Employee table creation script:

CREATE TABLE Employee

(

     EmpID INT NOT NULL

    ,FirstName NVARCHAR(250) NOT NULL

    ,LastName NVARCHAR(250) NULL

    ,Salary MONEY

)

On execution of above script table will be created in the database in below format:

EmpID

FirstName

LastName

Salary

If we see the above script, we have declared EmpID and FirstName as NOT NULL and other two columns as NULL that means those two fields are not mandatory to fill.

In SQL if we don't mention anything then the default constraint will be taken as NULL that is the reason for Salary field the default constraint will be NULL.

SQL Server - DDL, DML, DQL, DCL and TCL

Today we are going to learn different categories of languages available in SQL:

We have four categories of languages in SQL:

DDL – Data Definition Language:

It is used to modify the structure of the table

      CREATE, ALTER, DROP, TRUNCATE

There are too many posts where we are seeing whether TRUNCATE is DDL/DML command.

Its DDL command because

a. It internally performs DROP and CREATE of the table

b. If you want to TRUNCATE the table you require ALTER permissions not only Write permissions

c. As it is DDL command, it will reset IDENTITY property of the table

DML – Data Manipulation Language:

It is used to manipulate the data available in table

      INSERT, UPDATE, DELETE

DQL – Data Query Language:

It is used to fetch the data from table

     SELECT

DCL – Data Control Language:

It is used to create roles, permissions, and referential integrity and also to control access to database by securing it

     GRANT, REVOKE

TCL – Transaction Control Language:

It is used to control/manage different transactions performed on a table

     COMMIT, ROLLBACK

Querying Database

Querying Database:

Today we will see how can we write different queries to get the information from Employee table.

Find below the list of columns available in Employee table:

ColumnName

EmpID

EmpName

FirstName

LastName

Salary

Location

a. Select all the employees whose FirstName starts with a and salary greater than $10000

SELECT

      EmpName

FROM

      Employee

WHERE

      Salary > 10000

      AND FirstName LIKE 'a%'

Explanation:

To identify all the employees whose name starts with 'a' we have to use regular expression.

Here, 'a%' tells the query that FirstName should start with 'a' and remaining letters can be anything

LIKE Usage:

In SQL we have to use LIKE keyword whenver we are using regular expressions, if we use '=' operator then SQL will search for those employees whose name is 'a%' but not for those whose name starts with 'a'.

AND Usage:

We need only those employees who satisfy both the conditions that is the reason we are using AND operator over here, if our requirement is to get those employees whose salary great than 10000 or FirstName starts with 'a' then we can use OR in place of AND

b. Get the employee names whose FirstName is not available

SELECT

      EmpName

FROM

      Employee

WHERE

      FirstName IS NULL

Explanation:

Here IS NULL tells that FirstName details are not provided by employee

Try writing queries for below Questions:

a. Write a query to get the list of employees whose name is 'Arun' and they should live in Hyderabad

b. Get the list of EMPIDs whose salary is less than 20000 and LastName is not mentioned or FirstName is not mentioned