KTU DBMS LAB CSL 333 - 2019 SCHEME BTECH S5 - Dr Binu V P

Certainly! MySQL supports various data types that you can use to define columns in your tables. Here's a list of commonly used data types in MySQL: Numeric Data Types 1. INT or INTEGER: A standard integer that can store values from -2147483648 to 2147483647. - Example: `INT` 2. TINYINT A very small integer that can store values from -128 to 127. - Example: `TINYINT` 3. SMALLINT: A small integer that can store values from -32768 to 32767. - Example: `SMALLINT` 4. MEDIUMINT: medium-sized integer that can store values from -8388608 to 8388607. - Example: `MEDIUMINT` 5.BIGINT:A large integer that can store values from -9223372036854775808 to 9223372036854775807. - Example: `BIGINT` 6. DECIMAL or NUMERIC: Exact numeric data type where you specify the precision (total number of digits) and scale (number of digits after the decimal point). - Example: `DECIMAL(10, 2)` (allows up to 10 digits with 2 decimal places) 7. FLOAT: A floating-point number that stores approximate numeric values. -

In MySQL, JOIN operations are used to combine rows from two or more tables based on a related column between them. Here's an explanation of the different types of joins: 1. INNER JOIN The INNER JOIN keyword selects records that have matching values in both tables. SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column; Example Assume we have two tables, students and enrollments. SELECT students.id, students.first_name, students.last_name, enrollments.course_id FROM students INNER JOIN enrollments ON students.id = enrollments.student_id; 2. LEFT JOIN (or LEFT OUTER JOIN) The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match. SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column; Example SELECT students.id, students.first_name, students.last_name, enrollments.course_id FROM stude

In MySQL, constraints are rules enforced on data columns to ensure the integrity, validity, and reliability of the data within the database. Here are the various constraints that can be applied to tables and columns: Column-Level Constraints 1.NOT NULL: Ensures that a column cannot have a NULL value. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50 ) NOT NULL,     last_name VARCHAR(50) NOT NULL ); 2.UNIQUE : Ensures that all values in a column are unique. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     email VARCHAR(100) UNIQUE ); 3.PRIMARY KEY : Uniquely identifies each record in a table. It combines NOT NULL and UNIQUE . CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL ); 4.AUTO_INCREMENT : Automatically generates a unique number for the column. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&

  Let’s create two tables connected by a foreign key and then explore some sample queries. First, we’ll create two tables: Customers and Products. The Customers table will have an id (as the primary key), name, and age. The Products table will have a customer_id (as a foreign key referencing the id column in the Customers table), and a name. CREATE TABLE Customers (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(100) NOT NULL,     age INT NOT NULL ); CREATE TABLE Products (     id INT AUTO_INCREMENT PRIMARY KEY,     customer_id INT,     name VARCHAR(255) NOT NULL,     FOREIGN KEY (customer_id) REFERENCES Customers(id) ); In this example, the customer_id column in the Products table references the id column in the Customers table. This establishes a relationship between the two tables. INSERT INTO Customers (name, age) VALUES     ('John Doe', 30),     ('Jane Smith', 25),     ('Mike Johnson', 35),     ('Alice Brown', 28); INSERT INTO Products

 To insert data from a CSV file into a MySQL database, you can use MySQL's LOAD DATA INFILE statement. This method is efficient for bulk data insertion from CSV files. Here’s a step-by-step guide on how to do it: Step-by-Step Guide 1. Prepare Your CSV File Ensure your CSV file is properly formatted and accessible from your MySQL server. For example, let's assume your CSV file ( books.csv ) looks like this: title,author,publisher,year_of_publication,isbn,category Book A,Author A,Publisher X,2000,1234567890,Fiction Book B,Author B,Publisher Y,2005,2345678901,Non-fiction Book C,Author C,Publisher Z,2010,3456789012,Science 2. Create a Table in MySQL Before loading data, you need to have a table in your MySQL database that matches the structure of your CSV file. Here's an example of how you might create a Books table: CREATE TABLE Books (     title VARCHAR(255) NOT NULL,     author VARCHAR(255) NOT NULL,     publisher VARCHAR(255),     year_of_publication INT,     isbn VARCHAR

 Aggregation functions in MySQL are used to perform calculations on multiple values and return a single value. These functions are commonly used with the GROUP BY clause. Here are some of the most commonly used aggregation functions in MySQL: 1. COUNT() Counts the number of rows that match a specified condition. SELECT COUNT(*) FROM students; 2. SUM() Calculates the sum of a set of values. SELECT SUM(score) FROM scores; 3. AVG() Calculates the average value of a set of values. SELECT AVG(score) FROM scores; 4. MIN() Finds the minimum value in a set of values. SELECT MIN(score) FROM scores; 5. MAX() Finds the maximum value in a set of values. SELECT MAX(score) FROM scores; 6. GROUP_CONCAT() Concatenates values from multiple rows into a single string. SELECT GROUP_CONCAT(course_name) FROM courses; 7.VARIANCE() Returns the variance of a numeric column. SELECT VARIANCE(salary) FROM employees; 8.STDDEV() Returns the standard deviation of a numeric column. SELECT STDDEV(salary) FROM employe