Question What is a database view. Mention few database sql views advantages Answer A view is a virtual table (not part of the physical schema) composed from the result set of a stored query. Views contain dynamic content. This means if you modify the physical tables from which the view is constructed then the data shown in subsequent invocations of the
Question I was asked this question in an interview. How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables Answer one-one relationship is most likely forced by business rules but it does not flow naturally from the data. One can combine both tables into one table without breaking normalization rules. To implement this
All Keyword in SQL Given the following database table: employee(emp_name, emp_salary) write an SQL statement to print the employee name with the highest salary Solution Using the max aggregate function will give us the maximum salary but it does not give us the name of the employee who has the maximum salary. The solution is
Problem You have the following database table: employee (emp_name, emp_age, emp_department) Write an SQL statement to calculate the average age of employees who are older than 30 years old for each department. Do not display the average if the number of employees used in the calculation is less than 3. Solution You need to use
Problem Write code to simulate the SQL statement “Select unique emp_name from employees” Solution Assuming we have an array of employee names we need to write a function that prints the unique employee names in the array. We can use a built in hash table for that matter. Just hash all names then print the
Database functional dependencies example Given the relation LDS (L, D, S) with the following functional dependencies definitions: LDS is decomposed into two relations: LD (L, D) and SL (S, L) which are populated as follows: Inserting the following row violates the functional dependency S, D -> L The question is how to enforce the the
Problem Given the following database tables Employee (SSN, name, location) Assigned (SSN, project_name, duration) Project (project_name, category, budget, location) Write SQL statements to find the following: Project names with a budget smaller than the average budget of projects in a given category let us say “Test Category” Employees who are assigned to all projects with
