x08Ybsab

1SELECT * FROM dept_emp;
2SELECT * FROM salaries;
3SELECT COUNT(*) FROM employees;
4SELECT * FROM employees WHERE emp_no = 900002;
5SELECT * FROM dept_manager;
6SELECT * FROM titles;
7SELECT * FROM departments;
8
9CREATE VIEW avgsalary_dept AS
10  SELECT dept_no, AVG(salary) as avg_s FROM dept_emp as d
11    JOIN salaries as s ON d.emp_no = s.emp_no GROUP BY dept_no;
12
13DROP VIEW avgsalary_dept;
14
15CREATE VIEW maxsalary_emp AS
16  SELECT emp_no, MAX(salary) as max_s FROM salaries GROUP BY emp_no;
17
18SELECT MAX(salary) as max_s FROM salaries;
19SELECT * FROM salaries WHERE salary = 155709;
20DELETE FROM salaries WHERE emp_no = 47978;
21
22CREATE VIEW test AS
23  SELECT dept_no, COUNT(DISTINCT emp_no) as count_s FROM dept_emp GROUP BY dept_no;
24
25DROP VIEW test;
26
27SELECT emp_no, salary FROM salaries WHERE to_date = "9999-01-01";
28CREATE VIEW salary_dept AS
29  SELECT dept_no, SUM(s.salary) as dep_sal FROM dept_emp as d
30    JOIN salaries as s
31      ON d.emp_no = s.emp_no WHERE s.to_date = "9999-01-01" AND d.to_date = "9999-01-01" GROUP BY dept_no;
32
33
34CREATE VIEW stuff_info AS
35SELECT e.first_name, e.last_name, t.title, d.dept_name FROM employees AS e
36  JOIN titles AS t ON e.emp_no = t.emp_no
37    JOIN dept_emp as de ON e.emp_no = de.emp_no
38      JOIN departments AS d ON de.dept_no = d.dept_no WHERE t.to_date = "9999-01-01" AND de.to_date = "9999-01-01";
39
40SELECT * FROM stuff_info;
41
42DELIMITER //
43DROP FUNCTION IF EXISTS search //
44CREATE FUNCTION search(name CHAR(50), lname CHAR(50)) RETURNS CHAR(50)
45  BEGIN
46    DECLARE title, depart CHAR(50);
47    SELECT si.title, si.dept_name FROM stuff_info as si WHERE si.first_name = name AND si.last_name = lname
48    INTO title, depart;
49    RETURN concat(name,' ',lname,' - ',title,' in ',depart);
50  END //
51DELIMITER ;
52
53SELECT search('Basil', 'Tramer')as emp_info;
54
55
56CREATE TABLE salary (
57  emp_no INT (10) NOT NULL,
58  bonus INT(20) NOT NULL,
59  hire_date DATE,
60  FOREIGN KEY (emp_no) REFERENCES employees (emp_no),
61  PRIMARY KEY (emp_no)
62);
63
64DROP TABLE salary;
65
66SELECT * FROM salary;
67
68DELIMITER //
69CREATE TRIGGER bonus_new_emp AFTER INSERT ON employees FOR EACH ROW
70  BEGIN
71    INSERT INTO salary (emp_no, bonus, hire_date)
72    VALUES (NEW.emp_no, 50000, NOW());
73  END //
74DELIMITER ;
75
76DROP TRIGGER bonus_new_emp;
77
78INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
79    VALUES (900002, '1989-04-08', 'Andrey', 'Katkov', 'M', NOW());
80
81DELETE FROM employees WHERE emp_no = 900002;