Scribd Logo
Upload

Welcome to Scribd!

  • 85 views

    RDBMS Lab1

    Uploaded by

    Souradeep Gupta
    The document contains SQL queries to create tables, insert data, retrieve data, update data, delete data, alter tables, and rename tables. Tables are created for clients, products, and salesmen with various columns. Data is inserted into the tables. Queries are provided to select, update, delete data from the tables based on various conditions. The structure of tables is altered by adding, modifying and dropping columns. One table is renamed in the document.

    Copyright:

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

    RDBMS Lab1

    Uploaded by

    Souradeep Gupta
    85 views5 pages
    The document contains SQL queries to create tables, insert data, retrieve data, update data, delete data, alter tables, and rename tables. Tables are created for clients, products, and salesmen with various columns. Data is inserted into the tables. Queries are provided to select, update, delete data from the tables based on various conditions. The structure of tables is altered by adding, modifying and dropping columns. One table is renamed in the document.

    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 queries to create tables, insert data, retrieve data, update data, delete data, alter tables, and rename tables. Tables are created for clients, products, and salesmen with various columns. Data is inserted into the tables. Queries are provided to select, update, delete data from the tables based on various conditions. The structure of tables is altered by adding, modifying and dropping columns. One table is renamed in the document.

    Copyright:

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

    RDBMS Lab1

    Uploaded by

    Souradeep Gupta
    The document contains SQL queries to create tables, insert data, retrieve data, update data, delete data, alter tables, and rename tables. Tables are created for clients, products, and salesmen with various columns. Data is inserted into the tables. Queries are provided to select, update, delete data from the tables based on various conditions. The structure of tables is altered by adding, modifying and dropping columns. One table is renamed in the document.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 5

    Q1.

    Create Tables:

    a) Client_master: Use to store information about clients.


    Code:
    CREATE TABLE Client_master(
    client_no VARCHAR(6),
    name VARCHAR(20),
    address1 VARCHAR(30),
    address2 VARCHAR(30),
    city VARCHAR(15),
    pincode NUMERIC(8),
    state VARCHAR(15),
    bal_due NUMERIC(10,2)
    );

    b) product_master: Use to store information about products.


    Code:
    CREATE TABLE product_master(
    prduct_no VARCHAR(6),
    description VARCHAR(15),
    profit_percent NUMERIC(4,2),
    unit_measure VARCHAR(10),
    qty_on_hand NUMERIC(8),
    reorder_lvl NUMERIC(8),
    sell_price NUMERIC(8,2),
    cost_price NUMERIC(8,2)
    );

    c) salesman_master: Use to store information about salesman working in the company.


    Code:
    CREATE TABLE salesman_master(
    salesman_no VARCHAR(6),
    salesman_name VARCHAR(20),
    address1 VARCHAR(30),
    address2 VARCHAR(30),
    city VARCHAR(20),
    pincode NUMERIC(8),
    state VARCHAR(20),
    sal_amt NUMERIC(8,2),
    tgt_to_get NUMERIC(6,2),
    ytd_sales NUMERIC(6,2),
    remarks VARCHAR(60)
    );
    Q2. Insert Data into the tables

    a) Data for client_master table


    Code:

    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);

    b) Data for product_master table


    Code:

    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);
    c) Data for salesman_master table
    Code:
    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');

    Q3. Retrieving records from a tables

    a) Find out names of all the clients.


    Code:
    Select name
    From Client_master;

    b) Retrieve the entire contents of the client_master table.


    Code:
    Select *
    From Client_master;

    c) Retrieve the list of names and the cities of all the clients.
    Code:
    Select name, city
    From Client_master;

    d) List the various products available from the product_master table.


    Code:
    Select description
    From product_master;

    e) List all the clients who are located in Bombay.


    Code:
    Select *
    From Client_master
    Where city = 'Bombay';
    f) Find the names of the salesman who have a salary equal to Rs.3000
    Code:
    Select salesman_name
    From salesman_master
    Where sal_amt = 3000;

    Q4. Updating records in a table:


    a) Change the city of client_no ‘C00005’ to ‘Bombay’.
    Code:
    Update Client_master
    Set city = 'Bombay'
    Where client_no = 'C00005';

    b) Change the bal_due of client_no ‘C00001’ to Rs.1000.


    Code:
    Update Client_master
    Set bal_due = 1000
    Where client_no = 'C00001';

    c) Change the cost price of ‘1.22 Floppies’ to Rs. 950.00.


    Code:
    Update product_master
    Set cost_price = 950
    Where description = '1.22 Floppies';

    d) Change the city of the salesman to Mumbai.


    Code:
    Update salesman_master
    Set city = 'Mumbai';

    Q5. Exercise on deleting records in a table

    a) Delete all salesman from the salesman_master whose salaries are equal to Rs.3500.
    Code:
    DELETE FROM salesman_master
    Where sal_amt = 3500;

    b) Delete all products from product_master where the quantity on hand is equal to 100.
    Code:
    DELETE FROM product_master
    Where qty_on_hand = 100;
    c) Delete from client_master where the column state holds the value 'Tamil Nadu’.
    Code:
    DELETE FROM client_master
    Where state = 'Tamil Nadu';

    Q6. Altering the table structure:

    a) Add a column called 'telephone' of data type 'number' and size ='10’ to the client_master table.
    Code:
    ALTER Table client_master ADD telephone Numeric(10);

    b) Change the size of sell_ price column in product_master to 10,2.


    Code:
    ALTER TABLE product_master
    MODIFY COLUMN sell_price Numeric(10, 2);

    Q7. Deleting the table structure along with the data:

    a) Destroy the table client _master along with its data.


    Code:
    Drop table client_master;

    Q8. Renaming the table:

    a) Change the name of the salesman master table to sman_mast.


    Code:
    ALTER TABLE salesman_master RENAME sman_mast;

    You might also like