Syntax:- select max(col_name) from table_name

How to find 2nd highest salary

Example:- select max(salary) from employee

                   where salary<(select max(salary) from employee)

How to find top 2 highest salary

Example:- select distinct top 2 salary

                  from employee

                  order by salary desc

How to find nth highest salary using sub query

Example:- select top 1 salary from 

                  (select distinct top 1 salary

                   from employee

                   order by salary desc)

                   result

                   order by salary 

Here we used the sub query to find the nth highest salary but above query gives the top first salary but you needs 2nd,3nd,4nd,5nd...............salary we need to modify the query like this.you need 3nd highest salary then put  distinct top 3.....

 Example:-select top 1 salary from 

                   (select distinct top 1..(2nd,3nd,4nd,5nd....nth) salary

                   from employee

                   order by salary desc)

                   result

                   order by salary 

                                                In SQL,DDL means Data Definition Language. The SQL Server DDL Commends are used to create and modify the structure of a database and database objects.Important commands and operation in data definition language.

1. Create commands
2. Alter commands 
3. Drop commands
4. Truncate commands
5. Rename commands

1.Create commands:-

                                   To create database and database object.

Syntax:- Create table Table_Name

                (

                  column_Name1     Datatype (Size),

                  column_Name2     Datatype(Size),

                  column_Name3     Datatype(Size)

                 );

Example:- Create table xyz1

                   (

                      student ID              int,

                      student_name        varchar(20),

                      student_address    varchar(10),

                      student_age            int

                   );

              

2.Alter commands:-

                                   To modify existing database objects.

Syntax:- Alter table Table_Name add 

                (

                column_Name4   Datatype(Size),

                column_Name5   Datatype(Size),

                 column_Name6   Datatype(Size)

               );

Example:- Alter table xyz1 add

                   (

                    student_gender   char(5),

                    student_weight    int,

                    student_ph.no      double(20)

                  );

                

3.Drop commands:-

                                  To drop database and database objects.

syntax:- Drop table Table_Name

Example:- Drop table xyz1;

4.Truncate commands:-

                                   To remove all records from a table.

Syntax:-Truncate table Table_Name

Example:- Truncate table  xyz2;

5.Rename commands:-

                                   To rename the database objects.

Syntax:- Exec sp_rename 'table_name.old_column_name','new_column_name';

                here Exec is a execute.

Example:-Exec sp_rename 'xyz1.student_Name', 'student_fullname';

2.Data Manipulation Language:-

                                                      The Data Manipulation Language is work on the data or record (row). it is a subset of the SQL.

1. Select
2. Insert
3. Update
4. Delete

1.Select:-

              Retrives data from a tables.

Syntax:- Select  column1,column2....

                from Table_Name;

Example:- Select student ID, student_fullname

                   from xyz1;

Example:- Select * 

                   from xyz1;

NOTE:- Asterisk (*) symbol is used to represent the all columns in the table.so here we are fetching all the columns from the table.

2.Insert:- 

               Insert data into a table.

Syntax:- Insert into Table_Name ( column1_name,column2_name,column3_name........)

                values ( col1_values,col2_values,col3_values);

Example:- Insert into xyz2 (id,name,city)

                   values (1,'abd','delhi');

NOTE:- here Table_name is not mandatory (compulsory). directly put the values and gives                     the values. like this

Syntax:- Insert into Table_Name values ( col1_values,col2_values,col3_values);

Example:- Insert into xyz1 values ( 2,'name','mumbai');

                       

3.Update:- 

                 Update a existing record into a table.

Syntax:-  Update table Table_name

                 set column_Name = value1,column_Name=value2...
                 where column_Name= value;
Example:- Update table xyz1
                   set student_age = 20,student_Name ='suman'
                   where student ID =2;

4.Delete:- 

               SQL delete command is the delete the existing record into a table.
Syntax:- Delete from Table_Name [where condition]

Example:- Delete from xyz2
                   where student ID =5; -------> particular record will be deleted.

Example:- Delete from xyz2;------> all recorded will be deleted.

3.Data Control Language:-

                                            Important command and operation in data control language.

1. Grant
2. Revoke

1.Grant:-

               To provide access on the database object to the users.
                                                   OR
                Used to provide any user access privileges or other privileges for the database.
Syntax:-
              Grant create session to User_Name;
Example:-
                 GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

2.Revoke:-

                 To remove user access right to the database objects.
                                                     OR
                  Used to take back permission from the User.
Syntax:- Revoke create table to User_Name;

Example:-
                REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4.Transaction Control Language:-

                                                        TCL  command can only use with DML command like Insert,Update,Delete Only.These operations are automatically committed in the database that is why they can not be used while creating tables or dropping them. here are some TCL commands command.

1. Commit
2. Rollback
3. Save point

1.Commit:-

                Commit commands is used to save all the transaction to the database.
Syntax:-  Commit;

Example:- Delete from xyz1
                   where student ID =2
                   commit; 

2.Rollback:-

                    Rollback command is used to undo transaction that have not only been saved to the  database.
Syntax:- Rollback;

Example:- Delete from xyz2
                  where student ID =3
                  Rollback;

3. Save Point:-

                        It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:- SAVE POINT savepoint_name;

Example:- save point xyz3;