{"id":800,"date":"2017-03-29T02:46:13","date_gmt":"2017-03-29T10:46:13","guid":{"rendered":"http:\/\/www.tech.dimprash.com\/?p=800"},"modified":"2017-03-29T11:07:00","modified_gmt":"2017-03-29T19:07:00","slug":"sql-brushup","status":"publish","type":"post","link":"http:\/\/www.tech.dimprash.com\/?p=800","title":{"rendered":"SQL Brushup"},"content":{"rendered":"

http:\/\/a4academics.com\/interview-questions\/53-database-and-sql\/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&limitstart=<\/a><\/p>\n

Get employee details from employee table whose employee name are \u201cJohn\u201d and \u201cRoy\u201d<\/strong>
\nSelect * from EMPLOYEE where FIRST_NAME in (‘John’,’Roy’)<\/p>\n

Get employee details from employee table whose employee name are not \u201cJohn\u201d and \u201cRoy\u201d<\/strong>
\nSelect * from EMPLOYEE where FIRST_NAME not in (‘John’,’Roy’)<\/p>\n

Get employee details from employee table whose first name starts with ‘J’<\/strong><\/p>\n

Select * from EMPLOYEE where FIRST_NAME like ‘J%’<\/p>\n

Get employee details from employee table whose first name contains ‘o’<\/strong><\/p>\n

Select * from EMPLOYEE where FIRST_NAME like ‘%o%’<\/p>\n

Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters<\/strong><\/p>\n

Select * from EMPLOYEE where FIRST_NAME like ‘___n’ (Underscores)<\/p>\n

Get employee details from employee table whose Salary between 500000 and 800000<\/strong><\/p>\n

Select * from EMPLOYEE where Salary between 500000 and 800000<\/p>\n

Get employee details from employee table whose joining year is \u201c2013\u201d<\/strong>
\nSQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date)=’2013′<\/p>\n

Get database date<\/strong>
\nselect now()<\/p>\n

Get department wise average salary from employee table order by salary ascending<\/strong>
\nselect DEPARTMENT,avg(SALARY) AvgSalary from employee group by DEPARTMENT order by AvgSalary asc<\/p>\n

HAVING Clause <\/strong>
\nThe MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.<\/p>\n

Notice that the HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.<\/p>\n

Select department,total salary with respect to a department from employee table where total salary greater than 800000 order by Total_Salary descending<\/strong>
\nSelect DEPARTMENT,sum(SALARY) Total_Salary from employee group by DEPARTMENT having sum(SALARY) >800000 order by Total_Salary desc<\/p>\n

Select employee details from employee table if data exists in incentive table ?<\/strong>
\nselect * from EMPLOYEE where exists (select * from INCENTIVES)<\/p>\n

Since MySQL does not support MINUS operator here is one way to do it <\/strong>
\nInstead of <\/p>\n

SELECT x, y FROM table_a
\nMINUS
\nSELECT x, y FROM table_b;<\/p>\n

use
\nSELECT a.x, a.y
\nFROM table_a a LEFT JOIN table_b b
\nON a.x = b.x AND a.y = b.y
\nWHERE b.x IS NULL;<\/p>\n

Select 20 % of salary from John , 10% of Salary for Roy and for other 15 % of salary from employee table<\/strong>
\nSELECT FIRST_NAME, CASE FIRST_NAME WHEN ‘John’ THEN SALARY * .2 WHEN ‘Roy’ THEN SALARY * .10 ELSE SALARY * .15 END “Deduced_Amount” FROM EMPLOYEE<\/p>\n

Select Last Name from employee table which contain only numbers<\/strong>
\nSelect * from EMPLOYEE where lower(LAST_NAME)=upper(LAST_NAME)<\/p>\n","protected":false},"excerpt":{"rendered":"

http:\/\/a4academics.com\/interview-questions\/53-database-and-sql\/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&limitstart= Get employee details from employee table whose employee name are \u201cJohn\u201d and \u201cRoy\u201d Select * from EMPLOYEE where FIRST_NAME in (‘John’,’Roy’) Get employee details from employee table whose employee name are not \u201cJohn\u201d and \u201cRoy\u201d Select * from EMPLOYEE where FIRST_NAME not in (‘John’,’Roy’) Get employee details from employee table whose first name starts … Continue reading SQL Brushup<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-800","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/800","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=800"}],"version-history":[{"count":5,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/800\/revisions"}],"predecessor-version":[{"id":805,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=\/wp\/v2\/posts\/800\/revisions\/805"}],"wp:attachment":[{"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=800"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.tech.dimprash.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}