Page 130 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

EXERCISE 36 : Simple application on database using SP,
Triggers, Cursors & Indexing

Objectives

At the end of this exercise you shall be able to
• create simple application on database using SP Triggers cursors & indexing

Requirements

Tools/Materials
• Desktop/Laptop with latest configuration
• Operating system: window 10:11
• XAMPP server r3.3.0

Procedure

TASK 1 : Creating a simple application using stored procedures, triggers, cursors, and indexing in MySQL
1 CREATE DATABASE –
CREATE DATABASE library db;
USE library db;
2 CREATE TABLES –

Create table books and users -
CREATE TABLE books (
book id INT PRIMARY KEY AUTO INCREMENT,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,

quantity INT NOT NULL
);
CREATE TABLE users (
user id INT PRIMARY KEY AUTO INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
3 INSERT VALUES –

INSERT INTO books (title, author, quantity) VALUES
(‘Book 1’, ‘Author 1’, 5),
(‘Book 2’, ‘Author 2’, 8),
(‘Book 3’, ‘Author 3’, 3);

INSERT INTO users (name, email) VALUES
(‘User 1’, ‘user1@example.com’),
(‘User 2’, ‘user2@example.com’);

115
CITS : IT & ITES - Computer Software Application - Exercise 35