Question

Consider the following three tables, primary and foreign keys.

Table Name        SalesPeople

Attribute Name                                Type                                      Key Type

EmployeeNumber             Number                               Primary Key

Name                                   Character

JobTitle                                  Character           

Address                                 Character

PhoneNumber                     Character

YearsInPosition                             Number

Table Name        ProductDescription

Attribute Name                                Type                                      Key Type

                ProductNumber                Number                               Primary Key

                ProductName                  Character           

                ProductPrice                   Number

Table Name        SalesOrder

Attribute Name                                Type                                      Key Type

                SalesOrderNumber        Number                               Primary Key

                ProductNumber               Number                               Foreign Key

                EmployeeNumber           Number                               Foreign Key

                SalesOrderDate                Date

Assume that you draw up a new sales order for each product sold.

Develop the following queries in SQL:

a.       All the Sales People with less than four years in position.  

b.      All the Product Names sold on April 4^th.  

c.       All the Products sold by Sales People less than 3 years in the position.  

Answer 1

SOLUTION a:

SELECT * FROM SalesPeople WHERE yearsInPosition<4

EXPLANATION:

STEP 1: We need to check for the yearsInPosition is greater than 4 years or not and then we need to project them

OUTPUT:

SOLUTION b:

SELECT ProductName FROM SalesOrder so,ProductDescription pd
WHERE so.ProductNumber==pd.ProductNumber AND SalesOrderDate LIKE ("%04-04")

EXPLANATION:

STEP 1: We need to join the two tables salesorder and productdescription by using the productnumber

STEP 2: Then we need to check for the order date as like april 4 th

STEP 3: Project the ProductName

OUTPUT:

SOLUTION c:

SELECT ProductName,sp.name FROM SalesOrder so,SalesPeople sp,ProductDescription pd
WHERE so.EmployeeNumber ==sp.EmployeeNumber AND yearsInPosition<3 AND pd.ProductNumber==so.ProductNumber

EXPLANATION:

STEP 1: We need to join SalesOrder,SalesPeople so that we will get all the details of sales and sales person

STEP 2: Filter the yearsInposition condition less than 3

STEP 3: Join the previous result with the ProductDescription table to get the products table using productnumber

OUTPUT AND CODE: