collegestudent2010
asked on
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
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
Muhammad Ahmad Imran
sounds like some homework ??
We are not allowed to help you with homework on EE. Also, keep in mind that it's one question per question post.
mx
mx
ASKER
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
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.
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.
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!
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!
ASKER
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="Oper
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
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="Oper
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
ASKER
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
i dont have a Query to join the two tables togther can you help? Sample.accdb Sample.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.