Scribd
Upload
9 views5 pages

SQL Queries for Sales and Customers

The document contains 8 exercises providing SQL queries and views on various tables to return specific data. The exercises include queries to find salespeople and customers in the same city, orders between $500-$2000, salespeople and customers, orders by a specific salesperson and location, and views for salespeople by city, customer grades, and orders by date. The final exercise queries for managers and analysts in specific locations with over 5 years experience without commission, sorted by department location.

Uploaded by

cse2023mantosh48
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views5 pages

SQL Queries for Sales and Customers

The document contains 8 exercises providing SQL queries and views on various tables to return specific data. The exercises include queries to find salespeople and customers in the same city, orders between $500-$2000, salespeople and customers, orders by a specific salesperson and location, and views for salespeople by city, customer grades, and orders by date. The final exercise queries for managers and analysts in specific locations with over 5 years experience without commission, sorted by department location.

Uploaded by

cse2023mantosh48
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Dbms_Test5-Level4:

Exercise-1: From the following tables write a SQL query to find the salesperson and customer
who reside in the same city. Return Salesman, cust_name and city.(Hint: Joins)

Query:

Exercise-2: From the following tables write a SQL query to find those orders where the order
amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.(Hint: Joins)
Orders Customer

Query:
Exercise-3: Consider following 2 tables and write the queries for 3.1 to 3.4. (Hint: Joins)

3.1 Write a SQL query to find the salesperson(s) and the customer(s) he represents. Return
Customer Name, city, Salesman, commission.
Query:

3.2 Write a SQL query to find the salesperson(s) and the customer(s) he represents. Return
Customer Name, city, Salesman, commission.
Query:

3.3 Write a SQL query to locate those salespeople who do not live in the same city where their
customers live and have received a commission of more than 12% from the company. Return
Customer Name, customer city, Salesman, salesman city, commission.
Query:

3.4 Write a SQL query to find those customers with a grade less than 300. Return cust_name,
customer city, grade, Salesman, salesmancity. The result should be ordered by ascending
customer_id.
Query:
Exercise-4: Consider following 2 tables and write the queries for 4.1 and 4.2 (Hint: SubQueries)
Orders Salesman

4.1 Write a SQL query to find all the orders issued by the salesman 'Paul Adam'. Return ord_no,
purch_amt, ord_date, customer_id and salesman_id.
Query:

4.2 Write a SQL query to find all orders generated by London-based salespeople. Return ord_no,
purch_amt, ord_date, customer_id, salesman_id.
Query:

Exercise-5: From the following table, create a view for those salespeople who belong to the city of
New York. (View Name:newyorkstaff)

Query:
Exercise-6: From the following table, create a view that counts the number of customers in each
grade.(View Name:gradecount)

Query:

Exercise-7: From the following table, create a view to display the number of orders per day. Return
order date and number of orders.(View Name:dateord)

Query:
Exercise-8: From the following table, write a SQL query to find those employees who work as
'MANAGERS' and 'ANALYST' and working in ‘SYDNEY’ or ‘PERTH’ with an experience more than 5
years without receiving the commission. Sort the result-set in ascending order by department
location. Return employee ID, employee name, salary, and department name.

Query:

You might also like