Scribd Logo
Upload

Welcome to Scribd!

  • 25 views

    Activity No. 1 - SQL Command

    Uploaded by

    Nelson Zaidi
    This document outlines a laboratory activity on creating a database using MySQL. It describes tables to store customer, menu, category, and order data. It provides sample data to insert into each table. It then lists a series of queries to extract and display data from the tables in various formats.

    Copyright:

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

    Activity No. 1 - SQL Command

    Uploaded by

    Nelson Zaidi
    25 views5 pages
    This document outlines a laboratory activity on creating a database using MySQL. It describes tables to store customer, menu, category, and order data. It provides sample data to insert into each table. It then lists a series of queries to extract and display data from the tables in various formats.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document outlines a laboratory activity on creating a database using MySQL. It describes tables to store customer, menu, category, and order data. It provides sample data to insert into each table. It then lists a series of queries to extract and display data from the tables in various formats.

    Copyright:

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

    Activity No. 1 - SQL Command

    Uploaded by

    Nelson Zaidi
    This document outlines a laboratory activity on creating a database using MySQL. It describes tables to store customer, menu, category, and order data. It provides sample data to insert into each table. It then lists a series of queries to extract and display data from the tables in various formats.

    Copyright:

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

    School of Information and Communication Technology

    Laboratory Activity Form

    Course: Advanced Database Systems

    Topics Covered: Creating a system that uses a database using MySQL


    ● To create a database in MySQL
    Objectives: ● Add table in the database
    ● Perform simple query in the database using MySQL Console.

    DESCRIPTION:

    Database Name: Sales

    Tables:

    Customer
    *CustomerID int(11) PRIMARY KEY, AUTO
    INCREMENT
    *LastName varchar(75) not null
    *FirstName varchar(75) not null
    *Contact varchar(25) not null
    *Address varchar(75) not null

    Menu
    *MenuID int(11) PRIMARY KEY, AUTO
    INCREMENT
    *MenuName varchar(75) not null
    *CategoryID int(11) FK
    *Price double not null

    Category
    *CategoryID int(11) PRIMARY KEY, AUTO
    INCREMENT
    *CategoryName varchar(75) not null

    Orders
    *OrderID int(11) PRIMARY KEY, AUTO
    INCREMENT
    *CustomerID int(11) FK
    DateOrdered date

    OrderDetail
    OrderDetailID int(11) PRIMARY KEY, AUTO
    INCREMENT
    OrderID int(11) FK
    MenuID int(11) FK
    Quantity int(11)
    Insert the following record at the Customer table.

    Record No. 1
    CustomerID -1
    LastName -Tokyo
    FirstName -Shoji
    Contact -080-1234-5678
    Address -Kanda, Chiyoda-ku

    Record No. 2
    CustomerID -2
    LastName -Osaka
    FirstName -Shokai
    Contact -080-5679-1234
    Address -Doyama-cho, Kita-
    ku, Osaka City

    Record No. 3
    CustomerID -3
    LastName -Chugoku
    FirstName -Shonen
    Contact -080-5679-1235
    Address -Moto-machi, Naka-
    ku, Hiroshima City

    Insert the following record at the Category table.

    Record No. 1
    CategoryID -1
    CategoryName - Pork

    Record No. 2
    CategoryID -2
    CategoryName - Beef

    Record No. 3
    CategoryID -3
    CategoryName - Chicken

    Insert the following record at the Menu table.

    Record No. 1
    MenuID -1
    MenuName - Sweet and Sour
    Pork
    CategoryID -1
    Price -300

    Record No. 2
    MenuID -2
    MenuName - Kung Pao Chicken
    CategoryID -3
    Price -200

    Record No. 3
    MenuID -3
    MenuName - Ma Po Tofu
    CategoryID -2
    Price -350

    Insert the following record at the Orders table.

    Record No. 1
    OrderID -1
    CustomerID -3
    DateOrdered -1999-08-07

    Record No. 2
    OrderID -2
    CustomerID -1
    DateOrdered -1999-09-01

    Record No. 3
    OrderID -3
    CustomerID -2
    DateOrdered -1999-07-28

    Insert the following record at the OrderDetail table.

    Record No. 1
    OrderDetailID -1
    OrderID -3
    MenuID -3
    Quantity -5
    Record No. 2
    OrderDetailID -2
    OrderID -1
    MenuID -1
    Quantity -3

    Record No. 3
    OrderDetailID -3
    OrderID -2
    MenuID -2
    Quantity -4

    Extract the following:

    Paste a screenshot of your query after the number. The screenshot will show that you
    could extract the needed information correctly. Also, Write your name after this line.

    Name: ________________________________

    1. Retrieve CustomerID, FirstName, and LastName from customer table.

    2. Retrieve CustomerID from order table.

    3. Retrieve OrderID, MenuID, and Quantity from OrderDetail table.

    4. Write an SQL statement to extract the display below.

    MenuName Price
    Sweet and Sour 300
    Pork
    Kung Pao Chicken 200
    Ma Po Tofu 350

    5. Write an SQL statement to extract the display below.

    customerI Firstname Lastnam MenuName Quantit Price


    D e y
    3 Shonen Chugoku Sweet and Sour 3 300
    Pork
    2 Shokai Osaka Ma Po Tofu 5 350
    1 Shoji Tokyo Kung Pao Chicken 4 200

    6. Write an SQL statement to extract the display below.


    MenuID MenuName CategoryNa Price
    me
    1 Sweet and Sour Pork Pork 300
    2 Kung Pao Chicken Chicken 200
    3 Ma Po Tofu Beef 350

    7. Write an SQL statement to extract the display below.

    TotalAmount
    3450

    8. Retrieve from OrderDetail table the OrderID, MenuID and Quantity wherein
    quantity is less than
    4.

    9. Write an SQL statement to extract the display below.

    customerI Firstname Lastnam MenuName Quantit Pric TotalPric


    D e y e e
    3 Shonen Chugoku Sweet and Sour 3 300 900
    Pork
    2 Shokai Osaka Ma Po Tofu 5 350 1750
    1 Shoji Tokyo Kung Pao Chicken 4 200 800

    10. Retrieve from menu table the menu names and prices wherein the price is from
    300 to 350.

    11. Retrieve from customer table the customer first name and last name in the records
    wherein the CustomerID is 1 0r 3.

    12. Retrieve from OrderDetail table the OrderID and MenuID wherein the quantity is 4
    or larger.

    Upload this file in the google classroom.

    Remarks

    You might also like