9EAH93Yw

1sale_id | sale_amount | day_id | employee_id
21         500           1        1
32         600           1        1
43         750           1        1
5	
6DROP TABLE IF EXISTS `employees`;
7
8CREATE TABLE IF NOT EXISTS `tablename` (
9    `emp_id`        INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT                  COMMENT 'Primary Key',
10    `emp_name`      VARCHAR(200)            NOT NULL    DEFAULT ''                      COMMENT 'Name of employee',
11    `emp_email`     VARCHAR(200)            NOT NULL    DEFAULT ''                      COMMENT 'Email address of employee',
12    `emp_password`  VARCHAR(200)            NOT NULL    DEFAULT ''                      COMMENT 'Password encoded',
13    PRIMARY KEY (`emp_id`),
14    UNIQUE `idx_emp_name` (`emp_name`),
15    UNIQUE `idx_emp_email` (`emp_email`)
16) 
17    ENGINE=MyISAM 
18    AUTO_INCREMENT=1 
19    DEFAULT CHARSET=utf8 
20    COLLATE=utf8_unicode_ci
21    COMMENT 'List of Employees';
22	
23DROP TABLE IF EXISTS `sales`;
24
25CREATE TABLE IF NOT EXISTS `tablename` (
26    `sale_id`           INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT                  COMMENT 'Primary Key',
27    `sale_amount`       DOUBLE                  NOT NULL    DEFAULT 0                       COMMENT 'Amount Of Sale',
28    `sale_date`         DATETIME                NOT NULL    DEFAULT '0000-00-00'            COMMENT 'Date/Time of sale',
29    `sale_emp_id`       INT(11)                 NOT NULL                                    COMMENT 'FK To employees',
30    PRIMARY KEY (`sale_id`),
31    KEY `idx_sale_date` (`sale_date`),
32    KEY `idx_sale_emp_id` (`sale_emp_id`)
33) 
34    ENGINE=MyISAM 
35    AUTO_INCREMENT=1 
36    DEFAULT CHARSET=utf8 
37    COLLATE=utf8_unicode_ci
38    COMMENT 'List of Employees';
39	
40SELECT
41    a.`emp_name`,
42    WEEK(b.`sale_date`) as `week_no`,
43    SUM(b.`sale_amount`) as `total_sales_for_week`
44FROM `employees` a
45JOIN `sales` b
46    ON a.`emp_id` = b.`sales_emp_id`
47GROUP BY WEEK(b.`sale_date`),a.`emp_id` WITH ROLLUP;
48
49-- ==============================================================================================
50-- TRIGGER: Update sale_date timestamp on sales
51-- ==============================================================================================
52DELIMITER |
53
54DROP TRIGGER IF EXISTS `sales_CreatedTS`|
55
56CREATE TRIGGER `sales_CreatedTS` BEFORE INSERT ON `sales`
57FOR EACH ROW
58BEGIN
59    SET NEW.`sale_date` = CURRENT_TIMESTAMP;
60END|
61
62DELIMITER ;