Class 12 SQL Questions and Solutions
Uploaded by
Saraswati maharanaClass 12 SQL Questions and Solutions
Uploaded by
Saraswati maharana- SQL Questions 1 to 4
- SQL Questions 5 to 8
- SQL Questions 9 to 14
- SQL Question 15
Common questions
Powered by AITo find students who have achieved the highest marks, the SQL query would utilize the MAX function in this form: SELECT MAX(marks) AS 'Highest Marks', name FROM students GROUP BY name; This query identifies the maximum marks obtained and groups the results by student name to potentially showcase all those tied for the top mark. It helps understand peak performance by aggregating data and sorting it through the MAX function.
Renaming tables and columns can significantly impact database administration and application layers by potentially breaking links that depend on the original table or column names, such as reports, scripts, or application code. For example, if you rename a table from 'STUDENT' to 'SCHOLARS', as in the command ALTER TABLE STUDENT RENAME TO SCHOLARS; , existing queries and application code referencing 'STUDENT' must be updated to 'SCHOLARS'. Failure to do so can lead to errors and downtime, necessitating thorough planning and communication across teams.
To add a new column for capturing birth dates in an SQL table, you would use the ALTER TABLE command as follows: ALTER TABLE STUDENT ADD COLUMN DOB DATE; This allows for storing additional data related to each student. The potential implications include increased storage requirement for the database and the need to update existing entries with relevant data to fully utilize the new column.
An INNER JOIN in SQL returns only the rows where there is a match in both tables involved in the join operation. For example, SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID returns records with matching IDs in both Table1 and Table2. In contrast, a LEFT JOIN returns all records from the left table (Table1) and the matched records from the right table (Table2), with NULLs for unmatched rows in Table2. Thus, SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID will include all rows from Table1, with Table2's non-matching records filled with NULL .
Creating an index on a column in an SQL table, such as one on the 'Name' column in the 'SCHOLARS' table with ALTER TABLE SCHOLARS ADD INDEX (Name), serves to improve the speed and efficiency of query responses. An index allows the database to find and retrieve specific rows much more quickly than searching through the entire table linearly. However, while it speeds up retrieval operations, it can slow down insert and update operations, as the index must be maintained separately each time data changes.
A strategy to update specific records involves the UPDATE command with a conditioned WHERE clause. For example, UPDATE student SET marks = 95 WHERE Roll_No = 10; This command targets only the student with Roll_No 10 to update their marks to 95. Updates ensure data reflects current information, enhancing accuracy. However, incorrect updates or queries can compromise data integrity if not verified, leading to inconsistencies and possible erroneous analytics outcomes.
To count students with marks within a specific range, an SQL query employs the COUNT function alongside a BETWEEN conditional statement, such as SELECT COUNT(*) FROM students WHERE marks BETWEEN 50 AND 70; This query counts the number of students whose marks fall within the specified range. This method is useful for data analysis because it allows for targeted evaluation of performance groups within defined parameters, aiding in understanding trends and distribution.
To ensure data integrity when creating a new table, you must define a unique identifier for each record using a primary key. In SQL, the command for this would look like: CREATE TABLE STUDENT (Roll_No INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Marks FLOAT NOT NULL) This ensures that every student has a unique Roll_No, which cannot be NULL, maintaining the table's integrity.
The DELETE command, used with a WHERE clause, allows for the efficient removal of records based on specific criteria. For instance, the command DELETE FROM SCHOLARS WHERE marks < 40; deletes any records in the 'SCHOLARS' table where the marks are below 40. This mechanism is efficient because it allows for targeted deletion. However, care must be taken as it permanently removes data which can lead to data loss if not performed correctly or with due verification.
To retrieve unique records based on a particular column in SQL, the DISTINCT keyword is used in queries. For unique marks, the query is SELECT DISTINCT Marks FROM SCHOLARS; This approach helps in identifying distinct data points that reduce redundancy and optimize storage by only capturing unique entries, thereby improving data clarity and system efficiency.
