8.3 Data Definition Language (DDL) and Data Manipulation Language (DML) (3)

Resources | Revision Questions | Computer Science

Login to see all questions

Click on a question to view the answer

1.

Question 3

A company wants to track its employees and their departments. Design a database table called Employees. The table should include the following columns:

  • EmployeeID: An integer, primary key, and auto-incrementing.
  • FirstName: A string, with a maximum length of 50 characters.
  • LastName: A string, with a maximum length of 50 characters.
  • DepartmentID: An integer, foreign key referencing a hypothetical Departments table (which is not required to be created in this question).
  • Salary: A decimal number, representing the employee's salary.

Write the SQL DDL statement to create the Employees table.

2.

Question 3: Consider a scenario where a database application needs to be migrated from one DBMS (e.g., MySQL) to another (e.g., PostgreSQL). Explain how the SQL standard helps in this migration process. What potential challenges might arise during a SQL migration, and how can these be addressed?

3.

Consider a scenario where a user issues a complex query to a DBMS that involves multiple tables and requires significant processing. Describe how the DBMS utilizes its internal mechanisms, including DML, to execute this query efficiently and maintain data integrity throughout the process. Include a discussion of potential performance considerations.