SQL – Display Primary and Foreign Key Relationships Table With Their Constraints

In below query, We can view the relationships between more than one table as primary and foreign key …

SELECT
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PT ON PT.TABLE_NAME = PK.TABLE_NAME

This will help you to identify the relationships between the two tables..

SQL BASIC COMMANDS

In below sections there are basics sql queries about SELECT , WHERE , AND OR NOT , INSERT … There are other query commands also those we will see in the Next blog …

SELECT

Select command is use for to get the data from the table

Example: SELECT * FROM tablename

The above example will display all the columns from the table. If we want to display particular fields in the table then we have to use column-name operator.

If we want to display certain field without any duplicates then we use the DISTINCT keyword along with the select command. 

WHERE

If we need only certain records from the table then we use the where clause. Where clause acts as a Filtering mechanism. Under the Where section we need to specify certain conditions, only if those conditions are met the records will be extracted.

Example: SELECT * FROM tablename WHERE columnname = value

AND, OR, NOT

If we need to add two or more conditions in the where clause then we can use the above-mentioned operators. These keywords will add more complexity to the query. 

  • AND Operator: This operator displays a record if all the conditions separated by AND are TRUE.
  •  OR Operator: This operator displays a record if any of the conditions separated by OR is TRUE.
  •  NOT Operator: This operator displays a record if the condition/conditions are NOT TRUE.

INSERT INTO

If we want to insert any new record or data into a table then we can use the INSERT query. We can use the Insert into in two ways:

Here we specify the column names for which we need to insert the record.

INSERT INTO table_name (column1, column2,…)VALUES (value1, value2, value3, …);

Top 10 SQL Interview Questions

  1. What is SQL ?
    SQL is Structured Query Language . This is a standard language used to perform tasks such as INSERT/UPDATE/DELETE and SELECT of data from a database.
  2. What is Database ?
    Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data.
  3. Constraints in SQL ?
    NOT NULL – Restricts NULL value from being inserted into a column.
    CHECK – Verifies that all values in a field satisfy a condition.
    DEFAULT – Automatically assigns a default value if no value has been specified for the field.
    UNIQUE – Ensures unique values to be inserted into the field.
    INDEX – Indexes a field providing faster retrieval of records.
    PRIMARY KEY – Uniquely identifies each record in a table.
    FOREIGN KEY – Ensures referential integrity for a record in another table.
  4. Difference Between Primary Key and Foreign Key ?
    A Primary key is nothing but unique column in Table and in each table we can have one single Primary Key.
    A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
  5. What is a JOINS ? and How Many Types ?
    JOIN is nothing but combining the records of two or more tables.
    INNER JOIN : Matching the Table A and Table B Data.
    LEFT JOIN : Retrieving the Data if not matched with Table B.
    RIGHT JOIN : Retrieving the Data if not matched with Table A.
    FULL JOIN : Retrieving the Data if not matched or Unmatched with Table A and Table B.
  6. What is Difference between Clustered and Non Clustered Index ?
    Clustered Index : Clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered index is relatively slower.
    Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
    One table can only have one clustered index whereas it can have many non clustered index.
  7. What is Normalization?
    Normalization is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
    Better Database organization
    More Tables with smaller rows
    Efficient data access
    Greater Flexibility for Queries
    Allows easy modification
    More Compact Database
  8. Difference between DROP and TRUNCATE ?
    DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.
  9. What is Trigger ?
    Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.
  10. What is View ?
    A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.


Identify Block SPs

In a SQL Server there is two things Blocking and Locking. Today will check about blocking.

There are number of ways to find out the details of the system processes IDs (spids) involved in blocking. I have tried to cover some of the options in this tip to include:

  • sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. 

For Checking in SQL use below Query.

EXEC sp_who2
  • sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
  • Sys.dm_os_waiting_tasks

The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0

Index Scan vs Index Seek

Index Scan:
Index scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

A scan is the opposite of a seek.

Index Seek:
Index seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index seek uses the index to pinpoint the records that are needed to satisfy the query.

Be yourself; Everyone else is already taken.

— Oscar Wilde.

This is the first post on my new blog. I’m just getting this new blog going, so stay tuned for more. Subscribe below to get notified when I post new updates.

Create your website at WordPress.com
Get started