When I am creating a new database table, what factors should I take into account for selecting the primary key’s data type?
SQL Server: Make all UPPER case to Proper Case/Title Case
I have a table that was imported as all UPPER CASE and I would like to turn it into Proper Case. What script have any of you used to complete this?
What is equivalent of the Nz Function in MS Access in MySQL? Is Nz a SQL standard?
What is MySQL equivalent of the Nz Function in Microsoft Access? Is Nz a SQL standard? In Access, the Nz function lets you return a value when a variant is null. Source The syntax for the Nz function is: Answer The COALESCE() function does what you describe. It’s standard SQL and it should be supported in all SQL databases. The
What are views good for?
I’m just trying to get a general idea of what views are used for in RDBMSes. That is to say, I know what a view is and how to make one. I also know what I’ve used them for in the past. But I want to make sure I have a thorough understanding of what a view is useful for
Database design for database-agnostic applications
What do I have to consider in database design for a new application which should be able to support the most common relational database systems (SQL Server, MySQL, Oracle, PostgreSQL …)? Is it even worth the effort? What are the pitfalls? Answer The short answer is to stick to features that are standardly, or close to standardly implemented. What this
How to create timestamp column with default value ‘now’?
How to create a table with a timestamp column that defaults to DATETIME(‘now’)? Like this: This gives an error. Answer As of version 3.1.0 you can use CURRENT_TIMESTAMP with the DEFAULT clause: If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used in the new row is a text representation of the current UTC
Can you use an alias in the WHERE clause in mysql?
I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias: Answer You could use a HAVING clause, which can see the aliases,
What is the best way to partition large tables in SQL Server?
In a recent project the “lead” developer designed a database schema where “larger” tables would be split across two separate databases with a view on the main database which would union the two …
Best way to get result count before LIMIT was applied
When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls. Currently I do that by running the query twice, once wrapped in a count()…
How to use GROUP BY to concatenate strings in MySQL?
Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C Answer https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.