MySQL Database Management Fundamentals

Introduction

In this challenge, we will be performing DDL-related operations on a database. We need to create tables, modify their structures, and perform actions like deleting data and dropping tables. The challenge aims to test your knowledge and skills in using DDL statements such as CREATE, ALTER, DELETE, TRUNCATE, and DROP.

DDL Comprehensive Challenge

In this challenge, we need to create a database and perform DDL-related operations on it.

Tasks

Complete the following tasks:

1. Access MySQL using the sudo command without any password.
2. Create a database named testddl, with the character set specified as utf8mb4.
3. Create tables named emp and dept in the testddl database with the following structures, and insert information:

• emp

  Field Name	Attribute
  eid	INT(5) Primary Key
  ename	VARCHAR(50)
  sal	DECIMAL
  deptno	INT(5)

• dept

  Field Name	Attribute
  did	INT(5) Primary Key
  dname	VARCHAR(50)

4. Modify the primary keys of the emp and dept tables to be auto-incremented, starting from 10. Also, modify the data type of the sal field in the emp table to integer.
5. Use DELETE to delete all data in the emp table.
6. Use TRUNCATE to delete all data in the dept table.
7. Use DROP to delete the emp table and the testddl database.

Requirements

1. Save the SQL statements in script form, with the file name as ddl_perations.sql.
2. Ensure that the statements are executed in the correct order and separated by semicolons and line breaks.
3. The script file must be saved in the ~/project directory.

Examples

After writing the script, you can use the following example code in the MySQL client to run it:

mysql> source ~/project/ddl_perations.sql

Verify the completion of the tasks by checking the database and table structures.

SHOW DATABASES;
/* Expected output includes:
testddl
*/

USE testddl;
DESCRIBE emp;
/* Expected:
Field   Type        Null    Key     Default    Extra
eid     int(5)      NO     PRI     NULL       auto_increment
ename   varchar(50) YES            NULL
sal     int(5)      YES            NULL
deptno  int(5)      YES            NULL
*/

INSERT INTO emp(ename) VALUES('test');
SELECT eid FROM emp;
/* Expected:
eid
10
*/

SHOW DATABASES;
/* Expected: testddl not present */

Summary

Congratulations! You have completed the DDL Comprehensive Challenge challenge. You can practice more labs in LabEx to improve your skills.