Scribd Logo
Upload

Welcome to Scribd!

  • 104 views

    Module 5 - Exercise SQL - ZunigaFM181

    Uploaded by

    cess
    The document contains 4 SQL exercises with sample tables and queries: 1. Select employees with salary less than $52,500. 2. Select unique cities in California from suppliers table, ordered descending by city. 3. Display department info and manager name from departments and employees tables, ordered by department name. 4. Delete employees with salary greater than $60,000 from employees table.

    Copyright:

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

    Module 5 - Exercise SQL - ZunigaFM181

    Uploaded by

    cess
    104 views7 pages
    The document contains 4 SQL exercises with sample tables and queries: 1. Select employees with salary less than $52,500. 2. Select unique cities in California from suppliers table, ordered descending by city. 3. Display department info and manager name from departments and employees tables, ordered by department name. 4. Delete employees with salary greater than $60,000 from employees table.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains 4 SQL exercises with sample tables and queries: 1. Select employees with salary less than $52,500. 2. Select unique cities in California from suppliers table, ordered descending by city. 3. Display department info and manager name from departments and employees tables, ordered by department name. 4. Delete employees with salary greater than $60,000 from employees table.

    Copyright:

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

    Module 5 - Exercise SQL - ZunigaFM181

    Uploaded by

    cess
    The document contains 4 SQL exercises with sample tables and queries: 1. Select employees with salary less than $52,500. 2. Select unique cities in California from suppliers table, ordered descending by city. 3. Display department info and manager name from departments and employees tables, ordered by department name. 4. Delete employees with salary greater than $60,000 from employees table.

    Copyright:

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

    Exercise – SQL

    Read the problem carefully and write the answer on the spaces
    provided for below.

    #1:
    Based on the employees table below, select all fields from
    the employees table whose salary is less than or equal to $52,500 (no
    sorting is required):
    CREATE TABLE employees
    ( employee_number int NOT NULL,
    last_name char(50) NOT NULL,
    first_name char(50) NOT NULL,
    salary int,
    dept_id int,
    CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1001, 'Smith', 'John', 62000, 500);

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1002, 'Anderson', 'Jane', 57500, 500);

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1003, 'Everest', 'Brad', 71000, 501);

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1004, 'Horvath', 'Jack', 42000, 501);

    Answer:

    SELECT * FROM employees WHERE salary <= 52500;


    ______________________________________________

    The result that you should see:

    employee_number last_name first_name salary dept_id


    1004 Horvath Jack 42000 501

    ______________________________________________

    #2:
    Based on the suppliers table below, select the unique city values that
    reside in the state of California and order the results in descending
    order by city:
    CREATE TABLE suppliers
    ( supplier_id int NOT NULL,
    supplier_name char(50) NOT NULL,
    city char(50),
    state char(25),
    CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
    );

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (100, 'Microsoft', 'Redmond', 'Washington');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (200, 'Google', 'Mountain View', 'California');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (300, 'Oracle', 'Redwood City', 'California');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (400, 'Kimberly-Clark', 'Irving', 'Texas');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (500, 'Tyson Foods', 'Springdale', 'Arkansas');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (600, 'SC Johnson', 'Racine', 'Wisconsin');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (700, 'Dole Food Company', 'Westlake Village', 'California');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (800, 'Flowers Foods', 'Thomasville', 'Georgia');

    INSERT INTO suppliers


    (supplier_id, supplier_name, city, state)
    VALUES
    (900, 'Electronic Arts', 'Redwood City', 'California');

    Answer:

    SELECT DISTINCT city FROM suppliers WHERE state = ‘California’


    ORDER BY city DESC;
    _______________________________________

    The result that you should see:

    city
    Westlake Village
    Redwood City
    Mountain View
    ______________________________________________

    #3:
    Write a query to display the department number, department name,
    department phone number, employee number, and last name of each
    department manager. Sort the output by department name (Figure
    P7.42).
    The result is as follows:

    Answer:

    SELECT D.DEPT_NUM, D.DEPT_NAME, D.DEPT_PHONE,


    D.EMP_NUM, D.EMP_LNAME
    FROM LGDEPARTMENT AS D, LGEMPLOYEE AS E
    WHERE E.DEPT_NUM = D.DEPT_NUM
    AND E.EMP_NUM = D.EMP_NUM
    ORDER BY D.DEPT_NAME;
    _______________________________________

    #4:
    Based on the employees table, delete all employee records
    whose salary is greater than $60,000:

    CREATE TABLE employees


    ( employee_number int NOT NULL,
    last_name char(50) NOT NULL,
    first_name char(50) NOT NULL,
    salary int,
    dept_id int,
    CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1001, 'Smith', 'John', 62000, 500);

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1002, 'Anderson', 'Jane', 57500, 500);

    INSERT INTO employees


    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1003, 'Everest', 'Brad', 71000, 501);
    INSERT INTO employees
    (employee_number, last_name, first_name, salary, dept_id)
    VALUES
    (1004, 'Horvath', 'Jack', 42000, 501);

    Answer:

    DELETE FROM employees WHERE salary > 60000;


    ____________________________________________

    The result that you should see:

    employee_number last_name first_name salary dept_id


    1002 Anderson Jane 57500 500
    1004 Horvath Jack 42000 501

    ______________________________________________

    You might also like