SQL-Query on Multiple Tables

本文提供了多个SQL查询示例,包括如何找出与销售人员居住在同一城市的客户,如何查找所有客户及其对应的销售人员,以及如何显示那些不在销售人员所在城市下订单的客户等。通过这些示例,读者可以学习到如何使用JOIN、WHERE子句和其他SQL功能来获取特定的数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  1. Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city.
    SELECT customer.cust_name, salesman.name, customer.city
    FROM customer
    JOIN salesman ON customer.salesman_id=salesman.salesman_id;
    ------
    SELECT customer.cust_name,salesman.name, salesman.city
    FROM salesman, customer
    WHERE salesman.city = customer.city;
    
  2. Write a SQL statement to find the names of all customers along with the salesmen who works for them.
    SELECT customer.cust_name, salesman.name
    FROM customer
    LEFT JOIN salesman ON salesman.salesman_id=customer.salesman_id;
    ------
    SELECT customer.cust_name, salesman.name
    FROM customer,salesman
    WHERE salesman.salesman_id = customer.salesman_id;
    
  3. Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live.
    SELECT orders.ord_no, customer.cust_name, orders.customer_id, orders.salesman_id
    FROM orders
    JOIN salesman ON salesman.salesman_id=orders.salesman_id
    JOIN customer ON customer.customer_id=orders.customer_id
    WHERE salesman.city<>customer.city;
    ------
    SELECT ord_no, cust_name, orders.customer_id, orders.salesman_id
    FROM salesman, customer, orders
    WHERE customer.city <> salesman.city
    AND orders.customer_id = customer.customer_id
    AND orders.salesman_id = salesman.salesman_id;
    
  4. Write a SQL statement that finds out each order number followed by the name of the customers who made the order.
    SELECT orders.ord_no, customer.cust_name
    FROM orders
    JOIN customer ON orders.salesman_id=customer.salesman_id;
    ------
    SELECT orders.ord_no, customer.cust_name
    FROM orders, customer
    WHERE orders.customer_id = customer.customer_id; 
    
  5. Write a SQL statement that sorts out the customer and their grade who made an order. Each of the customers must have a grade and served by at least a salesman, who belongs to a city.
    SELECT customer.cust_name, customer.grade 
    FROM orders
    JOIN salesman ON salesman.salesman_id=orders.salesman_id
    JOIN customer ON customer.customer_id=orders.customer_id
    WHERE customer.grade IS NOT NULL
    AND salesman.city IS NOT NULL;
    ------
    SELECT customer.cust_name AS "Customer",
    customer.grade AS "Grade"
    FROM orders, salesman, customer
    WHERE orders.customer_id = customer.customer_id
    AND orders.salesman_id = salesman.salesman_id
    AND salesman.city IS NOT NULL
    AND customer.grade IS NOT NULL;
    
  6. Write a query that produces all customers with their name, city, salesman and commission, who served by a salesman and the salesman works at a rate of the commission within 12% to 14%.
    SELECT customer.cust_name, customer.city, salesman.name, salesman.commission
    FROM customer
    JOIN salesman ON customer.salesman_id=salesman.salesman_id
    WHERE salesman.commission BETWEEN 0.12 AND 0.14;
    ------
    SELECT customer.cust_name AS "Customer",
    customer.city AS "City",
    salesman.name AS "Salesman",
    salesman.commission
    FROM customer,salesman
    WHERE customer.salesman_id = salesman.salesman_id
    AND salesman.commission
    BETWEEN .12 AND .14;
    
  7. Write a SQL statement that produces all orders with the order number, customer name, commission rate and earned commission amount for those customers who carry their grade is 200 or more and served by an existing salesman.
    SELECT orders.ord_no, customer.cust_name, salesman.commission, orders.purch_amt*salesman.commission
    FROM orders
    JOIN salesman ON salesman.salesman_id=orders.salesman_id
    JOIN customer ON customer.customer_id=orders.customer_id
    WHERE customer.grade>=200;
    ------
    SELECT ord_no, cust_name, commission AS "Commission%",
    purch_amt*commission AS "Commission"
    FROM salesman,orders,customer
    WHERE orders.customer_id = customer.customer_id
    AND orders.salesman_id = salesman.salesman_id
    AND customer.grade>=200;
    

来源:w3resource

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值