2131

Create a new query named pelpro that will display the Surname, FirstName, Description fields (in the above order) from the r_orders query. Make sure the pelpro query displays Unique Values.

2145

Create a new query to update the ExecutedQuantity field with the value of the Quantity field of the Orders table where the date of order is less than or equal to 01/31/2004. Save the query under the name ExecutedQuantity.

2146

Modify the Query1 to display only the customers who have not placed any order, without displaying the OrderDate field. Save the changes.

2148

Modify the BigQuantitiesQ query so that when it is executed a new table named BigQuantities is created. The new table will contain all records where the quantity value is higher than or equal to 80. Save and run the query.

2149

Modify the BigQuantitiesQ query so that it returns only female clients and the total sum of quantities that each of them ordered. Save the changes.

2161

Create a query that will display only the OrderDate field from the Orders table, the Customer_Surname field from the Customers table and the Description field from the Products table.

Make sure the fields are displayed in the above order and all records of the Orders table are displayed even if there are not matching records in the Customers and Products tables. Save the query.

2162

Create a new query that will display all fields from the Products table and any records where the Description field value contains the phrase MATRIX Y AUTO

The Y character may be any character. Make sure there is an empty space before and after the Y character. Use the like operator. Save the query.

2163

Create a new query to retrieve all fields from the Products table and all records which display the phrase MATRIX Y AUTO in their Description field. The Y character may be either 1 or 3. Make sure there is an empty space before and after the Y character. Use the like operator only once. Save the query.

2164

Create a new query that will display all fields from the Products table and all records where the Description field value:

- begins with Viper and

- contains the letter V followed by 3 numbers.

Use the like operator only once. Save the query.

2165

Create a new query that will display all fields from the Products table and all records where the last number of the Price field value is number 9. Use the like operator. Save the query.

2166

Insert a new field in Query1 that will calculate the Price*Quantity. Name the new field myVal. Save the query.

2167

Insert a new field in Query1. Create a new expression that will display the result of the calculation between the isNew fields of the Products and Customers tables using the AND operator. Use the text isnewresult as name for the new field. Save the query.

2168

The IDProduct field should have been set as the Products table primary key. Create a new query named doublerecords that will display the IDProduct field of all duplicates.

2169

Create a new query named FalseOrders that will display all fields from the Orders table which are not assosiated with a customer. Save the query.

2192

Create a new field in the Payment query that will multiply the Price field value displayed in the Products table by number 0.23. Use TaxVal as name for the calculated field. Save and close the query.

2211

Create a new query named del1 that deletes all records from the Products table. Run the query.

2212

Create a new query named del2 that deletes all records where the Price field value is equal or less than 100 from the Products table. Run the query.

2213

Create a new query named update1 that will locate all prices lower than 100 in the Price field of the Products table and will update them to 0. Run the query.

2214

Create a new query named update2 that will locate all records where the Description field value of the Products table contains the word VIPER and update the Price field with Price * 1.1

Run the query only once.

2215

Insert one more field named VAT of Number-Double type in the Products table. Create a new query named update3 which will update all VAT field values to 19. Run the query.

2216

Insert one more field named VATValue of Number-Double type in the Products table. Create a new query named update4 that updates all VATValue field values to Price*0.19 with 2 decimal places rounding. Run the query.

2217

Locate the sqlscript.sql file which contains two SQL queries and is stored in the IL-ates\Access folder on your desktop. Then create a new query named create1 using the first query of the sqlscript.sql file. Then a second query named update1 using the second query of the sqlscript.sql file. Run both queries in the above order.

2218

Create a new query named insert1 to append only the Code and Description fields of the Products table to the Products500 table of the records where the value of the Price field is lower or equal to 500. Run the query.

2219

Create a new query named insert2 to add a new record in the Products table with the following data:

Code=1
Description=myproduct
Price=100

Run the query.

2220

Create a new query named min_q to display the minimum quantity contained in the Orders table. Use the text min1 as name of the calculated field.

2221

Create a new query named max_q to display the maximum quantity contained in the Orders table. Use the text max1 as name of the calculated field.

2222

Create a new totals query named sumorders based on the r_orders query that will display only the Surname, FirstName, sum1 (the sum in the TotalValue field) and max1 (the maximum value in the TotalValue field) fields. Also apply sorting by sum1 in descending order.

2223

Create a new crosstab query named mymap based on the r_orders query that will display the Description field as row heading, the Surname field as column heading and the sum of values in the TotalValue field named sum1.

Apply sorting by Description in ascending order.

2224

Create a new query named viper_sub based on the Products table that will display only the Description and Price fields. Make sure the query returns records where the description field value begins with VIPER and contains the word sub. Sort the resulting query output by Price in descending order.