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:
Notes
1. Selecting Data
,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:
Example :
Example of selecting data from a "Customers" table
SELECT
FirstName, LastName,Email
FROM
Customers;
In this example, the SELECT statement retrieves the first name, last name, and email address of all customers in the "Customers" table.
2. Filtering Data
To narrow down the results, you can use the WHERE clause to specify conditions that must be met.
Example :
Example of filtering data with a WHERE clause
SELECT
ProductName,
Price,
FROM
Products
WHERE
Price > 50;
This query retrieves the names and prices of products with prices greater than 50.
3. Sorting Data
To arrange the results in a specific order, you can use the ORDER BY clause. For instance:
Example :
Example :
Example of filtering data with a WHERE clause
SELECT
ProductName,
Price,
FROM
Products
ORDER BY
Price DESC;
In this query, products are selected and sorted in descending order of price.
This query retrieves the names and prices of products with prices greater than 50.
4. Real-world Querying Scenarios
E-commerce System:
- Selecting Data: Retrieve a customer's order history to display their past purchases.
- Filtering Data: Show products within a certain price range to help customers find affordable options.
- Sorting Data: Display search results by relevance, price, or customer rating.
Inventory Management System:
- Selecting Data: Retrieve a list of products that need restocking based on quantity.
- Filtering Data: Find products with expired shelf lives or low stock levels.
- Sorting Data: List products by category or supplier for better organization.
5. Key Takeaways
- DQL statements in SQL Server, particularly SELECT, are used to retrieve data from tables.
- The WHERE clause allows you to filter data based on specified conditions.
- The ORDER BY clause lets you sort the results in ascending or descending order.
6. Exercise Questions
1. Select all columns from a "Students" table where the GPA is greater than 3.0.
2. Retrieve the first name, last name, and salary of all employees in the "Employees" table who earn more than $50,000.
3. List the product names and their corresponding quantities in the "Inventory" table, but only for products with quantities less than 10.
4. Select the names and release dates of movies from a "Movies" table, sorted in ascending order of release date.
5. Retrieve the order IDs, customer IDs, and order dates of orders placed in the last month.
6. Find all books from a "Library" table that have "SQL" in their title and sort them by title alphabetically.
Choosing the right data types is crucial for efficient database design in Microsoft SQL Server. Data types define the kind of data a column can hold and impact storage requirements, query performance, and data integrity.