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', 'johndoe@example.com', '123-456-7890', '123 Main St', 'Anytown', 'CA', '12345'),
    (2, 'Jane', 'Doe', 'janedoe@example.com', '123-456-7891', '456 Elm St', 'Othertown', 'NY', '54321'),
    (3, 'Bob', 'Smith', 'bobsmith@example.com', '123-456-7892', '789 Oak St', 'Somewhere', 'TX', '67890'),
    (4, 'Alice', 'Johnson', 'alicejohnson@example.com', '123-456-7893', '987 Maple Ave', 'Anywhere', 'FL', '98765'),
    (5, 'Tom', 'Wilson', 'tomwilson@example.com', '123-456-7894', '654 Cedar Ln', 'Nowhere', 'WA', '45678'),
    (6, 'Emily', 'Brown', 'emilybrown@example.com', '123-456-7895', '321 Birch Rd', 'Everywhere', 'MA', '34567'),
    (7, 'David', 'Jones', 'davidjones@example.com', '123-456-7896', '852 Pine St', 'Here', 'IL', '23456'),
    (8, 'Karen', 'Davis', 'karendavis@example.com', '123-456-7897', '753 Oak Ave', 'There', 'OR', '54321'),
    (9, 'Mike', 'Miller', 'mikemiller@example.com', '123-456-7898', '159 Elm Ave', 'Nowhere', 'ME', '43210'),
    (10, 'Jessica', 'Lee', 'jessicalee@example.com', '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');
    
    

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('@','ramya.m@idctechnologies.com',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%', 'ramya.m@idctechnologies.com')
8
SELECT PATINDEX('%@idctechnologies.com%', 'ravikiran.sk@idctechnologies.com')
13
SELECT QUOTENAME('table sample1', '"')
"table sample1"
SELECT QUOTENAME('rk', '"')
"rk"
select replace ('ramya.m@idctechnologies.com','idctechnologies.com','gmail.com')

select* from sample1
ALTER TABLE sample1
ADD email nvarchar(50)
insert into sample1
select 1,35,'ramya.m@idctechnologies.com' union all
select 2,36,'ravikiran.sk@idctechnologies.com'

UPDATE SAMPLE1
SET EMAIL =REPLACE(email,'idctechnologies.com','gmail.com')
35 1 ramya.m@gmail.com
36 2 ravikiran.sk@gmail.com

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-------------------

ravikiran.sk@gmail.com
r****n.sk@gmail.com

ravikiran.sk@gmail.com
ra*****an.sk@gmail.com

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 !