Showing posts with label Database. Show all posts

Tuesday, January 23, 2024

How to implement server side paging query in ArangoDB database

While reading data from arangodb database if you have large dataset returned from your query result you will be unable to read data from arangodb. In this case you have to use limit operation to limit results in you dataset. The LIMIT operation allows you to reduce the number of results.

Syntax: Two general forms of LIMIT are:

LIMIT count
LIMIT offset, count

Example query:

For a1 IN Asset_Envelop
FILTER a1.updatedDate<@a1_updatedDate
LIMIT 0, 100
RETURN {"assetid":a1.`assetId`, "assetcategorylevel2":a1.`assetCategoryLevel2`, "assetcategorylevel3":a1.`assetCategoryLevel3`, "modelid":a1.`modelId`, "serialno":a1.`serialNo`, "manufacturer":a1.`

manufacturer`, "assetcategorylevel4":a1.`assetCategoryLevel4`, "locationid":a1.`locationId`, "thirdpartyid":a1.`thirdPartyId`, "measureid":a1.`measureId`, "inventoryyear":a1.`inventoryYear`, "manufacturedate":a1.`manufactureDate`, "location":a1.`location`, "count":a1.`count`, "sizelength":a1.`sizeLength`, "sizewidth":a1.`sizeWidth`, "sizeunit":a1.`sizeUnit`, "installdate":a1.`installDate`, "assetstatus":a1.`assetStatus`, "assetcondition":a1.`assetCondition`, "assetname":a1.`assetName`, "assetmaterial":a1.`assetMaterial`, "insulationlocation":a1.`insulationLocation`, "insulationtype":a1.`insulationType`, "insulationcondition":a1.`insulationCondition`, "glazingtype":a1.`glazingType`, "caulkingtype":a1.`caulkingType`, "caulkingcondition":a1.`caulkingCondition`, "weatherstrippingtype":a1.`weatherstrippingType`, "weatherstrippingcondition":a1.`weatherstrippingCondition`, "frametype":a1.`frameType`, "framecondition":a1.`frameCondition`, "additionalconditioncomments":a1.`additionalConditionComments`, "warranty":a1.`warranty`, "warrantystartdate":a1.`warrantyStartDate`, "warrantyenddate":a1.`warrantyEndDate`, "did":a1.`did`}

For a1 IN Asset_Envelop
FILTER a1.updatedDate<@a1_updatedDate
LIMIT 200, 100
RETURN {"assetid":a1.`assetId`, "assetcategorylevel2":a1.`assetCategoryLevel2`, "assetcategorylevel3":a1.`assetCategoryLevel3`, "modelid":a1.`modelId`, "serialno":a1.`serialNo`, "manufacturer":a1.`

The query performs paged query on database and returns limited results which works fine with large dataset also.

Thursday, September 2, 2021

SQL update from one Table to another based on a ID match

SQL update from one Table to another based on a ID match

While working with SQL database you might often require to update one table data based on values of a column value in another table. For this to be done successfully you might require a common id field or foreign key which will match values of both tables to update.

Here is the syntax to perform the task in SQL Server:

UPDATE table1

SET t1.Col = t2.Col

FROM table1 t1

INNER JOIN table2 t2 ON t1.ID = t2.ID;

To demonstrated this let us assume we have following two tables:

Place
===========
PlaceId                int,
name                    nvarchar(200),
ispublished           bit

PlaceDetails
==============
PlaceDetailId       int,
PlaceId                    int,
DetailsDesc            nvarchar(max),
SeoTitle                   nvarchar(max),
SeoDescription        nvarchar(max),
status                      bit

Case 1: Update status field of PlaceDetails from values of ispublished in Place table.

Solution:

UPDATE PlaceDetails

SET d.status = p.ispublished

FROM PlaceDetails d

INNER JOIN Place p ON d.PlaceId = p.PlaceId;

Case 2: Update status field of PlaceDetails from values of ispublished in Place table only for the places that are published.

Solution: You require to add a where condition along with previous query for this purpose.

UPDATE PlaceDetails

SET d.status = p.ispublished

FROM PlaceDetails d

INNER JOIN Place p ON d.PlaceId = p.PlaceId

WHERE p.ispublished='True';

Case 3: Update SeoTitle and SeoDescription field of PlaceDetails for a specific placetypes

Solution: This is a complex implementation of the query that allows you to update a table values based on a complex query result. For this purpose we consider some more tables.

The following query is used to generate SEO Titile and SEO Description information for all of the places of a category:

SELECT p.PlaceId, p.name, 'Book a room in ' + p.name +', '+ c.Name+' | addressschool.com',
p.name +' is a '+ t.Name+' in '+c.Name+', '+n.Name +' that is located in '+d.formatted_address
FROM     Place AS p INNER JOIN
                  PlaceDetail AS d ON p.PlaceId = d.PlaceId INNER JOIN
                  City AS c ON p.CityId = c.Id inner join
                PlaceType t on p.PlaceTypeId = t.Id INNER JOIN
                  Country AS n ON p.CountryId = n.Id
WHERE (p.PlaceTypeId = 1)

Now we use the above query as a inline view in the update statement to update SeoTitle and SeoDescription information in PlaceDetails table:

UPDATE
    PlaceDetail
SET
    PlaceDetail.name = SR.SeoTitle,
   PlaceDetail.url = SR.SeoDescription
FROM
    PlaceDetail pd
INNER JOIN
    (SELECT p.PlaceId, p.name, 'Book a room in ' + p.name +', '+ c.Name+' | addressschool.com' as SeoTitle,
   p.name +' is a '+ t.Name+' in '+c.Name+', '+n.Name +' that is located in '+d.formatted_address as SeoDescription
   FROM     Place AS p INNER JOIN
                      PlaceDetail AS d ON p.PlaceId = d.PlaceId INNER JOIN
                      City AS c ON p.CityId = c.Id inner join
                      PlaceType t on p.PlaceTypeId = t.Id INNER JOIN
                      Country AS n ON p.CountryId = n.Id
   WHERE (p.PlaceTypeId = 1)) SR
ON
    pd.PlaceId = SR.PlaceId;

Hope this will help you.

Any query and comments will be appreciated and answered.

Wednesday, April 7, 2021

What is the difference between primary key and unique key in SQL Server?

How to answer DBMS interview question about the difference between primary key and unique key

difference between primary key and unique key

This is very commonly asked interview question from SQL Server or database. Following is the answer of this question:

1. By default, Primary key creates clustered index in the table but unique key creates non-clustered index.

2. Primary key column does not accept any null values, where as a unique key column accept only one null value.

3. A table can have only one primary key. On the other hand a table can have more than one unique key.

4. Duplicate values are not allowed in primary key where as duplicate value will be accepted if one or more key parts are null

5. The purpose of implementing primary key is to enforce integrity between entities of database, on the other hand the purpose of unique key is to enforce unique data within the table.