EMP (Name, SSN, Salary, SuperSSN, Dno) DEPT (DNum, Dname, MgrSSN, Dno) DEPT_LOC (Dnum. Dlocation) DEPENDENT (ESSN, Dep name, Sex) WORKS ON (ESSN, Pno, Hours) PROJECT (Pname, Pnumber, Plocation, Dnum) Write the SQL queries for the following 1) Retrieve the name of the employee who works with same department as "Nick"

Question: SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname="IT AND Dnumber=Dno; Consider the above query i. Draw at least two query trees that can represent the query. Under what circumstances would you use each of your query trees? ii.

Question: 5 b) Consider the following Relational Database. Doctor (dno, dname, address,dcity) Hospital (hno, hname, street,hcity) Dochosp (dno, hno, date) Write the SQL statement to find the following result i. Find hospital names to which 'Dr. Mehbub' has visited. ii.

Question: Create View VDept_Info (Dept_Name, No_of_Emps, Total_Sal) As Select D.dname, COUNT(E.dno), Sum(E.salary) From Department D LEFT OUTER JOIN Employee E, Where D.dnumber = E.dno Group By D.dname; DEPARTMENT DNAME DNUMBER MGRSSN Headquarters 1 888665555 Administration 4 987654321 Research 5 333445555 Automation …

2) Alter relation with any key constraint / integrity constraint 3) Insert 3 records into tables above 4) Update query on Employee 5) Update MgrStartDate to 22-June 2021 for DNO 3 6) Delete an Employee with SSN=1564 7) Update Works On hours to 5hours for SSN=5463 8) Delete a department with Mgr StartDate before 01-Jan-1996 9) Delete Project ...

Question: 7.9. Consider the following view, DEPT_SUMMARY, defined on the COMPANY database in Figure 5.6: CREATE VIEW DEPT_SUMMARY (D, C, Total_s, Average_s) AS SELECT Dno, COUNT(*), SUM (Salary), AVG (Salary) FROM EMPLOYEE GROUP BY Dno: State which of the following queries and updates would be allowed on the view.

Engineering; Computer Science; Computer Science questions and answers; A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name, age, and relationship to the employee as attributes).

Question 3. Consider the relation EMPLOYEE(SSN, Salary, Age, DNO), where SSN is the social security number and DNO is the department number, and the selection query: SELECT * FROM EMPLOYEE WHERE (AGE = 60 OR AGE = 25) AND (DNO = 5) AND (Salary = 60000 OR Salary = 30000) Consider also the following information of the database system: Clustering Index on …

Submit a .mwb file for each scenario on Canvas. A company database needs to store information about employees (identified by id number, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with chno, chname and chage as attributes).

The following tables form part of a database held in a relational DBMS: Employee(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno) Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) Project(Pname, Pnumber, Plocation, Dnum) Works_on(Essn, Pno, Hours) The underlined attribute(s) in each relational schema is primary key.