1)Create a database named "Employee"

CREATE database Employee;

2) Create a table named "payroll"

CREATE TABLE payroll(name varchar(20),emp_id varchar(10),mobile int(10),email varchar(20),salary int(10),pf int(10),total_work_days int(10),emp_work_days int(10),extra_shifts int(12))


INSERT INTO `payroll` (`name`, `emp_id`, `mobile`, `email`, `salary`, `pf`, `total_work_days`, `emp_work_days`, `extra_shifts`) VALUES ('princy', '21ER', '45645456', 'princy@gmail.com', '20000', '10000', '30', '27', '2'), ('james', '13ER', '65766564', 'james@gmail.com', '50000', '1200', '31', '28','3'),('rosy','24ER','78878787','rosy2gmail.com','23000','1230','31','20','1'),('tina','24ER','76656545','tina@gmail.com','26000','2000','36','23','4'),('infant','25ER','765547097','infant@gmail.com','40000','3000','30','10','2'),('wiky','26ER','2345667','wiky@gmail.com','10000','200','30','12','0'),('dany','27ER','565656554','dany@gmail.com','70000','7000','29','27','1')


3) Create a variety of stored procedures

i. No parameter


BEGIN


CREATE PROCEDURE high salary()


SELECT * FROM payroll WHERE salary>20000;


SELECT * FROM payroll WHERE salary <40000;


END


ii. IN parameter


BEGIN


CREATE PROCEDURE `employee_details`(IN id INT(10))


SELECT emp_id FROM payroll LIMIT id;


SELECT name FROM payroll LIMIT id;


END


iii. OUT parameter


BEGIN


CREATE PROCEDURE `salary_details` (OUT maxsal INT(10))


SELECT max(salary) into maxsal FROM payroll;


SELECT min(salary) into minsal FROM payroll;


END


iv. INOUT parameter


BEGIN


CREATE PROCEDURE `personal_details`(INOUT `y` VARCHAR(20))


SELECT emp_work_days INTO y FROM payroll WHERE emp_id = y;


SELECT total_work_days INTO z FROM payroll WHERE emp_id = z;


END


                       Another one






Procedure without Parameter


CREATE PROCEDURE get merit student()


SELECT * FROM student_info WHERE marks > 70




CREATE PROCEDURE get_poor_student()


SELECT * FROM student Info WHERE marks < 60




CREATE PROCEDURE 'pickthree'()


SELECT * FROM student info LIMIT 3




CALL get_poor_student()




Procedures with IN Parameter


CREATE PROCEDURE get student(IN var1 INT)


SELECT * FROM student info LIMIT var1;


CALL get_student(4);


Multiple queries


BEGIN


SELECT * FROM student_info WHERE marks > 70 ;


SELECT COUNT(stud_code) AS Total_Student FROM student_info;


END


Procedures with OUT Parameter




CREATE PROCEDURE maxmarks(OUT high INT(10))


SELECT MAX(marks) INTO high FROM student_info


CALL `maxmarks`(@p0); SELECT @p0 AS `high`;




Procedures with INOUT Parameter




CREATE PROCEDURE `displaymarks`(INOUT `var1` VARCHAR(20))


SELECT marks INTO var1 FROM student_info WHERE stud_code = var1


SET @p0='21PDS801';


 CALL `displaymarks`(@p0); SELECT @p0 AS `var1`;



Multiple queries


BEGIN


SELECT * FROM student_info WHERE marks > 70 ;


SELECT COUNT(stud_code) AS Total_Student FROM student_info;


END


SHOW PROCEDURE STATUS


SHOW PROCEDURE STATUS WHERE db = 'mystudentdb';




Drop the procedure


DROP PROCEDURE display_marks;  

























Comments

Post a Comment

Popular posts from this blog

INTRO To Latex

Image
LaTeX is one of the efficient methods for publishing journals and papers in today's society, It is essential to study latex like other coding programmes  First step : \documentclass{article} This is the first step as like as in #include in C, Inside the braces we can use many extensions but mostly we use article   step 2 : Using packages \usepackage{amsmath,amssymb} this is the package for math mode typing also there are many packages, table for tabulation and table making,and graphicx for picture installation.   Step 3 : then we shall begin the document with the command. \begin{document}           ..............     \end{document}  On giving begin document automatically \end {document} will appear,if not we have to put \end{document} in the end of the problembetween begin and end document we've to create the program or the content Step 4 :    We can use the facilities which are available ...
Read more

Question

 1. To print your name 2. To print your mailing address 3. To print your name 4. To print your mailing address 5. To do arithmetical operations 6. To do arithmetical operations by getting input value from the keyboard or user 7. To find the area of a circle 8. To find the area of a triangle 9. To find the volume of the sphere 10. To find the volume of the cylinder 11. To convert farenheit to celcius scale 12. To find the square root for a given number 13. To identify the number as even or odd 14. To identify the given year is leap or not 15. To identify the bigger of two numbers 16. To identify the biggest of three numbers 17. To identify the given number as one digit, two digit, three digit or more than three digit 18. To count the number of digits present in a number 19. To identify the given character as vowel or not 20. To check whether a string is palindrome or not 21. To sort words in alphabetic order 22. To reverse a number 23. To compute the simple interest 24. To check ...
Read more

Good

 ADBMS Lab MYSQL Database Queries Create Database Mysql>CREATE DATABASE sjcds; Show Database mysql> SHOW DATABASES; Use Database mysql> USE emplyeedb; Drop Database DROP DATABASE [IF EXISTS] database_name; Create Table mysql> CREATE TABLE employee_table( id int NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, degree varchar(35) NOT NULL, age int NOT NULL, PRIMARY KEY (id) ); Show Tables mysql> SHOW TABLES; Alter table ALTER TABLE cus_tbl ADD mobile int(10) NOT NULL; Drop Table mysql> DROP TABLE orders; Truncate Table TRUNCATE TABLE table_name; Rename Table RENAME TABLE stu_table TO student_table; Insert Table CREATE TABLE People(     id int NOT NULL AUTO_INCREMENT,     name varchar(45) NOT NULL,     occupation varchar(35) NOT NULL,     age int,     PRIMARY KEY (id) ); INSERT INTO People VALUES (102, 'Joseph', 'Developer', 30), (103, 'Mike', 'Leader', 28), (104, 'Step...
Read more