List of country ,State,and cities

 Country Table 

USE [DBDEMO]

GO

/****** Object:  Table [dbo].[Countries]    Script Date: 4/18/2022 12:25:57 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Countries](

[CountryId] [int] IDENTITY(1,1) NOT NULL,

[CountryName] [varchar](50) NULL,

 CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 

(

[CountryId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

insert into Countries values('Afghanistan')

    insert into Countries values('Aland Islands')

    insert into Countries values('Albania')

    insert into Countries values('Algeria')

    insert into Countries values('American Samoa')

    insert into Countries values('Andorra')

    insert into Countries values('Angola')

    insert into Countries values('Anguilla')

    insert into Countries values('Antarctica')

    insert into Countries values('Antigua and Barbuda')

    insert into Countries values('Argentina')

    insert into Countries values('Armenia')

    insert into Countries values('Aruba')

    insert into Countries values('Australia')

    insert into Countries values('Austria')

    insert into Countries values('Azerbaijan')

    insert into Countries values('Bahamas')

    insert into Countries values('Bahrain')

    insert into Countries values('Bangladesh')

    insert into Countries values('Barbados')

    insert into Countries values('Belarus')

    insert into Countries values('Belgium')

    insert into Countries values('Belize')

    insert into Countries values('Benin')

    insert into Countries values('Bermuda')

    insert into Countries values('Bhutan')

    insert into Countries values('Bolivia')

    insert into Countries values('Bonaire, Sint Eustatius and Saba')

    insert into Countries values('Bosnia and Herzegovina')

    insert into Countries values('Botswana')

    insert into Countries values('Bouvet Island')

    insert into Countries values('Brazil')

    insert into Countries values('British Indian Ocean Territory')

    insert into Countries values('Brunei Darussalam')

    insert into Countries values('Bulgaria')

    insert into Countries values('Burkina Faso')

    insert into Countries values('Burundi')

    insert into Countries values('Cambodia')

    insert into Countries values('Cameroon')

    insert into Countries values('Canada')

    insert into Countries values('Cape Verde')

    insert into Countries values('Cayman Islands')

    insert into Countries values('Central African Republic')

    insert into Countries values('Chad')

    insert into Countries values('Chile')

    insert into Countries values('China')

    insert into Countries values('Christmas Island')

    insert into Countries values('Cocos (Keeling) Islands')

    insert into Countries values('Colombia')

    insert into Countries values('Comoros')

    insert into Countries values('Congo')

    insert into Countries values('Congo, Democratic Republic of the Congo')

    insert into Countries values('Cook Islands')

    insert into Countries values('Costa Rica')

    insert into Countries values('Cote D`Ivoire')

    insert into Countries values('Croatia')

    insert into Countries values('Cuba')

    insert into Countries values('Curacao')

    insert into Countries values('Cyprus')

    insert into Countries values('Czech Republic')

    insert into Countries values('Denmark')

    insert into Countries values('Djibouti')

    insert into Countries values('Dominica')

    insert into Countries values('Dominican Republic')

    insert into Countries values('Ecuador')

    insert into Countries values('Egypt')

    insert into Countries values('El Salvador')

    insert into Countries values('Equatorial Guinea')

    insert into Countries values('Eritrea')

    insert into Countries values('Estonia')

    insert into Countries values('Ethiopia')

    insert into Countries values('Falkland Islands -Malvinas')

    insert into Countries values('Faroe Islands')

    insert into Countries values('Fiji')

    insert into Countries values('Finland')

    insert into Countries values('France')

    insert into Countries values('French Guiana')

    insert into Countries values('French Polynesia')

    insert into Countries values('French Southern Territories')

    insert into Countries values('Gabon')

    insert into Countries values('Gambia')

    insert into Countries values('Georgia')

    insert into Countries values('Germany')

    insert into Countries values('Ghana')

    insert into Countries values('Gibraltar')

    insert into Countries values('Greece')

    insert into Countries values('Greenland')

    insert into Countries values('Grenada')

    insert into Countries values('Guadeloupe')

    insert into Countries values('Guam')

    insert into Countries values('Guatemala')

    insert into Countries values('Guernsey')

    insert into Countries values('Guinea')

    insert into Countries values('Guinea-Bissau')

    insert into Countries values('Guyana')

    insert into Countries values('Haiti')

    insert into Countries values('Heard Island and Mcdonald Islands')

    insert into Countries values('Holy See (Vatican City State)')

    insert into Countries values('Honduras')

    insert into Countries values('Hong Kong')

    insert into Countries values('Hungary')

    insert into Countries values('Iceland')

    insert into Countries values('India')

    insert into Countries values('Indonesia')

    insert into Countries values('Iran, Islamic Republic of')

    insert into Countries values('Iraq')

    insert into Countries values('Ireland')

    insert into Countries values('Isle of Man')

    insert into Countries values('Israel')

    insert into Countries values('Italy')

    insert into Countries values('Jamaica')

    insert into Countries values('Japan')

    insert into Countries values('Jersey')

    insert into Countries values('Jordan')

    insert into Countries values('Kazakhstan')

    insert into Countries values('Kenya')

    insert into Countries values('Kiribati')

    insert into Countries values('Korea, Democratic People`s Republic of')

    insert into Countries values('Korea, Republic of')

    insert into Countries values('Kosovo')

    insert into Countries values('Kuwait')

    insert into Countries values('Kyrgyzstan')

    insert into Countries values('Lao People1`s Democratic Republic')

    insert into Countries values('Latvia')

    insert into Countries values('Lebanon')

    insert into Countries values('Lesotho')

    insert into Countries values('Liberia')

    insert into Countries values('Libyan Arab Jamahiriya')

    insert into Countries values('Liechtenstein')

    insert into Countries values('Lithuania')

    insert into Countries values('Luxembourg')

    insert into Countries values('Macao')

    insert into Countries values('Macedonia, the Former Yugoslav Republic of')

    insert into Countries values('Madagascar')

    insert into Countries values('Malawi')

    insert into Countries values('Malaysia')

    insert into Countries values('Maldives')

    insert into Countries values('Mali')

    insert into Countries values('Malta')

    insert into Countries values('Marshall Islands')

    insert into Countries values('Martinique')

    insert into Countries values('Mauritania')

    insert into Countries values('Mauritius')

    insert into Countries values('Mayotte')

    insert into Countries values('Mexico')

    insert into Countries values('Micronesia, Federated States of')

    insert into Countries values('Moldova, Republic of')

    insert into Countries values('Monaco')

    insert into Countries values('Mongolia')

    insert into Countries values('Montenegro')

    insert into Countries values('Montserrat')

    insert into Countries values('Morocco')

    insert into Countries values('Mozambique')

    insert into Countries values('Myanmar')

    insert into Countries values('Namibia')

    insert into Countries values('Nauru')

    insert into Countries values('Nepal')

    insert into Countries values('Netherlands')

    insert into Countries values('Netherlands Antilles')

    insert into Countries values('New Caledonia')

    insert into Countries values('New Zealand')

    insert into Countries values('Nicaragua')

    insert into Countries values('Niger')

    insert into Countries values('Nigeria')

    insert into Countries values('Niue')

    insert into Countries values('Norfolk Island')

    insert into Countries values('Northern Mariana Islands')

    insert into Countries values('Norway')

    insert into Countries values('Oman')

    insert into Countries values('Pakistan')

    insert into Countries values('Palau')

    insert into Countries values('Palestinian Territory, Occupied')

    insert into Countries values('Panama')

    insert into Countries values('Papua New Guinea')

    insert into Countries values('Paraguay')

    insert into Countries values('Peru')

    insert into Countries values('Philippines')

    insert into Countries values('Pitcairn')

    insert into Countries values('Poland')

    insert into Countries values('Portugal')

    insert into Countries values('Puerto Rico')

    insert into Countries values('Qatar')

    insert into Countries values('Reunion')

    insert into Countries values('Romania')

    insert into Countries values('Russian Federation')

    insert into Countries values('Rwanda')

    insert into Countries values('Saint Barthelemy')

    insert into Countries values('Saint Helena')

    insert into Countries values('Saint Kitts and Nevis')

    insert into Countries values('Saint Lucia')

    insert into Countries values('Saint Martin')

    insert into Countries values('Saint Pierre and Miquelon')

    insert into Countries values('Saint Vincent and the Grenadines')

    insert into Countries values('Samoa')

    insert into Countries values('San Marino')

    insert into Countries values('Sao Tome and Principe')

    insert into Countries values('Saudi Arabia')

    insert into Countries values('Senegal')

    insert into Countries values('Serbia')

    insert into Countries values('Serbia and Montenegro')

    insert into Countries values('Seychelles')

    insert into Countries values('Sierra Leone')

    insert into Countries values('Singapore')

    insert into Countries values('Sint Maarten')

    insert into Countries values('Slovakia')

    insert into Countries values('Slovenia')

    insert into Countries values('Solomon Islands')

    insert into Countries values('Somalia')

    insert into Countries values('South Africa')

    insert into Countries values('South Georgia and the South Sandwich Islands')

    insert into Countries values('South Sudan')

    insert into Countries values('Spain')

    insert into Countries values('Sri Lanka')

    insert into Countries values('Sudan')

    insert into Countries values('Suriname')

    insert into Countries values('Svalbard and Jan Mayen')

    insert into Countries values('Swaziland')

    insert into Countries values('Sweden')

    insert into Countries values('Switzerland')

    insert into Countries values('Syrian Arab Republic')

    insert into Countries values('Taiwan, Province of China')

    insert into Countries values('Tajikistan')

    insert into Countries values('Tanzania, United Republic of')

    insert into Countries values('Thailand')

    insert into Countries values('Timor-Leste')

    insert into Countries values('Togo')

    insert into Countries values('Tokelau')

    insert into Countries values('Tonga')

    insert into Countries values('Trinidad and Tobago')

    insert into Countries values('Tunisia')

    insert into Countries values('Turkey')

    insert into Countries values('Turkmenistan')

    insert into Countries values('Turks and Caicos Islands')

    insert into Countries values('Tuvalu')

    insert into Countries values('Uganda')

    insert into Countries values('Ukraine')

    insert into Countries values('United Arab Emirates')

    insert into Countries values('United Kingdom')

    insert into Countries values('United States')

    insert into Countries values('United States Minor Outlying Islands')

    insert into Countries values('Uruguay')

    insert into Countries values('Uzbekistan')

    insert into Countries values('Vanuatu')

    insert into Countries values('Venezuela')

    insert into Countries values('Viet Nam')

    insert into Countries values('Virgin Islands, British')

    insert into Countries values('Virgin Islands, U.s.')

    insert into Countries values('Wallis and Futuna')

    insert into Countries values('Western Sahara')

    insert into Countries values('Yemen')

    insert into Countries values('Zambia')

    insert into Countries values('Zimbabwe')

State Table

USE [DBDEMO]

GO

/****** Object:  Table [dbo].[States]    Script Date: 4/18/2022 12:27:17 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[States](

[StateId] [int] IDENTITY(1,1) NOT NULL,

[CountryId] [int] NULL,

[StateName] [varchar](50) NULL,

 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED 

(

[StateId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[States]  WITH CHECK ADD  CONSTRAINT [FK_States_States] FOREIGN KEY([CountryId])

REFERENCES [dbo].[Countries] ([CountryId])

GO

ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_States]

GO

insert into States values(103,'Andhra Pradesh')

insert into States values(103,'Arunachal Pradesh')

insert into States values(103,'Assam')

insert into States values(103,'Bihar')

insert into States values(103,'Chhattisgarh')

insert into States values(103,'Goa')

insert into States values(103,'Gujarat')

insert into States values(103,'Haryana')

insert into States values(103,'Himachal Pradesh')

insert into States values(103,'Jharkhand')

insert into States values(103,'Karnataka')

insert into States values(103,'Kerala')

insert into States values(103,'Madhya Pradesh')

insert into States values(103,'Maharashtra')

insert into States values(103,'Manipur')

insert into States values(103,'Meghalaya')

insert into States values(103,'Mizoram')

insert into States values(103,'Nagaland')

insert into States values(103,'Odisha')

insert into States values(103,'Punjab')

insert into States values(103,'Rajasthan')

insert into States values(103,'Sikkim')

insert into States values(103,'Tamil Nadu')

insert into States values(103,'Telangana')

insert into States values(103,'Tripura')

insert into States values(103,'Uttar Pradesh')

insert into States values(103,'Uttarakhand')

insert into States values(103,'West Bengal')

City Table 

USE [DBDEMO]

GO

/****** Object:  Table [dbo].[Cities]    Script Date: 4/18/2022 12:28:13 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Cities](

[CityId] [int] IDENTITY(1,1) NOT NULL,

[StateId] [int] NULL,

[StateName] [varchar](50) NULL,

 CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED 

(

[CityId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Cities]  WITH CHECK ADD  CONSTRAINT [FK_Cities_States] FOREIGN KEY([StateId])

REFERENCES [dbo].[States] ([StateId])

GO

ALTER TABLE [dbo].[Cities] CHECK CONSTRAINT [FK_Cities_States]

GO

insert into Cities values(1,'Amaravati')

insert into Cities values(2,'Itanagar')

insert into Cities values(3,'Dispur')

insert into Cities values(4,'Patna')

insert into Cities values(5,'Raipur')

insert into Cities values(6,'Panaji')

insert into Cities values(7,'Gandhinagar')

insert into Cities values(8,'Chandigarh')

insert into Cities values(9,'Shimla')

insert into Cities values(10,'Ranchi')

insert into Cities values(11,'Bengaluru')

insert into Cities values(12,'Thiruvananthapuram')

insert into Cities values(13,'Bhopal')

insert into Cities values(14,'Mumbai')

insert into Cities values(15,'Imphal')

insert into Cities values(16,'Shillong')

insert into Cities values(17,'Aizawl')

insert into Cities values(18,'Kohima')

insert into Cities values(19,'Bhubaneswar')

insert into Cities values(20,'Chandigarh')

insert into Cities values(21,'Jaipur')

insert into Cities values(22,'Gangtok')

insert into Cities values(23,'Chennai')

insert into Cities values(24,'Hyderabad')

insert into Cities values(25,'Agartala')

insert into Cities values(26,'Lucknow')

insert into Cities values(27,'Dehradun `Winter`')

insert into Cities values(27,'Gairsain `Summer`')

insert into Cities values(28,'Kolkata')

ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE() RANKING FUNCTIONS

 CREATE TABLE #tbStudent

(

            StudentId        INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,

            StudentName VARCHAR(50),

            Section            CHAR(1),

            Marks              INT,

);

INSERT INTO #tbStudent

VALUES

('Rohan', 'A',90),

('Priya', 'A',45),

('Aasif', 'A',90),

('Vivek', 'B',58),

('Arjun', 'B',40),

('Sidharth', 'A',100),

('Aalia', 'A',95),

('Kabir', 'B',90),

('Robin', 'B',40),

('Rahul', 'A',30),

('Anju', 'B',32);

SELECT StudentId, StudentName, Section, Marks, ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNumber FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, ROW_NUMBER() OVER (PARTITION BY SECTION ORDER BY Marks DESC) AS RowNumber FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, RANK() OVER (ORDER BY Marks DESC) AS [Rank] FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRank FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, NTILE(3) OVER(ORDER BY Marks DESC) AS [NTILE] FROM #tbStudent

How to display only Decimal number

Using Sql Server 

SELECT FORMAT(2.3332232,'N2')

Using MySql 

SELECT ROUND(325.3547, 2);