- Using a join, get the full details of all the employees who are working on the Harvest Ball promotion.
- Get the promotion names (duplicates eliminated) being worked on by employees from the Rocky Mountain Produce store.
- Get the last names of all the employees who are working on promotion P1.
- Get the employee numbers and start dates of all the employees with start dates equal to the earliest date.
- Insert yourself in the employee table using the last five digits of your phone number as the employee number and show yourself as working for Rocky Mountain Produce. Then show all the records in the employee table.
- Delete yourself from the employee table by matching your employee number, and then show all of the records in the employee table.
Please find attached the sql script. You will need to modify the part with Insert and Delete of ...
Specific SQL queries for ITD640_B database working with Employee and Store tables. Below is a sample:
--Get the last names of all the employees who are working on promotion P1.
JOIN employee ON summary.employee_no = employee.employee_no
WHERE summary.promotion_no = 'P1'
SQL Database queries
Attached in the zip is the the sample database ITD640_B from the assignment. The sample database represents a company with employees and different stores. Each employee works at just one store. The employees have various jobs in different promotional activities (promotions) and may work on more than one promotion at a time.
database is attached
The following are the tables and data in the ITD640_B database:
Using the sample database, write the scripts in a file called ITD640_P3.SQL to create the following views. Remember to include a USES clause at the top of your script file to use the ITD640_B database. Also include code that checks if the view already exists. If it does, it should be dropped and recreated.
Create a view named v_stocker showing the employee number, promotion number, and start date where the job is "stocker."
Create a view called v_no_budget with all the columns of the promotion table except the budget column.
Create a view called v_count that shows the number of employees that are working on each promotion. The view should have columns for the promotion number and the count.