” Notes:
All assignments are individual work. No group collaboration is allowed.
The cover page of each assignment should include your last name, first name, and
your student number.
Please submit your assignment using Moodle.
Please upload sql file ONLY.
Create the four tables and insert the data as provided below. Please practice both insert
command and SQLLoader.
When you finish testing your queries, copy and paste your queries to Notepad or
WordPad and save it as .sql file.
Populate Department using the following data:
DID DName PhoneExt
ADM Administration 100
CLT Client Services 101
DAT Data Conversion 102
PRG Programming 103
SLS Sales 104
Populate Employee using the following data:
EID EName Salary MID DID
e001 Martin 57000 CLT
e002 West 39000 e001 CLT
e003 Wilson 64000 DAT
e004 Patel 48000 e003 DAT
e005 Rae 42000 e001 CLT
e006 Jones 36000 e003 DAT
e007 Dunn 52000 e003 PRG
e008 Chen 49000 e001 CLT
2
e009 Smith 32000 e001 ADM
e010 Gomez 45000 e003 DAT
e011 Wilson 50000 e003 SLS
e012 Monet 54000 e003 DAT
Populate Project using the following data:
PNum Pname Fee DueDate
p10010 Brooks Data Conversion 9500 15-Mar-07
p10011 Brooks Implementation 11500 22-Mar-07
p10012 RMK Data Cleanup 5400 01-Feb-07
p10013 Sterling Implementation 12000 31-Mar-07
p10014 Matteson Reports 5400 15-Jan-07
p10015 Speiker Data Conversion 12000 03-Mar-07
p10016 Wesley Reports 2500 01-Mar-07
Populate Assignment using the following data:
EID PNum Hours
Design SQL queries based on the provided data.
1. [2] List the names (Pname) and numbers (PNum) of all projects which the Data
Conversion department is working on.
2. [2] List the names and ids of those employees whose manager is from the Client
Services department.
3
3. [3] List the names and ids of the employees who worked on more than one project.
4. [3] Increase the salaries by $500 for those employees who worked more than 15 hours
on assigned projects. Once completed, display the content of the Employee table.
You are expected to submit the queries (.sql file) ONLY.
“
添加老师微信回复‘’官网 辅导‘’获取专业老师帮助，或点击联系老师1对1在线指导。
