/* in Microsoft SQL Server*//*Below answers of the

/* NAME: RIKIN GAJJAR, FILE NAME: GAJJARRGISC9303D3WINERY   DATE: 01/07/2018                                          PURPOSE: • To compose T-SQL statements to create and populate a database using T-SQL standard in SQL Server.            • To compose T-SQL statements to extract both attribute and spatial information from the database.*/USE MASTER;GOIF EXISTS (SELECT name FROM sys.databases WHERE name = N’GAJJARRGISC9303D3WINERY’) DROP DATABASE GAJJARRGISC9303D3WINERY;GO/*Part-1 Create the Winery database in Microsoft SQL Server using SQL statements*/CREATE DATABASE GAJJARRGISC9303D3WINERYGOUSE GAJJARRGISC9303D3WINERY;GO/*Table 1: tblWinery*/CREATE TABLE tblWinery(WineryID VARCHAR(6) PRIMARY KEY,WineryName VARCHAR(30) NOT NULL,StreetNumber VARCHAR(20),StreetName VARCHAR(30),StreetSuffix VARCHAR(20),Addresss VARCHAR (50),City VARCHAR(30),Province VARCHAR(20),PostalCode VARCHAR(7) CHECK (PostalCode LIKE ‘A-Z”0-9”A-Z””0-9”A-Z”0-9’),Country VARCHAR(20) DEFAULT ‘CANADA’,Telephone VARCHAR(14)UNIQUE,Fax NVARCHAR(14),Email VARCHAR(50)UNIQUE,Website VARCHAR(50),YearFounded DATE,Active BIT);GOCREATE INDEX idxWineryIDWineryNameON tblWinery (WineryID, WineryName);GO /*Table 2: Winery Location*/CREATE Table tblWineryLocation(LocationID VARCHAR(6) PRIMARY KEY NOT NULL,WineryID VARCHAR(6) NOT NULL,UTMEasting DECIMAL(10,3),/*In Decimal Degree*/UTMNorthing DECIMAL(10,3),/*In Decimal Degree*/Latitude FLOAT,Longitude FLOAT,VineyardArea DECIMAL(8,2),Elevation DECIMAL(10,2),Accuracy DECIMAL(10,2),CollectorLastName VARCHAR(25) NULL DEFAULT’Gajjar’,CollectorFirstName VARCHAR(25) NULL DEFAULT’Gajjar’, FOREIGN KEY (WineryID) REFERENCES tblWinery (WineryID));GOCREATE INDEX idxWineryIDLocationIDON tblWineryLocation (LocationID, WineryID);GO/*Table 3: WineTypeLookup*/CREATE TABLE tblWineTypeLookup(WineTypeID VARCHAR(4)PRIMARY KEY,WineType VARCHAR (20),WineTypeNotes VARCHAR (MAX));GO/*Table 4: WineVariety */CREATE TABLE tblWineVariety(WineVarietyID VARCHAR(5) PRIMARY KEY,WineVarietyName VARCHAR(30)NOT NULL,WineTypeID VARCHAR(4)NOT NULL,FOREIGN KEY (WineTypeID) REFERENCES tblWineTypeLookup(WineTypeID));GO/*Table 5: RecommendedWineVariety */CREATE TABLE tblRecommendedWineVarietyID(RecommendedWineVarietyID VARCHAR(20) PRIMARY KEY,WineVarietyID VARCHAR(5),GrapeColour VARCHAR(20),WineTypeID VARCHAR(4),VarietyOrigin VARCHAR(20),ClusterType VARCHAR(20),WingedCluster BIT,MinimumBerrySize TINYINT CHECK (MinimumBerrySize BETWEEN 5 and 15),MaximumBerrySize TINYINT CHECK (MaximumBerrySize BETWEEN 10 and 20),BerryShape VARCHAR(20),ClusterCompactness VARCHAR(30),DiseaseDisorder VARCHAR(30),FruitMaturity VARCHAR(30),WoodMaturity VARCHAR(30),ColdHardiness VARCHAR(30),KillingTemperature INT,Recommended BIT,FOREIGN KEY (WineTypeID) REFERENCES tblWineTypeLookup(WineTypeID),FOREIGN KEY (WineVarietyID) REFERENCES tblWineVariety(WineVarietyID),);GO/*Table 6: WineProduct */CREATE TABLE tblWineProduct(WineryID VARCHAR(6) PRIMARY KEY,WineVarietyID VARCHAR(5),VintageYear DATE,NumberofBottlesProduced INT,PricePerBottle MONEY,FOREIGN KEY (WineryID) REFERENCES tblWinery(WineryID),FOREIGN KEY (WineVarietyID) REFERENCES tblWineVariety(WineVarietyID));GO/*Part-2 P2 T-SQL in Microsoft SQL Server*//*Below answers of the questions are based on the D3RawWienry provided*/CREATE DATABASE WineryGOUSE WineryGO/*q1) List the top 3 wineries with the Winery Name and their Website, sorted by winery name in ascending order*/USE WinerySELECT TOP 3 WineryName, WebsiteFROM tblWinery ORDER BY WineryName ASC;GO/*q2) How many wineries are there in the database?  List only a single column name as Total Number of Wineries*/USE WinerySELECT COUNT(WineryID) AS Total Number of WineriesFROM tblWinery;GO/*q3) Marketing wants to target winery companies for a direct mailing sorted by winery name in ascending order.  Write a query to list only the Winery Name column for those wineries whose names contain either ‘Wine’ or ‘Vine’ letters.  Assume the database is not case-sensitive for string operations.*/USE WinerySELECT WineryName AS Winery NameFROM tblWineryWHERE WineryName LIKE ‘%WINE%’ OR WineryName LIKE ‘%VINE%’ORDER BY WineryName ASC;GO/*q4) What the Marketing Department really wants is the full mailing addresses within a single column named Mailing Address, that includes the Winery Name, Street, City, Province, Country, Postal Code fields for the wineries selected from the previous question (the field is separated by a comma AND a space).*/USE WinerySELECT WineryName+’, ‘+StreetNumber+’, ‘+StreetName+’, ‘+StreetSuffix+’, ‘+City+’,’+Province+’, ‘+Country+’, ‘+PostalCodeAS Mailing AddressFROM tblWineryWHERE WineryName LIKE ‘%WINE%’ OR WineryName LIKE ‘%VINE%’ORDER BY WineryName ASC;GO/*q5) Summarize each Wine Variety Name and its Total Number of Bottles of Wines Produced (non-zero production).  List the two columns and sort the Total Number of Bottles of Wines Produced column by descending order.*/USE WinerySELECT tblWineVariety.WineVarietyName AS Wine Variety Name,SUM(tblWineProduct.BottlesProduced) AS Total Number of Bottles ProducedFROM (tblWineProduct LEFT OUTER JOIN tblWineVarietyON tblWineProduct.WineVarietyID = tblWineVariety.WineVarietyID)GROUP BY tblWineVariety.WineVarietyNameORDER BY Total Number of Bottles Produced DESC;GO/*q6) Which winery has produced the most expensive wine per bottle?  Include the Winery Name, Wine Type, Wine Variety, Vintage Year (with only 4-digit year), Price per Bottle, UTM Easting, and UTM Northing.*/USE WinerySELECTTOP 1 WineryName,WineType,WineVarietyName,YEAR (VintageYear)’VintageYear’,BottlePrice,UTMEasting,UTMNorthingFROM tblWineTypeLookupJOIN tblWineVariety ON tblWineVariety.WineTypeID = tblWineTypeLookup.WineTypeIDJOIN tblWineProduct ON tblWineProduct.WineVarietyID = tblWineVariety.WineVarietyIDJOIN tblWinery ON tblWinery.WineryID = tblWineProduct.WineryIDJOIN tblWineryLocation ON tblWineryLocation.WineryID = tblWinery.WineryIDWHERE BottlePrice = (SELECT MAX(BottlePrice) FROM tblWineProduct)GROUP BY WineryName, WineType, WineVarietyName, BottlePrice, VintageYear, UTMEasting, UTMNorthing;GO/*q7) Retrieve the only wineries which were founded in and after 1994 with three fields: Winery Name, Year Founded (with only 4-digit year), and Total Number of Bottles of Wines Produced.  The queried records are sorted by the Total Number of Bottles of Wines Produced by descending order.*/USE WinerySELECT tblWinery.WineryName AS Winery Name,YEAR(tblWinery.YearFounded) AS Year Founded,SUM(tblWineProduct.BottlesProduced) AS Total Number of Wine Bottles ProducedFROM tblWinery JOIN tblWineProductON tblWineProduct.WineryID = tblWinery.WineryIDWHERE (tblWinery.YearFounded >= 1994)GROUP BY tblWinery.WineryName, YearFounded HAVING YEAR(tblWinery.YearFounded) >=1994ORDER BY Total Number of Wine Bottles Produced DESC;GO/*q8) Derive those wineries which have produced Chardonnay in 2001 and sort the Total Number of Bottles of Chardonnay Produced in descending order.  Fields included in the result are Winery Name, Wine Variety Name, Total Number of Bottles of Chardonnay Produced, and Vintage Year (use only 4-digit year).*/USE WinerySELECT tblWinery.WineryName AS ‘Winery Name’,tblWineVariety.WineVarietyName AS ‘Wine Variety Name’,tblWineProduct .BottlesProduced AS ‘Total Number of Bottles of Chardonnay Produced’,YEAR (tblWineProduct.VintageYear) AS ‘Vintage Year’FROM tblWinery FULL OUTER JOIN tblWineProduct ON tblWinery.WineryID = tblWineProduct.WineryID FULL OUTER JOIN tblWineVarietyON tblWineProduct.WineVarietyID = tblWineVariety.WineVarietyIDWHERE YEAR(tblWineProduct.VintageYear)=’2001’AND tblWineVariety.WineVarietyName = ‘Chardonnay’ORDER BY tblWineProduct.BottlesProduced DESC;GO/*q9) Extract 4 pieces of information: Winery Name, Total Number of Bottles of Wines Produced, Weighted Average Price per Bottle, and Total Sales for those wineries whose total sales are over 1 million dollars, ordered by the Total Sales in descending order.  Assume that each winery has sold out every bottle of their wines in stock at the price that was initially marketed.*/USE WinerySELECT tblWinery.WineryName AS Winery Name,SUM(tblWineProduct.BottlesProduced) AS Total Number of Bottles Produced,’$’ + CONVERT(VARCHAR(10),SUM(BottlesProduced*BottlePrice)/SUM(BottlesProduced), 1) AS Weighted Average Bottle Price,’$’ + CONVERT(VARCHAR(12),SUM(BottlePrice*BottlesProduced), 1) AS Total SalesFROM tblWineProduct JOIN tblWineryON tblWinery.WineryID = tblWineProduct.WineryIDGROUP BY WineryNameHAVING SUM(BottlesProduced*BottlePrice) > 1000000ORDER BY Total Sales DESC;GO/*q10) Extract two wineries, including the columns of the Winery ID, Winery Name, and the Distance between Two Wineries in Metre.  These two wineries are the closest to each other in spatial distance, i.e., they have the shortest distance in between.  Display the wineries within a single record and round the distance to the nearest integer in metre.  Note: This query is relatively complex and needs some work.*/USE Winery SELECT TOP 1 (one.WineryID + ‘ and ‘ + two.WineryID) AS Winery ID,(one1.WineryName + ‘ and ‘ + two2.WineryName) AS Winery Name,ROUND (GEOGRAPHY::Point(one.latitude, one.longitude, 4326).STDistance(GEOGRAPHY::Point(two.Latitude, two.Longitude, 4326)),0) AS Distance Between Two Wineries (Metres)FROM (tblWineryLocation one FULL OUTER JOIN tblWinery one1ON one1.WineryID=one.WineryID), (tblWineryLocation two FULL OUTER JOIN tblWinery two2ON two2.WineryID=two.WineryID)WHERE one.WineryID < two.WineryIDORDER BY Distance Between Two Wineries (Metres) ASC;GO