Employee and Student Database Operations
Uploaded by
ayushidixit248Employee and Student Database Operations
Uploaded by
ayushidixit248Common questions
Powered by AITo identify records based on string patterns in names within SQL tables, you can use the `LIKE` operator, which allows pattern matching. For example, to find names ending with a specific letter, you would use `WHERE name LIKE '%L'`. Similarly, to find names containing a specific substring, you use `WHERE name LIKE '%it%'`. These methods enable efficient searching and can be complemented by using wildcards for more flexible matches .
To handle transactions and ensure data consistency when mistakenly deleting student records, use transaction control statements `BEGIN`, `ROLLBACK`, and `COMMIT`. Begin by initiating a transaction with `BEGIN;`, execute the delete operation, and if an error is detected or confirmation is needed, rollback the transaction with `ROLLBACK;` to undo changes. If the deletion is verified as correct, finalize with `COMMIT;` to make the changes permanent. This process ensures any accidental deletions can be reverted .
Constraints can maintain data integrity by restricting the range of acceptable values in a database field. For instance, to ensure birth dates are less than a certain year, use a `CHECK` constraint during table creation or alter an existing column. Use syntax such as `ALTER TABLE employee ADD CONSTRAINT check_birth_date CHECK (year(birth_date) < 1990);`. This constraint ensures all entered birth dates comply with the specified rule, preventing incorrect data input .
To ensure that an employee database contains only unique employee IDs, you would implement a primary key constraint on the ID column. This can be done by defining the ID column as a primary key during table creation or by altering the existing table structure with `ALTER TABLE employee ADD PRIMARY KEY (id);`. Using a primary key constraint is crucial for maintaining data integrity and preventing duplicate entries .
Implement an index on a student table to improve query performance leveraging the `CREATE INDEX` statement. For example, `CREATE INDEX idx_rollno ON student (rollno);`. To test the performance improvement, compare the execution time of queries before and after indexing using profiling tools or `EXPLAIN` commands. This method speeds up data retrieval specific to indexed columns, reducing search times and enhancing performance .
To add a new field to an existing database table and populate it for all records, you first alter the table to include the new column using the SQL statement `ALTER TABLE table_name ADD column_name datatype`. After adding the column, you can update the table to insert respective data for all records by using the `UPDATE` statement, setting the new column with desired values for each record. This ensures all existing entries have data in the newly introduced field .
Identify students who have failed all their subjects by querying the database to apply conditions on their exam scores. Use a SQL statement like `SELECT name FROM student WHERE phy < 40 AND che < 40 AND bio < 40;`, assuming passing marks are 40. This checks each subject's score for every student and retrieves names of those consistently below the passing threshold, indicating failure in all subjects .
To update selected fields of customer records based on certain conditions, use the SQL `UPDATE` statement with a `WHERE` clause to specify the conditions. For example, to update the age for all customers from a specific city, execute `UPDATE customer SET age = new_value WHERE address = 'specified_city';`. This selectively changes data in specified rows, maintaining existing data for non-matching rows .
To calculate the average salary of employees after adding a new salary column to the table, use the SQL `AVG()` function. First, add the salary column using `ALTER TABLE employee ADD COLUMN salary DECIMAL(10, 2);`, then populate it with data. Calculate the average by executing `SELECT AVG(salary) FROM employee;` to retrieve the average salary value across all records .
To ensure age values in a customer table are consistently sorted, use the SQL `ORDER BY` clause to sort the retrieved data. Execute a query such as `SELECT * FROM customer ORDER BY age ASC;` to organize all records in ascending order based on the age field. This sorting is temporarily applied to the results of the query and helps maintain consistency in reporting .