SQL Homework Help, Project Help, SQL Assignment Solution

SELECT DISTINCT S.ProductKey AS 'SKU'
    FROM FactInternetSales AS S;

SELECT DISTINCT P.ProductKey AS 'SKU', P.EnglishProductName AS 'Product Name' 
    FROM DimProduct AS P
    LEFT OUTER JOIN FactInternetSales AS S
    ON P.ProductKey = S.ProductKey
    WHERE S.OrderDate IS NULL
    ORDER BY P.ProductKey ASC;

SELECT DISTINCT P.ProductKey AS 'SKU', P.EnglishProductName AS 'Product Name' 
    FROM DimProduct AS P
    LEFT OUTER JOIN FactInternetSales AS S
    ON P.ProductKey = S.ProductKey
    WHERE S.OrderDate IS NULL AND P.ProductSubcategoryKey IS NOT NULL
    ORDER BY P.ProductKey ASC;

-- Using a LEFT OUTEER JOIN, we match items in second table using first table. Every item in first table 
-- will be in resultant table; so it's only needed to add a filter for first table in order to only get products 
-- which subcategory is not null.

SELECT P.ProductKey AS 'SKU', P.EnglishProductName AS 'Product Name' 
    FROM DimProduct AS P    
    WHERE P.ProductKey NOT IN (
        SELECT DISTINCT S.ProductKey
            FROM FactInternetSales AS S)
    AND P.ProductSubcategoryKey IS NOT NULL
    ORDER BY P.ProductKey ASC;

SELECT FORMAT(AVG(P.ListPrice), 'C') AS 'Average Price'
    FROM DimProduct AS P
    INNER JOIN DimProductSubcategory AS PSC
    ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
    INNER JOIN DimProductCategory AS PC
    ON PSC.ProductCategoryKey = PC.ProductCategoryKey
    WHERE PC.EnglishProductCategoryName = 'Clothing';

SELECT P.ProductKey AS 'SKU', P.EnglishProductName AS 'Product Name', 
        FORMAT(P.ListPrice, 'C') AS 'Price'
    FROM DimProduct AS P    
    INNER JOIN DimProductSubcategory AS PSC
    ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
    INNER JOIN DimProductCategory AS PC
    ON PSC.ProductCategoryKey = PC.ProductCategoryKey
    WHERE PC.EnglishProductCategoryName = 'Clothing' 
    AND P.ListPrice > (
        SELECT AVG(P.ListPrice) AS 'AVGPrice'
            FROM DimProduct AS P
            INNER JOIN DimProductSubcategory AS PSC
            ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
            INNER JOIN DimProductCategory AS PC
            ON PSC.ProductCategoryKey = PC.ProductCategoryKey
            WHERE PC.EnglishProductCategoryName = 'Clothing')
    ORDER BY P.ProductKey ASC;

SELECT FORMAT(AVG(C.YearlyIncome), 'C') AS 'Average yearly income'
    FROM DimCustomer AS C;

SELECT CONCAT(C.LastName, ', ', C.FirstName) AS 'Name', FORMAT(C.YearlyIncome, 'C') AS 'Yearly Income'
    FROM DimCustomer AS C 
    WHERE C.YearlyIncome <= (
        SELECT AVG(C.YearlyIncome) FROM DimCustomer AS C
    ) ORDER BY C.YearlyIncome ASC;

SELECT P.EnglishProductName AS 'Product Name', FORMAT(P.ListPrice, 'C') AS 'List Price'
    FROM DimProduct AS P
    WHERE P.EnglishProductName = 'Road-450 Red, 52';

SELECT P.EnglishProductName AS 'Product Name', FORMAT(P.ListPrice, 'C') AS 'List Price'
    FROM DimProduct AS P
    INNER JOIN DimProductSubcategory AS PSC
    ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
    INNER JOIN DimProductCategory AS PC
    ON PSC.ProductCategoryKey = PC.ProductCategoryKey
    WHERE PC.EnglishProductCategoryName = 'Bikes' AND P.ListPrice >= (
        SELECT P.ListPrice FROM DimProduct AS P WHERE P.EnglishProductName = 'Mountain-500 Black, 42'
    ) ORDER BY P.ListPrice ASC, P.EnglishProductName ASC;

SELECT DISTINCT R.ResellerName AS 'Reseller', P.EnglishProductName AS 'Product'
    FROM FactResellerSales AS S
    INNER JOIN DimProduct AS P
    ON S.ProductKey = P.ProductKey
    INNER JOIN DimReseller AS R
    ON R.ResellerKey = S.ResellerKey
    ORDER BY R.ResellerName ASC, P.EnglishProductName ASC;

SELECT R.ResellerName AS 'Reseller'
    FROM DimReseller AS R
    WHERE R.ResellerKey IN (
        SELECT DISTINCT S.ResellerKey
        FROM FactResellerSales AS S
        WHERE S.ProductKey = (
            SELECT P.ProductKey FROM DimProduct AS P WHERE P.EnglishProductName = 'Road-450 Red, 52'
        )
    ) ORDER BY R.ResellerName ASC;

SELECT * FROM FactSurveyResponse;

SELECT CONCAT(C.FirstName, ', ', C.MiddleName, ', ', C.LastName) AS 'Name', C.EmailAddress AS 'E-mail'
    FROM DimCustomer AS C
    WHERE EXISTS (
        SELECT * FROM FactSurveyResponse AS SR
        WHERE SR.CustomerKey = C.CustomerKey
    ) ORDER BY C.LastName ASC, C.MiddleName ASC, C.FirstName ASC;

SELECT CONCAT(C.FirstName, ', ', C.MiddleName, ', ', C.LastName) AS 'Name', C.EmailAddress AS 'E-mail'
    FROM DimCustomer AS C
    WHERE EXISTS (
        SELECT * FROM FactSurveyResponse AS SR
        WHERE SR.CustomerKey = C.CustomerKey
    ) AND C.YearlyIncome <= (SELECT AVG(YearlyIncome) FROM DimCustomer)
     ORDER BY C.LastName ASC, C.MiddleName ASC, C.FirstName ASC;

SELECT CONCAT(C.FirstName, ', ', C.MiddleName, ', ', C.LastName) AS 'Name', C.EmailAddress AS 'E-mail'
    FROM DimCustomer AS C
    WHERE NOT EXISTS (
        SELECT * FROM FactSurveyResponse AS SR
        WHERE SR.CustomerKey = C.CustomerKey
    ) AND C.YearlyIncome <= (SELECT AVG(YearlyIncome) FROM DimCustomer)
     ORDER BY C.LastName ASC, C.MiddleName ASC, C.FirstName ASC;

SELECT AVG(C.NumberCarsOwned) AS 'Average number of cars owned'
    FROM DimCustomer AS C;

SELECT C1.CustomerKey AS 'CId', CONCAT(C1.LastName, ', ', C1.FirstName) AS 'Name', 
        C1.NumberCarsOwned AS '# of cars owned', C1.EnglishOccupation
    FROM DimCustomer AS C1
    WHERE C1.NumberCarsOwned > (
        SELECT AVG(C2.NumberCarsOwned)
        FROM DimCustomer AS C2
        WHERE C2.EnglishOccupation = C1.EnglishOccupation
    )
    ORDER BY C1.EnglishOccupation ASC, C1.NumberCarsOwned ASC, C1.LastName ASC;

SELECT R.BusinessType AS 'Business Type', R.ResellerName AS 'Name', 
        FORMAT(R.AnnualSales, 'C') AS 'Total sales'
    FROM DimReseller AS R
    WHERE R.AnnualSales > (
        SELECT AVG(AnnualSales) FROM DimReseller WHERE BusinessType = 'Specialty Bike Shop'
    ) ORDER BY R.BusinessType ASC, R.ResellerName ASC;

SELECT x.contactname AS Name, (

SELECT CASE WHEN x.PType = 'Home'
THEN x.phonenumber
END
)Home, (

SELECT CASE WHEN x.PType = 'Work'
THEN x.phonenumber
END
)Office, (

SELECT CASE WHEN x.PType = 'Mobile'
THEN x.phonenumber
END
)Mobile
FROM (

SELECT phonetype.description AS PType, phonenumber, ContactName
FROM customer, phonetype, extbusiness, phone
WHERE customer.extbusinessid = extbusiness.extbusinessid
AND phone.phonetype = phonetype.phonetype
AND phone.extbusinessid = extbusiness.extbusinessid
)x
GROUP BY x.contactname

Our online tutors for SQL also provide oracle homework help, which further assist the students to solve complex queries related to database. Our online tutors provide SQL assignment help to the students who had to study SQL as the standard language for Relation Database System.

• A list of all school customers and their contact details (contact name, email and all phone details).

  SELECT x.contactname AS Name, (
  
  SELECT CASE WHEN x.PType = 'Home'
  THEN x.phonenumber
  END
  )Home, (
  
  SELECT CASE WHEN x.PType = 'Work'
  THEN x.phonenumber
  END
  )Office, (
  
  SELECT CASE WHEN x.PType = 'Mobile'
  THEN x.phonenumber
  END
  )Mobile, x.EmailAddress AS Email
  FROM (
  
  SELECT phonetype.description AS PType, phonenumber, ContactName, EmailAddress
  FROM customer, phonetype, extbusiness, phone, GroupCustomer, School
  WHERE customer.extbusinessid = extbusiness.extbusinessid
  AND phone.phonetype = phonetype.phonetype
  AND phone.extbusinessid = extbusiness.extbusinessid
  AND School.GroupCustomerID = GroupCustomer.GroupCustomerID
  AND GroupCustomer.customerID = Customer.CustomerID
  )x
  GROUP BY x.contactname

• List all provider equipment details where the equipment description begins with a K(either upper case or lower case).

  SELECT Equipment.Description AS Product, ProviderName AS Name
  FROM Equipment, Provider, ProviderEquipment
  WHERE Equipment.EquipmentID = ProviderEquipment.EquipmentID
  AND ProviderEquipment.providerID = Provider.ProviderID
  AND UPPER( Equipment.Description ) LIKE 'K%'

• A list of all customers (id and name) together with their activity(s) including date, time and type of the activity and the percentage markup less discount percentage for the activity.

  SELECT Customer.customerid AS ID, ContactName AS Name, ActivityDate AS DATE, ActivityTime AS TIME, Activity.ActivityType AS 
  TYPE , (
  Markup - discount
  ) AS MarkupLessDiscount
  FROM customer, Activity, ActivityType, extBusiness
  WHERE Activity.Activitytype = ActivityType.Activitytype
  AND customer.customerid = Activity.customerid
  AND extBusiness.extbusinessid = customer.extbusinessid

• A list of the contact details (names, postal address and phone number) of all of the providers who supplied equipment for more than one activity.

  SELECT ProviderName, phonenumber, line1 || City || PostCode AS Postal
  FROM Provider, ADDRESS,  extbusiness, ProviderEquipment, phone, A_P_Equipment
  WHERE Provider.ProviderID = ProviderEquipment.ProviderID
  AND Provider.extbusinessid = phone.extbusinessid
  AND Provider.extbusinessid = address.extbusinessid
  AND phone.extbusinessid = extbusiness.extbusinessid
  AND A_P_Equipment.EquipmentID = ProviderEquipment.EquipmentID
  AND ProviderEquipment.ProviderID = A_P_Equipment.ProviderID
  AND Provider.ProviderId = ProviderEquipment.ProviderID
  And ProviderEquipment. EquipmentID=Equipment. EquipmentID
  AND A_P_Equipment.EquipmentID
  IN (
  
  SELECT EquipmentID,
  
  FROM A_P_Equipment
  GROUP BY EquipmentID
  HAVING COUNT( * ) >1
  )

• List each activity (activityID is sufficient) and all of the details of trainers who have worked on the activity, including the trainer provider id and name. Provide details sorted by trainer provider.

  SELECT Activity.activityID AS Activity, Trainer.TrainerID, ChristianName, Surname AS Name, Phone, Trainer_Provider.TrainerProviderID, name AS 'provider Name'
  FROM Activity, T_P_Trainer, Trainer, Trainer_Provider, Activity_T_P_S
  WHERE Activity.ActivityID = Activity_T_P_S.ActivityID
  AND Activity_T_P_S.TrainerProviderID = T_P_Trainer.TrainerProviderID
  AND Activity_T_P_S.TrainerID = T_P_Trainer.TrainerID
  AND Trainer.TrainerID = T_P_Trainer.TrainerID
  AND Trainer_Provider.TrainerProviderID = T_P_Trainer.TrainerProviderID
  ORDER BY Trainer_Provider.TrainerProviderID

• Display a list of equipment(id and description) that has had a unit charge update in the last 3 months.

  SELECT Equipment.EquipmentID, Description
  FROM Equipment, ProviderEquipment, A_P_EQUIPMENT
  WHERE Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND TIMESTAMPDIFF( 
  MONTH , CURDATE( ) , Effective_Date ) <=3

• Provide a list of all activities, including customer name, activity ID, date, time, location for which actual quantities used have been greater than allocated quantities. Include details of the equipment to which this applies as well as the cost difference.

  SELECT ContactName, activity.activityid, ActivityDate AS DATE, ActivityTime AS TIME, Location, Description AS equipment, (
  Actual_Quantity - Estimated_Quantity
  ) AS Difference, (
  Actual_Quantity - Estimated_Quantity
  ) * Eqpt_Price AS 'Cost difference'
  FROM customer, extbusiness, activity, equipment, A_P_Equipment, PROVIDEREQUIPMENT
  WHERE customer.extbusinessid = extbusiness.extbusinessid
  AND customer.customerid = activity.customerid
  AND Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND activity.activityid = A_P_EQUIPMENT.activityid
  AND Actual_Quantity > Estimated_Quantity

• Display the itemised actual equipment cost of all items for activities held between January and June 2013 (based on activity date).

  SELECT activity.activityid, ActivityDate AS DATE, ActivityTime AS TIME, Location, Description AS equipment, (
  Actual_Quantity
  ) * Eqpt_Price AS Cost
  FROM activity, equipment, A_P_Equipment, PROVIDEREQUIPMENT
  WHERE Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND activity.activityid = A_P_EQUIPMENT.activityid
  AND ActivityDate
  BETWEEN '2013-01-01'
  AND '2013-06-30'

• Increase by 2% the markup percentage of all types of activities.

  UPDATE activitytype SET markup = markup * 1.02

• List the equipment with the highest quantity of actual usage in the database.

  SELECT Description AS equipment, Actual_Quantity AS  Qunatity Used 
  FROM activity, equipment, A_P_Equipment, PROVIDEREQUIPMENT
  WHERE Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND activity.activityid = A_P_EQUIPMENT.activityid
  AND Actual_Quantity = ( 
  SELECT MAX( Actual_Quantity ) 
  FROM A_P_EQUIPMENT )

• Provide a list of all activities, including customer name, activity id, date, time, location for which actual hours worked have been greater than allocated hours. Include details of the cost difference and total change in cost for the activity for the difference in hours.

  SELECT ContactName AS name, activity.activityid, ActivityDate AS DATE, ActivityTime AS TIME, Location, Actual_Hrs_worked AS 'Actual Work Hours', (
  Actual_Hrs_worked - Estimated_HoursWorked
  ) * Hourly_rate AS 'Cost Difference', (
  
  SELECT SUM( Actual_Hrs_worked - Estimated_HoursWorked ) * Hourly_rate AS 'Total Cost difference'
  FROM Activity_T_P_S
  )
  FROM customer, extbusiness, activity, Activity_T_P_S
  WHERE customer.extbusinessid = extbusiness.extbusinessid
  AND customer.customerid = activity.customerid
  AND activity.activityid = Activity_T_P_S.activityid
  AND Actual_Hrs_worked > Estimated_HoursWorked

• Provide a list of all trainers (and their details), who have worked as an activity manager. Include the number of activities they have managed.

  SELECT surname,ActivityManager,activity.activityid
  from activity, ACTIVITY_T_P_S,TRAINER,T_P_TRAINER
  where 
  activity.activityid=ACTIVITY_T_P_S.activityid
  and Trainer.TrainerID=T_P_TRAINER.TrainerID
  and T_P_TRAINER.TrainerProviderID=ACTIVITY_T_P_S.TrainerProviderID
  and activity.ActivityManager=ACTIVITY_T_P_S.Trainerid
  and ACTIVITY_T_P_S.Trainerid=T_P_TRAINER.TrainerID

• Using the MIN function, provide the details of the activity with the lowest difference between the estimated and actual hired equipment costs.

  SELECT ContactName, activity.activityid, ActivityDate AS DATE, ActivityTime AS TIME, Location
  FROM customer, extbusiness, activity, equipment, A_P_Equipment, PROVIDEREQUIPMENT
  WHERE customer.extbusinessid = extbusiness.extbusinessid
  AND customer.customerid = activity.customerid
  AND Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND activity.activityid = A_P_EQUIPMENT.activityid
  and ((Actual_Quantity - Estimated_Quantity
  ) * Eqpt_Price) = ( select min(abs((Actual_Quantity - Estimated_Quantity
  ) * Eqpt_Price)) AS 'Cost difference' from A_P_EQUIPMENT)

• Using a sub?query and the NOT EXISTS statement, list all distinct equipment records (equipment ID and description) where the equipment has been used in an activity for which there are no other activities of that type.

  SELECT equipment.Description AS equipment, equipment.equipmentid,activitytype.activitytype
  FROM activity, equipment, A_P_Equipment, PROVIDEREQUIPMENT,activitytype
  WHERE Equipment.Equipmentid = PROVIDEREQUIPMENT.equipmentid
  AND ProviderEquipment.EquipmentID = A_P_EQUIPMENT.equipmentID
  AND A_P_EQUIPMENT.Providerid = ProviderEquipment.Providerid
  AND activity.activityid = A_P_EQUIPMENT.activityid
  and activitytype.activitytype=activity.activitytype
  and not exists(select activitytype from activitytype)
  
  CreateDatabase:
  CREATE DATABASE IF NOT EXISTS myDatabase201327;
  
  USE myDatabase201327;
  
  CREATE TABLE AddressType
  (
  AddressType INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(AddressType)
  );
  
  CREATE TABLE PhoneType
  (
  PhoneType INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(PhoneType)
  );
  
  CREATE TABLE ActivityType
  (
  ActivityType INT NOT NULL,
  Description VARCHAR(40),
  Markup INT,
  PRIMARY KEY(ActivityType)
  );
  
  CREATE TABLE BusinessType
  (
  BusinessType INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(BusinessType)
  );
  
  CREATE TABLE GovtLevel
  (
  GovtLevel INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(GovtLevel)
  );
  
  CREATE TABLE SchoolType
  (
  SchoolType INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(SchoolType)
  );
  
  CREATE TABLE Denomination
  (
  Denomination INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY(Denomination)
  );
  
  CREATE TABLE Equipment
  (
  EquipmentID INT NOT NULL,
  Description VARCHAR(40),
  GroupID INT, 
  PRIMARY KEY(EquipmentID),
  FOREIGN KEY (GroupID) REFERENCES Equipment(EquipmentID)
  );
  
  CREATE TABLE SkillType
  (
  SkillType INT NOT NULL,
  Description VARCHAR(40),
  PRIMARY KEY (SkillType)
  );
  
  CREATE TABLE ExtBusiness
  (
  ExtBusinessID INT NOT NULL,
  ContactName VARCHAR(40),
  PRIMARY KEY(ExtBusinessID)
  );
  
  CREATE TABLE Customer
  (
  CustomerID VARCHAR(5) NOT NULL,
  EmailAddress VARCHAR(40),
  ExtBusinessID INT NOT NULL,
  PRIMARY KEY(CustomerID),
  FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
  
  );
  
  CREATE TABLE GroupCustomer
  (
  GroupCustomerID VARCHAR(5) not null,
  CustomerID VARCHAR(5),
  BusinessName VARCHAR(40),
  WebsiteURL VARCHAR(100),
  PRIMARY KEY (GroupCustomerID), 
  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
  
  );
  CREATE TABLE CORPORATION
  (
  GroupCustomerID VARCHAR(5) not null,
  NoOfEmployees VARCHAR(5) NOT NULL,
  BusinessType INT,
  PRIMARY KEY (GroupCustomerID), 
  FOREIGN KEY (GroupCustomerID) REFERENCES GroupCustomer(GroupCustomerID),
  FOREIGN KEY (BusinessType) REFERENCES BusinessType(BusinessType)
  
  );
  CREATE TABLE GOVERNMENT
  (
  GroupCustomerID VARCHAR(5) not null,
  DepartmentName VARCHAR(5) NOT NULL,
  GovtLevel INT,
  NoOfEmployees VARCHAR(5) NOT NULL,
  PRIMARY KEY (GroupCustomerID), 
  FOREIGN KEY (GroupCustomerID) REFERENCES GroupCustomer(GroupCustomerID),
  FOREIGN KEY (GovtLevel) REFERENCES GovtLevel(GovtLevel)
  
  );
  CREATE TABLE School
  (
  GroupCustomerID VARCHAR(5) not null,
  NoOfStudents VARCHAR(5) NOT NULL,
  SchoolType INT,
  PRIMARY KEY (GroupCustomerID), 
  FOREIGN KEY (GroupCustomerID) REFERENCES GroupCustomer(GroupCustomerID),
  FOREIGN KEY (SchoolType) REFERENCES SchoolType(SchoolType)
  
  );
  
  CREATE TABLE RELIGIOUSGROUP
  (
  GroupCustomerID VARCHAR(5) not null,
  Denomination INT,
  PRIMARY KEY (GroupCustomerID), 
  FOREIGN KEY (GroupCustomerID) REFERENCES GroupCustomer(GroupCustomerID),
  FOREIGN KEY (Denomination) REFERENCES Denomination(Denomination)
  
  );
  
  CREATE TABLE Provider
  (
  ProviderID VARCHAR(5) NOT NULL,
  ProviderName VARCHAR(40),
  ExtBusinessID INT NOT NULL,
  PRIMARY KEY(ProviderID),
  FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
  );
  
  CREATE TABLE Trainer_Provider
  (
  TrainerProviderID VARCHAR(5) NOT NULL,
  Name VARCHAR(40),
  ExtBusinessID INT NOT NULL,
  PRIMARY KEY(TrainerProviderID),
  FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
  );
  
  CREATE TABLE Trainer
  (
  TrainerID INT NOT NULL,
  ChristianName VARCHAR(30),
  Surname VARCHAR(30),
  Phone VARCHAR(15),
  PRIMARY KEY(TrainerID)
  );
  
  CREATE TABLE T_P_Trainer
  (
  TrainerProviderID VARCHAR(5) NOT NULL,
  TrainerID INT NOT NULL,
  PRIMARY KEY(TrainerProviderID, TrainerID),
  FOREIGN KEY (TrainerProviderID) REFERENCES Trainer_Provider(TrainerProviderID), 
  FOREIGN KEY (TrainerID) REFERENCES Trainer(TrainerID)
  );
  
  Several students search for SQL assignment questions with answers to prepare 
  the assignment problems instantly. Our experts offer SQL homework help and 
  also discuss about SQL which is known as a computer language for storing, 
  manipulating, and retrieving data stored in relational database. 
  CREATE TABLE Activity
  (
  ActivityID INT NOT NULL,
  Location VARCHAR(50),
  CustomerID VARCHAR(5) NOT NULL,
  ActivityDate DATE,
  ActivityTime TIME,
  ActivityType INT NOT NULL,
  NumberAttendees INT,
  ActivityManager INT,
  Discount Decimal(5,2),
  Permits INT,
  Actual_TotalCost Decimal(7,2),
  Estimated_TotalCost Decimal(7,2),
  PRIMARY KEY(ActivityID),
  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
  FOREIGN KEY (ActivityType) REFERENCES ActivityType(ActivityType) 
  );
  
  CREATE TABLE Activity_T_P_S
  (
  ActivityID INT NOT NULL,
  TrainerProviderID VARCHAR(5) NOT NULL,
  TrainerID INT NOT NULL,
  Estimated_HoursWorked INT,
  Actual_Hrs_worked INT,
  Hourly_rate INT,
  Effective_Date Date,
  PRIMARY KEY (ActivityID, TrainerProviderID, TrainerID),
  FOREIGN KEY (ActivityID) REFERENCES Activity(ActivityID),
  FOREIGN KEY (TrainerProviderID, TrainerID) REFERENCES T_P_Trainer(TrainerProviderID, TrainerID),
  INDEX Manager(ActivityID, TrainerID)
  );
  
  ALTER TABLE Activity
  
  ADD FOREIGN KEY ActivityManager (ActivityID,ActivityManager) REFERENCES Activity_T_P_S(ActivityID,TrainerID)
  ;
  
  CREATE TABLE ProviderEquipment
  (
  ProviderID VARCHAR(5) NOT NULL,
  EquipmentID INT NOT NULL,
  PRIMARY KEY(ProviderID, EquipmentID),
  FOREIGN KEY (ProviderID) REFERENCES Provider(ProviderID), 
  FOREIGN KEY (EquipmentID) REFERENCES Equipment(EquipmentID)
  
  );
  
  CREATE TABLE A_P_Equipment
  (
  ActivityID INT NOT NULL,
  ProviderID VARCHAR(5) NOT NULL,
  EquipmentID INT NOT NULL,
  Actual_Quantity INT,
  Eqpt_Price Decimal(7,2),
  Effective_Date Date,
  Estimated_Quantity INT,
  PRIMARY KEY(ActivityID,ProviderID,EquipmentID),
  FOREIGN KEY (ActivityID) REFERENCES Activity(ActivityID),
  FOREIGN KEY (ProviderID,EquipmentID) REFERENCES ProviderEquipment(ProviderID,EquipmentID)
  
  );
  
  CREATE TABLE TrainerSkill
  (
  TrainerID INT NOT NULL,
  SkillType INT NOT NULL,
  PRIMARY KEY(TrainerID, SkillType),
  FOREIGN KEY (TrainerID) REFERENCES Trainer(TrainerID), 
  FOREIGN KEY (SkillType) REFERENCES SkillType(SkillType)
  );
  
  CREATE TABLE Phone
  (
  ExtBusinessID INT NOT NULL,
  PhoneType INT NOT NULL,
  PhoneNumber VARCHAR(15),
  PRIMARY KEY (ExtBusinessID,  PhoneType),
  FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID),
  FOREIGN KEY (PhoneType) REFERENCES PhoneType(PhoneType)
  
  );
  
  CREATE TABLE Address
  (
  ExtBusinessID INT NOT NULL,
  AddressType INT NOT NULL,
  Line1 VARCHAR(40),
  City VARCHAR(40),
  Postcode VARCHAR(4),
  PRIMARY KEY (ExtBusinessID,  AddressType),
  FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID),
  FOREIGN KEY (AddressType) REFERENCES AddressType(AddressType)
  );
  
  InsertData:
  USE myDatabase201327;
  
  INSERT INTO AddressType (`AddressType`, `Description`) VALUES 
  (1, 'Location'),
  (2, 'Postal'),
  (3, 'Delivery');
  
  INSERT INTO PhoneType (`PhoneType`, `Description`) VALUES 
  (1, 'Home'),
  (2, 'Mobile'),
  (3, 'Work');
  
  INSERT INTO ActivityType (`ActivityType`, `Description`,markup) VALUES 
  (1, 'Rock Climb and Abseil',10),
  (2, 'Boot Camp',12),
  (3, 'Mountain Hike and Bike',1),
  (4, 'Surf and Swim',10),
  (5, 'Kayak and Canoe Safari',5),
  (6, 'Dive and Strive',7),
  (7, 'Tree Climb and Abseil',11);
  
  INSERT INTO BusinessType (`BusinessType`, `Description`) VALUES 
  (1, 'Accomodation'),
  (2, 'Automotive'),
  (3, 'Entertainment'),
  (4, 'Financial Services'),
  (5,  'Manufacturing'),
  (6, 'Agriculture'),
  (7,  'Retail'),
  (8,  'Transportation');
  
  INSERT INTO GovtLevel (`GovtLevel`, `Description`) VALUES
  (1, 'Federal'),
  (2, 'State'),
  (3, 'Local');
  
  INSERT INTO SchoolType (`SchoolType`, `Description`) VALUES
  (1, 'Private'),
  (2, 'Public'),
  (3, 'Religious');
  
  INSERT INTO Denomination (`Denomination`, `Description`) VALUES
  (1, 'Roman Catholic'),
  (2, 'Uniting Church'),
  (3, 'Church of England'),
  (4, 'Theravada'),
  (5, 'Mahayana');
  
  /* For simplicity in this assignment we only create data that satisfies a unary 1 to many relationship. In practice the relationship would likely be unary many to many and another relation would be required*/
  
  INSERT INTO Equipment (`EquipmentID`, `Description`, `GroupID`) VALUES 
  (1, '20kg first aid kit', NULL),
  (2, '30kg first aid kit', NULL),
  (3, 'kitted out sea kayak',NULL),
  (4, 'kitted out river kayak',NULL),
  (5, 'kitted out canoe',NULL),
  (6, 'sea kayak paddling set', 3),
  (7, 'river kayak paddling set', 4),
  (8, 'canoe paddling set', 5),
  (9, 'sea kayak', 3),
  (10, 'canoe', 5),
  (11, 'river kayak', 4),
  (12, 'inflatable life jacket - extreme',6),
  (13, 'inflatable life jacket',7),
  (14, 'canoe life jacket',8),
  (15, 'sea kayak paddle', 6),
  (16, 'river kayak paddle', 7),
  (17, 'canoe paddle', 8),
  (18, 'Emergency Position-Indicating Radio Beacon',NULL),
  (19, '50 person Open Marquee', NULL),
  (20, '50 person Closed Marquee', NULL),
  (21, 'rock climbing set', NULL),
  (22, '200 metre climbing rope', 21),
  (23, '500 metre climbing rope', 21),
  (24, 'Climbing caribiner set', 21),
  (25, '2 Man tent', NULL),
  (26, 'climbing connector set', 21),
  (27, '3 Man tent', NULL),
  (28, 'climbing harness set', 21),
  (29, 'Climbing peg set',21),
  (30, 'abseiling set', NULL),
  (31, '200 metre abseiling rope', 30),
  (32, 'abseiling harness set', 30),
  (33, 'abseiling connector set', 30),
  (34, 'scuba kit', NULL),
  (35, 'Face Mask', 34),
  (36, 'Wet suit', 34),
  (37, 'Breathing equipment', 34),
  (38, 'Air Tank', 34),
  (39, 'Flippers', 34),
  (40, 'Helmet', NULL),
  (41, 'Exercise kit', NULL),
  (42, 'Skipping Rope', 41),
  (43, 'Tractor Tyre', 41),
  (44, 'Isometric ropes', 41),
  (45, 'all Weights set', 41),
  (46, 'wooden horse', 41),
  (47,  '5kg weight set', 45),
  (48, '10kg weight set', 45),
  (49, '20kg weight set', 45),
  (50, 'Marker set', NULL),
  (51, 'Traffic Cone set', 50),
  (52, 'Barrier rope set', 50)
  ;
  
  INSERT INTO SkillType (`SkillType`, `Description`) VALUES 
  (1, 'abseiling'),
  (2, 'windsurfing'),
  (3, 'biking'),
  (4, 'lifesaving'),
  (5, 'canoeing'),
  (6, 'kayaking'),
  (7, 'rock climbing'),
  (8, 'alpine climbing'),
  (9, 'tree climbing'),
  (10, 'diving'),
  (11, 'exercises'),
  (12, 'running')
  ;
  
  INSERT INTO ExtBusiness (`ExtBusinessID`, `ContactName`) VALUES 
  (1, 'Enoch Sundry'),
  (2, 'Barry Francis'),
  (3, 'Richard Entwistle'),
  (4, 'Megan Rowbott'),
  (5, 'Scott Friend'),
  (6, 'Bruce Lee'),
  (7, 'Gavin Peabody'),
  (8, 'Winsome Losesome'),
  (9, 'Barry Dawson'),
  (10, 'Felicity Sandshoe'),
  (11, 'Jos Burrows');
  
  INSERT INTO Customer (`CustomerID`,`EmailAddress`,`ExtBusinessID`) values
  
  ('00001','esundry@abccorporation.com.xx',1),
  ('00002','bfrancis@gmailx.com',2),
  ('00003','sfriend@gmailx.com',5),
  ('00005','wlosesome@stsnodgrassschool.edu.xx',8);
  
  INSERT INTO GroupCustomer(`GroupCustomerID`,`CustomerID`,`BusinessName`,`WebsiteURL`) values 
  ('1','00001','ABC Corporation','http://www.abccorporation.com.xx'),
  ('2','00005','St Snodgrass School','http://www.stsnodgrassschool.edu.xx');
  
  INSERT INTO CORPORATION(`GroupCustomerID`,`NoOfEmployees`, `BusinessType`) values 
  ('1',10,1),
  ('2',10,2);
  
  INSERT INTO GOVERNMENT(`GroupCustomerID`,`DepartmentName`,`NoOfEmployees`, `GovtLevel`) values 
  ('1','ABC',10,1),
  ('2','XYZ',10,1);
  
  INSERT INTO School(`GroupCustomerID`,`NoOfStudents`, `SchoolType`) values 
  ('1',10,1),
  ('2',20,2);
  
  INSERT INTO RELIGIOUSGROUP(`GroupCustomerID`,`Denomination`) values 
  ('1',1),
  ('2',2);
  
  INSERT INTO Provider (`ProviderID`, `ProviderName`,`ExtBusinessID`) VALUES 
  ('00001', 'Climb Every MOuntain, Ford Every Stream',3),
  ('00002', 'Coast to Coast Supplies',4),
  ('00003', 'Earth Wind and Fire Hire',6);
  
  INSERT INTO Trainer_Provider (`TrainerProviderID`, `Name`,`ExtBusinessID`) VALUES
  ('00001', 'Trainers For All Occasions',9),
  ('00002', 'Action Trainers',10),
  ('00003', 'We Provide the Trainer',11);
  
  INSERT INTO Trainer(`TrainerID`, `ChristianName`, `Surname`, `Phone`) VALUES 
  (1,'Chuck', 'Norriz','0440440440'),
  (2,'Steven', 'Seagull','044104410441'),
  (3,'Brittany', 'Spearchucker', '033903390339'),
  (4,'Fiery', 'Thompson','033803380338'),
  (5,'Owen', 'Owen','03703370337'),
  (6,'Owen', 'Stanley','03708370447'),
  (7,'Stanley', 'Owen','03203320332'),
  (8,'Constance', 'Rhett','02402340334');
  
  INSERT INTO T_P_Trainer(`TrainerProviderID`,`TrainerID`) VALUES 
  ('00001',1),
  ('00001',2),
  ('00001',3),
  ('00002',1),
  ('00002',4),
  ('00002',5),
  ('00002',6),
  ('00003',2),
  ('00003',7),
  ('00003',8)
  ;
  
  INSERT INTO Activity (`ActivityID`,`Location`, `CustomerID`,`ActivityDate`,`ActivityTime`, `ActivityType`, `NumberAttendees`,`ActivityManager`,`Discount`,
  `Permits`,`Actual_TotalCost`,`Estimated_TotalCost`) VALUES 
  (1,'Lake Wendouree North Gardens','00001','2012-06-10','10:00:00',7,40,NULL,1,0,1200,1300),
  (2,'Lake Fishwick','00002','2012-07-10','08:00:00',6,10,NULL,1,0,1200,1300),
  (3,'Francis Oval, Deepdene', '00003','2012-036-10','9:00:00',2,20,NULL,1,0,1200,1300),
  
  (5,'Nelson, Victoria','00005','2013-03-10','10:00:00',5,200, NULL,1,0,1200,1300);
  
  INSERT INTO ACTIVITY_T_P_S (ActivityID, TrainerProviderID, TrainerID,Estimated_HoursWorked,Actual_Hrs_worked,Hourly_rate,`Effective_Date`) VALUES
  (1,'00001',1,15,12,10,'2014-01-10'),
  (1,'00002',4,15,12,10,'2014-01-10'),
  (1,'00002',5,15,12,10,'2014-01-10'),
  (2,'00001',2,15,12,10,'2014-01-10'),
  (2,'00003',8,15,12,10,'2014-01-10'),
  (3,'00002',6,15,12,10,'2014-01-10'),
  (3,'00003',7,15,12,10,'2014-01-10'),
  (5,'00002',6,15,12,10,'2014-01-10'),
  (5,'00002',4,15,12,10,'2014-01-10'),
  (5,'00003',8,15,12,10,'2013-12-10'),
  (5,'00001',2,15,12,10,'2014-01-10')
  ;
  
  UPDATE ACTIVITY
  SET ActivityManager=1 WHERE ActivityID=1;
  
  UPDATE ACTIVITY
  SET ActivityManager=2 WHERE ActivityID=2;
  
  UPDATE ACTIVITY
  SET ActivityManager=6 WHERE ActivityID=3;
  
  UPDATE ACTIVITY
  SET ActivityManager=3 WHERE ActivityID=4;
  
  UPDATE ACTIVITY
  SET ActivityManager=8 WHERE ActivityID=5;
  
  INSERT INTO ProviderEquipment (`ProviderID`, `EquipmentID`) VALUES 
  ('00001',1),
  ('00001',2),
  ('00001',4),
  ('00001',5),
  ('00001',7),
  ('00001',8),
  ('00001',10),
  ('00001',11),
  ('00001',13),
  ('00001',14),
  ('00001',16),
  ('00001',17),
  ('00001',18),
  ('00001',21),
  ('00001',22),
  ('00001',23),
  ('00001',24),
  ('00001',25),
  ('00001',26),
  ('00001',27),
  ('00001',28),
  ('00001',29),
  ('00001',30),
  ('00001',31),
  ('00001',32),
  ('00001',33),
  ('00001',40),
  ('00002',1),
  ('00002',2),
  ('00002',3),
  ('00002',4),
  ('00002',5),
  ('00002',6),
  ('00002',7),
  ('00002',8),
  ('00002',9),
  ('00002',10),
  ('00002',11),
  ('00002',12),
  ('00002',13),
  ('00002',14),
  ('00002',15),
  ('00002',16),
  ('00002',17),
  ('00002',18),
  ('00002',34),
  ('00002',35),
  ('00002',36),
  ('00002',37),
  ('00002',38),
  ('00003',1),
  ('00003',2),
  ('00003',19),
  ('00003',20),
  ('00003',40),
  ('00003',41),
  ('00003',42),
  ('00003',43),
  ('00003',44),
  ('00003',45),
  ('00003',46),
  ('00003',47),
  ('00003',48),
  ('00003',49),
  ('00003',50),
  ('00003',51),
  ('00003',52)
  ;
  
  INSERT INTO A_P_Equipment (`ActivityID`,`ProviderID`,`EquipmentID`,Actual_Quantity,Eqpt_Price,`Effective_Date`,Estimated_Quantity) VALUES 
  (1,'00001',2,10,15,'2013-12-10',9),
  (1,'00001',21,10,15,'2013-12-10',9),
  (1,'00001',22,1,15,'2013-12-10',9),
  (1,'00001',30,10,15,'2013-12-10',9),
  (1,'00001',40,20,15,'2013-12-10',9),
  (2,'00002',1,1,15,'2013-12-10',9),
  (2,'00002',34,5,15,'2013-12-10',9),
  (2,'00003',51,3,15,'2013-12-10',9),
  (2,'00003',52,2,15,'2013-12-10',9),
  (3,'00003',1,1,15,'2013-12-10',9),
  (3,'00003',41,3,15,'2013-12-10',9),
  (3,'00003',42,2,15,'2013-12-10',9),
  (3,'00003',48,2,15,'2013-12-10',9),
  (3,'00003',50,2,15,'2013-12-10',9),
  
  (5,'00001',1,3,15,'2013-12-10',9),
  (5,'00002',1,2,15,'2013-12-10',9),
  /* The school wants to use sea kayaks and sea paddles in the river */
  (5,'00002',9,50,15,'2013-12-10',9),
  /* Whilst 50 are paddling, another set will practice on shore with their paddles */
  (5,'00002',15,100,15,'2013-12-10',9),
  (5,'00001',13,50,15,'2013-12-10',9),
  (5,'00001',18,5,15,'2013-12-10',9),
  (5,'00003',19,2,15,'2013-12-10',9)
  ;
  
  INSERT INTO TrainerSkill (`TrainerID`, `SkillType`) VALUES
  (1,9),
  (1,1),
  (1,7),
  (2,10),
  (2,6),
  (2,5),
  (3,7),
  (3,1),
  (4,6),
  (4,5),
  (4,1),
  (5,9),
  (5,1),
  (6,11),
  (6,6),
  (6,5),
  (7,11),
  (7,10),
  (8,12),
  (8,6),
  (8,5);
  
  INSERT INTO Phone (`ExtBusinessID`,`PhoneType`,`PhoneNumber`) VALUES 
  (1,3,'0356789213'),
  (2,3,'0256669214'),
  (3,3,'0356669000'),
  (4,3,'0448796090'),
  (5,3,'0634565482'),
  (6,3,'0382687900'),
  (7,3,'0299987987'),
  (1,2,'0448790680'),
  (2,2,'0448791681'),
  (3,2,'0440791666'),
  (4,2,'0442796090'),
  (5,2,'0448791331'),
  (6,2,'0445791221'),
  (5,1,'0357565444'),
  (7,1,'0358565482'),
  (8,2,'0446565446'),
  (8,3,'0358565333'),
  (9,2,'0448565448'),
  (9,3,'0357565444'),
  (10,2,'0441561441'),
  (10,3,'0352565235'),
  (11,2,'0440561440'),
  (11,3,'0353565890');
  
  INSERT INTO Address(`ExtBusinessID`,`AddressType`,`Line1`,`City`,`PostCode`) VALUES 
  (1,1,'1020 Sturt Street','Ballarat','3350'),
  (1,2,'PO Box 357, Ballarat Mail Centre','Ballarat','3350'),
  (1,3,'20 Traminer Industrial Park','Ballarat','3350'),
  (2,1,'20 FishCandle Rd','Fishwick','3388'),
  (3,1,'1 Glenlyon Road','Daylesford','3356'),
  (4,1,'350 Footscray Road','Footscray','3356'),
  (4,3,'Warehouse 9,Footscray Industrial Park','Footscray','3356'),
  (5,1,'1 Mangatook-Timberup Road','Timberup','3800'),
  (5,2,'RMB 62, Mangatook-Timberup Road','Timberup','3800'),
  (6,1,'350 Springvale Road','Springvale','3366'),
  (6,3,'350 Springvale Road','Springvale','3366'),
  (7,1,'62 Gear Avenue, Mount Helen','Ballarat','3500'),
  (8,1,'82 Penbury Road, Windsor','Melbourne','3048'),
  (8,2,'PO Box 62 Windsor Mail Centre','Melbourne','3048'),
  (8,3,'72 Penbury Road, Windsor','Melbourne','3048'),
  (9,1,'Unit 6, Chadstone Business Centre, Chadstone','Melbourne','3040'),
  (9,2,'PO Box 63 Chadstone Mail Centre', 'Melbourne','3041'),
  (10,1,'Unit 6, Wangaratta Business Centre','Wangaratta','3401'),
  (10,2,'PO Box 2 Wangaratta Mail Centre', 'Wangaratta','3402'),
  (11,1,'78 Hopeside Road, Mornington','Melbourne','3099'),
  (11,2,'78 Hopeside Road, Mornington','Melbourne','3099');

Problem

— 1.a. (3) Find the total number of customers who own homes (0 = no and 1 = yes). Be sure give each derived
— field an appropriate alias.

SELECT SUM(CAST(HouseOwnerFlag AS INT)) AS 'OwnHome', COUNT(*)-SUM(CAST(HouseOwnerFlag AS INT)) AS 'NotOwnHome'
    FROM DimCustomer;

–1.b. (2) Check your result. Write queries to determine if the answer is correct. You should be checking
— your work each time and not just when reminded.

SELECT COUNT(*) AS 'OwnHome'
    FROM DimCustomer
    WHERE HouseOwnerFlag = 1;

SELECT COUNT(*) AS 'NotOwnHome'
    FROM DimCustomer
    WHERE HouseOwnerFlag = 0;

Learners can solve their complex SQL assignments with the help of our skilled experts. Our experts have observed that the beginners usually face great difficulties in solving simple or complex queries, hence they provide SQL queries assignment with answers that gives easy and well commented solutions.

–1.c. (3) Find the total number of children at home and the total cars owned for customers who own homes.

SELECT SUM(NumberChildrenAtHome) AS 'TotalChilden', SUM(NumberCarsOwned) AS 'TotalCars'
    FROM DimCustomer
    WHERE HouseOwnerFlag = 1;

—- checking

SELECT SUM(NumberChildrenAtHome) AS 'TotalChilden', SUM(NumberCarsOwned) AS 'TotalCars'
    FROM DimCustomer
    WHERE CustomerKey IN (SELECT CustomerKey FROM DimCustomer WHERE HouseOwnerFlag = 1);

–1.d. (3) Find the total children at home, total cars owned, and average annual income for customers who own homes.

SELECT SUM(NumberChildrenAtHome) AS 'TotalChilden', SUM(NumberCarsOwned) AS 'TotalCars', AVG(YearlyIncome) AS 'AnnualIncome'
    FROM DimCustomer
    WHERE HouseOwnerFlag = 1;

—- checking

SELECT SUM(NumberChildrenAtHome) AS 'TotalChilden', SUM(NumberCarsOwned) AS 'TotalCars', AVG(YearlyIncome) AS 'AnnualIncome'
    FROM DimCustomer
    WHERE CustomerKey IN (SELECT CustomerKey FROM DimCustomer WHERE HouseOwnerFlag = 1);

–2.a. (2) List the total dollar amount (SalesAmount) for sales to Customers.

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount'
    FROM FactInternetSales;

–2.b. (3) List the total dollar amount (SalesAmount) for 2008 sales to Customers who live in Australia.
— Show only the total sales–one row, one column.

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount_AU2008'
    FROM FactInternetSales AS S
    INNER JOIN DimCustomer AS C
    ON S.CustomerKey = C.CustomerKey
    INNER JOIN DimGeography AS G
    ON C.GeographyKey = G.GeographyKey
    WHERE G.EnglishCountryRegionName = 'Australia' AND YEAR(S.OrderDate) = 2008;

—- checking

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount_AU2008'
    FROM FactInternetSales AS S
    WHERE YEAR(S.OrderDate) = 2008 AND S.CustomerKey IN
    (SELECT C.CustomerKey
        FROM DimCustomer AS C
        WHERE C.GeographyKey IN
        (SELECT G.GeographyKey FROM DimGeography AS G
            WHERE G.EnglishCountryRegionName = 'Australia'));

–3.a. (2) List the total dollar amount (SalesAmount) for sales to Resellers.

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount'
    FROM FactResellerSales;

–3.b. (3) List the total dollar amount (SalesAmount) for 2006 sales to resellers located in France.
— Show only the total sales–one row, one column.

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount_FR2006'
    FROM FactResellerSales AS S
    INNER JOIN DimReseller AS R
    ON S.ResellerKey = R.ResellerKey
    INNER JOIN DimGeography AS G
    ON R.GeographyKey = G.GeographyKey
    WHERE G.EnglishCountryRegionName = 'France' AND YEAR(S.OrderDate) = 2006;

—- checking

SELECT FORMAT(SUM(SalesAmount), 'C') AS 'TotalDollarAmount_FR2006'
    FROM FactResellerSales AS S
    WHERE YEAR(S.OrderDate) = 2006 AND S.ResellerKey IN
    (SELECT R.ResellerKey
        FROM DimReseller AS R
        WHERE R.GeographyKey IN
        (SELECT G.GeographyKey FROM DimGeography AS G
            WHERE G.EnglishCountryRegionName = 'France'));

–4. (4) List the average unit price for a mountain bike sold to resellers.

SELECT FORMAT(AVG(R.UnitPrice), 'C') AS 'AVG_MountainBike_UPrice'
    FROM FactResellerSales AS R
    INNER JOIN DimProduct AS P
    ON R.ProductKey = P.ProductKey
    INNER JOIN DimProductSubcategory AS PS
    ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
    WHERE PS.EnglishProductSubcategoryName = 'Mountain Bikes'

—- checking

SELECT FORMAT(AVG(R.UnitPrice), 'C') AS 'AVG_MountainBike_UPrice'
    FROM FactResellerSales AS R
    WHERE R.ProductKey IN (
        SELECT P.ProductKey 
            FROM DimProduct AS P
            WHERE P.ProductSubcategoryKey IN (
                SELECT PS.ProductSubcategoryKey 
                    FROM DimProductSubcategory AS PS
                    WHERE PS.EnglishProductSubcategoryName = 'Mountain Bikes'
            )
        );

–5. (5) List bikes that have a list price higher than the average list price for all bikes.
— Show product alternate key, English product name, and list price.
— Order descending by list price.

SELECT P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'Price'
    FROM DimProduct AS P
    INNER JOIN DimProductSubcategory AS PS
    ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
    INNER JOIN DimProductCategory AS PC
    ON PS.ProductCategoryKey = PC.ProductCategoryKey
    WHERE PC.EnglishProductCategoryName = 'Bikes' AND P.ListPrice > (
        SELECT AVG(R.UnitPrice)
            FROM FactResellerSales AS R
            INNER JOIN DimProduct AS P
            ON R.ProductKey = P.ProductKey
            INNER JOIN DimProductSubcategory AS PS
            ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
            WHERE PS.EnglishProductSubcategoryName = 'Mountain Bikes'
    ) ORDER BY P.ListPrice ASC;

—- checking…

SELECT P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'Price'
    FROM DimProduct AS P
    WHERE P.ProductSubcategoryKey IN (
        SELECT PS.ProductSubcategoryKey
            FROM DimProductSubcategory AS PS
            WHERE PS.ProductCategoryKey IN (
                SELECT P.ProductCategoryKey 
                    FROM DimProductCategory AS P
                    WHERE P.EnglishProductCategoryName = 'Bikes'
            )
    ) AND P.ListPrice > (
        SELECT AVG(R.UnitPrice)
            FROM FactResellerSales AS R
            INNER JOIN DimProduct AS P
            ON R.ProductKey = P.ProductKey
            INNER JOIN DimProductSubcategory AS PS
            ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
            WHERE PS.EnglishProductSubcategoryName = 'Mountain Bikes'
    ) ORDER BY P.ListPrice ASC;

–6. (5) List the lowest list price, the average list price, the highest list price, and
— product count for touring bikes.

SELECT FORMAT(MIN(P.ListPrice), 'C') AS 'Lowest', 
        FORMAT(AVG(P.ListPrice), 'C') AS 'Average',
        COUNT(*) AS 'ProdCount'
    FROM DimProduct AS P
    INNER JOIN DimProductSubcategory AS PS
    ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
    WHERE PS.EnglishProductSubcategoryName = 'Touring Bikes'

—- checking

SELECT FORMAT(MIN(P.ListPrice), 'C') AS 'Lowest', 
        FORMAT(AVG(P.ListPrice), 'C') AS 'Average',
        COUNT(*) AS 'ProdCount'
    FROM DimProduct AS P
    WHERE P.ProductSubcategoryKey IN (      
        SELECT PS.ProductSubcategoryKey 
            FROM DimProductSubcategory AS PS
            WHERE PS.EnglishProductSubcategoryName = 'Touring Bikes'        
        );

— 7. (4) List the product alternate key, product name, and list price for the product(s)
— with the highest List Price. There can be multiple products with the highest list price.

SELECT P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'Price'
    FROM DimProduct AS P
    WHERE P.ListPrice = (
        SELECT MAX(P.ListPrice) FROM DimProduct AS P
    ) ORDER BY P.ProductAlternateKey ASC;

—- checking

SELECT P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'Price'
    FROM DimProduct AS P
    WHERE P.ListPrice = (
        SELECT TOP(1) P.ListPrice FROM DimProduct AS P ORDER BY P.ListPrice DESC
    ) ORDER BY P.ProductAlternateKey ASC;

— 8. (5) List the product alternate key, product name, list price, dealer price, and the
— difference (calculated field) for the product(s) with the smallest difference between
— the list price and the dealer price. Show all money values to 2 decimal places.

SELECT P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'ListPrice', FORMAT(P.DealerPrice, 'C') AS 'DealerPrice', 
        FORMAT(P.ListPrice-P.DealerPrice, 'C') AS 'DiffPrice'
    FROM DimProduct AS P
    WHERE P.ListPrice-P.DealerPrice = (
        SELECT MIN(P.ListPrice-P.DealerPrice) FROM DimProduct AS P
    ) ORDER BY P.ProductAlternateKey ASC;

—- checking

SELECT TOP(1) P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'ListPrice', FORMAT(P.DealerPrice, 'C') AS 'DealerPrice', 
        FORMAT(P.ListPrice-P.DealerPrice, 'C') AS 'DiffPrice'
    FROM DimProduct AS P
    WHERE P.ListPrice IS NOT NULL AND P.DealerPrice IS NOT NULL
    ORDER BY DiffPrice ASC;

One of the main features of SQL permits users to access data in relational database management systems, allow the users to describe the data, lets the users to define the data in database and operate that data, allows to implant within other languages using libraries, SQL modules and pre-compilers, permits users to create and drop databases and tables, consents the users to create views, functions and stored procedures in a database and also permits users to set authorizations on tables, procedures and views. Our SQL experts at Programming Homework Help offer solutions within the stipulated deadline.

— 9. (4) List total Internet sales for product BK-R64Y-48 using two methods: Total the sales amount
— field and calculate the total amount using unit price and quantity. There will one result set with
— two columns and one row. Show all money values to 2 decimal places. Yes, the values should be the same.

SELECT FORMAT(SUM(S.SalesAmount), 'C') AS 'TotalSales1', 
        FORMAT(SUM(S.UnitPrice*S.OrderQuantity), 'C') AS 'TotalSales2'
    FROM FactInternetSales AS S
    INNER JOIN DimProduct AS P
    ON S.ProductKey = P.ProductKey
    WHERE P.ProductAlternateKey = 'BK-R64Y-48';

—- checking

SELECT FORMAT(SUM(S.SalesAmount), 'C') AS 'TotalSales1', 
        FORMAT(SUM(S.UnitPrice*S.OrderQuantity), 'C') AS 'TotalSales2'
    FROM FactInternetSales AS S
    WHERE S.ProductKey IN (
        SELECT P.Productkey 
            FROM DimProduct AS P
            WHERE P.ProductAlternateKey = 'BK-R64Y-48'
    );

The foundation knowledge gained here by learning SQL from our renowned experts is also helpful to students in learning any other database language. SQL is an acronym for structured query language and this is a computer language that is meant for storing, manipulating or fetching or retrieving the data that is stored in the relational database. The tables with the rows and columns are referred to as the relations. Each relation or the table is made up of the record which is referred to as the tuples. The SQL assignments are solved by our experts by adhering to strict timelines given by the students.

–10. (2) In your own words, write a business question that you can answer by querying the data warehouse
— and using an aggregate function.
— Then write the complete SQL query that will provide the information that you are seeking.

—- Which is the bike product that represents more gains?

SELECT TOP(1) P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'ListPrice', FORMAT(P.DealerPrice, 'C') AS 'DealerPrice', 
        FORMAT(P.ListPrice-P.DealerPrice, 'C') AS 'DiffPrice'
    FROM DimProduct AS P
    WHERE P.ProductKey IN (
        SELECT P.ProductKey 
        FROM DimProduct AS P
        INNER JOIN DimProductSubcategory AS PS
        ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
        INNER JOIN DimProductCategory AS PC
        ON PS.ProductCategoryKey = PC.ProductCategoryKey
        WHERE PC.EnglishProductCategoryName = 'Bikes'
    ) ORDER BY DiffPrice DESC;

—- checking

SELECT TOP(1) P.ProductAlternateKey AS 'SKU', P.EnglishProductName AS 'Product', 
        FORMAT(P.ListPrice, 'C') AS 'ListPrice', FORMAT(P.DealerPrice, 'C') AS 'DealerPrice', 
        FORMAT(P.ListPrice-P.DealerPrice, 'C') AS 'DiffPrice'
    FROM DimProduct AS P
    INNER JOIN DimProductSubcategory AS PS
    ON P.ProductSubcategoryKey = PS.ProductSubcategoryKey
    INNER JOIN DimProductCategory AS PC
    ON PS.ProductCategoryKey = PC.ProductCategoryKey
    WHERE PC.EnglishProductCategoryName = 'Bikes'
    ORDER BY DiffPrice DESC

Once SQL command is executed for any RDBMS, the SQL engine figures out how to interpret the task and the system determines the best way to carry out the request. There are many components included in the process. These components are Optimization Engines, Query Sender, SQL Query Engine and Classic Query Engine. Classic query engine manages all non-SQL queries whereas SQL query engine won’t handle logical files.The standard SQL commands that help with SQL programming assignment to relate with relational databases are CREATE, SELECT, DROP,INSERT, UPDATE and DELETE.

To know the various components included in the SQL process students seek help with SQL homework from our efficient team of experts.

Problem

Key Tasks

Task 1: Basic Conditions

The first task is to complete the implementation of a number of basic comparison operators, namely: greater than (>), greater than or equal (>=), less than (<), and less than or equal (<=). Specifically, you must complete the implementation of the evaluate() method in each of the
corresponding classes in the mapsql.condition package. While you can complete these implementations any way you want to, I recommend that you look at the implementation of equals (=) and not equals (<>) as a starting point. For each operator, I suggest that you create a simple test
program (add them to the mapsql.test package) that is based on the example test program. For example, to test less than, create a contacts table and insert 2 rows into the table. Now, create a select statement that will only return one of the rows.

Task 2: The Like Condition

The like condition can be used to compare CHAR fields (Strings). Specifically, like allows you to use the % sign as a wildcard for matching. In MapSQL, the use of wildcards is limited to the first and last characters in the string that is being matched against. Specifically, you can match %xxx, xxx%, or %xxx%. The first wildcard pattern allows you to check if a string ends with a given substring; the second pattern allow you to check if a string starts with a given substring; and the final pattern checks whether a string contains a given substring. The implementation of this condition is more complex than those completed in task 1. Again, remember to check your answer by creating a test program that checks all three permitted wildcard patterns.

Task 3: The Table.update() method

This method is supposed to be used in tandem with the Update statement. Specifically, it does the actual update of the any row that matches the WHERE clause. When updating a row, you should replace any existing values with the corresponding values that have been provided. The values of
any columns that are not explicitly updates should stay the same.

Task 4: The Table.delete() method

Like task 3, this task is supposed to be used in tandem with the Delete statement. Specifically, it does that actual removal of any rows that satisfy the WHERE clause.

Task 5: TableDescription.checkForNotNulls()

This task is concerned with ensuring that any field that is declared as NOT NULL is included explicitly in any INSERT statement. The method is already called by the Insert statement class, and it is expected that an SQLException will be raised as soon as a field is found that is declared as NOT NULL, but has not been explicitly included in the INSERT statement For example,given a table that has been created by the SQL statement:

CREATE TABLE contacts(
id INT AUTO_INCREMENT,
name CHAR(30) NOT NULL,
email CHAR(30)
);

Then the insert statement:

INSERT INTO contacts (email) VALUES('Henry');

Should result in an exception being thrown that has the following error message: Missing Value for NOT NULL field {“name

Task 6: The DropTable.execute() method

The DROP TABLE statement results in the named table being removed from the database and this behaviour should be implemented in this method. If you examine the codebase then you will see that there is a {“system table called {“mapsql.tables . You should generate an SQLException if
anybody attempts to delete this table. The error message for the exception should be: {“Table ‘mapsql.tables’ cannot be modified. .