Question: Database is:emp (eno, ename, bdate, title, salary, dno) proj (pno, pname, budget, dno) dept (dno, dname, mgreno) workson (eno, pno, resp, hours) a) Write an SQL query that returns the project number and name for projects with a budget greater than $100,000.b) Write an SQL query that returns all works on records where hours worked is less than 10 and the

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: 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.

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Figure 3.7 Referential integrity constraints displayed on the COMPANY …

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.

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.

• The column Dno in Department is the primary key. • The column Pnumber in Department is the primary key. • Dname in Department shouldn't be repeated. • The relation “Employee" contains a foreign key “Dnumber” That references the relation "Department".

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 …

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.