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;

This sequence gives a complete overview—from installation to database creation and access control—for working effectively with MySQL across platforms.^[5]^[4]^[2]^[6]

⁂ Resource to Learn more

1. https://www.w3schools.com/MySQL/default.asp

2. https://www.youtube.com/watch?v=a3HJnbYhXUc

3. https://www.w3schools.com/mysql/mysql_install_windows.asp

4. https://www.hostitsmart.com/manage/knowledgebase/444/create-mysql-database-by-command-line.html

5. https://dev.mysql.com/doc/mysql-getting-started/en/

6. https://www.hostarmada.com/blog/mysql-database-tutorial/

7. https://dev.mysql.com/doc/refman/9.0/en/creating-database.html

8. https://world.siteground.com/tutorials/php-mysql/create-user-database/

9. https://estuary.dev/blog/mysql-connectors/

10. https://www.youtube.com/watch?v=wgRwITQHszU

11. https://www.w3schools.com/mysql/mysql_create_db.asp

12. https://www.microfocus.com/documentation/idol/IDOL_12_0/MediaServer/Guides/html/English/Content/Getting_Started/Configure/_TRN_Set_up_MySQL.htm

13. https://www.youtube.com/watch?v=50CQoMs4vRo

14. https://www.prisma.io/dataguide/mysql/setting-up-a-local-mysql-database

15. https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/

16. https://www.geeksforgeeks.org/mysql/how-to-install-mysql-in-windows/

17. https://www.geeksforgeeks.org/mysql/mysql-create-database-statement/

18. https://www.youtube.com/watch?v=OYcxu11HkOM

19. https://docs.oracle.com/en/java/java-components/advanced-management-console/2.16/install-guide/mysql-database-installation-and-configuration-advanced-management-console.html

20. https://blog.devart.com/mysql-tutorial.html

------------------

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.^[1]^[2]^[3]

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.^[4]^[5]

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.^[6]^[1]

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.^[7]^[8]

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.^[3]

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.^[9]^[1]

Exiting MySQL

To leave the MySQL command interface:

exit;

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.^[4]^[1]^[3]

⁂

1. https://www.devart.com/dbforge/mysql/studio/mysql-commands.html

2. https://www.interserver.net/tips/kb/manage-mysql-database-command-line/

3. https://serverpilot.io/docs/guides/mysql/cli/

4. https://www.hostarmada.com/blog/how-to-create-database-in-mysql-command-line/

5. https://www.liquidweb.com/blog/create-a-mysql-database-on-linux-via-command-line/

6. https://www.interviewbit.com/blog/mysql-commands/

7. https://www.w3schools.com/mysql/mysql_sql.asp

8. https://www.bytebase.com/reference/mysql/how-to/top-mysql-commands-with-examples/

9. https://kb.hosting.com/docs/managing-mysql-databases-and-users-from-the-command-line

10. https://dev.mysql.com/doc/mysql-getting-started/en/

11. https://www.geeksforgeeks.org/mysql/mysql-tutorial/

12. https://www.youtube.com/watch?v=50CQoMs4vRo

13. https://dev.mysql.com/doc/en/mysql.html

14. https://www.youtube.com/watch?v=wgRwITQHszU

15. https://www.w3schools.com/MySQL/default.asp

16. https://www.geeksforgeeks.org/dbms/mysql-common-mysql-queries/

17. https://stackoverflow.com/questions/5287024/how-to-select-a-mysql-database-through-cli

18. https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html

19. https://www.plesk.com/blog/various/manage-mysql-databases-from-the-command-line/

20. https://www.hostitsmart.com/manage/knowledgebase/444/create-mysql-database-by-command-line.html

-----0-------1----------

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.^[3]

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

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

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

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).^[4]^[1]^[3]

· Row/column selection: A[, :] or A[:, ].^[4]^[1]^[3]

· Useful for reordering or sampling without loops.^[3]

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.

⁂ Other Source to Read [copy paste the link]

1. http://www.iimchyderabad.com/Material/introduction-to-python-programming.pdf

2. https://api.pageplace.de/preview/DT0400.9781351013222_A35584700/preview-9781351013222_A35584700.pdf

3. https://github.com/gowrishankarnath/Introduction_To_Python_Programming

4. https://www.taylorfrancis.com/books/mono/10.1201/9781351013239/introduction-python-programming-gowrishankar-veena