As a delete statement can only delete from one table at a time in SQL, you have to use a stored procedure to delete from multiple tables "at once".
However, it looks as if
are foreign keys to Pname on
. If that is the case then you want to do a "cascade delete" - here is one article on the subject in CodeProject CASCADE in SQL Server with Example
...and another from a different source Using DELETE CASCADE Option for Foreign Keys