Page 94 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

EXERCISE 25 : Adding indices to tables

Objectives
At the end of this exercise you shall be able to
• connect the MySQL database server
• create tables in database
• create index on tables.

Requirements

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

Procedure

TASK 1 : Access MySQL:
MySQL Workbench: Download and install the GUI for user-friendly management.
Command Prompt: Open it and navigate to the MySQL installation directory (e.g., C:\Program Files\MySQL\
MySQL Server 8.0\bin).
Connect to your Database:
MySQL Workbench: Provide your server hostname, username, password, and database name.
Command Prompt: Use the mysql command followed by your credentials (e.g., mysql -h localhost -u root -p).

Identify Columns for Indexing:
Choose columns frequently used in WHERE clauses, ORDER BY, or JOINs. Consider data types and selectivities
(percentage of unique values).
Use the Database:

USE your database name;

TASK 2 :Create a Table:

Let’s assume you have a table called employee:
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(100),

department id INT,
salary DECIMAL(10, 2)
);

TASK 3 : Insert Data into the Table:
INSERT INTO employee (id, name, department id, salary)
VALUES (1, ‘John Doe’, 1, 50000.00), (2, ‘Jane Smith’, 2, 60000.00);

79
CITS : IT & ITES - Computer Software Application - Exercise 24