Introduction I wanted to connect to an MS SQL server database using Python from my Mac laptop. I struggled a bit but eventually I had a working solution. I thought it can be useful so I am sharing it… Steps Make sure you have brew installed on mac. If not, you can get it from
Problem Given the following database table: DATA (id, data_date, data_value). The id field is not unique. Write an SQL statement to select the latest value for each id Solution Group rows by id then select rows with maximum date Code Here is the code
Problem Write an SQL statement to find rows with duplicate column values in a database table Solution You need to group by the column containing the duplicate values then select only the groups with count greater than 1 Code Here is the code in SQL
Question What is the difference between delete table and truncate table operation Answer Delete table operation deletes all rows in the table. The deletion of rows is logged in the transaction log. On the other hand truncate table operation similarly deletes all rows in the table but it does not log the deletion of rows
Question Suggest few areas to inspect to improve the performance of a slow query Answer Here are few areas to check: Tables have no indexes. Database indexes improve query performance dramatically on the expense of slow writes and extra space Tables are scanned in full. Scanning huge tables degrades performance Table statistics are not updated.
Question What is the difference between WHERE and HAVING clauses. Answer The WHERE clause restricts which rows are returned while the HAVING clause operates on groups of rows. HAVING is always used with GROUP BY and must have a function it restricts. The rule says if a condition refers to an aggregate function put that
Question What is a database view. Mention few database sql views advantages Answer A view is a virtual table (not part of the physical schema) composed from the result set of a stored query. Views contain dynamic content. This means if you modify the physical tables from which the view is constructed then the data shown in subsequent invocations of the
All Keyword in SQL Given the following database table: employee(emp_name, emp_salary) write an SQL statement to print the employee name with the highest salary Solution Using the max aggregate function will give us the maximum salary but it does not give us the name of the employee who has the maximum salary. The solution is
Problem You have the following database table: employee (emp_name, emp_age, emp_department) Write an SQL statement to calculate the average age of employees who are older than 30 years old for each department. Do not display the average if the number of employees used in the calculation is less than 3. Solution You need to use
Problem Write code to simulate the SQL statement “Select unique emp_name from employees” Solution Assuming we have an array of employee names we need to write a function that prints the unique employee names in the array. We can use a built in hash table for that matter. Just hash all names then print the
