Task - Joins - classicmodels DB

[akash-coded]

Database = classicmodels

Dump file: classicmodels.sql

DB Schema

Queries

1. Write a query to display a list of customers who locate in the same city by joining the customers table to itself.
   

2. Write a query to get:

   • The productCode and productName from the products table.
   • The textDescription of product lines from the productlines table.
     

3. Write a query that returns order number, order status, and total sales from the orders and orderdetails tables as follows:
   

4. Write a query to fetch the complete details of orders from the orders, orderDetails, and products table, and sort them by orderNumber and orderLineNumber as follows:
   

5. Write a query to perform INNER JOIN of four tables:
   
   Display the details sorted by orderNumber, orderLineNumber as per the following
   

6. Write a query to find the sales price of the product whose code is S10_1678 that is less than the manufacturer’s suggested retail price (MSRP) for that product as follows:
   

7. Each customer can have zero or more orders while each order must belong to one customer. Write a query to find all the customers and their orders as follows:
   

8. Write a query that uses the LEFT JOIN to find customers who have no order:
   

[deepak775035]

-- Task 14 march 2022 Morning session

-- Qusetion 1: 
SELECT c.city , c.customerName, m.customerName FROM customers c INNER JOIN customers m
ON c.city = m.city WHERE c.customerName<>m.customerName ORDER BY c.city;


-- Qusetion 2: 
SELECT p.productCode , p.ProductName , l.textDescription FROM products p INNER JOIN productlines l
ON p.productline = l.productline;

-- Qusetion 3: 
SELECT o.orderNumber , o.status , SUM(priceEach*quantityOrdered) FROM orders o INNER JOIN orderdetails od
ON o.orderNumber=od.orderNumber GROUP BY od.orderNumber;

-- Qusetion 4: 
SELECT o.orderNumber,o.orderDate, od.orderLineNumber, p.productName, od.quantityOrdered,od.priceEach FROM orders o 
INNER JOIN orderdetails od ON o.orderNumber=od.orderNumber
INNER JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;


-- Qusetion 5: 
SELECT o.orderNumber,o.orderdate,c.customerName,od.orderLineNumber,p.productName, od.quantityOrdered,od.priceEach FROM orders o
INNER JOIN customers c ON o.customerNumber=c.customerNumber
INNER JOIN orderdetails od ON o.orderNumber=od.orderNumber
INNER JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;



-- Qusetion 6: 
SELECT od.orderNumber, p.productName, msrp, priceEach FROM orderdetails od
INNER JOIN products p ON od.productCode=p.productCode
WHERE p.productCode = 'S10_1678' AND priceEach<msrp;


-- Qusetion 7:
SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber;


-- Qusetion 8:
SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber AND o.ordernumber IS NULL;

[aish955]

1. select a.city as city,a.customerName as customerName,b.customerName as customerName from customers a,customers b where a.customerNumber <> b.customerNumber AND a.city=b.city order by a.city;

2.select productCode,productName,textDescription from products a inner join productlines b on a.productline=b.productline;

3.select a.orderNumber,status,SUM(quantityOrdered * priceEach) as total from orders a inner join orderdetails b on a.orderNumber=b.orderNumber group by a.orderNumber;

4.select orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) order by orderNumber,orderLineNumber;

5.select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) inner join customers using(customerNumber) order by orderNumber,orderLineNumber;

6.select orderNumber,productName,msrp,priceEach from products a inner join orderdetails b on a.productcode=b.productcode and a.msrp>b.priceEach where a.productCode='S10_1678';

7.select a.customerNumber,a.customerName,b.orderNumber,b.status from customers a inner join orders b on a.customerNumber=b.customerNumber order by a.customerNumber;

8.select a.customerNumber,a.customerName,b.orderNumber,b.status from customers a left join orders b on a.customernumber=b.customernumber and b.ordernumber is null;

[Sarfinaa]

1.) select c1.city,c1.customerName,c2.customerName from customers c1, customers c2  where c1.city=c2.city and c1.customerNumber <> c2.customerNumber  order by c1.city;
2.) select p.productCode,p.productName,l.textdescription from products p join productlines l on p.productline=l.productline;
3.) select o.ordernumber,o.status,sum(d.quantityordered*d.priceeach) as total from orders o join orderdetails d on o.ordernumber=d.ordernumber group by o.ordernumber;
4.)select o.ordernumber,o.orderdate,d.orderlinenumber,p.productname,d.quantityordered,d.priceeach from orders o join orderdetails d on o.ordernumber=d.ordernumber join products p on d.productcode=p.productcode order by ordernumber,orderlinenumber;
5.) select o.ordernumber,o.orderdate,c.customername,d.orderlinenumber,p.productname,d.quantityordered,d.priceeach from orders o join orderdetails d on o.ordernumber=d.ordernumber join products p on d.productcode=p.productcode join customers c on o.customernumber=c.customernumber order by ordernumber,orderlinenumber;
6.) select d.ordernumber,p.productname,p.msrp,d.priceeach from orderdetails d join products p on d.productcode=p.productcode where d.productcode="S10_1678" and p.msrp>d.priceeach;
7.)  select c.customernumber,c.customername,o.ordernumber,o.status from customers c left join orders o on c.customernumber=o.customernumber order by c.customernumber ;
8.) select c.customernumber,c.customername,o.ordernumber,o.status from customers c left join orders o on c.customernumber=o.customernumber where o.ordernumber is null;

[Harshita-Joshi]

1.

    -> SELECT c1.city, c1.customerName, c2.customerName
    -> FROM customers c1 INNER JOIN customers c2
    -> WHERE c1.city = c2.city AND c1.customerName > c2.customerName
    -> ORDER BY c1.city
    -> LIMIT 12;

2.

    -> SELECT productCode, productName, textDescription
    -> FROM products, productlines
    -> WHERE products.productLine = productlines.productLine
    -> LIMIT 5;

3. 

    ->	SELECT o1.orderNumber, o1.status, SUM((o2.quantityOrdered*o2.priceEach)) AS total
    ->  FROM orders o1, orderdetails o2
    ->  WHERE o1.orderNumber = o2.orderNumber
    ->  GROUP BY o1.orderNumber
    ->  LIMIT 5;

4.
	
    -> SELECT o1.orderNumber, o1.orderDate, o2.orderLineNumber, p.productName,
    -> o2.quantityOrdered, o2.priceEach
    -> FROM orders o1, orderdetails o2, products p
    -> WHERE o1.orderNumber = o2.orderNumber AND
    -> o2.productCode = p.productCode
    -> ORDER BY o1.orderNumber, o2.orderLineNumber
    -> LIMIT 13;

5.

    -> SELECT o2.orderNumber, o1.orderDate, c.customerName, o2.orderLineNumber,
    -> p.productName, o2.quantityOrdered, o2.priceEach
    -> FROM orderdetails o2
    -> INNER JOIN orders o1 ON o1.orderNumber = o2.orderNumber
    -> INNER JOIN customers c ON o1.customerNumber = c.customerNumber
    -> INNER JOIN products p ON o2.productCode = p.productCode
    -> ORDER BY o2.orderNumber
    -> LIMIT 19;

6.

    -> SELECT o1.orderNumber, p.productName, p.MSRP, o1.priceEach
    -> FROM orderdetails o1, products p
    -> WHERE o1.productCode = 'S10_1678' AND o1.productCode = p.productCode AND p.MSRP > o1.priceEach
    -> ORDER BY o1.orderNumber
    -> LIMIT 8;

7.

    -> SELECT c.customerNumber, c.customerName, o.orderNumber, o.status
    -> FROM customers c LEFT JOIN orders o
    -> ON c.customerNumber = o.customerNumber
    -> ORDER BY c.customerNumber;

8.

    -> SELECT c.customerNumber, c.customerName, o.orderNumber, o.status
    -> FROM customers c LEFT JOIN orders o
    -> ON c.customerNumber = o.customerNumber
    -> where o.orderNumber IS NULL
    -> ORDER BY c.customerNumber;

[shreiyarandive]

1)select c1.city, c1.customerName, c2.customerName from customers c1 join customers c2 on c1.city = c2.city and c1.customerName <> c2.customerName order by c1.city;


2) select p.productCode, p.productName, pl.textDescription from products p join productlines pl on p.productLine = pl.productLine;


3) select o.orderNumber, o.status,sum(od.quantityOrdered * od.priceEach) from orders o join orderdetails od on o.orderNumber = od.orderNumber group by(od.orderNumber);


4) select o.orderNumber, o.orderDate, od.orderLineNumber, p.productName, od.quantityOrdered, od.priceEach from orders o, orderdetails od, products p where o.orderNumber
= od.orderNumber and od.productCode = p.productCode order by o.orderNumber, od.orderLineNumber;


5) select o.orderNumber, o.orderDate, c.customerName, od.orderLineNumber, p.productName, od.quantityOrdered, od.priceEach from orders o, orderdetails od, products p, customers c where o.orderNumber = od.orderNumber and od.productCode = p.productCode and o.customerNumber = c.customerNumber order by o.orderNumber,od.orderLineNumber;


6) select o.orderNumber, p.productName, p.msrp, o.priceEach from orderdetails o join products p on o.productCode = p.productCode where o.productCode = "S10_1678" and o.priceEach < p.msrp;



7) select c.customerNumber, c.customerName, o.orderNumber, o.status from customers c left join orders o on c.customerNumber = o.customerNumber;


8)select c.customerNumber, c.customerName, o.orderNumber, o.status from customers c left join orders o on c.customerNumber = o.customerNumber where o.orderNumber is null and o.status is null;

[SrideviYamala]

1. select c1.city,c1.customerName,c2.customerName from customers c1 inner join customers c2 using(city) where c1.customerName>c2.customerName order by c1.city;

2. select p.productCode,p.productName,pl.textDescription from products p natural join productLines pl;

3. select orderNumber,status,sum(quantityOrdered*priceEach) as total from orders inner join orderdetails using (orderNumber) group by orderNumber ;

4. select orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;

5. select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from customers inner join orders using(customerNumber) inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;

6. select orderNumber,productName,msrp,priceEach from products inner join orderdetails using(productCode) where productCode="S10_1678" and msrp>priceEach;

7. select customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) ;

8. select customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) where orderNumber is NULL;

[RohitNaikade1]

1.Write a query to display a list of customers who locate in the same city by joining the customers table to itself.

select a.city,a.customerName,b.customerName from customers a,customers b where a.city=b.city;

2.write queries to get:
(i).The productCode and productName from the products table.

select productCode,productName from products;

(ii).The textDescription of product lines from the productlines table.

select textDescription from productlines;

3.Write a query that returns order number, order status, and total sales from the orders and orderdetails tables as follows:

select o.orderNumber,o.status,sum(d.priceEach) from orders o inner join orderDetails d on o.orderNumber=d.orderNumber group by o.orderNumber;

4.Write a query to fetch the complete details of orders from the orders, orderDetails, and products table, and sort them by orderNumber and orderLineNumber as follows:

SELECT o.orderNumber,o.orderDate, od.orderLineNumber, p.productName, od.quantityOrdered,od.priceEach FROM orders o 
INNER JOIN orderdetails od ON o.orderNumber=od.orderNumber
INNER JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

5.Write a query to perform INNER JOIN of four tables:

select a.orderNumber,b.orderDate,c.customerName,
a.orderLineNumber,d.productName from orderDetails a inner join orders b
on a.orderNumber=b.orderNumber inner join customers c
on c.customerNumber=b.customerNumber inner join products d
on a.productCode=d.productCode order by a.orderNumber,a.orderLineNumber;

7.Each customer can have zero or more orders while each order must belong to one customer. Write a query to find all the customers and their orders as follows:

SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber;

8.Write a query that uses the LEFT JOIN to find customers who have no order:

SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber AND o.ordernumber IS NULL;

[KaminiJha]

**ques1**
Select c1.city,c2.customerName,c1.customerName from customers c1, customers c2 where  c1.city = c2.city AND c1.customerName < c2.customerName order by c1.city;
**ques2**
Select products.productCode, products.productName, productlines.textDescription from products INNER JOIN productlines ON products.productLine = productlines.productline;
**ques3**
Select o.orderNumber,o.status,sum(od.quantityOrdered) as total from orders o, orderdetails od  where o.orderNumber=od.orderNumber GROUP BY o.orderNumber;
**ques4**
Select o.orderNumber,o.orderDate,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach from orders o ,orderDetails od ,products p WHERE o.orderNumber = od.orderNumber AND od.productCode = p.productCode ORDER BY o.orderNumber ,od.orderLineNumber;
**ques5**
Select o.orderNumber,o.orderDate,c.customerName,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach from orders o ,orderDetails od ,products p ,customers c WHERE c.customerNumber=o.customerNumber AND  o.orderNumber = od.orderNumber AND od.productCode = p.productCode ORDER BY o.orderNumber ,od.orderLineNumber;
**ques6***
SELECT od.orderNumber, p.productName, msrp, priceEach FROM orderdetails od INNER JOIN products p ON od.productCode=p.productCode WHERE p.productCode = 'S10_1678' AND priceEach<msrp;
**ques7***
Select c.customerNumber,c.customerName , o.orderNumber ,o.status from customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
**ques8***
Select c.customerNumber,c.customerName,o.orderNumber,o.status from customers c LEFT JOIN orders o  ON c.customerNumber = o.customerNumber AND o.orderNumber IS NULL;

[ujjwal18bit1167]

1. select a.city as city,a.customerName as customerName,b.customerName as customerName from customers a,customers b where a.customerNumber <> b.customerNumber AND a.city=b.city order by a.city;

2. select p.productCode, p.productName, SUBSTR(q.textDescription,1,75) from products p inner join productlines q on p.productLine = q.productLine LIMIT 5;

3. select p.orderNumber, p.status,SUM( q.quantityOrdered*q.priceEach) from orders p inner join orderdetails q on p.orderNumber = q.orderNumber group by p.orderNumber LIMIT 5;

4. select ord.orderNumber,ord.orderDate, ordet.orderLineNumber,p.productName,ordet.quantityOrdered,ordet.priceEach from orders ord inner join orderdetails ordet on ord.orderNumber = ordet.orderNumber inner join products p on ordet.productCode = p.productCode order by ordet.orderNumber,ordet.orderLineNumber LIMIT 13;

5. select ordet.orderNumber, ord.orderDate, c.customerName,ordet.orderLineNumber, p.productName,ordet.quantityOrdered,ordet.priceEach from orderdetails ordet inner join orders ord on ordet.orderNumber =ord.orderNumber inner join customers c on ord.customerNumber = c.customerNumber inner join products p on ordet.productCode = p.productCode order by ord.orderNumber,ordet.orderLineNumber Limit 19;

6.select ordet.orderNumber,p.productName,p.msrp, ordet.priceEach from products p inner join orderdetails ordet on p.productCode = ordet.productCode and ordet.priceEach < p.msrp where ordet.productCode="S10_1678" limit 8;

7. SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
   LEFT JOIN orders o ON c.customerNumber=o.customerNumber;

8.SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber=o.customerNumber where orderNumber is NULL;

[lalit-ky]

use classicmodels;

-- 1). 
SELECT a.city,a.customerName,b.customerName 
	FROM customers a INNER JOIN customers b ON a.city = b.city 
	Where a.customerName > b.customerName
    ORDER BY a.city,b.customerName;

-- 2).
SELECT p.productCode,p.productName,pl.textDescription 
	FROM products p LEFT JOIN productlines pl USING (productLine) 
    ORDER BY pl.textDescription;

-- 3).
SELECT o.orderNumber,o.status,SUM(od.quantityOrdered*od.priceEach) AS total 
	FROM orders o INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY (od.orderNumber);

-- 4).
SELECT o.orderNumber,o.orderDate,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach 
	FROM (orderdetails od inner join orders o on od.orderNumber = o.orderNumber) 
    inner join products p on od.productCode = p.productCode
    ORDER BY o.orderNumber, od.orderLineNumber;

-- 5).    
SELECT o.orderNumber,o.orderDate,c.customerName,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach 
	FROM orderdetails od 
    inner join orders o on od.orderNumber = o.orderNumber
    inner join products p on od.productCode = p.productCode
    INNER JOIN customers c on o.customerNumber = c.customerNumber
    ORDER BY o.orderNumber, od.orderLineNumber;
    
-- 6).
SELECT od.orderNumber,p.productName,p.msrp,od.priceEach
		FROM orderdetails od INNER JOIN products p on od.productCode = p.productCode
        WHERE p.msrp > od.priceEach;

-- 7).
SELECT c.customerNumber,c.customerName,o.orderNumber,o.status
	FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
    ORDER BY c.customerNumber;

-- 8).    
SELECT c.customerNumber,c.customerName,o.orderNumber,o.status
	FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
    WHERE o.orderNumber is null
    ORDER BY c.customerNumber;

[Shubhi27]

1.SELECT c.city , c.customerName, a.customerName FROM customers c INNER JOIN customers a
ON c.city = a.city WHERE c.customerName<>a.customerName ORDER BY c.city;

2.SELECT productCode,productName,textDescription from products p inner join productlines pl on p.productline=pl.productline;

3.SELECT o.orderNumber,o.status,SUM(quantityOrdered * priceEach) as total from orders o inner join orderdetails od on o.orderNumber=od.orderNumber group by o.orderNumber;

4.SELECT orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) order by orderNumber,orderLineNumber;

5.SELECT orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) inner join customers using(customerNumber) order by orderNumber,orderLineNumber;

6.SELECT orderNumber,productName,msrp,priceEach from products p inner join orderdetails od on p.productcode=od.productcode and p.msrp>od.priceEach where p.productCode='S10_1678';

7.SELECT customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) ;

8.SELECT c.customerNumber,c.customerName,o.orderNumber,o.status from customers c left join orders o on c.customernumber=o.customernumber and o.ordernumber is null;

[rakeshGit419]

use classicmodels;
-- 1st issue
select c1.city,c1.customerName,c2.customerName
from customers c1
inner join customers c2
on c1.city = c2.city and c1.customerName <> c2.customerName
order by c1.city;


-- 2nd 
select p1.productCode,p1.productName,p2.textDescription
from products p1
join productlines p2
on p1.productLine = p2.productLine;

-- 3rd 
select o.orderNumber,o.status,sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber
group by o.orderNumber;

-- 4th 
select o1.orderNumber,o1.orderDate,o2.orderLineNumber,p.productName,o2.quantityOrdered,o2.priceEach
from orders o1
join orderdetails o2 on o1.orderNumber = o2.orderNumber
join products p on p.productCode = o2.productCode
order by o1.orderNumber,o2.orderLineNumber;

-- 5th
select o1.orderNumber,o1.orderDate,c.customerName,o2.orderLineNumber,p.productName,o2.quantityOrdered,o2.priceEach
from orders o1
join orderdetails o2
on o1.orderNumber = o2.orderNumber
join products p
on p.productCode = o2.productCode
join customers c
on c.customerNumber = o1.customerNumber
order by o1.orderNumber,o2.orderLineNumber;

-- 6th 
select o1.orderNumber,p.productName,p.msrp,o2.priceEach
from orders o1
join orderdetails o2
on o1.orderNumber = o2.orderNumber
join products p
on p.productCode = o2.productCode
where p.productCode = 'S10_1678' and p.msrp > o2.priceEach;

-- 7th  issue
select c.customerNumber,c.customerName,o.orderNumber,o.status
from customers c
inner join orders o
on c.customerNumber = o.customerNumber
order by o.orderNumber;

-- 8th
select c.customerNumber,c.customerName,o.orderNumber,o.status
from customers c
left join orders o
on c.customerNumber = o.customerNumber
where o.orderNumber is null and o.status is null;

[ps030300]

1. select a.city as city,a.customerName as customerName,b.customerName as customerName from customers a,customers b where a.customerNumber <> b.customerNumber AND a.city=b.city order by a.city;
   2.select p.productCode,p.productName,l.textdescription from products p join productlines l on p.productline=l.productline;
   3.select p.orderNumber, p.status,SUM( q.quantityOrdered*q.priceEach) as total from orders p inner join orderdetails q on p.orderNumber = q.orderNumber group by p.orderNumber LIMIT 5;
   4.Select o.orderNumber,o.orderDate,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach from orders o ,orderDetails od ,products p WHERE o.orderNumber = od.orderNumber AND od.productCode = p.productCode ORDER BY o.orderNumber ,od.orderLineNumber;

[Batchusatyasri]

1. select a.city as city,a.customerName as customerName,b.customerName as customerName from customers a,customers b where a.customerNumber <> b.customerNumber and a.city=b.city order by a.city;

2. select productCode,productName,textDescription from products p inner join productlines pl on p.productline=pl.productline;

3. select o.orderNumber , o.status , sum(priceEach*quantityOrdered) from orders o inner join orderdetails od
   on o.orderNumber=od.orderNumber group by od.orderNumber;

4.select a.orderNumber,a.orderDate,o2.orderLineNumber,p.productName,b.quantityOrdered,b.priceEach
from orders a
join orderdetails b on a.orderNumber = b.orderNumber
join products p on p.productCode = b.productCode
order by a.orderNumber,b.orderLineNumber;

5.select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) inner join customers using(customerNumber) order by orderNumber,orderLineNumber;

6. select orderNumber,productName,msrp,priceEach from products p inner join orderdetails od on p.productcode=od.productcode and p.msrp>od.priceEach where p.productCode='S10_1678';
7. select customers.customerNumber,customers.customerName,orders.orderNumber,orders.status from customers left join orders on customers.customerNumber=orders.customerNumber;
8. select customers.customerNumber,customers.customerName,orders.orderNumber,orders.status from customers left join orders on customers.customerNumber=orders.customerNumber where orders.orderNumber is null;

[bhanu-04]

1. select A.city, A.customerName as customerName, B.customerName as customerName from customers A join customers B where A.city = B.city and A.customerNumber<>B.customerNumber order by A.city;
2. SELECT A.productCode , A.ProductName , B.textDescription FROM products A INNER JOIN productlines B ON A.productline = B.productline;
3. select A.orderNumber, A.status, sum(B.quantityOrdered*B.priceEach) from orders A inner join orderdetails B where A.orderNumber=B.orderNumber group by A.orderNumber;
4. select orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using (orderNumber) inner join products using (productCode) order by orderNumber,orderLineNumber;
5. select A.orderNumber, A.orderDate, B.customerName, C.orderLineNumber, p.productName, C.quantityOrdered, C.priceEach from orders A, orderdetails C, products p, customers B where A.orderNumber = C.orderNumber and C.productCode = p.productCode and A.customerNumber = B.customerNumber order by A.orderNumber,C.orderLineNumber;
6. select orderNumber,productName,msrp,priceEach from products p inner join orderdetails od on p.productcode=od.productcode and p.msrp>od.priceEach where p.productCode='S10_1678';
7. select c.customernumber,c.customername,o.ordernumber,o.status from customers c left join orders o on c.customernumber=o.customernumber order by c.customernumber ;
8. select c.customerNumber, c.customerName, o.orderNumber, o.status from customers c left join orders o on c.customerNumber = o.customerNumber where o.orderNumber is null and o.status is null;

[Khyati-tripathi]

1. select c.city,c.customerName, c1.customerName 
from customers c inner join customers c1 using(city)
where(c.customerName<> c1.customerName) 
order by city; 

2. select p.productCode, p.productName , pl.textDescription
 from products p natural join productlines pl; 
 
3.  select o.orderNumber, o.status, 
 sum(d.priceEach*d.quantityOrdered) as total 
 from orders o natural join orderdetails d
 group by o.orderNumber;

4.  SELECT o.orderNumber,o.orderdate,d.orderLineNumber,
 p.productname,d.quantityordered,d.priceeach
  from orders o inner join orderDetails d
  USING(orderNumber) inner join products p
  using(productCode) ORDER BY o.orderNumber,d.orderLineNumber;

 5.  SELECT  o.orderNumber, o.orderDate,
  c.customerName,d.orderLineNumbers,p.productName,
  d.quantityOrdered,d.priceEach from orders o 
  inner join customers c using(customernumber) 
  inner join orderdetails d using(ordernumber) 
  inner join products p USING(productCode)
  ORDER BY orderNumber,orderLineNumber;

6.  SELECT d.orderNumber, p.productName, p.MSRP,
   d.priceEach from orderdetails d inner join 
   products p using(productCode)
  WHERE d.productCode = 'S10_1678'
   AND p.MSRP>d.priceEach;

  7. c.customerNumber,c.customerName,
o.orderNumber,o.status
from customers c left join orders o 
using(customerNumber); 

8. select c.customerNumber,c.customerName,
o.orderNumber,o.status
from customers c left join orders o 
using(customerNumber)
where o.orderNumber is null; 

[amruth2211]

`1)
> SELECT c.city , c.customerName ,d.customerName FROM customers c INNER JOIN
    -> customers d ON c.city=d.city WHERE c.customerName <> d.customerName ORDER BY c.city;

2)
> SELECT c.productCode , c.productName ,d.textDescription FROM products c INNER JOIN 
	->productLines d ON c.productLine= d.productLine ;

3)
> SELECT c.orderNumber ,c.status ,SUM((d.quantityOrdered*d.priceEach)) AS total FROM orders c, orderDetails d
    -> WHERE c.orderNumber=d.orderNumber GROUP BY c.orderNumber;
4)
>  SELECT o1.orderNumber, o1.orderDate, o2.orderLineNumber, p.productName,
    ->  o2.quantityOrdered, o2.priceEach
    ->  FROM orders o1, orderdetails o2, products p
    ->  WHERE o1.orderNumber = o2.orderNumber AND
    ->  o2.productCode = p.productCode
    ->  ORDER BY o1.orderNumber, o2.orderLineNumber
    ->      LIMIT 13;

5)
 select orderNumber,productName,msrp,priceEach from products inner join orderdetails using(productCode) where productCode="S10_1678" and msrp>priceEach;

6)
select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from customers inner join orders using(customerNumber) inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;

 7).
 c.customerNumber,c.customerName,
o.orderNumber,o.status
from customers c left join orders o 
using(customerNumber); 

8)
Select c.customerNumber,c.customerName,o.orderNumber,o.status from customers c LEFT JOIN orders o  ON c.customerNumber = o.customerNumber AND o.orderNumber IS NULL;`

[Prem-3]

use classicmodels;

-- 1st issue
select c1.city, c1.customerName, c2.customerName
from customers c1
inner join customers c2
on c1.city = c2.city and c1.customerName <> c2.customerName
order by c1.city;

-- 2nd 
select p1.productCode,  p1.productName, p2.textDescription
from products p1
join productlines p2
on p1.productLine = p2.productLine;

-- 3rd 
select o.orderNumber, o.status,  sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber
group by o.orderNumber;

-- 4th 
select o1.orderNumber, o1.orderDate, o2.orderLineNumber, p.productName, o2.quantityOrdered, o2.priceEach
from orders o1
join orderdetails o2 on o1.orderNumber = o2.orderNumber
join products p on p.productCode = o2.productCode
order by o1.orderNumber,o2.orderLineNumber;

-- 5th
select o1.orderNumber, o1.orderDate, c.customerName, o2.orderLineNumber, p.productName, o2.quantityOrdered, o2.priceEach
from orders o1
join orderdetails o2
on o1.orderNumber = o2.orderNumber
join products p
on p.productCode = o2.productCode
join customers c
on c.customerNumber = o1.customerNumber
order by o1.orderNumber,o2.orderLineNumber;

-- 6th 
select o1.orderNumber, p.productName, p.msrp, o2.priceEach
from orders o1
join orderdetails o2
on o1.orderNumber = o2.orderNumber
join products p
on p.productCode = o2.productCode
where p.productCode = 'S10_1678' and p.msrp > o2.priceEach;

-- 7th  
select c.customerNumber, c.customerName, o.orderNumber, o.status
from customers c
inner join orders o
on c.customerNumber = o.customerNumber
order by o.orderNumber;

-- 8th
select c.customerNumber, c.customerName, o.orderNumber, o.status
from customers c
left join orders o
on c.customerNumber = o.customerNumber
where o.orderNumber is null and o.status is null;

[VigneshVishwa]

SELECT c.city , c.customerName , m.customerName FROM customers c INNER JOIN customers m 
ON c.city = m.city where c.customerName<> m.customerName order by c.city;

SELECT p.productCode,p.productName, d.textDescription FROM products p JOIN productlines d 
WHERE p.productLine=d.productLine;

select o.orderNumber , o.status, sum(quantityOrdered*priceEach) as total from orders o inner join orderdetails od on o.orderNumber=od.orderNumber
group by od.orderNumber;

select o.orderNumber,o.orderDate,od.orderLineNumber ,p.productName,od.quantityOrdered,od.priceEach from orders o INNER JOIN orderdetails od on 
o.orderNumber=od.orderNumber INNER JOIN products p on od.productCode=p.productCode
order by orderNumber,orderLineNumber;

SELECT o.orderNumber,o.orderdate,c.customerName,od.orderLineNumber,p.productName, od.quantityOrdered,od.priceEach FROM orders o
INNER JOIN customers c ON o.customerNumber=c.customerNumber
INNER JOIN orderdetails od ON o.orderNumber=od.orderNumber
INNER JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

SELECT od.orderNumber, p.productName, msrp, priceEach FROM orderdetails od
INNER JOIN products p ON od.productCode=p.productCode
WHERE p.productCode = 'S10_1678' AND priceEach<msrp;

SELECT c.customernumber,c.customername,o.ordernumber,o.status FROM customers c 
LEFT JOIN orders o ON c.customernumber=o.customernumber ORDER BY c.customernumber ;

SELECT c.customernumber,c.customername,o.ordernumber,o.status FROM customers c 
LEFT JOIN orders o ON c.customernumber=o.customernumber WHERE o.ordernumber is null;

[DivikaAgrawal]

1. SELECT c1.customerName, c2.customerName,c1.city
   FROM c1.customers INNER JOIN c2.customers
   WHERE c1.city = c2.city AND c1.customerName <> c2.customerName
   ORDER BY c1.city

2. SELECT p.productCode,p.productName,t.textDescription
   FROM products p JOIN productlines t
   WHERE p.productline=t.productline;

3. SELECT a.orderNumber,a.status,SUM(b.quantity*b.priceEach) as total
   FROM orders a JOIN orderdetails b
   WHERE a.orderNumber=b.orderNumber
   GROUP BY a.orderNumber;

4. SECT o.orderNumber,o.orderDate,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach
   FROM orders o
   INNER JOIN orderdetail od ON o.orderNumber=od.orderNumber
   INNER JOIN product p ON od.productCode=p.productCode
   ORDER BY orderNumber,orderLine;

5. SELECT a.orderNumber,a.orderDate,c.customerName,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach
   FROM order a
   INNER JOIN customers c ON a.customerName=c.customerName
   INNER JOIN orderdetails od ON a.orderNumber=od.orderNumber
   INNER JOIN products p ON od.productCode=p.productCode
   ORDER BY a.orderNumber,od.orderLineNumber;

6. SELECT od.orderNumber, p.productName, msrp, priceEach
   FROM orderdetails od
   INNER JOIN products p ON od.productCode=p.productCode
   WHERE p.productCode = 'S10_1678' AND priceEach<msrp;

7. SELECT c.customerNumber, c.customerName, o.orderNumber, o.status
   FROM customers c
   LEFT JOIN orders o on c.customerNumber = o.customerNumber;

8. SELECT c.customerNumber, c.customerName, o.orderNumber, o.status
   FROM customers c
   LEFT JOIN orders o on c.customerNumber = o.customerNumber
   WHERE o.orderNumber is NULL and o.status is NULL;

[mkaifahmed]

1. select c1.city,c1.customerName,c2.customerName from customers c1 inner join customers c2 using(city) where c1.customerName>c2.customerName order by c1.city;

2. select p.productCode,p.productName,pl.textDescription from products p natural join productLines pl;

3. select orderNumber,status,sum(quantityOrdered*priceEach) as total from orders inner join orderdetails using (orderNumber) group by orderNumber ;

4. select orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;

5. select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from customers inner join orders using(customerNumber) inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;

6. select orderNumber,productName,msrp,priceEach from products inner join orderdetails using(productCode) where productCode="S10_1678" and msrp>priceEach;

7. select customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) ;

8. select customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) where orderNumber is NULL;

[meghvi-raj]

--Q1
select a.city,
    a.customerName,
    b.customerName
from customers a,
    customers b
where a.city = b.city
    AND a.customerName <> b.customerName
order by a.city;

--Q2
select p.productCode,
    p.productName,
    l.textDescription
from products p,
    productlines l
where p.productline = l.productline;

--Q3
select d.orderNumber,
    o.status,
    sum(d.priceEach * d.quantityOrdered) as `Total`
from orderdetails d,
    orders o
where d.orderNumber = o.orderNumber
group by orderNumber;

--Q4
select o.orderNumber,
    o.orderDate,
    c.customerName,
    d.orderLineNumber,
    p.productName,
    d.quantityOrdered,
    d.priceEach
from customers c,
    products p,
    orders o,
    orderdetails d
where c.customerNumber = o.customerNumber
    AND o.orderNumber = d.orderNumber
    AND d.productCode = p.productCode
ORDER BY o.orderNumber,
    d.orderLineNumber;

--Q5
select o.orderNumber,
    o.orderDate,
    c.customerName,
    d.orderLineNumber,
    p.productName,
    d.quantityOrdered,
    d.priceEach
from customers c,
    orders o,
    orderdetails d,
    products p
where o.orderNumber = d.orderNumber
    and d.productCode = p.productCode
    and o.customerNumber = c.customerNumber
order by o.orderNumber,
    d.orderLineNumber;

--Q6
select d.orderNumber,
    p.productName,
    p.MSRP,
    d.priceEach
from orderdetails d,
    products p
where p.productCode = 'S10_1678'
    AND d.productCode = p.productCode
    AND d.priceEach < p.msrp;

--Q7
select c.customerNumber,
    c.customerName,
    o.orderNumber,
    o.status
from customers c
    left join orders o on o.customerNumber = c.customerNumber;

--Q8
SELECT c.customerNumber,
    c.customerName,
    o.ordernumber,
    o.status
FROM customers c
    LEFT JOIN orders o ON c.customerNumber = o.customerNumber
    AND o.ordernumber IS NULL;

[ghost]

1. select c1.city,c1.customerName,c2.customerName from customers c1 inner join customers c2 using(city) where c1.customerName>c2.customerName order by c1.city;
2. select p.productCode,p.productName,pl.textDescription from products p natural join productLines pl;
3. select orderNumber,status,sum(quantityOrdered*priceEach) as total from orders inner join orderdetails using (orderNumber) group by orderNumber ;
4. select orderNumber,orderDate,orderLineNumber,productName,quantityOrdered,priceEach from orders inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;
5. select orderNumber,orderDate,customerName,orderLineNumber,productName,quantityOrdered,priceEach from customers inner join orders using(customerNumber) inner join orderdetails using(orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber ;
6. select orderNumber,productName,msrp,priceEach from products inner join orderdetails using(productCode) where productCode="S10_1677" and msrp>priceEach;
7. select customerNumber,customerName,orderNumber,status from customers left join orders using(customerNumber) ;
8. select customerNumber, customerName, orderNumber, status from customers left join orders using(customerNumber) where orderNumber is NULL;

[vshvandana]

select c1.city, c1.customerName, c2.customerName from  customers c1 inner join customers c2 where c1.city=c2.city and c1.customerName<> c2.customerName order by city;

select productCode, productName, textDescription from products inner join productlines using(productLine);

select orderNumber, status, sum(priceEach*quantityOrdered)as total from orderdetails inner join orders using(orderNumber) group by orderNumber;

select * from orders inner join orderdetails using (orderNumber) inner join products using(productCode) order by orderNumber,orderLineNumber;

select * from customers inner join orders using(customerNumber) inner join orderdetails using (orderNumber) inner join products using(productCode) order by orderNumber, orderLineNumber;

SELECT od.orderNumber, p.productName, msrp, priceEach FROM orderdetails od INNER JOIN products p ON od.productCode=p.productCode WHERE p.productCode = 'S10_1678' AND priceEach<msrp;

SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber=o.customerNumber;

SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber=o.customerNumber AND o.ordernumber IS NULL;

[dhanyaMeruva9]

use classicmodels;
--1
SELECT c.city , c.customerName, m.customerName FROM customers c JOIN customers m
ON c.city = m.city WHERE c.customerName<>m.customerName ORDER BY c.city;

--2
SELECT p.productCode , p.ProductName , l.textDescription FROM products p INNER JOIN productlines l
ON p.productline = l.productline;

--3
SELECT o.orderNumber , o.status , SUM(priceEach*quantityOrdered) as 'Total Sales' FROM orders o INNER JOIN orderdetails od
ON o.orderNumber=od.orderNumber GROUP BY od.orderNumber;

--4
SELECT o.orderNumber,o.orderDate, od.orderLineNumber, p.productName, od.quantityOrdered,od.priceEach FROM orders o
JOIN orderdetails od ON o.orderNumber=od.orderNumber
JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

--5
SELECT o.orderNumber,o.orderdate,c.customerName,od.orderLineNumber,p.productName, od.quantityOrdered,od.priceEach FROM orders o
INNER JOIN customers c ON o.customerNumber=c.customerNumber
INNER JOIN orderdetails od ON o.orderNumber=od.orderNumber
INNER JOIN products p ON od.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

--6
SELECT od.orderNumber, p.productCode, p.productName, msrp, priceEach FROM orderdetails od
INNER JOIN products p ON od.productCode=p.productCode
WHERE p.productCode = 'S10_1678' AND priceEach<msrp;

--7
SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber;

--8
SELECT c.customerNumber, c.customerName, o.ordernumber, o.status FROM customers c
LEFT JOIN orders o ON c.customerNumber=o.customerNumber AND o.ordernumber IS NULL;

[ashumore1998]

-- 1. Write a query to display a list of customers who locate in the same city by joining the customers table to itself.

select c1.city,c1.customerName as customer_from_c1 ,c2.customerName as customer_from_c2
from customers c1
join customers c2
on c1.city = c2.city
where c1.customerName<>c2.customerName
order by c1.city;

--2. Write a query to get:

--The productCode and productName from the products table.
--The textDescription of product lines from the productlines table.

select p1.productCode,p1.productName,p2.textDescription
from products p1
join productlines p2
on p1.productLine = p2.productLine
;

--3 Write a query that returns order number, order status, and total sales from the orders and orderdetails tables as follows:

select od.orderNumber,o.status,(od.priceEach* od.quantityOrdered) as total
from orders o
join orderdetails od
on o.orderNumber =od.orderNumber
order by (od.priceEach* od.quantityOrdered) desc
;

--4 Write a query to fetch the complete details of orders from the orders, orderDetails, and products table, and sort them by orderNumber and orderLineNumber as follows:

select o.orderNumber,o.orderDate,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach
from orders o
join orderdetails od
on o.orderNumber =od.orderNumber
join products p
on od.productCode =p.productCode
order by o.orderNumber,od.orderLineNumber
;

--5 Write a query to perform INNER JOIN of four tables:

--Display the details sorted by orderNumber, orderLineNumber as per the following

select o.orderNumber,o.orderDate,c.customerName,od.orderLineNumber,p.productName,od.quantityOrdered,od.priceEach
from customers c
join orders o
on c.customerNumber=o.customerNumber
join orderdetails od
on o.orderNumber =od.orderNumber
join products p
on od.productCode =p.productCode
order by o.orderNumber,od.orderLineNumber
;

--6 Write a query to find the sales price of the product whose code is S10_1678 that is less than the manufacturer’s suggested retail price (MSRP) for that product as follows:

select o.orderNumber,p.productName,p.MSRP,od.priceEach
from orders o
join orderdetails od
on o.orderNumber =od.orderNumber
join products p
on od.productCode =p.productCode
where p.productCode = 'S10_1678'
and p.MSRP>od.priceEach
;

--7 Each customer can have zero or more orders while each order must belong to one customer. Write a query to find all the customers and their orders as follows:

select c.customerNumber,c.customerName,o.orderNumber,o.status
from customers c
left join orders o
on c.customerNumber=o.customerNumber
;

--8 Write a query that uses the LEFT JOIN to find customers who have no order:

select c.customerNumber,c.customerName,o.orderNumber,o.status
from customers c
left join orders o
on c.customerNumber=o.customerNumber
where o.orderNumber is null
;

[var5]

#1. Write a query to display a list of customers who locate in the same city by joining the customers table to itself.

SELECT c1.city,c1.customerName,c2.customerName FROM customers c1
JOIN customers c2
ON c1.city = c2.city
ORDER BY c1.city ASC;

#2. Write a query to get:
-- The productCode and productName from the products table.
-- The textDescription of product lines from the productlines table.

SELECT p.productCode,p.productName,p2.textDescription FROM products p
JOIN productLines p2
ON p.productLine = p2.productLine;

#3. Write a query that returns order number, order status, and total sales from
-- the orders and orderdetails tables as follows:
SELECT o1.orderNumber,o1.status,SUM(o2.quantityOrdered*o2.priceEach) AS total_sales
FROM orders o1
JOIN orderdetails o2
ON o1.orderNumber = o2.orderNumber
GROUP BY orderNumber;

#4. Write a query to fetch the complete details of orders from the orders, orderDetails, and products table, and sort
-- them by orderNumber and orderLineNumber as follows
SELECT o.orderNumber,o.orderDate,o2.orderLineNumber,p.productName,o2.quantityOrdered,o2.priceEach
FROM orders o
JOIN orderdetails o2
ON o.orderNumber = o2.orderNumber
JOIN products p
ON p.productCode = o2.productCode
ORDER BY o.orderNumber,o2.orderLineNumber;

#5. Write a query to perform INNER JOIN of four tables:

SELECT o2.orderNumber,o2.orderDate,o.orderLineNumber,p.productName,o.quantityOrdered,o.priceEach FROM products p
JOIN orderdetails o
ON p.productCode = o.productCode
JOIN orders o2
ON o.orderNumber = o2.orderNumber
JOIN customers c
ON o2.customerNumber = c.customerNumber
ORDER BY o2.orderNumber,o.orderLineNumber;

#6. Write a query to find the sales price of the product whose code is S10_1678 that is less
-- than the manufacturer’s suggested retail price (MSRP) for that product as follows:

SELECT o.orderNumber,p.productName,p.MSRP,o.priceEach FROM orderdetails o
JOIN products p
ON o.productCode = p.productCode
WHERE p.productCode = 'S10_1678'
AND o.priceEach < p.MSRP;

#7 - Each customer can have zero or more orders while each order must belong to one customer.
-- Write a query to find all the customers and their orders as follows:
SELECT o.customerNumber,c.customerName,o.orderNumber,o.status FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;

#8. Write a query that uses the LEFT JOIN to find customers who have no order:
SELECT c.customerNumber,c.customerName,o.orderNumber,o.status FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
AND orderNumber IS NULL;

[bijayjenafs]

-- 1. Write a query to display a list of customers who locate in the same city by joining the customers table to itself.

select c1.city , c1.customerName, c2.customerName
from customers c1
join customers c2 using (city)
where c1.customerName < c2.customerName
order by c1.city;

-- 2. Write a query to get:
-- - The productCode and productName from the products table.
-- - The textDescription of product lines from the productlines table.

select productCode, productName, textDescription
from products
join productlines using (productline);

-- 3. Write a query that returns order number, order status, and total
-- sales from the orders and orderdetails tables.

select orderNumber, status, sum(quantityOrdered * priceEach) as total
from orders
join orderdetails using (orderNumber)
group by orderNumber, status;

-- 4. Write a query to fetch the complete details of orders from the orders, 
-- orderDetails, and products table, and sort them by orderNumber and orderLineNumber.

select *
from products
join orderdetails using (productCode)
join orders using (orderNumber)
order by orderNumber, orderLineNumber;

-- 5. Write a query to perform INNER JOIN of four tables.
-- Display the details sorted by orderNumber, orderLineNumber.

select * 
from orders
join orderdetails using (orderNumber)
join products using (productCode)
join productlines using (productline)
order by orderNumber, orderLineNumber;

-- 6. Write a query to find the sales price of the product whose code is S10_1678
-- ie less than the manufacturer’s suggested retail price (MSRP) for that product.

select orderNumber, productName, msrp, priceEach
from products 
join orderdetails using (productCode)
where productCode = 'S10_1678' and priceEach < msrp;

-- 7. Each customer can have zero or more orders while each order must belong to
-- one customer. Write a query to find all the customers and their orders.

select customerNumber, customerName, orderNumber, status
from customers
left join orders using (customerNumber);

-- 8. Write a query that uses the LEFT JOIN to find customers who have no order.

select customerNumber, customerName, orderNumber, status
from customers
left join orders using (customerNumber)
where orderNumber is null;

[NANDU-R-N]

#1. Write a query to display a list of customers who locate in the same city by joining the customers table to itself.

USE classicmodels;

SELECT c1.city,c1.customerName,c2.customerName FROM customers c1
JOIN customers c2
ON c1.city = c2.city
ORDER BY c1.city ASC
;

#2. Write a query to get:
-- The productCode and productName from the products table.
-- The textDescription of product lines from the productlines table.

SELECT p.productCode,p.productName,p2.textDescription FROM products p
JOIN productLines p2
ON p.productLine = p2.productLine;

#3. Write a query that returns order number, order status, and total sales from
-- the orders and orderdetails tables as follows:
SELECT o1.orderNumber,o1.status,SUM(o2.quantityOrdered*o2.priceEach) AS total_sales
FROM orders o1
JOIN orderdetails o2
ON o1.orderNumber = o2.orderNumber
GROUP BY orderNumber;

#4. Write a query to fetch the complete details of orders from the orders, orderDetails, and products table, and sort
-- them by orderNumber and orderLineNumber as follows
SELECT o.orderNumber,o.orderDate,o2.orderLineNumber,p.productName,o2.quantityOrdered,o2.priceEach
FROM orders o
JOIN orderdetails o2
ON o.orderNumber = o2.orderNumber
JOIN products p
ON p.productCode = o2.productCode
ORDER BY o.orderNumber,o2.orderLineNumber;

#5. Write a query to perform INNER JOIN of four tables:

SELECT o2.orderNumber,o2.orderDate,o.orderLineNumber,p.productName,o.quantityOrdered,o.priceEach FROM products p
JOIN orderdetails o
ON p.productCode = o.productCode
JOIN orders o2
ON o.orderNumber = o2.orderNumber
JOIN customers c
ON o2.customerNumber = c.customerNumber
ORDER BY o2.orderNumber,o.orderLineNumber;

#6. Write a query to find the sales price of the product whose code is S10_1678 that is less
-- than the manufacturer’s suggested retail price (MSRP) for that product as follows:

SELECT o.orderNumber,p.productName,p.MSRP,o.priceEach FROM orderdetails o
JOIN products p
ON o.productCode = p.productCode
WHERE p.productCode = 'S10_1678'
AND o.priceEach < p.MSRP;

#7 - Each customer can have zero or more orders while each order must belong to one customer.
-- Write a query to find all the customers and their orders as follows:
SELECT o.customerNumber,c.customerName,o.orderNumber,o.status FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;

#8. Write a query that uses the LEFT JOIN to find customers who have no order:
SELECT c.customerNumber,c.customerName,o.orderNumber,o.status FROM customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
AND orderNumber IS NULL;

[TABASSUM-12345]

#1q
select t1.city as city,t1.customerName as customerName,t2.customerName as customerName from classicmodels.customers as t1,classicmodels.customers as t2 where t1.customerNumber <> t2.customerNumber AND t1.city=t2.city order by t1.city;

#2q
Select t1.productCode,t1.productName,t2.textDescription from classicmodels.products as t1 inner join classicmodels.productlines as t2 on t1.productline=t2.productline;

#3q
select t1.orderNumber as orderNumber,status,SUM(quantityOrdered * priceEach) as total from classicmodels.orders as t2 inner join classicmodels.orderdetails as t1 on t1.orderNumber=t2.orderNumber group by t1.orderNumber;

#4q
SELECT t1.orderNumber,t1.orderDate, t2.orderLineNumber, p.productName, t2.quantityOrdered,t2.priceEach FROM classicmodels.orders as t1
INNER JOIN classicmodels.orderdetails as t2 ON t1.orderNumber=t2.orderNumber
INNER JOIN classicmodels.products p ON t2.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

#5q
SELECT t1.orderNumber,t1.orderdate,c1.customerName,t2.orderLineNumber,p.productName, t2.quantityOrdered,t2.priceEach FROM classicmodels.orders as t1
INNER JOIN classicmodels.customers as c1 ON t1.customerNumber=c1.customerNumber
INNER JOIN classicmodels.orderdetails as t2 ON t1.orderNumber=t2.orderNumber
INNER JOIN classicmodels.products p ON t2.productCode=p.productCode
ORDER BY orderNumber,orderLineNumber;

#6q
SELECT t2.orderNumber, p.productName, msrp, priceEach FROM classicmodels.orderdetails as t2
INNER JOIN classicmodels.products p ON t2.productCode=p.productCode
WHERE p.productCode = 'S10_1678' AND priceEach<msrp;

#7q
SELECT c.customerNumber, c.customerName, t1.ordernumber, t1.status FROM classicmodels.customers as c
LEFT JOIN classicmodels.orders as t1 ON c.customerNumber=t1.customerNumber;

#8q
SELECT t1.customerNumber, t1.customerName, t2.ordernumber, t2.status FROM classicmodels.customers as t1
LEFT JOIN classicmodels.orders as t2 ON t1.customerNumber=t2.customerNumber AND t2.ordernumber IS NULL;