> Code Storage: SELECT

Code Storage

A Technical blog for those who want to gain knowledge about SQL,MySQL,Data Science and real time query

Responsive Ads Here

Advertisement

Showing posts with label SELECT. Show all posts
Showing posts with label SELECT. Show all posts

Monday, January 13, 2020

how to find nth highest salary| max salary different ways ?

nth highest salary,highest salary,max,
                                                     1.how to find nth highest salary| max salary
                                                                                                       different ways

How to find max salary
 Syntax:- select max(col_name) from table_name
 Example:select max(salary) from employee   

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 


Monday, January 6, 2020

What is RDBMS |Subset of SQL

RDBMS,DBMS,SQL,Subset
                                                   1.What is RDBMS and Subset of SQL


RDBMS


  • RDBMS stands for Relational Database Management System.
  • RDBMS is the basis fro SQL, and for all modern databse system such as MS SQL Server,IBM DB2,Oracle,MySQL,and Microsoft Access.
  • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Subset of SQL
SQL command can be classified into group on their nature.they are
  1. Data Definition Language.
  2. Data Manipulation Language.
  3. Data Control Language.
  4. Transaction Control Language.

1.Data Definition Language:-

                                                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;


                    
         

                                               

SEVER TYPE IN SSMS ( Sql Server Managment studio)

When you install the SQL Server management studio and then you want to connect with the SQL Server then there are four types of SQL servers ...