Guruh talabasi Abdig’opporov javoxir Mysql 1-2-amaliy ishlar

Advanced Exercises

1. 
   Adding Photo: We could store photo in MySQL using data type of BLOB (Binary Large Object) (up to 64KB), MEDIUMBLOB (up to 16MBytes), LONGBOLB (up to 4GBytes). For example,
   
2. 
   -- Use function LOAD_FILE to load a picture file into a BLOB field
   
3. 
   UPDATE vehicles SET photo=LOAD_FILE('d:/temp/car.jpg') WHERE veh_reg_no = 'SBA1111A';
   

SELECT * FROM vehicles WHERE veh_reg_no = 'SBA1111A' \G
You can conveniently load and view the photo via graphical tools such as MySQL Workbench. To load a image in MySQL Workbench ⇒ right-click on the cell ⇒ Load Value From File ⇒ Select the image file. To view the image ⇒ right-click on the BLOB cell ⇒ Open Value in Editor ⇒ choose "Image" pane.
I also include a Java program for reading and writing image BLOB from/to the database, based on this example: "TestImageBLOB.java".

4. 
   VIEW: Create a VIEW called rental_prices on the rental_records with an additional column called price. Show all the records of the VIEW.
   
5. 
   DROP VIEW IF EXISTS rental_prices;
   
6. 
   CREATE VIEW rental_prices
   
7. 
   AS
   
8. 
   SELECT
   
9. 
   v.veh_reg_no AS `Vehicle No`,
   
10. 
    v.daily_rate AS `Daily Rate`,
    
11. 
    c.name AS `Customer Name`,
    
12. 
    c.discount*100 AS `Customer Discount (%)`,
    
13. 
    r.start_date AS `Start Date`,
    
14. 
    r.end_date AS `End Date`,
    
15. 
    DATEDIFF(r.end_date, r.start_date) AS `Duration`,
    
16. 
    -- Compute the rental price
    
17. 
    -- Preferred customer has discount, 20% discount for 7 or more days
    
18. 
    -- CAST the result from DOUBLE to DECIMAL(8,2)
    
19. 
    CAST(
    
20. 
    IF (DATEDIFF(r.end_date, r.start_date) < 7,
    
21. 
    DATEDIFF(r.end_date, r.start_date)*daily_rate*(1-discount),
    
22. 
    DATEDIFF(r.end_date, r.start_date)*daily_rate*(1-discount)*0.8)
    
23. 
    AS DECIMAL(8,2)) AS price
    
24. 
    FROM rental_records AS r
    
25. 
    INNER JOIN vehicles AS v USING (veh_reg_no)
    
26. 
    INNER JOIN customers AS c USING (customer_id);
    
27. 
    
28. 
    DESC `rental_prices`;
    
29. 
    SHOW CREATE VIEW `rental_prices` \G
    
30. 
    
31. 
    -- Try selecting all rows
    

SELECT * FROM `rental_prices`;
It is probably easier to compute the price using a program/procedure, instead of inside the view.

32. 
    From the payments table, create a view to show the outstanding balance.
    
33. 
    Define more views.
    
34. 
    FUNCTION: Write a function to compute the rental price.
    
35. 
    Define more procedures and functions.
    
36. 
    TRIGGER: Write a trigger for the created_date and created_by columns of the payments table.
    
37. 
    Define more triggers.
    
38. 
    Implement discount on weekday (Monday to Friday, except public holiday): Need to set up a new table called public_hoilday with columns date and description. Use function DAYOFWEEK (1=Sunday, …, 7=Saturday) to check for weekday or weekend.
    
39. 
    -- pseudocode for calculating rental price
    
40. 
    price = 0;
    
41. 
    for each date from start_date to end_date {
    
42. 
    if date is weekend or public_holiday, price += daily_rate;
    
43. 
    else price += daily_rate*(1-discount);
    
44. 
    }
    
45. 
    if (duration >= 7) price *= (1 - long_duration_discount);
    

price *= (1 - perferred_customer_discount);
7.2 Product Sales Database