· 8 years ago · Jun 06, 2018, 05:48 PM
1/*
2Name: Anju Chawla
3Date : Feb. 5, 2018
4Purpose: To create a database with a table therein that stores
5information about employees
6Query the data in the table and perform updates and deletes in the table
7*/
8
9#set the environment
10SET SQL_MODE = 'STRICT_ALL_TABLES';
11SET SQL_SAFE_UPDATES = 0;
12
13#drop existing database, if it exists
14DROP DATABASE IF EXISTS db_employee;
15#create the database
16CREATE DATABASE db_employee;
17#use the database
18USE db_employee;
19
20#drop the table, if it exists
21DROP TABLE IF EXISTS tbl_employee;
22#create the table structure; notice the use of database_name.table_name.
23#You may not 'use' the database but may qualify the table wherever needed
24
25CREATE TABLE db_employee.tbl_employee(
26 employee_number SMALLINT UNSIGNED PRIMARY KEY,
27 employee_name VARCHAR(40) NOT NULL,
28 job_description VARCHAR(20) NOT NULL DEFAULT 'CLERK',
29 manager_number SMALLINT UNSIGNED ,
30 hire_date DATE NOT NULL, #stored in 'yyyy-mm-dd' format
31 salary DEC(7,2) UNSIGNED NOT NULL,
32 commission DEC(7,2) UNSIGNED DEFAULT 0
33);
34
35# inserting records into the table
36
37INSERT INTO tbl_employee(employee_number,employee_name,job_description,manager_number, hire_date, salary, commission)
38VALUES
39(7839, 'KING', 'PRESIDENT',NULL, '1981-11-17', 5000, default),
40(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, default),
41(7782, 'CLARK', 'MANAGER',7839, '1981-06-09', 2450, default),
42(7566, 'JONES', 'MANAGER',7839, '1981-04-02', 2975, default),
43(7654, 'MARTIN', 'SALESMAN',7698, '1981-09-28', 1250, 1400),
44(7499, 'ALLEN', 'SALESMAN',7698, '1981-02-20', 1600, 300),
45(7844, 'TURNER', 'SALESMAN',7698, '1981-09-08', 1500, 0),
46(7900, 'JAMES', 'CLERK',7698, '1981-12-03', 950, default),
47(7521, 'WARD', 'SALESMAN',7698, '1981-02-22', 1250, 500),
48(7902, 'FORD', 'ANALYST',7566, '1981-12-03', 3000, default),
49(7369, 'SMITH', 'CLERK',7902, '1980-12-17', 800, default),
50(7788, 'SCOTT', 'ANALYST',7566, '1982-12-09', 3000, default),
51(7876, 'ADAMS', 'CLERK',7788, '1983-01-12', 1100, default),
52(7934, 'MILLER', 'CLERK',7782, '1982-01-23', 1300, default);
53
54/*
55SELECT field_list
56FROM table_name
57[WHERE condition]
58[ORDER BY field_name(s)]
59[LIMIT offset,how_many]
60*/
61
62#select all the records
63SELECT * FROM tbl_employee;
64
65#1) A list of the all the employee names and their job description.
66SELECT employee_name 'Employee Name', job_description 'Job Description'
67FROM tbl_employee;
68
69#2) The names of all the managers.
70SELECT employee_name AS 'Manager Name'
71FROM tbl_employee
72WHERE job_description = TRIM('manager'); #exact match; case insensitive; trailing blanks are ignored by default.
73
74SELECT employee_name AS 'Manager Name'
75FROM tbl_employee
76WHERE job_description LIKE TRIM('%manager%');
77
78#3) The name(s) of all employees who do not have a manager(use manager_number field to retrieve information)
79SELECT employee_name AS 'Manager Name'
80FROM tbl_employee
81WHERE manager_number IS NULL;
82
83#4) The names and hire date of the employees who were hired after 1981. Don't peek in the table. You have no clue of the other hiring dates.
84# Note that dates can be written as text in the format 'yyyy-mm-dd', as I have done or as number in the format yyyymmdd.
85#Do not use LIKE with dates since they are not text as per se.
86SELECT employee_name, hire_date
87FROM tbl_employee
88WHERE hire_date >= CAST('1982-01-01' AS Date);
89
90SELECT employee_name, hire_date
91FROM tbl_employee
92WHERE hire_date > CAST('1981-12-31' AS Date);
93
94SELECT employee_name, hire_date
95FROM tbl_employee
96WHERE hire_date >= CAST(19820101 AS Date);
97
98#5) The names and hire date of all employees that were hired in 1981 only.
99SELECT employee_name, hire_date
100FROM tbl_employee
101WHERE hire_date >= CAST('1981-01-01' AS Date) AND hire_date <= CAST('1981-12-31' AS Date);
102
103SELECT employee_name, hire_date
104FROM tbl_employee
105WHERE hire_date BETWEEN CAST('1981-01-01' AS Date) AND CAST('1981-12-31' AS Date);
106
107SELECT employee_name, hire_date
108FROM tbl_employee
109WHERE YEAR(hire_date) = 1981;
110
111/*DO NOT USE THIS BECAUSE Date IS NOT Text
112SELECT employee_name, hire_date
113FROM tbl_employee
114WHERE hire_date LIKE '1981%';*/
115
116#6) List of the names and commission of all employees that have earned a commission
117SELECT employee_name, commission
118FROM tbl_employee
119WHERE commission > 0;
120
121#7) The names and salaries of all employees that earn at least 5000.
122
123 SELECT employee_name, salary
124 FROM tbl_employee
125 WHERE salary >= 5000;
126
127#8) The names, job description and salaries of all employess that earn between 1000 and 2000, inclusive.Give two solutions for this query.
128
129 #i)
130 SELECT employee_name, job_description, salary
131 FROM tbl_employee
132 WHERE salary >=1000 AND salary <= 2000;
133
134 #ii)
135 SELECT employee_name, job_description, salary
136 FROM tbl_employee
137 WHERE salary BETWEEN 1000 AND 2000 ;
138
139#9) The names and salaries of all the clerk employees.
140
141 SELECT employee_name, salary
142 FROM tbl_employee
143 WHERE job_description='clerk';
144
145#10) The names and job description of the employees that have no commission
146
147 #i)
148 SELECT employee_name, job_description
149 FROM tbl_employee
150 WHERE commission = 0;
151
152#11) Find the names and job description of employees who is either a manager, analyst or salesman. Give two different solutions for this query.
153
154 #i)
155 SELECT employee_name, job_description
156 FROM tbl_employee
157 WHERE job_description='manager' OR job_description='analyst' OR job_description='salesman';
158
159 #ii)
160 SELECT employee_name, job_description
161 FROM tbl_employee
162 WHERE job_description IN ('manager', 'analyst', 'salesman');
163
164#12) List the names and job description of employees who is neither a salesman nor a clerk.
165
166 #i)
167 SELECT employee_name, job_description
168 FROM tbl_employee
169 WHERE job_description != 'salesman' AND job_description != 'clerk' ;
170
171 #ii)
172 SELECT employee_name, job_description
173 FROM tbl_employee
174 WHERE job_description <> 'salesman' AND job_description <>'clerk' ;
175
176
177 #iii)
178 SELECT employee_name, job_description
179 FROM tbl_employee
180 WHERE job_description NOT IN ('salesman', 'clerk') ;
181
182 #iv)
183 SELECT employee_name, job_description
184 FROM tbl_employee
185 WHERE NOT job_description = 'salesman' AND NOT job_description = 'clerk' ;
186
187 #v)
188 SELECT employee_name, job_description
189 FROM tbl_employee
190 WHERE NOT (job_description = 'salesman' OR job_description = 'clerk') ;
191
192#Now for some updates to the data
193/*
194UPDATE tbl_name
195SET col_name = value [, col_name=value...]
196[WHERE cond]
197[LIMIT how_many]
198*/
199DESCRIBE tbl_employee;
200
201#13) Update Turner's commission to $400.
202SELECT employee_name, commission
203FROM tbl_employee;
204
205UPDATE tbl_employee
206SET commission = 400
207WHERE employee_name = 'Turner';
208
209SELECT employee_name, commission
210FROM tbl_employee;
211
212
213#14) Change the job description of all 'ANALYST' to 'MARKET ANALYST'.
214SELECT employee_name, job_description
215FROM tbl_employee
216WHERE job_description = 'Analyst';
217
218UPDATE tbl_employee
219SET job_description = 'Market Analyst'
220WHERE job_description = 'Analyst';
221
222SELECT employee_name, job_description
223FROM tbl_employee
224WHERE job_description = 'Market Analyst';
225
226#15) Update all NULL commissions to zero.
227SELECT employee_name, commission
228FROM tbl_employee
229WHERE commission IS NULL;
230
231UPDATE tbl_employee
232SET commission = 0
233WHERE commission IS NULL;
234
235SELECT employee_name, commission
236FROM tbl_employee
237WHERE commission = 0;
238
239
240
241#16) Update Miller's hire date to Jan 25, 1982.
242SELECT employee_name, hire_date
243FROM tbl_employee;
244
245UPDATE tbl_employee
246SET hire_date = CAST('1982-01-25' AS DATE)
247WHERE employee_name = 'Miller';
248
249SELECT employee_name, hire_date
250FROM tbl_employee;
251
252#17) Update Martin's infomation. He is now a manager and will not have any defined commision
253SELECT employee_name, job_description, commission
254FROM tbl_employee;
255
256UPDATE tbl_employee
257SET job_description = 'MANAGER' AND commission IS NULL
258WHERE employee_name = 'Martin';
259
260SELECT employee_name, job_description, commission
261FROM tbl_employee;
262
263#18) Update all records - add 500 to all commissions.
264#When all records are to be updated, you don't require a where clause
265
266
267
268#19) Update the first 2 records, set commission to 0
269
270
271#Let us delete some information we do not need
272# DELETE FROM tbl_name [WHERE cond]
273#[#LIMIT how_many]
274
275#20) Adams has left the company. Delete his record.
276
277
278
279#21) Delete the records of all employees who joined the company in 1982.
280
281
282#22)Delete the first 5 records
283
284
285#23)Delete all records -BEWARE ALL RECORDS WILL BE DELETED