SQL Assessment Test Questions and Tasks
Uploaded by
SasirekhaSQL Assessment Test Questions and Tasks
Uploaded by
SasirekhaCommon questions
Powered by AITo tailor patient search queries in SQL for specific criteria, use complex WHERE clauses incorporating multiple conditions. For example, 'SELECT * FROM patients WHERE SUBSTRING(first_name FROM 3) LIKE '%r%' AND gender = 'F' AND EXTRACT(MONTH FROM birth_date) IN (2, 5, 12) AND weight BETWEEN 60 AND 80 AND MOD(patient_id, 2) = 1 AND city = 'Kingston';'. This query filters records based on name, gender, birth month, weight range, odd patient_id, and city, utilizing SQL's pattern matching and logical operators .
A FOREIGN KEY constraint is important in SQL for maintaining referential integrity between tables by ensuring that a value in one table corresponds to a value in another table. Misconceptions include the belief that a foreign key value cannot be null, whereas it can indeed be null if set up that way, and that it must always match an existing value in the referenced table, which is true. It also establishes a relationship with primary or unique keys in either the same or different tables .
SQL can be utilized to present structured information about doctors by joining relevant tables and summarizing data. For instance, 'SELECT d.doctor_id, CONCAT(d.first_name, ' ', d.last_name) AS doctor_full_name, MIN(a.admission_date) AS first_admission, MAX(a.admission_date) AS last_admission FROM doctors d JOIN admissions a ON d.doctor_id = a.doctor_id GROUP BY d.doctor_id, d.first_name, d.last_name;'. This combines personal doctor information with admission records to give each doctor's active and detailed history .
To generate passwords for patients based on specific details, use a SQL concatenation: 'SELECT patient_id, CONCAT(patient_id, LENGTH(last_name), EXTRACT(YEAR FROM birth_date)) AS temp_password FROM admissions;'. This method combines patient ID, last name length, and birth year to create a potentially secure and unique temporary password. The composition is significant as it creates easily remembered and unique passwords enhancing patient security .
To query a SQL database for records where both the first and last names meet specific conditions, such as starting and ending with 's' and being at least 6 characters long, you can use the following query: 'SELECT patient_id, first_name FROM patients WHERE first_name LIKE 's____%' AND first_name LIKE '%s' AND LENGTH(first_name) >= 6;'. This utilizes pattern matching with the LIKE operator and length conditions to filter results based on precise character requirements .
To derive demographic insights regarding gender distribution from a patient database, use SQL's GROUP BY and aggregation features. For example, 'SELECT gender, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM patients) AS percent FROM patients GROUP BY gender;' This query computes the percentage of each gender in relation to the whole dataset, providing actionable demographic insight .
There are several methods to insert records into a SQL table, primarily through the INSERT INTO statement. The simplest form is 'INSERT INTO table_name VALUES (value1, value2, ...);', which requires specifying values for all columns. Another method is 'INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);', which allows for inserting only into specific columns. The 'INSERT INTO table_name SELECT ...' form is used to insert data from another query's result set. The choice of method depends on whether specific columns need to be populated or a whole row from another table is required .
The SQL keyword 'DISTINCT' is used to retrieve only unique rows from a dataset. It is critical in data analysis for eliminating duplicate entries and ensuring that the analysis is based on distinct records, which is essential for accurate results .
To update a SQL table to replace NULL values in a column, use the UPDATE statement with a SET clause. For example, 'UPDATE patients SET allergies = 'NKA' WHERE allergies IS NULL;' will change the 'allergies' column to 'NKA' where it is currently NULL. This ensures that any NULL entries are replaced by a specified default value .
To determine the total number of admissions a doctor has attended each year, use a combination of GROUP BY and aggregation functions: 'SELECT doctor_id, doctor_full_name, specialty, EXTRACT(YEAR FROM admission_date) AS year, COUNT(admission_id) AS total_admissions FROM admissions GROUP BY doctor_id, doctor_full_name, specialty, EXTRACT(YEAR FROM admission_date);'. This groups the data by doctor and year, counting admissions for each group .