Question

Write an update statement modifies the sales_total column of sales_totals table.

Modify the sales_total as "2345.56" for the representative with id 4 for the year 2017

After execution of your query, data of sales_Totals table should look as shown below :

rep_id sales_year sales_total

1 2016 1274856.38

1 2017 923746.85

1 2018 998337.46

2 2016 978465.99

2 2017 974853.81

2 2018 887695.75

3 2016 1032875.48

3 2017 1132744.56

4 2017 2345.56

4 2018 72443.37

5 2017 422847.86

5 2018 45182.44

2. Consider the modified “sales Total “table from 5th question to solve this query.

Write SELECT statement using subquery that returns these columns from sales_totals table

“Representative ID” : rep_id column

“Year of sales” : sales_year column

“Total sales”: sales_total column

Return only the rows that have lowest sales_total.

Answer 1

1) Modify the sales_total as "2345.56" for the representative with id 4 for the year 2017

ans == we used here rep_id and Sales_year as conditon to update the row value of sale_total

query==

UPDATE sales_totals
SET sales_total = '2345.56' WHERE rep_id =4 AND sales_year=2017;

2)

Write SELECT statement using subquery that returns these columns from sales_totals table

“Representative ID” : rep_id column

“Year of sales” : sales_year column

“Total sales”: sales_total column

Return only the rows that have lowest sales_total.

ans== We used Min function on sale total in subquery which returns Minimum sale total and then compared with query.

Query==

SELECT rep_id AS "Reprensentative ID",sales_year AS "Year of sales",sales_total AS "Sale Totals" FROM `sales_totals` WHERE sales_total = (SELECT MIN(sales_total) FROM sales_totals);