<=Back
<=KNEC Diploma in ICT module 2 Database Management System past paper: March 2023
Table 1 shows a relation named workers in a database. Use it to answer the questions that follow:
Write a Structured Query Language statement that:
i) Was used to create the workers relation having the field Id with unique values: (2 marks)
ii) Would change all the values to 20000 in the salary column for the workers in the support department: (2 marks)
iii) Would delete the record for the worker named Mebe: (2 marks)
iv) Would display the columns Name and Department for the workers whose age is between 25 and 30, sorted in descending order by Department: (2 marks)
v) Would display the columns Department and the sum of salary for each department displayed as TOTAL SALARY: (2 marks)
Write a Structured Query Language statement that:
i) Was used to create the workers relation having the field Id with unique values: (2 marks)
ii) Would change all the values to 20000 in the salary column for the workers in the support department: (2 marks)
iii) Would delete the record for the worker named Mebe: (2 marks)
iv) Would display the columns Name and Department for the workers whose age is between 25 and 30, sorted in descending order by Department: (2 marks)
v) Would display the columns Department and the sum of salary for each department displayed as TOTAL SALARY: (2 marks)
Viewed: 105 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. Outline four transaction control language (TCL) commands used in Structured Query Language (SQL). (4 marks)
2. Explain the function of each of the following in a database: (4 marks)
3. Table 1 shows a relation named workers in a database. Use it to answer the questions that follow:
4. Explain each of the following terms as used in database management systems: (4 marks) (i) Indexes (ii) Constraints
5. The following are tables named First and Second in a database. Use it to answer the questions that follow:
6. Outline a circumstance under which each of the following SQL aggregate functions would be used in SQL: (3 marks) (i) AVG() (ii) COUNT() (iii) LAST()
7. Explain a circumstance that would necessitate the use of each of the following data types in SQL: (i) Char (ii) Varchar
8. Distinguish between TRUNCATE and DROP commands as used in SQL. (4 marks)
9. Explain the function of each of the following users in a database management system environment
10. The following are relations named Table2 and Table3. Use them to answer the questions that follow
11. Explain the meaning of a subquery as used in SQL.
12. Explain a circumstance when subqueries are used in SQL.
13. Distinguish between data manipulation language (DML) and data query language (DQL) as used in database management systems.
14. Write the algebraic expression that would perform each of the following in a database: (i) Display vehicles whose brand is Toyota and price is less than Ksh1,000,000 from vehicles table: (ii) Display brand and color of vehicles for all vehicles whose brand is Toyota: (iii) Display all the Toyota brand vehicles whose drive mode is 4WD
15. Outline the function of each of the following logical operators in SQL: i. EXISTS ii. AND iii. IS NULL
16. Explain the term identity column as used in SQL.
17. i) Explain the term data warehousing as used in databases. ii) Explain the application of data warehousing in each of the following areas
18. The following relational schema is obtained from a database used by a lecturer to manage scores in tests for different units. Student(sid: integer, sname: string) Unit(uid: integer, uname: string) Test(sid: integer, uid: integer, cat: integer, endterm: integer) Write the SQL code that would: (i) Create the Test table having relevant fields: (2 marks)
19. Explain each of the following terms as used in database management systems: i. Trigger ii. Cardinality
20. Jane, a database administrator in a company, has been tasked to consider migrating data from an old DBMS to a new one installed in the premises. i) Outline four factors that she should consider while planning and executing data migration. (4 marks) ii) Outline four reasons for preparing a plan during the database migration. (4 marks)
21. Explain the requirement for each of the following conditions to occur in a database (i) A table to be in Third Normal Form (3NF) (ii) Referential Integrity (2 marks)
22. A database designer intends to restrict some users from accessing certain portions of data in a database system. Explain two methods that would be used by the designer to achieve this goal. (4 marks)
23. Outline the importance of each of the following transaction properties in a database
24. Outline the function of each of the following logical operators used in SQL
25. Distinguish between conceptual schema and external schema views in a database management system. (4 marks)
26. Table 4 shows an extract of an assessment schedule for students in a college. Normalize the table to 3NF, showing the steps. (10 marks)
27. Explain the term database management systems.
28. Outline four advantages of database management systems.
29. Distinguish between each of the following pairs of terms as used in database management systems
30. With the aid of a diagram in each case, describe the following database models
1. Outline four transaction control language (TCL) commands used in Structured Query Language (SQL). (4 marks)
2. Explain the function of each of the following in a database: (4 marks)
3. Table 1 shows a relation named workers in a database. Use it to answer the questions that follow:
4. Explain each of the following terms as used in database management systems: (4 marks) (i) Indexes (ii) Constraints
5. The following are tables named First and Second in a database. Use it to answer the questions that follow:
6. Outline a circumstance under which each of the following SQL aggregate functions would be used in SQL: (3 marks) (i) AVG() (ii) COUNT() (iii) LAST()
7. Explain a circumstance that would necessitate the use of each of the following data types in SQL: (i) Char (ii) Varchar
8. Distinguish between TRUNCATE and DROP commands as used in SQL. (4 marks)
9. Explain the function of each of the following users in a database management system environment
10. The following are relations named Table2 and Table3. Use them to answer the questions that follow
11. Explain the meaning of a subquery as used in SQL.
12. Explain a circumstance when subqueries are used in SQL.
13. Distinguish between data manipulation language (DML) and data query language (DQL) as used in database management systems.
14. Write the algebraic expression that would perform each of the following in a database: (i) Display vehicles whose brand is Toyota and price is less than Ksh1,000,000 from vehicles table: (ii) Display brand and color of vehicles for all vehicles whose brand is Toyota: (iii) Display all the Toyota brand vehicles whose drive mode is 4WD
15. Outline the function of each of the following logical operators in SQL: i. EXISTS ii. AND iii. IS NULL
16. Explain the term identity column as used in SQL.
17. i) Explain the term data warehousing as used in databases. ii) Explain the application of data warehousing in each of the following areas
18. The following relational schema is obtained from a database used by a lecturer to manage scores in tests for different units. Student(sid: integer, sname: string) Unit(uid: integer, uname: string) Test(sid: integer, uid: integer, cat: integer, endterm: integer) Write the SQL code that would: (i) Create the Test table having relevant fields: (2 marks)
19. Explain each of the following terms as used in database management systems: i. Trigger ii. Cardinality
20. Jane, a database administrator in a company, has been tasked to consider migrating data from an old DBMS to a new one installed in the premises. i) Outline four factors that she should consider while planning and executing data migration. (4 marks) ii) Outline four reasons for preparing a plan during the database migration. (4 marks)
21. Explain the requirement for each of the following conditions to occur in a database (i) A table to be in Third Normal Form (3NF) (ii) Referential Integrity (2 marks)
22. A database designer intends to restrict some users from accessing certain portions of data in a database system. Explain two methods that would be used by the designer to achieve this goal. (4 marks)
23. Outline the importance of each of the following transaction properties in a database
24. Outline the function of each of the following logical operators used in SQL
25. Distinguish between conceptual schema and external schema views in a database management system. (4 marks)
26. Table 4 shows an extract of an assessment schedule for students in a college. Normalize the table to 3NF, showing the steps. (10 marks)
27. Explain the term database management systems.
28. Outline four advantages of database management systems.
29. Distinguish between each of the following pairs of terms as used in database management systems
30. With the aid of a diagram in each case, describe the following database models
