Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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);
Add Comment
Please, Sign In to add comment