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

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11

Preamble: The Database Management Systems course is intended to impart the elementary concepts of a database management system to students and equip them to design and implement a database application based on those concepts. This course helps the learners to get practical exposure on database creation, SQL queries creation, transaction processing and NoSQL & MongoDB based operations. The course enables the students to create, manage and administer the databases, develop necessary tools for the design and development of the databases, and to understand emerging technologies to handle Big Data. Prerequisite: A sound knowledge of the basics of relational DBMS. Course Outcomes: After the completion of the course the student will be able to CO1 Design database schema for a given real world problem-domain using standard design and modeling approaches. (Cognitive Knowledge Level: Apply) CO2 Construct queries using SQL for database creation, interaction, modification, and updation. (Cog

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate databases. It is widely used for web applications and is a central component of the LAMP stack (Linux, Apache, MySQL, PHP/Python/Perl). Key Features of MySQLOpen Source:  MySQL is free to use under the GNU General Public License (GPL). Cross-Platform: It can be installed on various operating systems including Linux, Windows, and macOS. High Performance: MySQL is designed for high-speed transactions and supports a large number of concurrent users. Scalability: Suitable for both small applications and large-scale enterprise solutions. Security: Provides robust security features to protect data. Replication: Supports database replication to increase availability and performance. Installing MySQL On Linux sudo apt-get update  sudo apt-get install mysql-server On Windows Download the MySQL installer from the official website and follow the installation wi

An RDBMS (Relational Database Management System) is software that manages a relational database.  Structured Data Storage: An RDBMS organizes data into tables, with rows and columns. Each table represents a specific type of data (e.g., customers, orders). Columns correspond to attributes (e.g., customer name, order date). Rows contain records or tuples (specific data instances). Relationships: RDBMS allows you to establish links between tables using common attributes. For example, a Customer table can be related to an Order table via a shared customer ID. Primary keys (unique identifiers) and foreign keys (references to other tables) create these relationships. Example: Imagine a Customer table with columns like Customer ID, Name, and Address. An Order table might have columns for Order ID, Customer ID, and Order Date. By linking the Customer ID, you can query both tables to create reports or use the data for applications. Key Concepts Tables : Data is organized into tables, which cons

MySQL built-in functions are pre-defined functions provided by MySQL to perform various operations on data. These functions are categorized into different types based on their functionality: String Functions CONCAT() : Concatenates two or more strings. SUBSTRING() : Extracts a substring from a string. LENGTH() : Returns the length of a string. UPPER() : Converts a string to uppercase. LOWER() : Converts a string to lowercase. TRIM() : Removes leading and trailing spaces from a string. REPLACE() : Replaces occurrences of a specified string with another string. Numeric Functions ABS() : Returns the absolute value of a number. CEIL()  or  CEILING() : Rounds a number up to the nearest integer. FLOOR() : Rounds a number down to the nearest integer. ROUND() : Rounds a number to a specified number of decimal places. MOD() : Returns the remainder of a division operation. POWER() : Raises a number to the power of another number. Date and Time Functions CURDATE() : Returns the current date. CURT

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It is used to perform tasks such as querying data, updating records, inserting data, and deleting records. Here are some key points about SQL: Data Querying : SQL allows users to query data from a database using the SELECT statement, which can retrieve specific data based on given criteria. Data Manipulation : SQL provides commands for inserting ( INSERT ) , updating ( UPDATE ) , and deleting ( DELETE ) data within database tables. Data Definition : SQL includes commands for defining the structure of databases and tables, such as CREATE TABLE , ALTER TABLE , and DROP TABLE . Data Control : SQL has features for controlling access to data within a database, including granting and revoking permissions using GRANT and REVOKE . SQL is widely used in various database systems, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Databa

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

DCL (Data Control Language) commands in MySQL are used to control access to data within the database. They primarily deal with the permissions and security of the database objects. The two main DCL commands in MySQL are: GRANT : This command is used to give privileges to users. REVOKE : This command is used to remove privileges from users. GRANT The GRANT command is used to assign privileges to users. Privileges can be on various levels: global, database, table, column, or stored procedure GRANT privilege_type [(column_list)]     ON [object_type] privilege_level     TO user_or_role [auth_option]     [WITH GRANT OPTION]; Examples: Grant all privileges on a database: GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host'; Grant specific privileges on a table: GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host'; Grant privileges with the option to grant the same privileges to others: GRANT ALL PRIVILEGES ON database_name.* TO

In MySQL, GROUP BY, HAVING, and ORDER BY are clauses used in SQL queries to group, filter, and sort the result set. Here’s an explanation of each: GROUP BY The GROUP BY clause groups rows that have the same values into summary rows, like "find the total quantity sold for each product". It is often used with aggregate functions such as SUM(), AVG(), MAX(), MIN(), and COUNT(). HAVING The HAVING clause is used to filter groups of rows created by the GROUP BY clause. It is similar to the WHERE clause but is used with aggregated data. ORDER BY The ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts in ascending order, but you can specify DESC to sort in descending order Example: -- Create Products table CREATE TABLE Products (     ProductID INT AUTO_INCREMENT PRIMARY KEY,     ProductName VARCHAR(100) NOT NULL ); -- Insert sample data into Products INSERT INTO Products (ProductName) VALUES ('Product A'), -- ProductID 1 (