Project 1
USE dbmsworkshop;
CREATE TABLE Cart(
Cart_id VARCHAR(7),
date_added VARCHAR(8),
quantity_wished VARCHAR(1),
PRIMARY KEY(Cart_id));
CREATE TABLE Customer(
cust_id VARCHAR(6)NOT NULL,
cust_fname VARCHAR(10)NOT NULL,
cust_lname VARCHAR(20)NOT NULL,
cust_email VARCHAR(20)NOT NULL,
cust_del_address VARCHAR(20)NOT NULL,
cust_dob VARCHAR(8),
cust_password VARCHAR(7),
cust_gender VARCHAR(1),
cust_city VARCHAR(20),
PRIMARY KEY(cust_id),
Cart_id VARCHAR(7)NOT NULL,
FOREIGN KEY(Cart_id)REFERENCES Cart(Cart_id)
);
INSERT INTO customer TABLE(100,Neha,shreyan,[email protected],tssp hitech city,26_12_2018,7660,f);
SELECT * FROM customer;
CREATE TABLE Seller(
Seller_id VARCHAR(6)NOT NULL,
seller_name VARCHAR(20)NOT NULL,
seller_email VARCHAR(10)NOT NULL,
seller_contact number INT(10),NOT NULL,
PRIMARY KEY(seller_id)
);
INSERT INTO seller TABLE(101,Ansi,[email protected],8765789009);
SELECT * FROM Seller;
CREATE TABLE Product(
product_id INT,
product_name VARCHAR(20),
prod_cus_id INT,
product_items VARCHAR(50),
product_color VARCHAR(8),
product_size VARCHAR(5),
product_cost VARCHAR(30),
product_quantity VARCHAR(5),
pro_type VARCHAR(20),
product_total_ratings VARCHAR(20),
product_reviews VARCHAR(8),
product_origin VARCHAR(10),
PRIMARY KEY(product_id));
INSERT INTO Product TABLE(212,1 plus mobiles,324,121,5,silver blue,13 inches,35000,1,2,99,5,2);
SELECT * FROM product;
CREATE TABLE Payment(
pay_id INT,
pay_amt VARCHAR(30),
pay_date VARCHAR(8),
PRIMARY KEY(pay_id));
INSERT INTO payment TABLE(345,35000,13_06_2022);
SELECT * FROM payment;
CREATE TABLE Orders(
ord_id INT,
order_status VARCHAR(50),
order_delivery_expected VARCHAR(20),
ord_desc VARCHAR(10),
ord_type VARCHAR(10),
PRIMARY KEY(ord_id));
INSERT INTO Orders TABLE(545,cancelled/ordered/packed/shipped/delivered,today/tomorrow,50%,payment);
SELECT * FROM Orders TABLE;
CREATE TABLE shipping address(
shipping_id INT,
billing_name VARCHAR(20),
billing_number VARCHAR(8),
street,colony VARCHAR(50),
phone_number VARCHAR(10),
pin VARCHAR(8),
PRIMARY KEY(shipping_id));
INSERT INTO shipping address(647,nth heights,768,hitech city,040_764837,500084);
SELECT * FROM shipping address;
1)SELECT customers.customer_id,customers_city FROM ORDER of the city INNER JOIN Orders ON customers.customer_city=order.orders_city;
2)ALTER TABLE table_name product BY RENAME COLUMN product TO prod_;
3)SELECT * FROM Customers WHERE CustID IN (SELECT CustID FROM Purchases WHERE productID='prod1');
4)SELECT *FROM customers c,orders o,order_details od,WHERE c.customer_id=o.customer_id AND o.order_id=od.order_id
GROUP BY od.order_id HAVING COUNT(od.item_id)>1;
5)SELECT customer_id,MAX(order_price)AS highestPurchase FROM order_details GROUP BY customer_id ORDER BY MAX(order_price)>1;
6)INSERT INTO table_name(column_list1,column_list2)VALUES(column_values1,column_values2);
INSERT INTO table_name VALUES(column_values);
INSERT INTO destination_table(column_list,..)
SELECT column_list FROM source_table WHERE CONDITION;
INSERT INTO Customers(Customers_name,contact_num,address,city)VALUES(vincy,767898678,kukatpally,hyd);
SELECT * FROM customers;
INSERT INTO country(country_name,country_code)VALUES(india,+91);
SELECT * FROM country;
INSERT INTO city(city_name,city_code)VALUES(hyd,040);
SELECT * FROM city;