Thursday, May 19, 2022

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.










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

What is the difference between DELETE and TRUNCATE commands

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:
output 5

delete from stu where s_name=’Bob’

Output:

output 6

The syntax for the TRUNCATE command:

TRUNCATE TABLE
Table_name;

Example:

select * from stu1

Output:

output 7

truncate table stu1

Output:

output 8

This deletes all the records from a table.

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