Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 62 views

    RDBMS Lab3

    Uploaded by

    Souradeep Gupta
    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.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    RDBMS Lab3

    Uploaded by

    Souradeep Gupta
    62 views7 pages
    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.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    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.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    62 views7 pages

    RDBMS Lab3

    Uploaded by

    Souradeep Gupta
    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.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 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