Practicals
Create following Three Tables
- Salesman
- Customer
- Orders
First Create a Database with your name.
Or You can use SQL-Playground to execute this queries for FREE:
CREATE DATABASE YourNameDB;
Use the database
USE YourName;
1. create salesman table.
create table salesman (
snum int(4) unique,
sname varchar(15),
city varchar(10),
commission int(3)
);
Insert data into the salesman table.
INSERT INTO salesman (snum, sname, city, commission)
VALUES (1001, 'Piyush', 'London', 12);
INSERT INTO salesman (snum, sname, city, commission)
VALUES (1002, 'Niraj', 'Surat', 13);
INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1003, 'MITI', 'LONDON', 11);
INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1004, 'RAJESH', 'BARODA', 15);
INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1005, 'ANAND', 'NEW DELHI', 10);
INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1006, 'RAM', 'PATAN', 10);
INSERT INTO Salesman (SNUM, SNAME, CITY, COMMISSION)
VALUES (1007, 'LAXMAN', 'BOMBAY', 9);
Create customer table.
create table customer (
cnum int(4) unique,
cname varchar(15),
city varchar(10),
rating int(3),
snum int(4) references salesman(snum)
);
Insert data into customer table.
INSERT INTO customer (cnum, cname, city, rating, snum)
VALUES (2001, 'Hardik', 'London', 100, 1001);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2002, 'GITA', 'ROME', 200, 1003);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2003, 'LAXIT', 'SURAT', 200, 1002);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2004, 'GOVIND', 'BOMBAY', 300, 1002);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2005, 'CHANDU', 'LONDON', 100, 1001);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2006, 'CHAMPAK', 'SURAT', 300, 1007);
INSERT INTO Customer (CNUM, CNAME, CITY, RATING, SNUM)
VALUES (2007, 'PRATIK', 'ROME', 100, 1004);
Create orders table.
create table orders (
onum int(4) primary key,
amount int(6),
odate date,
cnum int(4) references customer(cnum),
snum int(4) references salesman(snum)
);
Insert data into orders table.
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3001, 18.69, '10/03/99', 2006, 1007);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3002, 767.19, '10/03/99', 2001, 1001);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3003, 1900.10, '10/03/99', 2007, 1004);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3004, 5160.45, '10/03/99', 2003, 1002);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3005, 1098.25, '10/04/99', 2006, 1007);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3006, 1713.12, '10/04/99', 2002, 1003);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3007, 75.75, '10/05/99', 2004, 1002);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3008, 4723.00, '10/05/99', 2005, 1001);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3009, 1309.95, '10/05/99', 2004, 1002);
INSERT INTO Order (ONUM, AMOUNT, ODATE, CNUM, SNUM)
VALUES (3010, 9898.87, '10/06/99', 2001, 1001);
Output:
