Написать запрос, который выведет список электронных адресов, у которых правильная доменная зона.
Тестовые данные:
CREATE TABLE "EMAIL"("EMAIL_NAME" VARCHAR2(4000 CHAR)); COMMENT ON TABLE "EMAIL" IS 'Адрес электронной почты'; COMMENT ON COLUMN "EMAIL"."EMAIL_NAME" IS 'Наименование'; CREATE TABLE "DOMAIN"("DOMAIN_NAME" VARCHAR2(4000 CHAR)); COMMENT ON TABLE "DOMAIN" IS 'Домен'; COMMENT ON COLUMN "DOMAIN"."DOMAIN_NAME" IS 'Наименование'; INSERT INTO domain (domain_name) VALUES ('ru'); INSERT INTO domain (domain_name) VALUES ('com'); INSERT INTO domain (domain_name) VALUES ('рф'); INSERT INTO email (email_name) VALUES ('user1@gmail.ru'); INSERT INTO email (email_name) VALUES ('user2@mail.rud778dfuds'); INSERT INTO email (email_name) VALUES ('user3@mail.com7'); INSERT INTO email (email_name) VALUES ('user4@mai4com7'); INSERT INTO email (email_name) VALUES ('user5@yandex.рф'); INSERT INTO email (email_name) VALUES ('user6@gmail.com'); /
Запрос:
SELECT e.email_name AS domain_name FROM email e JOIN domain d ON d.domain_name = regexp_substr(e.email_name, '[^\.]+$'); /
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776
Tags: Oracle