Explore BrainMass
Share

SQL queries

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

Consider the 3 tables with these attributes:

TABLE MECHANIC
(
LAST_NAME VARCHAR(50),
BIRTHDATE DATETIME,
PHONE_NO CHAR(10)
)

TABLE AIRPLANE
(
SERIAL_NO CHAR(12),
MODEL VARCHAR(40),
CAPACITY INT
)

TABLE WORKS_ON
(
MECHANIC_LAST_NAME VARCHAR(50),
MECHANIC_BIRTHDATE DATETIME,
AIRPLANE_SERIAL_NO CHAR(12),
CONTRACT_AMOUNT MONEY
)

Write SQL queries to answer the following questions:

(a) Find the last names, birth dates, and phone numbers of all mechanics who have earned at least $50000 from contracts on Boeing 747 airplanes.

(b) Find the average amount of money that it has taken to do maintenance on each different model of jumbo jet (defined to be any airplane model whose capacity is at least 150).

(c) Double the capacity of each DC-10 airplane.

(d) Add to the database, for each DC-9 model airplane,
a DC-10 model airplane with a capacity 50 greater than that of the DC-9 and a serial number
the same as that of the DC-9 except with an extra leading "1".

© BrainMass Inc. brainmass.com October 25, 2018, 2:48 am ad1c9bdddf
https://brainmass.com/computer-science/sql/sql-queries-several-requirements-last-names-315646

Solution Preview

Please find the required SQL queries below.

(a)
SELECT WORKS_ON.MECHANIC_LAST_NAME, WORKS_ON.MECHANIC_BIRTHDATE, MECHANIC.PHONE_NO
FROM WORKS_ON, AIRPLANE, MECHANIC
WHERE WORKS_ON.CONTRACT_AMOUNT > 50000
AND ...

Solution Summary

SQL queries that answer specified requirements such as finding the last names, birth dates, and phone numbers of all mechanics who have earned at least $50000 from contracts on Boeing 747 airplanes. Find the average amount of money that it has taken to do maintenance on each different model of jumbo jet.

$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