Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
Can we join two tables without primary foreign key relation
Yes, we can join two tables without primary foreign key relation as long as the column values involved in the join can be converted to one type.
ID column in Departments table is not the primary Key and DepartmentId column in Employees table is not the foreign key. But we can still join these tables using ID column from Departments table and DepartmentId column from Employees table, as both the columns involved in the join have same data type i.e int.
Select Employees.Name as EmployeeName, Departments.Name as DepartmentName
join Departments on Departments.ID = Employees.DepartmentId
The obious next question is, if primary foreign key relation is not mandatory for 2 tables to be joined then what is the use of these keys?
Primary key enforces uniqueness of values over one or more columns. Since ID is not a primary key in Departments table, 2 or more departments may end up having same ID value, which makes it impossible to distinguish between them based on the ID column value.
Foreign key enforces referential integrity. Without foreign key constraint on DepartmentId column in Employees table, it is possible to insert a row into Employees table with a value for DepartmentId column that does not exist in Departments table.
The following insert statement, successfully inserts a new Employee into Employees table whose DepartmentId is 100. But we don't have a department with ID = 100 in Departments table. This means this employee row is an orphan row, and the referential integrity is lost as result
Insert into Employees values (8, 'Mary', 'Female', 80000, 100)
If we have had a foreign key constraint on DepartmentId column in Employees table, the following insert statement would have failed with the following error.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "Sample", table "dbo.Departments", column 'ID'.
I have a request to you. Can you make an console application in which MVC pattern, List and LIst childern are used and getdata and showdata are used recursively. Please Venkat I am facing some problem in this. Please help me.
is there a way we could find what two columns can be joined from two different table ? i am sure its way to figure out if its few column like you explained but in real scenario we usually have 100 or more column in a table.
Hi i have a question, For Transpose if it is city 4 u can manually edit and add in the query and make it effective. But what if there goes n number of rows that needs to be PIVOTED ?(for ex : 25 rows needs to be Transposed). does the user needs to enter the row name each time to get the output result as PIVOT ?
Hi, I have question here. If there is no common attribute/column in 2 tables, can I join them and get the result like below ? Thank you :)
ID | Department
Salary | Title
Department | Title
Hi Venkat. I went to the few interviews and on couple of them i was asked this question
Let’s pretend I have a table with this structure.
Create table order_detail
Order_id int not null
, product_id int not null
,customer_id int not null
,purchase_date datetime not null
, amount money not null
It has data for one year, let’s pretend that it is 2014 through January 1st to December 31st
I need to delete few millions of records for customer_id = 42 for period of time through May 1st and June 30th
I was thinking about to use delete as
Delete from table order_detail Where customer_id = 42 and purchase_date=> 05012014 and purchase_date=< 06302014
However this statement is not good for few millions of records. I was wondering if you know any other way to delete this records which will not going to slow down server and run faster than delete
--Deleting a big data using a while loop like this avoid from transaction log increasing in size quickly.
WHILE 1 = 1
DELETE TOP (1000) FROM order_detail
WHERE customer_id = 42 AND
purchase_date=> 05012014 and purchase_date=< 06302014;
IF @@rowcount < 1000 BREAK;
Thanks for YouTube Videos. They are most useful. I have faced an interview question as below. Can you please explain it.
Support we have a Stored procedure but the performance of the SP is not good. what are the all the changes u will do to improve performance.
1) Add indexes when ever required.
2) remove if there are too many indexes
3) with select use no lock
4) replace sub queries with joins
Could you please explain if there are any other
2016 Workforce 100: Ranking the World’s Top Companies for HR.
American Express, which has made the list all three years, takes the top spot in this year’s list of top companies for HR.
To excel at everything from talent management to recruiting to benefits to diversity is not an easy task. To shine in those areas year after year is exemplary. Now in its third year, the Workforce 100 recognizes companies that excel in various areas of human resources during the course of the previous year. To determine which companies make the list, Workforce editors work with researchers from the Human Capital Media Advisory Group, the magazine’s research arm.
To find out which companies are the best for HR management, the research team created a statistical formula to shift through publicly available data on HR performance to separate the best from the rest. This year, to give employees more of a “say,” we’ve asked recruiting and job review website Glassdoor Inc. to provide data on what workers are saying about the companies that made our short list. From there, we combined that information with the public data available to create our 2016 Workforce 100 list.
American Express Inc. was the top company on this year’s list, and it has made the list all three years. Thirty-three other companies have also made all of the lists.