Scribd Logo
Upload

Welcome to Scribd!

  • 25 views

    CC 4057

    Uploaded by

    ilustraterp
    The document provides instructions to create a database called "MyStore" with two tables: suppliers and products. The suppliers table has fields for supplier ID, name, address, and phone. The products table has fields for product ID, product code, name, quantity, price, supplier ID (as a foreign key to suppliers), and optional info field. Data is inserted into both tables. SQL statements are then listed to perform queries on the tables, including selecting, updating, deleting records and adding a column.

    Copyright:

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

    CC 4057

    Uploaded by

    ilustraterp
    25 views2 pages
    The document provides instructions to create a database called "MyStore" with two tables: suppliers and products. The suppliers table has fields for supplier ID, name, address, and phone. The products table has fields for product ID, product code, name, quantity, price, supplier ID (as a foreign key to suppliers), and optional info field. Data is inserted into both tables. SQL statements are then listed to perform queries on the tables, including selecting, updating, deleting records and adding a column.

    Original Title

    cc4057

    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 provides instructions to create a database called "MyStore" with two tables: suppliers and products. The suppliers table has fields for supplier ID, name, address, and phone. The products table has fields for product ID, product code, name, quantity, price, supplier ID (as a foreign key to suppliers), and optional info field. Data is inserted into both tables. SQL statements are then listed to perform queries on the tables, including selecting, updating, deleting records and adding a column.

    Copyright:

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

    CC 4057

    Uploaded by

    ilustraterp
    The document provides instructions to create a database called "MyStore" with two tables: suppliers and products. The suppliers table has fields for supplier ID, name, address, and phone. The products table has fields for product ID, product code, name, quantity, price, supplier ID (as a foreign key to suppliers), and optional info field. Data is inserted into both tables. SQL statements are then listed to perform queries on the tables, including selecting, updating, deleting records and adding a column.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 2

    CC4057 Introduction to Information

    Systems
    Lab work – week 2
    1. Create a database named “MyStore”.
    2. Create a table named “suppliers” in the database with the following fields:
    supplierID INT PRIMARY KEY
    name VARCHAR(255)
    address VARCHAR(255)
    phone VARCHAR(255)
    3. Create a table named “products” in the database with the following fields:
    productID INT PRIMARY KEY
    product_code VARCHAR(255)
    name VARCHAR(255)
    quantity INT
    price INT
    supplier INT FOREIGN KEY
    *The foreign key supplier should reference the supplier_ID field of the
    suppliers table.
    4. Insert data into the “suppliers” table
    supplierID name address phone
    1 Adidas Lalitpur 889-22-12
    2 Nivea Pokhara 686-48-34
    3 CG Kathmandu 950-43-11
    5. Insert data into the “products” table
    productID product_code name quantity price supplier
    1 p001 waiwai 10000 20 3
    2 p002 cream 2000 200 2
    3 p003 shirt 2500 600 1
    4 p004 pant 5000 1200 1
    5 p005 tv 50000 50000 3

    6. Write SQL statements to do the following:


    a) show all records from both tables
    b) show the names and the prices of all products
    c) show the productID and quantity of all the products
    d) show the names and phone numbers of all the suppliers
    e) show the phone number of supplier named “Adidas”
    f) show all the products having supplier as 1
    g) show the names and prices of products which are expensive than 500
    h) show the product_code and quantity of the products having quantity
    less than or equal to 5000
    i) show the price of “waiwai”
    j) show all the details of the products which are expensive than 500
    and having quantity greater than 2000
    k) change the price of “waiwai” to 30
    l) change the quantity of cream to 1500
    m) delete the data of “waiwai” from the products table
    n) add a column named “info” with type VARCHAR(255) in the products
    table
    o) in the products table set the value of the column “info” to “from CG
    group” where supplier is equal to 3

    You might also like