Skip to main content

Testdome Interview question - Mysql Stored Procedure

 Mysql Stored Procedure 
A company needs a stored procedure that will insert a new user with an appropriate type.

Consider the following tables:

TABLE userTypes
id INTEGER NOT NULL PRIMARY KEY, type VARCHAR(50) NOT  NULL
TABLE users
id INTEGER NOT NULL  PRIMARY KEY  AUTO_INCREMENT, email VARCHAR(50)NOT NULL, userTypeId INTEGER NOT NULL ,FOREIGN KEY(userTypeId) REFERENCES  user Types (id)
Finish the insertUser procedure so that it inserts a user, with these requirements:
• id is auto incremented.
• email is equal to the email parameter.
• userTypeld is the id of the userTypes row whose type attribute is equal to the type parameter.
DELIMITER $$
CREATE PROCEDURE insertUser(
    IN p_email VARCHAR(50),
    IN p_type VARCHAR(50)
)
BEGIN
    DECLARE v_userTypeId INT;
    -- Get userTypeId from userTypes table
    SELECT id INTO v_userTypeId  FROM userTypes WHERE type = p_type    LIMIT 1;
    -- Insert into users table
    INSERT INTO users (email, userTypeId)  VALUES (p_email, v_userTypeId);
END $$
DELIMITER ;

Comments

Post a Comment

Popular posts from this blog

Interview questions related to Laravel 8 updates- Laravel Interview questions

 Laravel 8 brought several updates and features to the framework. If you are preparing for an interview and expecting questions related to Laravel 8 updates, here are some potential questions: 1. What are the major features introduced in Laravel 8? Laravel Jetstream: A new application scaffolding for Laravel, providing teams with a starting point for building robust applications. Laravel Breeze: A lightweight and minimalistic front-end starter kit. Model Factory Classes: Introduction of factory classes for model factories, allowing for better organization of data seeding logic. Job Batching: A feature that allows you to easily run a batch of jobs and then perform some action when all the jobs have completed. Dynamic Blade Components: The ability to render Blade components dynamically. 2. Explain the improvements made to the Laravel job queue in version 8. Laravel 8 introduced Job Batching, which allows you to group multiple jobs into a batch and perform actions upon the completion ...
Post a Comment
Read more

AWS Lambda functions within a Laravel application

O ne common scenario for using AWS Lambda functions within a Laravel application is to offload specific tasks or processes that are either time-consuming, resource-intensive, or need to be executed asynchronously. Here are some common use cases: Image Processing: You can use Lambda functions to resize, crop, or manipulate images uploaded by users. For example, when a user uploads an image, trigger a Lambda function to process it and generate thumbnails or apply filters asynchronously. Email Notifications: Lambda functions can be used to send email notifications, such as welcome emails, password reset emails, or transactional emails. You can trigger Lambda functions from events within your Laravel application, such as user registration or order placement. Data Processing and Transformation: Perform data processing tasks, such as parsing CSV files, transforming data formats, or aggregating data from multiple sources. Lambda functions can be invoked by events like file uploads to S3 or by...
Post a Comment
Read more