StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Data Analysis by SQL - Essay Example

Cite this document
Summary
The aim of the management report "Data Analysis by SQL" is to analyse the current database of a company and to identify any behaviour patterns, in terms of consumer behaviour, products or suppliers. The report also will provide suitable recommendations for improvement based on the findings…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER95.5% of users find it useful
Data Analysis by SQL
Read Text Preview

Extract of sample "Data Analysis by SQL"

Part Data Analysis by SQL The aim of this management report is to analyse the current database of a company and to identify any behaviour patterns, in terms of consumer behaviour, products or suppliers. This will provide the management with valuable insight about the various aspects of the business and enable them to make appropriate decisions and plans for improvement. The report also will provide suitable recommendations for improvement based on the findings. Customer Behaviour: The customers are the most valuable assets for any business. As an initial step, the customer database is segmented to find the most valuable customers. This is carried out by segmenting the customers based on their Recency, Frequency and Monetary value (RFM) of their purchases. Apart from this, the customer and order database are analysed to based on the products purchased, location of the customer, handling employees and the shipping preferences, to identify any patterns and behaviour. Based on the results obtained, it is evident that the highest value customers in terms of the revenue are mainly from Europe, including, Denmark, Germany and France. The total revenue from US customers amounts to $ 56,029, whereas the revenue from France is $ 68,011. By identifying the products bought by the customers, their preferences can be identified. This will enable the firm to create a customer profile and make the appropriate products readily available in the proper locations. It will also be beneficial to identify the most preferred shipping service of the customers. From the database, it is evident that the United Package has been chosen for 326 times, the Federal Express was preferred for around 255 orders, whereas Speedy Express for 249 orders. Hence it is evident that the three shipping services are equally important. Analysis of Products: The orders are analysed based on the products, in order to identify the most preferred and the highest revenue generating products. The high revenue generating categories are also identified and the role of discounts in the orders are also analysed. From the analysis, the highly sold product has been found as Gorgonzoo Telino whereas the product generating the highest revenue is Cote de Blaye. However, when analysing the categories, it is found that the best selling categories in terms of revenue and number of products are Beverages, Dairy products and Confections. It is imperative to note that the least revenue generating revenues include Produce and Grains and Cereals. Also, analysing the discounts applied indicates that there was no algorithm set in deciding discounts and they were applied arbitrarily, based on the situation. Analysis of Suppliers: The suppliers of high demand and high value products are identified in order to formulate a supplier relationship strategy. The high value suppliers are identified by analysing the products that are highly sold and the corresponding suppliers. The discontinued products are also analysed on the basis of suppliers. The high value suppliers identified include, Pavlola Ltd., Plutzer and Gai pasturage. It is interesting to note that products from two of these suppliers have been discontinued. It should also be noted that the products in high demand, in terms of quantity are supplied by Formaggi Fortini and Pavlola. Hence these suppliers should be given high importance and steps should be taken to improve relationships with these suppliers. Recommendations: The company can strengthen the relationship with the right customers by providing more value and offers. For instance, the top three high value customers identified are Simon Bistro, Drachenblut and Blondel pere et fils. Stronger relationships can be maintained with them by identifying their most ordered products and offering discounts for those items. Currently, there is no effective customer relationship management in place and hence importance has to be given to implementing this value – based customer retention approach. This is important, as acquiring a new customer is six times costlier than retaining an existing one. The less performing categories and products can be analysed in terms of the profit to the firm. This will give a clearer picture about these items and help make the decision whether these products and categories can be discontinued. This will enable the firm to focus on the revenue generating and high demand products as this increase the total value of the firm. A more detailed and clear study can be done if the profit margin for each of the products is also specified in the product database. This will help in analysing the income potential of each product and help make a better decision. It is evident from the analysis of the orders that the discounts were applied solely based on the situation and ordering quantities and there is no business logic involved. The firm can bring in structured discount programs. This will create better marketing opportunities. The business model of the firm is ‘intermediary’ and hence it is very important that the company maintains a strong supplier as well as customer relationship. As discussed, the relationships with the high value suppliers along with suppliers whose products are in demand are to be strengthened and effective communication and logistical facilities should be implemented. On the other hand, the low value suppliers, for instance, Tokyo Traders, whose products are not in great demand and also the revenues, are lesser. It should also be noted that already one of the products of Tokyo traders has been discontinued. Conclusion: The database of the firm was analysed and the matching patterns and key facts are identified. This has shed light on various facts including ways to improve the revenue, marketing and also customer and supplier relationship. Finally, suitable recommendations are made based on the analyses. Thus SQL techniques are utilized to analyse the database to make business level decisions. SQL Statements Consumer Behaviour: The following view is initially created: Create View vw_Orders as select * from orderdetails left outer join orders on orders.orderID=orderdetails.orederID go 1. Monetary Value of Purchases: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_Orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_Orders.CustomerID) as Country, sum([unit price]*quantity) as Amount from vw_Orders group by CustomerID order by Amount desc 2. Frequency of Purchase: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=orders.CustomerID) as Country, count(*) as NOTime from orders group by CustomerID order by NOTime desc 3. Recency: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=orders.CustomerID) as Country, max([order date]) as [order date] from orders group by CustomerID order by [order date] desc 4. Products Purchased: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_orders .CustomerID) as Country, productID, (select [Product Name] from Products where Products.ProductID=vw_orders.ProductID) as [Product Name], sum(Quantity) as Quantity from vw_orders group by productID, CustomerID order by CustomerID, Quantity desc 5. Analysis based on Location: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_orders .CustomerID) as Country from vw_Orders group by CustomerID order by Country 6. Customer and Employee Relations: select customerID, (select [Company Name] from Customers where Customers.CustomerID=Orders.CustomerID) as [Company Name], employeeid, (select [First Name] from employees where employees.employeeid=Orders.employeeID) as [Employee Name], count(*) as NOTime from Orders group by customerID, employeeid order by NOTime desc, customerID 7. Customers and shipping details: select customerID, (select [Company Name] from Customers where Customers.CustomerID=Orders.CustomerID) as [Company Name], [ship via], count(*) as NOTime, (select [Company Name] from shippers where shippers.ShipperID=Orders.[ship via]) as [Ship Via] from Orders group by customerID, [ship via] order by NOTime desc, customerID 8. Discounts and Consumer Behaviour: select productid, (select [product name] from products where products.productid=vw_orders.productid) as [product name], (select customerid from customers where customers.customerid=vw_orders.customerid) as customerid, (select [company name] from customers where customerid=(select customerid from customers where customers.customerid=vw_orders.customerid)) as [customer name], sum(quantity) as quantity , discount from vw_orders where discount0 group by discount, customerid, productid order by discount desc, customerid, quantity desc Products: 1. Most Number of Products Sold: Select [OrderDetails].[ProductID], (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], sum([OrderDetails].[Quantity]) as [Quantity], sum([OrderDetails].[Unit Price]*[OrderDetails].[Quantity]) as [Amount] from [OrderDetails] group by [OrderDetails].[ProductID] order by [Quantity] desc 2. High Revenue Product: Select [OrderDetails].[ProductID], (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], sum([OrderDetails].[Quantity]) as [Quantity], sum([OrderDetails].[Unit Price]*[OrderDetails].[Quantity]) as [Amount] from [OrderDetails] group by [OrderDetails].[ProductID] order by [Amount] desc 3. Category with the most number of Products ordered: The following view is created: create view vw_CategorySum as select category, (select sum(quantity) from orderdetails where orderdetails.productID=products.productID) as Quantity, (select sum([unit price]*quantity) from orderdetails where orderdetails.productID=products.productID) as Amount from products go select category, (select [category name] from Categories where category=Categories.categoryid) as CategoryName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_CategorySum group by category order by Quantity desc 4. High Revenue Product and Category: select category, (select [category name] from Categories where category=Categories.categoryid) as CategoryName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_CategorySum group by category order by Amount desc 5. Analysis of Discount: select productid, (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], discount, sum(Quantity) as Quantity from orderdetails group by productid,discount order by discount asc , Quantity desc 6. Analysis of Discontinued Products: select ProductID, (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], Quantity, ([Unit price]*Quantity) as Amount, Discount, [Order Date] from orderdetails left outer join orders on orders.orderid=orderdetails.orederid where exists (select productid from products where discontinued=1 and orderdetails.productid=productid) order by productID, [Order Date] Suppliers: The following view is initially created: create view vw_SupplierSum as select supplier, (select sum(quantity) from orderdetails where orderdetails.productID=products.productID) as Quantity, (select sum([unit price]*quantity) from orderdetails where orderdetails.productID=products.productID) as Amount from products go 1. Suppliers of High Demand Products: --Sort by Quantity select supplier, (select [company name] from Suppliers where supplier=Suppliers.supplierid) as SupplierName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_SupplierSum group by supplier order by Quantity desc --Sort by Amount select supplier, (select [company name] from Suppliers where supplier=Suppliers.supplierid) as SupplierName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_SupplierSum group by supplier order by Amount desc 2. Analysis of Discontinued Products: create view vw_DiscProdSupplier as select supplierid, [company name], productid, [product name] from suppliers left outer join products on discontinued=1 and supplier=suppliers.supplierid where productid is not null go Discontinued products list select * from vw_DiscProdSupplier; Active Products of suppliers who discontinued their products: select vw_DiscProdSupplier.supplierid, vw_DiscProdSupplier.[company name], products.productid, products.[product name] from products left outer join vw_DiscProdSupplier on vw_DiscProdSupplier.supplierid=products.supplier where supplierid is not null order by supplierid, products.productid 3. High Value Suppliers: select productid, (select [product name] from products where products.productid=orderdetails.productid) as [product name], (select supplier from products where products.productid=orderdetails.productid) as supplierid, (select [company name] from suppliers where supplierid=(select supplier from products where products.productid=orderdetails.productid)) as [supplier name], sum(quantity) as quantity from orderdetails group by productid order by quantity desc 4. Discounts and Suppliers select productid, (select [product name] from products where products.productid=vw_orders.productid) as [product name], (select supplier from products where products.productid=vw_orders.productid) as supplierid, (select [company name] from suppliers where supplierid=(select supplier from products where products.productid=vw_orders.productid)) as [supplier name], sum(quantity) as quantity , discount from vw_orders where discount0 group by discount, productid order by discount desc, supplierid, quantity desc Read More
Cite this document
  • APA
  • MLA
  • CHICAGO
(“Data Analysis by SQL Essay Example | Topics and Well Written Essays - 1750 words”, n.d.)
Data Analysis by SQL Essay Example | Topics and Well Written Essays - 1750 words. Retrieved from https://studentshare.org/management/1553902-data-analysis-by-sql
(Data Analysis by SQL Essay Example | Topics and Well Written Essays - 1750 Words)
Data Analysis by SQL Essay Example | Topics and Well Written Essays - 1750 Words. https://studentshare.org/management/1553902-data-analysis-by-sql.
“Data Analysis by SQL Essay Example | Topics and Well Written Essays - 1750 Words”, n.d. https://studentshare.org/management/1553902-data-analysis-by-sql.
  • Cited: 0 times

CHECK THESE SAMPLES OF Data Analysis by SQL

Data Analysis for National Treasure Online Shop

The class diagram has three important classes that are Product, Customer, and Purchase Order along with three other classes as Product category, Line product, and Payment detail.... The various attributes of the… Every product has only one product category, therefore the association between the Product class and Product category class is one to one, i....
4 Pages (1000 words) Essay

Computer Attacks and Their Counter Measures

hellip; Without proper security measures and control, your data might be prone to an outside attack.... Some of these attacks are passive, in that there is the monitoring of the information, while others can be termed as active implying that the data within the system is manipulated with the intent to destroy or corrupt the network or data itself.... Once accessed, the invader can pretend to be the official user and freely access and manipulate data as they please....
6 Pages (1500 words) Essay

Security of Big Data

The paper "Securing of Big data" explores firms must be aware that certificate authority servers are vulnerable to data loss....  … Big data is defined as “the ability for an organization to take multiple pieces of information the exponential growth and availability of data, both structured and unstructured.... rdquo; Big data means to clean raw pieces of information to get a better picture of data....
8 Pages (2000 words) Case Study

The Concert Office Database Server Management

From the paper "The Concert Office Database Server Management " it is clear that in the case of The Concert Office, an object-oriented analysis is a technical approach that would work very well for this type of development of the system.... n the analysis, a stage we figured out that “REUNION” concert was one that could run in multiple venues.... These are scenarios recorded for purposes of having consistency in data.... The consistency that arose from the definitions laid bare by the anticipation of data to be collected here is what leads to the springing up of tables....
8 Pages (2000 words) Case Study

SQL Injection Vulnerabilities

The aim of the paper "sql Injection Vulnerabilities" is to provide comprehensive guidance on the use of the open source techniques and tools for independent identification of common sql injection vulnerabilities simulating the attacker's approaches.... hellip; sql injection is a class of vulnerability that poses extremely high risks in the present threat landscape.... The MITRE Common Weakness Enumeration (CWE) ranked sql injection as first in the list of top 25 most dangerous software errors....
5 Pages (1250 words) Research Paper

Learning Environment For Teaching The SQL Language

The paper "Learning Environment For Teaching The sql Language" discusses the project that presents for student learners with the basics of sql language and does so in a dynamic and technologically forward-looking manner that increases student absorption and retention of knowledge.... he primary objective of this project not to just present student learners with the basics of sql(Structured Query Language) language, but to do so in a dynamic and technologically forward-looking manner that increases student absorption and retention of knowledge....
15 Pages (3750 words) Case Study

IT Employment Market

et, sql Triggers, C#, AJAX, HTML, DHTML, ASP.... et, T-sql CSS... … COMPANY: InformatiqSource: Retrieved from Totaljobs online agency accessed from http://www.... otaljobs.... om/JobSeeking/3rd-Line-IT-Support-Engineer---MCSE--Cisco_job51342352 DetailsThird Line Infrastructure Support Engineer will be expected to perform COMPANY: InformatiqSource: Retrieved from Totaljobs online agency accessed from http://www....
8 Pages (2000 words) Assignment

Employing XML, XQUERY, and SQL Queries Techniques

… The paper “Employing XML, XQUERY, and sql Queries Techniques” is a  brilliant variant of assignment on logic & programming.... Here, the sql command is selecting data from two tables, Orders, and Order details.... The paper “Employing XML, XQUERY, and sql Queries Techniques” is a  brilliant variant of assignment on logic & programming.... Here, the sql command is selecting data from two tables, Orders, and Order details....
11 Pages (2750 words) Assignment
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us