sPtUq2r3

1-- by Will Bollock, LIS3784 P1
2-- 1.
3CREATE VIEW v_attorney_info AS
4    select
5    concat(per_lname, ", ", per_fname) as name,
6    concat(per_street, ", ", per_city,", ",per_state," ", per_zip) as address,
7    TIMESTAMPDIFF(year, per_dob, now()) as age,
8    CONCAT('$', FORMAT(aty_hourly_rate, 2)) as hourly_rate,
9    bar_name, spc_type
10    from person
11        natural join attorney
12        natural join bar
13        natural join specialty
14        order by per_lname;
15
16select 'display view v_attorney_info' as '';
17
18select * from v_attorney_info;
19
20--2.
21drop procedure if exists sp_num_judges_born_by_month;
22DELIMITER //
23CREATE PROCEDURE sp_num_judges_born_by_month()
24BEGIN
25    select month(per_dob) as month, monthname(per_dob) as month_name, count(*) as count
26    from person
27    natural join judge
28    group by month, month_name
29    order by month;
30END //
31DELIMITER ;
32
33CALL sp_num_judges_born_by_month();
34
35--3. 
36drop procedure if exists sp_cases_and_judges;
37DELIMITER //
38CREATE PROCEDURE sp_cases_and_judges()
39BEGIN
40
41select per_id, cse_id, cse_type, cse_description,
42    concat(per_fname, " ", per_lname) as name,
43    concat('(',substring(phn_num,1,3),')', substring(phn_num,4,3), '-', substring(phn_num,7,4)) as judge_office_num,
44    phn_type,
45    jud_years_in_practice,
46    cse_start_date,
47    cse_end_date
48from person
49    natural join judge
50    natural join `case`
51    natural join phone
52where per_type='j'
53order by per_lname;
54
55END //
56DELIMITER ;
57
58CALL sp_cases_and_judges();
59
60
61--4. 
62DROP TRIGGER IF EXISTS trg_judge_history_after_insert;
63DELIMITER //
64CREATE TRIGGER trg_judge_history_after_insert
65AFTER INSERT ON judge
66FOR EACH ROW
67BEGIN
68    INSERT INTO judge_hist
69    (per_id, jhs_crt, jhs_date, jhs_type, jhs_salary, jhs_notes)
70    VALUES
71    (
72        NEW.per_id, NEW.crt_id, current_timestamp(), 'i', NEW.jud_salary,
73        concat("modifying user: ", user(), "Notes: ", NEW.jud_notes)
74    );
75END //
76DELIMITER ;
77
78select 'fire trigger by inserting record into judge table' as '';
79do sleep(5);
80
81--5.
82DROP TRIGGER IF EXISTS trg_judge_history_after_update
83DELIMITER //
84CREATE TRIGGER trg_judge_history_after_update
85AFTER UPDATE ON judge
86FOR EACH ROW
87BEGIN
88
89   INSERT INTO judge_hist
90    (per_id, jhs_crt_id, jhs_date, jhs_type, jhs_salary, jhs_notes)
91    VALUES
92    (
93        NEW.per_id, NEW.crt_id, current_timestamp(), 'u', NEW.jud_salary,
94        concat("modifying user: ", user(), "Notes: ", NEW.jud_notes)
95    );
96END //
97DELIMITER ;
98
99select 'fire trigger by updating latest judge entry (salary and notes)' as '';
100do sleep(5);
101
102--6. 
103drop procedure if exists sp_add_judge_record;
104DELIMITER //
105
106CREATE PROCEDURE sp_add_judge_record()
107BEGIN
108    INSERT INTO judge
109     (per_id, crt_id, jud_salary, jud_years_in_practice, jud_notes)
110     VALUES
111     (6,1,110000,0,concat("New judge was former attorney. ", "Modifying event creator: ", current_user()));
112END //
113
114DELIMITER ;
115
116--Extra Credit/Stored Procedures
117DROP EVENT IF EXISTS one_time_add_judge;
118
119DELIMITER //
120CREATE EVENT IF NOT EXISTS one_time_add_judge
121ON SCHEDULE
122    AT NOW() + INTERVAL 5 SECOND
123COMMENT 'adds a judge record only one time'
124DO
125BEGIN
126    CALL sp_add_judge_record();
127END//
128
129DELIMITER ;
130
131-- SSN
132DROP PROCEDURE IF EXISTS CreatePersonSSN;
133DELIMITER $$
134CREATE PROCEDURE CreatePersonSSN()
135BEGIN
136
137    DECLARE x, y INT;
138    SET x = 1;
139
140    select count(*) into y from person;
141
142    WHILE x <= y DO
143
144    SET @salt=RANDOM_BYTES(64);
145    SET @ran_num=FLOOR(RAND()*(999999999-111111111+1))+111111111;
146    SET @ssn=unhex(sha2(concat(@salt, @ran_num), 512));
147
148    update person
149    set per_ssn=@ssn, per_salt=@salt
150    where per_id=x;
151
152    SET x = x + 1;
153
154    END WHILE;
155
156END$$
157DELIMITER ;
158call CreatePersonSSN();