Data Definition Language (DDL) statements in SQL Server are used for defining and managing database objects, including tables. Here's how you create tables:
Notes
1. Inserting Data
Data Manipulation Language (DML) statements in SQL Server are used to interact with data within tables. Here's how you can insert data into a table
Example :
Example of inserting data into a "Customers" table
INSERT INTO
Customers (FirstName, LastName, Email)
VALUES
('John', 'Doe', 'john.doe@example.com');
In this example, a new record is added to the "Customers" table with values for the "FirstName," "LastName," and "Email" columns.
2. Updating Data
DML statements also allow you to modify existing data in a table. You can use the UPDATE statement for this purpose:
Example :
Example of updating data in the "Customers" table
UPDATE
Customers
SET
Email = 'johndoe@example.com'
WHERE
CustomerID = 123;
3. Deleting Data
To remove data from a table, you can use the DELETE statement:
Example :
Example of deleting a record from the "Customers" table
DELETE
FROM
Customers
WHERE
CustomerID = 123;
In this example, a customer record with a specific ID is deleted from the "Customers" table.
4. Real-world DML Operationss
E-commerce System:
● Inserting Data : Add new customer records when users create accounts or insert order records when customers make purchases.
● Updating Data: Modify order statuses when orders are processed and shipped.
● Deleting Data: Remove canceled or returned orders from the database.
Inventory Management System:
● Inserting Data : Insert new product records when new products are added to the inventory.
● Updating Data: Update product quantities when stock is received or sold.
● Deleting Data: Delete outdated product listings that are no longer available.
5. Key Takeaways
- DML statements in SQL Server, such as INSERT, UPDATE, and DELETE, are used to manipulate data in tables.
- INSERT INTO is used to add new records to a table.
- UPDATE is used to modify existing data in a table.
- DELETE is used to remove data from a table.
6. Exercise Questions
1. Create a table named "Employees" with columns for employee ID (INT), first name (VARCHAR), last name (VARCHAR), and date of hire (DATE). Define appropriate constraints.
2. Add a new column to the "Products" table called "StockQuantity" to keep track of product stock levels. Set a default value of 0 for this column.
3. Modify the "Customers" table to add a constraint that ensures email addresses are unique.
4. Create a table named "Orders" with columns for order ID (INT), customer ID (INT), order date (DATE), and total amount (DECIMAL). Include primary key and foreign key constraints.
5. In a library database, create a table named "Books" with columns for book ID (INT), title (VARCHAR), author (VARCHAR), and publication date (DATE). Add a constraint to ensure that the title and author together are unique.
Data Query Language (DQL) statements in SQL Server are used to retrieve data from tables. The most common DQL statement is SELECT. Here's how you can select data from a table: