Scribd
Upload
44 views3 pages

DBMS Lab5

The document outlines the creation and management of three tables: SALES MASTER, SALES ORDER, and SALES_ORDER_DETAIL, detailing their attributes, data types, sizes, and constraints. It includes sample data for each table and specifies additional requirements such as making CLIENT_NO a primary key in CLIENT_MASTER and adding constraints to PRODUCT_MASTER. The document also contains SQL commands for altering existing tables and querying specific data.

Uploaded by

vibhavbhartiya13
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
44 views3 pages

DBMS Lab5

The document outlines the creation and management of three tables: SALES MASTER, SALES ORDER, and SALES_ORDER_DETAIL, detailing their attributes, data types, sizes, and constraints. It includes sample data for each table and specifies additional requirements such as making CLIENT_NO a primary key in CLIENT_MASTER and adding constraints to PRODUCT_MASTER. The document also contains SQL commands for altering existing tables and querying specific data.

Uploaded by

vibhavbhartiya13
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

LAB 5: To Create and manage tables with constraints on it.

CREATE THE TABLES WITH FOLLOWING SPECIFICATIONS AND


CONSTRAINTS:

TABLE NAME: SALES MASTER


ATTRIBUTE DATA TYPE SIZE CONSTRAINT
SALESMAN_NO VARCHAR2 6 PRIMARY KEY, FIRST LETTER IS ‘S’
SALES_NAME VARCHAR2 20 NOT NULL
ADDRESS VARCHAR2 20 NOT NULL
CITY VARCHAR2 20 ---
STATE VARCHAR2 20 ---
PINCODE NUMBER 6 ---
SAL_AMT NUMBER 8,2 NOT NULL, CAN’T BE ZERO
Tgt_to_get NUMBER 6,2 NOT NULL, CAN’T BE ZERO
Ytd_sales NUMBER 6,2 NOT NULL, CAN’T BE ZERO
Remark VARCHAR2 30

TABLE NAME: SALES ORDER


ATTRIBUTE DATA TYPE SIZE CONSTRAINT

S_ORDER_No VARCHAR2 6 PRIMARY KEY,FIRST LETTER IS ‘O’


S_ORDER_DATE DATE --- ---
CLIENT_NO NUMBER 10 FOREIGN KEY FROM CLIENT MASTER
SALESMAN_NO VARCHAR2 26 FOREIGN KEY FROM SALES MASTER
DELIVERY_TYPE CHAR 1 P FOR PARTIAL AND F FOR FULL, DEFAULT IS F
BILLED_YN CHAR 1 ‘Y’ FOR YES AND ‘N’ FOR NO
DELIVERY_DATE DATE --- CAN’T BE LESS THAN S_ORDER_DATE
ORDER_STATUS VARCHAR2 10 IN(IN-PROCESS,FULFILLED,BACK ORDER,CANCELLED)

TABLE NAME: Sales_order_detail


Column Dadatype Size Attributes
S_order_no Varchar2 6 PK/FK references
s_order_no of
sales_order
Product_no Varchar2 6 PK/FK references
product_no of
product_master
Qty_order Number 8
Qty_disp Number 8
Product_rate Number 10,2

DATA OF SALES_MASTER:
Sales No. Tgt_to_ Ytd_
Sales_Name Address City Pincode State Salamt get sales
A/14 worli
S00001 Kiran Bombay 400002 MAH 3000 100 50
65,
S00002 Manish Nariman Bombay 400001 MAH 3000 200 100
P-7,
S00003 Ravi Bandra Bombay 400032 MAH 3000 200 100
S00004 Ashish A/5 Juhu Bombay 400044 MAH 3500 200 150

DATA OF SALES_ORDER

Dely Salesma n Delay Orders


S_order_no S_order_date Client no type Bill yn no date s
20-Jan-
96
O19001
12-Jan-96 1 F N 50001 IP
27-Jan-
96
O19002
25-Jan-96 2 P N 50002 C
20-Feb-
96
O16865
18-Feb-96 3 F Y 500003 F
07-Apr-
96
O19003
03-Apr-96 1 F Y 500001 F
22-
May-96
O46866
20-May-96 4 P N 500002 C
26-
May-96
O10008
24-May-96 5 F N 500004 IP

Data for sale_order_detail


S_order _no Producct_no Qty_order Qty_disp Product_rate
O19001 P00001 4 4 525

O19001 P07965 2 1 8400

O19001 P07885 2 1 5250

O19002 P00001 10 0 525

O46865 P07868 3 3 3150

O46865 P07885 10 10 5250

O19003 P00001 4 4 1050

O19003 P03453 2 2 1050

O46866 P06734 1 1 12000

O46866 P07965 1 0 8400

O10008 P07975 1 0 1050

O10008 P00001 10 5 525

1) Make client_no primary key in client_master.

2) Add new column phone_number in client_master table.

3) Add not null constraint in product master with columns : description,


profit_percent, sellprice, costprice

4) Change size of client_no field in client_master.

5) Add check constraint to product_master such that sellprice is always greater


than costprice.

6) Select produc_no,description where profit percent is between 20 and 30 both


inclusive.

You might also like