kFejBXKC

1-- Remember to drop the table if it already exists
2
3--create the ACMEEMPLOYEE TABLE
4DROP TABLE ACMEEMPLOYEE;
5CREATE TABLE ACMEEMPLOYEE
6(
7	acmeID INT IDENTITY(1,1),
8	acmeFirst VARCHAR(20),
9	acmeSurname VARCHAR(25),
10	acmeAge INT,
11	acmeSalary DECIMAL(8,2),
12	acmeDepartment VARCHAR(20),
13);
14
15--INSERT rows of data into the TABLE ACMEEMPLOYEE
16INSERT INTO ACMEEMPLOYEE (acmeFirst, acmeSurname, acmeAge, acmeSalary, acmeDepartment)
17   VALUES('Jane', 'Smith', 35, 78000.45, 'Marketing'),
18		('John', 'Gordons', 56, 96000.99, 'Sales'),
19		('Geremy', 'Butler', 29, 65000.99, 'IT'),
20		('Sarah', 'Butler',27, 59000.00, 'Reception'),
21		('Lucy', 'Sullivan', 52, 89000.00, 'Sales');
22
23--Show the values inside the TABLE ACMEEMPLOYEE
24SELECT * FROM ACMEEMPLOYEE
25
26--age queries
27SELECT * FROM ACMEEMPLOYEE WHERE acmeAge >= 50;
28SELECT * FROM ACMEEMPLOYEE WHERE acmeAge >49;
29SELECT * FROM ACMEEMPLOYEE WHERE acmeAge >29 AND acmeAge <51;
30
31--using LIKE but = would have worked as well
32SELECT * FROM ACMEEMPLOYEE WHERE acmeAge >= 50 OR acmeDepartment LIKE 'marketing';
33
34--which of the employees are from sales
35SELECT * FROM ACMEEMPLOYEE WHERE acmeDepartment = 'sales';
36
37--which of the employees are not in sales (method #1)
38SELECT * FROM ACMEEMPLOYEE WHERE acmeDepartment <> 'sales';
39
40--which of the employees are not in sales (method #2)
41SELECT * FROM ACMEEMPLOYEE WHERE acmeDepartment NOT LIKE 'sales';
42SELECT * FROM ACMEEMPLOYEE WHERE acmeDepartment NOT LIKE 'sales' AND acmeSalary >70000;
43SELECT * FROM ACMEEMPLOYEE WHERE acmeDepartment NOT LIKE 'sales' AND acmeSalary <70000;
44
45--fun with wildcards
46SELECT * FROM ACMEEMPLOYEE WHERE acmeFirst LIKE '%y%';
47SELECT * FROM ACMEEMPLOYEE WHERE acmeSurname LIKE '[a-g]%';
48SELECT * FROM ACMEEMPLOYEE WHERE acmeFirst LIKE 'g%' AND acmeSurname LIKE 'b%';
49
50--more fun with wildcards
51SELECT * FROM ACMEEMPLOYEE WHERE acmeFirst LIKE '_g%';
52SELECT acmeFirst, acmeSurname, acmeDepartment FROM ACMEEMPLOYEE WHERE acmeSurname LIKE '%t%';
53SELECT * FROM ACMEEMPLOYEE WHERE acmeFirst LIKE '_a__' OR acmeFirst LIKE '_o__';
54
55
56--working with multiple tables: department, employee, acmeemployee
57
58--which employees earn more than $60k?
59SELECT * FROM EMPLOYEE WHERE empSalary > 60000;
60
61--which departments have a budget of more than 200k?
62SELECT * FROM DEPARTMENT WHERE deptBudget >200000;
63
64--which departments have a budget of 200k or more?
65SELECT * FROM DEPARTMENT WHERE deptBudget >=200000;
66
67--who is not a manager and earns more than 70k?
68SELECT * FROM EMPLOYEE WHERE empSalary > 70000 AND empTitle <> 'Manager';
69
70--who has worked there longer than jan 1st 1990?
71SELECT * FROM EMPLOYEE WHERE empStart >='1990-01-01';
72
73--which employee has a surname with 'lini' in it somewhere?
74SELECT * FROM EMPLOYEE WHERE empLast LIKE '%lini%';
75
76SELECT acmeFirst AS 'Employe Name' FROM ACMEEMPLOYEE;
77
78SELECT acmeFirst + ' ' + acmeSurname AS 'Full Name' FROM ACMEEMPLOYEE;
79
80SELECT acmeFirst + ' ' + acmeSurname AS fullName, acmeDepartment FROM ACMEEMPLOYEE
81 
82SELECT acmeAge+5 AS futureAge FROM ACMEEMPLOYEE;
83
84--Display the full name of all employees
85SELECT empFirst + ' ' + empLast AS 'Full Name' FROM EMPLOYEE;
86
87-- Show a person's full name & the dept they work in
88SELECT empFirst + ' ' + empLast AS 'Employee Name', empTitle FROM EMPLOYEE;
89
90--Show a persons full name and their Bonus based on 10% of their Salary
91SELECT acmeFirst + ' ' + acmeSurname AS 'Employee Name', acmeSalary * .1 AS 'Budget' FROM ACMEEMPLOYEE;
92
93SELECT avg(acmeAge) from ACMEEMPLOYEE;
94
95SELECT 
96	MIN(acmeAge) AS 'Youngest employee',
97	MAX(acmeAge) AS 'Eldest employee',
98	AVG(acmeAge) AS 'Average age of employees',
99	COUNT(*) AS 'Number of employees'
100FROM ACMEEMPLOYEE;
101
102SELECT MIN(empSalary) FROM EMPLOYEE;
103SELECT MAX(deptBudget) FROM DEPARTMENT;
104SELECT MIN(deptBudget) FROM DEPARTMENT;
105
106--subqueries
107SELECT MIN(deptBudget) FROM DEPARTMENT;
108
109--find the name of the person who's age is the same)
110SELECT acmeFirst + ' ' + acmeSurname AS 'Oldest employee', acmeAge FROM ACMEEMPLOYEE
111WHERE acmeAge = (select MAX(acmeAge) FROM ACMEEMPLOYEE);
112
113--who are the employees younger than the average age of our staff?
114SELECT acmeFirst + ' ' + acmeSurname AS 'Employee Name', acmeAge FROM ACMEEMPLOYEE
115WHERE acmeAge < (select AVG(acmeAge) FROM ACMEEMPLOYEE);
116
117--which department has the highest budget?
118SELECT deptName, deptBudget FROM DEPARTMENT
119WHERE deptBudget = (select MAX(deptBudget) FROM DEPARTMENT);
120
121--show the full name of the employee cocatenated in the field 'Staff member',
122-- their department and salary; who has the highest salary'
123-- fix this later
124SELECT acmeName + ' ' + acmeSurname AS 'Staff Member', acmeDepartment, acmeSalary FROM ACMEEMPLOYEE
125WHERE acmeSalary = (select MAX(acmeSalary) FROM ACMEEMPLOYEE);