a1. Use CREATE statement to create a table Client (ClientName, cellphone). Word ClientName is major key and you should outline this major key in CREATE statement. Present the statement.
a2. Use INSERT statement to add two consumer data into Client table (make up your personal knowledge for purchasers). Present the INSERT statements and use choose statement to present the table contents.
a3. Use ALTER statements to add a international key ClientName as a constraint into the Mission table. In order that table Client has a one to many relationship with table Mission. Word, you want to use TWO ALTER statements , one for including attribute ClientName into Mission table; one for including international key constraint into Mission table. Present the ALTER statements.
a4. Person ALTER statement to Add an attribute Project_Count into Worker table (knowledge sort to be integer, refer to the info sort used for Workon table (hours) in loadDB file).
a5. Use UPDATE statement to fill the worth of Project_count of every worker report in Worker table. Specifically, add the rely of whole variety of initiatives an worker works on into Project_count in Worker table for every worker. Trace: you want a subQuestion Assignment in Replace statement as follows
Replace _________
Set _____ = (choose rely (pid) …… )
Present the contents of Worker after replace.
a6 Create a table Promotion_list (EMPID, Identify, Wage, DivisionName).
a7 Load Promotion_list with the data of staff who make lower than firm common and work on not less than 2 initiatives. (Trace use INSERT INTO SELECT statement ). Present the code and end result.
Half B
b1. Enhance the finances of a challenge by 5% if there may be a supervisor engaged on it .
b2. Listing the identify of worker who work on a challenge sponsored by his/her personal division. (corelated subQuestion Assignment)
b3. Listing the identify of challenge that has finances that’s greater than ALL initiatives from ‘advertising and marketing’ division.
b4. Listing the identify of challenge that has finances which are greater than ALL initiatives from ‘chen’s division.
b5. Listing the identify of worker who work on extra initiatives than his/her supervisor.
b6. Listing The identify of division that has worker(s) who work on different division’s challenge . (corelated subQuestion Assignment)
attachment
loadDB.sql
——
a1. Create a table Client with the CREATE statement (ClientName, cellphone). ClientName is the primary key, and it have to be outlined within the CREATE statement. Show the declare.
a2. Insert two consumer data into the Client table utilizing the INSERT command (make up your personal knowledge for purchasers). Present the INSERT statements and the table contents with the choose statement.
a3. Add a international key ClientName as a constraint to the Mission table utilizing ALTER statements. In order that table Client has a one to many relationship with table Mission. Word, you want to use TWO ALTER statements , one for including attribute ClientName into Mission table; one for including international key constraint into Mission table. Present the ALTER statements.
a4. Person ALTER statement to
