• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

sql code in access 2007

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
collegestudent2010
Asked:
collegestudent2010
1 Solution
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
sounds like some homework ??
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
collegestudent2010Author Commented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
hnasrCommented:
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
 
collegestudent2010Author Commented:
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
 
collegestudent2010Author Commented:
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
 
hnasrCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now