{"id":154,"date":"2015-02-18T23:51:33","date_gmt":"2015-02-18T21:51:33","guid":{"rendered":"http:\/\/anisoft.org\/wordpress\/?p=154"},"modified":"2015-02-18T23:58:25","modified_gmt":"2015-02-18T21:58:25","slug":"154","status":"publish","type":"post","link":"https:\/\/anisoft.org\/wordpress\/?p=154","title":{"rendered":""},"content":{"rendered":"

SQL requests<\/h1>\n
\n

First some more easy sql requests.<\/h2>\n

Select all table records<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> * <\/code>FROM<\/code> Departments<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select any column from table<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> Name<\/code> FROM<\/code> Departments<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select concat names and put FullName for column name<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(FirstName, <\/code>' '<\/code>, LastName) FullName <\/code>FROM<\/code> Employees<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select non-repeated names (DISTINCT)<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> DISTINCT<\/code> Salary <\/code>FROM<\/code> Employees<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select records that have given JobTitle<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> * <\/code>FROM<\/code> Employees e<\/code><\/div>\n
WHERE<\/code> JobTitle = <\/code>'Sales Representative'<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select two columns where name have ei somewhere in it<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> FirstName, LastName <\/code>FROM<\/code> Employees<\/code><\/div>\n
WHERE<\/code> LastName <\/code>like<\/code> '%ei%'<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select Salery where it is between any values<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> Salary <\/code>FROM<\/code> Employees e<\/code><\/div>\n
WHERE<\/code> Salary <\/code>BETWEEN<\/code> 20000 <\/code>AND<\/code> 30000<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select Employees where their salaty is any of given values(in)<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> FirstName, LastName <\/code>FROM<\/code> Employees<\/code><\/div>\n
WHERE<\/code> Salary <\/code>IN<\/code> (25000, 14000, 12500, 23600)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select Employees and sort them(in)<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> FirstName, LastName, Salary <\/code>FROM<\/code> Employees<\/code><\/div>\n
WHERE<\/code> Salary > 50000<\/code><\/div>\n
ORDER<\/code> BY<\/code> Salary <\/code>DESC<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Select first 5 Employees (top)<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> TOP<\/code> 5 FirstName, LastName, Salary <\/code>FROM<\/code> Employees<\/code><\/div>\n
WHERE<\/code> Salary > 50000<\/code><\/div>\n
ORDER<\/code> BY<\/code> Salary <\/code>DESC<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

More complicated sql requests.<\/h2>\n

Join data from different tables<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> FirstName, LastName, AddressText<\/code><\/div>\n
FROM<\/code> Employees e<\/code><\/div>\n
JOIN<\/code> Addresses a<\/code><\/div>\n
ON<\/code> e.AddressID = a.AddressID<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

or<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> FirstName, LastName, AddressText<\/code><\/div>\n
FROM<\/code> Employees e, Addresses a<\/code><\/div>\n
WHERE<\/code> e.AddressID = a.AddressID<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Join data from three tables<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(e.FirstName, <\/code>' '<\/code>, e.LastName) <\/code>AS<\/code> FullName,\u00a0 CONCAT(m.FirstName, <\/code>' '<\/code>, m.LastName) ManagerFullName, a.AddressText<\/code><\/div>\n
FROM<\/code> Employees e<\/code><\/div>\n
JOIN<\/code> Employees m<\/code><\/div>\n
on<\/code> e.ManagerID = m.EmployeeID<\/code><\/div>\n
JOIN<\/code> Addresses a<\/code><\/div>\n
on<\/code> e.AddressID = a.AddressID<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Nested select with agregate function(function that return number)<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(FirstName, <\/code>' '<\/code>, LastName) <\/code>AS<\/code> FullName, Salary<\/code><\/div>\n
FROM<\/code> Employees<\/code><\/div>\n
WHERE<\/code> Salary = (<\/code>SELECT<\/code> MIN<\/code>(Salary) <\/code>FROM<\/code> Employees)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find the full name, salary and department of the employees that take the minimal salary in their department<\/h3>\n

We have one join and where for salary equal to min salary by departments<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(FirstName, <\/code>' '<\/code>, LastName) <\/code>AS<\/code> FullName, Salary, d.<\/code>Name<\/code><\/div>\n
FROM<\/code> Employees e <\/code>JOIN<\/code> Departments d<\/code><\/div>\n
ON<\/code> d.DepartmentID = e.DepartmentID<\/code><\/div>\n
WHERE<\/code> Salary =<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>(<\/code>SELECT<\/code> MIN<\/code>(Salary) <\/code>FROM<\/code> Employees<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>WHERE<\/code> DepartmentID = e.DepartmentID)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>order<\/code> by<\/code> d.<\/code>Name<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find the average salary in the \u201cSales\u201d department<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> AVG<\/code>(Salary) [Avarage Salary] <\/code>FROM<\/code> Employees e<\/code><\/div>\n
JOIN<\/code> Departments d<\/code><\/div>\n
ON<\/code> e.DepartmentID = d.DepartmentID<\/code><\/div>\n
WHERE<\/code> d.<\/code>Name<\/code> = <\/code>'Sales'<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find the number of all employees that have manager<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> COUNT<\/code>(*) [<\/code>Count<\/code> Employees]<\/code><\/div>\n
FROM<\/code> Employees e<\/code><\/div>\n
WHERE<\/code> ManagerID <\/code>IS<\/code> NOT<\/code> NULL<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find all departments and the average salary for each of them<\/h3>\n

When we have aggregate function and colum we must group records by column name.<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> d.<\/code>Name<\/code> [Department], <\/code>AVG<\/code>(Salary) [Avarage Salary]<\/code><\/div>\n
FROM<\/code> Employees e<\/code><\/div>\n
JOIN<\/code> Departments d<\/code><\/div>\n
ON<\/code> e.DepartmentID = d.DepartmentID<\/code><\/div>\n
GROUP<\/code> BY<\/code> d.<\/code>Name<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find all managers that have exactly 5 employees<\/h3>\n

When we have grouping and must filter by something we use HAVING.<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(e.FirstName, <\/code>' '<\/code>, e.LastName) ManagerName, <\/code>COUNT<\/code>(*) [<\/code>Count<\/code> Employees]<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>FROM<\/code> Employees e<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>JOIN<\/code> Employees em<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>ON<\/code> em.ManagerID = e.EmployeeID<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>GROUP<\/code> BY<\/code> e.FirstName, e.LastName<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>HAVING<\/code> COUNT<\/code>(*) = 5<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

or
\nwe can create temporary table it nested select and make where on it<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> * <\/code>FROM<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>(<\/code>SELECT<\/code> CONCAT(e.FirstName, <\/code>' '<\/code>, e.LastName) ManagerName, <\/code>COUNT<\/code>(*) [<\/code>Count<\/code> Employees]<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>FROM<\/code> Employees e<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>JOIN<\/code> Employees em<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>ON<\/code> em.ManagerID = e.EmployeeID<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>GROUP<\/code> BY<\/code> e.FirstName, e.LastName) <\/code>AS<\/code> Managers<\/code><\/div>\n
WHERE<\/code> Managers.[<\/code>Count<\/code> Employees] = 5<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Find all employees along with their managers. For employees that do not have manager display the value \u201c(no manager)\u201d.<\/h3>\n

We must use if statement in sql this is (case -> then -> else).<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(e.FirstName, <\/code>' '<\/code>, e.LastName) <\/code>AS<\/code> FullName,<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>CASE<\/code> WHEN<\/code> CONCAT(m.FirstName, <\/code>' '<\/code>, m.LastName) <> <\/code>' '<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>THEN<\/code> CONCAT(m.FirstName, <\/code>' '<\/code>, m.LastName)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>ELSE<\/code> 'no manager'<\/code> END<\/code> AS<\/code> ManagerFullName<\/code><\/div>\n
FROM<\/code> Employees e<\/code><\/div>\n
LEFT<\/code> JOIN<\/code> Employees m<\/code><\/div>\n
on<\/code> e.ManagerID = m.EmployeeID<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Use LEN for string length.<\/h3>\n
\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(e.FirstName, <\/code>' '<\/code>, e.LastName) ManagerName<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>FROM<\/code> Employees e<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>WHERE<\/code> LEN(e.LastName) = 5<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Get date in a specific format.<\/h3>\n

The format must be given with numbers.<\/p>\n

\n
\n\n\n\n
\n
\n
SELECT<\/code> CONCAT(<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CONVERT<\/code>(<\/code>varchar<\/code>, GETDATE(), 104),<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>' '<\/code>,<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>CONVERT<\/code>(<\/code>varchar<\/code>, GETDATE(), 114))<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[],"class_list":["post-154","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=154"}],"version-history":[{"count":5,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions"}],"predecessor-version":[{"id":159,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions\/159"}],"wp:attachment":[{"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anisoft.org\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}