SQL Queries

Question: Select

Answer:  Return data from the table

SELECT data1, data2 FROM TableName;

Question: where

Answer: Where clause is used to filter records.

SELECT * FROM TableName WHERE column1='Value';

SELECT * FROM TableName WHERE column3 > Values1;

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: Used to return only distinct values.

SELECT Distinct column1, column2, ... 

FROM TableName ORDER BY column1;

Question: And Operator

Answer: Used to filter records for more than one condition

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

Question: OR Operator

Answer: used to filter records for more than one condition

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

Question: NOT Operator

Answer: To get opposite result

SELECT * FROM TableName WHERE NOT column1 = 'Value1'

Question: Select Top

Answer: Select Top is used to specify the number of records to be fetched

SELECT TOP 10 * FROM TableName WHERE condition

returns 10 records from the table

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: Count() function return the numbers of rows that matches the specific conditions.

SELECT COUNT(columnName) FROM TableName WHERE condition

Question: Sum

Answer: Returns the total sum of a numeric column.

SELECT SUM(Salary) FROM TableName WHERE condition

Question: Avg 

Answer: Avg() - Returns the average value of a numeric column.

SELECT AVG(Salary) FROM TableName WHERE condition

Question: Like

Answer: Like operator is used to search for a specific pattern in a colum.

%  (percent sign) represents multiple characters

_  (underscore sign) represents single character

SELECT * FROM TableName WHERE colName LIKE 'C%'

SELECT * FROM TableName WHERE colName LIKE 'C_Sh__'

Question: WildCards

Answer: 

Symbol | Description

%  Represents zero or more characters

_ Represents a single character

[] Represents any single character within the brackets *

^ Represents any character not in the brackets *

- Represents any single character within the specified range *

{} Represents any escaped character **

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: 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 compares a value with a null value and returns true if the compared value is null; otherwise, it returns false.

SELECT * FROM TableName
WHERE phoneNumber IS NULL;

SELECT * FROM TableName
WHERE phoneNumber IS NOT NULL;

Question: SQL Aggregate Functions

Answer: The following are the commonly used SQL aggregate functions:

 AVG() – returns the average of a set.

 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: SQL alias allows you to assign a table or a column a temporary name during the execution of a query. SQL has two types of aliases: table and column aliases.

SELECT firstName, lastName, 

Salary * 1.1 AS NewSalary FROM Employee

WHERE condition

Question: SQL CASE

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

Simple CASE expression : -

CASE expression 

CASE when_expression1 THEN

  RESULT_1

CASE when_expression2 THEN

  RESULT_2

CASE when_expression3 THEN

  RESULT_3

 ...

ELSE elseResult

END

e.g. -

SELECT name, salary, deptId,

CASE (deptId) 

CASE 1 THEN 'Management'

CASE 2 THEN 'Executive'

CASE 3 THEN 'Finance'

CASE 4 THEN 'Sales'

END department

FROM Employee ORDER BY name;

Search 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 evaluation

FROM Employee ORDER BY name;

Question: UNION and UNION ALL operator

Answer:  UNION operator is used to combine the result set of two or more select statement.

SELECT column_names FROM table1
UNION
SELECT column_names FROM table2;

UNION ALL will allows all values while UNION provides only distinct values

SELECT column_names FROM table1
UNION ALL
SELECT column_names FROM table2;

Question: INTERSECT

Answer: Return the intersection of two or more queries using the INTERSECT operator.

SELECT column_names FROM table1
INTERSECT
SELECT column_names FROM table2;

Question: MINUS 

Answer: subtract a result set from another result set using the MINUS operator.

SELECT column_names FROM table1
MINUS
SELECT column_names FROM table2;

Question: GROUP BY

Answer: GROUP BY statement groups rows that have the same values into summary rows

It combines rows into groups and apply an aggregate function to each group.

SELECT column1, column2, 

AGGEGRATE_FUNCTION(column3)

FROM TableName

GROUP BY colume1,column2;

SELECT COUNT(EmpId), colume1
FROM TableName
GROUP BY colume1;

Question: HAVING

Answer: Specify a condition for filtering groups summarized by the GROUP BY clause

SELECT column1, column2,
AGGEGRATE_FUNCTION(column3)
FROM TableName
GROUP BY colume1,column2
HAVING groupCondition ;


SELECT COUNT(CustomerID), colume1
FROM TableName
GROUP BY colume1
HAVING COUNT(CustomerID) > 5;

Question: INSERT

Answer: Used to insert new rows into a table.

INSERT INTO TableName(column1, column2, column3 ....)
VALUES (values1, values2, values3 ....);

Question: UPDATE

Answer: update existing record in a table.

UPDATE tableName

SET column1=values1,

 column2=values2

WHERE condition ;

Question: DELETE

Answer:  delete data from a table

DELETE FROM tableName

WHERE condition ;

Question: CREATE TABLE

Answer: create a new table in the database

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

e.g.

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

Question: ALTER TABLE

Answer: Modify the structure of an existing table.

To add a column in a table

ALTER TABLE tableName
ADD columnName datatype;

drop a column in a table

ALTER TABLE tableName
DROP COLUMN columnName;

Rename column in a table

ALTER TABLE tableName
RENAME COLUMN oldName to new_name;

Alter/Modify datatype in a table  

SQL Server -

ALTER TABLE tableName
ALTER COLUMN columnName datatype;

MySQL -

ALTER TABLE tableName
ALTER COLUMN columnName datatype;

Rename column in a table

ALTER TABLE tableName
RENAME COLUMN oldName to new_name;

Question: DROP TABLE

Answer:  remove the tables permanently

DROP TABLE tableName;

Question: TRUNCATE TABLE

Answer: delete all data in a big table fast and efficiently.

TRUNCATE TABLE tableName;

Question: SQL Constraints

Answer: SQL Constraints are used to specify the rules for data in the table.

NOT NULL - Validate that a column cannot have a NULL value

UNIQUE - Validate that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - walk you through the steps of enforcing the relationship between data in two tables using the foreign key constraint.

CHECK - Validate data in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

CREATE INDEX - Used to create and retrieve data from the database very quickly

Question: PRIMARY KEY

Answer: It 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 prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

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

Question: UNIQUE

Answer: 

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: used to cast datatype

SELECT CAST(290.5 AS varchar);

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

Question: Covert() Function

Answer: used to covert datatype

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

* style is optional

e.g. -

SELECT CONVERT(varchar, 2133)

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