Customers orders and shipping

gistfile1.txt

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20),
    address VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE shipping (
    shipping_id INT PRIMARY KEY,
    order_id INT,
    shipping_date DATE,
    delivery_date DATE,
    shipping_address VARCHAR(100),
    shipping_city VARCHAR(50),
    shipping_state VARCHAR(50),
    shipping_zip_code VARCHAR(10),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Insert 10 rows into the customers table
INSERT INTO customers (customer_id, first_name, last_name, email, phone_number, address, city, state, zip_code)
VALUES
    (1, 'John', 'Doe', '[email protected]', '123-456-7890', '123 Main St', 'Anytown', 'CA', '12345'),
    (2, 'Jane', 'Doe', '[email protected]', '123-456-7891', '456 Elm St', 'Othertown', 'NY', '54321'),
    (3, 'Bob', 'Smith', '[email protected]', '123-456-7892', '789 Oak St', 'Somewhere', 'TX', '67890'),
    (4, 'Alice', 'Johnson', '[email protected]', '123-456-7893', '987 Maple Ave', 'Anywhere', 'FL', '98765'),
    (5, 'Tom', 'Wilson', '[email protected]', '123-456-7894', '654 Cedar Ln', 'Nowhere', 'WA', '45678'),
    (6, 'Emily', 'Brown', '[email protected]', '123-456-7895', '321 Birch Rd', 'Everywhere', 'MA', '34567'),
    (7, 'David', 'Jones', '[email protected]', '123-456-7896', '852 Pine St', 'Here', 'IL', '23456'),
    (8, 'Karen', 'Davis', '[email protected]', '123-456-7897', '753 Oak Ave', 'There', 'OR', '54321'),
    (9, 'Mike', 'Miller', '[email protected]', '123-456-7898', '159 Elm Ave', 'Nowhere', 'ME', '43210'),
    (10, 'Jessica', 'Lee', '[email protected]', '123-456-7899', '753 Main St', 'Anywhere', 'NC', '78901');

-- Insert 10 rows into the orders table
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
    (1, 1, '2022-01-01', 100.00),
    (2, 1, '2022-02-01', 50.00),
    (3, 2, '2022-01-15', 75.00),
    (4, 3, '2022-02-15', 200.00),
    (5, 4, '2022-03-01', 150.00),
    (6, 5, '2022-02-28', 80.00),
    (7, 6, '2022-03-15', 125.00),
    (8, 7, '2022-02-10', 95.00),
    (9, 8, '2022-03-01', 50.00),
    (10, 9, '2022-01-30', 300.00);
    
    INSERT INTO shipping (shipping_id, order_id, shipping_date, delivery_date, shipping_address, shipping_city, shipping_state, shipping_zip_code)
VALUES
    (1, 1, '2022-01-02', '2022-01-10', '123 Main St', 'Anytown', 'CA', '12345'),
	(2, 2, '2022-02-02', '2022-02-08', '123 Main St', 'Anytown', 'CA', '12345'),
    (3, 3, '2022-01-18', '2022-01-25', '456 Elm St', 'Othertown', 'NY', '54321'),
    (4, 4, '2022-02-20', '2022-02-28', '789 Oak St', 'Somewhere', 'TX', '67890'),
    (5, 5, '2022-03-02', '2022-03-08', '987 Maple Ave', 'Anywhere', 'FL', '98765'),
    (6, 6, '2022-03-01', '2022-03-07', '654 Cedar Ln', 'Nowhere', 'WA', '45678'),
    (7, 7, '2022-03-18', '2022-03-25', '321 Birch Rd', 'Everywhere', 'MA', '34567'),
    (8, 8, '2022-02-12', '2022-02-18', '852 Pine St', 'Here', 'IL', '23456'),
    (9, 9, '2022-03-03', '2022-03-10', '753 Oak Ave', 'There', 'OR', '54321'),
    (10, 10, '2022-02-05', '2022-02-12', '159 Elm Ave', 'Nowhere', 'ME', '43210');
    
    

Queries:
1.Retrieve all data from the customers table.

SELECT * FROM customers;

2.Retrieve all data from the orders table.

SELECT * FROM orders;

3.Retrieve all data from the shipping table.

SELECT * FROM shipping;

4.Retrieve the name and email of all customers.

SELECT name, email FROM customers;

5.Retrieve the name and shipping address of all customers who have placed an order.

SELECT customers.first_name, shipping_address FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id;

6.Retrieve the order date and total price of all orders.

SELECT order_date, total_amount FROM orders;

7.Retrieve the shipping date and address of all shipped orders.

SELECT shipping_date, address FROM shipping;

8.Retrieve the name and email of customers who have placed orders.

SELECT customers.first_name, customers.email FROM customers JOIN orders ON customers.customer_id = orders.customer_id;

9.Retrieve the name and shipping address of customers who have shipped orders.

SELECT customers.first_name, shipping_address FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id;

10.Retrieve the total price and shipping date of all shipped orders.

SELECT total_amount, shipping_date FROM orders JOIN shipping ON orders.order_id = shipping.order_id;

11.Retrieve the name and email of customers who have placed orders but have not had any orders shipped.

SELECT customers.first_name, customers.email FROM customers JOIN orders ON customers.customer_id = orders.customer_id LEFT JOIN shipping ON orders.order_id = shipping.order_id WHERE shipping.order_id IS NULL;

12.Retrieve the name and email of customers who have placed orders and have had orders shipped.

SELECT customers.first_name, customers.email FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id;

13.Retrieve the name and shipping address of customers who have placed orders and have had orders shipped.

SELECT customers.first_name, shipping_address FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id;

14.Retrieve the order date, total price, and shipping date of all shipped orders

SELECT order_date, total_amount, shipping_date FROM orders JOIN shipping ON orders.order_id = shipping.order_id;

15.Retrieve the name and email of customers who have placed orders with a total price greater than $100.

SELECT customers.first_name, customers.email FROM customers JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.total_amount > 100;

16.Retrieve the name and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.

SELECT customers.first_name, shipping_address FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id WHERE orders.total_amount > 100;

17.Retrieve the order date, total price, and shipping date of all shipped orders with a total price greater than $100.

SELECT order_date, total_amount, shipping_date FROM orders JOIN shipping ON orders.order_id = shipping.order_id WHERE total_amount > 100;

18.Retrieve the name, email, and total price of customers who have placed orders with a total price greater than $100.

SELECT customers.first_name, customers.email, orders.total_amount FROM customers JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.total_amount > 100;

19.Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.

SELECT customers.first_name, customers.email, shipping_address FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN shipping ON orders.order_id = shipping.order_id WHERE orders.total_amount > 100;

20.Retrieve the order date, total price, and shipping date of all shipped orders with a total price between $50 and $100.

SELECT order_date, total_amount, shipping_date FROM orders JOIN shipping ON orders.order_id = shipping.order_id WHERE total_amount BETWEEN 50 AND 100;

21. Retrieve the name, email, and total price of customers who have placed orders with a total price between $50 and $100
22. Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price between $50 and $100.
23. Retrieve the order date, total price, and shipping date of all shipped orders with a total price less than $50
24. Retrieve the name, email, and total price of customers who have placed orders with a total price less than $50.
25. Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price less than $50.
26. Retrieve the name and email of customers who have placed orders on a specific date (e.g., '2022-01-01').
27. Retrieve the name and shipping address of customers who have placed orders and have had orders shipped on a specific date (e.g., '2022-01-01').
28. Retrieve the name and email of customers who have never placed an order.
29. Retrieve the name and email of customers who have placed an order but have never had an order shipped.
30. Retrieve the total number of orders placed by each customer.
31. Retrieve the name and email of customers who have placed the most orders.
32. Retrieve the name and email of customers who have spent the most money.
33. Retrieve the name and email of customers who have placed an order in the last 30 days.
34. Retrieve the name and email of customers who have never placed an order or have not placed an order in the last year.
35. Retrieve the name and email of customers who have placed an order with a product that has the word "book" in its name.
36. Retrieve the name and email of customers who have placed an order with a product that costs more than $50.
37. Retrieve the name and email of customers who have placed an order with a product that was shipped to a specific address (e.g., '123 Main St').
38. Retrieve the name and email of customers who have placed an order with a product that was shipped using a specific shipping company (e.g., 'UPS').
39. Retrieve the name and email of customers who have placed an order with a product that was shipped on a specific date (e.g., '2022-01-01').
40. Retrieve the name and email of customers who have placed an order with a product that was shipped from a specific city (e.g., 'New York').
41. Retrieve the name and email of customers who have placed an order with a product that was shipped to a specific state (e.g., 'California').
42. Retrieve the total revenue generated by each product.
43. Retrieve the name and email of customers who have placed an order with a product that has the lowest price.
44. Retrieve the name and email of customers who have placed an order with a product that has the highest price.
45. Retrieve the name and email of customers who have placed an order with a product that has the highest quantity ordered.
46. Retrieve the name and email of customers who have placed an order with a product that has the lowest quantity ordered.

1.Retrieve all data from the customers table.

SELECT* FROM customers

2.Retrieve all data from the orders table.

select * from orders

3.Retrieve all data from the shipping table.

select * from shipping

4.Retrieve the name and email of all customers.

select concat(first_name,last_name) as Name ,email from customers

5.Retrieve the name and shipping address of all customers who have placed an order.

select first_name,shipping_address from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id=shipping.order_id

select first_name,shipping_address from customers,orders,shipping where customers.customer_id=orders.customer_id and orders.order_id=shipping.order_id;

6.Retrieve the order date and total price of all orders.

Select order_date,total_amount from orders

7.Retrieve the shipping date and address of all shipped orders.

Select shipping_date ,shipping_address from shipping

8.Retrieve the name and email of customers who have placed orders.

select first_name,email from customers join orders on customers.customer_id=orders.customer_id

9.Retrieve the name and shipping address of customers who have shipped orders.

select first_name,shipping_address from customers,orders,shipping where customers.customer_id=orders.customer_id and orders.order_id=shipping.order_id

> select first_name,shipping_address from customers  join orders on customers.customer_id=orders.customer_id  join shipping
>  on orders.order_id=shipping.order_id

10.Retrieve the total price and shipping date of all shipped orders.

select total_amount,shipping_date from orders,shipping where orders.order_id=shipping.order_id

11.Retrieve the name and email of customers who have placed orders but have not had any orders shipped.

select first_name,email from customers,orders,shipping where customers.customer_id=orders.customer_id and orders.order_id != shipping.order_id group by first_name,email

12.Retrieve the name and email of customers who have placed orders and have had orders shipped.

select first_name,email from customers,orders,shipping where customers.customer_id=orders.customer_id and orders.order_id =shipping.order_id group by first_name,email

13.Retrieve the name and shipping address of customers who have placed orders and have had orders shipped.

select first_name,shipping_address from customers,orders,shipping where customers.customer_id=orders.customer_id and orders.order_id =shipping.order_id

14.Retrieve the order date, total price, and shipping date of all shipped orders

select order_date,total_amount,shipping_date from orders,shipping where orders.order_id=shipping.order_id

15.Retrieve the name and email of customers who have placed orders with a total price greater than $100.

select first_name,email from customers,orders where customers.customer_id=orders.customer_id and total_amount>100

16.Retrieve the name and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.

select first_name,shipping_address from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount>100

17.Retrieve the order date, total price, and shipping date of all shipped orders with a total price greater than $100.

select order_date,total_amount ,shipping_date from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount between 50 and 100

18.Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.

select first_name,email,shipping_address from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount>100

19.Retrieve the order date, total price, and shipping date of all shipped orders with a total price between $50 and $100.

> select order_date,total_amount ,shipping_date from customers join orders on customers.customer_id=orders.customer_id join shipping
 on orders.order_id =shipping.order_id where total_amount between 50 and 100

21.Retrieve the name, email, and total price of customers who have placed orders with a total price between $50 and $100
select first_name,email ,total_amount from customers ,orders
where customers.customer_id=orders.customer_id and total_amount between 50 and 100

22.select first_name,email ,shipping_address from customers ,shipping,orders
where customers.customer_id=orders.customer_id and orders.order_id=shipping.order_id
and total_amount between 50 and 100

23.Retrieve the order date, total price, and shipping date of all shipped orders with a total price less than $50
select order_date,total_amount,shipping_date from orders,shipping
where orders.order_id=shipping.order_id
and total_amount <50

24.Retrieve the name, email, and total price of customers who have placed orders with a total price less than $50.
select first_name,email,total_amount from orders,customers
where customers.customer_id=orders.customer_id
and total_amount <50

25.Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price less than $50.

select first_name,email,shipping_address from orders,customers,shipping
where customers.customer_id=orders.customer_id and orders.order_id=shipping.order_id
and total_amount <50

26.Retrieve the name and email of customers who have placed orders on a specific date (e.g., '2022-01-01').
select first_name,email from customers,orders
where customers.customer_id=orders.customer_id
and order_date='2022-01-01'

27.Retrieve the name and shipping address of customers who have placed orders and have had orders shipped on a specific date (e.g., '2022-01-01').
select first_name,shipping_address from customers,orders,shipping
where customers.customer_id=orders.customer_id and orders.order_id=shipping.order_id
and shipping_date='2022-01-01'

28.Retrieve the name and email of customers who have never placed an order.
select first_name,email from customers,orders
where customers.customer_id Not in (SELECT DISTINCT orders.customer_id FROM orders)

29.Retrieve the name and email of customers who have placed an order but have never had an order shipped.
select first_name,email from customers,orders
where orders.order_id not in(SELECT DISTINCT shipping.order_id FROM shipping)

30.Retrieve the total number of orders placed by each customer.
select customer_id, count(order_id) as totalorder from orders group by customer_id

corrected 17 to 20 question
17.Retrieve the order date, total price, and shipping date of all shipped orders with a total price greater than $100.

select order_date,total_amount ,shipping_date from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount >100

18.Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.
select first_name,email,shipping_address from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount>100

19.Retrieve the name, email, and shipping address of customers who have placed orders and have had orders shipped with a total price greater than $100.

select first_name,email,shipping_address from customers join orders on customers.customer_id=orders.customer_id join shipping on orders.order_id =shipping.order_id and total_amount>100

20.Retrieve the order date, total price, and shipping date of all shipped orders with a total price between $50 and $100.

> ```
select order_date,total_amount ,shipping_date from customers join orders on customers.customer_id=orders.customer_id join shipping
 on orders.order_id =shipping.order_id where total_amount between 50 and 100

31.Retrieve the name and email of customers who have placed the most order

32.Retrieve the name and email of customers who have spent the most money.
select customer_id, sum(total_amount)as spentmoney from orders group by customer_id incomplete--------------

33.Retrieve the name and email of customers who have placed an order in the last 30 days.

select distinct first_name,email from customers join orders
on customers.customer_id = orders.customer_id
where orders.order_date>= DATEADD(day,-30,getdate())

select ASCII('A')
Select CHAR(65)

--PRINT A TO Z
Declare @start int
set @start=65
while (@start<=90)
begin
select char(@start)
set @start=@start+1
end

--print a to z

Declare @start int
set @start=97
while (@start<=123)
begin
select char(@start)
set @start=@start+1
end

select ltrim(' hello')
hello
select rtrim(' hello ')
hello
select upper(ltrim(' ramya'))
RAMYA
select lower ('RAMYA')
ramya
select reverse('ravikiran')
narikivar
select reverse('narikivar')
ravikiran
select len(' hello ')
8
select LEFT('ravikiran',4)
ravi
select Right('ravikiran',5)
kiran

select CHARINDEX('@','[email protected]',1)
8
select difference('ravikiran','ramyakiran')range 0-4
3
select nchar(8364)
€
select nchar(65)
A
SELECT PATINDEX('%world%', 'Hello, world!')
8
SELECT PATINDEX('%@idctechnologies.com%', '[email protected]')
8
SELECT PATINDEX('%@idctechnologies.com%', '[email protected]')
13
SELECT QUOTENAME('table sample1', '"')
"table sample1"
SELECT QUOTENAME('rk', '"')
"rk"
select replace ('[email protected]','idctechnologies.com','gmail.com')

select* from sample1
ALTER TABLE sample1
ADD email nvarchar(50)
insert into sample1
select 1,35,'[email protected]' union all
select 2,36,'[email protected]'

UPDATE SAMPLE1
SET EMAIL =REPLACE(email,'idctechnologies.com','gmail.com')
35 1 [email protected]
36 2 [email protected]

select replicate('*',10)

---

SELECT SOUNDEX('myth')
M300
SELECT SOUNDEX('mith')
M300
SELECT SOUNDEX('smitha')
S530
SELECT SOUNDEX('smitha')
S530

select space(9)
select* from mytable

CREATE TABLE mytable (
firstname VARCHAR(50),
lastname VARCHAR(50)
);

select firstname+ space(8)+lastname as ename from mytable
INSERT INTO mytable (firstname, lastname)
VALUES ('John', 'Doe'),
('Jane', 'Smith'),
('Bob', 'Johnson');
update mytable
set name= firstname+ space(8)+lastname as ename
UPDATE mytable
SET firstname= CONCAT(firstname, space(5), lastname)

str()-----------------
SELECT CAST(123 AS VARCHAR(10))----------------
SELECT CONVERT(VARCHAR(10), 123)------------------------------

select STRING_SPLIT (string, separator)
SELECT value
FROM STRING_SPLIT('apple,orange,banana,mango',',')

STUFF (string_expression, start, length, replace_with)
SELECT STUFF('Hello World', 7, 5, 'ramya')---------------------

select *from sample1
select name,
select stuff (email,3,5,'*****') as stuffedemail from sample1-------------------

[email protected]
r****[email protected]

[email protected]
ra*****[email protected]

substring()---------------------------------

SELECT UNICODE('A')
SELECT UNICODE('こんにちは')

SELECT TRIM(' Hello, World! ')

CONCAT_WS(separator, string1, string2, ...)
SELECT CONCAT_WS(' >', 'Hello', 'World', '!')
Hello >World >!

SELECT CONCAT_WS(' "', 'Hello', 'World', '!')
Hello "World "!

SELECT CONCAT_WS(' ', 'Hello', 'World', '!')
Hello World !