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

    KTU DBMS LAB CSL 333 Syllabus and Experiments

     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
    Read more