SQL Challenge - Team-Juan/user-guides GitHub Wiki

SQL Challenge

Now that you have read the SQL user guide. You may be ready for the SQL challenge.

Part 1

CREATE challenge:

Create a database named 'MegaCorp'. This database will contain two tables, one named 'Employees' and one named 'Products'. The table structure will consist of the following:

Employees:

eID - The employee's ID, cannot be null and must auto increment in number.

fName - The employee's first name, maximum length is 16 and cannot be null.

lName - The employee's last name, maximum length is 24 and cannot be null.

eTitle - The employee's title, maximum length is 20.

eSalary - The employee's salary.

Products:

pID - The product ID. Cannot be null and must auto increment in number.

pName - The product's name, maximum length is 24 and cannot be null.

pDesc - The product's description, maximum length is 128.

pPrice - The product's price.

Part 2

INSERT challenge:

• Insert three rows into each table.

Part 3

SELECT challenge:

• SELECT all rows from the Employees table.
• SELECT all rows from the Products table.
• SELECT the first and last name of every employee from the Employee table.
• SELECT all employees whose first name begins with 'S'.
• SELECT all products that have a price that is more than $50.

Part 4

UPDATE challenge:

• One of your employees got married and wants their last name changed to reflect their marriage.
• One of your employees got a promotion and an increase in salary.
• One of your products price increased due to a rise in production costs.
• One of your products has a 10% discount.

Part 5

DELETE/DROP challenge:

Delete all rows from the Employees and Products tables. Drop both tables. Drop the database.

Solutions

Click here for the solutions to this challenge.