Month: March 2019

Indexes in SQL Server

An index in a database is pretty much similar to an index in a book which helps us to find a piece of data in an efficient manner. For example, let say you want to find customers from Auckland city. Now if we have an index on city column then SQL Server can find all … Continue reading Indexes in SQL Server →

Weather Observation Station 3

Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order but must exclude duplicates from your answer. FieldTypeIDNUMBERCITYVARCHAR(20)STATEVARCHAR(20)LAT_NNUMBERLONG_WNUMBER SELECT DISTINCT City FROM Station WHERE Id % 2 = 0; As per logical query processing, the query will start from the FROM clause with Station … Continue reading Weather Observation Station 3 →

Japanese Cities’ Attributes

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN. SELECT Id, Name, CountryCode, District, Population FROM City WHERE countryCode = 'JPN'; The query to get all attributes from the CITY table for which the COUNTRYCODE is JPN is very simple. According to logical query processing, the query … Continue reading Japanese Cities’ Attributes →

Constraints – SQL Server

SQL Server constraints are very useful and help us to achieve data integrity. There are two types of constraints. Default ConstraintsTable Constraints Default Constraint It's a column level constraint. It helps to set a default value for a column if the value for that column is not provided while insertion of the row. It makes … Continue reading Constraints – SQL Server →

NULL in T-SQL

In T-SQL NULL represents the missing value. This means if the value for a column is not provided while row insertion then the NULL marker will be used in that column(if the column is nullable). Note that NULL is not a value. Various components of T-SQL language treat NULL in a different way. NULL can … Continue reading NULL in T-SQL →

Select By ID

Query all columns for a city in the CITY table with the ID 1661. The CITY table is described below. SELECT Id, [Name], CountryCode, District, Population FROM City WHERE Id = 1661; As per the logical query processing phase, our query will start from the FROM clause. The query will take the CITY table as … Continue reading Select By ID →

Revising the Select Query II

Query the names of all American cities in CITY table with a population greater than 120000. The CountryCode for America is the USA. The CITY table: FieldTypeIDNUMBERNAMEVARCHAR(20)COUNTRYCODEVARCHAR(20)DISTRICTVARCHAR(20)POPULATIONNUMBER SELECT Name FROM City WHERE population > 120000 AND CountryCode = 'USA'; As per logical query processing, we'll start with the FROM clause. The query takes the CITY … Continue reading Revising the Select Query II →

Revising the Select Query I

Query all columns for all American cities in CITY with populations larger than 100000. The CountryCode for America is the USA. FieldTypeIDNUMBERNAMEVARCHAR(17)COUNTRYCODEVARCHAR(3)DISTRICTVARCHAR(20)POPULATIONNUMBER SELECT Id, Name, CountryCode, District, Population FROM City WHERE population > 100000 AND countrycode = 'USA'; Explanation According to logical query processing, our query will start from the FROM clause and will use … Continue reading Revising the Select Query I →