1. Task 1: Create a text file named Create_<GroupID>.sql (for example, Create_F01.sql) that will contain SQL statements to:
Create a database named WareMart_< GroupID >.
AI.Create all of the tables for the database according to the Database schema given with this document (separately attached).
2. Task 2: Create a text file named Insert_< GroupID>.sql that will contain SQL statements to:
I.Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in Task 3, outputs at least one record.
3. Task 3: Create a text file named Query_< GroupID>.sql that will contain all the queries to display the following
I. An alphabetically sorted list of all clients. Only client number and name are required.
AI.List of names and complete address of all employees sorted by their salary.
III. The date on which the most recent stock request has been made. The date itself will suffice.
IV. List of all the client names and their residential addresses.
V. A list of all clients that have not placed a stock request yet. Displaying client number will be sufficient.
VI. A list containing the name (surname and first name) of any employee that has picked any product(s) for a stock request.
VII. A list containing the total quantity on hand for each product (product no and description) regardless of warehouses and location.
VIII. A list showing each product requested on each client stock request.
Show client name, product number and quantity requested, sorted by client name and then product number.
IX. A list of employees (surname and first name are sufficient) and their salary for all employees whose salary is less than or equal to average salary.
X.A list of employees as in Question IX, but show their salary with a 7.5% increase.
XI. A list of all products (product number and description) and the quantity on hand for that product for each location at which it is stored within each warehouse. Sort it by product number and then place all locations (warehouse number and location number) for a given product together.
XII. A list showing product number, the quantity requested, the quantity picked and the difference between the two. For products stored in more than one location within a warehouse the quantities should be added together.
XIII. A list of supervisors (staffid, surname and first name) and all of their subordinates (staffid, surname and first name).
Note: Tasks 1 to 3 are for all students, only 7003ICT students should do extra Queries XI to XIII in Task 3.
You are required to adhere to the following output formatting conventions:
Any query requiring names of people should be printed as GivenName FamilyName (e.g. John Smith) in a column labelled NAME;
Any query requiring addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS; and
All monetary values should be printed with a dollar symbol ($), two digits after the decimal point.