Scribd
Upload
79 views7 pages

RDBMS Lab3

The document contains SQL statements that create tables for a sales order database including tables for clients, products, salespeople, sales orders, and sales order details. The tables are created with the proper columns, data types, primary keys, foreign keys, and other constraints. Sample data is then inserted into the tables.

Uploaded by

Souradeep Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
79 views7 pages

RDBMS Lab3

The document contains SQL statements that create tables for a sales order database including tables for clients, products, salespeople, sales orders, and sales order details. The tables are created with the proper columns, data types, primary keys, foreign keys, and other constraints. Sample data is then inserted into the tables.

Uploaded by

Souradeep Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

CREATE TABLE Client_master(

client_no VARCHAR(6) PRIMARY KEY CHECK(client_no LIKE 'C%'),


name VARCHAR(20) NOT NULL,
address1 VARCHAR(30),
address2 VARCHAR(30),
city VARCHAR(15),
pincode NUMERIC(8),
state VARCHAR(15),
bal_due NUMERIC(10,2)
);
CREATE TABLE product_master(
product_no VARCHAR(6) PRIMARY KEY CHECK(product_no LIKE 'P%'),
description VARCHAR(15) NOT NULL,
profit_percent NUMERIC(4,2) NOT NULL,
unit_measure VARCHAR(10) NOT NULL,
qty_on_hand NUMERIC(8) NOT NULL,
reorder_lvl NUMERIC(8) NOT NULL,
sell_price NUMERIC(8,2) NOT NULL CHECK(sell_price != 0),
cost_price NUMERIC(8,2) NOT NULL CHECK(cost_price != 0)
);

CREATE TABLE salesman_master(


salesman_no VARCHAR(6) PRIMARY KEY CHECK(salesman_no LIKE 'S%'),
salesman_name VARCHAR(20) NOT NULL,
address1 VARCHAR(30) NOT NULL,
address2 VARCHAR(30),
city VARCHAR(20),
pincode NUMERIC(8),
state VARCHAR(20),
sal_amt NUMERIC(8,2) NOT NULL CHECK(sal_amt != 0),
tgt_to_get NUMERIC(6,2) NOT NULL CHECK(tgt_to_get != 0),
ytd_sales NUMERIC(6,2) NOT NULL,
remarks VARCHAR(60)
);
CREATE TABLE sales_order(
order_no VARCHAR(6) PRIMARY KEY CHECK(order_no LIKE 'O%'),
order_date DATE,
client_no VARCHAR(6),
dely_Addr VARCHAR(25),
salesman_no VARCHAR(6),
dely_type CHAR(1) DEFAULT 'F' CHECK (dely_type IN ('P', 'F')),
billed_yn CHAR(1),
dely_date DATE,
order_status VARCHAR(10) CHECK (order_status IN ('In Process', 'Fulfilled',
'BackOrder', 'Cancelled')),
CHECK (dely_date >= order_date),
FOREIGN KEY(client_no) REFERENCES client_master(client_no),
FOREIGN KEY(salesman_no) REFERENCES salesman_master(salesman_no)
);
CREATE TABLE sales_order_details(
order_no VARCHAR(6),
product_no VARCHAR(6),
qty_ordered NUMERIC(8),
qty_disp NUMERIC(8),
product_rate NUMERIC(10,2),
FOREIGN KEY(order_no) REFERENCES sales_order(order_no),
FOREIGN KEY(product_no) REFERENCES product_master(product_no)
);
Alter Table sales_order_details ADD PRIMARY KEY(order_no, product_no);

INSERT INTO Client_master(client_no, name, city, pincode, state, bal_due)


VALUES('C00001', 'Ivan Bayross', 'Bombay', 400054, 'Maharashtra', 15000);
INSERT INTO Client_master(client_no, name, city, pincode, state, bal_due)
VALUES('C00002', 'Vandana Saitwal', 'Madras', 780001, 'Tamil Nadu', 0);
INSERT INTO Client_master(client_no, name, city, pincode, state, bal_due)
VALUES('C00003', 'Pramada Jaguste', 'Bombay', 400057, 'Maharashtra', 5000);
INSERT INTO Client_master(client_no, name, city, pincode, state, bal_due)
VALUES('C00004', 'Basu Navindgi', 'Bombay', 400056, 'Maharashtra', 0);
INSERT INTO Client_master(client_no, name, city, pincode, bal_due)
VALUES('C00005','Ravi Sreedharan', 'Delhi', 100001, 2000);
INSERT INTO Client_master(client_no, name, city, pincode, state, bal_due)
VALUES('C00006', 'Rukmini', 'Bombay', 400050, 'Maharashtra', 0);
INSERT INTO product_master VALUES('P00001', '1.44 Floppies', 5, 'Piece', 100, 20,
525, 500);
INSERT INTO product_master VALUES('P03453', 'Monitors', 6, 'Piece', 10, 3, 12000,
11280);
INSERT INTO product_master VALUES('P06734', 'Mouse', 5, 'Piece', 20, 5, 1050,
1000);
INSERT INTO product_master VALUES('P07865', '1.22 Floppies', 5, 'Piece', 100, 20,
525, 500);
INSERT INTO product_master VALUES('P07868', 'Keyboards', 2, 'Piece', 10, 3, 3150,
3050);
INSERT INTO product_master VALUES('P07885', 'CD Drive', 2.5, 'Piece', 100, 3,
5250, 5100);
INSERT INTO product_master VALUES('P07965', '540 HDD', 4, 'Piece', 10, 3, 8400,
8000);
INSERT INTO product_master VALUES('P07975', '1.44 Drive', 5, 'Piece', 10, 3,
1050, 1000);
INSERT INTO product_master VALUES('P08865', '1.22 Drive', 5, 'Piece', 2, 3, 1050,
1000);
INSERT INTO salesman_master VALUES('S00001', 'Kiran', 'A/14', 'Worli', 'Bombay',
400002, 'Maharashtra', 3000, 100, 50, 'Good');
INSERT INTO salesman_master VALUES('S00002', 'Manish', '65', 'Nariman', 'Bombay',
400001, 'Maharashtra', 3000, 200, 100, 'Good');
INSERT INTO salesman_master VALUES('S00003', 'Ravi', 'P-7', 'Bandra', 'Bombay',
400032, 'Maharashtra', 3000, 200, 100, 'Good');
INSERT INTO salesman_master VALUES('S00004', 'Ashish', 'A/5', 'Juhu', 'Bombay',
400044, 'Maharashtra', 3500, 200, 150, 'Good');

INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O19001','1996-01-12','C00001','F','N','S00001','1996-01-
20','In Process');
INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O19002','1996-01-25','C00002','P','N','S00002','1996-01-
27','Cancelled');
INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O46865','1996-02-18','C00003','F','Y','S00003','1996-02-
20','Fulfilled');
INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O19003','1996-04-03','C00001','F','Y','S00001','1996-04-
07','Fulfilled');
INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O46866','1996-05-20','C00004','P','N','S00002','1996-05-
22','Cancelled');
INSERT INTO
sales_order(order_no,order_date,client_no,dely_type,billed_yn,salesman_no,dely_da
te,order_status)VALUES('O19008','1996-05-24','C00005','F','N','S00004','1996-05-
26','In Process');
INSERT INTO sales_order_details VALUES('O19001', 'P00001', 4, 4, 525);
INSERT INTO sales_order_details VALUES('O19001', 'P07965', 2, 1, 8400);
INSERT INTO sales_order_details VALUES('O19001', 'P07885', 2, 1, 5250);
INSERT INTO sales_order_details VALUES('O19002', 'P00001', 10, 0, 525);
INSERT INTO sales_order_details VALUES('O46865', 'P07868', 3, 3, 3150);
INSERT INTO sales_order_details VALUES('O46865', 'P07885', 3, 1, 5250);
INSERT INTO sales_order_details VALUES('O46865', 'P00001', 10, 10, 525);
INSERT INTO sales_order_details VALUES('O46865', 'P03453', 4, 4, 1050);
INSERT INTO sales_order_details VALUES('O19003', 'P03453', 2, 2, 1050);
INSERT INTO sales_order_details VALUES('O19003', 'P06734', 1, 1, 12000);
INSERT INTO sales_order_details VALUES('O46866', 'P07965', 1, 0, 8400);
INSERT INTO sales_order_details VALUES('O46866', 'P07975', 1, 0, 1050);
INSERT INTO sales_order_details VALUES('O19008', 'P00001', 10, 5, 525);
INSERT INTO sales_order_details VALUES('O19008', 'P07975', 5, 3, 1050);

You might also like