Question

LP3.1 Assignment: Using Advanced SQL

This assignment will assess the competency 3. Construct advanced SQL queries.

Directions: Using the sample dataset, create three queries including a search, outer join and subquery. Create a table on how to use the query optimizer with these queries. Include screenshots and an explanation on why and when to use the optimizer.

.

Answer 1

Sample Data sets attached below:

Outer Join or Full join

1.It returns all the matching records from both the tables, whether the other table matches or not.

2. It will return very large data set, comparing to other joins.

Syntax for Outer Join

SELECT Column-names FROM table_1 FULL OUTER JOIN table_2 ON column_1 = column+2

Where conditions

Example:

Here we are using the outer join to display all the fields in both the tables above.

Select first_name, last_name, order_date, order_date

From customer c full join order o

on c.customer_id = o.customer_id.

Search query in SQL:

There are various type of search queries available in SQL, either by char fields, integer fieds, and so on.

1. For example

   To select the person with first_name george

select * from customer where first_name = 'george';

2. For example

    To select person with customer_id 4

Select * from customer where customer_id = 4;

Subqueries:

An sql sub query is a query within another query embedded within where clause.

It can be used with select, update, insert, delete.

Condition for sub queries;

1. Enclosed within paranthesis

2. Order by can't be used.

3. Can't be immediiately enclosed in set function.

For example: Select * from customers where ID in (select ID from cuustomers where salary > 4500).

Query optimizing or Tuning

It is process of ensuring that, sql statements that an application will issue will run in fastest possible time. Just like there are different ways to repair my TV, there are different ways to wirte queries, we have to optimize for better results.

Example:

CREATE TABLE sql_tuning_a
(
    ID NUMBER(38)
);

CREATE TABLE sql_tuning_b
(
    ID NUMBER(38)
);

After Query Optimizing:

SELECT COUNT(*)
FROM sql_tuning_a a, sql_tuning_b b
WHERE a.id = b.id;