<=Back
<=Exam Prediction paper: Diploma in ICT module 2 Database Management System
f) A database called WorksOn has the following relations:
emp (eno, ename, bdate, title, salary, dno)
proj (pno, pname, budget, dno)
dept (dno, dname, mgreno)
workson (eno, pno, resp, hours)
i) Write an SQL query that returns the project number and name for projects with a
budget greater than $100,000.
ii) Write an SQL query that returns all works on records where hours worked is less than
10 and the responsibility is 'Manager'.
iii) Write an SQL query that returns the employees (number and name only) who have a
title of 'EE' or 'SA' and make more than $35,000. (1 mark)
iv) Write an SQL query that returns the employees (name only) in department 'D1'
ordered by decreasing salary. (1 mark)
v) Write an SQL query that returns the departments (all fields) ordered by ascending
department name. (0.5 marks)
vi) Write an SQL query that returns the employee name, department name, and employee
title. (0.5 marks)
vii) Write an SQL query that returns the project name, hours worked, and project number
for all works on records where hours > 10.
viii) Write an SQL query that returns the project name, department name, and budget for
all projects with a budget < $50,000.
ix) Write an SQL query that returns the employee numbers and salaries of all employees
in the 'Consulting' department ordered by descending salary.
x)Write an SQL query that returns the employee name, project name, employee title,
and hours for all works on records.
emp (eno, ename, bdate, title, salary, dno)
proj (pno, pname, budget, dno)
dept (dno, dname, mgreno)
workson (eno, pno, resp, hours)
i) Write an SQL query that returns the project number and name for projects with a
budget greater than $100,000.
ii) Write an SQL query that returns all works on records where hours worked is less than
10 and the responsibility is 'Manager'.
iii) Write an SQL query that returns the employees (number and name only) who have a
title of 'EE' or 'SA' and make more than $35,000. (1 mark)
iv) Write an SQL query that returns the employees (name only) in department 'D1'
ordered by decreasing salary. (1 mark)
v) Write an SQL query that returns the departments (all fields) ordered by ascending
department name. (0.5 marks)
vi) Write an SQL query that returns the employee name, department name, and employee
title. (0.5 marks)
vii) Write an SQL query that returns the project name, hours worked, and project number
for all works on records where hours > 10.
viii) Write an SQL query that returns the project name, department name, and budget for
all projects with a budget < $50,000.
ix) Write an SQL query that returns the employee numbers and salaries of all employees
in the 'Consulting' department ordered by descending salary.
x)Write an SQL query that returns the employee name, project name, employee title,
and hours for all works on records.
Viewed: 72 times
For Better experience and learning,for video answers, after video ends playing, click next question in the questions list!!!
Share in:
Questions List:
1. Define the term database management system
2. Outline two reasons for considering database security
3. Outline three types of end users in a database environment
4. With the aid of a diagram, outline the stages of the database design cycle
5. Explain two circumstances under which an organization would implement a centralized database system
6. f) Susan a database administrator is working on a table named Employee with the following attributes: Employee (employeeNumber, DepartmentNumber, hours, EmployeeNumber, DepartmentName, DepartmentLocation) i) State three functional dependencies that can be derived from the table ii) Outline three challenges she would encounter when working with data on the table
7. With the aid of appropriate symbol, explain each of the following components of an Entity Relationship Diagram(ERD). (i) entity; (ii) relationship; (iii) Attribute
8. Differentiate between super key and candidate key as used in database management system
9. Define each of the following terms as used in Entity-Relationship Modelling: (4 marks) (i) Specialization; (ii) Aggregation
10. Outline three characteristics of the 1NF
11. Table 1 shows information contained in a database table. Use it to answer the questions that follow Normalize the table up to the second normal form (2NF
12. write the SQL statements equivalents to the following algebraic expression
13. Explain the data type varchar as used in the structure of the table
14. a) Table 3 shows a table named employee in a database used by employer to manage employees’ salaries. Use it to answer the questions that follows Write SQL statements that would: (8 marks). (i) Display the salary of all employees whose grade values is D (ii) Change Alex’s salary to 30000. (iii) Display all employees with Grade A and salaries with values between 10000 and 15000. (iv) Display the m
15. b) Table 4 shows a database table named students. Use it to answer the questions that follows: Write a relational calculus statement that would; (i) Display the records for the field SID, SNAME and LNAME of all students. (ii) Display the records for the field SNAME, LNAME and MARK for all students with MARKS values more than 60. (3 marks) (iii) Display the MARKS values for a student
16. d) Table 5 is a table named results in a database. Use it to answer the questions that follows: Write SQL statements that would perform each of the following: (i) Display all records from the fields FirstName, SirName and IDNO; (ii) Display the DOB value for John Wallace; (iii) Display all records whose marks range from 50 to 90
17. A database has a table named employees. Use it to answer the questions below Write an SQL statement that would: (i) Count all the employees in DeptNo 02 (ii) Calculate the average salary of all employees (iii) Calculate the total salary for all employees (iv) Display all records for employees whose salary is less or equal to the average salary of all employees
18. f) A database called WorksOn has the following relations: emp (eno, ename, bdate, title, salary, dno) proj (pno, pname, budget, dno) dept (dno, dname, mgreno) workson (eno, pno, resp, hours) i) Write an SQL query that returns the project number and name for projects with a budget greater than $100,000. (1 mark) ii) Write an SQL query that returns all works on records where hours worked is less tha
19. Explain the term database organization as used in databases
1. Define the term database management system
2. Outline two reasons for considering database security
3. Outline three types of end users in a database environment
4. With the aid of a diagram, outline the stages of the database design cycle
5. Explain two circumstances under which an organization would implement a centralized database system
6. f) Susan a database administrator is working on a table named Employee with the following attributes: Employee (employeeNumber, DepartmentNumber, hours, EmployeeNumber, DepartmentName, DepartmentLocation) i) State three functional dependencies that can be derived from the table ii) Outline three challenges she would encounter when working with data on the table
7. With the aid of appropriate symbol, explain each of the following components of an Entity Relationship Diagram(ERD). (i) entity; (ii) relationship; (iii) Attribute
8. Differentiate between super key and candidate key as used in database management system
9. Define each of the following terms as used in Entity-Relationship Modelling: (4 marks) (i) Specialization; (ii) Aggregation
10. Outline three characteristics of the 1NF
11. Table 1 shows information contained in a database table. Use it to answer the questions that follow Normalize the table up to the second normal form (2NF
12. write the SQL statements equivalents to the following algebraic expression
13. Explain the data type varchar as used in the structure of the table
14. a) Table 3 shows a table named employee in a database used by employer to manage employees’ salaries. Use it to answer the questions that follows Write SQL statements that would: (8 marks). (i) Display the salary of all employees whose grade values is D (ii) Change Alex’s salary to 30000. (iii) Display all employees with Grade A and salaries with values between 10000 and 15000. (iv) Display the m
15. b) Table 4 shows a database table named students. Use it to answer the questions that follows: Write a relational calculus statement that would; (i) Display the records for the field SID, SNAME and LNAME of all students. (ii) Display the records for the field SNAME, LNAME and MARK for all students with MARKS values more than 60. (3 marks) (iii) Display the MARKS values for a student
16. d) Table 5 is a table named results in a database. Use it to answer the questions that follows: Write SQL statements that would perform each of the following: (i) Display all records from the fields FirstName, SirName and IDNO; (ii) Display the DOB value for John Wallace; (iii) Display all records whose marks range from 50 to 90
17. A database has a table named employees. Use it to answer the questions below Write an SQL statement that would: (i) Count all the employees in DeptNo 02 (ii) Calculate the average salary of all employees (iii) Calculate the total salary for all employees (iv) Display all records for employees whose salary is less or equal to the average salary of all employees
18. f) A database called WorksOn has the following relations: emp (eno, ename, bdate, title, salary, dno) proj (pno, pname, budget, dno) dept (dno, dname, mgreno) workson (eno, pno, resp, hours) i) Write an SQL query that returns the project number and name for projects with a budget greater than $100,000. (1 mark) ii) Write an SQL query that returns all works on records where hours worked is less tha
19. Explain the term database organization as used in databases
