Explore BrainMass
Share

SQL QUERIES

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

I am using MS SQL SERVER 2000 (I have the database built), I would like to run these quieries.

Using the database and tables write SQL queries using BETWEEN, LIKE and UNION:

1. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection (use salaries to restrict the data).
2. 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).
3. 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).
4. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection (use zip codes to restrict data).
5. Write a SQL query that uses UNION of the two tables to produce a third table.

Table: Employee

Social_security_number
Last_name
First_name
Address
City
State
Zip_code
Telephone_area_code
Telephone_number
Email_address
Job_title_code
Hire_date
Salary

Table: Job_title

Job_title_code
Job_title
Exempt_non_exempt_status
Minimum_salary
Maximum_salary

6. Write a SQL INSERT statement (enter ten records into the employee table) and (enter five records into the job_title table

© BrainMass Inc. brainmass.com October 24, 2018, 6:00 pm ad1c9bdddf
https://brainmass.com/computer-science/pattern-matching/sql-queries-28193

Solution Summary

SQL QUERIES are noted.

$2.19
See Also This Related BrainMass Solution

Write SQL queries using Between, Like and Union.

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

View Full Posting Details