SQL with Microsoft SQL Server

Introduction to SQL
SQL (Structured Query Language) is a language used to communicate with and manipulate
databases. It's a standard language for managing data in relational database management
systems (RDBMS).

Data Manipulation Language (DML)

DML commands are used to manage data within database objects. The most common DML
commands are:

INSERT: Adds new data to a table.

UPDATE: Modifies existing data in a table.
DELETE: Removes data from a table.
SELECT: Retrieves data from one or more tables.

INSERT Data

The INSERT INTO statement is used to add new rows to a table.

Syntax: INSERT INTO table_name (column_name1, column_name2, ...) VALUES (expression1,

expression2, ...);

You can specify the columns you want to insert data into.
If you are providing data for all columns in the table, you can omit the column names.

Example (inserting data for all columns): INSERT INTO Client VALUES ('C200', 'Gayan', 'Galle',
0712569014, 15000, '2012-12-21');

Example (inserting data for specific columns): If order_date is not available for a new client:
INSERT INTO Client (client_Id, Client Name, Address, Telephone, Balance_due) VALUES ('C300',
'Pathum', 'Matara', 0772569025, 14000);

Inserting Multiple Rows: You can insert multiple rows with a single INSERT statement by
providing multiple sets of values separated by commas.

Example:

INSERT INTO Client VALUES

('C400', 'Ashan', 'Colombo', 0722569014, 15000, '2012-05-11'),
('C500', 'Kamala', 'Kandy', 0712569014, 17500, '2013-05-11'),
('C600', 'Waruna', 'Galle', 0782569014, 18500, '2012-06-11'),
('C700', 'Sadun', 'Kandy', 0712562514, 19500, '2012-05-11');

UPDATE Data

The UPDATE statement is used to modify existing records in a table. The WHERE clause is
crucial for specifying which rows to update.

Syntax: UPDATE table_name SET column_name = new_value WHERE condition;

Always use the WHERE clause to target specific rows. If omitted, all rows in the table will be
updated.
It's best practice to use the primary key in the WHERE clause when updating a single record.

Example (updating a specific client's address): UPDATE Client SET Client_Address = 'Matara'
WHERE client_ID = 'C100';

Example (updating multiple columns for a specific client): UPDATE Client SET balance_due =
20000, Client_Address = 'Kurunagala' WHERE Client_Id = 'C300';

Example (updating a single column for a specific client): UPDATE Client SET balance_due =
18900 WHERE Client_Id = 'C200';

Example (updating a column for multiple clients based on a condition): UPDATE Client SET
balance_due = 45000 WHERE Client_Address = 'Kandy';

Example (updating a column for all clients): UPDATE Client SET balance_due = 5000;

Example (updating a column by a percentage): To update balance_due by 15% for client 'C100':
UPDATE Client SET balance_due = balance_due * 1.15 WHERE Client_Id = 'C100';

To update balance_due by 10% for all clients: UPDATE Client SET balance_due = balance_due *
1.10;

DELETE Data

The DELETE statement is used to remove rows from a table. The WHERE clause specifies which
rows to delete.

Syntax: DELETE FROM table_name WHERE condition;

 If the WHERE clause is omitted, all rows in the table will be deleted.

Example (deleting a specific client): DELETE FROM Client WHERE Client_Id = 'C100';

Example (deleting clients based on a condition): DELETE FROM Client WHERE Client_Address =
'Kandy';

DELETE FROM Client WHERE Balance_due >= 22000;

Example (deleting all data from a table): DELETE FROM Client;

Difference Between DROP, TRUNCATE, and DELETE

DROP: Removes the table structure and all data. Cannot be undone. Locks the entire table.
TRUNCATE: Removes only the data from the table, but keeps the table structure. Cannot be
undone. Executes very fast and locks the entire table.
DELETE: Removes data from the table, with an optional WHERE clause. Can be undone if
within a transaction. Locks rows, making it slower than TRUNCATE for large datasets.

Transactions: Commands like DELETE can be part of a transaction, allowing for rollback.

Example (using transactions):

BEGIN TRANSACTION;
DROP TABLE Client;
-- If you decide to keep the table:
-- ROLLBACK TRANSACTION;
-- If you want to permanently remove it:
-- COMMIT TRANSACTION;

Querying Tables with SELECT Command

The SELECT statement is used to retrieve data from a database.

Syntax: SELECT [DISTINCT] column_list FROM table_list [WHERE condition] [GROUP BY

column_list] [HAVING condition] [ORDER BY column_list];

Selecting Data
Selecting All Columns: Use the asterisk (*) to select all columns. SELECT * FROM Client;
Selecting Specific Columns: List the desired column names, separated by commas. SELECT
Client_Name FROM Client; SELECT Client_Name, Client_Address, TP FROM Client;
Selecting Distinct Data: Use the DISTINCT keyword to return only unique values in the
 specified columns. SELECT DISTINCT name FROM client;

Filtering Data with WHERE Clause

The WHERE clause filters records based on a specified condition.

Logical Operators:

=: Equal to
>: Greater than
>=: Greater than or equal to
<: Less than
<=: Less than or equal to
<> or !=: Not equal to

Example (finding clients with balance greater than 5000): SELECT Client_Name, Client_Address
FROM Client WHERE Balance_due > 5000;

Example (finding clients with balance less than or equal to 15000): SELECT * FROM Client
WHERE Balance_due <= 15000;

Boolean Operators:

AND: Returns a record if all conditions are true.

OR: Returns a record if at least one condition is true.

Example (finding clients in Colombo with balance over 10000): SELECT Client_Name,
Client_Address FROM Client WHERE Balance_due > 10000 AND Client_Address = 'Colombo';

Example (finding clients in Colombo or Kandy): SELECT * FROM Client WHERE Client_Address =
'Colombo' OR Client_Address = 'Kandy';

Other Comparison Operators:

BETWEEN value1 AND value2: Checks if a value is between two specified values (inclusive).
IN (value1, value2, ...): Checks if a value matches any value in a list.
LIKE pattern
: Searches for a specified pattern in a string column.
%: Represents zero or more characters.
_: Represents exactly one character.

NOT: Negates the operator.

IS NULL: Checks if a value is NULL.

Example (using BETWEEN): Find employees with salaries between $25,000 and $50,000.
SELECT Eno, Position, Salary
FROM Employee
WHERE Salary BETWEEN 25000 AND 50000;

Example (using IN): Find employees with the designation of 'Manager' or 'Cashier'.

SELECT Eno, Ename, Position, Salary

FROM Employee
WHERE Position IN ('Manager', 'Cashier');

Example (using LIKE): Find employees whose name starts with 'a'. SELECT Eno, Ename, Position,
Salary FROM Employee WHERE Ename LIKE 'a%';

Find employees whose name contains 'a'. SELECT Eno, Ename, Position, Salary FROM Employee
WHERE Ename LIKE '%a%';

Find employees whose name starts with 'a' followed by exactly 4 characters. SELECT Eno,
Ename, Position, Salary FROM Employee WHERE Ename LIKE 'a____';

Find employees whose name ends with 'i'. SELECT Eno, Ename, Position, Salary FROM Employee
WHERE Ename LIKE '%i';

Example (using NOT LIKE): Find employees whose name does not start with 'A'. SELECT * FROM
Employee WHERE Ename NOT LIKE 'a%';

Example (using NOT IN): Find employees who are not 'Manager'. SELECT * FROM Employee
WHERE Position NOT IN ('Manager');

Example (using IS NULL): Find employees who do not have a position assigned. SELECT * FROM
Employee WHERE Position IS NULL;

Ordering Data with ORDER BY Clause

The ORDER BY clause sorts the result set in ascending or descending order.

ASC: Ascending order (default).

DESC: Descending order.

Syntax: ORDER BY column_name [ASC|DESC], column_name [ASC|DESC], ...;

Example (ordering by name ascending): SELECT name FROM client ORDER BY name;
Example (ordering by name descending): SELECT * FROM client ORDER BY name DESC;

Example (ordering by multiple criteria): SELECT * FROM client ORDER BY name, client_no DESC;

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value.

COUNT(): Counts the number of rows.

MIN(): Finds the minimum value.
MAX(): Finds the maximum value.
SUM(): Calculates the sum of values.
AVG(): Calculates the average of values.

Example (average salary of all employees): SELECT AVG(Salary) FROM Employee;

Example (total salary of all employees): SELECT SUM(Salary) FROM Employee;

Example (average salary of managers): SELECT AVG(Salary) FROM Employee WHERE Position =
'Manager';

Example (maximum salary): SELECT MAX(Salary) FROM Employee;

Example (sum of salary for cashiers): SELECT SUM(Salary) FROM Employee WHERE Position =
'Cashier';

Using Aliases: You can rename the output column of an aggregate function using the AS
keyword.

Example (average salary of cashiers with an alias): SELECT AVG(Salary) AS Salary_Avg FROM
Employee WHERE Position = 'Cashier';

Example (counting records with an alias): SELECT COUNT(*) AS Number_OF_EMP FROM

Employee;

Example (minimum salary for Department D3): SELECT MIN(Salary) AS Min_sal FROM Employee
WHERE Dno = 'D3';

GROUP BY Clause
The GROUP BY clause groups rows that have the same values in one or more columns into a
summary row. It is often used with aggregate functions.

Syntax: GROUP BY column_name1, column_name2, ...;

Example (total salary per department): SELECT Dno, SUM(Salary) AS Dep_Sum_Sal FROM
Employee GROUP BY Dno;

Example (total salary per position): SELECT Position, SUM(Salary) AS Tot_Sal FROM Employee
GROUP BY Position;

Example (average salary of managers per department): SELECT Dno, AVG(Salary) AS AVG_SAL
FROM Employee WHERE Position = 'Manager' GROUP BY Dno;

Example (number of employees per position per department): SELECT Dno, Position,
COUNT(Eno) FROM Employee GROUP BY Dno, Position;

HAVING Clause

The HAVING clause is used to filter groups created by the GROUP BY clause based on specified
conditions. It works similarly to WHERE, but WHERE filters individual rows before grouping, while
HAVING filters the groups after aggregation.

Syntax: HAVING condition;

Example (departments with total salary exceeding $75,000): SELECT Dno, SUM(Salary) AS Total
FROM Employee GROUP BY Dno HAVING SUM(Salary) > 75000;

Example (departments with total salary of cashiers exceeding $10,000): SELECT Dno,
SUM(Salary) AS Total FROM Employee WHERE Position = 'Cashier' GROUP BY Dno HAVING
SUM(Salary) > 10000;

Example (departments with more than 1 manager): SELECT Dno, COUNT(Eno) AS Emp_Count
FROM Employee WHERE Position = 'Manager' GROUP BY Dno HAVING COUNT(Eno) > 1;

Example (departments (excluding managers) with total salary exceeding $25,000): SELECT Dno,
SUM(Salary) AS Total FROM Employee WHERE Position != 'Manager' GROUP BY Dno HAVING
SUM(Salary) > 25000 ORDER BY SUM(Salary) DESC;
Joining Tables
A JOIN clause combines rows from two or more tables based on a related column between
them.

Types of Joins
INNER JOIN: Returns only rows where the join condition is met in both tables.
LEFT JOIN: Returns all rows from the left table and matched rows from the right table. If no
match, NULL values are returned for the right table's columns.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. If no
match, NULL values are returned for the left table's columns.
FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table. If no
match, NULL values are returned for the non-matching side.

INNER JOIN

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example (employee details with department name):

SELECT [Link], [Link], [Link], [Link]

FROM Employee
INNER JOIN Department
ON [Link] = [Link];

Alternative Syntax (using WHERE clause):

SELECT Eno, Ename, DeptNo, Dname

FROM Employee, Department
WHERE [Link] = [Link];

Alternative Syntax (using aliases):

SELECT [Link], [Link], [Link], [Link]

FROM Employee E, Department D
WHERE [Link] = [Link];

Example (employees earning more than $25,000 with department name):

SELECT Eno, Ename, Salary, Dname
FROM Employee, Department
WHERE [Link] = [Link]
AND Salary > 25000;

Example (employees in Colombo with department name):

SELECT Eno, Ename, Salary, EAddress, Dname

FROM Employee, Department
WHERE [Link] = [Link]
AND EAddress = 'Colombo';

Example (employees in Colombo earning between $15,000 and $65,000 with department name):

SELECT Eno, Ename, Salary, EAddress, Dname

FROM Employee, Department
WHERE [Link] = [Link]
AND EAddress = 'Colombo' AND Salary BETWEEN 15000 AND 65000;

Example (departments with total salary exceeding $75,000, showing department name):

SELECT DeptNo, Dname, SUM(Salary)

FROM Department, Employee
WHERE [Link] = [Link]
GROUP BY DeptNo, Dname
HAVING SUM(Salary) > 75000;

Example (departments with total salary exceeding $75,000, excluding managers):

SELECT Dno, Dname, SUM(Salary)

FROM Employee, Department
WHERE [Link] = [Link]
AND Position != 'Manager'
GROUP BY Dno, Dname
HAVING SUM(Salary) > 75000;

LEFT JOIN

Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example (all employees and their department details, showing NULL for departments with no
employees):
SELECT DeptNo, Dname, Eno, Ename
FROM Employee
LEFT JOIN Department
ON [Link] = [Link];

RIGHT JOIN

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example (all departments and their employees, showing NULL for employees with no
department):

SELECT DeptNo, Dname, Eno, Ename

FROM Employee
RIGHT JOIN Department
ON [Link] = [Link];

FULL OUTER JOIN

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example (all records from both tables, showing NULL where no match exists):

SELECT DeptNo, Dname, Eno, Ename

FROM Employee
FULL OUTER JOIN Department
ON [Link] = [Link];

Joining Multiple Tables

You can join more than two tables by adding additional JOIN clauses.

Example (joining Employee, Department, and Project tables):

SELECT [Link], [Link], [Link], [Link]
FROM ((Employee
INNER JOIN Department ON [Link] = [Link])
INNER JOIN Project ON [Link] = [Link]);