12/31/2023 0 Comments Postgres in clauseMySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix etc. SQL is the core of the relational database which is used for accessing and managing database SQL is a standard language which stands for Structured Query Language based on the English language What is the difference between SQL and MySQL? What is the difference between DELETE and TRUNCATE statements?.What is the difference between CHAR and VARCHAR2 datatype in SQL?.What do you mean by table and field in SQL?.What do you mean by DBMS? What are its different types?.What is the difference between SQL and MySQL?.Let’s get started! SQL Interview Questions In this tutorial, you have learned how to use the PostgreSQL CASE expression to form complex queries.Want to Upskill yourself to get ahead in your career? Check out the Top Trending Technologies. The following statement uses CASE expression with the SUM function to calculate the number of films in each rating: SELECT B) Using simple PostgreSQL CASE expression with aggregate function example In this example, we used a simple CASE expression to compare the rating from the film table with some literal values like G, PG, NC17, PG-13 and return the corresponding rating description. WHEN 'PG-13' THEN 'Parents Strongly Cautioned' WHEN 'PG' THEN 'Parental Guidance Suggested' The following statement uses the CASE expression to add the rating description to the output: SELECT title, A) Simple PostgreSQL CASE expression example If CASE does not find any matches, it returns the else_result in that follows the ELSE, or NULL value if the ELSE is not available. Once the result of the expression equals a value (value1, value2, etc.) in a WHEN clause, the CASE returns the corresponding result in the THEN clause. The CASE first evaluates the expression and compares the result with each value( value_1, value_2, …) in the WHEN clauses sequentially until it finds the match. PostgreSQL provides another form of the CASE expression called simple form as follows: CASE expression And we applied the SUM function to calculate the total of films for each price segment. In this example, we used the CASE expression to return 1 or 0 if the rental rate falls into each price segment. In this case, you can use the CASE expression to construct the query as follows: SELECT SUM ( CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 ENDįilm Code language: SQL (Structured Query Language) ( sql ) If the rental rate is 4.99, the film is premium.Īnd you want to know the number of films that belong to economy, mass, and premium.If the rental rate is 1.99, the film is mass.If the rental rate is 0.99, the film is economic.Suppose that you want to assign price segments to films with the following logic: B) Using CASE with an aggregate function example Note that we placed a column alias duration after the CASE expression. ORDER BY title Code language: PHP ( php ) WHEN length> 0 AND length 50 AND length 120 THEN 'Long' To apply this logic, you can use the CASE expression in the SELECT statement as follows: SELECT title, If the length is greater than 120 minutes, the film is long.If the length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |