SQL SERVER Eng Part1

1. SQL Server Installation

• Steps to install SQL Server:
  1. Download and install SQL Server Developer Edition or Express Edition from Microsoft.
  2. Configure SQL Server setup (choose authentication mode).
  3. Install SQL Server Management Studio (SSMS) to interact with the database.
  4. Connect to the SQL Server instance using SSMS.

 SSMS Installation (SQL Server Management Studio)

• SSMS is used to manage SQL Server databases visually.
• Download from Microsoft’s website and install it.
• Use it to create, modify, and execute SQL queries easily.

2. SELECT Query

• Used to retrieve data from one or more tables.
• Syntax: SELECT column1, column2 FROM table_name;
• Can retrieve all columns using SELECT * FROM table_name;
• Can use aliases with AS for better readability: SELECT column1 AS NewName FROM table_name;

3. ORDER BY Clause

• Used to sort query results in ascending (ASC, default) or descending (DESC) order.
• Example: SELECT * FROM Employees ORDER BY Salary DESC;
• Can sort by multiple columns: ORDER BY Department ASC, Salary DESC;
• NULL values usually appear first in ascending order, last in descending.

4. WHERE Clause

• Filters records based on conditions.
• Operators: =, !=, <, >, <=, >=, BETWEEN, LIKE, IN, NOT IN
• Example: SELECT * FROM Employees WHERE Salary > 50000;
• Used with AND, OR, and NOT for multiple conditions.

5. HAVING Clause

• Used to filter aggregated results from GROUP BY.
• Difference from WHERE: WHERE filters individual rows, HAVING filters grouped results.

6. GROUP BY Clause

• Groups rows sharing the same values into summary rows.
• Must be used with aggregate functions like COUNT(), SUM(), AVG().
• Example: SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

7. Aggregation Calculations

• Functions used to summarize data:
  • SUM(), AVG(), COUNT(), MIN(), MAX()

8. OVER Clause with Partition

• Used for window functions to calculate rankings, running totals, etc., without collapsing rows.

9. RANK() and DENSE_RANK() Functions

• RANK(): Assigns ranks but skips numbers for ties.
• DENSE_RANK(): Assigns continuous ranking numbers without skipping for ties.

10. Subqueries

• A query inside another query, used in WHERE, SELECT, FROM.

11. Wildcards for Specific Patterns (LIKE Operator)

• Used for pattern matching with LIKE.
• %  → Matches any sequence of characters.
• _  → Matches a single character.
• [A-Z] → Matches specific character ranges.

12. AS Keyword for New Column (Aliasing)

• Renames columns or tables temporarily in queries.

13. OFFSET and FETCH NEXT (Pagination)

• Used to skip records and fetch a specific number of rows.
• Example (Fetch 5 rows after skipping the first 10):

14. IN and NOT IN Operators