Lecture Notes on SQL and SQL Server

Jul 13, 2024

Lecture Notes on SQL and SQL Server

1. Overview of SQL

  • SQL stands for Structured Query Language; essential for handling relational databases.
  • SQL can insert, search, update, delete, and** modify** database records.
  • Widely used SQL commands:
    • DDL (Data Definition Language): CREATE, ALTER, DROP
    • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
    • DCL (Data Control Language): GRANT, REVOKE
    • TCL (Transaction Control Language): COMMIT, ROLLBACK

2. Features of SQL Server

  • Performance: Efficient for high transaction environments.
  • Security: High level of security for data storage.
  • Reliability: Ensures high uptime and stable performance.
  • Data Analytics: Support for Python and R integration for advanced analytics.
  • High Availability: Features like Log Shipping, Mirroring, and Replication.

3. SQL Server Architecture

  • Database Engine: Handles storage, transaction processing, and security.
  • Relational Engine: Processes queries and executes them.
  • Storage Engine: Manages physical storage of data.
  • Protocol Layer: Handles communication with SQL Server clients.

4. SQL Server Installation

  1. Download SQL Server setup from Microsoft website.
  2. Choose between Basic or Custom installation.
  3. Define installation directories and configure Authentication Modes (Windows/ Mixed).
  4. Use SQL Server Management Studio (SSMS) to manage databases.

5. Data Types in SQL Server

  • Numeric Types: INT, SMALLINT, BIGINT, FLOAT, etc.
  • Character Strings: CHAR, VARCHAR, TEXT
  • Date and Time: DATE, TIME, DATETIME, SMALLDATETIME
  • Binary Strings: BINARY, VARBINARY
  • Other Types: BOOLEAN, UUID, XML, JSON

6. SQL Command Categories

Data Definition Language (DDL)

  • CREATE: To create databases and tables.
  • ALTER: To modify existing database objects.
  • DROP: To delete databases and tables.
  • TRUNCATE: To remove all records from a table, keeping the structure.

Data Manipulation Language (DML)

  • INSERT INTO: To add data to tables.
  • UPDATE: To modify existing data.
  • DELETE: To remove data from tables.
  • SELECT: To retrieve data from tables using conditions & joins.

Data Control Language (DCL)

  • GRANT: To give user access.
  • REVOKE: To withdraw user access.

Transaction Control Language (TCL)

  • COMMIT: Save changes.
  • ROLLBACK: Undo changes.
  • SAVEPOINT: Sets a savepoint within a transaction.

7. Constraints and Keys

Constraints

  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that all values in a column are unique.
  • PRIMARY KEY: A combination of NOT NULL and UNIQUE. Uniquely identifies each row in the table.
  • FOREIGN KEY: Ensures referential integrity by enforcing a link between the data in two tables.
  • CHECK: Ensures that all values in a column satisfy specific conditions.
  • DEFAULT: Specifies a default value for a column.

Keys in Database

  • Primary Key: Uniquely identifies each record in a table.
  • Foreign Key: Uniquely identifies a record in another table.
  • Unique Key: Uniquely identifies each record in a table but can accept NULL values.
  • Composite Key: A combination of two or more columns to uniquely identify a record.

8. Normalization

Normalization process to minimize redundancy and dependency by organizing fields and table relations:

  • 1NF: Eliminates repeating groups in individual tables.
  • 2NF: Removes subsets of data that apply to multiple rows of a table and places them in separate tables.
  • 3NF: Removes columns that are not dependent upon the primary key.
  • BCNF: A more rigorous form of 3NF where every determinant must be a candidate key.

9. SQL Functions

  • AGGREGATE FUNCTIONS: SUM, COUNT, AVG, MIN, MAX
  • String Functions: CONCAT, SUBSTRING, LENGTH
  • Date Functions: CURRENT_DATE, DATEADD, DATEDIFF
  • Conversion Functions: CAST, CONVERT

10. Joins in SQL

  • INNER JOIN: Returns records with matching values in both tables.
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table.
  • FULL JOIN: Returns all records when there is a match in either table.

11. Views in SQL

  • View: A virtual table formed as a result of a query on one or more tables.
  • CREATE VIEW: To create a new view.
  • DROP VIEW: To delete a view.

12. Triggers in SQL

  • Trigger: SQL code that runs automatically when certain events occur.
  • BEFORE & AFTER Triggers: Executes before or after an event (INSERT, UPDATE, DELETE).
  • Syntax: CREATE TRIGGER trigger_name ON table_name FOR INSERT/UPDATE/DELETE AS BEGIN ... END

13. Stored Procedures

  • Stored Procedure: A collection of SQL statements which can be executed as a single unit.
  • Syntax: CREATE PROCEDURE procedure_name AS BEGIN ... END
  • EXECUTE: To run a stored procedure.

14. Replication Types

  • Merge Replication: Merges data from multiple servers into a single centralized database.
  • Transactional Replication: Distributes data from publisher to subscribers.
  • Snapshot Replication: Distributes data exactly as it appears at a specific moment.

15. Error Handling in SQL

  • TRY...CATCH: To handle exceptions.
  • THROW: To raise an error and transfer control to the CATCH block.

16. Other Notable Topics

  • Indexing: To improve query performance.
  • SQL Profiling: To analyze and replay trace results for performance tuning.
  • Log Shipping: For high availability and disaster recovery.
  • CDC (Change Data Capture): To capture all changes made to the data.

Sample Interview Questions and Concepts

  1. Differences between SQL Server and MySQL.
  2. Explain SQL Server Agent and its functionalities.
  3. Authentication modes in SQL Server.
  4. Differences between local and global temporary tables.
  5. Explain data quality services in SQL Server.
  6. What is a TCP port on which SQL Server runs? (Answer: 1433)
  7. Explain log shipping and its advantages.
  8. Describe different types of replication in SQL Server.
  9. What are the common performance issues in SQL Server?

Installation Steps

  1. Download SQL Server: From Microsoft website.
  2. Choose Installation Type: Basic or Custom.
  3. Define Directories and Configure Authentication: Select directories and authentication modes.
  4. Install SSMS: Download SQL Server Management Studio.
  5. Connect to Database Engine: Use SSMS to connect and configure.

Practical/Hands-On Examples

  • Creation and Dropping of Tables.
  • Using Constraints: NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT.
  • Inserting and Updating Data: Using INSERT INTO, UPDATE statements.
  • Joins and Sub-Queries: Using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
  • Error Handling and Transactions: Using TRY...CATCH, COMMIT, ROLLBACK.

Tools and Commands

  • SSMS: SQL Server Management Studio for GUI-based interactions.
  • Command-Line Tools: SQLCMD utility for command-line operations.
  • Profiler: SQL Server Profiler for performance analysis.