Itech 2004 06 lab - DATABASE PDF

Preview

Summary

DATABASE...

Description

ITECH 2004 DATA MODELLING

Lab 6 Topic Introduction to SQL

Questions 1. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions. 2. What is the difference between the COUNT aggregate function and the SUM aggregate function? COUNT aggregate function returns the number of items in a group whereas SUM aggregate function returns the sum of all the values 3. What is a CROSS JOIN? Give an example of its syntax? A CROSS JOIN is identical to the PRODUCT relational operator. The CROSS JOIN is also known as the Cartesian product of two tables. For example, if you have two tables, AGENT, with 10 rows and CUSTOMER, with 21 rows, the CROSS JOIN resulting set will have 210 rows and will include all of the columns from both tables. Syntax examples are: SELECT * FROM CUSTOMER CROSS JOIN AGENT; or SELECT * FROM CUSTOMER, AGENT If you do not specify a join condition when joining tables, the result will be a CROSS Join or PRODUCT operation. 4. What is a recursive join? Recursive join is a compound operation that involves repeating the join operation, typically accumulating more records each time, until a repetition makes no change to the results (as compared to the results of the previous iteration). 5. Explain the difference between an ORDER BY clause and a GROUP BY clause. The ORDER BY clause’s purpose is to sort the query result by specific columns and the GROUP BY clause’s purpose is summarize unique combinations of columns values

Exercises 6. The ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the ConstructCo database are shown in Figure E7.1. Note that the ASSIGNMENT table in Figure E7.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant.

CRICOS Provider No. 00103D

e48d352361bb10880aea3b8c51855911.docx

Page 1 of 5

Given the structure and contents of the ConstructCo database shown in Figure E7.1, use SQL commands to answer the questions below:

CRICOS Provider No. 00103D

e48d352361bb10880aea3b8c51855911.docx

Page 2 of 5

Figure E7.1 ConstructCo Database

a. Download the following file from moodle: Week 6: Lab Files / 06_ConstructCo_MySQL.txt b. Import the file 06_ConstructCo_MySQL.txt into XAMPP. c.

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_INITIAL FROM `employee` WHERE EMP_LNAME LIKE "smith%" order BY EMP_NUM

d. Using the EMPLOYEE, JOB, and PROJECT tables in the 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 E7.2, sorted by project value. SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE, EMPLOYEE.EMP_LNAME, EMP_FNAME,EMP_INITIAL, EMPLOYEE.JOB_CODE, JOB.JOB_DESCRIPTION, JOB.JOB_CHG_HOUR FROM PROJECT, EMPLOYEE, JOB WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE; Figure E7.2 Query Results Exercise 6d.

CRICOS Provider No. 00103D

e48d352361bb10880aea3b8c51855911.docx

Page 3 of 5

e. Write the SQL code that will produce the same information that was shown in Exercise 6d, but sorted by the employee’s last name. SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE, EMPLOYEE.EMP_LNAME, EMP_FNAME,EMP_INITIAL, EMPLOYEE.JOB_CODE, JOB.JOB_DESCRIPTION, JOB.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;

f.

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

g. 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 assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number. SELECT ASSIGN_NUM, EMP_NUM, PROJ_NUM, ASSIGN_CHARGE, ROUND(ASSIGN_CHG_HR * ASSIGN_HOURS,2) AS CALC_ASSIGN_CHARGE FROM ASSIGNMENT ORDER BY ASSIGN_NUM h. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure E7.3. SELECT ASSIGNMENT.EMP_NUM, EMPLOYEE.EMP_LNAME,Sum(ASSIGNMENT.ASSIGN_HOURS) AS SumOfASSIGN_HOURS, Sum(ASSIGNMENT.ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE FROM EMPLOYEE, ASSIGNMENT WHERE EMPLOYEE.EMP_NUM = ASSIGNMENT.EMP_NUM GROUP BY ASSIGNMENT.EMP_NUM, EMPLOYEE.EMP_LNAME ORDER BY EMPLOYEE.EMP_NUM ; Figure E7.3 Query Results Exercise 6h.

CRICOS Provider No. 00103D

e48d352361bb10880aea3b8c51855911.docx

Page 4 of 5

CRICOS Provider No. 00103D

e48d352361bb10880aea3b8c51855911.docx

Page 5 of 5...