Scribd Logo
Upload

Welcome to Scribd!

  • 26 views

    Lab 1 &amp Amp 2

    Uploaded by

    علي احمد
    The document contains SQL statements to create two database tables, CUSTOMERS and ORDERS, and populate them with sample data. It then writes several queries against these tables to return customer and order information by joining the tables on their primary and foreign keys.

    Copyright:

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

    Lab 1 &amp Amp 2

    Uploaded by

    علي احمد
    26 views9 pages
    The document contains SQL statements to create two database tables, CUSTOMERS and ORDERS, and populate them with sample data. It then writes several queries against these tables to return customer and order information by joining the tables on their primary and foreign keys.

    Original Title

    Lab 1 & 2

    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 to create two database tables, CUSTOMERS and ORDERS, and populate them with sample data. It then writes several queries against these tables to return customer and order information by joining the tables on their primary and foreign keys.

    Copyright:

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

    Lab 1 &amp Amp 2

    Uploaded by

    علي احمد
    The document contains SQL statements to create two database tables, CUSTOMERS and ORDERS, and populate them with sample data. It then writes several queries against these tables to return customer and order information by joining the tables on their primary and foreign keys.

    Copyright:

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

    Lab 1

    CREATE TABLE CUSTOMERS


    (
    C_ID NUMBER (2) NOT NULL,
    NAME VARCHAR2 (50) NOT NULL,
    AGE NUMBER (3) NOT NULL,
    ADDRESS VARCHAR2 (50) NOT NULL,
    SALARY NUMBER (6) NOT NULL,
    SUPERVISOR_ID NUMBER (2),
    PRIMARY KEY(C_ID)
    )
    CREATE TABLE ORDERS
    (
    OID NUMBER (3) NOT NULL,
    O_DATE DATE NOT NULL,
    CUSTOMER_ID NUMBER (2),
    AMOUNT NUMBER (4) NOT NULL,
    PRIMARY KEY(OID),
    FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(C_ID)
    )
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 1, 'Ahmad',
    32, 'Abha', 2000, NULL);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 2, 'Ali', 25,
    'Jeddah', 1500, NULL);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 3, 'Fawwaz',
    23, 'Makkah', 2000, 1);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 4, 'Fahad',
    25, 'Abha', 6500, 1);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 5, 'Sultan',
    32, 'Najran', 8500, 1);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 6, 'Ahmad',
    22, 'Jeddah', 4500, 2);
    INSERT INTO CUSTOMERS( C_ID, NAME, AGE, ADDRESS, SALARY, SUPERVISOR_ID) VALUES( 7, 'Salem',
    24, 'Riyadh', 10000, 2);
    COMMIT;
    INSERT INTO ORDERS( OID, O_DATE, CUSTOMER_ID, AMOUNT) VALUES( 100, TO_DATE('10/8/2009 12:00:00
    AM'), 3, 1500);
    INSERT INTO ORDERS( OID, O_DATE, CUSTOMER_ID, AMOUNT) VALUES( 101, TO_DATE('11/20/2009 12:00:00
    AM'), 2, 1560);
    INSERT INTO ORDERS( OID, O_DATE, CUSTOMER_ID, AMOUNT) VALUES( 102, TO_DATE('10/8/2009 12:00:00
    AM'), 3, 3000);
    INSERT INTO ORDERS( OID, O_DATE, CUSTOMER_ID, AMOUNT) VALUES( 103, TO_DATE('5/20/2008 12:00:00
    AM'), NULL, 2060);
    COMMIT;
    Lab 2
    SELECT CUSTOMERS.C_ID, CUSTOMERS.NAME, ORDERS.OID, ORDERS.O_DATE
    FROM CUSTOMERS, ORDERS
    WHERE CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID;

    SELECT C.C_ID, C.NAME, O.OID, O.O_DATE


    FROM CUSTOMERS C, ORDERS O
    WHERE C.C_ID = O.CUSTOMER_ID;
    SELECT C1.C_ID AS CUSTOMER_ID, C1.NAME AS CUSTOMER_NMAE,
    C2.C_ID AS SUPERVISOR_ID, C2.NAME AS SUPERVISOR_NAME
    FROM CUSTOMERS C1, CUSTOMERS C2
    WHERE C1.SUPERVISOR_ID = C2.C_ID;

    SELECT C2.C_ID, C2.NAME, C2.SALARY


    FROM CUSTOMERS C1, CUSTOMERS C2
    WHERE C1.name='Fahad'
    AND C2.SALARY > C1.SALARY;
    SELECT C_ID, NAME, AMOUNT, O_DATE
    FROM CUSTOMERS
    INNER JOIN ORDERS
    ON CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID;

    SELECT C_ID, NAME, AMOUNT, O_DATE


    FROM CUSTOMERS, ORDERS
    WHERE CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID;
    SELECT C_ID, NAME, OID, AMOUNT, O_DATE
    FROM CUSTOMERS
    LEFT JOIN ORDERS
    ON CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID;

    SELECT C_ID, NAME, OID, AMOUNT, O_DATE


    FROM CUSTOMERS, ORDERS
    WHERE CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID (+);
    SELECT C_ID, NAME, OID, AMOUNT, O_DATE
    FROM ORDERS
    LEFT JOIN Customers
    ON CUSTOMERS.C_ID = ORDERS.CUSTOMER_ID;

    You might also like