SQL server Interview Question – Part 2


What is the difference between a Where clause and a Having Clause?
·       Where clause is using before result retrieving.
Having clause is using after retrieving the data.
·       Where is used to check conditions before the aggregation takes place.
Having is used to check conditions after the aggregation takes place.
·       Where clause is used before Group by clause.
Having clause is used after Group by clause.
·       Where clause uses with Select, Insert, Update and Delete clause.
Having clause can only use with Select clause. If you perform Having clause with other clause like Insert, Update, and Delete clause it will returns an error.
·       Where clause is used for filtering rows.
Having clause is used to filter groups.

What is Join?
Join used to combine two or more tables logically with or without common field.
There are four most important types of joins available in SQL –
1.        Inner Join – It returns match rows in both tables.
2.        Left Join – It returns all record from left table, and also return matched records from the right table.
3.        Right Join – it returns all record from right table, and also return matched record from the right table.
4.        Full Join – it returns all rows when there is match in either left or right table.

What is a constraint?
A constraint allows you to apply simple referential integrity of the data inside table. Constraints can be used when the table is created and its using create table statement.
Following are the constraints are used in SQL:
·       PRIMARY KEY
·       FOREIGN KEY
·       UNIQUE KEY
·       NOT NULL
·       CHECK
·       DEFAULT
·       INDEX

What is Primary Key?
A Primary key is something which comes mainly from database. Primary key is almost the same as a Unique key. Primary key is all the values are unique and it can’t accept single null. A table can have only one primary key. Clustered index automatically created when the defined primary key. Primary key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.
What is a trigger?
Triggers are stored procedures created in order to enforce integrity rules in a database.  Triggers are executed automatically on occurrence of one of the data-modification operations. A trigger is a database object directly related with the particular table. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint.

What is index covering of a query?
Index covering means the data can be discovered only using indexes, without touching the tables.

Explain MySQL architecture.
The front layer takes care of network connectivity and securely authentications. The middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL or a commercial one supplied by a third party.

What is ACID?
ACID is a Atomicity, Consistency, Isolation and Durability.
Atomicity – Transactions are atomic and should be treated as one in case of rollback.
Consistency – The database should be in consistent state between multiple states in transaction.
Isolation – No other queries can access the data modified by a running transaction.
Durability – System crashes should not lose the data.

How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID () will return the last value assigned by the auto_increment function.
Note: You do not have to specify the table name.

When would you use ORDER BY in DELETE statement?
When you are not deleting by row id. Such as in DELETE FROM employee_info ORDER BY timestamp LIMIT 1. This will delete the most recently posted information in the table employee_info.

What is the difference between CHAR_LENGTH and LENGTH?
The CHAR_LENGTH is naturally the character count. The LENGTH is byte count. For the Latin characters the numbers are the same, but they are not the same for Unicode and other encodings.

Explain the difference between FLOAT, DOUBLE and REAL.
FLOAT - it store floating point numbers with 8 places and take up 4 bytes.
DOUBLE – it store floating point numbers with 16 places and take up 8 bytes.
REAL – it is a synonym of FLOAT for now.


Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/

Visit our website: https://www.expertwithsagarjaybhay.com/



Comments

Post a Comment

Popular posts from this blog

ALTER statement in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the ALTER table statement in MySQL. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/  Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/ MySQL ALTER command is used to modify the table, view an existing database. When you have completed your database design and after that, you realize some of the information was left out when creating a database. You want to change without losing existing data, and then you can change data using ALTER command. MySQL ALTER command is used to add a column with an existing table, add multiple columns, drop a column, modify a column, rename a column, and rename the table. ALTER command also used to add and drop constraints on the existing table. ...
Read more

Order By Clause in MySQL

Image
Hello and welcome everyone. In this article, we will learn about the Order By Clause in MySQL. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/  Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/ MySQL Order by clause is used to sort the records or data in ascending or descending order. Order by clause allows us to sort records or data by a single column or multiple columns in ascending or descending order. There are two key using in Order by clause:          ASC: - sorting data or records by ascending order.          DESC: - sorting data or records by descending order. Syntax:          SELECT column_name_1, column_n...
Read more

MySQL Constraint – PRIMARY KEY

Image
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/  Web API 2- .Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/ Hello everyone in the previous article we learn about MySQL Constraint – UNIQUE and in this article, we will learn PRIMARY KEY constraint with example. Let’s go. MongoDB with .Net Core course link => https://www.udemy.com/mongodb-with-net-core-sagar-jaybhay/ What is the PRIMARY KEY? MySQL PRIMARY KEY constraint uniquely identifies each row in a table and also it creates a unique index for accessing the table will access faster. The columns of a Primary Key cannot have null values in MySQL and it must contain unique values. A Primary key has only one on a table, if the primary key used with multiple fields, then they are called a Composite Key. Syntax 1 for creating PRIMARY KEY with CREATE table statement.      ...
Read more