Legit start

--changes you have to make in college change the column name of customer sname -> cname and ratings -> rating
-- NEW
-- creating table 1
CREATE TABLE salesman_aj(
snum INT,
sname VARCHAR(25),
city VARCHAR(25),
commision INT);


 -- Inserting into table 1
INSERT INTO salesman_aj VALUES(1001,'Piyush','London',12);
INSERT INTO salesman_aj VALUES(1002,'Sejal','Surat',13);
INSERT INTO salesman_aj VALUES(1004,'Miti','London',11);
INSERT INTO salesman_aj VALUES(1007,'Rajesh','Baroda',15);
INSERT INTO salesman_aj VALUES(1003,'Anand','New Delhi',10);

 -- Creating table 2
CREATE TABLE customer_aj(
cnum INT,
cname VARCHAR(25),
city VARCHAR(25),
rating INT,
snum INT);


 -- Inserting into table 2
INSERT INTO customer_aj VALUES (2007,'Pratik','Rome',100,1004);
INSERT INTO customer_aj VALUES (2008,'Chinmay','Surat',300,1007);
INSERT INTO customer_aj VALUES (2006,'Chirag','London',100,1001);
INSERT INTO customer_aj VALUES (2004,'Govind','Bombay',300,1002);
INSERT INTO customer_aj VALUES (2003,'Lalit','Surat',200,1002);
INSERT INTO customer_aj VALUES (2002,'Gita','Rome',200,1003);
INSERT INTO customer_aj VALUES (2001,'Harsh','London',100,1001);

 -- Creating table 3
CREATE TABLE orders_aj(
onum INT PRIMARY KEY,
amount DECIMAL(20,5),
odate DATE,
cnum INT,
snum INT);


-- Inserting into table 3
INSERT INTO orders_aj VALUES(3011,9891.88,'10-JuN-97',2006,1001);
INSERT INTO orders_aj VALUES(3010,1309.95,'10-jUN-97',2004,1002);
INSERT INTO orders_aj VALUES(3008,4723.00,'10-mAY-97',2006,1001);
INSERT INTO orders_aj VALUES(3007,75.75,'10-Apr-97',2004,1002);
INSERT INTO orders_aj VALUES(3009,1713.23,'10-Apr-97',2002,1003);
INSERT INTO orders_aj VALUES(3006,1098.16,'10-Mar-97',2008,1007);
INSERT INTO orders_aj VALUES(3005,5160.45,'10-Mar-97',2003,1002);
INSERT INTO orders_aj VALUES(3002,1900.10,'10-Mar-97',2007,1004);
INSERT INTO orders_aj VALUES(3003,767.19,'10-Mar-97',2001,1001);
INSERT INTO orders_aj VALUES(3001,18.69,'10-mar-97',2008,1007);
-- SELECT * FROM orders_aj



-- Creating primary key for table 1 and 2
ALTER TABLE salesman_aj ADD PRIMARY KEY(SNUM);
ALTER TABLE customer_aj ADD PRIMARY KEY(CNUM);
-- DESC salesman_aj;

--Adding foriegn Keys -> names fk_add kk_add ck_add
ALTER TABLE customer_aj ADD CONSTRAINT fk_add FOREIGN KEY(SNUM) REFERENCES salesman_aj(SNUM);
ALTER TABLE orders_aj ADD CONSTRAINT kk_add FOREIGN KEY(SNUM) REFERENCES salesman_aj(SNUM);
ALTER TABLE orders_aj ADD CONSTRAINT ck_add FOREIGN KEY(CNUM) REFERENCES customer_aj(CNUM);
























-- OLD

-- Creating table 1
CREATE TABLE salesman_aj(
snum INT,
sname VARCHAR(25),
city VARCHAR(25),
commision INT);
DESC salesman_aj;

 -- Inserting into table 1
INSERT INTO salesman_aj VALUES(1001,'piyush','london',12);
INSERT INTO salesman_aj VALUES(1002,'sejal','surat',13);
INSERT INTO salesman_aj VALUES(1004,'miti','london',11);
INSERT INTO salesman_aj VALUES(1007,'Rajesh','Baroda',15);
INSERT INTO salesman_aj VALUES(1003,'Anand','New Delhi',10);

 -- Creating table 2
CREATE TABLE customer_aj(
cnum INT,
sname VARCHAR(25),
city VARCHAR(25),
ratings INT,
snum INT);
DESC customer_aj;

 -- Inserting into table 2
INSERT INTO customer_aj VALUES (2007,'Pratik','Rome',100,1004);
INSERT INTO customer_aj VALUES (2008,'Chinmay','Surat',300,1007);
INSERT INTO customer_aj VALUES (2006,'Chirag','London',100,1001);
INSERT INTO customer_aj VALUES (2004,'Govind','Bombay',300,1002);
INSERT INTO customer_aj VALUES (2003,'Lalit','Surat',200,1002);
INSERT INTO customer_aj VALUES (2002,'Gita','Rome',200,1003);
INSERT INTO customer_aj VALUES (2001,'Harsh','London',100,1001);

 -- Creating table 3
CREATE TABLE orders_aj(
onum INT PRIMARY KEY,
amount DECIMAL(20,5),
odate DATE,
cnum INT,
snum INT);
DESC orders_aj


-- Inserting into table 3
INSERT INTO orders_aj VALUES(3011,9891.88,'10-JuN-97',2006,1001);
INSERT INTO orders_aj VALUES(3010,1309.95,'10-jUN-97',2004,1002);
INSERT INTO orders_aj VALUES(3008,4723.00,'10-mAY-97',2006,1001);
INSERT INTO orders_aj VALUES(3007,75.75,'10-Apr-97',2004,1002);
INSERT INTO orders_aj VALUES(3009,1713.23,'10-Apr-97',2002,1003);
INSERT INTO orders_aj VALUES(3006,1098.16,'10-Mar-97',2008,1007);
INSERT INTO orders_aj VALUES(3005,5160.45,'10-Mar-97',2003,1002);
INSERT INTO orders_aj VALUES(3002,1900.10,'10-Mar-97',2007,1004);
INSERT INTO orders_aj VALUES(3003,767.19,'10-Mar-97',2001,1001);
INSERT INTO orders_aj VALUES(3001,18.69,'10-mar-97',2008,1007);
SELECT * FROM orders_aj



-- Creating primary key for table 1 and 2
ALTER TABLE salesman_aj ADD PRIMARY KEY(SNUM);
ALTER TABLE customer_aj ADD PRIMARY KEY(CNUM);
DESC salesman_aj;

--Adding foriegn Keys -> names fk_add kk_add ck_add
foreing KEY
ALTER TABLE customer_aj ADD CONSTRAINT fk_add FOREIGN KEY(SNUM) REFERENCES salesman_aj(SNUM);
ALTER TABLE orders_aj ADD CONSTRAINT kk_add FOREIGN KEY(SNUM) REFERENCES salesman_aj(SNUM);
ALTER TABLE orders_aj ADD CONSTRAINT ck_add FOREIGN KEY(CNUM) REFERENCES customer_aj(CNUM);