Computer Hardware - Assignment Example

SQL Code:SELECT product_no, plant_name FROM product WHERE plant_name LIKE '%rose%'ii.) List the product numbers and plant names of any products where a quantity of 20 or more has been ordered on a single order.SQL Code: SELECT product.product_no, product.plant_name, order_line.qty_ordered FROM product, order_line WHERE order_line.qty_ordered =>20iii.) List the names and addresses of any customers who have ordered plants that like to grow on 'clay soil'.SQL Code: SELECT customer. name, the customer. address, product.

soil_type FROM customer, product WHERE product.soil_type = 'clay soil'iv.) List the order no's and order dates of any orders that have been placed for more than 10 different products.SQL Code:SELECT order.order_no, order.order_date, order_line.qty_ordered, order_line.order_no FROM order, order_line WHERE order.order_no = order_line.order_no AND order_line.qty_ordered > 10v.) List the names and addresses of those customers where the average value of their orders is more than twice the average value of all customer orders.

SQL Code: SELECT customer. name, the customer. address, AVG(order.order_total) as total FROM customer, order WHERE order.order_total > (2*total)Question 3:The Kennel Club database includes the following schema:Member (KC_memb_no {pk}, Name, Address, Tel_num)Show (Show_name {pk}, Year {pk}, Venue)Dog (KC_reg_no {pk}, Dog_name, Breed_name, KC_memb_no)Entry (Show_name {pk}, KC_reg_no {pk}, Year {pk}, final_position)Primary key attributes are indicated with {pk}. Notes - final_position is a numeric attribute that indicates where the dog was placed in the show, i.e. 1 = 1st place.

Specify the following queries in relational algebra:i.). Inspectors have a unique inspector number, and the details of their name and position are held on record. Products are identified by their name and records of the product type and a description of the product are kept. Primary key attributes are indicated with {pk}. N.B. All dates are stored in the format-number (6) to represent YYMMDD, i.e. the date 16th June 2005 will be stored as 050616. Selling_price is stored in the format-number (4, 2).

Quantities are stored in the format-number (4). All other attributes are stored as char (). The order_total is the total value of the order. The qty_ordered is the quantity of a particular product ordered. Soil_type refers to the favored growing conditions of the plant, e.g. 'sandy soil'. Specify the following queries in SQL: Primary key attributes are indicated with {pk}. Notes - final_position is a numeric attribute that indicates where the dog was placed in the show, i.e. 1 = 1st place. Specify the following queries in relational algebra: PROJECTDog.

Dog_name, Dog.KC_reg_no, Entry.Show_name, Entry.Year, Entry.final_position (SELECT Entry.Show_name = 'Top Dog' and Entry.Year=2002 and Entry.Year =2003 group by Entry.final_position (Dog X Entry))