SQL Practice Questions and Exercises
Uploaded by
raneerizalSQL Practice Questions and Exercises
Uploaded by
raneerizalCommon questions
Powered by AIUse 'SELECT std_id, name FROM Students WHERE name LIKE 'A%'' to identify students whose names start with 'A'. This query demonstrates SQL's pattern matching capabilities using the LIKE operator and wildcard '%', allowing searches for strings meeting specific criteria .
To sort students by roll number in descending order, use 'SELECT * FROM Students ORDER BY roll_no DESC'. This is useful for listing recent entries or prioritizing top numbers, helpful in scenarios such as assigning priorities or latest student registrations .
Use the SQL query 'SELECT DISTINCT house_name FROM Houses' to retrieve distinct house names. This operation is important for eliminating duplicate results, ensuring each unique house name appears only once, which is crucial for reporting and analysis tasks where uniqueness is desired .
To set up a relational database structure with tables for houses and students, follow these steps: First, create a database. Then, define two tables: 'Houses' with columns (house_id, abbreviation, house_name) and 'Students' with columns (std_id, roll_no, name, house_id). Assign 'house_id' as the primary key for 'Houses' and 'std_id' for 'Students'. Set 'house_id' in 'Students' as a foreign key referencing 'house_id' in 'Houses'. This establishes a relationship between the tables .
To retrieve the name and house_name of all students with their respective houses, use the SQL query: 'SELECT Students.name, Houses.house_name FROM Students JOIN Houses ON Students.house_id = Houses.house_id'. This query joins the 'Students' and 'Houses' tables on the 'house_id' foreign key relationship .
Execute 'SELECT COUNT(*) FROM Students' to retrieve the total number of students. This uses the aggregate function COUNT to analyze the table and return the total count of entries, which is vital for understanding table size and conducting further statistical analysis .
Use 'SELECT * FROM Students WHERE house_id IS NULL' to find students not assigned to any house. This query reveals data anomalies or incomplete entries where the foreign key 'house_id' has not been set, indicating students without house affiliations .
Use the query 'SELECT name FROM Students WHERE roll_no > 50' to find students with roll numbers greater than 50. This query implies filtering student records based on the condition specified in the WHERE clause, i.e., selecting only those students whose 'roll_no' exceeds 50 .
Execute the query 'SELECT name FROM Students ORDER BY name ASC' to sort student names in ascending order. This sorting is useful for organized data presentation and ease of locating specific students alphabetically in applications such as attendance lists or directories .
The query 'SELECT name FROM Students WHERE house_id IN (SELECT house_id FROM Houses WHERE abbreviation = 'RH' OR abbreviation = 'BH')' retrieves students from the specified houses. This conditional logic illustrates SQL's ability to execute complex checks using subqueries and logical operators, crucial for intricate data retrieval tasks involving multiple conditions .