Skip to main content

DDL (Data Definition Language)

SQL Data Definition Language (DDL) is used to define, modify, and manage database objects such as tables, indexes, and constraints. It includes commands like CREATE, ALTER, and DROP. DDL statements define the structure and properties of database objects, including their data types, constraints, and relationships. DDL commands are used to create new objects, modify existing ones, or remove them from the database schema. DDL statements are essential for database schema design and management, enabling users to define the organization and characteristics of data stored in a database.

CREATE

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, hire_date DATE );

In this example:

  • CREATE TABLE is the DDL command used to create a new table named employees.
  • employee_id, first_name, last_name, department_id, and hire_date are columns within the table.
  • INT specifies the integer data type for employee_id and department_id.
  • VARCHAR(50) specifies variable-length character strings for first_name and last_name with a maximum length of 50 characters.
  • DATE specifies the date data type for hire_date.
  • PRIMARY KEY constraint is applied to employee_id, making it unique for each row and ensuring it acts as the primary identifier for each employee.

 

ALTER

-- Adding a new column

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

 -- Modifying column data type

ALTER TABLE employees ALTER COLUMN department_id INT;

-- Adding a primary key constraint

ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

 -- Adding a foreign key constraint

ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Dropping a column

ALTER TABLE employees DROP COLUMN hire_date;

In this example:

  • ADD COLUMN is used to add a new column named email to the employees table.
  • ALTER COLUMN is used to modify the data type of the department_id column.
  • ADD CONSTRAINT is used to add primary key and foreign key constraints to the table.
  • DROP COLUMN is used to remove the hire_date column from the employees table.

 

DROP

DROP TABLE employees;

In this example:

  • DROP TABLE is the DDL command used to delete the table named employees.
  • Once executed, all data stored within the employees table will be permanently deleted, and the table structure will be removed from the database.

 

Comments

Popular posts from this blog

Performance Optimization

Performance optimization in SQL is crucial for ensuring that your database queries run efficiently, especially as the size and complexity of your data grow. Here are several strategies and techniques to optimize SQL performance: Indexing Create Indexes : Primary Key and Unique Indexes : These are automatically indexed. Ensure that your tables have primary keys and unique constraints where applicable. Foreign Keys : Index foreign key columns to speed up join operations. Composite Indexes : Use these when queries filter on multiple columns. The order of columns in the index should match the order in the query conditions. Avoid Over-Indexing:  Too many indexes can slow down write operations (INSERT, UPDATE, DELETE). Only index columns that are frequently used in WHERE clauses, JOIN conditions, and as sorting keys. Query Optimization Use SELECT Statements Efficiently : SELECT Only Necessary Columns : Avoid using SELECT * ; specify only ...

DAX UPPER Function

The DAX UPPER function in Power BI is used to convert all characters in a text string to uppercase. This function is useful for standardizing text data, ensuring consistency in text values, and performing case-insensitive comparisons. Syntax: UPPER(<text>) <text>: The text string that you want to convert to uppercase. Purpose: The UPPER function helps ensure that text data is consistently formatted in uppercase. This can be essential for tasks like data cleaning, preparing text for comparisons, and ensuring uniformity in text-based fields. E xample: Suppose you have a table named "Customers" with a column "Name" that contains names in mixed case. You want to create a new column that shows all names in uppercase. UppercaseName = UPPER(Customers[Name]) Example Scenario: Assume you have the following "Customers" table: You can use the UPPER function as follows: Using the UPPER function, you can convert all names to uppercase: UppercaseName = ...

TechUplift: Elevating Your Expertise in Every Click

  Unlock the potential of data with SQL Fundamental: Master querying, managing, and manipulating databases effortlessly. Empower your database mastery with PL/SQL: Unleash the full potential of Oracle databases through advanced programming and optimization. Unlock the Potential of Programming for Innovation and Efficiency.  Transform raw data into actionable insights effortlessly. Empower Your Data Strategy with Power Dataware: Unleash the Potential of Data for Strategic Insights and Decision Making.