About
Summarise your career here. You can make a PDF version of your resume using our free Sketch template here. Aenean commodo ligula eget dolor aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fringilla vel, aliquet nec, vulputate eget. Lorem ipsum dolor sit amet, consectetuer adipiscing elit.
Solved Tasks Examples
Introduction to MySQL
Homework 1
Create a MySQL database meeting the following requirements:
Import the Tennis database (should be done from MySQL Tutorial)
Execute the following queries and save the results. Also save the SQL statements you created to answer the query.
SELECT PLAYERNO FROM PLAYERS WHERE JOINED > 1980 AND TOWN = 'Stratford' ORDER BY PLAYERNO;
SELECT * FROM PENALTIES;
Modify query a) to display the player name instead of the player number.
Display the contents of the TEAMS table.
Display what tables are in the database.
Submit the saved results file which includes the SQL statements and results using the below name.
Each query is worth 20% of the homework grade.
Technologies used:
- MySQL
Introduction to MySQL
Homework 2
Use the Tennis database.
Execute the following queries and save the results. Also save the SQL statement you created to answer the query.
Get the numbers of the teams of which the player with the name Parmenter and initial R is a member; in this example, we assume that no two players have the same name and initials.
For each penalty, find the payment number, the amount, and the number and name of the player who incurred it.
For ALL players, get the player number and a list of the numbers of teams for which they have ever played. Some players may not have played for teams in the database. Hint a JOIN of some kind is needed.
Get the number of each player who won at least one match. A win from matches is more wins than losses.
Get the payment numbers of the penalties incurred by players who were born after 1965.
Submit the saved results file using the below name or paste as a text submission.
Each query is worth 20% of the homework grade.
Technologies used:
- MySQL
Introduction to MySQr
Homework 3
Use the Tennis database.
Execute the following queries and save the results. Also save the SQL statements you created to answer the query.
Find the number of penalties and the highest penalty amount.
For each player, get the player number and the difference between his or her lowest and highest penalty amounts.
For each team that has played in the first division, give the team number, number of matches, and total number of sets won.
Get the name and initials of each player who has incurred two or more penalties of more than $40.
Get the match numbers of the matches with the two highest numbers and also the two lowest numbers.
Submit the saved results file using the below name or paste as a text submission.
Each query is worth 20% of the homework grade.
Technologies used:
- MySQL
Introduction to MySQr
Homework 4
Use the original Tennis database. Make sure to undo any changes you may have made.
This will change the database so use ReplaceAndPopulateTennisTables.txt to restore the database to its original content.
Under Preferences -> SQL Editor you may need to disable ‘Safe Update’ mode.
Execute the following changes to the database. Save the SQL statements you created.
Add a new penalty to the PENALTIES table; the payment number is 11, this concerns player 44, the payment date was 1985-11-08, and the penalty amount is $100.
Double the amount of all penalties.
Delete the two lowest penalties.
Delete all players who live in the same town as player 57 but keep the data about player 57 (you may need to alias the sub-query (example 17.15) on the town name for 57).
Delete all penalties and matches of player 104, regardless of whether the player appears in both tables.
Submit the following queries as a file using the below name or paste as a text submission. Make sure to include the commands you created from #4.
SELECT * FROM PENALTIES;
SELECT * FROM MATCHES;
SELECT * FROM PLAYERS;
Each update is worth 20% of the homework grade
Technologies used:
- MySQL
Introduction to MySQr
Homework 5
Use the original Tennis database. Make sure to undo any changes you may have made.
This will change the database so use ReplaceAndPopulateTennisTables.txt to restore the database to its original content. Execute the following changes to the database. Save the SQL statements you created.
HINT: Many of these changes can be done through MySQLWorkbench by clicking on the wrench symbol in the schema window for the table. MySQLWorkbench will then display the SQL code it generates for the changes you have selected using the table editor.
Add a new column called Founded of type date to the TEAMS table after the TEAMNO.
Add a new column called Paid of type char (1) to the PENALTIES table.
In the PENALTIES table, change the column name AMOUNT to PENALTY_AMOUNT.
Add a new column called Date of type date to the MATCHES table.
In the MATCHES table, change the column name MATCHNO to MATCH. MATCH is a keyword but this can be done.
Submit the following queries as a file using the below name or paste as a text submission. Make sure to include the commands you created.
SELECT * FROM TEAMS;
SELECT * FROM PENALTIES;
SELECT * FROM MATCHES;
Each change is worth 20% of the homework grade.
Technologies used:
- MySQL
Introduction to MySQr
Project 1
Import the following database file city.txt
Add to the database the following table and data:
Add a EDUCATION table with the following columns;
country code as a primary key,
literacy rate,
male literacy rate,
female literacy rate
Add data for the following countries
AFG, 38, 52, 24
DEU, 99, 99, 99
NLD, 99, 99, 99
USA, 99, 99, 99
Create and execute the SQL statements for following queries and save the results.
Display the languages of the United States
Display the cities of Germany and the population of each city
Display the country and the female literacy rate of all countries
Submit the saved results file using the below name or paste as a text submission.
Include the results of the queries and the queries themselves.
Creating the database, adding the table with data, and each query is each worth 20% of the homework grade.
Technologies used:
- MySQL
Introduction to MySQr
Homework 6
Download the following design file employee.mwb
Normalize the database in the design file and save your changes (50% of grade). Read the Normalization PDF Database Normalization.pdf Database Normalization.pdf - Alternative Formats
Be aware of the possibility of duplicate data.
Name in Role and Employee and Emp_Contact_name all refer to the same data.
Name and Email in Vendor refer to the Vendor.
Assumptions made:
An employee works with one or more vendors.
An employee may have none or many dependents.
An employee may have many roles over the employee's work history and a record should be retained for each role.
Make sure to have a primary key for each table (25% of grade).
Make sure to include the relationships between the tables (25% of grade).
Submit the updated design file using the below name.
YourLastName_hw6.mwb
Hint: This is an example design file reverse engineered from the Tennis database tennis.mwb
Technologies used:
- MySQL
Introduction to MySQr
Project 2
Open and examine the following ERR design file people.mwb
Each person is described by their name and email.
Each person can be registered in multiple countries.
Ensure it is in 3rd normal form.
Ensure the relationships and primary and foreign keys are correct.
Create a database from the design.
Import the following data into the database people.txt
You may have to modify the data to match your design
Execute the following commands and save the results.
SHOW TABLES
Execute DESCRIBE for each table in the database
Export the database using MySQL Workbench
https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html
Each part; the correct design, correct database, imported data, and execute commands is each worth 25% of the homework grade.
Submit the updated design file, saved results file, and the exported database file.
YourLastName_people.mwb
YourLastName_p2.txt
YourLastName_database.txt
Technologies used:
- MySQL
Skills & Tools
Hire expert for:
-
Angular
-
React
-
Java
-
PHP
-
MySQL
-
JavaScript
-
Node.js
-
HTML/CSS/SASS/LESS
-
Python/Django
-
Ruby/Rails
-
WordPress
And many others
Pricing
-
Single task$
-
Whole course$
Language
- English