Technology Now

Hello to everyone;

Here I wan to share very technical information and examples of Oracle:

–select * from customers;
–select rowid,rownum, customer_id,FIRST_NAME from customers;
–select 2*6 from dual;
–select to_date(’25-jul-2007′) +2 from dual;
–select 1 from dual;
–select name,price * 0.5 +1 from products;
–select name,price * 3 + 1/2 from products;
–select price*2 double_price from products;
–select name product_name,price product_price from products;
–select dob nacimiento from customers;
–select ‘first name’ from dual;
–select first_name || ‘ ‘ || last_name as “customer name” from customers;
–select first_name || ‘ the best’ as “the best” from customers;
—————select ltrim(rowid,’AAASr2AAEAAAAJf’),rownum, name from products;
–select * from customers where dob is null;
–select * from customers where phone is null;
–select first_name, last_name, nvl(phone,’unkonm’) as phone_number from customers;
–select customer_id,first_name,last_name, nvl(dob,’01-jan-2000′) as dob from customers;
–select distinct customer_id from purchases;
——select TRIM(LEADING ‘AAASr2AAEAAAAJf’ FROM rowid),rownum, name from products;
–select distinct customer_id from customers;
–select * from customers where customer_id 2;
–select product_id,name from products where product_id >=8;
–select rowid,rownum,first_name from customers;
–select rowid,rownum,name from products;
–select rownum, product_id,name from products where rownum >=3; –it is not useful

–select * from customers where customer_id > any (2,3,4);
–select * from customers where customer_id > all (2,3,4);
–select * from customers where first_name all (‘Doreen’,’Fred’);
–select * from CUSTOMERS where FIRST_NAME like ‘__r%’;
–select * from CUSTOMERS where FIRST_NAME like ‘_t%’;
–select * from PRODUCTS where PRODUCT_ID like ‘1%’
–select * from PRODUCTS where PRODUCT_ID not like ‘1%’
–select name from PROMOTIONS where NAME like ‘1%\%%’ ESCAPE ‘\’;
——————select name from PROMOTIONS where NAME like “__’%’%”;
–select products.name,PRODUCTS.PRICE from PRODUCTS,PRODUCT_TYPES where PRODUCTS.PRODUCT_ID = PRODUCT_TYPES.PRODUCT_TYPE_ID and PRODUCT_TYPES.NAME =’Book’;
–select p.name, pt.name from products p, PRODUCT_TYPES pt where p.PRODUCT_TYPE_ID = pt.PRODUCT_TYPE_ID;
—-delete from EMPLOYEES where rowid not in (select min(rowid) from EMPLOYEES group by ssn,name);

–select c.first_name, c.last_name, p.name as product, pt.name as type
–from customers c, PURCHASES pr, products p, products_types pt
–where c.customer_id = pr.customer_id
–and p.product_id = pr.product_id
–and p.product_type_id = pt.product_type_id
–order by p.name;

–select *from salary_grades;

–select e.first_name, e.last_name, e.salary, sg.salary_grade_id
–from employees e, salary_grades sg
–where e.salary between sg.low_salary and sg.high_salary
–order by salary_grade_id;

–SELECT * FROM products;

–select p.name, pt.NAME
–from products p, product_types pt
–where p.product_type_id = pt.product_type_id (+)
—where p.product_type_id = pt.product_type_id
–order by p.name;

–select p.name, pt.NAME
–from products p, product_types pt
–where p.product_type_id = pt.product_type_id (+)
–and pt.PRODUCT_TYPE_ID is null
–order by p.name;

–select p.name, pt.NAME
–from products p, product_types pt
–where p.product_type_id (+) = pt.product_type_id
–and p.PRODUCT_TYPE_ID is null
–order by p.name;

–select p.name, pt.NAME
–from products p, product_types pt
–where p.product_type_id = pt.product_type_id (+)
–order by p.name;

–describe employees;
–select * from employees;

–select w.FIRST_NAME ||’ ‘|| w.LAST_NAME || ‘ works for ‘ || NVL(m.FIRST_NAME,’Himself’) || ‘ ‘ || m.LAST_NAME
–from employees w, employees m
–where w.manager_id = m.employee_id (+)
–order by m.FIRST_NAME;

–select p.name, pt.name
–from products p, PRODUCT_TYPES pt
–where p.PRODUCT_TYPE_ID = pt.PRODUCT_TYPE_ID
–order by p.name

–select p.name, pt.name
–from products p inner join PRODUCT_TYPES pt
–on p.PRODUCT_TYPE_ID = pt.PRODUCT_TYPE_ID
–order by p.name

–select e.FIRST_NAME,e.LAST_NAME,e.TITLE,e.salary,sg.SALARY_GRADE_ID
–from employees e, salary_grades sg
–where e.salary between sg.low_salary and sg.high_salary
–order by sg.salary_grade_id

–select e.FIRST_NAME,e.LAST_NAME,e.TITLE,e.salary,sg.SALARY_GRADE_ID
–from employees e inner join salary_grades sg
–on e.salary between sg.low_salary and sg.high_salary
–order by sg.salary_grade_id

–select p.name, pt.name
–from products p inner join PRODUCT_TYPES pt
–using (PRODUCT_TYPE_ID)
–order by p.name

–from customers c, purchases pr, products p, products_types pt
–where c.customers_id = pr.customers_id
–and p.product_id =

–select * from PRODUCT_TYPES cross join PRODUCTS;

–select p.name, pt.name
–from products p left outer JOIN PRODUCT_TYPES pt
–using (PRODUCT_TYPE_ID)
–order by p.name

–select p.name, pt.name
–from products p right outer JOIN PRODUCT_TYPES pt
–using (PRODUCT_TYPE_ID)
–order by p.name

–select p.name, pt.name
–from products p full outer JOIN PRODUCT_TYPES pt
–using (PRODUCT_TYPE_ID)
–order by p.name

–select w.last_name || ‘ works for ‘ || m.last_name
–from employees w inner join EMPLOYEES m
–on m.MANAGER_ID = w.employee_id;

–select * from product_types cross join products;

–set define ‘$’ –define how to define variables ‘&’ is the default
–select * from products where product_id = $myv_products;

–set define ‘&’
–select name, &&v_col from &v_table where &&v_col = &v_val;

–define v_product_id = 7
–exec scripta.sql
–exec scriptb.sql

–define v_products_id = 7;
——define v_product_id = &v_
–define v_products_id;

–define; –all variables

–define bob = &joe;
–define bob

–accept v_customer_id number format 99 prompt ‘Customer ID: (Ex:99)’
–accept v_date date format ‘DD–MM–YYYY’ prompt ‘Date: (Ex:DD-MM-YYYY)’
–accept v_password char prompt ‘Password: ‘ Hide;

–define v_date;

–select to_date(‘126,07′,’ddd.yyyy’) from dual;

–select customer_id, to_char(dob,’DD-MON-YYYY HH24:MI:SS’) from customers;

–insert into customers (customer_id, first_name,last_name,dob)
–values (7,’steve’,’purple’,to_date(’05-feb-1987 19:32:36′,’DD-MON-YYYY HH24:MI:SS’));
–COMMIT
–select customer_id,first_name,to_char(dob,’DD-MON-YYYY HH24:MI:SS’) from customers;

–select to_char(to_date(’04-JUL-85′,’DD-MON-YY’),’DD-MON-YYYY’) from dual;

–select add_months(’01-JAN-2007′,13) from dual;

–alter session set NLS_DATE_FORMAT = ‘MON-DD-YYYY’;
–select add_months(‘JAN-01-2007’,13) from dual;
–alter session set NLS_DATE_FORMAT = ‘DD-MON-YYYY’;

—–select MONTHS_BETWEEN
—–       (TO_DATE(’02-02-1995′,’MM-DD-YYYY’),
—        TO_DATE(’03-08-1995′,’MM-DD-YYYY’) ) from dual;

–select next_day(’05-OCT-2015′,’SATURDAY’) from dual;

–select round(to_date(’25-OCT-2008′),’YYYY’) from dual;
–select round(to_date(’25-FEB-2008′),’YYYY’) from dual;

–select dbtimezone from dual;
–select sessiontimezone from dual;
–select tz_offset(‘EST’) from dual;

–select TO_CHAR(new_time(to_date(’25-MAY-2015 16:40′,’DD-MON-YYYY HH24:MI’), ‘PST’,’EST’),’DD-MON-YYYY HH24:MI’) from dual;

——select * from PURCHASES_TIMESTAMP_WITH_TZ;

–select current_timestamp from dual;

–alter session set time_zone = ‘EST’;

–select
–EXTRACT (year from to_date(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as year,–
–EXTRACT (month from to_date(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as month,
–EXTRACT (day from to_date(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as day-,
–EXTRACT (hour from to_timestamp(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as hour,
–EXTRACT (minute from to_timestamp(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as minute,
–EXTRACT (second from to_timestamp(’01-JAN-2008 19:20:20′,’DD-MON-YYYY HH24:MI:SS’)) as second
–from dual;

–select
–EXTRACT (timezone_hour from to_timestamp_tz(’01-JAN-2008 19:20:20 -7:15′,’DD-MON-YYYY HH24:MI:SS TZH:TZM’)) as TZH,
–EXTRACT (timezone_minute from to_timestamp_tz(’01-JAN-2008 19:20:20 -7:15′,’DD-MON-YYYY HH24:MI:SS TZH:TZM’)) as TZM,
–EXTRACT (timezone_region from to_timestamp_tz(’01-JAN-2008 19:20:20 -PST’,’DD-MON-YYYY HH24:MI:SS TZR’)) as REGION
–from dual;

–select
–from_tz(timestamp,time_zone(‘2008-05-13 07:07:07.1234′,’-7:00′)=timestamp with tz
–from_tz(timestamp,time_zone(‘2008-05-13 07:07:07.1234′,’-7:00′))
–SYS_EXTRACT_UTC(TIMESTAMP ‘2000-03-28 11:30:00.00 -08:00’)
–FROM_TZ(TIMESTAMP ‘2000-03-28 08:00:00’, ‘3:00′)

–from dual;

–select * from coupons;

–insert into coupons (COUPON_ID,NAME,duration)
–VALUES(7,’$1 off Z Files’,interval ‘456’ YEAR(3));
–COMMIT

–functions

–select concat(first_name,last_name) from customers;
–select initcap(description) from products;
–select name, length(name) from products;
–select rpad(name,30,’.’),lpad(price,10,’$’) from products; –filling the space
–select ltrim(‘     Hello Gail Seymour!’) from dual;
–select rtrim(‘Hello Gail Seymour!!!!!!!!’,’!’) from dual;
–select trim(‘*’ from ‘*****Hello Gail Seymour*******’) from dual;
–select customer_id, nvl(phone, ‘Unknown Number’) from customers;
–select customer_id, nvl2(phone, ‘Yes’,’No’) as PHONE from customers;
–select replace(name,’Science’,’physics’) from products;
–select last_name from customers where soundex(last_name) = soundex(‘white’);

–select substr(name,1,5) from products;
–select substr(‘mary had a little lamb’,1,5) from dual;

–select abs(-10) from dual;

–select ceil(5.2), ceil(abs(-5.2)) from dual;
–select floor(5.2), floor(abs(-5.2)) from dual;

–select mod(8,4) from dual;

–select power(2,1),power(2,3) from dual;

–select round(5.75),round(5.75,1),round(5.75,-1) from dual;

–select sign(-5),sign(5),sign(0) from dual;
–select trunc(5.75), trunc(5.75,1), trunc(5.75,-1) from dual;

–select substr(rowid,16,3),name from products;

–select TO_CHAR(2007,’RN’) from dual;
———select TO_CHAR(2007,’X’) from dual;

–select to_number(‘970.13′) + 25.5 from dual;

–select cast(price as varchar2(10)) from products where PRODUCT_ID = 1;
–select cast(price + 2 as number(7,2)) from products where PRODUCT_ID = 1;

–1965
–1968
–^196[5-8]$

–select customer_id,first_name,last_name,dob from customers where regexp_like(to_char(dob,’YYYY’),’^196[5-8]$’);

–select CUSTOMER_ID,FIRST_NAME,LAST_NAME,dob from customers where regexp_like(FIRST_NAME,’^j’,’i’);

–select regexp_instr(‘But, soft! What light through yoder window breaks?’,’l[[:alpha:]]{4}’) from dual;
–select regexp_replace(‘But, soft! What light through yoder window breaks?’,'[Ll][[:alpha:]]{4}’,’sound’) from dual;
–select regexp_count(‘But, soft! What light through yoder window breaks?’,'[Ll][[:alpha:]]{4}’) from dual;

–select distinct AVG(price) from products;
–select AVG(distinct price) from products where PRODUCT_TYPE_ID;
–select avg(price + 2) from products;
–select count(DISTINCT product_type_id) from products;

–select max(dob), min(dob) from customers;
–select max(name), min(name) from products;

–select sum(quantity)from purchases;

–select variance(price) from products;

–select PRODUCT_TYPE_ID, avg(price) from products group by PRODUCT_TYPE_ID;

–select CUSTOMER_ID from purchases group by CUSTOMER_ID order by CUSTOMER_ID;

–select count(prd_type_id) from all_sales group by PRD_TYPE_ID;

–select product_id,product_type_id,name, price
–from products outer
–where price >
–(select avg(price) from products inner
–where inner.product_type_id = outer.product_type_id);

–select product_id,product_type_id,name, price
–from products a
–where price >
–(select avg(price) from products b
–where b.product_type_id = a.product_type_id);

–select product_id,product_type_id,name, price
–from products a
–where price =
–(select max(price) from products b
–where b.product_type_id = a.product_type_id);

–select employee_id,last_name from employees outer
–where EXISTS
–(select employee_id from employees inner
–where inner.manager_id = outer.employee_id);

–select * from employees;

–select employee_id,last_name from employees outer
–where EXISTS
–(select 1 from employees inner
–where inner.manager_id = outer.employee_id);

–select employee_id,last_name from employees outer
–where not EXISTS
–(select 1 from employees inner
–where inner.manager_id = outer.employee_id);

–select product_id,name from products outer
–where not EXISTS
–(select 1 from purchases inner where inner.product_id = outer.product_id);

–select year, avg(amount) from All_sales group by year

–select avg(amount)
–from all_sales
–group by month
–where  > 100;
–select sum(AMOUNT) as mysum
–from ALL_SALES lola
–group by MONTH
–order by MONTH;

–(select avg(outer.mysum) as myavg from all_sales inner group by year);

–select avg(amount) from all_sales group by year;

–select month,sum(amount)
–from all_sales outer
–where outer.MONTH >
–(select avg(amount) from all_sales inner)
—-group by month)
—–where outer.month = inner.month)
–group by month;

–select avg(amount) from all_sales inner;

–select product_type_id, name
–from product_types outer
–where not exists
–(select 1 from products inner where
–inner.product_type_id=outer.product_type_id
–)

–select product_type_id, name
–from product_types outer
–where product_type_id not in
–(select product_type_id from products inner);

–select product_type_id, name
–from product_types outer
–where product_type_id not in
–(select nvl(product_type_id,0) from products inner);

select year, to_char(sum(amount),’$9,999,999.00′) as YEAR_SALES, first_name, employee_id from
(
SELECT ALL_SALES.YEAR,
ALL_SALES.MONTH,
ALL_SALES.AMOUNT,
PRODUCT_TYPES.NAME,
EMPLOYEES2.FIRST_NAME,
EMPLOYEES2.LAST_NAME,
EMPLOYEES2.EMPLOYEE_ID
FROM ALL_SALES
INNER JOIN PIVOT_SALES_DATA
ON ALL_SALES.PRD_TYPE_ID = PIVOT_SALES_DATA.PRD_TYPE_ID
INNER JOIN PRODUCT_TYPES
ON PRODUCT_TYPES.PRODUCT_TYPE_ID = ALL_SALES.PRD_TYPE_ID
INNER JOIN EMPLOYEES2
ON EMPLOYEES2.EMPLOYEE_ID = ALL_SALES.EMP_ID
where EMPLOYEES2.FIRST_NAME like ‘&fnd’
order by ALL_SALES.MONTH desc
)
group by year, employee_id, first_name
order by sum(amount) desc;

SELECT CUSTOMERS.FIRST_NAME,
CUSTOMERS.LAST_NAME,
CUSTOMERS.DOB,
CUSTOMERS.PHONE,
ALL_SALES.YEAR,
ALL_SALES.MONTH,
ALL_SALES.AMOUNT,
PURCHASES.QUANTITY,
PRODUCTS.NAME,
PRODUCTS.DESCRIPTION,
PRODUCTS.PRICE,
PRODUCT_TYPES.NAME AS NAME1,
MORE_PRODUCTS.NAME AS NAME2,
MORE_PRODUCTS.AVAILABLE
FROM CUSTOMERS
INNER JOIN PURCHASES
ON CUSTOMERS.CUSTOMER_ID = PURCHASES.CUSTOMER_ID
INNER JOIN PRODUCTS
ON PRODUCTS.PRODUCT_ID = PURCHASES.PRODUCT_ID
INNER JOIN PRODUCT_TYPES
ON PRODUCT_TYPES.PRODUCT_TYPE_ID = PRODUCTS.PRODUCT_TYPE_ID
INNER JOIN ALL_SALES
ON PRODUCT_TYPES.PRODUCT_TYPE_ID = ALL_SALES.PRD_TYPE_ID
INNER JOIN MORE_PRODUCTS
ON PRODUCT_TYPES.PRODUCT_TYPE_ID = MORE_PRODUCTS.PRD_TYPE_ID