Project 1
1. SELECT *
FROM Customers
ORDER BY City ;
2.
ALTER TABLE Product
CHANGE COLUMN Name Prod_Name VARCHAR(255) NOT NULL,
CHANGE COLUMN Description Prod_Description TEXT,
CHANGE COLUMN Price Prod_Price DECIMAL(10, 2) NOT NULL,
CHANGE COLUMN CategoryID Prod_CategoryID INT,
CHANGE COLUMN StockQuantity Prod_StockQuantity INT;
3.SELECT
cust_id,
cust_name,
city,
grade
FROM
customers C
WHERE
EXISTS (
SELECT 1
FROM
orders O
WHERE
cust_id = cust_id
);
4.SELECT
cust_id,
cust_name,
city,
grade
FROM
customers
WHERE
cust_name LIKE 'A%A'
AND cust_id IN (
SELECT cust_id
FROM orders
GROUP BY cust_id
HAVING COUNT(ord_no) > 1
);
5.SELECT
O.ord_no,
O.purch_amt,
O.ord_date,
O.cust_id,
O.salesman_id
FROM
orders O
JOIN
customers C ON O.cust_id = C.cust_id
WHERE
O.delivery_status = 'yes' -- Use the correct column name 'delivery_status'
AND O.purch_amt > 10000 -- Filter for orders with cost > 1,000
GROUP BY
O.ord_no, O.purch_amt, O.ord_date, O.cust_id, O.salesman_id;
6. CREATE TABLE customers (
cust_id INT PRIMARY KEY,
cust_name VARCHAR(255),
city VARCHAR(255),
grade INT,
salesman_id INT
);
CREATE TABLE orders (
ord_no INT PRIMARY KEY,
purch_amt DECIMAL(10, 2),
ord_date DATE,
cust_id INT,
salesman_id INT,
delivery_status VARCHAR(3)
);
INSERT INTO customers (cust_id, cust_name, city, grade, salesman_id)
VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3003, 'Jozy Altidor', 'Moscow', 200, 5007),
(3004, 'Fabian Johnson', 'Paris', 300, 5006),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3008, 'Julian Green', 'London', 300, 5002),
(3009, 'Anna', 'Berlin', 100, 5003);
INSERT INTO orders (ord_no, purch_amt, ord_date, cust_id, salesman_id, delivery_status)
VALUES
(70002, 65.26, '2012-10-05', 3002, 5001, 'yes'),
(70004, 110.50, '2012-08-17', 3009, 5003, 'no'),
(70005, 2400.60, '2012-07-27', 3007, 5001, 'yes'),
(70007, 948.50, '2012-09-10', 3005, 5002, 'yes'),
(70008, 5760.00, '2012-09-10', 3002, 5001, 'yes'),
(70009, 270.65, '2012-09-10', 3002, 5005, 'yes');