SQL expression is a combination of one or more values, operators and SQL functions that results in to a value. These SQL EXPRESSIONs are similar to a formula and they are written in query language. You can also use them to query the database for a specific set of data.<\/p>\n
Consider the basic syntax of the SELECT statement as follows \u2212<\/p>\n
SELECT column1, column2, columnN \nFROM table_name \nWHERE [CONDITION|EXPRESSION];<\/pre>\nSQL\u00a0expression can be classified into following categories.<\/p>\n
\n
- Boolean<\/li>\n
- Numeric<\/li>\n
- Date<\/li>\n<\/ol>\n
SQL Boolean Expression<\/span><\/h3>\n
SQL Boolean Expression fetches data based on the condition that is mentioned as part of the SQL query. It should fetch just single value when the query is executed. Its syntax is given below.<\/p>\n
SELECT<\/span> column<\/span> \nFROM<\/span> table_name \nWHERE<\/span> SINGLE_VALUE_MATCHING_EXPRESSION;\n<\/code><\/pre>\nLet us try to understand Boolean expression using a sample table with some data.<\/p>\n
\n\n
\n EMPID<\/th>\n EMPNAME<\/th>\n EMPAGE<\/th>\n EMPSALARY<\/th>\n<\/tr>\n \n 1<\/td>\n A<\/td>\n 32<\/td>\n 2000<\/td>\n<\/tr>\n \n 2<\/td>\n B<\/td>\n 25<\/td>\n 2500<\/td>\n<\/tr>\n \n 3<\/td>\n C<\/td>\n 29<\/td>\n 3000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n We will consider the Employee table mentioned above as an example.<\/p>\n
\nExample Query:<\/div>\n<\/div>\nSELECT<\/span> EmpName FROM<\/span> Employee WHERE<\/span> EmpId = 1<\/span>;\n<\/code><\/pre>\nThe query above will result in single value which is \u201cA\u201d for our query. SQL Boolean expressions should be used with primary keys to make sure you always get only one results. This is required in case of nested\u00a0SQL select\u00a0queries.<\/p>\n
select<\/span> posts.title, posts.author_id from<\/span> posts, authors<\/span> \nwhere<\/span> posts.author_id = authors.id and<\/span> \nposts.author_id = (select<\/span> id<\/span> from<\/span> authors<\/span> where<\/span> name<\/span> = \\'Pankaj\\'<\/span>);\n<\/code><\/pre>\nAbove query will return all the posts name and id where author name is Pankaj. Here nested SQL query should always return only one row otherwise you will get error message as\u00a0
Subquery returns more than 1 row<\/code>.<\/p>\nSQL Numeric Expression<\/span><\/h3>\n
SQL Numeric Expression is used for performing mathematical operation in SQL query. Its syntax is as follows:<\/p>\n
SELECT<\/span> NUMERICAL_EXPRESSION as<\/span> OPERATION_NAME\nFROM<\/span> table_name\n<\/code><\/pre>\nNUMERICAL_EXPRESSION is the mathematical formula for function which will be used in the SQL query.<\/p>\n
Let\u2019s try to understand Numeric expression using an example.<\/p>\n
SELECT<\/span> count<\/span>(*) FROM<\/span> Employee; -- 3<\/span>\n<\/code><\/pre>\nThe query above will result as 3 because the COUNT(*) function will provide the total count of the rows based on the condition in WHERE clause. For example\u00a0
select count(*) from employee where empsalary > 2400;<\/code>\u00a0will return 2.<\/p>\nThere are other functions also like sum(), avg(), min(), max() etc. These functions are used for mathematical operations. Let\u2019s see some more example code snippets for SQL numeric expressions.<\/p>\n
SELECT<\/span> sum<\/span>(EmpSalary) as<\/span> \\\"Salary\\\"<\/span> FROM<\/span> Employee; -- 7500<\/span>\nselect<\/span> min<\/span>(EmpSalary) from<\/span> Employee; -- 2000<\/span>\nselect<\/span> max<\/span>(EmpSalary) from<\/span> Employee; -- 3000<\/span>\nselect<\/span> sum<\/span>(EmpSalary) from<\/span> Employee; -- 7500<\/span>\nselect<\/span> avg<\/span>(EmpSalary) from<\/span> Employee; -- 2500.0000<\/span>\n<\/code><\/pre>\nSQL Date Expression<\/span><\/h3>\n
SQL Date Expression results in datetime value.<\/p>\n
Let\u2019s try to understand Date expression with some sql statements.<\/p>\n
SELECT<\/span> CURRENT_TIMESTAMP<\/span>; -- 2018-01-20 10:32:37<\/span>\n\n-- For MYQSL<\/span>\nSELECT<\/span> now<\/span>(); -- 2018-01-20 10:32:57<\/span>\n\n-- For SQL Server<\/span>\nSELECT<\/span> GetDate<\/span>();\n\n-- For Oracle DB<\/span>\nselect<\/span> sysdate<\/span> from<\/span> Dual; -- 20-JAN-18<\/span>\n<\/code><\/pre>\nThe above queries will result in current time of the system. Note that every relational database vendor has specific methods to get current date and timestamp values, so there is a slight chance that one function in a database doesn\u2019t work in another database.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL expression is a combination of one or more values, operators and SQL functions that results in to a value. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2],"tags":[],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":0,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"wp:attachment":[{"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/barkhane.com\/sql\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}