How to get all tables without primary key in SQL Server?

There are many ways to find tables without primary key one of them described below.


Select
   Table_CataLog as DB_NAME,
   Table_Schema as TBL_SCHEMA,
   Table_Name as TBL_NAME     
from
   information_schema.tables T    
where
   Not Exists(
      Select
         1
      from
         information_Schema.Table_Constraints C    
      where
         Constraint_Type='PRIMARY KEY'    
         and C.Table_Name=T.Table_Name    
         and C.Table_Schema=T.Table_Schema
   )    
   and Table_Type='BASE TABLE'



This query will fetch all the table's information which do not have the primary keys.

How to get all tables which have primary key in SQL Server?

Sometimes we need to get all the tables that have primary key constraints. There are many ways to get this information.


Select
       ISTC.Table_Catalog as BD_Name,
       ISTC.Table_Schema AS TBL_SCHEMA,
       ISTC.Table_Name AS TBL_NAME,
       ISCCU.Column_Name AS COL_NAME,
       ISTC.Constraint_Name AS CONSTRAINT_NAME
From
       information_Schema.Table_Constraints ISTC 
INNER JOIN
       Information_Schema.constraint_column_usage ISCCU 
       on ISTC.Constraint_Name=ISCCU.Constraint_Name 
       AND ISTC.Table_Name=ISCCU.Table_Name 
where
       Constraint_Type='PRIMARY KEY'




This one is the best query to find out the tables, which have a primary key because information schema provides more information, and all the data can be retrieved with the filtered filter.

Interview Questions and answers for beginners - SQL Server Day 11

Question: What is SQL?
Answer: SQL stands for Structured Query Language, which is used to retrieve, store, and manipulate data in the database.

Question: What is data?
Answer: In simple terms, the data can be considered an object of the relevant facts. For example, your name, age, height, weight, image, etc.

Question: What is Database?
Answer: The physical collection of data in a systematic way is called a database. The database gives permission to manage data like a store, retrieve and manipulate.
In one line Database is the collection data, schemas, tables, queries, reports, views, and other objects.

Question: What is the table?
Answer: Table Is the collection of data which have columns and rows. A table has a specified number of columns but can have any number of rows.
Example: Student table

Name
Address
FatherName
DOB
Class
Dilip
Noida
PB Singh
09-12-2001
10
Anil
GKP
MG Singh
08-10-2002
9
Ashish
Delhi
DK Singh
12-02-2012
UKG

Question: What is the field in a column?
Answer: A column is the set of values. This contains a particular data type value in the table. It shows vertically in the table. For Example above table includes columns Name, Address, FatherName, DOB and Class. Name is the column that contains the student’s name.

Name
Dilip
Anil
Ashish

Question: What is RDBMS? 
Answer:
A Relational Database management system (RDBMS) is the Database management system in which we maintain data records in tables and relationship among and across The data and table. The relationship among the data is also stored in the form of table.

A Relational Database management system (RDBMS) is an additional condition which system supports a tabular structure of the data, with applies of relationships between tables.


A Relational Database management system (RDBMS) has the capability to recombine the data items from different files, providing powerful tools for data usages.

Interview Questions and answers - SQL Server Day 10

Question: Can we sort a column using a column alias in SQL Server?

Answer: Yes, we can sort a column using a column alias in SQL Server.
Example: Execute the following query


CREATE TABLE #TEMP
(
       FNAME VARCHAR(100)
       ,LNAME VARCHAR(100)
       ,CREATEDDATE DATETIME,
)

INSERT INTO #TEMP
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'ANIL','SINGH',GETDATE()
UNION ALL
SELECT 'ASHISH','PRATAP', GETDATE()

-- In the following query, FNAME alias is FISRT_NAME. We can apply for Order by clause on an alias as below
SELECT FNAME AS FIRST_NAME,LNAME AS LAST_NAME FROM #TEMP
ORDER BY FIRST_NAME ASC

DROP TABLE #TEMP


Question: What is the difference between NULL value, zero, and blank space in SQL Server? Are they same?

Answer: NULL value is the difference from zero and blank space, a NULL value is unsigned, unavailable, unknown or not applicable while zero is number, and blank space is the character.

Question: If a table contains duplicates row, how can we eliminate from a query result?

Answer: We can eliminate duplicates rows using DISTINCT keywords.
For Example: Execute following query.


CREATE TABLE #TEMP
(
       FNAME VARCHAR(100)
       ,LNAME VARCHAR(100)
       ,CREATEDDATE DATETIME,
)

INSERT INTO #TEMP
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'ASHISH','PRATAP', GETDATE()


SELECT DISTINCT FNAME LNAME FROM #TEMP

DROP TABLE #TEMP


Question: What is default sorting ordering of ORDER BY CLAUSE in SQL Server?

Answer:  Default sorting order of ORDER By clause is ascending.

Question: Is the following query will run or not in SQL Server?


SELECT FNAME LNAME, COUNT(*) FROM #TEMP


Answer: No, I will give an error '#TEMP.FNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.’

Right query is.


SELECT FNAME LNAME, COUNT(*) FROM #TEMP
GROUP BY FNAME



Interview Questions and answers - SQL Server Day 9

Question: What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Answer:
nchar and nvarchar can store Unicode characters.
char and varchar cannot store Unicode characters.
char and nchar are fixed-length which will reserve storage space for the number of characters you specify even if you don't use up all that space.
varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar. For more details click here

Question: What is the Difference between @@CONNECTIONS and @@MAX_CONNECTIONS in SQL Server?

Answer:
@@MAX_CONNECTIONS: @@MAX_CONNECTIONS returns the maximum number of synchronous user connections allowed. SQL Server allowed by default a maximum 32767 connections. To avoid many connections it can be configured at the server level.
Note: Maximum connections (32767) are depending on application and server hardware limits.

@@CONNECTIONS: @@CONNECTIONS returns number of connection attempts on SQL Server since SQL Server has been started.
SELECT [ConnectionAttempts] = @@CONNECTIONS,
       [MaximumAllowed] = @@MAX_CONNECTIONS
For more click here 

Question: What is the difference between DATEFIRST and @@DATEFIRST in SQL Server?

Answer: DATEFIRST keyword use to reset first day of week in SQL Server and @@DATEFIRST return the current value for the session of DATEFIRST.
For details click here 

Question: What is the Difference between a HAVING CLAUSE and a WHERE CLAUSE in SQL Server?

Answer:
WHERE clause applies to individual rows. While HAVING clause is like a WHERE clause, but applies only to groups as a whole.

The WHERE clause is applied first to the individual rows in the tables means WHERE clause is fetched data from the memory according to condition. The HAVING clause is applied to the rows in the result set, means first data is fetched from memory to result set then apply HAVING clause on the row of the result set.


We can use WHERE clause with the HAVING clause, but it applies before the GROUP BY clause. We can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. For more details click here 

Interview Questions and answers - SQL Server Day 8

Question: What is schema in SQL Server? How can we create the schema in SQL Server?

Answer: Schema introduced in SQL Server 2005 which is used for separation, Management and Ownership of the database object. Schema is a distinct namespace that is used to store the database object. It is remove the tight coupling of database objects and owners to improve the security administration of database objects.

Create schema using wizard 




Create schema using query

USE [Test]
GO
--Create Schema MySchema
CREATE SCHEMA [MySchema] AUTHORIZATION [dilip] --dilip is the owner (database user)
GO

Question: How to change the schema of the table in SQL Server?

Answer: Suppose we have a table Codefari which has default schema [dbo] and we have to transfer it in schema [blog], the following query may help you.
CREATE SCHEMA Blog
GO

ALTER SCHEMA Blog
TRANSFER dbo.Codefari
GO

Question: How to drop schema in SQL Server?
Answer: If we have permission then using the following query we can drop the schema.

DROP Schema Blog

Interview Questions and answers - SQL Server Day 7

Question: What are ACID properties in SQL Server?

Answer: The ACID rules of a transaction in any database ensure the reliability of data in all transactions in the database.

Atomicity
Atomicity shows the ability of the database to assure that either all tasks of a transaction are performed or none of them.

Consistency
Consistency assures that the database remains consistent state before the transaction and after the transaction is over.

Isolation
Isolation assures that other operations can't be performed in an intermediate state during a transaction. As a result, transactions that run concurrently appear to be serialized.

Durability
Durability is responsible for notifying the transaction is successful; the transaction will persist, not undone. It will survive system failure, and the database system has checked the integrity constraints and no need to abort the transaction.
For example, click here. 

Question: What is a TRANSACTION in SQL Server?

Answer: Transaction is the collection of T-SQL statements that executes in a single unit. A transaction begins with a specific T-SQL statement and ends when all T-SQL statements execute successfully. If any statements fail, then the transaction fails. A transaction has only two types of results success or failure.
For more details, click here.

Question: What are COMMIT and ROLLBACK in SQL Server?

Answer: COMMIT statement use to the committed transaction to save changes in the database successfully.
ROLLBACK statement used to stop the current transaction and un-done the changes made by the transaction.
For more details, click here. 

Question: What is a WITH(NOLOCK) in SQL Server?

Answer: WITH(NOLOCK) used in the SELECT statement to give priority to select statement. WITH(NOLOCK) unlock the table for a SELECT statement to stop another process.
Example
SELECT * FROM TBL WITH(NOLOCK)

Question: GO command in the SQL server?

Answer: The GO command indicates the end of the SQL statements. It is used when there are multiple statements to be executed but sent as a batch.

Question: What is the difference between UNION and UNION ALL in SQL Server?

Answer: UNION operator is used to combining multiple results set into one result set but remove any duplicate rows. Basically, UNION used to performing a DISTINCT operation across all columns in the result set. UNION operator has the extra overhead of removing duplicate rows and sorting results.


UNION ALL operators use to combine multiple results set into one result set, but it does not remove any duplicate result. Actually, this is not removed duplicate rows, so it is faster than the UNION operator if you want to combine multiple results and without duplicate records, then use UNION. For more details click here 

Interview Questions and answers - SQL Server Day 6


Question: When we used UPDATE_STATISTICS commands in the SQL server?
Answer: When bulk changes are done in your database like INSERT, UPDATE, and DELETE because bulk changes may increase your index view fragmentation. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view.
Question: What is view in SQL Server?
Answer: The view is a virtual table that contains one or more than one field of more than one physical table. The view is used for de-normalization purposes. This is used to join multiple tables and get the data.
Question: What is the property of the relational table in SQL Server?
Answer:
1.       Values are atomic.
2.       Column values are of the same kind.
3.       Each row is unique.
4.       The sequence of columns is insignificant.
5.       The sequence of rows is insignificant.
6.       Each column must have a unique name.
Question: Explain the different types of lock-in SQL Server?
Answer: The following locks are available in SQL Server.
Shared (S)
Used for reading operations those do not change or update data, such as a SELECT statement.
Update (U)
Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking and potentially updating resources later.
Exclusive (X)
Used for data-modification Operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent
Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema
Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU)
Used when bulk copying data into a table, and the TABLOCK hint is specified.
Key-range
Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the questions were rerun.
Question: What is the DBCC command in SQL Server?
Answer: DBCC Stands for Database Consistency Checker. These commands are used to check the consistency of the database, like validation tasks, maintenance, and status checks.
For example –
DBCC CHECKALLOC – Responsible for checking that all pages are correctly allocated in the database.
DBCC CHECKDB – Responsible for checking the integrity & allocation of specific objects in a database.
DBCC SQLPERF- Responsible to show transaction log statistics.
DBCC SQLFILEGROUP – Responsible for checking all the tables file groups for any design.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...