Practice SQL Scenarios & Interview questions and answers
a. The names of all salespeople that have an order with Samsonic.
select Salesperson.Name from Salesperson, Orders, Customer where
Salesperson.ID = Orders.salesperson_id
and Orders.cust_id = Customer.id
and Customer.Name = ‘Samsonic’;
b. The names of all salespeople that do not have any order with Samsonic.
select Salesperson.Name, Salesperson.ID from Salesperson
where Salesperson.ID not in(
select Orders.salesperson_id from Orders, Customer where
Orders.cust_id = Customer.ID and Customer.Name = ‘Samsonic’ )
c. The names of salespeople that have 2 or more orders.
SELECT Name, COUNT(salesperson_id) as ‘Num Orders’, SUM(amount) as ‘total’
FROM Orders o, Salesperson s
WHERE o.salesperson_id = s.id
GROUP BY name, salesperson_id
HAVING COUNT( salesperson_id ) >1
d. Write a SQL statement to insert rows into a table called highAchiever(Name, Age),
where a salesperson must have a salary of 100,000 or greater to be included in the table.
insert into highAchiever (name, age)
(select name, age from salesperson where salary >= 100000);