• 1. What is the difference between the WHERE and HAVING clauses?

    Answer:

    1. WHERE

    - Filter data from a result, usually filter columns available in the table. 

    - It filters before any groups are made.

    - Can't use the aggregate function to filter.

    2. HAVING

    - Used to filter values from a group.

    - Only use the aggregate function to filter.

    - Can't using HAVING if not use to the GROUP BY clause.

    - Performance is SLOW because the aggregate function needs to calculation.

  • 2. What is the difference between IN and EXISTS clauses?

    Answer:

    1. IN

    - Works on the list data set.

    - It's doesn’t work on subqueries resulting in virtual tables with multiple columns.

    - Compares every value in the result list.

    - Performance is comparatively SLOW for larger resultset of a subquery.

    2. EXISTS

    - Works only on Virtual tables.

    - It is used with co-related queries.

    - Exits comparison when the match is found.

    - Performance is comparatively FAST for larger resultset of the subquery.

  • 3. How do you copy data from one table to another table?

    Answer:

    It's very simple, you can insert data for a table base on a SELECT query from another table, look example below:

    INSERT INTO table2 (column1, column2, column3, ...)
    SELECT column1, column2, column3, ...
    FROM table_name
    WHERE condition;
    
  • 4. How to find a duplicate record?

    Case 1: Duplicate records only one field.

    Case 2: Duplicate records with many fields.

    Answer:

    We can use GROUP BY with COUNT aggregate function to count if it's greater than 1.

    Case 1: Duplicate records only one field.

    SELECT DisplayName, COUNT(PhoneNumber) 
    FROM Customer
    GROUP BY DisplayName
    HAVING COUNT(PhoneNumber) > 1 

    Case 2: Duplicate records with many fields.

    SELECT DisplayName, PhoneNumber, COUNT(*) 
    FROM Customer
    GROUP BY DisplayName, PhoneNumber
    HAVING COUNT(*) > 1
    
  • 5. What is the difference between INNER JOIN and LEFT JOIN?

    Answer:

    INNER JOIN: Only get data existed from both two tables.

    LEFT JOIN: Get full data from the left table (table A). If data from the right table is not existing will return a null value.

    difference between inner join and left join

  • 6. What is the difference between Clustered and Non-clustered index in SQL Server?

    Answer:

    1. Clustered Index

    - Only has a clustered index in a table.

    - It's created on the primary key.

    - Data retrieval is faster than the non-cluster index.

    - Sort the records and store them physically according to the order

    - Do not need extra space to store the logical structure.

    2. Non-Clusted

    - Quicker for insert and update operations than a clustered index.

    - Can create many non-cluster indexes in a table,  the maximum of 999 non-clustered indexes per table as per limitations.

    - Use extra space to store logical structure.

  • 7. Why we have to create the index for a table in the SQL server? What column data type is better to create an index?

    Answer:

    Why we have to create the index for a table in the SQL server?

    An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

    If not have the index when we select data, the SQL server has to lost many times to scan all data in the table.

    What column data type is better to create an index?

    - Number data always index better than other data types.

    - Column has short content always index better than column has long content. Such as TitleNews(max length 100) and LongContent(max length 4000) column, TitleNews column always index better

  • 8. What is the difference between Union and Union All in SQL Server?

    Answer:

    UNION: Merge two or more data from Select clause, always remove the duplicate records.

    UNION ALL: Merge two or more data from Select clause, display all duplicate records

  • 9. How can I select a random record in SQL Server?

    Answer:

    1. Using NEWID() function with ORDER BY

    SELECT TOP 5 [CustomerID], [CompanyName], [ContactName]
    FROM [dbo].[Customers] 
    ORDER BY NEWID()

    2. Using RAND() function with ORDER BY

    SELECT TOP 5 [CustomerID], [CompanyName], [ContactName]
    FROM [dbo].[Customers] 
    ORDER BY RAND()
    
  • 10. How to set the value to a variable from the SELECT query in SQL Server?

    Answer:
    -- Set value to variable
    DECLARE @fullName varchar(30);         
    SELECT @fullName = FullName         
    FROM Customer        
    WHERE CustomerID = 1000;   
    
    -- get value from variable
    SELECT @fullName AS 'FullName';   

    OR

    -- Set value to variable
    DECLARE @fullName varchar(30);         
    SELECT @fullName = (SELECT FullName   
    FROM Customer   
    WHERE CustomerID = 1000);  
    
    -- get value from variable
    SELECT @fullName AS 'FullName';  
    
  • 11. What are the advantages and disadvantages of the stored procedure?

    Answer:

    1. Advantages

    - Very secure, you can avoid attack SQL injection.

    - Faster, because SP is pre-compiled and it is store in the server so when we call SP, we only determine SP name and parameter, this help request is faster.

    - Support full CRUD

    2. Disadvantages

    - Because it stores in the server so it makes the server heavy and only executes it in that server.

  • 12. What is the difference between TRUNCATE and DELETE clauses?

    Answer:

    1. DELETE

    - Delete data from the table, can delete one or more records by using the WHERE clause. 

    - Not reset Identity increase value.

    - Commit and Rollback can be performed after DELETE statement.

    2. TRUNCATE

    - Delete all data from a table, can't delete some records.

    - Reset Identity increase value to 0.

    - Can not Rollback data after delete.

  • 13. How to select unique records from a table?

    Answer:

    You can use DISTINCT clause. It helps remove all duplicate records. Duplicate means that all value of all column has to same. For example:

    SELECT DISTINCT CustomerId, Name, ProductId, ProductName FROM Customer;
    
  • 14. How to get all records from the Employee table has FirstName start with "Jonh"?

    Answer:

    To search text, you can use LIKE keyword and % prefix to search:

    - Select with FirstName start with "Jonh"?

     SELECT CustomerId, Name FROM Customer WHERE Name LIKE'Jonh%';

    - Select with FirstName end with "Jonh"?

    SELECT CustomerId, Name FROM Customer WHERE Name LIKE'%Jonh';

    - Select with FirstName contain "Jonh"?

    SELECT CustomerId, Name FROM Customer WHERE Name LIKE'%Jonh%';
    
  • 15. What is the difference between VIEW and TABLE in SQL Server?

    Answer:

    1. VIEW

    - Not store data.

    - Can return data from many tables.

    - It's read-only, it can not insert, update, delete data.

    - It's helpful to make a report.

    2. TABLE

    - It's help store data

    - Can manage data with insert, update, delete data.

  • 16. What are Constraints in SQL Server? List out some constraints you know.

    Answer:

    What are Constraints in SQL Server?

    Constraints in SQL Server are rules and restrictions applied on a column or a table such that unwanted data can't be inserted into tables. This ensures the accuracy and reliability of the data in the database. We can create constraints on single or multiple columns of any table. Constraints maintain the data integrity and accuracy in the table.

    How many types of constraints provided in the SQL server?

    SQL Server contains the following 6 types of constraints:

    • Not Null Constraint
    • Check Constraint
    • Default Constraint
    • Unique Constraint
    • Primary Constraint
    • Foreign Constraint
  • 17. What is wrong with the SQL query below?

    SELECT UserId, UserName, AVG(Total) AS AvgTotal
    FROM Invoices
    GROUP BY Userid
    HAVING AVG(Total) >= 10
    
    Answer:

    It's wrong in GROUP BY clause because all columns  in SELECT have to appear in GROUP BY. We can fix it as below:

    SELECT UserId, UserName, AVG(Total) AS AvgTotal
    FROM Invoices
    GROUP BY Userid, UserName
    HAVING AVG(Total) >= 10

    OR remove column UserName in SELECT clause

    SELECT UserId, AVG(Total) AS AvgTotal
    FROM Invoices
    GROUP BY Userid
    HAVING AVG(Total) >= 10
    
  • 18. What is the order execution of some clauses below in the SELECT command?

    SELECT, FROM, TOP, WHERE, JOIN, ON, ORDER BY, GROUP BY, HAVING, DISTINCT.

    Answer:

    Below is the order execute of clauses above:

    1. FROM
    2. ON
    3. JOIN
    4. WHERE
    5. GROUP BY
    6. HAVING
    7. SELECT
    8. DISTINCT
    9. ORDER BY
    10. TOP
  • 19. What are Aggregate and Scalar functions in the SQL server?

    Answer:

    1. Aggregate

    Aggregate functions determine and calculate values from multiple columns in a table and return a single value.

    • AVG(): Returns the average value from specified columns.
    • COUNT(): Returns number of table rows.
    • MAX(): Returns the largest value among the records.
    • MIN(): Returns smallest value among the records.
    • SUM(): Returns the sum of specified column values.

    2. Scalar

    Scalar functions are used to return a single value based on the input values.

    • FORMAT(): Specifies the display format.
    • GETDATE(): Get current date-time of system.
    • LEN(): Get length of string text.
    • ROUND(): Rounds up the decimal field value to a number.
    • DATEDIFF(): Return the difference between two date values, in years.
  • 20. What is the relationship? How many types of relationships are there in the SQL server?

    Answer:

    1. What is the relationship?

    This is a relationship between two tables through foreign key, it helps using joining to retrieve data from two or many tables.

    2. How many types of relationships are there in the SQL server?

    We have 3 relationships between two tables as below :

    - ONE To ONE(1:1)

     For example, the relationship between Customer and Customer Detail, one customer only one information detail about them.

    - ONE To MANY(1:n)

    See the relationship between Customer and Order, one customer can make many orders from an economic website.

    - MANY To MANY(n:n)

    See the relationship between Customer and Product, you can see that a customer can buy many products but one product can buy by many customers.