Unit-III

  

Working with Databases: Setting Up a MySQL Database

Setting up a MySQL database involves installing the MySQL Server, configuring it, and creating databases and users. MySQL is a widely used open-source relational database system ideal for application development and data management.

Installing MySQL

Windows Setup Steps:

1.      Download Installer: Go to the official MySQL site and download the MySQL Community Edition Installer.^[2]

2.     Run Installer: Choose a setup type such as Full or Server Only.^[3]

3.     Configuration:

o   Set the root user password.

o   Configure MySQL as a Windows service.

o   Apply and complete the configuration by clicking Execute.^[3]

4.     Verification: Launch MySQL Workbench or open Command Prompt and run mysql -u root -p to access the MySQL shell.

macOS and Linux Setup Steps:

·       On Ubuntu/Debian:

sudo apt update 
sudo apt install mysql-server 
sudo systemctl start mysql 
sudo mysql_secure_installation

·       On macOS (Homebrew):

brew install mysql 
brew services start mysql

These commands install and start the MySQL service securely.^[4]

Creating a MySQL Database:

After installation, follow these steps in the MySQL shell:

1.      Login:

mysql -u root -p

Enter your root password.

2.     Create a database:

CREATE DATABASE my_database;

Confirm creation with:

SHOW DATABASES;

You’ll see your new database listed.^[5][6]

3.     Select your database:

USE my_database;

This sets the active database for subsequent commands.^[7]

Creating a User and Granting Access

Creating dedicated users improves security.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON my_database.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

This assigns full privileges for newuser to manage the specific database.^[6][8]

Connecting via MySQL Workbench or ODBC

You can manage databases graphically using MySQL Workbench or connect applications using MySQL Connector/ODBC.
To configure a connector on Windows, open ODBC Data Source (64-bit), add a MySQL ODBC 8.0 ANSI Driver, and specify connection parameters like hostname, username, password, and database name.^[9]

Summary of Key Commands

Task	Command Example
Start MySQL Shell	mysql -u root -p
Create Database	CREATE DATABASE dbname;
Show Databases	SHOW DATABASES;
Use Database	USE dbname;
Create User	CREATE USER 'user'@'localhost' IDENTIFIED BY 'pwd';
Grant Privileges	GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Apply Changes	FLUSH PRIVILEGES;

 

 

Using a MySQL Database: Command Line, Using a MySQL Database

Using a MySQL database from the command line involves connecting to the MySQL server, selecting a database, and executing SQL commands to create, modify, or query data. This is often the most efficient and scriptable way to manage MySQL.

 

Accessing the MySQL Command-Line Interface

Open your terminal or command prompt and log in using:

mysql -u root -p

·       -u specifies the username (use any MySQL account, not only root)

·       -p prompts for the user’s password
Once authenticated, the prompt changes to:

mysql>

You are now in the MySQL shell and can execute commands directly.

Selecting and Managing Databases

Create a new database:

CREATE DATABASE example_db;

View available databases:

SHOW DATABASES;

Select a database to use:

USE example_db;

Delete a database:

DROP DATABASE example_db;

Each of these statements ends with a semicolon (;) to indicate the command’s completion.^[2][1]

Managing Tables Within a Database

Create a table:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade CHAR(2)
);

Show all tables:

SHOW TABLES;

Describe table structure:

DESCRIBE students;

Delete a table:

DROP TABLE students;

These commands define and inspect tables within the active database.

Inserting, Retrieving, and Updating Data

Insert data:

INSERT INTO students (name, age, grade)
VALUES ('Ravi Kumar', 20, 'A');

View data:

SELECT * FROM students;

Filter data:

SELECT name, grade FROM students WHERE age > 18;

Update existing data:

UPDATE students SET grade = 'B' WHERE name = 'Ravi Kumar';

Delete records:

DELETE FROM students WHERE id = 1;

These commands handle the CRUD operations — Create, Read, Update, Delete.

Exporting and Importing Databases

Export a database:

mysqldump -u root -p example_db > backup.sql

Import a database:

mysql -u root -p example_db < backup.sql

These are run outside the MySQL shell in your system terminal. They are useful for backups and transfers.

Common Administrative Commands

Task	Command Example
Show current user	SELECT USER();
Show current database	SELECT DATABASE();
Create a user	CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass123';
Grant privileges	GRANT ALL PRIVILEGES ON example_db.* TO 'user1'@'localhost';
Revoke privileges	REVOKE ALL PRIVILEGES ON example_db.* FROM 'user1'@'localhost';
Apply privilege changes	FLUSH PRIVILEGES;

 

These commands help manage access control securely.

Exiting MySQL

To leave the MySQL command interface:

exit;

or

quit;

This returns you to your normal system shell.

This approach allows full control of MySQL databases from the command line, from basic creation to advanced administrative and data operations.

Working with Tabular Numeric Data 

(NumPy with Python)

Getting started

·       Install and import: pip install numpy, then use import numpy as np in scripts for consistency and shorter code in answers.

·       NumPy arrays are homogeneous, n-dimensional arrays optimized for vectorized operations and broadcasting, making them faster than Python lists for numeric work.

NumPy arrays with array()

·       Create from Python lists or nested lists: a = np.array(), b = np.array([,]).

·       dtype is inferred; can be set: np.array(, dtype=np.float64).

·       Exam tip: mention “ndarray is homogeneous and supports vectorized ops.”

Example:

import numpy as np
a = np.array([10, 20, 30])
b = np.array([[1, 2], [3, 4]])
print(a.dtype, a.shape)      # int64 (3,)
print(b.ndim, b.size)        # 2 4

Array attribute

·       shape: tuple of dimensions, e.g., (2,3).

·       ndim: number of axes, e.g., 1D/2D.

·       size: total elements.

·       dtype: element type.

·       itemsize and nbytes: bytes per element and total bytes.

Example:

x = np.array([[1,2,3],[4,5,6]], dtype=np.int32)
print(x.shape, x.ndim, x.size, x.dtype, x.itemsize, x.nbytes)

Arrays with initial placeholder content

·       Zeros: np.zeros((2,3))

·       ones: np.ones((3,))

·       full : np.full((2,2), 7).

·       arange: np.arange(0,10,2),

·       linspace : np.linspace(0,1,5).

·       eye/identity: np.eye(3) for identity matrices.

·       random examples (if allowed in syllabus): np.random.rand(2,3).

Example:

z = np.zeros((2,3))
o = np.ones(4)
f = np.full((2,2), 5)
r = np.arange(1, 10, 2)

Integer indexing

·       1D: a[i] returns element at index i.

·       2D: A[i, j] accesses row i, column j; A[i] returns row i

·       Negative indices count from end: a[-1].

·       Fancy indexing: use integer arrays/lists to pick positions: a[].

Example:

A = np.array([[10,11,12],[20,21,22]])
print(A[0,2], A[^1], A[-1,-1])  # 12 [20 21 22] 22

Array indexing (fancy indexing)

·       Select specific rows/cols by index arrays: A[,] picks (0,1) and (1,2).

·       Row/column selection: A[, :] or A[:, ].

·       Useful for reordering or sampling without loops

Example:

A = np.array([[1,2,3],[4,5,6],[7,8,9]])
print(A[[0,2], :])     # rows 0 and 2
print(A[:, [0,2]])     # columns 0 and 2

Boolean array indexing

·       Create a boolean mask from a condition: mask = A > 5; A[mask] returns matching elements.^[3]

·       Combine conditions with & and |, use parentheses: A[(A%2==0) & (A>3)]. ^[3]

·       Good for filtering without loops.^[2]

Example:

A = np.array([1,4,6,7,10])
print(A[A > 5])        # [ 6  7 10]
print(A[(A % 2 == 0)]) # [ 4  6 10]

Slicing and iterating

·       Slicing: a[start:stop:step]; defaults allowed, negative step reverses: a[::-1].^[3]

·       2D slices: A[r0:r1, c0:c1] gives a view (no copy); modifying the slice changes original; use .copy() if needed.^[3]

·       Iteration is possible but vectorization is preferred for performance.^[2]

Example:

A = np.array([[1,2,3],[4,5,6],[7,8,9]])
sub = A[0:2, 1:3]  # [[2,3],[5,6]]
sub[0,0] = 99      # changes A unless copy() used

Basic arithmetic operations

·       Elementwise: +, -, *, /, **, np.add, np.subtract, np.multiply, np.divide, np.sqrt.^[3]

·       Works shape-wise; if shapes match or broadcast-compatible, operations apply per element.^[3]

·       Matrix multiplication (if needed): A @ B or np.matmul(A,B) distinct from elementwise *.^[3]

Example:

x = np.array([1,2,3], dtype=float)
y = np.array([4,5,6], dtype=float)
print(x + y, x * y, x / y, x ** 2)

Mathematical functions in NumPy

·       Elementwise ufuncs: np.sqrt, np.exp, np.log, np.sin, np.cos.

·       Aggregations: a.sum(), a.mean(), a.min(), a.max(), axis parameter for row/col operations.^[2]

·       Example with axis:

M = np.array([[1,2,3],[4,5,6]])
print(M.sum())          # 21
print(M.sum(axis=0))    # column-wise: [5 7 9]
print(M.mean(axis=1))   # row-wise: [2. 5.]

Changing the shape of an array

·       reshape(new_shape) returns a view when possible; size must match.

·       ravel() flattens to 1D view; flatten() creates a copy.

·       Transpose: A.T swaps axes for 2D; np.transpose for general axes.

Example:

a = np.arange(6)             # [0 1 2 3 4 5]
b = a.reshape((2,3))         # [[0 1 2],[3 4 5]]
c = b.T                      # [[0 3],[1 4],[2 5]]

Stacking and splitting arrays

·       Vertical and horizontal stacking: np.vstack([a,b]), np.hstack([a,b]).

·       Column_stack for 1D to 2D columns: np.column_stack([a,b]).

·       Splitting: np.vsplit(A, n), np.hsplit(A, n) or np.split(A, indices, axis).

Example:

a = np.array([1,2,3])
b = np.array([4,5,6])
print(np.vstack([a,b]))   # [[1 2 3],[4 5 6]]
print(np.hstack([a,b]))   # [1 2 3 4 5 6]

Broadcasting

·       Rule: trailing dimensions must be equal or one of them is 1; arrays expand logically to match without copying.

·       Common cases: add scalar to array; add 1D row to 2D rows; add column vector (n,1) to 2D columns.

·       Avoid shape mismatches; reshape with np.newaxis or None.

Example:

M = np.array([[1,2,3],[4,5,6]])
row = np.array([10,20,30])
col = np.array([[^100],[^200]])
print(M + row)           # row-wise add
print(M + col)           # column-wise add

Quick exam pointers

·       Define ndarray, homogeneity, and vectorization; give one-line examples.

·       Distinguish elementwise * vs matrix @.

·       Explain view vs copy in slicing; mention .copy() to avoid side-effects.

·       State broadcasting rule and give a shape example, e.g., (2,3) + (3,) works, (2,3) + (2,) doesn’t.

------------------------------------**same question *------------------------------------------

1) NumPy Arrays Creation Using array()

·        Purpose: Convert Python sequences (lists, nested lists) to fast, typed ndarrays.

·        Basic: np.array() → 1D array.

·        2D (matrix): np.array([, ]).^[2][3][4][1]

·        dtype inference: NumPy promotes to a common type (e.g., int → float if mixed).

·        dtype control: np.array(, dtype=np.float64).^[3][1][2]

·        Good practice: Ensure inner lists have equal length for proper 2D arrays.

·        Example:

import numpy as np
a = np.array([10, 20, 30])
B = np.array([[1, 2], [3, 4]], dtype=np.int32)
print(a.dtype, a.shape)   # platform-dependent dtype, shape (3,)
print(B.ndim, B.size)     # 2 4

2) Array Attributes

·        shape: tuple of axis lengths; product(shape) = size.

·        ndim: number of axes (rank); 1D vector → 1, 2D matrix → 2.

·        size: total elements; equals shape product.

·        dtype: element type (e.g., int32, float64).

·        itemsize: bytes per element; nbytes: total memory = size * itemsize.

·        Example:

x = np.array([[1,2,3],[4,5,6]], dtype=np.int32)
print(x.shape, x.ndim)    # (2, 3) 2
print(x.size, x.dtype)    # 6 int32
print(x.itemsize, x.nbytes)  # 4 24

3) Arrays with Initial Placeholder Content

·        zeros: np.zeros((m, n)) → all 0.0 by default.

·        ones: np.ones(k) → all 1.0 by default.

·        full: np.full((r, c), value) → constant-filled.

·        arange: np.arange(start, stop, step) → step-based range, stop excluded.

·        linspace: np.linspace(start, stop, num) → count-based, endpoints included.

·        eye: np.eye(n) → n×n identity matrix.

·        dtype control for precision/memory: use dtype=... where needed.

·        Example:

z = np.zeros((2,3))
o = np.ones(4)
f = np.full((2,2), 5)
r = np.arange(1, 10, 2)
l = np.linspace(0, 1, 5)
I = np.eye(3)

4) Integer Indexing

·        1D element: a[i]; negative index from end: a[-1].

·        2D element: A[i, j] (row i, column j).

·        Whole row: A[i] or A[i, :].

·        Whole column: A[:, j].

·        Fancy integer indexing (multiple picks): A[, :] (rows 0 and 2); A[:, ] (cols 0 and 2).^[2]

·        Example:

A = np.array([[10,11,12],[20,21,22],[30,31,32]])
print(A[0, 2])   # 12
print(A[^1])      # [20 21 22]
print(A[:, 1])   # [11 21 31]
print(A[[0,2], :])  # rows 0 and 2

5) Array Indexing (Fancy Indexing Overview)

·        Use integer arrays/lists to select arbitrary rows/columns/order without loops.

·        Returns a new array (often a copy) rather than a view.

·        Combine row and column picks: A[][:, ] for rows 0,2 and cols 1,2.^[1][2]

·        Example:

A = np.array([[1,2,3],[4,5,6],[7,8,9]])
rows = [2, 0]
cols = [2, 0]
print(A[rows, :])   # rows 2 then 0
print(A[:, cols])   # cols 2 then 0

6) Boolean Array Indexing

·        Build boolean masks from conditions: mask = A > 5.

·        Filter elements: A[mask].

·        Combine conditions: (A % 2 == 0) & (A > 3); use parentheses.

·        Useful for conditional selection without loops.

·        Example:

x = np.array([1,4,6,7,10])
print(x[x > 5])                   # [ 6  7 10]
print(x[(x % 2 == 0) & (x > 3)])  # [ 4  6 10]

7) Slicing and Iterating in Arrays

·        1D slicing: a[start:stop:step]; defaults allowed; reverse: a[::-1].

·        2D slicing: A[r0:r1, c0:c1].

·        Slices are typically views (modifying slice affects original); use .copy() for independent data.

·        Iteration is possible (for row in A) but vectorization is preferred for speed.

·        Example:

A = np.array([[1,2,3],[4,5,6],[7,8,9]])
sub = A[0:2, 1:3]   # [[2,3],[5,6]]
sub[0,0] = 99       # modifies A unless copy used

8) Basic Arithmetic Operations on NumPy Arrays

·        Elementwise: +, -, *, /, ** or np.add/np.subtract/np.multiply/np.divide.

·        Works when shapes match or are broadcast-compatible.

·        Matrix multiplication: A @ B (not the same as *).

·        Example:

x = np.array([1,2,3.0])
y = np.array([4,5,6.0])
print(x + y)   # [5. 7. 9.]
print(x * y)   # [ 4. 10. 18.]
print(x / y)   # [0.25 0.4  0.5 ]
print(x ** 2)  # [1. 4. 9.]

9) Mathematical Functions in NumPy

·        Universal functions (ufuncs): np.sqrt, np.exp, np.log, np.sin, np.cos (elementwise).

·        Aggregations: arr.sum(), arr.mean(), arr.min(), arr.max().

·        axis parameter: axis=0 (column-wise on 2D), axis=1 (row-wise on 2D).

·        Example:

        M = np.array([[1,2,3],[4,5,6]])
print(M.sum())         # 21
print(M.sum(axis=0))   # [5 7 9]
print(M.mean(axis=1))  # [2. 5.]

10) Changing the Shape of an Array

·        reshape(new_shape): size must remain constant; returns a view when possible.

·        ravel(): flatten to 1D view; flatten(): returns a copy.

·        Transpose: A.T swaps axes (for 2D); np.transpose for arbitrary axis order.

·        Example:

        a = np.arange(6)          # [0 1 2 3 4 5]
b = a.reshape((2,3))      # [[0 1 2],[3 4 5]]
c = b.T                   # [[0 3],[1 4],[2 5]]

11) Stacking and Splitting of Arrays

·        Vertical stack (rows): np.vstack([a, b]).

·        Horizontal stack (columns): np.hstack([a, b]).

·        Column stack 1D vectors into columns: np.column_stack([a, b]).

·        Split arrays: np.vsplit(A, n), np.hsplit(A, n), or np.split(A, indices, axis=...).

·        Example:

a = np.array([1,2,3])
b = np.array([4,5,6])
print(np.vstack([a,b]))   # [[1 2 3],[4 5 6]]
print(np.hstack([a,b]))   # [1 2 3 4 5 6]

12) Broadcasting in Arrays

·        Rule: align shapes from the right; dimensions are compatible if equal or one is 1.

·        The smaller array is virtually stretched (no data copy) to match larger shape.

·        Common patterns: add scalar; add a row vector (1×n) to each row; add a column vector (n×1) to each column.

·        Use None/np.newaxis to reshape for compatibility when needed.

·        Example:

M = np.array([[1,2,3],[4,5,6]])
row = np.array([10,20,30])
col = np.array([[^100],[^200]])
print(M + row)  # [[11 22 33],[14 25 36]]
print(M + col)  # [[101 102 103],[204 205 206]]

Quick Exam Pointers

·        Define ndarray: homogeneous, n-D, efficient for vectorized numeric operations.

·        array() vs placeholder creators: array() from lists; zeros/ones/full/arange/linspace/eye for fast initialization.

·        Attributes to state: shape, ndim, size, dtype, itemsize, nbytes; compute size and memory if asked.

·        Distinguish elementwise * vs matrix @.

·        Slicing produces views; use .copy() to avoid side-effects.

·        Broadcasting rule: right-aligned, equal or 1 per dimension.

DS SEM-3 IMP Questions