CONSTRUCTCO O
1. Write the SQL code required to list the employee number, last name, first name, and middle initial of
all employees whose last names start with Smith. In other words, the rows for both Smith and
Smithfield should be included in the listing. Sort the results by employee number. Assume case
sensitivity.
SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL
FROM EMPLOYEE
WHERE EMP_LNAME LIKE '%Smith%'
ORDER BY EMP_NUM;
2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database, write the
SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common
attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.
SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,
EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE,
JOB_DESCRIPTION, JOB_CHG_HOUR
FROM PROJECT, EMPLOYEE, JOB
WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM
AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE
ORDER BY PROJ_VALUE;
SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,
E.EMP_LNAME, EMP_FNAME, EMP_INITIAL, E.JOB_CODE,
J.JOB_DESCRIPTION, J.JOB_CHG_HOUR
FROM PROJECT AS P
INNER JOIN EMPLOYEE AS E ON E.EMP_NUM = P.EMP_NUM
INNER JOIN JOB AS J ON J.JOB_CODE = E.JOB_CODE
ORDER BY PROJ_VALUE;
3. Write the SQL code that will produce the same information that was shown in Problem 2, but sorted
by the employee’s last name.
SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,
EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE,
JOB_DESCRIPTION, JOB_CHG_HOUR
FROM PROJECT, EMPLOYEE, JOB
WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM
AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE
ORDER BY EMP_LNAME;
SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,
E.EMP_LNAME, EMP_FNAME, EMP_INITIAL, E.JOB_CODE,
J.JOB_DESCRIPTION, J.JOB_CHG_HOUR
FROM PROJECT AS P
INNER JOIN EMPLOYEE AS E ON E.EMP_NUM = P.EMP_NUM
INNER JOIN JOB AS J ON J.JOB_CODE = E.JOB_CODE
ORDER BY E.EMP_LNAME;
4. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted
by project number.
SELECT DISTINCT PROJ_NUM
from ASSIGNMENT
ORDER BY PROJ_NUM;
5. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your
query should retrieve the assignment number, employee number, project number, the stored
剩余7页未读，继续阅读
资源评论
_djinn
- 粉丝: 7
- 资源: 4
最新资源
- (源码)基于Arduino和Firebase的智能家庭管理系统NodeSmartHome.zip
- (源码)基于C++的East Zone DSTADSO Robotics Challenge 2019机器人控制系统.zip
- (源码)基于Arduino平台的焊接站控制系统.zip
- (源码)基于ESPboy系统的TZXDuino WiFi项目.zip
- (源码)基于Java的剧场账单管理系统.zip
- (源码)基于Java Swing的船只资料管理系统.zip
- (源码)基于Python框架的模拟购物系统.zip
- (源码)基于C++的图书管理系统.zip
- (源码)基于Arduino的简易温度显示系统.zip
- (源码)基于Arduino的智能电动轮椅系统.zip
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
