Uncategorized

Stored Procedures:

A stored procedure represents a set of SQL statements that are given a name, and are then stored in a relational database management system (RDBMS) as a set, with the main aim of reusing it in executing multiple programs.

Creating a procedure in MySQL.
The statement below generates a new stored procedure.

            CREATE PROCEDURE GetAllCustomers()

            BEGIN //

                        SELECT * FROM customers;
            END //


            How to execute a new stored procedure.

            Executing a stored procedure, we make use of the CALL statement.

            CALL  GetAllCustomers()

  • SQL Functions

            MySQL Functions

CONCAT()Is used to add two or more statements together

FORMAT()

Is used to formats a number into a new format like “x,xxx,xxx”, rounded off to a specific number of decimal places.

INSERT()

Is used to add a string into another string at the specified location and for the specified number of characters.

LCASE()

Is used to converts a string value to lower case.

TRIM()

Is used to remove any extra white spaces from a string.

  
AVG()

Is used to get the average value of an expression.

COUNT()

Is used in calculating the total records returned from a query.

GREATEST()

Is used to establish the item with the greatest value from a list of arguments.

LEAST()

Is used to establish the item with the least value from a list of arguments.

MAX()

Used to establish the maximum value from a list of arguments.

MIN()

Is used to establish the minimum value from a list of arguments.

MOD()

Is used to give the remainder of a number divide by another number

  
DATE()

Is used to return the date part from a date-time value

TIMESTAMP()

Is used to return a date-time value according to a date or date-time value

CURRENT_TIMESTAMP()Is used to give the current date and time values.

  

Views

Views are made to keep your table queries as a virtual table. Views are used to make the tables simpler and are then reused frequently as needed. Views do not normally store data but instead have the statements used to provide the re-suability.

Example of an SQL view

            CREATE VIEW [Meru Clients] AS
            SELECT ClientsName, ClientsName
            FROM Clients
            WHERE County = ‘Meru’;

Tags

You might also like

Leave a Reply

Your email address will not be published. Required fields are marked *