Reference no: EM131626525

Applying SQL

The Assignment is comprised of three parts. Please complete three parts in one Word document, and label each part and question so that your responses are clear.

Part 1

A manager wants to know how many employees are making a salary of 50,000 or more.

Consider the EMPLOYEES table having the following records: ATTACHED

Develop a subquery statement with a SELECT statement, and develop a table with the results similar to the one in this question above, except with the results of your statement.

Part 2

A manager needs to find out some information that is located in two tables. The SQL clause will be used to combine these records as follows:

(a) CUSTOMERS Table: ATTACHED

(b) ORDERS Table: ATTACHED

Using the following SELECT statement joining these tables, develop table with the results:

SQL> SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS, ORDERS

WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Part 3

Please respond to the following questions. If you choose to use outside sources, please make sure they are cited and referenced appropriately. There is no minimum length for your answers.

1. Define CROSS JOIN. Give an example of CROSS JOIN's syntax.

2. Identify the three join types included in the OUTER JOIN classification.

3. Define subquery and its basic characteristics.

4. Explain the differences between UNION and UNION ALL, and write the syntax for each.

5. Suppose you have two tables named EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for four employees: George Coston, Peggy Jesse, Angela Mills, and Sarah Houston. The EMPLOYEE_1 table contains the records for employees Andrew Myers and George Coston. Given this information, list the query output for the UNION query.

6. Given the employee information in Question 5, complete the following:

a. List the query output for the UNION ALL query.

b. List the query output for the INTERSECT query.

7. How is embedded SQL used, and define it.

8. Explain the differences between dynamic SQL and static SQL.