Chet  Lubowitz

Chet Lubowitz

1594979280

Part III: Functions, Stored Procedures, Triggers and Pivot Tables

This is the third post in the series of posts on ‘ ‘How to Write Efficient SQL Queries and Statements’

For our demonstration, we will be working on the AdventureWorksLT17 database which is a fictitious, multinational manufacturing company called ‘Adventure Works Cycles’ used for OLTP (Online Transaction Processing) and Real-Time Analytics.

Advanced Data Aggregations

##Window Functions

Window Functions are those functions that are applied to a window or set of rows. They in turn include Ranking, Offset, Aggregate and Distribution functions.

Ranking Functions

**RANK** — Returns the Rank of Each Row within Partition of Results

SELECT TOP(5) SalesOrderID, CustomerID, TaxAmt,
RANK() OVER(ORDER BY TaxAmt DESC) AS RankbyTaxAmt
FROM SalesLT.SalesOrderHeader
ORDER BY RankbyTaxAmt;

Using the ‘RANK’ Function

**DENSE_RANK** — Similar to RANK, except it returns results without gaps, i.e. results that have consecutive rank values.

SELECT SalesOrderID, ProductID, OrderQty,
DENSE_RANK() OVER (PARTITION BY OrderQty
ORDER BY UnitPrice DESC) AS Rank
FROM SalesLT.SalesOrderDetail
ORDER BY OrderQty DESC;

#sql-server #sql-queries

What is GEEK

Buddha Community

Part III: Functions, Stored Procedures, Triggers and Pivot Tables
Vincent Lab

Vincent Lab

1605017502

The Difference Between Regular Functions and Arrow Functions in JavaScript

Other then the syntactical differences. The main difference is the way the this keyword behaves? In an arrow function, the this keyword remains the same throughout the life-cycle of the function and is always bound to the value of this in the closest non-arrow parent function. Arrow functions can never be constructor functions so they can never be invoked with the new keyword. And they can never have duplicate named parameters like a regular function not using strict mode.

Here are a few code examples to show you some of the differences
this.name = "Bob";

const person = {
name: “Jon”,

<span style="color: #008000">// Regular function</span>
func1: <span style="color: #0000ff">function</span> () {
    console.log(<span style="color: #0000ff">this</span>);
},

<span style="color: #008000">// Arrow function</span>
func2: () =&gt; {
    console.log(<span style="color: #0000ff">this</span>);
}

}

person.func1(); // Call the Regular function
// Output: {name:“Jon”, func1:[Function: func1], func2:[Function: func2]}

person.func2(); // Call the Arrow function
// Output: {name:“Bob”}

The new keyword with an arrow function
const person = (name) => console.log("Your name is " + name);
const bob = new person("Bob");
// Uncaught TypeError: person is not a constructor

If you want to see a visual presentation on the differences, then you can see the video below:

#arrow functions #javascript #regular functions #arrow functions vs normal functions #difference between functions and arrow functions

Chet  Lubowitz

Chet Lubowitz

1594979280

Part III: Functions, Stored Procedures, Triggers and Pivot Tables

This is the third post in the series of posts on ‘ ‘How to Write Efficient SQL Queries and Statements’

For our demonstration, we will be working on the AdventureWorksLT17 database which is a fictitious, multinational manufacturing company called ‘Adventure Works Cycles’ used for OLTP (Online Transaction Processing) and Real-Time Analytics.

Advanced Data Aggregations

##Window Functions

Window Functions are those functions that are applied to a window or set of rows. They in turn include Ranking, Offset, Aggregate and Distribution functions.

Ranking Functions

**RANK** — Returns the Rank of Each Row within Partition of Results

SELECT TOP(5) SalesOrderID, CustomerID, TaxAmt,
RANK() OVER(ORDER BY TaxAmt DESC) AS RankbyTaxAmt
FROM SalesLT.SalesOrderHeader
ORDER BY RankbyTaxAmt;

Using the ‘RANK’ Function

**DENSE_RANK** — Similar to RANK, except it returns results without gaps, i.e. results that have consecutive rank values.

SELECT SalesOrderID, ProductID, OrderQty,
DENSE_RANK() OVER (PARTITION BY OrderQty
ORDER BY UnitPrice DESC) AS Rank
FROM SalesLT.SalesOrderDetail
ORDER BY OrderQty DESC;

#sql-server #sql-queries

Kole  Haag

Kole Haag

1602403200

What is NoSQL and How is it Utilized?

Posted on September 25, 2020 by Dean Conally | Updated: October 8, 2020

Category: Tutorials | Tags: CassandraColumnsDatabaseDatabase ManagementDatabase StructureDB2Document StoresDynamic SchemaExtensible Record StoresGraph StoresJSONKey-ValueMSSQLMulti-RowMySQLNodeNode Relationship NodeNon-Relational DatabasesNoSQLNoSQL ModelQueryRowsScalabilitySchema FreeSQLStoresTablesWide-Column

Reading Time: 5 minutes

What is NoSQL?

A NoSQL or a NoSQL Database is a term used when referring to a “non SQL” or “not only SQL” database. NoSQL databases store data in a different format than a traditional relational database management systems. This is why NoSQL is often associated with the term “non-relational” database. Simply put, NoSQL databases are modern databases with high flexibility, blazing performance, and built for scalability. These databases are used when you require low latency and high extensibility while working with large data structures. The versatility of NoSQL is due to the nature of as being unrestricted in comparison to relational databases models such as MySQL or DB2.

SQL vs. NoSQL Comparison

There are multiple differences between SQL and NoSQL database types. In the table below, we will compare some of the most critical variations.

#tutorials #cassandra #columns #database #database management #database structure #db2 #document stores #dynamic schema #extensible record stores #graph stores #json #key-value #mssql #multi-row #mysql #node #node relationship node #non-relational databases #nosql #nosql model #query #rows #scalability #schema free #sql #stores #tables #wide-column

Charity  Ferry

Charity Ferry

1620811310

Interesting 5 Functions and 5 Triggers With PostgreSQL

Once the DVD becomes available for rent, a rental record should be added to the rental table and the reservation record should be removed form the reservation table.

Example 1 : I would like to create a trigger that will delete a customer’s reservation record once the customer’s rents the DVD. In this case, I will create a trigger on the **rental ** table (ON rental) that will be triggered when a row gets inserted (AFTER INSERT) in the rental  table. The trigger will execute delete_reserve() ** function. The delete_reserve() ** function will be responsible of deleting the reservation record once the customer has rented the reserved DVD (EXECUTE PROCEDURE delete_reserve()).

#posgresql #trigger #function #interesting triggers

Gunjan  Khaitan

Gunjan Khaitan

1600221480

Pivot Tables In Excel | Learn Pivot Table In Excel | Pivot Table Tutorial 2020

Microsoft Excel is one of the oldest and most popular tools used to analyze data and create exciting reports. Excel offers a lot of features and functionalities, and Pivot tables are among them. This video on Pivot Table in Excel will help you build basic and advanced summary reports using India census data. You will also learn how to create calculated fields and pivot charts in Excel. Let’s get started.

#excel #pivot-tables #developer