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 →

Database Normalization

Database normalization is consist of a set of normalization rules derived from recognized good practices. It ensures that the database is structured to store data in a possible efficient way. It also helps to reduce the amount of duplicate data from the database. First Normal Form(1NF) A table is in 1NF if there is all … Continue reading Database Normalization →

Tables in SQL Server

Tables in database are responsible to store data in form of a grid like structure. Tables consist of Columns and Rows. The columns are the variables for which we want to store values and these values for each column forms a row of data. Each row is a collection of values of columns. Table Types … Continue reading Tables in SQL Server →