联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2019-10-30 10:20

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.


版权所有:留学生编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。