1. What is the difference between the
- 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.
- Used to filter values from a group.
- Only use the aggregate function to filter.
- Can't using
HAVINGif not use to the
- Performance is SLOW because the aggregate function needs to calculation.
2. What is the difference between
- 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.
- 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
SELECTquery 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
COUNTaggregate 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: 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.
6. What is the difference between
Non-clusteredindex 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.
- 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 Allin SQL Server?Answer:
UNION: Merge two or more data from
Selectclause, always remove the duplicate records.
UNION ALL: Merge two or more data from
Selectclause, display all duplicate records
9. How can I select a random record in SQL Server?
10. How to set the value to a variable from the
SELECTquery 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';
-- 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:
- 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
- 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
- Delete data from the table, can delete one or more records by using the
- Not reset Identity increase value.
- Commit and Rollback can be performed after
- 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
DISTINCTclause. 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
FirstNamestart with "Jonh"?Answer:
To search text, you can use
LIKEkeyword 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
TABLEin SQL Server?Answer:
- 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.
- 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) >= 10Answer:
It's wrong in
GROUP BYclause because all columns in
SELECThave 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:
- GROUP BY
- ORDER BY
19. What are
Scalarfunctions in the SQL server?Answer:
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.
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.
* NOTE: You need Login before leave an comment