Solved

sql code in access 2007

Posted on 2010-11-20
9
391 Views
Last Modified: 2013-11-27
Dear experts,
i need help with some sql code in access 2007

this is code that i have created and ran multiple queries


CREATE TABLE Employee
(
EmployeeID int PRIMARY KEY,
Last_Name varchar(255),
First_Name varchar(255),
Address varchar(255),
City varchar(255),
State varchar(255),
Telephone_Area_Code int,
Telephone_Number int,
EE0_1_Classification varchar(255),
Hire_Date int,
Salary int,
Gender varchar(255),
Age int
)


CREATE TABLE Job_Title
(
Job_Id int FORIGEN KEY,
EE0_Classification varchar(255),
Job_Title varchar(255),
Job_Description varchar(255),
Exempt varchar(255)
)

*PLease not that the job_ID PRIMARY KEY is set up as 1-9
as you can see in the database. at the moment i have to change that job_ID primary key and make
it relate to the employee table.there should be a primary key in thejob_title table and an forgien key
in the employee table can you tell me how?
i have iused the insert syntax to instert records into the tables

I have to join two tables and use BETWEEN to restrict data .use salary to restrict?
join to tables and use BETWEEN to restrict data between to restrict data. use hire date?
join to tables and use LIKE to restrict data . telephone are codes to restrict?
join to tables and use LIKE to restrict data. age to restrict?

calculate the average salary for all employees?
Calculate the maximum salaries for exempt and non exempt employees?
calculate the mximum salaries for all employees?
Calculate the minimum salaries for exempt and non exempt employees?
calculate the minimum salaries for all employees?
increase all employess salaries with any EE0 classification by 10 %?
increase all employees salaries by 5%


THE ABOVE QUESTIONS I HAVE TO JOIN THE TABLES  AND SELECT
DATA FROM BOTH TABLES.

 CAN YOU HELP ME ANSWER
THE ABOVE QUESTIONS





0
Comment
Question by:collegestudent2010
9 Comments
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
sounds like some homework ??
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
We are not allowed to help you with homework on EE.  Also, keep in mind that it's one question per question post.

mx
0
 

Author Comment

by:collegestudent2010
Comment Utility
no its not is is a sql code that i have been working on to get certified practice test for myself to learn how to do sql in access using sql
0
 
LVL 84
Comment Utility
You have no Primary Key defined on Job_Title. You'd need to add a field to that table and set it as the PK:

CREATE TABLE Job_Title
(
Job_Title_ID int PRIMARY KEY
Job_Id int FORIGEN KEY,
EE0_Classification varchar(255),
Job_Title varchar(255),
Job_Description varchar(255),
Exempt varchar(255)
)

Your Job_ID Foreign Key is most likely not needed, unless this table is used to "feed" other tables.

If you need to relate the Employee table to the Job_Title table, you'd have to include a Foreign Key field in the Employee table, and store the value of Job_Title_ID in that field.

Once you do that, to Join the tables, review the use of the sql JOIN Syntax. For example, to return all records from the Employees table, along with the text value of the related Job_Title record:

SELECT *.Employee, Job_Title.Job_Title FROM Employee JOIN Job_Title ON Employee.Job_Title_ID = Job_Title.Job_Title_ID

As to your other questions:

Restricting data is done with WHERE clauses in your SELECT statements.

AVG, MAX and MIN are SQL functions that can be used. As this is a practice test, it would seem that you would be best served by looking up those functions and applying them to your database.

Increasing the value of a field is done via an UPDATE statement.

If you build your statements and post them back here, we can review them and evaluate.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
collegestudent2010,

Attach a sample database, and explain your requirement for one issue.  That may help you in other issues, because I see almost identical requests.

Good luck!
0
 

Author Comment

by:collegestudent2010
Comment Utility
here is my builded statements below:




SELECT avg(Salary) AS Average
FROM Employee;

UPDATE Employee SET Salary = Salary*1.05;

UPDATE Employee SET Salary = Salary*1.1
WHERE EE0_1_Classification="Operatives";



somehow i have to select data in both tables in the above statements ?
which means i have to join employee table and the job title table ?

i have no clue where to start for min ,max for the exempt employess and non exempt employees
0
 

Author Comment

by:collegestudent2010
Comment Utility
As you can here is my sample date base. the above calculations problems i need help without cuz i cann figuure how to jon the tables togther and using the MAX and MIN Fuctions for non exempt and exempt employees selecting data from both tables
i dont have a Query to join the two tables togther can you help? Sample.accdb Sample.accdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 300 total points
Comment Utility
To join the two tables you need a join field. Added Job_Title to Employee table.
A query Employee_Job_Title_q is created.
Check and see if more help is needed.
Sample-2.accdb
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now