SQL Queries

Question: Select
Answer: SELECT is used to return data from a table.

 SELECT data1, data2 FROM TableName; 

Question: WHERE
Answer: The WHERE clause is used to filter records.

 SELECT * FROM TableName WHERE column1 = 'Value'; SELECT * FROM TableName WHERE column3 > Value1; 

Question: ORDER BY
Answer: ORDER BY is used to sort the result set in ascending or descending order.

 SELECT * FROM TableName ORDER BY column1; SELECT * FROM TableName ORDER BY column1 ASC, column3 DESC; 

Question: DISTINCT
Answer: DISTINCT is used to return only distinct values.

 SELECT DISTINCT column1, column2 FROM TableName ORDER BY column1; 

Question: AND Operator
Answer: The AND operator is used to filter records that satisfy more than one condition.

 SELECT * FROM TableName WHERE column1 = 'Value1' AND column2 LIKE 'C%'; 

Question: OR Operator
Answer: The OR operator is used to filter records that satisfy at least one of multiple conditions.

 SELECT * FROM TableName WHERE column1 = 'Value1' OR column1 = 'Value2'; 

Question: NOT Operator
Answer: The NOT operator is used to get the opposite result of a condition.

 SELECT * FROM TableName WHERE NOT column1 = 'Value1'; 

Question: SELECT TOP
Answer: SELECT TOP is used in SQL Server to specify the number of records to be fetched.

 SELECT TOP 10 * FROM TableName WHERE condition; 

This returns up to 10 records from the table that satisfy the condition.
Question: MIN and MAX
Answer: The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.

 SELECT MIN(Salary) FROM TableName WHERE condition; SELECT MAX(Salary) FROM TableName WHERE condition; 

Question: COUNT
Answer: The COUNT() function returns the number of rows that match the specified conditions.

 SELECT COUNT(columnName) FROM TableName WHERE condition; 

Question: SUM
Answer: The SUM() function returns the total sum of a numeric column.

 SELECT SUM(Salary) FROM TableName WHERE condition; 

Question: AVG
Answer: The AVG() function returns the average value of a numeric column.

 SELECT AVG(Salary) FROM TableName WHERE condition; 

Question: LIKE
Answer: The LIKE operator is used to search for a specific pattern in a column.
The % character represents zero or more characters.
The _ character represents a single character.

 SELECT * FROM TableName WHERE colName LIKE 'C%';
SELECT * FROM TableName WHERE colName LIKE 'C_Sh__';

Question: Wildcards
Answer: Wildcard characters used with the LIKE operator vary by SQL dialect. The following pattern characters are commonly associated with SQL Server and bracket expressions:

Symbol	Description
%	Represents zero or more characters.
_	Represents a single character.
[]	Represents any single character within the brackets in SQL Server.
^	When used immediately after the opening bracket in a SQL Server bracket expression, represents any character not in the brackets.
-	Represents a range of characters when used within a SQL Server bracket expression.

Question: IN Operator
Answer: The IN operator allows you to specify multiple values in a WHERE clause.

 SELECT * FROM TableName WHERE colName IN (value1, value2, value3); 

Question: BETWEEN
Answer: The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

 SELECT * FROM TableName WHERE age BETWEEN 18 AND 35; 

Question: IS NULL
Answer: The IS NULL operator checks whether a value is NULL. The IS NOT NULL operator checks whether a value is not NULL. operator returns the The EXCEPT operator returns rows from the first result set that are not present in the second result statement groups rows that have the same values into summary rows. It combines rows into groups andbr>

 SELECT * FROM TableName WHERE phoneNumber IS NULL;
SELECT * FROM TableName
WHERE phoneNumber IS NOT NULL;

Question: SQL Aggregate Functions
Answer: Commonly used SQL aggregate functions include:

• AVG() – returns the average of a set of values.
• COUNT() – returns the number of items in a set.
• MAX() – returns the maximum value in a set.
• MIN() – returns the minimum value in a set.
• SUM() – returns the sum of all or distinct values in a set.

Question: SQL Alias
Answer: A SQL alias allows you to assign a table or column a temporary name during the execution of a query. SQL has two types of aliases: table aliases and column aliases.

 SELECT firstName, lastName, Salary * 1.1 AS NewSalary FROM Employee WHERE condition; 

Question: SQL CASE
Answer: The CASE expression allows you to evaluate a list of conditions and return one of the possible results. The CASE expression has two formats: simple CASE and searched CASE.
Simple CASE expression:

 CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 ELSE elseResult END 

Example:

 SELECT name, salary, deptId, CASE deptId WHEN 1 THEN 'Management' WHEN 2 THEN 'Executive' WHEN 3 THEN 'Finance' WHEN 4 THEN 'Sales' END AS department FROM Employee ORDER BY name; 

Searched CASE expression example:

 SELECT name, deptId, CASE WHEN salary < 15000 THEN 'Low' WHEN salary > 15000 AND salary < 50000 THEN 'Average' WHEN salary > 50000 THEN 'High' END AS evaluation FROM Employee ORDER BY name; 

Question: UNION and UNION ALL Operator
Answer: The UNION operator is used to combine the result sets of two or more SELECT statements. UNION removes duplicate rows, while UNION ALL includes duplicate rows.

 SELECT column_names FROM table1 UNION SELECT column_names FROM table2; 

 SELECT column_names FROM table1 UNION ALL SELECT column_names FROM table2; 

Question: INTERSECT
Answer: The INTERSECT operator returns the intersection of two or more query result sets.

 SELECT column_names FROM table1 INTERSECT SELECT column_names FROM table2; 

Question: EXCEPT (MINUS in Oracle)
Answer: The EXCEPT operator returns rows from the first result set that are not present in the second result set. Oracle uses MINUS for this operation.

 SELECT column_names FROM table1 EXCEPT SELECT column_names FROM table2; 

Question: GROUP BY
Answer: The GROUP BY statement groups rows that have the same values into summary rows. It combines rows into groups and applies an aggregate function to each group.

 SELECT column1, column2, AGGREGATE_FUNCTION(column3) FROM TableName GROUP BY column1, column2;
SELECT COUNT(EmpId), column1
FROM TableName
GROUP BY column1;

Question: HAVING
Answer: The HAVING clause specifies a condition for filtering groups summarized by the GROUP BY clause.

 SELECT column1, column2, AGGREGATE_FUNCTION(column3) FROM TableName GROUP BY column1, column2 HAVING groupCondition;
SELECT COUNT(CustomerID), column1
FROM TableName
GROUP BY column1
HAVING COUNT(CustomerID) > 5;

Question: INSERT
Answer: INSERT is used to insert new rows into a table.

 INSERT INTO TableName(column1, column2, column3) VALUES (value1, value2, value3); 

Question: UPDATE
Answer: UPDATE is used to update existing records in a table.

 UPDATE tableName SET column1 = value1, column2 = value2 WHERE condition; 

Question: DELETE
Answer: DELETE is used to delete data from a table.

 DELETE FROM tableName WHERE condition; 

Question: CREATE TABLE
Answer: CREATE TABLE is used to create a new table in the database.

 CREATE TABLE tableName ( column1 datatype, column2 datatype, column3 datatype ); 

Example:

 CREATE TABLE Employee ( EmployeeID int, Name varchar(255), Dept_name varchar(255), City varchar(255) ); 

Question: ALTER TABLE
Answer: ALTER TABLE is used to modify the structure of an existing table.
To add a column in a table:

 ALTER TABLE tableName ADD columnName datatype; 

To drop a column in a table:

 ALTER TABLE tableName DROP COLUMN columnName; 

To rename a column in MySQL:

 ALTER TABLE tableName RENAME COLUMN oldName TO new_name; 

To rename a column in SQL Server:

 EXEC sp_rename 'tableName.oldName', 'new_name', 'COLUMN'; 

To alter or modify a datatype in SQL Server:

 ALTER TABLE tableName ALTER COLUMN columnName datatype; 

To alter or modify a datatype in MySQL:

 ALTER TABLE tableName MODIFY COLUMN columnName datatype; 

Question: DROP TABLE
Answer: DROP TABLE removes a table permanently.

 DROP TABLE tableName; 

Question: TRUNCATE TABLE
Answer: TRUNCATE TABLE removes all rows from a table efficiently.

 TRUNCATE TABLE tableName; 

Question: SQL Constraints
Answer: SQL constraints are used to specify rules for data in a table.

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that values in a column are unique.
• PRIMARY KEY: Uniquely identifies each row in a table and enforces uniqueness and non-nullability.
• FOREIGN KEY: Enforces a relationship between data in two tables.
• CHECK: Ensures that data in a column satisfies a specified condition.
• DEFAULT: Sets a default value for a column when no value is specified.

CREATE INDEX is not a constraint. It is used to create an index to improve data retrieval performance.
Question: PRIMARY KEY
Answer: A PRIMARY KEY is used to uniquely identify each record in a table.

 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); 

Question: FOREIGN KEY
Answer: The FOREIGN KEY constraint is used to enforce a relationship between tables and maintain referential integrity.
A foreign key is a field, or collection of fields, in one table that refers to a key in another table, typically the primary key.

 CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(ID) ); 

Question: UNIQUE
Answer: The UNIQUE constraint ensures that values in a column are unique.

 CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); 

Question: CHECK
Answer: The CHECK constraint is used to limit the value range that can be placed in a column.

 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age >= 18) ); 

Question: CAST() Function
Answer: The CAST() function is used to convert a value from one data type to another.

 SELECT CAST(290.5 AS varchar);
SELECT CAST('1987-08-13' AS datetime);

Question: CONVERT() Function
Answer: The CONVERT() function is used to convert a value from one data type to another. The following syntax is SQL Server syntax.

 SELECT CONVERT(dataType(length), expression, style); 

The style parameter is optional.
Examples:

 SELECT CONVERT(varchar, 2133);
SELECT CONVERT(varchar, '1987-08-13', 101);