SQL Training: Rollup and Cube(OLAP/SSAS)

--Rollup

select st.[Group],st.[Name],st.CountryRegionCode,
       sum(st.SalesYTD) current_year_sales,
   sum(st.SalesLastYear ) last_year_sales
from AdventureWorks2012.Sales.SalesTerritory as st
Group by st.[Group],st.[Name],st.CountryRegionCode
with Rollup

--cube

select st.[Group],st.[Name],st.CountryRegionCode,
       sum(st.SalesYTD) current_year_sales,
   sum(st.SalesLastYear ) last_year_sales
from AdventureWorks2012.Sales.SalesTerritory as st
Group by st.[Group],st.[Name],st.CountryRegionCode
with cube



These two functions are comparable to OLAP transactions.

Comments

Post a Comment

Popular posts from this blog

SQL Training: Exercises on Stored Procedures and Functions

Exercises on Stored Procedures and Functions: 1. Create a function that should take two inputs those are product id and location id and should return stock level. Note: Consider product Inventory table in adventurworks database for this requirement. 2. Create a function that should take input as store id and returns the columns ProductID, Name, and the aggregate of line total for each product sold to the store. Note: consider product, sales order details, sales order header and customer tables for this requirements. 3. Someone deleted few records from a table which has an IDENTITY column, so need to create stored procedures to find missing sequential numbers. Note: for this requirement create sample table with identity column and insert some data and delete few records from table. 4. Sales order header table in adventureworks database as follows Requirement: Need to create stored procedure that should take sortdate as argument (varchar datatype) then it should displ...
Read more

SQL Training: Exercise on Clauses,String and Mathematical functions,Date Time and Aggregate Functions

Exercise 3: 1. WAQ to display Tool Designer designation employee’s details. 2. WAQ to display Design Engineer and Tool designation employee’s details. 3. WAQ to display female market specialist employee’s details. 4. WAQ to display single female employee’s details. 5. WAQ to display employees whose vocation hours between 40 and 50. 6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave. 7. WAQ to display employees whose job title contain engineer word. 8. WAQ to display employees whose vacation hours contain more than or equal to 40. 9. WAQ to display all employees except tool designer designation employees. 10.WAQ to display employee details whose vacation hours not in range 20 and 40. 11.WAQ to display employee details except tool designer and designer engineer. 12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31. 13. WAQ to...
Read more

SQL Training:Exercises on Tables

Exercises on Tables Exercise 1 1. Create table books with columns Bookid (it should accept numbers between 1 to 2000) Bookname (it should accept Unicode variable length characters) Bookprice (it should accept currency values) 2. Insert this given values into books table BookId: 1, BookName: Reasoning, price: 500 BookId: 2, BookName: c programming, price: 600 BookId: 3, BookName: c++ programming, price: 400 BookId: 2, BookName: .net, price: 500 BookId: 3, BookName: Java, price: 400 BookId: 1, BookName: Sql server, price: 600 3. Create Table which contain Address information with columns Countryname (It should allow 20 Unicode characters only). Statename (It should allow 20 non Unicode characters only). Cityname (It should allow allows 20 non Unicode characters only). 4. Insert only Country name and state name into address table. 5. Insert this giving data at time into Address table Countryname: India, statename: AP, ctname: Hyd Countryname: India, statename: ...
Read more