Stored Procedure

 

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

HAVING and WHERE

 

difference between HAVING and WHERE clauses?

The distinction between HAVING and WHERE clauses in SQL is that while the WHERE clause cannot be used with aggregates, the HAVING clause is used with the aggregated data. The WHERE clause works on the data from a row and not with the aggregated data.

Union and Union All

 

The Union All operator gives all the records from both tables including the duplicates.

A UNION command does a SELECT DISTINCT on the final results set

views and tables

 

views and tables.

Views	Tables
A view is a virtual table that is extracted from a database.	A table is structured with a set number of columns and a boundless number of rows.
A view does not hold data itself.	A table contains data and stores it in databases.
A view is utilized to query certain information contained in a few distinct tables.	A table holds fundamental client information and cases of a characterized object.
In a view, we will get frequently queried information.	In a table, changing the information in the database changes the information that appears in the view

view and materialized view

 

What are views? 

Views are virtual tables used to limit the tables that we want to display. Views are nothing but the result of an SQL statement that has a name associated with it. Since views are not physically present, they take less space to store.

Materialized views 

Materialized views are also the logical virtual copy of data-driven by the select query but the result of the query will get stored in the table or disk.

The main difference between view and materialized view is that view is a virtual table that is not stored in the disk while materialized view is a physical copy of the original database that is stored in the disk.

COALESCE function

 

What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.

Syntax:

COALESCE(val1,val2,val3,……,nth val)

Example:

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output:

1

DELETE and TRUNCATE commands

 

What is the difference between DELETE and TRUNCATE commands?

• DELETE: This query is used to delete or remove one or more existing tables.
• TRUNCATE: This statement deletes all the data from inside a table.

The difference between DELETE and TRUNCATE commands are as follows:

• TRUNCATE is a DDL command, and DELETE is a DML command.
• With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger.
• If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

The syntax for the DELETE command:

DELETE FROM table_name
[WHERE condition];

Example:

select * from stu

Output:

delete from stu where s_name=’Bob’

Output:

The syntax for the TRUNCATE command:

TRUNCATE TABLE
Table_name;

Example:

select * from stu1

Output:

truncate table stu1

Output:

This deletes all the records from a table.

AUTO_INCREMENT

 

What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted.

INDEX IN SQL

 

What is an index?

Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to find matches, which may result in slow operation on large data.

Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

Explain the types of indexes

Single-column Indexes: A single-column index is created for only one column of a table.

Syntax:

CREATE INDEX index_name
ON table_name(column_name);

Composite-column Indexes: A composite-column index is created for two or more columns of a table.

Syntax:

CREATE INDEX index_name
ON table_name (column1, column2)

Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.

Syntax:

CREATE UNIQUE INDEX index
ON table_name(column_name)

primary key,unique key,foreign key

 

primary key

A primary key is used to uniquely identify all table records. 

It cannot have NULL values and must contain unique values. 

A table can have only one primary key that consists of single or multiple fields.

unique key

The key that can accept only a null value and cannot accept duplicate values is called a unique key. 

The role of a unique key is to make sure that all columns and rows are unique.

foreign 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.

What is the difference between primary key and unique key?

Both primary and unique keys carry unique values but a primary key can not have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.

Difference between row_number(), rank() and dense_rank()

  

      Row_number() -> Used for generating serial number

                                           it gives unique value to each row

• RANK() ---->gives  the same row number to the same value, leaving "holes"
• DENSE_RANK() --->will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

Writing Query to Find Second Highest Salary in SQL

1) options  1 (without using LIMIT function )

Answer :

SELECT MAX(Salary) From Employee WHERE Salary < ( SELECT Max(Salary) FROM Employee);

2) options  2 (without using LIMIT function )

Answer :

SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable ORDER BY Salary ASC;

3) options  3 (using LIMIT function )

Answer :

SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;

GENERIC  SOLUTION USING DENSE_RANK  

select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=2


change the r=3 for  3RD Highest salary 

                   r=4 for  4 th  Highest salary ,,, and so on

Different Types of SQL JOINs

1.  Different Types of SQL JOINs

• (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