Quick SQL Refresher

Introduction

Many students or even experience people ask for quick read or you can say it cheatlist specially for SQL. As it is the base technology and every interview have questions on SQL. I am trying to collate all important points and concept at one place.

SQL Keys

• Super Key: A set of one or more columns that can uniquely identify a record in a table.
• Candidate Key: A minimal set of columns that can uniquely identify each row in a table.
• Primary Key: A specific candidate key selected to uniquely identify database records, cannot be null.
• Foreign Key: A column or set of columns in one table that references the primary key of another table to make a relationship.
• Unique Key: A column, or set of columns, where all values must be unique across the dataset, can contain nulls.
• Alternate Key: A candidate key that is not chosen as the primary key but is still capable of uniquely identifying each row.
• Composite/Compound Key: A key consisting of two or more columns used together as a unique identifier for a record.

SQL Commands

1. Data Definition Language (DDL) Commands
   • CREATE: Creates a new table or view or other objects.
   • ALTER: Modifies an existing database object, such as a table.
   • DROP: Deletes an entire table or view or other objects.
   • TRUNCATE: Removes all rows from a table.
2. Data Manipulation Language (DML) Commands
   • INSERT: Adds new rows to a table.
   • UPDATE: Modifies existing data within a table.
   • DELETE: Removes rows from a table
3. Data Query Language (DQL) Commands
   • SELECT: Retrieves certain records from one or more tables
4. Data Control Language (DCL) Commands
   • GRANT: Gives users access privileges to the database.
   • REVOKE: Removes access privileges given to the user.
5. Transaction Control Language (TCL) Commands
   • COMMIT: Saves all changes made in the current transaction.
   • ROLLBACK: Undoes all changes made in the current transaction.
   • SAVEPOINT: Sets a point within a transaction to which you can later roll back.

SQL Constraints

1. Learn what constraints are and why they are important.
2. Understand their role in maintaining data integrity.
3. Explore some real-life examples how constraints can be implemented.
4. SQL Constraints are as:
   • NOT NULL: Ensures that a column cannot have a NULL value.
   • UNIQUE: Guarantees that all values in a column are different.
   • PRIMARY KEY: A combination of NOT NULL and UNIQUE, uniquely identifies each row in a table.
   • FOREIGN KEY: Ensures referential integrity by linking a column or a group of columns to a PRIMARY KEY in another table.
   • CHECK: Enforces a specific rule on each row in a table, ensuring that certain conditions are true or false.
   • DEFAULT: Set a default value for a column when no value is given to that column

SQL Clauses/Queries

1. Practice simple queries focusing on specific clauses.
2. Here are the SQL Clauses:
   • SELECT: Specifies the columns to be retrieved from the database.
   • FROM: Indicates the table(s) from which data is to be selected.
   • WHERE: Filters the rows returned by the query based on specified conditions.
   • GROUP BY: Groups rows sharing a property so that aggregate functions can be applied to each group.
   • HAVING: Filters groups defined by the GROUP BY clause based on specified conditions.
   • ORDER BY: Sorts the results of a query in ascending or descending order based on specified columns.
3. Understand SQL Clauses Execution Order as they are executed in a specific order within a query.

T-SQL Fundamentals

Follow below points to navigate T-SQL fundamentals:

• Data Types: Learn data types- int, varchar, datetime, float, etc.
• Variables: Understand how to declare and assign variables, as well as scope and lifetime of variables.
• Operators: Understand comparison and logical operators, as well as ‘+’ for string concatenation.
• Conditional Statements: Implement if statements, Case statements, NULL Handling as well as nested conditions.

SQL Joins

• Recognize scenarios where joins are necessary and visualize the relationships between tables.
• INNER JOIN: Returns rows from different tables based on a related column and returns only matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table.
• FULL JOIN (FULL OUTER JOIN): Combines LEFT and RIGHT JOINs, returning all rows from both tables.
• CROSS JOIN: Produces a Cartesian product of rows from the joined tables, with all possible combinations of rows.
• SELF JOIN: Joins a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement.

SQL Functions

• Used to perform calculations on data, manipulate string, numerical, or date data.
• Returns single value & customize the output of queries.

1. Aggregate Functions: Operate on a set of values.
   • COUNT(): Counts the number of rows.
   • SUM(): Calculates the sum of values.
   • AVG(): Calculates the average of values.
   • MIN(): Retrieves the minimum value.
   • MAX(): Retrieves the maximum value.
2. Scalar Functions: Operate on a single value
   • UPPER(): Converts a string to uppercase.
   • LOWER(): Converts a string to lowercase.
   • LEN() or LENGTH(): Returns the length of a string.
   • CONCAT(): Concatenates two or more strings.
   • SUBSTRING(): Extracts a portion of a string.
3. User-defined Functions: Created by users to perform specific operations, not available using built-in SQL functions.

SQL Exception Handling

• Recognize that exceptions are errors or unexpected events that can occur during the execution of SQL code.
• Understand various types of exceptions, including syntax errors, runtime errors, and logical errors.
• Learn to Implement TRY..CATCH Block to handle the exceptions.
• Use error functions to retrieve information about the exceptions occured.
• Familiarize yourself with some common error functions:
  • ERROR_MESSAGE(): Returns the error message text.
  • ERROR_NUMBER(): Returns the error number.
  • ERROR_SEVERITY(): Returns the severity of the error.

SQL Indexes

1. Improve the speed of data retrieval operations on a database table by providing quick access to rows based on the index keys.
2. Identify scenarios where using indexes is beneficial.
3. Familiarize yourself with types of SQL indexes:
   • Clustered Index: Organizes the actual data rows within a table in a specific, sorted order based on the index key.
   • Non-Clustered Index: Creates a separate structure holding a sorted list of key values, along with pointers to the corresponding rows in the table.
4. Regularly revisit and refine your indexing strategy based on evolving database requirements and usage

SQL Views

1. A virtual table created by a query that selects data from one or more tables, providing a customizable and reusable snapshot of data.
2. Create simple views to retrieve columns from one or more tables.
3. Understand how to update data through views.
4. Be aware of restrictions on updating views based on complex queries.
5. Use ALTER VIEW statement to modify existing views.
6. Understand how changes in tables affect views.
7. Use DROP VIEW to remove unnecessary views.
8. Be cautious about dependencies before dropping views.

SQL Stored Procedures

Know various types of stored procedures in SQL Server:

1. System Defined Stored Procedure: Document the available system procedures, their purposes, and any specific requirements for their usage.
2. User-defined Stored Procedure: Identify the business requirements that can be fulfilled using user-defined stored procedures.
3. CLR Stored Procedure: Learn about CLR integration and how it allows you to use .NET languages to create stored procedures.
4. Extended Stored Procedure: Understand the concept of extended stored procedures, which are specific to Microsoft SQL Server

SQL Transaction Control

Familiarize yourself with the concept of transactions, the ACID properties (Atomicity, Consistency, Isolation, Durability), and how they ensure data integrity.

1. BEGIN TRANSACTION Command: Understand how to initiate a transaction using the BEGIN TRANSACTION command.
2. SET TRANSACTION Command: Learn this to configure properties like isolation level and other transaction-specific settings.
3. COMMIT Command: Understand the purpose and usage of COMMIT command to make the changes within a transaction permanent.
4. ROLLBACK Command: Learn to undo changes made within a transaction.
5. SAVEPOINT Command: Understand how to set intermediate points within a transaction.

SQL Triggers

• A special kind of stored procedure that automatically executes in response to certain events on a particular table or view, like insertions, updates, or deletions.
• There are following types of SQL Server Triggers:
  • Data Manipulation Language (DML) Triggers: Understand the role of DML triggers in responding to data manipulation events (INSERT, UPDATE, DELETE)
  • Data Definition Language (DDL) Triggers: Understand the purpose of DDL triggers in responding to changes in the database structure (CREATE, ALTER, DROP statements).
  • Logon Triggers: Understand the purpose of logon triggers, which execute in response to a LOGON event on the server

SQL Cursors

• A cursor is a database object that retrieves and manipulates rows returned by a query, one row at a time, allowing for row-by-row processing in SQL.
• Explore the types of cursors as listed below:
  • STATIC Cursors: Take a snapshot of the data at the time of creation, unaffected by the changes in the data.
  • FAST_FORWARD Cursors: Optimized, forward-only, read-only cursors, do not allow backward navigation.
  • DYNAMIC Cursors: Reflect real-time changes in the data, showing all changes made by other transactions as you navigate through them.
• Explore cursor operations: OPEN, FETCH and CLOSE.