# Worked-out Example #### Software Installation * Install mySQL for Windows or Ubuntoo from [here](https://dev.mysql.com/downloads/mysql/). Please ensure that you download sakila and world sample databases offered by mySQL as part of core installer. * Install mySQL Workbench for Windows or Ubuntoo from [here](https://dev.mysql.com/downloads/workbench/). This a GUI for mySQL database software that helps the database user to review and analysis the datasets available in the database. #### Example Follow below commands and try them on you local machine! Create a database named "Campus" in MySQL, you can use the following SQL query: ```sql CREATE DATABASE IF NOT EXISTS Campus; ``` ```sql SHOW DATABASES; ``` This will list all databases, including "Campus" if it was successfully created. Create the tables "Students", "Faculty", and "Course" under the "Campus" database in MySQL, you can use the following SQL queries. Each table includes relevant columns and data types. ```sql USE Campus; CREATE TABLE IF NOT EXISTS Students ( StudentID INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DateOfBirth DATE, Email VARCHAR(100) UNIQUE ); CREATE TABLE IF NOT EXISTS Faculty ( FacultyID INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Department VARCHAR(100), Email VARCHAR(100) UNIQUE ); CREATE TABLE IF NOT EXISTS Course ( CourseID INT AUTO_INCREMENT PRIMARY KEY, CourseName VARCHAR(100) NOT NULL, Credits INT CHECK (Credits > 0), FacultyID INT, FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID) ); ``` #### Explanation * **USE Campus;**: This command sets the context to the "Campus" database. * **AUTO\_INCREMENT**: Automatically generates a unique value for each new record. * **PRIMARY KEY**: Uniquely identifies each record in the table. * **VARCHAR(n)**: A variable character string with a maximum length of `n`. * **DATE**: Data type for storing date values. * **UNIQUE**: Ensures that all values in a column are different. * **CHECK (Credits > 0)**: Ensures that credits must be greater than zero. * **FOREIGN KEY**: Establishes a relationship between the "Course" and "Faculty" tables. These queries will create the specified tables with appropriate constraints and relationships. Insert 10 records into the "Students", "Faculty", and "Course" tables under the "Campus" database in MySQL, you can use the following SQL queries. Each query will insert multiple rows at once. ```sql USE Campus; -- Inserting records into Students table INSERT INTO Students (FirstName, LastName, DateOfBirth, Email) VALUES ('John', 'Doe', '2000-05-15', 'john.doe@example.com'), ('Jane', 'Smith', '2001-08-22', 'jane.smith@example.com'), ('Emily', 'Johnson', '1999-12-30', 'emily.johnson@example.com'), ('Michael', 'Brown', '2002-01-10', 'michael.brown@example.com'), ('Sarah', 'Davis', '2000-03-25', 'sarah.davis@example.com'), ('David', 'Wilson', '1998-07-14', 'david.wilson@example.com'), ('Laura', 'Garcia', '2001-11-05', 'laura.garcia@example.com'), ('James', 'Martinez', '1999-09-19', 'james.martinez@example.com'), ('Linda', 'Hernandez', '2000-02-28', 'linda.hernandez@example.com'), ('Robert', 'Lopez', '2002-04-18', 'robert.lopez@example.com'); -- Inserting records into Faculty table INSERT INTO Faculty (FirstName, LastName, Department, Email) VALUES ('Dr. Alice', 'White', 'Computer Science', 'alice.white@university.edu'), ('Prof. Bob', 'Green', 'Mathematics', 'bob.green@university.edu'), ('Dr. Carol', 'Black', 'Physics', 'carol.black@university.edu'), ('Prof. Dan', 'Blue', 'Chemistry', 'dan.blue@university.edu'), ('Dr. Eve', 'Red', 'Biology', 'eve.red@university.edu'); -- Inserting records into Course table INSERT INTO Course (CourseName, Credits, FacultyID) VALUES ('Introduction to Programming', 3, 1), ('Calculus I', 4, 2), ('Physics I', 4, 3), ('Organic Chemistry', 4, 4), ('Biology 101', 3, 5); ``` #### Explanation * **USE Campus;**: Sets the context to the "Campus" database. * **INSERT INTO**: Specifies which table to insert data into. * **VALUES**: Contains the data to be inserted. * Each set of parentheses represents a new record to be inserted into the respective table. These queries will successfully insert 10 records into the "Students" table, 5 records into the "Faculty" table, and 5 records into the "Course" table. Adjust the email addresses and other details as necessary for your specific use case. Create a "Student\_Registration" table under the "Campus" database that contains columns associated with the "Students" and "Course" tables, along with course registration details, you can use the following SQL query: ```sql USE Campus; CREATE TABLE IF NOT EXISTS Student_Registration ( RegistrationID INT AUTO_INCREMENT PRIMARY KEY, StudentID INT, CourseID INT, RegistrationDate DATE DEFAULT '2025-01-26', Status ENUM('Registered', 'Dropped', 'Completed') DEFAULT 'Registered', FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ); ``` #### Explanation * **USE Campus;**: Sets the context to the "Campus" database. * **CREATE TABLE IF NOT EXISTS**: Creates the table only if it does not already exist. * **RegistrationID INT AUTO\_INCREMENT PRIMARY KEY**: A unique identifier for each registration record, automatically incremented. * **StudentID INT**: Foreign key referencing the "Students" table. * **CourseID INT**: Foreign key referencing the "Course" table. * **RegistrationDate DATE DEFAULT '2025-01-26'**: The date of registration, defaulting to January 26, 2025. * **Status ENUM('Registered', 'Dropped', 'Completed') DEFAULT 'Registered'**: A status field indicating the current state of the registration, defaulting to 'Registered'. * **FOREIGN KEY** constraints ensure referential integrity by linking to corresponding records in the "Students" and "Course" tables. This query will successfully create the "Student\_Registration" table with the specified columns and relationships. Insert 10 records into the "Student\_Registration" table under the "Campus" database, you can use the following SQL query. This query assumes that you have valid `StudentID` and `CourseID` values corresponding to the records in the "Students" and "Course" tables. ```sql USE Campus; INSERT INTO Student_Registration (StudentID, CourseID, RegistrationDate, Status) VALUES (1, 1, '2025-01-26', 'Registered'), (2, 2, '2025-01-26', 'Registered'), (3, 3, '2025-01-26', 'Registered'), (4, 4, '2025-01-26', 'Registered'), (5, 5, '2025-01-26', 'Completed'), (1, 2, '2025-01-26', 'Dropped'), (2, 3, '2025-01-26', 'Registered'), (3, 4, '2025-01-26', 'Completed'), (4, 5, '2025-01-26', 'Registered'), (5, 1, '2025-01-26', 'Registered'); ``` #### Explanation * **USE Campus;**: Sets the context to the "Campus" database. * **INSERT INTO Student\_Registration**: Specifies the table into which data will be inserted. * **(StudentID, CourseID, RegistrationDate, Status)**: Lists the columns to which values will be assigned. * **VALUES**: Contains multiple sets of values for each record being inserted. Each record includes: * **StudentID**: Corresponding ID from the "Students" table. * **CourseID**: Corresponding ID from the "Course" table. * **RegistrationDate**: Set to January 26, 2025. * **Status**: Indicates whether the registration is registered, dropped, or completed. This query will insert 10 records into the "Student\_Registration" table as specified. Adjust `StudentID` and `CourseID` values as necessary based on your existing data. Now add a new column called "semester" to the "Student\_Registration" table under the "Campus" database, you can use the following SQL query: ```sql USE Campus; ALTER TABLE Student_Registration ADD COLUMN semester VARCHAR(10) AFTER Status; ``` #### Explanation * **USE Campus;**: Sets the context to the "Campus" database. * **ALTER TABLE Student\_Registration**: Specifies the table that will be modified. * **ADD COLUMN semester VARCHAR(10)**: Adds a new column named "semester" with a data type of `VARCHAR` that can hold up to 10 characters. * **AFTER Status**: Positions the new column after the existing "Status" column in the table. This query will successfully add the "semester" column to the "Student\_Registration" table. Adjust the data type and length as necessary based on your specific requirements. Now retrieve details from the "Students," "Course," and "Student\_Registration" tables in the "Campus" database using a JOIN query, you can use the following SQL statement: ```sql USE Campus; SELECT s.StudentID, s.FirstName AS StudentFirstName, s.LastName AS StudentLastName, s.Email AS StudentEmail, c.CourseID, c.CourseName, c.Credits, sr.RegistrationDate, sr.Status AS RegistrationStatus, sr.semester FROM Students s INNER JOIN Student_Registration sr ON s.StudentID = sr.StudentID INNER JOIN Course c ON sr.CourseID = c.CourseID; ``` #### Explanation * **USE Campus;**: Sets the context to the "Campus" database. * **SELECT**: Specifies the columns to retrieve from the tables. * `s.StudentID`, `s.FirstName`, `s.LastName`, `s.Email`: Columns from the "Students" table. * `c.CourseID`, `c.CourseName`, `c.Credits`: Columns from the "Course" table. * `sr.RegistrationDate`, `sr.Status`, `sr.semester`: Columns from the "Student\_Registration" table. * **FROM Students s**: Indicates that the main table is "Students," with an alias `s`. * **INNER JOIN Student\_Registration sr ON s.StudentID = sr.StudentID**: Joins the "Student\_Registration" table on the matching `StudentID`. * **INNER JOIN Course c ON sr.CourseID = c.CourseID**: Joins the "Course" table on the matching `CourseID`. This query will display a comprehensive view of student details, course details, and registration details for all registered students in the "Campus" database as of January 26, 2025. Adjust any column names or aliases as necessary based on your specific schema. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://sai11101989.gitbook.io/iss_workbook/databases/structured-databases/worked-out-example.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.