Introduction to Chapter Structured Query 8 Language (SQL) | Class 11 Informatics Practices Notes

8.4 SQL FOR DATA DEFINITION

Data Definition Language (DDL) in SQL provides commands to define and modify the structure of database objects such as databases and tables. The CREATE statement is used to create databases and tables. Before creating a database, one should plan the number of tables, their attributes, data types, and constraints. The syntax to create a database is 'CREATE DATABASE databasename;'. After creating a database, it must be selected using the 'USE databasename;' command to make it active for subsequent operations. The 'SHOW TABLES;' command lists all tables in the selected database. To create a table, the CREATE TABLE statement is used with syntax specifying attribute names, data types, and constraints. For example, creating a STUDENT table with attributes RollNumber (INT, PRIMARY KEY), SName (VARCHAR(20), NOT NULL), SDateofBirth (DATE, NOT NULL), and GUID (CHAR(12), FOREIGN KEY). The DESCRIBE or DESC command shows the structure of a table including attribute names, data types, nullability, and keys. The ALTER TABLE statement allows modifying table structure after creation, such as adding or dropping attributes, changing data types, adding constraints like PRIMARY KEY or FOREIGN KEY, and setting default values. The DROP statement is used to permanently delete tables or databases but should be used with caution as it cannot be undone. Understanding these DDL commands is essential for designing and managing database schemas effectively.

📊 Diagram: Figure 8.1: MySQL Shell; Activity 8.3; Tables 8.3, 8.4, 8.5 showing data types and constraints for STUDENT, GUARDIAN, and ATTENDANCE tables

🧪 Activity: Activity 8.3: Use 'SHOW DATABASES;' command to check if StudentAttendance database is listed.

🔗 Connection: This section introduces SQL commands for data definition, leading to the next section on data manipulation using SQL.