Explore BrainMass
Share

Explore BrainMass

    Write SQL queries using Between, Like and Union.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Using the example databases and tables below, write SQL queries using Between, Like and Union.

    - Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.)
    - Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)
    - Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)
    - Write a SQL query that joins two tables in the example database and uses greater than to restrict record selection. (Use date of birth to restrict data.)
    - Write a SQL query that joins two tables and restricts the data based on a single job classification.
    - Write a SQL query that joins two tables and restricts data based on a specific job title and hire_date that is before a specific date.
    - Write a SQL query that uses UNION of the two tables to return appropriate results.
    - Write a SQL query that finds a job title record which has no associated employee.
    - Write a SQL query that finds all the job classifications(no duplicates).
    - Write a SQL query that finds all employees whose last name begins with the letters p through z.

    Write the following queries using the SQL GROUP statement.

    - Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification.
    - Group employees by salary: Select the employees' last names and group them by salary.
    - Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification.

    Write the following queries.

    - Find the number of employees within each job classification.
    - Find the number of exempt employees within each job title.

    SQL 2008 code:

    USE KUDLER
    CREATE TABLE JOB_TITLE
    (
    eeo_1_classification VARCHAR (20) NOT NULL,
    Job_title_id VARCHAR (5) PRIMARY KEY,
    job_title VARCHAR (50) NOT NULL,
    job_description VARCHAR (100) NOT NULL,
    exempt VARCHAR (4) NOT NULL
    )

    CREATE TABLE Employee
    (
    id_num INT IDENTITY (1, 1) PRIMARY KEY,
    last_name VARCHAR (15) NOT NULL,
    first_name VARCHAR(15) NOT NULL,
    address VARCHAR (50) NOT NULL,
    city VARCHAR (20) NOT NULL,
    state VARCHAR (2) NOT NULL,
    telephone_area_code INT NOT NULL,
    telephone_number VARCHAR (10) NOT NULL,
    eeo_1_classification VARCHAR (20) NOT NULL,
    ssn VARCHAR (20) NOT NULL,
    hire_date DATETIME NOT NULL,
    salary DECIMAL (10,2),
    gender CHAR(1) NOT NULL,
    date_of_birth VARCHAR (100) NOT NULL,
    job_title_id VARCHAR (5)
    CONSTRAINT FK_Employee_Job_title
    FOREIGN KEY REFERENCES Job_title (Job_title_id)
    )

    INSERT INTO JOB_TITLE VALUES
    ('Office Clerical','071',
    'Accounting Clerk',
    'Maintains and computes all records',
    'No');

    INSERT INTO JOB_TITLE VALUES
    ('Officials Managers','062',
    'Asst Manager',
    'Supervises and coordinates workers',
    'Yes');

    INSERT INTO JOB_TITLE VALUES
    ('Sales Worker','053',
    'Bagger',
    'Places customer items in bags',
    'No');

    INSERT INTO JOB_TITLE VALUES
    ('Sales Workers','084',
    'Cashier',
    'Itemize customer purchases',
    'No');

    INSERT INTO JOB_TITLE VALUES
    ('Technician','095',
    'Computer Support Specialist',
    'Updates software/hardware and provides training and technical assistance',
    'Yes');

    INSERT INTO JOB_TITLE VALUES
    ('Officials Managers','016',
    'Director of Finance Accounting',
    'Plans and directs finance and accounting',
    'Yes');

    INSERT INTO JOB_TITLE VALUES
    ('Craft Workers','027',
    'Retail Assistant Bakery & Pastry',
    'monitors workers',
    'No');

    INSERT INTO JOB_TITLE VALUES
    ('Operatives','038',
    'Retail Assistant Butchers and Seafood Specialist',
    'monitors workers',
    'No');

    INSERT INTO JOB_TITLE VALUES
    ('Stocker','049',
    'Office clerical',
    'Stores, prices, and restocks merchandise displays in store',
    'No');

    INSERT INTO EMPLOYEE VALUES
    ('Edelman','Glenn','175 Bishops Lane','La_Jolla','CA','619','555-0199',
    'Sales Workers','123456789','07-OCT-2003',21500.75,'M','01-JAN-1946','084');

    INSERT INTO Employee VALUES
    ('McMullen','Eric','763 Church St','Lemon Grove','CA','619','555-0135',
    'Sales Worker','234567890','1-NOV-2002',13500.00,'M','03-FEB-1990','084');

    INSERT INTO Employee VALUES
    ('Slentz','Raj','123 Torrey Dr.','North Clairmont','CA','619','555-0123',
    'Officials & Managers','157863498','1-JUN-2000',48000.00,'M','25-MAR-1976','016');

    INSERT INTO Employee VALUES
    ('Broun','Erin','2045 Parkway Apt.2B','Encinitas','CA','760','555-0100',
    'Sales Workers','654983473','12-MAR-2003',10530.00,'F','19-APR-1986','053');

    INSERT INTO Employee VALUES
    ('Carpenter','Donald','927 Second ST.', 'Encinitas','CA','619','555-0154',
    'Office/Clerical','468656425','1-NOV-2003', 15000.00,'M','05-MAY-1992','071');

    INSERT INTO Employee VALUES
    ('Esquivez','David','10983 N. Coast Hwy Apt 902','Encinitas','CA','760','555-0108',
    'Operatives','560348976','25-JUL-2003',18500.00,'M','12-JUN-1985','038');

    INSERT INTO Employee VALUES
    ('Sharp','Nancy','10793 Monteciono Rd','Ramona','CA','858','555-0135',
    'Cashier','556332157','12-JUL-2003',21000.00,'F','29-JULY-1986','053');

    Select * from Employee
    Select * from job_title

    © BrainMass Inc. brainmass.com October 10, 2019, 1:20 am ad1c9bdddf
    https://brainmass.com/computer-science/sql/sql-queries-using-between-like-union-330336

    Solution Preview

    Please find the solution in the attached file.

    Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.)
    Solution:
    select * from example.employee e,example.job_title j where e.job_title_id=j.Job_title_id and e.salary Between 15000 and 20000;

    • Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)
    Solution:
    select * from example.employee e,example.job_title j where e.job_title_id=j.Job_title_id and e.hire_date Between '2000-01-01' and '2003-01-01';

    • Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)

    Solution:
    select * from example.employee e,example.job_title j where e.job_title_id=j.Job_title_id and e.telephone_number like '555-013%';

    • Write a SQL query that joins two tables in ...

    Solution Summary

    The expert writes SQL queries using between, like union is examined.

    $2.19