SQL Training: Exercises on Joins

Exercise 6:

1. WAQ to display details of Customers with Orders.

2. WAQ to display details of Customers and their Orders made in 2006.

3. WAQ to display details of Sales personnel including their Customers.

4. WAQ to display details of Customers that made Orders in February.

5. WAQ to display details of Products including its Total no. of Orders.

6. WAQ to display details of Products including its Total Sales.

7. WAQ to display details of Customers that have bought keyboards.

8. WAQ to display details of Salespersons and their customers, including the total number of Orders by each Customer.

9. WAQ to display details of Orders sorted both by Products and by year. Solve and create the most
efficient view.

10. WAQ to display names (first and last) of all Customers and their relevant Sales person information.

 MY SOLUTIONS:

 -----------------------------------------------
-----------------------------------------------
--JOINS EXERCISE

SELECT c.customer_Id
      ,c.Customer_Fname
      ,c.Customer_Lname
  ,o.order_id
  FROM dbo.Customer c
INNER JOIN dbo.orders o
     ON c.customer_Id = o.customer_Id


SELECT c.customer_Id
      ,c.Customer_Fname
      ,c.Customer_Lname
  ,o.order_id
  FROM dbo.Customer c
INNER JOIN dbo.orders o
     ON c.customer_Id = o.customer_Id
AND YEAR(o.order_date) = 2006


SELECT DISTINCT s.salesperson_Id
               ,s.salesperson_Fname
               ,s.SalesPerson_Lname
           ,c.customer_Id
               ,c.Customer_Fname
               ,c.Customer_Lname
        FROM dbo.SalesPerson s
 INNER JOIN dbo.orders o
       ON s.salesperson_Id = o.salesperson_id
 INNER JOIN dbo.Customer c
       ON o.customer_Id = c.customer_Id


SELECT c.customer_Id
      ,c.Customer_Fname
      ,c.Customer_Lname
  ,o.order_id
  FROM dbo.Customer c
INNER JOIN dbo.orders o
     ON c.customer_Id = o.customer_Id
AND MONTH(o.order_date) = 2

SELECT p.product_id
      ,p.product_Name
  ,p.price
  ,COUNT(o.order_id) AS TOTORDERS
 FROM dbo.product p
 INNER JOIN dbo.orders o
        ON p.product_id = o.product_id
GROUP BY p.product_id
        ,p.product_Name
    ,p.price

SELECT p.product_id
      ,p.product_Name
  ,p.price
  ,SUM(o.Order_Qty) AS TOTSALES
 FROM dbo.product p
 INNER JOIN dbo.orders o
        ON p.product_id = o.product_id
GROUP BY p.product_id
        ,p.product_Name
    ,p.price

SELECT DISTINCT
       c.customer_Id
      ,c.Customer_Fname
      ,c.Customer_Lname
 FROM dbo.product p
 INNER JOIN dbo.orders o
        ON p.product_id = o.product_id
 INNER JOIN dbo.Customer c
        ON o.customer_Id = c.customer_Id
 WHERE p.product_Name = 'keyboard'



 SELECT DISTINCT
                c.customer_Id
               ,c.Customer_Fname
               ,c.Customer_Lname
               ,s.salesperson_Fname
               ,s.SalesPerson_Lname
   ,COUNT(o.order_id) AS TOTALORDERSPERCUSTOMER
        FROM dbo.SalesPerson s
 INNER JOIN dbo.orders o
       ON s.salesperson_Id = o.salesperson_id
 INNER JOIN dbo.Customer c
       ON o.customer_Id = c.customer_Id
 GROUP BY c.customer_Id
         ,c.Customer_Fname
         ,c.Customer_Lname
         ,s.salesperson_Fname
         ,s.SalesPerson_Lname

SELECT
       o.order_id
      ,o.Order_Qty
  ,o.order_date
  ,o.product_id
  ,o.customer_Id
  ,o.salesperson_Id
 FROM dbo.orders o
 INNER JOIN dbo.product p
        ON o.product_id = p.product_id
ORDER BY o.product_id,YEAR(o.order_date)

SELECT DISTINCT
                c.customer_Id
               ,c.Customer_Fname
               ,c.Customer_Lname
   ,s.salesperson_Fname
               ,s.SalesPerson_Lname
        FROM dbo.Customer c
 INNER JOIN dbo.orders o
       ON c.customer_Id = o.customer_Id
 INNER JOIN dbo.SalesPerson s
       ON o.salesperson_Id = s.salesperson_id


Comments

Post a Comment

Popular posts from this blog

SQL Training: Exercises on Stored Procedures and Functions

Exercises on Stored Procedures and Functions: 1. Create a function that should take two inputs those are product id and location id and should return stock level. Note: Consider product Inventory table in adventurworks database for this requirement. 2. Create a function that should take input as store id and returns the columns ProductID, Name, and the aggregate of line total for each product sold to the store. Note: consider product, sales order details, sales order header and customer tables for this requirements. 3. Someone deleted few records from a table which has an IDENTITY column, so need to create stored procedures to find missing sequential numbers. Note: for this requirement create sample table with identity column and insert some data and delete few records from table. 4. Sales order header table in adventureworks database as follows Requirement: Need to create stored procedure that should take sortdate as argument (varchar datatype) then it should displ...
Read more

SQL Training: Exercise on Clauses,String and Mathematical functions,Date Time and Aggregate Functions

Exercise 3: 1. WAQ to display Tool Designer designation employee’s details. 2. WAQ to display Design Engineer and Tool designation employee’s details. 3. WAQ to display female market specialist employee’s details. 4. WAQ to display single female employee’s details. 5. WAQ to display employees whose vocation hours between 40 and 50. 6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave. 7. WAQ to display employees whose job title contain engineer word. 8. WAQ to display employees whose vacation hours contain more than or equal to 40. 9. WAQ to display all employees except tool designer designation employees. 10.WAQ to display employee details whose vacation hours not in range 20 and 40. 11.WAQ to display employee details except tool designer and designer engineer. 12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31. 13. WAQ to...
Read more

SQL Training: Exercises on Functions

Exercises on Functions: 1. Write a Function which returns Ename from EMP table by taking empno as argument. 2. Write a scalar value return function which returns sum of two given numbers. 3. Write a Function to display biggest of 3 given numbers(need to take 3 argument for 3 values) 4. Write a Function to display maximum salary of a given deptno from EMP table. 5. Write a Function to display the SUM, AVG sal of employees according to their deptno’s. 6. Write a Function to display details of departments and their corresponding Employee details from DEPT and EMP tables according to deptno. 7. Write a Function to display all the employee details whose salary between first argument value and second argument value. 8. Write a function to display the Names of Employees whose name contain particular character (character we need to take from argument). 9.Write a function to display specific number of senior most employees in arganization. 10.write a table valued return func...
Read more