It’s because the name column on the bar table does not have the UNIQUE constraint. So imagine you have 2 rows on the bar table that contain the name ‘ams’ and you insert a row on baz with ‘ams’ on bar_fk, which row on bar would it be referring since there are two rows matching?
The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible. If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN. EDIT: In … Read more
First, always use the latest version of PostgreSQL. Performance improvements are always coming, so you’re probably wasting your time if you’re tuning an old version. For example, PostgreSQL 9.2 significantly improves the speed of TRUNCATE and of course adds index-only scans. Even minor releases should always be followed; see the version policy. Don’ts Do NOT … Read more
If empName is a VARCHAR(50) column: ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department; EDIT Per the comments, you can also do this: ALTER TABLE Employees CHANGE COLUMN empName empName VARCHAR(50) AFTER department; Note that the repetition of empName is deliberate. You have to tell MySQL that you want to keep the same column … Read more
The most efficient (using over()). select Grade, count(*) * 100.0 / sum(count(*)) over() from MyTable group by Grade Universal (any SQL version). select Grade, count(*) * 100.0 / (select count(*) from MyTable) from MyTable group by Grade; With CTE, the least efficient. with t(Grade, GradeCount) as ( select Grade, count(*) from MyTable group by Grade … Read more
By using NULL you can distinguish between “put no data” and “put empty data”. Some more differences: A LENGTH of NULL is NULL, a LENGTH of an empty string is 0. NULLs are sorted before the empty strings. COUNT(message) will count empty strings but not NULLs You can search for an empty string using a … Read more
The best way is to store the dates as a number, received by using the Calendar command. //Building the table includes: StringBuilder query=new StringBuilder(); query.append(“CREATE TABLE “+TABLE_NAME+ ” (“); query.append(COLUMN_ID+”int primary key autoincrement,”); query.append(COLUMN_DATETIME+” int)”); //And inserting the data includes this: values.put(COLUMN_DATETIME, System.currentTimeMillis()); Why do this? First of all, getting values from a date range … Read more
From the SQL Server Management Studio you can right click on your database and select: Tasks -> Generate Scripts Then simply proceed through the wizard. Make sure to set ‘Script Data’ to TRUE when prompted to choose the script options. SQL Server 2008 R2 Further reading: Robert Burke: SQL Server 2005 – Scripting your Database
Beginning with MySQL 8.0.19 you can use an alias for that row (see reference). INSERT INTO beautiful (name, age) VALUES (‘Helen’, 24), (‘Katrina’, 21), (‘Samia’, 22), (‘Hui Ling’, 25), (‘Yumie’, 29) AS new ON DUPLICATE KEY UPDATE age = new.age … For earlier versions use the keyword VALUES (see reference, deprecated with MySQL 8.0.20). INSERT … Read more
Dynamic SQL PIVOT: create table temp ( date datetime, category varchar(3), amount money ) insert into temp values (‘1/1/2012’, ‘ABC’, 1000.00) insert into temp values (‘2/1/2012’, ‘DEF’, 500.00) insert into temp values (‘2/1/2012’, ‘GHI’, 800.00) insert into temp values (‘2/10/2012’, ‘DEF’, 700.00) insert into temp values (‘3/1/2012’, ‘ABC’, 1100.00) DECLARE @cols AS NVARCHAR(MAX), @query AS … Read more