SQL Assignment Help
SQL Assignment HelpIf you need plagiarism free Sql homework, then you’re in luck. I’ve had to do 6 copies of the same assignment (due to student referral, that was hard since each to be written from scratch without repeating anything. I even changed the algorithms, not just variable names and functions, this was a total rewrite). So don’t worry about getting a copy that your friend has, this will be unique and will pass any cheat detector.

SQL is a standard to access databases, you can use SQL from one database to access another database (with minor exceptions).

As an example of a Sql project, this indicates the type of solution we can provide.

SQL queries for the following tasks

Problem:

— You are to develop SQL statements for each task listed.
— You should type your SQL statements under each task.

— 1.a. (1) List the distinct ProductKey for products sold over the Internet. (One table, one field, many rows)
— No sort needed. Here you need to understand in which table sales over the Internet are stored.

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

— 1.b. (2) Using an Outer Join find all Products that have not been sold over the Internet. Show Product
— Key and the English Product Name. Add a meaningful sort.

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;

–1.c. (2) Using the Outer Join from 1.b. find all products that are for sale and have not been sold
— over the Internet. Show Product Key and the English Product Name. Add a meaningful sort.
— Recall that inventory was talked about in Assignment 7, Questions 2c. There are several ways to find
— products that are not for sale. Pick a method that works and makes sense to you. Include a comment
— about why you used that method.

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.

–1.d. (3) Rewrite the Outer Join from 1c as a subquery to find all Products that are for sale and have not been
— sold over the Internet. HINT: Review 1a and 1b. There will be no joins in the statement for 1d. 1a will be
— used as a subquery in the WHERE clause to return a list. You want to find product keys that are not in
— that list and are for sale (see 1c). This statement is likely simpler than you think it should be.

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;

— 2.a. (4) List the average listprice of clothing items for sale by AdventureWorks. No sort needed.
— Remember to provide a column alias. Use the AVG function that was demonstrated in the Subqueries Demo file.

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';

— 2.b. (3) List all products in the Clothing category that have a listprice higher than the average
— listprice of clothing itens. Show product alternate key, product name, and listprice in the
— results set. Order the information so it is easy to understand. Be sure
— to use a subquery; do not enter the actual value from 2.a. into the statement.

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;

— 3.a. (2) Find the average yearly income of all customers in the customer table.

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

— 3.b. (4) Find all houseowners in the customers table with an income less than or the same as the
— average income of all customers. List last name, a comma and space, and first name in
— one column and yearly income in another column. There will be two columns in the Results set.
— Be sure to use a subquery; do not enter the actual value from 3.a. into the statement.

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;

— 4.a. (2) List the product name and list price for the bike named Road-450 Red, 52
— Road-450 Red, 52 1457.99

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

— 4.b. (3) List the product name and price for each bike that has a price greater than or equal to
— that of the Mountain-500 Black, 42. Be sure you are using the subquery not an actual value.
— 55 rows

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;

/ Questions 5 and 6 ask you to experiment with a few of the Special Predicate Keywords for Subqueries. There are other ways to solve the statements, but use the requested predicate for practice. /

— 5.a. (4) List the names of resellers and the product names of products they sold.
— Eliminate duplicate rows. Use an appropriate sort.

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;

— 5.b. (4) List only one time the names of all resellers who sold a Road-450 Red, 52.
— Use the IN predicate and a subquery to accomplish the task. Use an appropriate sort.
— The WHERE clause in this one is similar to, but less complex than 1.d.

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;

— 6.a. (1) Show all data from the Survey Response fact table. Use select all.

SELECT * FROM FactSurveyResponse;

— 6.b. (4) Use a subquery and the EXISTS predicate to find customers that respond to surveys. List full
— name (first, middle, last) and email address (2 columns). Use the CONCAT() function for the name
— to overcome the NULL issue. You will not see NULL in any row. Refer to the selected solutions demo
— in the Module 03 discussion board for help with CONCAT. EXISTS is in the Module 09 demo file.

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;

— 6.c. (2) Copy/paste 6.b and use an additional subquery to narrow the results of 6.b. to only those customers
— with a yearly income that is less than or the same as the average of all customers.

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;

— 6.d. (1) Modify 6.c to find those customers at the income level specified there who do not response to surveys.

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;

— 7.a. (1) Find the average number of cars owned by all customers.
— Use the Average function and provide an appropriate alias.

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

— 7.b. (3) Use a correlated subquery to find customers who own more cars than the average
— number owned by customers in their same occupation. List customer key, last name,
— first name, number of cars owned, and English occupation. Add a meaningful sort.
— In a correlated subquery the inner query is dependent on the outer query for its value.
— There is an example of a similar request in the Subqueries demo file.

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;

— 8. (4) List all resellers whose annual sales are above the average annual sales for resellers whose
— Business Type is “Specialty Bike Shop”. Show Business type, Reseller Name, and annual sales.
— Use appropriate subqueries.

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;

As an illustration of an assigment, here’s a previous assignment that indicates the syle we can do with a Sql language assignment.

Data management system for a adventure sports organising business.

Project Specification

AS Adventure Sports (ASAS) is a business that organises outdoor activities e.g. rock climbing, abseiling, mountain biking and surfing for individuals and organisations. You have been asked by Peter, its managing director to design a database to assist them with managing their booking, customer and equipment provider information. The majority of the design has been completed, however there are some changes that they would like made to the system. Using the specifications from the previous assignment, the ER diagram and files provided, update and implement the system using the following information.

Original Specification

ASAS would like to store information regarding their customers. Customers can either be individuals or group customers. Group customers may include corporate, government, school or religious groups. For individual customers they would like to store the contact name, address details (including their location, postal and delivery address details), email address and phone numbers (mobile, home, work). For group customers they would like to store the business name, a contact name, address details (including their location, postal and delivery address details), email address, website URL, and phone numbers (mobile, work). Additionally, for their corporate customers they would like to store the business name, business type (markets in which they operate) and number of employees. For government customers they would also like to store the department name, level of government (local, state, federal) and number of employees. For school groups they would also like to store the type of school (private, public, religious) and number of students and for religious groups, the denomination.

ASAS provides equipment for their outdoor activities. They would like to record both the detail of the equipment available and also record what has been used (and the quantity) for each activity. The equipment provided is quite varied in type and size but may include complete items for example mountain bikes, surfboards or climbing equipment or smaller items for example ropes, carabiners, connectors or harnesses that make up those larger items. Sometimes in the case of group items e.g. climbing gear, the group item is used and in other cases individual pieces are used for the activity. All available pieces of equipment should be recorded in the one table with the relationship between group and individual items included.

The equipment is hired from equipment providers. One piece of equipment may be available for hire from more than one provider and a provider might hire out one or many pieces of equipment. So in addition to recording the equipment available for hire, ASAS would like to record what provider has this equipment available and also keep a register of their providers, including such details as provider identifier, name, contact name, address details (location, postal) and phone numbers (mobile, home, work).

As well as providing equipment for the activity, ASAS provides trainers to assist with the activity and records these details. The trainers are provided by hiring them from trainer providers and an activity may have trainers assigned from one or more trainer providers. ASAS would like to keep a record of these trainer providers and the names and details of the trainers available for hire. For trainer providers the information recorded should include provider identifier, name, contact name, address details (location, postal) and phone numbers (mobile, home, work). For the trainers the information recorded should include trainer provider, identifier, christian name, surname and mobile phone. It is a competitive field so a trainer may be hired out by more than one trainer provider. A trainer may have certain skills that make them more attractive for some events than others. These skills e.g. “abseiling”, “wind surfing”, “biking”, “lifesaving” should be recorded separately as desirable skills and then be available for assigning to trainers as details of the trainer are entered/changed.

For each activity ASAS would like to store details such as where the activity is to be held, the customer who they are organising the activity for, the date and time of the activity, where the activity will be held, the type of activity and how many people will be at the activity. As already mentioned, details are also recorded of the equipment used (and quantity) for the activity as well as the trainers used. Each activity will have one trainer who takes on the role of activity manager.

Additional Requirements

ASAS would like to be able to work out the price and direct costs of activities they conduct for clients. The price charged to the customer is based on a percentage markup over the total costs of the activity. The total costs are made up of the equipment used (determined by ASAS based on the type of activity and the numbers attending), the hiring costs of the trainers involved (the number needed is determined by ASAS and advised to the customer) and fixed costs for the activity e.g. entry permits. Other costs of operation such as insurance can be ignored for this assignment.

With respect to the price, a percentage markup is required to be stored. This markup is based on the type of activity. To simplify our example, this markup does not change over time. ASAS though want to have some flexibility to apply a discount percentage to customers for a particular activity(s). This discount is included at ASAS′s discretion and may be a reward for returning customers, for example. It is subtracted from the markup percentage rather than applied after the markup calculation.

With respect to costs of equipment used, ASAS negotiates hiring charges for this equipment with each provider and charges remain current for a period of time. The period of time is recorded as a date, which is the commencement date of its application. In the initial costing of the activity for a customer, ASAS estimate the equipment required, choose the provider(s) for that equipment and fill in the anticipated number of items needed. The provider′s charge for that item of equipment for that period then becomes the cost of that item of equipment. The total of all estimated
equipment used multiplied by its relevant item charge becomes the cost of equipment charged to the customer. When the job has been completed, the actual quantity of each item of equipment hired (which may be different to the estimate) is recorded separately so that a comparison can be made between the estimated and actual quantities used for an activity.

With respect to hiring costs of trainers, they are all paid based on number of hours worked. The number of hours worked are estimated and allocated for each activity and an hourly rate is applied based on the rate set by the trainer provider. The database should allow for an effective date for these rates and thus allow changes over time to the amount paid per hour. ASAS estimate the number of hours of each trainer used and record this. When the job has been completed, the actual hours worked are recorded. With respect to direct fixed costs for activities, ASAS would like to have a reference table with predetermined categories and another table recording totals of any of these costs against an activity. They would like to be able to record an estimated total cost for that category and then be able to record the actual cost after the activity is completed.

ASAS understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record these.

As an example, imagine that St. Snodgrass School commissioned ASAS to conduct a Year 9 adventure activity – kayaking down the Glenelg River, Victoria. St. Snodgrass will have 200 students taking part in the activity. ASAS charges a 20% markup for Kayaking events but St. Snodgrass is a repeat customer and ASAS decided to include a 5% discount. ASAS estimated the following equipment was needed:

  • 50 kayaks @ $100/day = $5000;
  • 100 paddles @ $10/day = $1000;
  • 50 lifejackets @ $5/day = $250;
  • 5 eprbs @ $50/day = $250;
  • 2 marquees @ $200/day = $400;
  • 5 first?aid kits @ $50/day = $250;

Total Estimated Equipment Hire Costs = $7150

It was estimated that four trainers would be needed and they were selected. The activity was expected to take eight hours. The rate of pay for two of these trainers was $50.00 per hour. The rate for the other two was $60.00 per hour. The total estimated wages costs were calculated to be:

  • 2 trainers – ($50 * 8) * 2 = $800
  • 2 trainers – ($60 * 8) * 2 = $960

Total Wages Costs = $1760

A cost for permit fees for use of the National Park of $200 for the day was also included. The price charged to St Snodgrass was therefore $10476.50:

  • Total Costs = $7150 + $1760 + $200 = $9110
  • Price = $9110 * 1.15 = $10476.50.

When the actual activity took place, all estimates about equipment usage were correct. However, two trainers (paid $50 per hour) needed to work an extra hour each on cleanup tasks. The estimate of permit fees was incorrect due to changes in legislation (a price rise of $20 was included) and a fine of $200 was imposed by the Department of Conservation for damage to some trees at the base site. In the working database, the actual quantities of equipment used and hours worked would be entered, the actual permit fee costs for the job would be entered and a record created for the job to record the fine imposed (with an estimated cost of $0). The actual cost of the job was therefore increased by $320 (($50 * 2 * 1hr) + $20 + $200). The gross margin on the job was therefore $1046.50 ($10476.50 ? ($9110 + $320)) or 11.1%.

You will need to incorporate the above details in the updated ER diagram and database and then use that database to generate the reports detailed below.

Required Reports

When defining select queries, you are required to adhere to the following output formatting conventions:

  • where applicable, names of people should be printed as GivenNameFamilyName (e.g. John Smith) in a column labelled NAME
  • where applicable, addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS
  • You are not allowed to create temporary queries to answer these queries

You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an “unusual” approach. The following reports are required :

  1. A list of the names and phone numbers (including description of phone type) of all customers in
    alphabetical order by contact name. You should think about what happens when a phone

    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
    
  2. 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
    
  3. 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%'
    
  4. 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
    
  5. 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
    )
    
  6. 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
    
  7. 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
    
  8. 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
    
  9. 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'
    
  10. Increase by 2% the markup percentage of all types of activities.
    UPDATE activitytype SET markup = markup * 1.02
    
  11. 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 )
    
  12. 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
    
  13. 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
    
  14. 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)
    
  15. 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)
    );
    
    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;

–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;

— 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'
    );

–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

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. .

If the statement does refer to an existing table then the table should be removed from both the tables Map and from the above system table.

Task 7: The Sources.execute() method

This last task is to implement the SOURCES command. This command basically loads a specified text file and attempts to parse it. If the file is parsed successfully, then the resultantSQL statements are
executed.

update.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.core.Condition;
import mapsql.sql.core.Row;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class Update implements SQLStatement {
    private String name;
    private String[] columns;
    private String[] values;
    private Condition where;

    public Update(String name, String[] columns, String[] values, Condition where) {
        this.name = name;
        this.columns = columns;
        this.values = values;
        this.where = where;
    }

    public Update(String name, String[] columns, String[] values) {
        this(name, columns, values, null);
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        if (name.equals("mapsql.tables")) throw new SQLException("Table 'mapsql.tables' cannot be modified");
        final Table table = tables.get(name);
        if (table == null) throw new SQLException("Unknown table: " + name);

        // Here we are just checking that the columns we have provided are correct
        table.description().resolveColumns(columns);

        table.update(columns, values, where);

        return new SQLResult() {

            @Override
            public TableDescription description() {
                return table.description();
            }

            @Override
            public List<Row> rows() {
                return null;
            }

            public String toString() {
                return "OK";
            }
        };
    }

}

SelectResult.java

package mapsql.sql.statement;

import mapsql.sql.core.Field;
import mapsql.sql.core.Row;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class SelectResult implements SQLResult {
    TableDescription description;
    List<Row> rows;
    String[] columns;

    public SelectResult(TableDescription description, List<Row> rows, String[] columns) {
        this.description = description;
        this.rows = rows;
        this.columns = columns;
    }

    public TableDescription description() {
        return description;
    }

    public List<Row> rows() {
        return rows;
    }

    public String toString() {
        String names = "|";
        for (int i=0; i<columns.length; i++) {
            Field field = description.findField(columns[i]);
            names += " " + field.toFixedWidthString() + " |";
        }
        String line = "";
        for (int i=0; i<names.length(); i++) line+="-";

        String out = line+"\n"+names+"\n" + line + "\n";

        for (Row row : rows) {
            out += "|";
            for (int i=0; i<columns.length; i++) {
                Field field = description.findField(columns[i]);
                out += " " + field.toFixedWidthString(row.get(field.name())) + " |";
            }

            out += "\n";
        }

        out += line;

        return out;
    }
}

Select.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.core.Condition;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;

public class Select implements SQLStatement {
    private String name;
    private String[] columns;
    private Condition where;

    public Select(String name, String[] columns, Condition where) {
        this.name = name;
        this.columns = columns;
        this.where = where;
    }

    public Select(String name, String[] columns) {
        this(name, columns, null);
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        Table table = tables.get(name);
        if (table == null) throw new SQLException("Unknown table: " + name);

        String[] cols = table.description().resolveColumns(columns);

        return new SelectResult(table.description(), table.select(where), cols);
    }

}

Insert.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.core.Row;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class Insert implements SQLStatement {
    private String name;
    private String[] columns;
    private String[] values;

    public Insert(String name, String[] columns, String[] values) {
        this.name = name;
        this.columns = columns;
        this.values = values;
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        if (name.equals("mapsql.tables")) throw new SQLException("Table 'mapsql.tables' cannot be modified");

        final Table table = tables.get(name);
        if (table == null) throw new SQLException("Unknown table: " + name);

        table.description().checkForNotNulls(columns);

        String[] cols = table.description().resolveColumns(columns);

        table.insert(cols, values);
        return new SQLResult() {

            @Override
            public TableDescription description() {
                return table.description();
            }

            @Override
            public List<Row> rows() {
                return null;
            }

            public String toString() {
                return "OK";
            }
        };
    }

}

DropTable.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.condition.Equals;
import mapsql.sql.core.Condition;
import mapsql.sql.core.Row;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class DropTable implements SQLStatement {
    private String name;

    public DropTable(String name) {
        this.name = name;
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        if (name.equals("mapsql.tables")) 
            throw new SQLException("Table 'mapsql.tables' cannot be dropped");

        if (!tables.containsKey(name)) 
            throw new SQLException("Table: " + name + " does not exist");

        // remove the table from the tables
        tables.remove(name);

        // remove the row recording the table in the mysql.tables table
        Equals equals = new Equals("table", name);
        tables.get("mapsql.tables").delete(equals);

        return new SQLResult() {
            public String toString() {
                return "OK";
            }

            @Override
            public TableDescription description() {
                return null;
            }

            @Override
            public List<Row> rows() {
                return null;
            }
        };
    }

}

Delete.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.core.Condition;
import mapsql.sql.core.Row;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class Delete implements SQLStatement {
    private String name;
    private Condition where;

    public Delete(String name, Condition where) {
        this.name = name;
        this.where = where;
    }

    public Delete(String name) {
        this(name, null);
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        if (name.equals("mapsql.tables")) throw new SQLException("Table 'mapsql.tables' cannot be modified");

        final Table table = tables.get(name);
        if (table == null) throw new SQLException("Unknown table: " + name);

        table.delete(where);

        return new SQLResult() {

            @Override
            public TableDescription description() {
                return table.description();
            }

            @Override
            public List<Row> rows() {
                return null;
            }

            public String toString() {
                return "OK";
            }
        };
    }

}

CreateTable.java

package mapsql.sql.statement;

import java.util.Map;

import mapsql.sql.core.Field;
import mapsql.sql.core.Row;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.SQLResult;
import mapsql.sql.core.SQLStatement;
import mapsql.sql.core.Table;
import mapsql.sql.core.TableDescription;
import mapsql.util.List;

public class CreateTable implements SQLStatement {
    private String name;
    private Field[] fields;

    public CreateTable(String name, Field[] fields) {
        this.name = name;
        this.fields = fields;
    }

    @Override
    public SQLResult execute(Map<String, Table> tables) throws SQLException {
        if (tables.containsKey(name)) throw new SQLException("Duplicate Table Name: " + name);

        final TableDescription description = new TableDescription(name, fields);

        tables.put(name, new Table(description));

        // add a row recording the new table to the mapsql.tables table
        tables.get("mapsql.tables").insert(new String[] {"table"}, new String[] {name});

        return new SQLResult() {
            public String toString() {
                return "OK";
            }

            @Override
            public TableDescription description() {
                return description;
            }

            @Override
            public List<Row> rows() {
                return null;
            }
        };
    }

}

Like.java

package mapsql.sql.condition;

import java.util.Map;
import java.util.regex.Pattern;

import mapsql.sql.core.Field;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.TableDescription;
import mapsql.sql.field.CHARACTER;

public class Like extends AbstractCondition {
    private String column;
    private String value;

    public Like(String column, String value) {
        this.column = column;
        this.value = value;
    }

    @SuppressWarnings("unchecked")
    @Override
    public boolean evaluate(TableDescription description, Map<String, String> data) throws SQLException {
        Field field = description.findField(column);

        // LIKE must be used for field of type CHARACTER (string)
        if (!Field.CHARACTER.equals(field.type())) {
            throw new SQLException("Error: LIKE must used for field of type CHARACTER.");
        }

        String v = (String) field.toValue(data.get(column));

        // now check whether: v like the pattern (value)
        // regular expression is used to check
        //  replace % with .* 
        //  add ^ $ so the whole word should be matched
        String pattern = value.replaceAll("%", ".*");
        pattern = "^" + pattern + "$";

        Pattern p = Pattern.compile(pattern);

        // now check whether the value of the row matches the pattern
        return p.matcher(v).matches();
    }
}

LessThan.java
package mapsql.sql.condition;

import java.util.Map;

import mapsql.sql.core.Field;
import mapsql.sql.core.SQLException;
import mapsql.sql.core.TableDescription;

public class LessThan extends AbstractCondition {
    private String column;
    private String value;

    public LessThan(String column, String value) {
        this.column = column;
        this.value = value;
    }

    @SuppressWarnings("unchecked")
    @Override
    public boolean evaluate(TableDescription description, Map<String, String> data) throws SQLException {
        Field field = description.findField(column);
        return comparator.compare(field.toValue(data.get(column)), field.toValue(value)) < 0;
    }

}

TableDescription.java
package mapsql.sql.core;

public class TableDescription {
    private String name;
    private Field[] fields;

    public TableDescription(String name, Field[] fields) {
        this.name = name;
        this.fields = fields;
    }

    public String name() {
        return name;
    }
    public Field findField(String name) {
        for (int i=0; i < fields.length; i++) {
            if (fields[i].name().equals(name)) return fields[i];
        }
        return null;
    }

    /**
     * This method resolves an array of columns into the actual column headings to
     * be returned (i.e. * is resolved to all the column names). If an invalid 
     * column name is given, then this method will throw an SQLException.
     * 
     * @param columns
     * @return
     * @throws SQLException
     */
    public String[] resolveColumns(String[] columns) throws SQLException {
        String[] cols;
        if (columns.length == 1 && columns[0].equals("*")) {
            cols = new String[fields.length];
            for (int i=0;i<fields.length;i++) {
                cols[i] = fields[i].name();
            }
        } else {
            cols = new String[columns.length];
            for (int i=0; i<columns.length;i++) {
                Field field = findField(columns[i]);
                if (field == null) throw new SQLException("Unknown field '" + columns[i] + "' in table: '" + name + "'");
                cols[i] = columns[i];
            }
        }
        return cols;
    }

    public Field[] fields() {
        return fields;
    }

    /**
     * Checks that no columns marked "not null" have been missed.
     * @param cols
     * @throws SQLException 
     */
    public void checkForNotNulls(String[] cols) throws SQLException {
        // iterate all fields, if the field must be not null 
        // but cols do not have that column, then throw exception.
        for (Field field : fields) {
            if (field.isNotNull()) {
                boolean found = false;
                for (String col : cols) {
                    if (col.equals(field.name())) {
                        found = true;
                    }
                }
                if (!found) { // this not null field is not found
                    throw new SQLException("Error: '" + field.name() + "' cannot be null.");
                }
            }
        }
    }

}