Alter,Where,Order By and SQL alias in SQL Server

Alter Command:

    - When we are required to change the structure of the existing table that time we use Alter command.

    - following operation we can perform using alter command

• Able to add one or more columns using the ADD clause.

• Also we can modify  data type,constraint, default value using MODIFY clause.

• Able to remove one or more column using DROP clause

1. Add column
   Syntax:
     ALTER TABLE tbl_name ADD new_columm_name data_type column_constraint [AFTER existing_column];
   Example:
       ALTER TABLE employee ADD joining_date Date;
   
   - To add multiple column using single statement:
   
   Syntax:
   ALTER TABLE tbl_name
   ADD new_columm_name data_type column_constraint [AFTER existing_column],
   ADD new_columm_name data_type column_constraint [AFTER existing_column]

        ALTER TABLE employee 

       ADD salary Double [After emp_name],

       ADD address varchar(100) [After emp_name];

1. Modify column
   Syntax:
   ALTER TABLE tbl_name
   Alter column column_definition;
   
   Example:

      ALTER TABLE employee1 

Alter column emp_mobileNumber varcher (20);

1. Drop one or more column
   Syntax:
   ALTER TABLE tbl_Name
   DROP COLUMN column_name1, DROP COLUMN column_name2;
   
   Example:
   ALTER TABLE employee1
   DROP COLUMN emp_name, DROP COLUMN emp_email;

Where Clause:

-   By using where clause we are able to filter the table records/rows based on specified condition.

Use Where clause with Select statement

     1. Get IT department employee

     2. Get admin department employee

     3. Get Shubham name emp details:

Use where clause with Update statement

    1. Update mobile number of employee

 Whose id is 4

   2. Update email of employee whose id is 5

Use where clause with Delete statement

    1. Delete match mobile number record

    2. Delete id == 4 record

Order By Clause:

• Used to sort the records/rows return by the select statement

Syntax

SELECT 

    select_list

FROM

    table_name

ORDER BY 

    sort_expression [ASC | DESC];

Example:

SELECT

employee_id,

first_name,

last_name,

hire_date,

salary

FROM

employees

ORDER BY

first_name;

SQL TRUNCATE TABLE

• To delete all data from a table, you use the DELETE statement without a WHERE clause. For a big table that has a few million rows, the DELETE statement is slow and not efficient.

TRUNCATE TABLE table_name;

SQL Alias:

It allows you to assign a table or a column a temporary name during the SQL Query execution . There are two types of aliases: table and column aliases.

Column alias:

Syntax:

column_name AS alias_name

Example

SELECT 

    Emp_name AS Name, 

    salary, 

    salary * 1.1 AS new_salary

FROM

    Employees;

Table Alias:

Syntax:
table_name AS table_alias

Example:

SELECT 

    employees.first_name, 

    employees.last_name

FROM

    employees;

SELECT 

    e.first_name, 

    e.last_name

FROM

    employees AS e;