INSERT/UPDATE/DELETE/SELECT into Table SQL

Insert
 Insert statements are used to insert data into tables.

 Syntax

 INSERT INTO Table VALUES( “attribute value1”, “attribute value 2”....);
 INSERT INTO Table(Coumn1,Column2,..) VALUES( “attribute value1”, “attribute
value 2”....);

 Example

 Insert into Customer VALUES(1,'Customer Name1','Male',40,'India')
 Insert into Customer(CustomerID,CustomerName,Gender,Age,Location)
VALUES(1,'Customer Name1','Male',40,'India')


 Update

 UPDATE statement helps in modifying database records

Syntax

 UPDATE Table set attribute2 = “new value” WHERE attribute1 = “xyz”;

 Example

 Update Customer set age=30 where CustomerID=100


Delete

 Delete statement helps in deleting database records

Syntax
 DELETE FROM Table WHERE attribute1 = “xyz”;

Example
 Delete from Customer where CustomerID=100;

Select

Select statements help in selecting data from the database

 Syntax
 SELECT * FROM Table ;

 Example
 SELECT * FROM Customer

 Data can be retrieved from the database using different Select options which have been
discussed below

Select statement with projections 

 Selecting only particular columns from the database

 Syntax
 SELECT attribute1,attribute2 FROM Table ;

 Example
 SELECT CustomerID,CustomerName from Customer

Select statement with Distinct

 Shows the column values by removing the duplicate values.

 Syntax
 SELECT DISTINCT attribute1 FROM Table ;

 Example
 Select distinct Location from Customer

Select statement with String Functions

 String functions such as Upper,Lower,Substring,CharIndex can be used in the Select statements

 Syntax
 SELECT UPPER( attribute1), LOWER( attribute1) FROM Table;

Example
SELECT UPPER(Location) from Customer

Syntax
SELECT SUBSTRING(attribute1, 1, CHARINDEX(' ', attribute1)) FROM Table;

The below example will retrieve the first name from the Customer Name in which the First and the Last Name are separated by spaces.

Select SUBSTRING(CustomerName,1, CHARINDEX(' ', CustomerName)) from Customer

 The CharIndex function returns the position of the space character in Customer  Name. Then using the Substring function we can retrieve the first name by providing the  start position and the position returned by CharIndex function.

Select statement with Convert Functions

 The Convert function is sued to display the date columns in the table in different  styles

 Syntax
 SELECT CONVERT(data type, attribute1, format ) FROM Table;

 Example
 The below statement will display the DateOfBirth in mm/dd/yy format.

 Select Convert(varchar(10),DOB,101) from Customer

Select statement with Filters

 Select statements with Filters are used to select records based on condition

SELECT statement with WHERE

 The filter condition is specified using the WHERE Clause.
Syntax

 SELECT attribute1 FROM Table WHERE attribute2 = “xyz” ;
 SELECT attribute1 FROM Table WHERE attribute2 = “xyz” AND attribute3 = “pqr”;

Example
 Select CustomerName from Customer where CustomerID > 100;
 Select CustomerName from Customer where CustomerID > 100 and CustomerID <200;

 SELECT statement with WHERE and IN/NOT IN

 This filter is used for checking for matching and unmatched records given a list of values

Syntax

 SELECT attribute1 FROM Table WHERE attribute2 IN (“pqr”, “xyz”) ;
 SELECT attribute1 FROM Table WHERE attribute2 NOT IN (“pqr”, “xyz”) ;

 Example

 Select CustomerID,CustomerName from Customer Where Location in ('India','US')

 Displays the Customer details whose Location is either India or US.

 Select CustomerID,CustomerName from Customer Where Location NOT in ('India','US')

 Displays the Customer details whose Location is neither India or US.

SELECT statement with WHERE and NULL

This filter is used for selecting records for columns with NULL value.

Syntax

 SELECT attribute1 FROM Table WHERE attribute2 IS NULL ;

 Example

 Select CustomerID,CustomerName from Customer where Location is NULL

 Displays the Customer details whose Location is NULL.

SELECT statement with Order By

 This filter is used to display the records based on column values either in  ascending or descending order

Syntax

SELECT attribute1 FROM Table ORDER BY attribute1;
 SELECT attribute1 FROM Table ORDER BY attribute1 order by desc.

 Example

 Select CustomerID,CustomerName from Customer order by Location.
 Select CustomerID,CustomerName from Customer order by Location desc

SELECT statement with Group By

This filter is used for grouping records based on a column value. The column values used for grouping can be listed using the select statement.

Syntax

SELECT attribute1 FROM Table GROUP BY attribute1;

 Example

 SELECT Location FROM Customer GROUP BY Location;

 In the above example the records in Customer table will be grouped by location values. Suppose we have locations like India,US,UK, the records will be now grouped under  US,UK and India. The column values used for grouping alone can be displayed using  Select. So the above statement will display only US,UK and India.

SELECT statement with Group By and Having

The Having clause is used for displaying the column values used in group by clause based on a condition. In the above example if we want to display the locations which are having a count of more than 10 employees,then we can go for having clause. Having clause has to be used along with aggregate functions. Refer to Aggregate functions documents for understanding on Aggregate functions.

Syntax

SELECT attribute1 FROM Table GROUP BY attribute1 HAVING aggregate_function(attribute1) > value ;

 Example

 SELECT Location FROM Customer GROUP BY Location having  count(Location) > 0

 The aggregate function used is the count function.

 Problem Scenario

 *Insert values into Employee table which has Columns like EmployeeID,Employee Name, Department,Date of Joining.

 *Update the Employee name and Department of a particular Employee

* Delete an Employee based on Employee Id

* Select the Employee based on below criteria
 1.Employees of a Department
 2.Employees not assigned to department
 3.Employees count grouped by department

Approach to solve the problem

 1.Insert the values into Employee table using Insert statement
 2.Update the Department and Employee name using update statement
 3.Delete an Employee using the delete statement by providing the Employeeid in the
 where condition
 4.List the employees of a department using select with where condition
 5.List the employees who have not been assigned to department using null condition
 6.List the count of employees using GroupBy

Solution

 Insert into Employee(1, 'Emp1','Finance','09-09-2000')
 Update Employee set EmpName='Emp2',Department='Accounts' where Empid=2
 Delete from Employee where Empid= 100
 Select * from Employee where Department='Accounts'
 Select * from Employee where Department is null
 Select count(*) from Employee group by Department

Explanation about the solution

 1.Details are inserted using Insert statement.
 2.The Department and EmpName has been updated using update statement based on  Empid
 3.An employee with empid is deleted using delete statements
 4.The employees of Department Account have been listed
 5.The employees not assigned to any department have been listed using null condition
 6.The count of employees of each department is displayed using group by and aggregate  function