Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql code in access 2007

Posted on 2010-11-20
9
394 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
ID: 34181545
sounds like some homework ??
0
 
LVL 75
ID: 34181589
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
ID: 34181639
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 84
ID: 34182742
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
 
LVL 30

Expert Comment

by:hnasr
ID: 34182744
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
ID: 34183625
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
ID: 34183727
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
ID: 34184551
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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