Bushnell Trophy Magnifier

cybercriminals Applying SQL Part 4

! - @ Page {size: 21cm 29.7cm; margin: 2cm} P {margin-bottom: 0.21cm } ->

start with September 1, 1920 new exercises to continue our tutorial database (see third

).

To begin this part we have to create a new database to which we will call

company which in turn will have 2 related tables

employees and departments

. CREATE DATABASE `company`; USE `company`, (to proceed to create the tables only if we are in console mode) CREATE TABLE `departments` (`CODIGODEP ` INTEGER (11), `NOMBREDEP

`VARCHAR (100), BUDGET` `INTEGER (11),

PRIMARY KEY (` CODIGODEP `)) ENGINE = InnoDB

CREATE TABLE `employee` (`RUT ` VARCHAR (10),
`name` VARCHAR (100),
`LAST` VARCHAR (200), `DEPARTMENT` INTEGER (11), PRIMARY KEY (` `RUT), ) ENGINE = InnoDB ALTER TABLE EMPLOYEES ADD FOREIGN KEY (` DEPARTMENT `) REFERENCES` departments `(` CODIGODEP `) ON DELETE CASCADE ON UPDATE CASCADE

Now insert records into the tables created


INSERT INTO `departments` (`CODIGODEP`, `NOMBREDEP`, `Budget`) VALUES
(1, 'Computer', 1000000),
(2, 'Personal', 9000000),
(13, 'Security' 500000),
(56, 'HR', 5000000),
(77, 'Administration', 2000000);
COMMIT INSERT INTO `

employee` (`RUT`, `name`, `LAST`, `DEPARTMENT`)
VALUES ('10333444-1 ',' Sofia ',' Quintana ', 13),
('10352097-6', 'Maria', 'Contreras', 77),
('11111111-1', 'Pedro' , 'Tapia', 2),
('12052097-0 ',' Jorge ',' Lara ', 1),
('13456789-2', 'Maria', 'Bathroom', 13),
('16152094 -2 ',' Jose ',' Perez ', 1),
('21345678-7', 'Manuel', 'Rodriguez', 56),
('23567876-4 ',' Ignacio ',' Race ' 2),
('6435456-6 ',' Peter ',' Asnar ', 56),
('7654231-9', 'Elias', 'Soto', 13);
COMMIT;
(All data are invented and have no relation to real life people xD)






Exercises:

! - @ page {size: 21cm 29.7cm; margin: 2cm} P {margin-bottom: 0.21cm} ->



1. Get the names of employees


SQL statement: SELECT NAME FROM

EMPLOYEES;



Query Result:


2. Get the names of employees No repeats Note
have to create a new worker with a name will be repeated in this case Perez to run this query (this because so far we have no name repeated) INSERT INTO
employees (RUT , NAME, LAST NAME, DEPARTMENT)
VALUES ('13987345-9 ',' Miguel ',' Perez ', 13);

SQL statement: SELECT NAME FROM EMPLOYEE GROUP BY NAME;
Result query: (as we only see a Perez, thanks to the Group By)
3. Get all the data employees whose last name is Perez SQL statement: SELECT * FROM
EMPLOYEES WHERE NAME = 'Perez';
Query Result:




4. Get all the details of employees whose last name is Perez and those named Contreras SQL statement: SELECT * FROM
EMPLOYEES WHERE NAME = 'Perez' or NAME = 'Contreras';

Results query:
5. Get all the information of employees working in the department 1
SQL statement: SELECT RUT, NAME, LAST NAME, DEPT FROM EMPLOYEE INNER JOIN DEPARTMENT CODIGODEP ON WHERE DEPARTMENT = DEPARTMENT = 1;
Query Result:

6. Get all the information of employees working for the Department 1
and the department 13 Important Note:
As the rut is a primary key can not repeat a worker in another department and we will fail primary key, the solution would include a field
rut in the department table and thus we would have more or less solved this but as I did at the beginning and not create new relationships Confucianism'll add a field called department 2 in the Employees table. ALTER TABLE EMPLOYEE ADD
DEPARTAMENTO2 INTEGER DEFAULT NULL;


The default value of the fields is Null (ie non-zero and empty).
now assign to workers to another company department. (Those who are in Department 1 also take you to 13) DEPARTAMENTO2 UPDATE EMPLOYEES SET = 13 WHERE department = 1;
And now to carry out the sentence requested in the exercise
SQL statement:
SELECT RUT, NAME, LAST NAME, DEPARTMENT DEPARTAMENTO2 FROM EMPLOYEES WHERE DEPARTMENT = 1 OR DEPARTMENT AND DEPARTAMENTO2 = 13 = 1 OR DEPARTAMENTO2 = 13;
Note: I asked 1 to 13 in both fields to filter the query correctamete.

Query Result:



7. Get all the details of employees whose last name starts with P. SQL statement: SELECT * FROM
EMPLOYEES WHERE NAME LIKE 'P%';
Note:




LIKE is another keyword that is used in the WHERE clause . Basically, LIKE

allows you to make a search based on a pattern rather than specifying exactly what you want (as in IN
) or determine a range (as in BETWEEN

). In this case LIKE 'P%'; deliver all surnames beginning with P Query Result: 8. Get the total budget for all departments. SQL statement: SELECT NOMBREDEP, BUDGET FROM DEPARTMENTS;
Query Result:
9. Get the number of employees in each department.

SQL statement: SELECT COUNT (DEPARTMENT), NOMBREDEP FROM DEPARTMENTS INNER JOIN EMPLOYEES ON DEPARTMENT = CODIGODEP
DEPARTAMENTO2 = CODIGODEP GROUP OR BY NOMBREDEP;
NOTE: This query
I have considered that there are employees who work in 2 departments at once for it to do the count department, but turn in the inner join enter

departamento2 Query Result:

10. Get a listing full each employee for each employee including data syu department.

SQL statement: SELECT

NOMBREDEP, FIRST, LAST, RUT
FROM DEPARTMENTS INNER JOIN EMPLOYEES ON
CODIGODEP = DEPARTMENT OR ORDER BY CODIGODEP = DEPARTAMENTO2 NOMBREDEP;

NOTE: This refers to the me the most was to make the select optimal to join Enner department and employees as mentioning as I have employees come in 2 departments at a time (it is a more realistic example)
:



11. Get a complete listing of each employee for each employee including the names next to their name and budget departament0 SQL statement: SELECT

, LAST, RUT, NOMBREDEP,

BUDGET FROM DEPARTMENTS INNER JOIN EMPLOYEES ON
CODIGODEP =
DEPARTMENT OR ORDER CODIGODEP = DEPARTAMENTO2 BY NOMBREDEP; Note: Users who belong to two departments appear with your budget in both departments.)

12. Get a first and last names of employees working in departments with a budget greater than $ 500,000

SQL statement: SELECT , LAST
FROM DEPARTMENTS INNER JOIN EMPLOYEES ON CODIGODEP = DEPARTMENT OR CODIGODEP = DEPARTAMENTO2 WHERE BUDGET> 500000 ORDER BY NOMBREDEP;



13. Get a data from the departments whose budget is greater than the average price of all departments. SQL statement: SELECT

NOMBREDEP, BUDGET FROM DEPARTMENTS WHERE BUDGET> (SELECT AVG (BUDGET) FROM DEPARTMENT)
14. Get only the names of the departments that have more than 2 employees.
SQL statement:
NOMBREDEP SELECT FROM EMPLOYEE INNER JOIN DEPARTMENT ON DEPARTMENT OR = CODIGODEP
DEPARTAMENTO2 = CODIGODEP NOMBREDEP GROUP BY HAVING COUNT (DEPARTMENT)> 2;
I personally believe that this consultation is more complete indication also how many employees does the department above.

SELECT COUNT (*) As AMOUNT, NOMBREDEP
DEPARTMENTS FROM EMPLOYEE INNER JOIN DEPARTMENT ON
OR = CODIGODEP DEPARTAMENTO2 = CODIGODEP NOMBREDEP
GROUP BY HAVING COUNT (DEPARTMENT)> 2;





15. Add new Quality department with code 40000 and budgeted $ 11 ..

SQL statement: INSERT INTO

DEPARTMENTS (CODIGODEP, NOMBREDEP, BUDGET)
VALUES (11, 'quality', 40000); Select * from departments;

16 . Add a new employee to department quality (Esther Vazquez rut 23456789-9).
SQL statement: INSERT INTO
EMPLOYEES (RUT, NAME, LAST NAME, DEPARTMENT) VALUES (23456789-9, 'Esther', 'Vazquez', 11);

17. Apply a 10% budget cut to all departments.

SQL statement: UPDATE DEPARTMENTS SET BUDGET = BUDGET * 0.9;


18. Department workers reallocate 11 (Quality) to the department 1 (computer). SQL statement: UPDATE

SET DEPARTMENT EMPLOYEES WHERE DEPARTMENT = 1 = 11;

19. Remove all employees of the department 1 (computer).

Note: As I indicated there are employees who are on 2 departments and disposed Computer departamento2 also be removed from the computer science but this is not so this year will be settled in 3 steps.



Important: Always be very careful in making a direct delete the database because if you do not assign a condition Where all records will be deleted. (It is no exaggeration to make a backup before doing a delete) remember that here there is no undo command ....

1 .- First remove all empledos working in Department 1 and not having a job in a second department (for departamento2 default should be NULL)


SQL statement:

DELETE FROM EMPLOYEES WHERE DEPARTMENT = 1 AND DEPARTAMENTO2 NULL;

2 .- As I is for previous consultations in column 2 only department employees are assigned to the department 13 will update the information so that those who are computer (which by default are also on the dept 13) will be assigned only to the department 13, but as your first apartment.

<=> SQL statement:

UPDATE EMPLOYEES SET WHERE DEPARTMENT = DEPARTAMENTO2 DEPARTAMENTO2 = 13; 3 .- Now that you have my updated information proceed to remove the information from leaving depratamento2 NULL field (remember that the same information already in the department and this column is just to keep the information repeated departamento2)

SQL statement: UPDATE EMPLOYEES SET
DEPARTAMENTO2 = NULL WHERE DEPARTAMENTO2 = 13;

Let's see how it looks our table select * from employees;
20. Remove all employees who work in a department with a budget greater than $ 2000000.

SQL statement:
DELETE FROM EMPLOYEES A
INNER JOIN DEPARTMENTS A = CODIGODEP
DEPARTMENT ON WHERE BUDGET> 2000000
;

Note:
Here I used an inner join again to join the tables but for the delete to succeed you must assign an alias to the table used in this If my nickname is A
(if you look my sentence says DELETE A FROM. ...).

stay Let's see how our information .... now only should we have employees in departments with budgets of up to $ 2000000 SELECT RUT, FIRST, LAST, NOMBREDEP DEPARTMENT, DEPARTAMENTO2 FROM EMPLOYEE INNER JOIN DEPARTMENT ON DEPARTMENT = CODIGODEP;

21. Remove all employees. Note:

this statement is the easiest of all

, but do not forget to make a

backup before and if your boss gave them the order is always the possibility that you will regret or that we need that information. SQL statement: DELETE FROM

EMPLOYEES; see how it turned out our information (although it is ..... there is nothing)

SELECT * FROM EMPLOYEES;
It was all for now .... any public consultation comments.