- A Consider The Schema In Fig 2 For Writing The Sql Mentioned In I To Vi I Write The Ddl Commands For The Existing Ta 1 (80.34 KiB) Viewed 12 times
a) Consider the schema in Fig. 2 for writing the SQL mentioned in i to vi: i. Write the DDL commands for the existing tables of the given schema: - Add a new column in the 'Project' table named 'Project_details' with varchar (500) data type. Then, change the data type of 'Project_details' to varchar (1000). - In the 'Department' table add two new constraints to the column Dept_name so that it should be unique and not null. ii. Show the Dept_Id wise total no of employees and average salary from the 'Employee' table. Only consider those departments that have the total no of employees greater than 5 . iii. From the 'Department' table, show Dept_name and Location group name. Group the location based on the following criteria: Fig. 2: Schema Diagram for Question 3(a) iv. From the 'Project' table, show the project details of the projects that have the pattern "IT" in Project._name with minimum 6 characters in the name. Consider the projects whose Project_Id is an even number. v. Use join operation: Show employee full name, salary, and project name by the descending order of salary from 'Employee' and 'Project' table for all the employees (even if they have not been assigned in any project). vi. Use subquery: Show the first_name, salary, Dept_Id of the employees from the 'Employee' table. Include the employees with the maximum salary of 50000 and the minimum salary of 15000 .