First some more easy sql requests.
Select all table records
SELECT * FROM Departments
|
Select any column from table
SELECT Name FROM Departments
|
Select concat names and put FullName for column name
SELECT CONCAT(FirstName, ' ', LastName) FullName FROM Employees
|
Select non-repeated names (DISTINCT)
SELECT DISTINCT Salary FROM Employees
|
Select records that have given JobTitle
SELECT * FROM Employees e
WHERE JobTitle = 'Sales Representative'
|
Select two columns where name have ei somewhere in it
SELECT FirstName, LastName FROM Employees
WHERE LastName like '%ei%'
|
Select Salery where it is between any values
SELECT Salary FROM Employees e
WHERE Salary BETWEEN 20000 AND 30000
|
Select Employees where their salaty is any of given values(in)
SELECT FirstName, LastName FROM Employees
WHERE Salary IN (25000, 14000, 12500, 23600)
|
Select Employees and sort them(in)
SELECT FirstName, LastName, Salary FROM Employees
WHERE Salary > 50000
ORDER BY Salary DESC
|
Select first 5 Employees (top)
SELECT TOP 5 FirstName, LastName, Salary FROM Employees
WHERE Salary > 50000
ORDER BY Salary DESC
|
More complicated sql requests.
Join data from different tables
SELECT FirstName, LastName, AddressText
FROM Employees e
JOIN Addresses a
ON e.AddressID = a.AddressID
|
or
SELECT FirstName, LastName, AddressText
FROM Employees e, Addresses a
WHERE e.AddressID = a.AddressID
|
Join data from three tables
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS FullName, CONCAT(m.FirstName, ' ', m.LastName) ManagerFullName, a.AddressText
FROM Employees e
JOIN Employees m
on e.ManagerID = m.EmployeeID
JOIN Addresses a
on e.AddressID = a.AddressID
|
Nested select with agregate function(function that return number)
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, Salary
FROM Employees
WHERE Salary = (SELECT MIN(Salary) FROM Employees)
|
Find the full name, salary and department of the employees that take the minimal salary in their department
We have one join and where for salary equal to min salary by departments
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, Salary, d.Name
FROM Employees e JOIN Departments d
ON d.DepartmentID = e.DepartmentID
WHERE Salary =
(SELECT MIN(Salary) FROM Employees
WHERE DepartmentID = e.DepartmentID)
order by d.Name
|
Find the average salary in the “Sales” department
SELECT AVG(Salary) [Avarage Salary] FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE d.Name = 'Sales'
|
Find the number of all employees that have manager
SELECT COUNT(*) [Count Employees]
FROM Employees e
WHERE ManagerID IS NOT NULL
|
Find all departments and the average salary for each of them
When we have aggregate function and colum we must group records by column name.
SELECT d.Name [Department], AVG(Salary) [Avarage Salary]
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
GROUP BY d.Name
|
Find all managers that have exactly 5 employees
When we have grouping and must filter by something we use HAVING.
SELECT CONCAT(e.FirstName, ' ', e.LastName) ManagerName, COUNT(*) [Count Employees]
FROM Employees e
JOIN Employees em
ON em.ManagerID = e.EmployeeID
GROUP BY e.FirstName, e.LastName
HAVING COUNT(*) = 5
|
or
we can create temporary table it nested select and make where on it
SELECT * FROM
(SELECT CONCAT(e.FirstName, ' ', e.LastName) ManagerName, COUNT(*) [Count Employees]
FROM Employees e
JOIN Employees em
ON em.ManagerID = e.EmployeeID
GROUP BY e.FirstName, e.LastName) AS Managers
WHERE Managers.[Count Employees] = 5
|
Find all employees along with their managers. For employees that do not have manager display the value “(no manager)”.
We must use if statement in sql this is (case -> then -> else).
SELECT CONCAT(e.FirstName, ' ', e.LastName) AS FullName,
CASE WHEN CONCAT(m.FirstName, ' ', m.LastName) <> ' '
THEN CONCAT(m.FirstName, ' ', m.LastName)
ELSE 'no manager' END AS ManagerFullName
FROM Employees e
LEFT JOIN Employees m
on e.ManagerID = m.EmployeeID
|
Use LEN for string length.
SELECT CONCAT(e.FirstName, ' ', e.LastName) ManagerName
FROM Employees e
WHERE LEN(e.LastName) = 5
|
Get date in a specific format.
The format must be given with numbers.
SELECT CONCAT(
CONVERT(varchar, GETDATE(), 104),
' ',
CONVERT(varchar, GETDATE(), 114))
|
