Concepts
SQL Constraints: SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column-level constraints apply to a column, and table-level constraints apply to the whole table.
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfy a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
KEYs e.g. (Primary Key (PK), Foreign Key (FK), Unique Key, Super Key, Candidate Key, Composite Key, Natural Key, Surrogate Key, Alternate Key, Non-key Attribute) In a relational database (RDBMS), there are several types of keys used to establish relationships and ensure data integrity.
Primary Key (PK): A primary key is a unique identifier for each record (row) in a table. It ensures that no two rows in the table have the same values for the primary key column(s). Primary keys are used to uniquely identify records and enforce data integrity. Each table should have one primary key.
Foreign Key (FK): A foreign key is a column or a set of columns in one table that refers to the primary key of another table. It establishes a relationship between the two tables, and it is used to enforce referential integrity. Foreign keys ensure that data in the referencing table (child table) corresponds to data in the referenced table (parent table).
Unique Key: A unique key is similar to a primary key in that it enforces uniqueness, but it does not necessarily serve as the main identifier for a record. Unlike primary keys, a table can have multiple unique keys.
Super Key: A super key is a set of one or more columns that can be used to uniquely identify a record. It may contain more columns than needed to uniquely identify a record. Super keys are a broader concept that includes primary keys and unique keys.
Candidate Key: A candidate key is a minimal super key, meaning it is a subset of a super key and uniquely identifies a record. In a table, there may be multiple candidate keys, and one of them is chosen as the primary key.
Composite Key: A composite key is a key that consists of two or more columns in a table, and together, these columns uniquely identify a record. It is used when a single column is not sufficient to establish uniqueness.
Natural Key: A natural key is a key derived from data that already exists in the real world and is used as a unique identifier in the database. For example, a social security number or a product's UPC code can be a natural key.
Surrogate Key: A surrogate key is an artificially generated key, often an integer or a GUID, used as the primary key in a table. It is not derived from natural data but is created solely for the purpose of uniquely identifying records.
Alternate Key: An alternate key is a candidate key that is not chosen as the primary key. While it can be used for identifying records, it is not the primary means of identification.
Non-key Attribute: Non-key attributes are columns in a table that are not part of any key but contain other information about the entity being modeled.
CURSOR: In SQL, a cursor is a database object that is used to retrieve and manipulate data from a result set, typically within a stored procedure or a block of code. Cursors are especially useful when you need to process rows of data one at a time, rather than fetching the entire result set at once. Cursors are often used in procedural SQL languages like PL/SQL (used in Oracle databases) or T-SQL (used in Microsoft SQL Server). There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.
DML e.g (SIUD/CRUD) (Data Manipulation Language): DML commands are used to interact with the data stored in the database. They allow you to perform operations like inserting, updating, retrieving, and deleting data from database tables. Common DML commands include:
SELECT: Used to retrieve data from one or more database tables. It is used to query the database and retrieve specific information.
INSERT: Used to add new records (rows) to a table.
UPDATE: Used to modify existing data in a table by changing the values in one or more columns.
DELETE: Used to remove records from a table based on specified conditions.
MERGE: Used to perform an "upsert" operation, which combines INSERT, UPDATE, and DELETE operations based on certain conditions.
DDL (Data Definition Language): DDL commands are used to define and manage the structure of a database. They allow you to create, modify, and delete database objects such as tables, indexes, and schemas. Common DDL commands include:
CREATE: Used to create a new database, table, view, index, or other database objects.
ALTER: Used to modify the structure of an existing database object, such as adding or deleting columns in a table.
DROP: Used to delete a database object, such as a table or index.
TRUNCATE: Used to remove all the data from a table but keep the table structure intact.
COMMENT: Used to add comments or descriptions to database objects for documentation purposes.
GRANT and REVOKE: Used to manage permissions and access control on database objects.
DCL (Data Control Language): DCL commands are used to control and manage access to the data stored in a database. These commands are typically used to set permissions and privileges for users and roles, thereby controlling who can perform certain actions on database objects. Common DCL commands include:
GRANT: This command is used to give specific privileges or permissions to a user or a role. Privileges may include SELECT (read), INSERT (write), UPDATE (modify), DELETE (remove), and more.
REVOKE: This command is used to take away previously granted privileges from a user or a role.
DENY: Some database management systems support the DENY command, which explicitly denies certain privileges to a user or role, even if they have been granted elsewhere.
TCL (Transaction Control Language): TCL commands are used to control and manage transactions within a database. Transactions are sequences of one or more SQL statements that are treated as a single unit of work. TCL commands help ensure data integrity and consistency by managing transactions. Common TCL commands include:
COMMIT: The COMMIT command is used to save all the changes made during the current transaction and make them permanent in the database. It effectively ends the transaction.
ROLLBACK: The ROLLBACK command is used to undo all the changes made during the current transaction. It is typically used to revert the database to its state before the transaction started.
SAVEPOINT: The SAVEPOINT command allows you to set a point within a transaction to which you can later roll back. It provides a way to have partial rollbacks within a transaction.
SET TRANSACTION: The SET TRANSACTION command is used to specify characteristics of a transaction, such as isolation level and transaction access mode.
JOINs e.g.
Inner Join, Left Join/Left Outer Join, Right Join/Right Outer Join, Full Join/Full Outer Join
On the other hand e.g (Set operations) Self Join, Cross Join, Union
SQL Wildcards: Wildcard Characters e.g. % _ [] ^ - {} (select * from tablename where columnname like '%columnnamevalue%')
SQL NULL Functions: IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
SQL Server Functions: Click >>>
SQL Quick Ref: Click >>>
Stored Procedure vs Function: The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
Here's the basic syntax for using the PIVOT operation in SQL Server:
Here's a simple example to illustrate how the PIVOT operation works. Suppose you have a table named Sales with columns Product, Month, and Revenue, and you want to pivot the data to show monthly revenues for each product as columns:
14. PIVOT/UNPIVOT: In Microsoft SQL Server, the 'PIVOT' operation is used to transform rows of data into columns. This can be useful when you want to perform aggregations or cross-tabulations on your data. The PIVOT operation is typically used in conjunction with the 'UNPIVOT' operation to switch data from a normalized form (rows) to a denormalized form (columns) or vice versa.
Let's break down the components of the PIVOT operation:
SELECT *: This specifies the columns that you want to retrieve from the pivoted table. You can select specific columns based on your requirements.
(SELECT ...) or the source data query: This is the source data that you want to pivot. It can be a query that retrieves data from one or more tables.
PIVOT: This keyword indicates the start of the PIVOT operation.
AGGREGATE_FUNCTION: This is the aggregation function (e.g., SUM, MAX, MIN, AVG) that will be applied to the data when pivoting.
column_to_aggregate: This is the column that will be aggregated based on the values in the pivot column.
FOR PIVOT_COLUMN IN (value1, value2, value3, ...): This specifies the pivot column, which contains the values that will become column headers in the pivoted table. You list the values you want to pivot in the IN clause.
AS PivotTable: This is an alias for the resulting pivoted table.
This query would transform the data from rows to columns, with each product's revenue for each month in separate columns. Remember that PIVOT is best used when you know the distinct values in the pivot column in advance. If you have dynamic or unknown values, you might need to use dynamic SQL to construct your PIVOT query.
#Database #Query #QueryAnalysis #Concepts #AbdurRahimRatulAliKhan #ARRAK
>>>
