A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

• (INNER) JOIN : Returns records that have matching values in both tables.

• LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table.

• RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

• PRIMARY KEY

  1. A primary key is used to ensure data in the specific column is unique.

  2. It uniquely identifies a record in the relational database table.

  3. Only one primary key is allowed in a table.

  4. It is a combination of UNIQUE and Not Null constraints.

  5. It does not allow NULL values.

  6. Its value cannot be deleted from the parent table.

  7. It constraint can be implicitly defined on the temporary tables.

• FOREIGN KEY

  1. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

  2. It refers to the field in a table which is the primary key of another table.

  3. Whereas more than one foreign key are allowed in a table.

  4. It can contain duplicate values and a table in a relational database.

  5. It can also contain NULL values.

  6. Its value can be deleted from the child table.

  7. It constraint cannot be defined on the local or global temporary tables.

• PRIMARY KEY

  1. Used to serve as a unique identifier for each row in a table.

  2. Cannot accept NULL values.

  3. Only one primary key per table

  4. Creates clustered index

  5. A Primary key supports auto increment value.

  6. We cannot change or delete values stored in primary keys.

• UNIQUE KEY

  1. Uniquely determines a row which isn’t primary key.

  2. Can accepts NULL values.

  3. More than one unique key per table

  4. Creates non-clustered index

  5. A unique key does not supports auto increment value.

  6. We can change unique key values.

• Normalization is used to decompose a larger, complex table into simple and smaller ones. This helps us in removing all the redundant data.

• Data Definition Language (DDL)
  DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.

  1. CREATE Creates databases, tables, schema, etc.

  2. DROP: Drops tables and other database objects

  3. DROP COLUMN: Drops a column from any table structure

  4. ALTER: Alters the definition of database objects

  5. TRUNCATE: Removes tables, views, procedures, and other database objects

  6. ADD COLUMN: Adds any column to the table schema

• Data Manipulation Language (DML)
  These SQL queries are used to manipulate data in a database.

  1. SELECT INTO: Selects data from one table and inserts it into another

  2. INSERT: Inserts data or records into a table

  3. UPDATE: Updates the value of any record in the database

  4. DELETE: Deletes records from a table

• Data Control Language (DCL)
  These SQL queries manage the access rights and permission control of the database.

  1. GRANT: Grants access rights to database objects

  2. REVOKE: Withdraws permission from database objects

• Transaction Control Language (TCL)
  TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.

  1. COMMIT: Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrieved

  2. ROLLBACK: Reverts the steps in a transaction in case of an error

  3. SAVEPOINT: Sets a savepoint in the transaction to which rollback can be executed

  4. SET TRANSACTION: Sets the characteristics of the transaction

• A primary key is used to uniquely identify all table records. It cannot have NULL values and must contain unique values. Only one primary key can exist in one table, and it may have single or multiple fields, making it a composite key.

• The role of a unique key is to make sure that all columns and rows are unique. The key that can accept only a null value and cannot accept duplicate values is called a unique key.

• A foreign key is an attribute or a set of attributes that reference the primary key of some other table. Basically, a foreign key is used to link together two tables.

• Data integrity is the assurance of accuracy and consistency of data over its whole life cycle.

• DELETE:

  • This query is used to delete or remove one or more existing tables

  • Its a DML command

  • DELETE is slower than TRUNCATE

  • On DELETE we can apply WHERE command

• TRUNCATE:

  • This statement deletes all the data from inside a table

  • Its a DDL command

  • TRNCATE is faster than DELETE

  • we cannot rollback the deleted data because the log is not maintained while performing this operation.

1. The DELETE statement is used when we want to remove some or all of the records from the table, while the TRUNCATE statement will delete entire rows from a table.

2. DELETE is a DML command as it only modifies the table data, whereas the TRUNCATE is a DDL command.

3. DELETE command can filter the record/tuples by using the WHERE clause. However, the TRUNCATE command does not allow to use WHERE clause, so we cannot filter rows while truncating.

4. DELETE activates all delete triggers on the table to fire. However, no triggers are fired on the truncate operation because it does not operate on individual rows.

5. DELETE performs deletion row-by-row one at a time from the table, in the order, they were processed. However, TRUNCATE operates on data pages instead of rows because it deleted entire table data at a time.

6. DELETE statement only deletes records and does not reset the table's identity, whereas TRUNCATE resets the identity of a particular table.

7. DELETE command require more locks and database resources because it acquires the lock on every deleted row. In contrast, TRUNCATE acquires the lock on the data page before deleting the data page; thus, it requires fewer locks and few resources.

8. DELETE statement makes an entry in the transaction log for each deleted row whereas, TRUNCATE records the transaction log for each data page.

9. TRUNCATE command is faster than the DELETE command as it deallocates the data pages instead of rows and records data pages instead of rows in transaction logs.

10. Once the record deletes by using the TRUNCATE command, we cannot recover it back. In contrast, we can recover the deleted data back which we removed from the DELETE operation.

1. If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints.

2. To create and use the table again in its original form, all the elements associated with the table need to be redefined.

3. However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

Online Analytical Processing (OLAP): Online Analytical Processing consists of a type of software tools that are used for data analysis for business decisions. OLAP provides an environment to get insights from the database retrieved from multiple database systems at one time. Examples – Any type of Data warehouse system is an OLAP system. The uses of OLAP are as follows:

• Spotify analyzed songs by users to come up with a personalized homepage of their songs and playlist.

• Netflix movie recommendation system.

Online transaction processing (OLTP): Online transaction processing provides transaction-oriented applications in a 3-tier architecture. OLTP administers the day-to-day transactions of an organization.

Examples: Uses of OLTP are as follows:

• ATM center is an OLTP application.

• OLTP handles the ACID properties during data transactions via the application.

• It’s also used for Online banking, Online airline ticket booking, sending a text message, add a book to the shopping cart.

• Stored Procedure: A View represents a virtual table. You can join multiple tables in a view and use the View to present the data as if the data were coming from a single table.

  1. SP Accept parameters

  2. SP Can not be used as a building block in SELECT query

  3. SP Can contain CREATE, ALTER, DROP, INSERT, DELETE, UPDATE commands

  4. SP Can not be used in View

• View: A Stored Procedure is precompiled database query that improves the security, efficiency and usability of database client/server applications.

  1. View Does not accepts parameters

  2. View Can be used as a building block in SELECT query

  3. View Can contain only one single Select query

  4. View Can not contain CREATE, ALTER, DROP, INSERT, DELETE, UPDATE commands

  5. View Can be used in Stored Procedure

• A function is also a database object in SQL Server. It’s essentially a sequence of SQL statements that take only input parameters, execute tasks, and return the output.

  • Function must return a value.

  • Will allow only Select statements, it will not allow us to use DML statements.

  • It will allow only input parameters, doesn't support output parameters.

  • It will not allow us to use try-catch blocks.

  • Transactions are not allowed within functions.

  • We can use only table variables, it will not allow using temporary tables.

  • Stored Procedures can't be called from a function.

  • Functions can be called from a select statement.

  • A UDF can be used in join clause as a result set.

• A stored procedure is a database object in SQL Server. It compiles and stores a series of SQL statements that we need to execute frequently.

  • Stored Procedure may or not return values.

  • Can have select statements as well as DML statements such as insert, update, delete and so on

  • It can have both input and output parameters.

  • For exception handling we can use try catch blocks.

  • Can use transactions within Stored Procedures.

  • Can use both table variables as well as temporary table in it.

  • Stored Procedures can call functions.

  • Procedures can't be called from Select/Where/Having and so on statements.

  • Execute/Exec statement can be used to call/execute Stored Procedure.

  • Procedures can't be used in Join clause

• Data Definition Language (DDL)
  DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.

  • CREATE Creates databases, tables, schema, etc.

  • DROP: Drops tables and other database objects

  • DROP COLUMN: Drops a column from any table structure

  • ALTER: Alters the definition of database objects

  • TRUNCATE: Removes tables, views, procedures, and other database objects

  • ADD COLUMN: Adds any column to the table schema

• Data Manipulation Language (DML)
  These SQL queries are used to manipulate data in a database.

  • SELECT INTO: Selects data from one table and inserts it into another

  • INSERT: Inserts data or records into a table

  • UPDATE: Updates the value of any record in the database

  • DELETE: Deletes records from a table

• Data Control Language (DCL)
  These SQL queries manage the access rights and permission control of the database.

  • GRANT: Grants access rights to database objects

  • REVOKE: Withdraws permission from database objects

• Transaction Control Language (TCL)
  TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.

  • COMMIT: Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrieved

  • ROLLBACK: Reverts the steps in a transaction in case of an error

  • SAVEPOINT: Sets a savepoint in the transaction to which rollback can be executed

  • SET TRANSACTION: Sets the characteristics of the transaction