Please find the solution:
Section A: Query Design [50 Marks] 1. Create a query using the Job_History and Departments tables to display the start_date in the form of September, Nineteen Eighty-Nine, Thursday, 21ST with the column name StartDate and department_name with right padding of '# using the size of 15 characters, whose start_date is after '12-Dec-1995' or department_name ends with letter g. Arrange the output on employee_id from lowest to highest. [6 Marks] 2. Create a query using the Locations table to display the country_id and total number of cities with the column name Total Cities for each country_ID, whose state province contain letter 'a' and whose total number of cities is less than 3. Arrange the output on country_id from highest to lowest. (5 Marks] 3. Create a query using the Locations table to display the street_address in capital letters with the column name Address, Create a new column with the name "NewCity" by combining of the column city by replacing letter 'o' with '@' and 4 characters of state_province from 2nd position, Size of postal_code with the column name PCLength. Restrict the output to whose location_id is more than 1400 and size of the postal code is more than 5. [6 Marks] 4. Using subquery, display street_address and remainder of postal_code by dividing 5 with the column name PCode from Locations table, whose country_id is same as the country_id of the Seattle city and the street_address begins with 201. Arrange the output on PCode from highest to the lowest. [6 Marks] 5. Using subquery, display department_id, total salary of for each department_id from Employees table, whose department_id is not belonging to 80 or 110 and whose total salary is below to all the employees; those employees do not have the commission_pct. Arrange the output on department_id from lowest to highest. [6 Marks] 6. By using CASE expression, use the Employees table to display Last_name, divide the salary by manager_id, if the remainder is null then replace with 10 and name the column as New Sal. A new column named as Job using a column job_id, with the case expression as follows; If the job_id is AD_VP', display Addl Vice President" If the job_id is 'SA_REP, display Sales Representative If the job_id is 'SA_MAN', display Salesmen Otherwise, display Others Restrict the output for all employees, whose hire_date is after '15-Jun-1998'. Arrange the output on Last_name from z to a. [6 Marks] 7. Create a view and name it as "NewStaffs using F_Staffs table with the columns of Last_name, add 3 months to Birthdate and name the column as BDate, Staff_type with the label as "SType. Restrict the output for all F_Staffs, whose salary is below 50 and last_name ends with letter e or who is not having training. Arrange the output on stype in alphabetic order. [6 Marks] 8. Assume that a table named PROJECT is already existed with the following rows. (5 Marks] ProjectID 10 20 30 PROJECT ProjectName Ground Water Clean&Green Constructions a) Add a new column. Size Column Name Location Data Type Varchar Constraint-Column Level Should not be empty 20 b) Modify the project name with Clean and Green for the project_id 20. c) Delete the constraint of Location column. 9. Assume that the table 'PROJECT' is available in user125. [4 Marks] a) Give the permissions to allow the user user995 to modify the data in table PROJECT. b) Create a save point with the name of SP1. c) Write a query to modify ProjectName of projectID 30 to Others by user user 995. d) Cancel all the transactions up to save point SP1. e) Cancel all the privileges given to user user995. Section A: Query Design [50 Marks] 1. Create a query using the Job_History and Departments tables to display the start_date in the form of September, Nineteen Eighty-Nine, Thursday, 21ST with the column name StartDate and department_name with right padding of '# using the size of 15 characters, whose start_date is after '12-Dec-1995' or department_name ends with letter g. Arrange the output on employee_id from lowest to highest. [6 Marks] 2. Create a query using the Locations table to display the country_id and total number of cities with the column name Total Cities for each country_ID, whose state province contain letter 'a' and whose total number of cities is less than 3. Arrange the output on country_id from highest to lowest. (5 Marks] 3. Create a query using the Locations table to display the street_address in capital letters with the column name Address, Create a new column with the name "NewCity" by combining of the column city by replacing letter 'o' with '@' and 4 characters of state_province from 2nd position, Size of postal_code with the column name PCLength. Restrict the output to whose location_id is more than 1400 and size of the postal code is more than 5. [6 Marks] 4. Using subquery, display street_address and remainder of postal_code by dividing 5 with the column name PCode from Locations table, whose country_id is same as the country_id of the Seattle city and the street_address begins with 201. Arrange the output on PCode from highest to the lowest. [6 Marks] 5. Using subquery, display department_id, total salary of for each department_id from Employees table, whose department_id is not belonging to 80 or 110 and whose total salary is below to all the employees; those employees do not have the commission_pct. Arrange the output on department_id from lowest to highest. [6 Marks] 6. By using CASE expression, use the Employees table to display Last_name, divide the salary by manager_id, if the remainder is null then replace with 10 and name the column as New Sal. A new column named as Job using a column job_id, with the case expression as follows; If the job_id is AD_VP', display Addl Vice President" If the job_id is 'SA_REP, display Sales Representative If the job_id is 'SA_MAN', display Salesmen Otherwise, display Others Restrict the output for all employees, whose hire_date is after '15-Jun-1998'. Arrange the output on Last_name from z to a. [6 Marks] 7. Create a view and name it as "NewStaffs using F_Staffs table with the columns of Last_name, add 3 months to Birthdate and name the column as BDate, Staff_type with the label as "SType. Restrict the output for all F_Staffs, whose salary is below 50 and last_name ends with letter e or who is not having training. Arrange the output on stype in alphabetic order. [6 Marks] 8. Assume that a table named PROJECT is already existed with the following rows. (5 Marks] ProjectID 10 20 30 PROJECT ProjectName Ground Water Clean&Green Constructions a) Add a new column. Size Column Name Location Data Type Varchar Constraint-Column Level Should not be empty 20 b) Modify the project name with Clean and Green for the project_id 20. c) Delete the constraint of Location column. 9. Assume that the table 'PROJECT' is available in user125. [4 Marks] a) Give the permissions to allow the user user995 to modify the data in table PROJECT. b) Create a save point with the name of SP1. c) Write a query to modify ProjectName of projectID 30 to Others by user user 995. d) Cancel all the transactions up to save point SP1. e) Cancel all the privileges given to user user995
