Explore BrainMass
Share

Sql Queries for Employees, Projects, Salary tables

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

L_lunches table
LUNCH_ID LUNCH_DATE EMPLOYEE_ID DATE_ENTERED
1 16-NOV-05 201 13-OCT-05
2 16-NOV-05 207 13-OCT-05
3 16-NOV-05 203 13-OCT-05
4 16-NOV-05 204 13-OCT-05
6 16-NOV-05 202 13-OCT-05
7 16-NOV-05 210 13-OCT-05
8 25-NOV-05 201 14-OCT-05
9 25-NOV-05 208 14-OCT-05
12 25-NOV-05 204 14-OCT-05
13 25-NOV-05 207 18-OCT-05
15 25-NOV-05 205 21-OCT-05
16 05-DEC-05 201 21-OCT-05
17 05-DEC-05 210 21-OCT-05
20 05-DEC-05 205 24-OCT-05
21 05-DEC-05 203 24-OCT-05
22 05-DEC-05 208 24-OCT-05

L_lunch_items

LUNCH_ID ITEM_NUMBER SUPPLIER_ID PRODUCT_CODE QUANTITY
1 1 Asp Fs 1
1 2 Asp Sw 2
1 3 Jbr Vr 2
2 1 Asp Sw 2
2 2 Frv Ff 1
2 3 Jbr Vr 2
2 4 Vsb As 1
3 1 Asp Fs 1
3 2 Cbc Gs 1
3 3 Frv Ff 1
3 4 Jbr Vr 1
3 5 Jbr As 1
4 1 Asp Sp 2
4 2 Cbc Sw 2
4 3 Frv Ff 1
4 4 Jbr As 2
6 1 Asp Sp 1
6 2 Cbc Gs 1
6 3 Frv Ff 1
6 4 Jbr Vr 2
6 5 Vsb As 1
7 1 Asp Fs 1
7 2 Asp Sp 1
7 3 Cbc Gs 1
7 4 Jbr Vr 1
7 5 Vsb As 1
8 1 Asp Fs 1
8 2 Cbc Gs 1
8 3 Jbr As 1
9 1 Asp Fs 1
9 2 Asp Sp 1
9 3 Cbc Sw 2
9 4 Frv Ff 1
9 5 Jbr Vr 1
9 6 Jbr As 1
12 1 Asp Fs 1
12 2 Cbc Gs 1
12 3 Jbr Vr 2
12 4 Vsb As 1
13 1 Asp Sp 2
13 2 Asp Sw 2
13 3 Frv Ff 1
13 4 Jbr As 1
15 1 Asp Sp 1
15 2 Cbc Gs 1
15 3 Frv Ff 1
15 4 Jbr As 2
16 1 Asp Fs 1
16 2 Asp Sw 1
16 3 Cbc Sw 1
16 4 Jbr Vr 1
16 5 Jbr As 1
17 1 Asp Sp 1
17 2 Cbc Gs 1
17 3 Frv Ff 1
17 4 Jbr Vr 2
17 5 Vsb As 1
20 1 Asp Fs 1
20 2 Asp Sp 1
20 3 Cbc Gs 1
20 4 Frv Ff 1
20 5 Jbr As 1
21 1 Asp Sp 1
21 2 Cbc Gs 1
21 3 Jbr Vr 2
21 4 Vsb As 1
22 1 Asp Fs 1
22 2 Cbc Gs 1
22 3 Frv Ff 1
22 4 Jbr Vr 1
22 5 Jbr As 1

L_suppliers

SUPPLIER_ID SUPPLIER_NAME
Arr Alice & Ray's Restaurant
Asp A Soup Place
Cbc Certified Beef Company
Frv Frank Reed's VegeTABLEs
Fsn Frank & Sons
Jbr Just Beverages
Jps Jim Parker's Shop
Vsb Virginia Street Bakery

1. List all the employees in the emp table. If they work for department Accounting, then add 1200 to their salary. If they work for department Sales, then add 100 to their salary, otherwise add nothing to their salary.

2. List all the suppliers and the foods that they supply from the l_foods table. If supplier_id ASP provides the food, add 1 dollar to his price. If supplier_id, CBC supplies the food, add 2 dollars to his price. Otherwise, just show the price as is.

3. List the employee_id and employee_name of the employees form the l_employees table with the top 4 credit limits.
4. Change your answer from above to list those employees with the bottom 4 credit limits.

5. From the project, project_hours, and employee table, list the all the projects, the hours worked, and the date worked. (You will need this for the project).

6. Using the Lunches database, find the total amount spent on lunches by each employee. Show first_name, last_name, credit_limit, and total_price_spent in your results. Order your answer by total_price_spent in descending order. Only show the employees
who spent more than their credit limit (Modify question 9 from the last homework.)

7. Using the Lunches database, count the number of foods supplied by each supplier. List all the suppliers. Show the supplier_id, supplier_name, and number of foods from each supplier. Sort the rows on the supplier_id. Make sure you count all suppliers, even if they don't supply foods.

8. Using the Lunches database, count the number of employees who work in each department. List all the departments. Show the department code, department name, and the number of employees. Sort on the dept_code. Make sure you count all employees, regardless of whether they work for departments or not.

© BrainMass Inc. brainmass.com October 25, 2018, 2:37 am ad1c9bdddf
https://brainmass.com/computer-science/sql/sql-queries-for-employees-projects-salary-tables-308598

Attachments

Solution Preview

Dear Student, In the attached task.txt you will find the SQL ...

Solution Summary

Contains multiple SQL queries to:

1. List all the employees in the emp table.
If they work for department Accounting, then add 1200 to their salary.
If they work for department Sales, then add 100 to their salary,
otherwise add nothing to their salary.
2. List all the suppliers and the foods that they supply from the l_foods table.
If supplier_id ASP provides the food, add 1 dollar to his price.
If supplier_id, CBC supplies the food, add 2 dollars to his price.
Otherwise, just show the price as is.
3. List the employee_id and employee_name of the employees form the l_employees
table with the top 4 credit limits.
and more...

$2.19
See Also This Related BrainMass Solution

Sql Server Database

SQL Server database for Kudler Fine Foods/Service Request SR-kf-009

Create the following two tables using the following fields:

Note: Supply the appropriate SQL Server data types when creating the tables. In the Employee table, create an Employee ID field that will generate a unique number for each employee and designate the field as the Primary Key. In the Job Title table, you will need to either utilize one of the listed fields as the table's primary key or you will need to create an additional field to use as the primary key. The primary key from the Job Title table will appear as the foreign key in the Employee table.

· Employee

§ Emp_ID

§ Last_name

§ First_name

§ Address

§ City

§ State

§ Telephone_area_code

§ Telephone_number

§ EEO-1 Classification

§ Hire_date

§ Salary

§ Gender

§ Age

§ Foreign Key from Job Title table

· Job_title

§ EEO-1 Classification

§ Job_title

§ Job description

§ Exempt / Non-Exempt Status

Using the SQL INSERT statement:

· Go to the Human Resources department in the Kudler Fine Foods intranet. Using information found in the Employee Files for the La Jolla and Encinitas stores, enter records into the employee table for the following employees:

§ Glenn Edelman

§ Eric McMullen

§ Raj Slentz

§ Erin Broun

§ Donald Carpenter

§ David Esquivez

§ Nancy Sharp

· Using the Kudler Fine Foods Job Classifications and Job Descriptions information, enter records into the job_title table for the following job titles:

§ Accounting Clerk

§ Asst. Manager

§ Bagger

§ Cashier

§ Computer Support Specialist

§ Director of Finance & Accounting

§ Retail Asst. Bakery & Pastry

§ Retail Asst. Butchers and Seafood Specialists

§ Stocker

Non-Exempt employees at Kudler Fine Foods are paid an hourly wage and are required to track their working hours.

Check the results by selecting all of the columns from both of your tables.

Using the database and tables from Week Two, write SQL statements and enter the records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check the results by selecting all of the columns from both of your tables

You will use the database you created in week 1 and will add these scripts to the database.

Project Description
Query 1 (SELECT LIKE):
Write a SQL query that joins two tables Employee and JobTitle. Use LIKE to restrict record selection. (Use age column to restrict data. I will leave it up to you to select the restricting age.)

Query 2 (SELECT BETWEEN):
Write a SQL statement that does an INNER JOIN the two tables Employee and JobTitle with the result displaying the Employee full name, EEO Classification, job description and salary. You will use the BETWEEN qualifier to join only records where the Employee salary is between $20,000 and $35,000. You will order the list by employee's last name.

Query 3 (SELECT INTO and SELECT UNION):
First write a SELECT INTO query to create a table employee_ca. What you will be doing is selecting all fields for every record where the employee is from a specific state. I used California as my state in the sample query. Make a table appropriate to the state in your current employee data

Then write a statement that uses the UNION ALL statement to select records from the employee table and all records from the employee_ca table you created above. Notice that the query displays all duplicate records.

Query 4 (SELECT GROUPORDER):
Write a SQL statement that displays the average salary of employees by EEO Classification. You only need to display the EEO Classification and the average salary. No invalid EEO Classifications should be displayed

Query 5 (SELECT DISTINCT):
Create a SELECT query that will display the number of different (distinct) states employees are in and the actual number of employees in each of the states. This means you need to use the DISTINCT and COUNT commands

Query 6 (UPDATE DATA):

Choose an EEO-1 Classification - then write a query that increases all employees' salaries that have that classification by 10%. This means you need to use the UPDATE command.

Query 7 (DELETE EMPLOYEE):

Choose an employee from the employee table - then write a query that deletes this employee. This means you need to use the DELETE command.

Query 8 (SELECT SUBQUERY):

Write a SELECT query that utilizes a SUBQUERY to determine if the EEO-Classification for an employee is invalid. This means that you need to use the NOT EXIST for the SUBQUERY.

Query 9 (SELECT-SUM-COUNT):

Create a SELECT statement that displays the Title, Number of Employees and Total salary paid to all employees for the specific Title. You need to COUNT the employees and SUM the salary. The resultant output should look like:

Query 10 (SELECT MIN-MAX):

Write a SELECT statement that displays the employee's average salary, employee's minimum salary, employee's maximum salary by title by state.

View Full Posting Details