Oracle

How to Divide one Column by Another in MySQL or Oracle

Problem: You want to divide one column by another in MySQL or Oracle. Example: An online store has an orders table with data in the columns order_id, total_order_payment, and item_count. order_idtotal_order_paymentitem_count 124 2154 3562 Let’s say we want to extract the average cost per item for each order, i.e., the total payment for the order divided by the item count.

How to Format a Date in Oracle

Problem: You want to format a date column or value in Oracle. Example: An online store’s database has a table named order with data in the columns order_id, customer_id, and order_time. order_idcustomer_idorder_time BKN234GH748230472022-12-01 01:43:44 F7N3JK84J845638472022-12-02 08:07:12 HSTHJLL2P539403342022-12-15 15:26:02 We want to extract the info for each order, but we want to know only the date of the order, ignoring an exact time.

How to Group by Month in Oracle

Problem: You'd like to group records by month in an Oracle database. Example: Our database has a table named lamps with data in the columns id, color, and production_timestamp. idcolorproduction_timestamp 1Galaxy Blue2022-02-01 11:45:23 2Noir2022-02-01 11:46:13 3Blush2022-01-22 17:22:05 Solution: You can use two EXTRACT() functions to group records in a table by month and year.

How to Get Day Names in Oracle

Problem: You want to extract the name of the day of the week from a date in Oracle. Solution 1: To extract the day name from the date, use the TO_CHAR() function. The first parameter is the date and the second is the format in which we want to output the date. To extract the day name, the format should be 'Day': SELECT TO_CHAR(DATE '2022-01-01', 'Day') FROM DUAL; The result is Saturday.

How to Get the Current Date in Oracle

Problem: You want to get the current date (without the time) in Oracle. Solution 1 (if you don't mind the zeros as the time): SELECT TRUNC(CURRENT_DATE) AS current_date FROM dual; For example, if you were to run this query on June 16, 2021, the result table would look like this: current_date 2021-06-16T00:00:00Z Discussion: Oracle does not have a data type that stores only the date without the time.

How to Split a String in Oracle

Problem: You want to split a string in Oracle. Example: You have a sentence, and you'd like to split it by the space character. Solution: SELECT REGEXP_SUBSTR('An example sentence.', '[^ ]+', 1, level) AS parts FROM dual CONNECT BY REGEXP_SUBSTR('An example sentence.', '[^ ]+', 1, level) IS NOT NULL; The result table looks like this: parts An example sentence.

How to Limit Results in Oracle

Problem: You want to limit the number of rows resulting from a query in Oracle. Example: In the exam table, there are names of the students with the results of the exam. nameexam_result Janet Morgen9 Taya Bain11 Anne Johnson11 Josh Kaur10 Ellen Thornton8 You want to get the three rows with the best exam results.

Kateryna Koidan

SQL Date and Time Functions in 5 Popular SQL Dialects

Are you confused by all the date and time functions used across different SQL dialects? In this article, I summarize the date and time data types used in PostgreSQL, Oracle, SQLite, MySQL, and T-SQL. I also provide examples with the key SQL date and time functions used across these dialects. It’s time to become date and time gurus! Do you want to calculate how often employees are running late for work?

How to Find the Name of a Constraint in Oracle

Problem: You want to find the names of the constraints in a table in Oracle. Example: We want to display the names of the constraints in the table student. Solution: SELECT constraint_name, constraint_type, table_name FROM user_constraints WHERE table_name = 'STUDENT'; Here is the result: constraint_nameconstraint_typetable_name SYS_C007376RSTUDENT SYS_C007374PSTUDENT SYS_C007375USTUDENT SYS_C007372CSTUDENT Discussion: In Oracle, use the view user_constraints to display the names of the constraints in the database.

How to Replace a New Line in Oracle

Problem: You’d like to remove a line break in a column in Oracle database. Example: Our database has a table named address_book with data in the columns city_name and company_address. You’d like to replace each of the tabs and the line breaks in the company_address column with ' ' (a space) to make it more visually appealing. city_namecompany_address Tokyo15th Oreo Street, Tokyo9870-11 Warsaw18 Marszalkowska Ave, Warsaw03-654 Accra123 Banana Junction, Circle-Accra, 00244 Berlin25th Mango Avenue, Asylum Down, DE-1234 Solution: Here’s the query:

How to Order by Month Name in PostgreSQL or Oracle

Problem: You want to sort the rows by month number, given month names (you want January to be shown first, December last). Example: The birthday table contains two columns: name and birthday_month. The months are given in names, not in numbers. namebirthday_month Ronan TishaNULL Angie JuliaApril Narelle DillanApril Purdie CaseyJanuary Donna NellNULL Blaze GraemeOctober You want to sort the rows by birthday_month.

How to Get the Date From a String in Oracle

Problem: You’d like to convert a string to a date in your result set in Oracle database. Example: Our database has a table named accounts_creation, with a field named company_id of the data type NUMBER and two NVARCHAR2 fields named company_name and registration_date as shown below. company_idcompany_nameregistration_date 1Decathlon5 Jan 1978 2Vestel21 Sep 1991 3White Chapel18 Oct 2017 4Silvercup4 Dec 2021 5JohnnyBros28 Aug 1984 We want to convert the string column registration_date into a date column.

How to Order by Date in PostgreSQL or Oracle

Problem: You want to sort the rows by date in PostgreSQL or Oracle database. Example 1: The exam table has two columns, subject and exam_date. subjectexam_date Mathematics2022-12-19 English2023-01-08 Science2023-01-05 Health2023-01-05 ArtNULL You want to sort the rows by exam_date. Solution: SELECT * FROM exam ORDER BY exam_date; The result looks like this (the rows are sorted in ascending order by exam_date):

How to Extract a Substring From a String in Oracle or SQLite

Problem: You have a column of strings, and you'd like to get substrings from them. Example 1: In the emails table, there is an email column. You'd like to display the first seven characters of each email. The table looks like this: email jake99@gmail.com tamarablack@zoho.com notine@yahoo.fr jessica1995@onet.pl Solution 1: SELECT email, SUBSTR(email, 1, 7) AS substring FROM emails; The result is:

How to Find the Difference Between Two Timestamps in Oracle

Problem: You have two columns of the type timestamp and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution: SELECT id, departure, arrival, arrival - departure AS difference FROM travel; The result is:

How to Calculate Date Difference in PostgreSQL/Oracle

Problem: You have two columns of the date type and you want to calculate the difference between them in PostgreSQL or Oracle database. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure and the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution: SELECT id, departure, arrival, arrival - departure AS date_difference, arrival - departure + 1 AS days FROM travel; The result is:

How to Get a Remainder Using MOD() in PostgreSQL, MS SQL Server, and MySQL

Problem: You want to find the (non-negative) remainder. Example: In the table numbers, you have two columns of integers: a and b. ab 93 53 23 03 -23 -53 -93 5-3 -5-3 50 00 You want to compute the remainders from dividing a by b.

How to Get Yesterday’s Date in Oracle

Problem: You would like to display yesterday's date (without time) in an Oracle database. Solution 1: SELECT TO_DATE(CURRENT_DATE - 1) AS yesterday_date FROM dual; Assuming today is 2020-09-24, the result is: yesterday_date 2020-09-23 Discussion: To get yesterday's date, you need to subtract one day from today. Use CURRENT_DATE to get today's date. In Oracle, you can subtract any number of days simply by subtracting that number from the current date.

Aldo Zelen

Oracle Top-N Queries for Absolute Beginners

It's common to run a query using only part of a dataset – for example, the top 100 salespeople in a company. In this article, we'll see how to use Oracle's Top-N query method to query just these rows. Top-N queries retrieve a defined number of rows (top or bottom) from a result set. In other words, they find the best or worst of something – the ten best selling cars in a certain region, the five most popular routers, the 20 worst-performing stores, etc.

Patrycja Dybka

Oracle Collations: Binary and Linguistic Collations

Oracle bases its language support on the values of parameters that begin with NLS. These parameters specify, for example, how to display currency or how the name of a day is spelled. The table below presents some of the NLS parameters. By using one of them, NLS_SORT, we can specify the sort method (binary or linguistic) for both SQL WHERE clause operations and NLSSORT function operations. Option Name Description NLS_LANG The current language, territory, and database character set, which are determined by session-wide globalization parameters.

Patrycja Dybka

The Most Useful Date and Time Functions

Date and time functions and formats are quite different in various databases. In this article, let's review the most common functions that manipulates dates in an Oracle database. The function SYSDATE() returns a 7 byte binary data element whose bytes represents: century, year, month, day, hour, minute, second It's important to know that select sysdate from dual in SQL*Plus gives the same result as select to_char(sysdate) from dual because SQL*Plus binds everything into character strings so it can print it.

Agnieszka Kozubek-Krycuń

MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names. if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} parent_name child_name John Tom Michael Sylvie John Anna Michael Sophie To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows: