From the following tables, create a view that shows all matches of customers with salesperson such that at least one customer in the city of customer served by a salesperson in the city of the salesperson

  • برمجة سي كيو ال sql

 From the following tables, create a view that shows all matches of customers with salesperson such that at least one customer in the city of customer served by a salesperson in the city of the salesperson.

Sample table: customer

customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005
 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

الأجوبة

CREATE VIEW citymatch(custcity, salescity)
AS SELECT DISTINCT a.city, b.city
FROM customer a, salesman b
WHERE a.salesman_id = b.salesman_id;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM citymatch;
  custcity  | salescity
------------+-----------
 Seattle    | Paris
 Moscow     | Rome
 New York   | New York
 NC         |
 Paris      | Paris
 California | Paris
 Berlin     |
 London     | Paris
 London     | London
 Dallas     | New York
(10 rows)
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson
From the following tables, create a view to get the salesperson and customer by name
From the following tables, create a view to find the salesperson who handles a customer who makes the highest order of a day
From the following tables write a SQL query to list all salespersons along with customer name, city, grade, order number, date, and amount

القوائم الدراسية التي ينتمي لها السؤال

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

ابحث عن مدرسك
محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...