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

Nested queries, also known as subqueries, are queries placed inside other queries. They allow you to perform complex queries by using the result of one query as the input for another. Nested queries can appear in the SELECT, FROM, WHERE, or HAVING clauses of an SQL statement. Example CREATE TABLE Authors (     AuthorID INT PRIMARY KEY,     Name VARCHAR(100) ); CREATE TABLE Books (     BookID INT PRIMARY KEY,     Title VARCHAR(100),     AuthorID INT,     Price DECIMAL(10, 2),     FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ); INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Author One'), (2, 'Author Two'), (3, 'Author Three'); INSERT INTO Books (BookID, Title, AuthorID, Price) VALUES (1, 'Book A', 1, 10.00), (2, 'Book B', 2, 15.00), (3, 'Book C', 1, 20.00), (4, 'Book D', 3, 25.00); Find the Titles of Books Written by the Author Who Wrote the Book with the Highest Price SELECT Title FROM Books WHERE AuthorID = (     SELECT A

Set operations in SQL allow you to combine the results of two or more queries into a single result set. These operations include UNION, UNION ALL, INTERSECT, and EXCEPT (or MINUS in some SQL dialects). Each of these operations has its specific use case and behavior: UNION: Combines the results of two or more SELECT queries, removing duplicate rows. UNION ALL: Combines the results of two or more SELECT queries, including all duplicates. INTERSECT: Returns only the rows that are common to the results of two SELECT queries. EXCEPT (or MINUS) : Returns the rows from the first SELECT query that are not in the result of the second SELECT query. Implementing Set Operations in MySQL UNION: SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; UNION ALL : SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2; INTERSECT:  (MySQL does not support INTERSECT directly, but it can be simulated using INNER JOIN or subqueries) SELECT column1, col

Transaction Control Language (TCL) commands in MySQL are used to manage transactions in the database. Transactions allow you to execute a series of SQL statements as a single unit of work, ensuring that the database remains in a consistent state. The primary TCL commands in MySQL are START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT. 1.START TRANSACTION Begins a new transaction. START TRANSACTION; 2. COMMIT Saves all changes made during the current transaction. Once a COMMIT is issued, the changes are permanent and visible to other transactions. COMMIT; 3. ROLLBACK Reverts all changes made during the current transaction. This command is used to undo changes if an error occurs or if the transaction needs to be aborted. ROLLBACK; 4. SAVEPOINT Sets a point within a transaction to which you can later roll back. This is useful for creating sub-transactions or checkpoints within a larger transaction. SAVEPOINT savepoint_name; 5. ROLLBACK TO SAVEPOINT Rolls back the transaction to the speci

Views in MySQL A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table, and the fields in a view are fields from one or more real tables in the database. Creating a View Syntax: CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; CREATE VIEW employee_view AS SELECT employee_id, first_name, last_name, department FROM employees WHERE department = 'Sales'; Using the View You can query a view as you would a regular table: SELECT * FROM employee_view; Modifying a View To modify an existing view, use the ALTER VIEW statement: ALTER VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Dropping a View To drop a view, use the DROP VIEW statement: DROP VIEW view_name; Example: Let's create a scenario with a table, views, and some queries and operations based on them. We'll use a simple database for a company's employee management system. SQL to Cre

Learn about ER diagrams Design a normalized database schema for the following requirement. The requirement: A library wants to maintain the record of books, members, book issue, book return, and fines collected for late returns, in a database. The database can be loaded with book information. Students can register with the library to be a member. Books can be issued to students with a valid library membership. A student can keep an issued book with him/her for a maximum period of two weeks from the date of issue, beyond which a fine will be charged. Fine is calculated based on the delay in days of return. For 0-7 days: Rs 10, For 7 – 30 days: Rs 100, and for days above 30 days: Rs 10 will be charged per day. Sample Database Design BOOK (Book_Id, Title, Language_Id, MRP, Publisher_Id, Published_Date, Volume, Status) //Language_Id, Publisher_Id are FK (Foreign Key) AUTHOR(Author_Id, Name, Email, Phone_Number, Status) BOOK_AUTHOR(Book_Id, Author_Id) // many-to-many relationship, both col

Database schema for the library management system include  several tables to capture the required entities and relationships: Books : To store information about books. Members : To store information about library members. BookIssues : To track the issuance of books to members. BookReturns : To track the return of books and any fines collected. 1. Books Table This table will store details about the books available in the library. CREATE TABLE Books (     book_id INT AUTO_INCREMENT PRIMARY KEY,     title VARCHAR(255) NOT NULL,     author VARCHAR(255) NOT NULL,     publisher VARCHAR(255),     year_of_publication INT,     isbn VARCHAR(13),     category VARCHAR(100) ); INSERT INTO Books (title, author, publisher, year_of_publication, isbn, category) VALUES ( 'Book 1' , 'Author 1' , 'Publisher A' , 2000 , '1234567890123' , 'Fiction' ), ( 'Book 2' , 'Author 2' , 'Publisher B' , 2005 , '2345678901234' , 'Non-fiction

Create a Temporary Table: Use the CREATE TEMPORARY TABLE statement to create a temporary table. Temporary tables are session-specific and are automatically dropped when the session ends or when they are explicitly dropped. Drop the Temporary Table (Optional):  Temporary tables are automatically dropped when the session ends. However, if you want to explicitly drop the temporary table within the session, use the DROP TEMPORARY TABLE statement. Example: CREATE TEMPORARY TABLE temp_table (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(255) ); INSERT INTO temp_table (name) VALUES ('John'), ('Alice'), ('Bob'); SELECT * FROM temp_table WHERE name = 'Alice'; DROP TEMPORARY TABLE temp_table; Remember, temporary tables are useful for storing intermediate results within a session and are automatically cleaned up when the session ends, making them suitable for temporary data storage needs. Using intermediate tables in SQL queries is a common practice for