Answered: SQL Sales people must be trained to…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

SQL

Sales people must be trained to make orders. For a sales person to receive credit for an order, the order_date must fall between a training start and end date for that sales person.

1. Write a query that finds all orders where the salesperson did not have an active training on the order date.

The following query seems to almost work, but pulls in order id 4 which is not desired.

select *

from orders t1

join training t2

on t1.salesperson_id = t2.salesperson_id

where (order_date not between start_date and end_date

or order_date between start_date and end_date is null)

group by id;

Orders
ID
order_date salesperson_id Amount
1
8/2/12
540
2
1/30/12
14
1800
7/14/12
1
460
1/29/12
2/3/13
3/2/13
4
2
1100
5
6
600
6
1250
7
5/6/12
150
4/1/13
3/5/13
8
1
1000
9
2
950
Training
ID
salesperson_id Start date End date
1/1/12
1/1/13
1
1
12/31/12
1
NULL
3
2
1/1/12
7/31/12
1/1/13
1/1/12
14
2
5/1/13
5
3
NULL
6
14
1/1/12
12/31/12
7
5
1/1/13
NULL
8
1/1/12
NULL

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions