Power BI Interview Questions And Answers | Power BI Interview Preparation

Read More

SQL Questions asked in interview | SQL Interview Questions and Answers

Read More

Comparison Operators in SQL Server: SQL AND, OR, BETWEEN, LIKE....

Introduction 

A comparison operator in SQL Server is a type of operator that is used to compare two or more values in a query. These operators allow you to compare values based on specific criteria such as equality, inequality, greater than, less than, and so on. Comparison operators are often used in the WHERE clause of a SELECT statement to filter data based on certain conditions. 
 
List of comparison operators available in SQL Server: 

• Equal (=) – used to compare two values for equality. 
  
• Not equal(<> or !=) – used to compare two values for inequality.
   
• Greater than (>) – used to compare if one value is greater than another. 
  
• Less than (<) – used to compare if one value is less than another. 
  
• Greater than or equal to (>=) – used to compare if one value is greater than or equal to another. 
  
• Less than or equal to (<=) – used to compare if one value is less than or equal to another. 
  
• LIKE – used to compare if a value matches a pattern. 
  
• BETWEEN – used to compare if a value is between two specified values. 
  
• IN – used to compare if a value matches any value in a list of values. 
  
• IS NULL/IS NOT NULL – used to check if a value is null or not null. 
  
• NOT LIKE – Used to specify a pattern to exclude from a search. 
  
• NOT BETWEEN – Used to specify a range of values to exclude from a search. 
  
• NOT IN – Used to specify a list of values to exclude from a search. 
  
• ALL – Used to specify that all values in a subquery must meet a certain condition. 
  
• ANY/SOME – Used to specify that at least one value in a subquery must meet a certain condition. 
  
• EXISTS – Used to check if a subquery returns any rows. 

To gain a better understanding of comparison operators, let’s explore all the operators using examples. 

We will now create a table named “students” that contains columns for “id”, “name”, “age”, and “grade”, followed by inserting some sample data into it. 

For creating table, you can use the below syntax: 

CREATE TABLE Students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade FLOAT
);
 
INSERT INTO Students (id, name, age, grade) VALUES
(1, 'John', 18, 3.5),
(2, 'Sarah', 19, 3.7),
(3, 'Mike', 17, 3.2),
(4, 'Emily', 18, 3.8),
(5, 'David', 19, 3.6);

Equal (=)

it is used to compare two values for equality. 
Example: find the student with an age of 18. 
Syntax: 

SELECT * FROM Students WHERE age = 18

Result:

Not Equal Operator (<> or !=):

it is used to compare two values for inequality. 
Example: find all students who are not 19 years old. 
Syntax: 

SELECT * FROM Students WHERE age <> 19;

OR 

SELECT * FROM Students WHERE age != 19;

Both syntaxes will give the same results, either use “<>” or “!=” 

Result:

Greater than Operator (>):

it is used to compare if one value is greater than another. 
Example: find all students who have a grade higher than 3.6. 
Syntax: 

SELECT * FROM students WHERE grade > 3.6;

 Result:

Less than Operator (<):

used to compare if one value is less than another. 
Example: find all students who have a grade lower than 3.5 
Syntax: 

SELECT * FROM students WHERE grade < 3.5;

Result:  

Greater than or equal to Operator (>=):

Used to compare if one value is greater than or equal to another. 
Example: find all students who are 18 or older. 
Syntax: 

SELECT * FROM Students WHERE age >= 18;

 Result:  

Less than or equal to Operator (<=):

used to compare if one value is less than or equal to another. 
Example: find all students who have a grade of 3.5 or lower. 
Syntax: 

SELECT * FROM Students WHERE grade <= 3.5;

Result:   

Like Operator:

Used to compare if a value matches a pattern. 
Suppose we want to find all the students whose name starts with “J”. We can use the following query: 

SELECT * FROM Students WHERE name LIKE 'J%';

 Result:  

Now, we want to find all the students whose name ends with “e”. We can use the following query: 

SELECT * FROM Students WHERE name LIKE '%e';

Result: 

Now, suppose we want to find all the students whose name second letter is “a”. We can use the following query: 

SELECT * FROM students WHERE name LIKE '_a%';

Result: 

Now, we want to find all the students whose name’s second last letter is “i”. We can use the following query: 

SELECT * FROM students WHERE name LIKE ‘%i_’;

Result: 

We will find all the students whose name starts with “M” and ends with “e”. We can use the following query:

SELECT * FROM students WHERE name LIKE 'M%e';

Result: 

NOT LIKE:

Not like operator is used to specify a pattern to exclude from a search. 
Let’s understand this operator using an example – 
Example: Find all the students from “Student” table, whose names do not contain the letter ‘a’. 

SELECT * FROM students
WHERE name NOT LIKE '%a%';

Result:

Between:

It is used to compare if a value is between two specified values. 
Suppose we want to find all the students whose age is between 18 and 20. We can use the following query: 

SELECT * FROM Students
WHERE age BETWEEN 18 AND 20;

Result:

NOT BETWEEN:

Used to specify a range of values to exclude from a search. 
Example – Find all the students who are not between the ages of 18 and 20. 
Syntax:

SELECT * FROM Students
WHERE age NOT BETWEEN 18 AND 20;

Result:  

IN operator:

It is used to compare if a value matches any value in a list of values. 
Example: Suppose we want to find all the students whose name is either “Emily” or “John”. We can use the following query: 

SELECT * FROM Students
WHERE name IN ('Emily', 'John');

Result:  

NOT IN:

Used to specify a list of values to exclude from a search. 
Example: find all the students who are not from the cities “New York” or “Los Angeles”.  
We can use the following query: 

SELECT * FROM Students
WHERE age NOT IN (18, 17);

Result: 

IS NULL and IS NOT NULL operator:

This operator, used to check if a value is null or not null. 
Suppose we want to find all the students who have not yet been assigned a grade. We can use the following query: 

IS NULL: 

SELECT * FROM Students
WHERE grade IS NULL;

Since the “Students” table does not have any null values, the query does not return any results. 

Result: 

IS NOT NULL: 

SELECT * FROM Students
WHERE grade IS NOT NULL;

Result: 

ALL:

ALL is a keyword that is used to compare a value with all the values in a list or a subquery. It returns true if the comparison is true for all the values in the list or subquery, and false otherwise.
Suppose we have an “orders” table with the following columns: “order_id”, “product_name”, “quantity”, “price”. 
To demonstrate the ALL operator, let’s insert some sample data into the table: 

CREATE TABLE Orders (
order_id INT PRIMARY KEY, 
product_name VARCHAR(50), 
quantity INT, 
price DECIMAL(10,2));

INSERT INTO Orders VALUES 
(1, 'Product A', 10, 100.00), 
(2, 'Product B', 5, 50.00), 
(3, 'Product C', 15, 75.00), 
(4, 'Product D', 20, 80.00), 
(5, 'Product E', 8, 120.00);

Example of using the ALL operator in SQL with a new “orders” table: 

Now let’s say we want to find all the orders with a quantity greater than or equal to the maximum quantity in the “orders” table. We can use the ALL operator in the following query: 

SELECT * FROM Orders

WHERE quantity >= ALL(SELECT MAX(quantity) FROM Orders);

Result: 

 

ANY: 
Used to specify that at least one value in a subquery must meet a certain condition. 
Example using ANY: 
SELECT product_name, quantity, price FROM Orders 
WHERE price > ANY (SELECT price FROM Orders WHERE quantity < 10);
In this example, the query selects the product_name, quantity, and price columns from the "Orders" table where the price is greater than any of the prices from orders where the quantity is less than 10. 
Result: 


It means that the query returns all rows from "orders" where the price is greater than to the price of any order, whose quantity is less than 10. 
EXISTS: 
Exists operator is used to check if a subquery returns any rows. 
Let’s create a table for understand the Exists Operator. 
CREATE TABLE Customers (
customer_id INT PRIMARY KEY, 
customer_name VARCHAR(50), 
city VARCHAR(50), 
state VARCHAR(50));

INSERT INTO Customers VALUES 
(1, 'John Doe', 'New York', 'NY'), 
(2, 'Jane Smith', 'Los Angeles', 'CA'), 
(3, 'Bob Johnson', 'Chicago', 'IL'), 
(4, 'Alice Lee', 'San Francisco', 'CA'), 
(5, 'Mike Brown', 'Houston', 'TX'), 
(6, 'Sarah Lee', 'San Francisco', 'CA'), 
(7, 'David Kim', 'New York', 'NY');
Now let's say we want to check if there are any customers from the state of "TX". We can use Now let's say we want to check if there are any customers from the state of "TX". We can use the EXISTS operator for this: 
SELECT * FROM Customers
WHERE EXISTS (SELECT * FROM Customers WHERE state = 'TX');


 This will return all the rows from the "customers" table where there exists at least one row where the state is "TX". In this case, it will return only one row (customer_id 5, Mike Brown). 
Interview related questions: 
What is the difference between the LIKE operator and the = operator in SQL? 
How does the BETWEEN operator work in SQL? 
What is the difference between the IN and NOT IN operators in SQL? 
How can you check for NULL values in SQL using comparison operators? 
What is the difference between the ANY and ALL operators in SQL? 
What is the purpose of the EXISTS operator in SQL? 
Can you combine comparison operators in SQL? If so, how? 
What is the order of precedence for comparison operators in SQL? 
How does the NOT operator affect comparison operators in SQL? 
What is the difference between the UNION and UNION ALL operators in SQL? 
Conclusion: 
In SQL Server, comparison operators are used to compare values between two expressions or operands. There are different types of comparison operators like equal to (=), not equal to (<> or !=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), LIKE, BETWEEN, IN, IS NULL/IS NOT NULL, ALL, ANY/SOME, and EXISTS. 
These operators are very useful when it comes to filtering data from the database tables. They help to retrieve specific data based on certain conditions. For example, the LIKE operator is used to retrieve data based on a specific pattern or string match, the BETWEEN operator is used to retrieve data between two values, the IN operator is used to retrieve data that matches a set of values, and the EXISTS operator is used to retrieve data that exists in a subquery. 
Interviewers often ask questions related to comparison operators during SQL Server interviews to assess the candidate's knowledge of SQL queries and their ability to retrieve specific data from a database table based on certain conditions. 

Read More

SQL - Rename Table and Database | How to rename table & database in SQL?

Definition:  

• Renaming a database  

Renaming a database in SQL refers to the process of changing the name of the entire database. This can be useful when a database needs to be rebranded or when the name of the database no longer accurately reflects its contents. Renaming a database updates the system catalogs and changes the physical database file names on disk. However, any connections to the database must be closed before renaming it, and the database must be in a single-user mode to perform the renaming operation. 

• Renaming a table 

Renaming a table in SQL refers to the process of changing the name of a specific table within a database. This can be useful for improving the clarity of the database schema, or for correcting a misspelled or inaccurate table name. Renaming a table does not affect any data within the table or any relationships between the table and other tables. 

Renaming a database  

Method1: Direct from object explorer 

Right click on Database>Rename

Method2 

Stntax: 

ALTER DATABASE Old_Database_name MODIFY NAME =New_Database_name; 

Example: 

ALTER DATABASE Bitacloud MODIFY NAME = Bita; 

Result:

Database_Name before execution of command. 

Database_Name after execution of command.

 

limitations for changing name of database in sql: 

• Single-User Mode: To change the name of a database in SQL Server, the database must be in single-user mode. This means that no other users or applications can be connected to the database while the rename operation is taking place. 
• User and Application Connections: All user and application connections must be closed before renaming the database. This can be disruptive to ongoing operations, and may require downtime for affected applications. 
• Database Replication: If the database is part of a replication topology, additional steps may be required to ensure that the replication process continues to function properly after the renaming operation. 
• Backup and Restore: Backup and restore operations may be affected by a database rename, and may require additional steps to ensure that backups and restores continue to function properly. 
• Linked Servers: If the database is linked to other servers, additional configuration steps may be required to ensure that the links continue to function properly after the renaming operation. 

• Security and Permissions: Any security and permissions settings associated with the old database name will need to be updated to reflect the new database name. 

Renaming a table 

Method1 

Method2 

Syntax: 

EXEC sp_rename ‘Old Table_Name’, ‘New Table_Name’ 

Example: 

EXEC sp_rename ‘dbo.products’, ‘product’ 

Result: 

Before:- Table_Name before execution of command. 

After:- Table_Name after execution of command. 

Read More