My SQL - 6.11.24 PDF

Day 3: DBMS - MYSQL Date: 6.11.24 1. Relations, Tuples, Attributes  Relations: A relation is a table in a relational database. o Example: A Student table is a relation in a university database.  Tuples: A tuple i...

Day 3: DBMS - MYSQL Date: 6.11.24 1. Relations, Tuples, Attributes  Relations: A relation is a table in a relational database. o Example: A Student table is a relation in a university database.  Tuples: A tuple is a row in a table, representing a single record. o Example: A row with values (101, "John Doe", "Computer Science") in the Student table is a tuple.  Attributes: Attributes are the columns in a table, representing properties of the relation. o Example: Attributes in the Student table could include StudentID, Name, and Major. 2. Primary Key, Foreign Key, Candidate Key, Super Key  Primary Key: A unique identifier for each tuple in a relation. No two rows can have the same primary key, and it cannot be NULL. o Example: StudentID in the Student table is a primary key because it uniquely identifies each student.  Foreign Key: An attribute that creates a link between two tables. It refers to the primary key in another table, establishing a relationship. o Example: CourseID in an Enrollments table can be a foreign key that references the CourseID primary key in a Courses table.  Candidate Key: An attribute (or set of attributes) that could be a primary key, as it uniquely identifies a tuple. o Example: In the Student table, if Email is also unique for each student, then both StudentID and Email are candidate keys.  Super Key: Any set of attributes that uniquely identifies a tuple. Every primary key is a super key, but super keys can contain extra attributes. o Example: {StudentID, Name} is a super key in the Student table if StudentID alone is unique. 3. Integrity Constraints: Entity and Referential Integrity  Entity Integrity: Ensures that each table has a primary key, and no part of the primary key can be NULL. This guarantees that each row can be uniquely identified. o Example: In the Student table, StudentID (primary key) must always have a value (cannot be NULL), ensuring each student is identifiable.  Referential Integrity: Ensures that a foreign key value must either be NULL or match an existing primary key in the referenced table. This maintains the relationships between tables. o Example: In the Enrollments table, CourseID must match a CourseID in the Courses table, ensuring that students can only enroll in existing courses.  Keys types: 1. Super Key  Definition: A set of one or more attributes that can uniquely identify each record in a table.  Example Table: Employee table with columns: EmployeeID, Email, Phone.  Super Key Example: {EmployeeID}, {Email}, and {EmployeeID, Phone} are super keys because they uniquely identify records. EmployeeID Email Phone 1 [email protected] 555-1234 2 [email protected] 555-5678 2. Candidate Key  Definition: A minimal super key, which is the smallest subset of attributes that can uniquely identify records.  Example Table: Same Employee table as above.  Candidate Key Example: {EmployeeID} and {Email} are candidate keys, as each can uniquely identify an employee. 3. Primary Key  Definition: A candidate key chosen to uniquely identify records in a table.  Example Table: Employee table.  Primary Key Example: {EmployeeID} is chosen as the primary key to ensure each record is unique. 4. Composite Key  Definition: A key that uses multiple attributes to uniquely identify a record.  Example Table: OrderDetails table with columns: OrderID, ProductID, Quantity.  Composite Key Example: {OrderID, ProductID} uniquely identifies each row in the table. OrderID ProductID Quantity 1 101 2 1 102 1 2 101 3 5. Alternate Key  Definition: A candidate key not selected as the primary key.  Example Table: Same Employee table.  Alternate Key Example: If EmployeeID is the primary key, then Email is an alternate key. 6. Unique Key  Definition: Enforces uniqueness on a column or set of columns, allowing NULL values.  Example Table: User table with columns: UserID, Username, Email.  Unique Key Example: Username can be a unique key to ensure each username is unique. UserID Username Email 1 johndoe [email protected] 2 janedoe [email protected] 7. Foreign Key  Definition: A key that creates a relationship between two tables by referring to the primary key of another table.  Example Table: Orders table with OrderID, CustomerID, and OrderDate.  Foreign Key Example: CustomerID is a foreign key referring to CustomerID in a Customers table. OrderID CustomerID OrderDate 1001 1 2023-01-01 1002 2 2023-02-15 8. Surrogate Key  Definition: An artificially created key with no real-world meaning, often auto-incremented.  Example Table: Product table with columns: ProductID, ProductName.  Surrogate Key Example: ProductID is a surrogate key used only for unique identification. ProductID ProductName 1 Laptop 2 Smartphone 9. Natural Key  Definition: A key with real-world relevance, naturally identifying a record.  Example Table: LibraryBooks table with columns: ISBN, Title, Author.  Natural Key Example: ISBN is a natural key as it is a globally unique identifier for books. ISBN Title Author 978-123456789 Database Systems A. Author 978-987654321 Algorithms B. Writer Description Properties Example Key May contain A set of one or additional attributes In a Student table with more attributes beyond the columns StudentID, Email, 1. Super Key that can uniquely minimum needed for and PhoneNumber: identify a record uniqueness. Includes StudentID or {Email, in a table. candidate and PhoneNumber} primary keys. Minimal unique A minimal super identifier. Can have key that has no In the Student table: Candidate multiple candidate 2. unnecessary StudentID and Email (both Key keys; one can be attributes. Only uniquely identify students) chosen as the essential columns. primary key. A specific candidate key Unique across the StudentID in the Student chosen to 3. Primary Key table, cannot be table; every student has a uniquely identify NULL. unique StudentID. each record in a table. A key composed Useful when a single In an OrderDetails table of two or more attribute cannot with columns OrderID and Composite 4. attributes that ensure uniqueness. ProductID: {OrderID, Key together uniquely Can be a candidate ProductID} as a composite identify a record. or super key. key A candidate key In the Student table: Email Acts as an that is not chosen could be an alternate key if 5. Alternate Key alternative unique as the primary StudentID is the primary identifier. key. key. Ensures Allows NULL Email in a User table; all uniqueness of a values, but non- emails must be unique, but 6. Unique Key column or group NULL entries must some rows can have NULL of columns. be unique. (optional field).  Establishes a relationship Ensures referential CustomerID in an Orders between two integrity. Can have 7. Foreign Key table referring to CustomerID tables by referring NULL values if in a Customers table. to the primary key allowed. of another table. An artificial key No business with no real- meaning, purely for In a Product table, a column Surrogate world meaning, unique ProductID (an auto- 8. Key often an auto- identification. Often generated integer) with no incrementing used as a primary connection to product details. number. key. A key with a logical, business- In an Employee table: SSN Typically derived 9. Natural Key related meaning (Social Security Number) or from real-world data. and real-world ISBN in a Book table. relevance. Homework: 1. Write a query to find employees with a salary greater than 50,000. 2. Write a query to select employees who joined after January 1, 2020. 3. Write a query to select employees with names starting with the letter 'A'. 4. Create an ER diagram for an inventory management system with entities like Product, Supplier, Order, and Warehouse. Show relationships like Supplies (Supplier to Product), Stores (Warehouse to Product), and Contains (Order to Product). Note: Refer to this link for an ER diagram. https://youtube.com/playlist?list=PLlGqj2KrYnpSIavv7ZdRkD8VWUwwZ2dF2&si=ivDl5 qCvczZU2V70  MySQL Oueries for workbench practice : -- create student infomation table -- CREATE TABLE STUDENT_INFO (Student_ID INT NOT NULL, Student_Name VARCHAR(20) NOT NULL, City_State VARCHAR(15) NOT NULL, Age INT NOT NULL, Result VARCHAR(15) NOT NULL, Marks INT NOT NULL,PRIMARY KEY (STUDENT_ID)); INSERT INTO STUDENT_INFO VALUES (1, 'Vasanth', 'Erode', 21, 'NoRank', 37), (2, 'Guru', 'Tiruppur', 20, 'NoRank', 28), (3, 'Gokul', 'Tiruchirapalli', 18, 'Average', 40), (4, 'Mani', 'Kumarapalayam', 24, 'NoRank', 31), (5, 'Moorthy', 'Salem', 18, 'VeryGood', 86), (6, 'Amutha', 'Chennai', 17, 'Average', 61), (7, 'Jaga', 'Madurai', 24, 'VeryGood', 89), (8, 'Pavithra', 'Erode', 23, 'Average', 68), (9, 'Arthi', 'Tiruppur', 17, 'Average', 53), (10, 'Kabilan', 'Tiruchirapalli', 24, 'Average', 67), (11, 'Manasi', 'Kumarapalayam', 17, 'Excellent', 97), (12, 'Suja', 'Salem', 23, 'VeryGood ', 85), (13, 'Arun', 'Chennai', 22, 'NoRank', 32), (14, 'Deepa', 'Madurai', 20, 'Average', 49), (15, 'Sindhu', 'Erode', 22, 'Average', 65), (16, 'Madhavi', 'Tiruppur', 20, 'Good', 78), (17, 'Swetha', 'Tiruchirapalli', 17, 'Good', 73), (18, 'Selvi', 'Kumarapalayam', 22, 'Average', 47), (19, 'Pooja', 'Salem', 19, 'VeryGood', 88), (20, 'Lakshmi', 'Chennai', 17, 'Excellent', 99), (21, 'Veeramani', 'Madurai', 21, 'Average', 67), (22, 'Pandian', 'Erode', 21, 'Good', 72), (23, 'Veera', 'Tiruppur', 20, 'Average', 51), (24, 'Devi', 'Tiruchirapalli', 20, 'Excellent', 96), (25, 'Devan', 'Kumarapalayam', 21, 'Excellent', 100), (26, 'Keerthi', 'Salem', 17, 'VeryGood ',89), (27, 'Venkatesh', 'Chennai', 24, 'Good', 75), (28, 'Raja', 'Madurai', 24, 'Average', 42); --Select statememt – -- Retrieve All Student Information -- 1.Write an SQL query to retrieve all columns and records from the student_info table. SELECT * FROM STUDENT_INFO; --Get Student Names and Their Results-- 2. Write an SQL query to display each student's name along with their corresponding result from the student_info table. select student_name, result from student_info; --Fetch Student Names and Ages-- 3.Write an SQL query to list the names and ages of all students from the student_info table. select student_name, age from student_info; --List Student Names, City/State, and Ages – 4.Write an SQL query to display each student's name, their city/state, and age from the student_info table. select student_name, city_state, age from student_info; --Retrieve Students from Chennai-- 5.Write an SQL query to fetch the names, city/state, and ages of students who reside in Chennai from the student_info table. select student_name, city_state, age from student_info where city_state = 'chennai'; --Retrieve Students from Salem— 6.Write an SQL query to fetch the names, city/state, and ages of students who reside in Salem from the student_info table. select student_name, city_state, age from student_info where city_state = 'salem'; --Retrieve Students from Chennai Aged 21 or Older-- 7. Write an SQL query to fetch the names, city/state, and ages of students who reside in Chennai and are aged 21 or older from the student_info table. SELECT student_name, city_state, age FROM student_info WHERE city_state = 'chennai' AND age >= 21; --Retrieve Students from Madurai Aged 21 or Older-- 8.Write an SQL query to fetch the names, city/state, and ages of students who reside in Madurai and are aged 21 or older from the student_info table. SELECT student_name, city_state, age FROM student_info WHERE city_state = 'madurai' AND age >= 21; --List Students from Either Chennai or Madurai -- 9.Write an SQL query to display the names, city/state, and ages of students who reside in either Chennai or Madurai from the student_info table. SELECT student_name, city_state, age FROM student_info WHERE city_state = 'chennai' OR city_state = 'madurai'; --Retrieve Students from Chennai or Madurai Aged 21 or Older, Ordered by City-- 10.Write an SQL query to fetch the names, city/state, and ages of students who reside in either Chennai or Madurai and are aged 21 or older. Sort the results by city/state in ascending order. SELECT student_name, city_state, age FROM student_info WHERE (city_state = 'chennai' OR city_state = 'madurai') AND age >= 21 ORDER BY city_state; --List All Distinct City/State Values-- 11.Write an SQL query to display all unique city/state values found in the student_info table. SELECT DISTINCT city_state FROM student_info; -- Count Total Number of Students by City/State-- 12.Write an SQL query to count the total number of students based on their city/state from the student_info table. SELECT COUNT(city_state) FROM student_info; --Count Unique City/State Values-- 13.Write an SQL query to count the number of distinct (unique) city/state values in the student_info table. SELECT COUNT(DISTINCT city_state) FROM student_info; Class 2 : DOUBT SESSION: 1. How can you rename a column in a table and change its data type in the MARKSDETAILS table to an integer that does not allow NULL values? ALTER TABLE MARKSDETAILS CHANGE MARKS MARKS_details int NOT NULL; 2. How can you modify the data type of the MARKS_details column in the MARKSDETAILS table to DECIMAL(10,2) and ensure it cannot be NULL? ALTER TABLE MARKSDETAILS MODIFY MARKS_details DECIMAL(10,2) NOT NULL; 3. Write an SQL query to update the status of employees to 'inactive' for employees with IDs 1, 3, and 5. UPDATE employees SET status = 'inactive' WHERE employee_id IN (1, 3, 5); 4. How would you delete records from the employees table for employees with IDs 1, 3, and 5 using different SQL statements? DELETE FROM employees WHERE employee_id IN (1, 3, 5); DELETE FROM employees WHERE employee_id = 1 OR employee_id = 3 OR employee_id = 5; DELETE FROM employees WHERE department_id = 2 AND status = 'inactive'; DELETE FROM employees WHERE employee_id BETWEEN 10 AND 20;