SQL requests

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))

Comments are closed.