Get a Freelance Job

Use the database and tables from last week’s assignment to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the Del Mar store. Check results by selecting all columns from both tables.

1) Use the database and tables from last week’s assignment to write SQL Select queries using Between, Like with Joins:
a. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use salary to restrict data.
b. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use hire dates to restrict data.
c. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use telephone area codes to restrict data.
d. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use age to restrict data.
e. Write a SQL query that joins the two tables to produce a third table. Use Select Into.
f. Deliverable: Save each SQL Statement for the above in a Word file and post using the Assignments tab with your week 3 individual assignments.
2) Use the updated database to write the following queries:
a. Select employees’ including last names and order them by EEO-1 classification.
b. Select employees’ including last names and order them by salary.
c. Select employees’ last names and order them by salary in their EEO-1 classification.
d. Count employees grouped by Exempt/Non-Exempt Status.
e. Count employees grouped by their EEO-1 classification.
f. Deliverable: Save each SQL Statement for the above in a Word file and post using the Assignments tab with your week 3 individual assignments.

Objective:-
1.To complete a mysql query on Virtuemart tables to produce desired csv file output.
2. The mysql query produced must run in Navicat 8 mysql for windows.

Requirements:-
Job has 6 Tasks – see attached file – task.csv, where the attachment contains:
- required alias field names and desired csv format for query output
- VM database equivalent fields & tables (discovered so far)
- the 6 task details, listed under the field the task applies to.

Also need query to be commented so I can add “custom data” in right places & add extra fields easily should they ever be needed. Comments must take account of any joins needed (i.e. how to add joins).

Skills needed:-
solid mysql query creation
ability to concatenate, x-ref, append, identify (a few) fields in DB, & produce joins where data resides in different tables.
Provide coherent comments on code

I can provide DB login details to a Virtuemart example if this needed.

Attachment 2 – nevermind.txt – my attempt at part of the query – if it’s of any use!

Priority: Fairly Urgent!

I would like to commission a “paid to click” site with a difference.

#Admin needs to be able to create surveys in which advertisers can target specific members. (business to business vs. regular consumers, consumers with kids, etc)
Example: Consumer’s age, sex, income bracket, Single, Married, No. of kids, residential location (city and/ or suburb), interests, anything to help advertisers to target specific consumers.
For example, an orchid shop may want to target a consumer who likes flowers. An insurance company may wish to target car owners.

#Members cannot view ads without filling in the surveys.
Important Note: there are two types of clientele. Advertisers and consumers (web surfers). Advertisers buy

Use the database and tables from last week’s assignment to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the Del Mar store. Check results by selecting all columns from both tables.

(You are to put all your screen snapshots for the items below, each showing the required SQL statements and results, in order, in a single Microsoft Word file. Failure to do so may result in lost points on this assignment.)

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 should now have 17 total employees and 9 total job titles (2 points)

Using the database and tables in Week Two, write SQL queries using Between, Like and Union: (1 point each)

Project is to create the Card game Phase 10 for multiplayer:

When players click on the game’s link, a “Lobby” opens, and there are game rooms available, saying how many players are already in the game. There is a maximum of 8 players per game room.
If there are for example 7 players in a game, a player can still join this game, when he joins, the card game opens in a new window and no one can join the game that is now full(8 players). Though players can join a “waiting list”, so when a player loses or leaves, the first in the waiting list will be able to play and the card game will open.
Players are able to open new rooms in the lobby.

When a person starts a game, they can’t play a phase until there is at least two people.. If the second person leaves, and its just one left – the game is froze until another joins.. When a person joins, he takes over a prevous persons spot.. If there are no previous persons, and he is completely new, then he starts at the lowest phase of all uers (So if there is 5 users, and 3 are at phase 4 and 2 are at phase 2 then the new person would start at phase 2.). His points will be added by however many people there are at that lowest phase points average (For example: there are two people at phase 2.. One has 290 points, the other has 320 points. You would take the average of those two scores, and that would be the new users total points.. Of course there can be more than 2 users at that phase, there can be up to 8 at that one phase.. And if there is only 1 at that phase, then that new person starts off with the same amount of points as that person.). It is done like this so that each new joiner to a game is played fair, and they aren’t all the way in the dust.

Some information will need to be outputted from flash to MySQL database, using PHP.

Game_Id, Winner_Id, Winner_Points, Loser_Ids

Game id is the ID of the game, the first game played on the server is 1… and the 1000th game’s ID is … 1000!

Winner ID is the winner of the game.

Winner points, how many points that user had.

Loser_Ids are the ID’s of the players who lost on the table.

The ID’s are already in my server, which are the registered members…

Thanks

Project #1

Save all of your code in Notepad as a *.txt file and attach it when you submit the project. Take screen shots of all successful implementation of each set of SQL.

Data Changes and SQL Statements

Resource: Table Queries assignment

Use the database and tables that are attached to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the store. Check results by selecting all columns from both tables.

Between, Like, and Union:

Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use salary to restrict data.

Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. Use hire dates to restrict data.
Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use telephone area codes to restrict data.

Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. Use age to restrict data.

Write a SQL query that uses the UNION of the two tables to produce a third table.

Save each query in a txt file and take a screen shot of its results and submit them as an attachment.

Group:

Use the updated database to write the following queries, using the SQL GROUP statement:

Select employees’ last names and group them by EEO-1 classification.

Select employees’ last names and group them by salary.

Select employees’ last names and group them by salary in their EEO-1 classification.

Select employees’ last names and group them by salary in job titles, grouped into exempt and non-exempt.

Save all the SQL statements in a txt file and submit as an attachment.

Screen Shot