Activity A (20 points)

Database creation

Do not consider primary and foreign keys for this assignment

1. Create a database named company.
2. Create a table in this database. Named: departments

The following data fields will be declared in your table:

Department name, Department ID number

Insert records of this table (departments). Use the data in slide 18-19 of ch4.

3. Create a table employees

The following columns should be used in this table

Employee ID, Last name, first name, department ID number, manager ID number.

Insert the data in slide 24 of ch4.

4. Create a table named projects

The following columns should be used in this table

(Project ID, employee ID)

Use the data in slides 18-19 of ch4.

Activity B (20 points)

Translate the following into queries:

1. All employees (Employee ID, last name and first name) who work for the payroll department.
2. An employee who does not belong to any department.
3. How many employees work for personnel department (count all employees for each department).
4. All employees (first name, last name) who work for project p1012

Activity C (20 points)

Its time for you to practice join commands covered in ch4.

What is join command in SQL?

Joining is the process of taking data from multiple tables and putting it into one generated view. So, an SQL Join clause in a Select statement combines columns from one or more tables in a relational database and returns a set of data.

Write join statements for the following:

1. Perform a left outer join on the Departments and employees tables.
2. Perform a right outer join on the Departments and employees tables.
3. Perform left outer joins to join all three tables: Departments, employees, and projects.
4. Perform the inner join and outer join in the same query. Use the inner join for the first join instead of a left outer join.
5. For part c and d above, show the intermediate results from the first join, then the complete join (You may capture the screen).

Activity D (40 points)

Note: Use the database (AP) when needed (AP from textbook).

1. Use the UNION operator to generate a result set consisting of two columns from the Vendors table: vendor_name and vendor_state. If the vendor is in California, the vendor state value should be ''CA''; otherwise, the vendor_state value should be ''Outside CA." Sort the final result set by vendor name.
2. Run the following code and explain what the code does by writing comments for each union?

(note: read code description in chapter 4)

SELECT 'Active' AS source, invoice_number, invoice_date, invoice_total

FROM invoices

WHERE invoice_total - payment_total - credit_total > 0

UNION

SELECT 'Paid' AS source, invoice_number, invoice_date, invoice_total

FROM invoices

WHERE invoice_total - payment_total - credit_total <= 0

ORDER BY invoice_total DESC;

SELECT invoice_number, vendor_name, '33% Payment' AS payment_type,

invoice_total AS total, invoice_total * 0.333 AS payment

FROM invoices JOIN vendors

ON invoices.vendor_id = vendors.vendor_id

WHERE invoice_total > 10000

UNION

SELECT invoice_number, vendor_name, '50% Payment' AS payment_type,

invoice_total AS total, invoice_total * 0.5 AS payment

FROM invoices JOIN vendors

ON invoices.vendor_id = vendors.vendor_id

WHERE invoice_total BETWEEN 500 AND 10000

UNION

SELECT invoice_number, vendor_name, 'Full amount' AS payment_type,

invoice_total AS total, invoice_total AS payment

FROM invoices JOIN vendors

ON invoices.vendor_id = vendors.vendor_id

WHERE invoice_total < 500

ORDER BY payment_type, vendor_name, invoice_number;

3. Use the functions getdate() and datediff(.) to compute your age in days.
4. Write a select statement that uses an arithmetic expression which adds 2.5% to 1000 and write the column heading as add 2.5% of 1000 to 1000).