Creating a Database and Table ,executing queries - MySQL Lab Exercise

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', 'Doe', '2000-01-15', 'john.doe@example.com', 'Male', '2018-09-01'),
    ('John', 'Smith', '2001-02-20', 'jane.smith@example.com', 'Female', '2019-09-01'),
    ('Alice', 'Johnson', '1999-03-30', 'alice.johnson@example.com', 'Female', '2017-09-01'),
    ('Bob', 'Brown', '2002-04-10', 'bob.brown@example.com', 'Male', '2020-09-01');

    Query Examples
    1. Retrieve all students
    SELECT * FROM students;

    2.Retrieve students enrolled in 2019
    SELECT * FROM students WHERE YEAR(enrollment_date) = 2019;

    3. Retrieve the count of students by gender
    SELECT gender, COUNT(*) AS count FROM students GROUP BY gender;

    4.Retrieve students whose last name is 'Doe'
    SELECT * FROM students WHERE last_name = 'Doe';

    5. Retrieve students ordered by their birthdate
    SELECT * FROM students ORDER BY birthdate;

    6. Update the email address of a student
    UPDATE students SET email = 'new.email@example.com' WHERE id = 1;
    verify this
    SELECT * FROM students WHERE id=1;

    7. Delete a student record by ID
    DELETE FROM students WHERE id = 4;

    8. Find the youngest student
    SELECT * FROM students
    ORDER BY birthdate DESC
    LIMIT 1;

    9.Find the oldest student
    SELECT * FROM students
    ORDER BY birthdate ASC
    LIMIT 1;

    10.Find the average age of the students
    SELECT AVG(TIMESTAMPDIFF(YEAR, birthdate, CURDATE())) AS average_age FROM students;

    11.List students who enrolled in the last 30 days
    SELECT * FROM students
    WHERE enrollment_date >= CURDATE() - INTERVAL 30 DAY;

    12. Retrieve students who have been enrolled for more than 3 years
    SELECT gender, COUNT(*) AS total_students
    FROM students
    GROUP BY gender;

    13. Count the number of students enrolled each year
    SELECT YEAR(enrollment_date) AS year, COUNT(*) AS count
    FROM students
    GROUP BY year;

    14. Retrieve students whose email domain is 'example.com'
    SELECT * FROM students
    WHERE email LIKE '%@example.com';

    15.Retrieve students with birthdates in a specific month (e.g., January)
    SELECT * FROM students
    WHERE MONTH(birthdate) = 1;

    16. Retrieve students who have not provided an email address (assuming email can be NULL)
    S6LECT * FROM students
    WHERE email IS NULL;

    17. List students who were enrolled between two specific dates
    SELECT * FROM students
    WHERE enrollment_date BETWEEN '2018-01-01' AND '2019-12-31';

    18. Retrieve students with a birthdate in the 2000s
    SELECT * FROM students
    WHERE birthdate BETWEEN '2000-01-01' AND '2009-12-31';

    19.Find Students with Common First Names
    SELECT first_name, COUNT(*) AS count
    FROM students
    GROUP BY first_name
    HAVING count > 1;

    20.Retrieve the Count of Students by Enrollment Year

    This query will group the students by the year they enrolled and count how many students enrolled in each year.
    SELECT YEAR(enrollment_date) AS enrollment_year, COUNT(*) AS student_count
    FROM students
    GROUP BY YEAR(enrollment_date)
    ORDER BY enrollment_year;

    UPDATE queries
    1. Update a student's email address
    UPDATE students
    SET email = 'new.email@example.com'
    WHERE id = 1;

    2. Update multiple fields for a student
    UPDATE students
    SET first_name = 'Jonathan', last_name = 'Doe', email = 'jonathan.doe@example.com'
    WHERE id = 1;

    3. Update the enrollment date for students enrolled before a specific date
    UPDATE students
    SET enrollment_date = '2022-01-01'
    WHERE enrollment_date < '2020-01-01';

    4. Update the gender of students with a specific last name
    UPDATE students
    SET gender = 'Other'
    WHERE last_name = 'Smith';

    5.Increment the year of birthdate for all students (example: correcting an error)
    UPDATE students
    SET birthdate = DATE_ADD(birthdate, INTERVAL 1 YEAR);

    6. Update the email domain for all students (example: changing company domain)
    UPDATE students
    SET email = REPLACE(email, '@oldcompany.com', '@newcompany.com');

    7. Update the first name of students with a specific email domain
    UPDATE students
    SET first_name = 'John'
    WHERE email LIKE '%@example.com';

    8. Set the email to NULL for students without an email address (example: if the email column was initially set with default values)
    UPDATE students
    SET email = NULL
    WHERE email = '';

    9.Update students who enrolled after a certain date to a new gender
    UPDATE students
    SET gender = 'Female'
    WHERE enrollment_date > '2021-01-01';

    10. Update multiple students' last names in a single query
    UPDATE students
    SET last_name = CASE
        WHEN id = 1 THEN 'Anderson'
        WHEN id = 2 THEN 'Brown'
        WHEN id = 3 THEN 'Clark'
        ELSE last_name
    END
    WHERE id IN (1, 2, 3);

    DELETE Queries
    1. Delete a record by a specific id
    DELETE FROM students
    WHERE id = 1;

    2. Delete records by a specific last_name
    DELETE FROM students
    WHERE last_name = 'Doe';

    3. Delete records for students enrolled before a certain date
    DELETE FROM students
    WHERE enrollment_date < '2020-01-01';

    4. Delete records for students with a specific email domain
    DELETE FROM students
    WHERE email LIKE '%@example.com';

    5. Delete records for students of a specific gender
    DELETE FROM students
    WHERE gender = 'Female';

    6.Delete records for students born in a specific year
    DELETE FROM students
    WHERE YEAR(birthdate) = 2000;

    7. Delete all records from the table
    DELETE FROM students;
    Note: This will delete all records from the students table but will keep the table structure intact. To truncate the table and reset any auto-increment counters:
    TRUNCATE TABLE students;

    8. Delete records using a subquery (e.g., delete students who are not enrolled in the last 5 years)
    DELETE FROM students
    WHERE enrollment_date < (SELECT DATE_SUB(CURDATE(), INTERVAL 5 YEAR));

    9. Delete duplicate records based on a combination of columns

    If you want to delete duplicate records based on a combination of first_name, last_name, and birthdate while keeping the record with the lowest id, you can use:

    DELETE FROM students

    WHERE id NOT IN (

        SELECT MIN(id)

        FROM students

        GROUP BY first_name, last_name, birthdate

    );


    Safety Considerations
    Backup Data: Before executing DELETE queries, it's a good practice to backup your data.
    Test Queries: Test your DELETE queries using SELECT to ensure they affect only the intended rows:
    Use Transactions: For critical deletions, use transactions to ensure you can rollback if necessary
    START TRANSACTION;
    DELETE FROM students WHERE condition;
    -- If everything is fine
    COMMIT;
    -- If you want to rollback
    ROLLBACK;

    ALTER TABLE queries
    These ALTER TABLE queries allow you to modify the schema of your students table, making it more flexible and suitable for your evolving data requirements.

    Adding Columns
    1. Add a phone_number column to the students table
    ALTER TABLE students
    ADD COLUMN phone_number VARCHAR(15);

    2. Add a address column to the students table
    ALTER TABLE students
    ADD COLUMN address VARCHAR(255);

    Modifying Columns
    3. Modify the phone_number column to increase its length
    ALTER TABLE students
    MODIFY COLUMN phone_number VARCHAR(20);

    4. Change the birthdate column to birth_date
    ALTER TABLE students
    CHANGE COLUMN birthdate birth_date DATE;

    5. Change the data type of email column to increase its length
    ALTER TABLE students
    MODIFY COLUMN email VARCHAR(150);

    6. Drop the address column from the students table
    ALTER TABLE students
    DROP COLUMN address;


    Renaming Columns
    7. Rename the enrollment_date column to date_of_enrollment
    ALTER TABLE students
    CHANGE COLUMN enrollment_date date_of_enrollment DATE;

    Renaming the Table
    8. Rename the students table to student_records
    ALTER TABLE students
    RENAME TO student_records;


    Adding Constraints
    9. Add a UNIQUE constraint to the email column
    ALTER TABLE students
    ADD CONSTRAINT unique_email UNIQUE (email);

    10. Add a CHECK constraint to ensure birth_date is in the past
    ALTER TABLE students
    ADD CONSTRAINT check_birth_date CHECK (birth_date < CURDATE());

    DROP TABLE and DATABASE

    Dropping a Table

    Drop the students table
    DROP TABLE students;

    Drop the students table if it exists
    DROP TABLE IF EXISTS students;

    Dropping a Database

    Drop the school database
    DROP DATABASE school;

    Drop the school database if it exists
    DROP DATABASE IF EXISTS school;

    Comments

    Post a Comment

    Popular posts from this blog

    KTU DBMS LAB CSL 333 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 ***********************
    Read more

    Procedures in PL/SQL

    Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
    Read more

    Cursors in PL/SQL

    In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
    Read more