Untitled
unknown
plain_text
4 months ago
13 kB
3
Indexable
Select * from students ;
select * from employees;
select * from grades;
select *from locations
DESC students
select name , surname from students;
select count(*) from students
select last_name , first_name from employees
SELECT DISTINCT (name) from students
Select * from departments
select location_id from departments
Select name ||' '|| surname data from students/*1 way one word*/
Select name ||' '|| surname as data from students--2 way one word
Select name ||' '|| surname as "Personel Data" from students--3way multi word
select 'Employee: ' || UPPER(last_name) as who ,'Earns: '||salary||'$' as "how much" from employees
SELECT NAME , surname , birth_date as birthday from students
ORDER BY birth_date DESC;
SELECT first_name , last_name , hire_date as "employment date"
from employees
order by last_name , 2 desc , "employment date";
select name ,surname , year , dean_gr from students where year=2 and dean_gr in(1,2);--1 way
select name , surname , year , dean_gr from students where year= 2 and (dean_gr=1 or dean_gr=2) --2 way
Select name ,surname , year ,dean_gr from students where year=2 and dean_gr BETWEEN 1 AND 2
------------------------------------------------------------------------------------------------------------
Select first_name , last_name from employees where department_id between 10 AND 80 AND manager_id = 103;
select * from employees where department_id in(50,30,80) and job_id like '%MAN';
select name , surname from students where name like 'MARIAN%' OR name like 'Marian%' or name LIKE 'marian%' ;
select name , surname from students where surname like 'Kowalsk_' or surname like 'Nowa_';
select surname , name from students where name IN ('Adam' , 'Anna' , 'Magdalena' , 'Konrad') order by 2 ;
select name , surname from students where name like '%a' and name not like 'B%' order by 1,2 ;
select name , surname from students where name not like '%a' and surname like '% %' order by 2 desc , 1 desc;
select name , surname from students where surname like '%-%' ;
select * from students where (surname like '%k' or surname like '%z') and (name like '_a%' or name like '_o%');
select upper(first_name) , upper(last_name) , Lower(job_id) from employees;
-----------------------------------------------------------------------------------------------------------------
Select last_name surname , job_id job , salary+NVL(commission_pct ,0)*salary ||'PLN' salary_commission ,
ABS((salary+NVL(commission_pct , 0)*salary)-8408.17)||'+' deviation From employees
Where salary+NVL(commission_pct , 0)*salary>8408.17
ORDER BY salary+NVL(commission_pct , 0)*salary DESC;
select
SQRT (POWER(11.43,4) + POWER(2399 + SQRT(1234.56) , 1/4) + log(7 , log(10,12345))) result ,
ROUND (SQRT(POWER (11.43,4) + POWER(2399+SQRT(1234.56),1/4) + log(7,log(10,12345)))) AS round,
TRUNC (SQRT(POWER (11.43,4) + POWER(2399+SQRT(1234.56),1/4) + log(7,log(10,12345)))) AS trunc,
CEIL (SQRT(POWER (11.43,4) + POWER(2399+SQRT(1234.56),1/4) + log(7,log(10,12345)))) AS ceil,
FLOOR (SQRT(POWER (11.43,4) + POWER(2399+SQRT(1234.56),1/4) + log(7,log(10,12345)))) AS floor
from dual;
SELECT * FROM NLS_SESSION_PARAMETERS;
select Current_date , Sysdate , Current_timestamp , Systimestamp from Dual;
select Current_date , Sysdate , Current_timestamp , Systimestamp at time zone 'Poland' from dual;
ALTER SESSION SET NLS_LANGUAGE = english;
ALTER SESSION SET NLS_TERRITORY = "POLAND"; --"UNITED KINGDOM" "AMERICA";
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.ff';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.ff';
SELECT INTERVAL '101-11' YEAR(3) TO MONTH as "INTERVAL A 101 years 11 months",
INTERVAL '25 3:05:36.6' DAY TO SECOND as "INTERVAL B 25 days 3h 5m 36.6s",
TIMESTAMP '101-11-25 03:05:36.6' AS "together A+B" FROM DUAL;
select sysdate+4321 "in 4321 days" , current_date-4321 "4321 days ago" ,
trunc (((sysdate+4321)-(sysdate-4321))/7) weeks,
trunc (months_between(sysdate+4321 , sysdate-4321)) months from dual;
Select Systimestamp "Current time",
Systimestamp-interval '567 8:9' DAY(3) TO MINUTE as "Past",
Systimestamp-interval '129-11' YEAR(3) TO MONTH as "future full fmt",
to char (Systimestamp +INTERVAL '129-11' year(3) to month , 'yyyy-mm-dd hh24:mi:ss') as "future of fmt to seconds" from dual;
---------------------------------------------------------------------------------------------------------------------------------------------------
SELECT TIMESTAMP '2018-12-11 19:07:00' - TIMESTAMP '2015-02-15 22:04:19' AS "differemce in days " from dual
SELECT TIMESTAMP '2018-12-11 19:07:00' - TIMESTAMP '2015-02-15 22:04:19' AS "differemce in days " from dual
SELECT systimestamp,
EXTRACT(YEAR FROM sysdate) year,
EXTRACT(MONTH FROM sysdate) month
FROM dual
SELECT * FROM students
WHERE EXTRACT(Month from birth_date) = extract (month from sysdate) and
extract (day from birth_date) = extract (day from sysdate)
SELECT Add_months (Sysdate , 134) "date after 134 months",
to_char (Add_months(Sysdate, 134), 'ww') "no of week in this year"
from dual;
SELECT to_char(Last_day(Sysdate) , 'YY/MM/DD') AS "Last day of this month",
to_char(Last_day(Sysdate) , 'DAY') AS "Day of week"
from dual;
SELECT to_char(Sysdate , 'yyyy-mm-dd') "Current date" ,
ROUND (Sysdate , 'mm') "ROUND to Months" ,
TRUNC (Sysdate , 'yy') "TRUNC to years" ,
to_char(ROUND(Sysdate , 'cc') , 'yyyy-mm-dd cc') "ROUND to Centuries"
from dual
SELECT surname , name , birth_date ,
TRUNC(months_between(sysdate , birth_date)/12) "Full Years" ,
EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM birth_date) "According to birth year"
FROM students
WHERE name like 'M%'
ORDER BY 4 DESC , 1;
SELECT Systimestamp as "Current time ct" ,
CAST (Systimestamp as date ) as "ct converted to date"
from dual
SELECT '777'||'444' STRING,
CAST( '777'||'444' AS NUMBER) "NUMBER" ,
CAST('777' || '444' AS NUMBER )-555222 "DIFFERENCE "
FROM dual
SELECT '777444' - 555222 DIFFERENCE FROM dual
SELECT to_char(to_char(Sysdate , 'CC') , 'RN') century from dual
SELECT 'HELLO , TODAY IS' || to_char (Sysdate , ' day , dd month') || to_char(Sysdate , 'FMyyyy')||' year!' AS "DATA"
FROM dual
SELECT to_char(to_date('12-09-1683' , 'dd-mm-yyyy') , 'cc') || ' century, ' ||
to_char(to_date('12-09-1683' , 'dd-mm-yyyy') , 'q') || ' quarter, ' ||
to_char(to_date('12-09-1683' , 'dd-mm-yyyy') , 'month') || ', ' ||
to_char(to_date('12-09-1683' , 'dd-mm-yyyy') , 'w') || 'week ' ||
to_char(to_date('12-09-1683' , 'dd-mm-yyyy') , 'day') "details of date 12,09,1683"
from dual
SELECT last_name , job_id , hire_date,
TRUNC (Months_between(Sysdate , hire_date)/12) || 'years' ||
mod(TRUNC(Months_between(Sysdate , hire_date)) , 12) || 'months' as "he/she has been working for"
from employees
order by 3 , 1
------------------------------------------------------------------------------------------------------------------
Select * from employees
Select last_name ,
job_id ,
salary ,
Nvl(commission_pct , 0) com_pct
from employees;
Select last_name ,
job_id ,
salary ,
Decode (commission_pct , NULL , 0 , commission_pct) com_pct
FROM employees
Select last_name ,
job_id ,
salary ,
CASE
WHEN commission_pct IS NOT NULL
THEN commission_pct
ELSE 0
END AS IS_NOT_NULL
FROM employees
SELECT department_id,
DECODE(department_id, 10, 'ten', 20, 'twenty', 30, 'thirty',' - ') f_decode,
salary,
CASE
WHEN salary > 8000 THEN 'greater than 8000'
WHEN salary > 6000 THEN ' greater than 6000'
ELSE ' - ' END f_case,
job_id,
CASE
WHEN job_id IN ('MK_MAN','MK_REP') AND department_id=20 THEN '1'
WHEN job_id IN ('AD_PRES') THEN '2'
ELSE ' - ' END f_case2
FROM employees
order by department_id;
SELECT UID,USER from DUAL; -- it is not important
SELECT
count(*) count,
sum(salary) sum,
round(avg(salary),2) average,
max(salary) greatest,
min(salary) smallest,
count(salary) "quantity of rows SALARY",
count(commission_pct)"quantity of COMMISSION",
count(nvl(commission_pct,0))"quantity of COMMISSION with NULL",
round(avg(commission_pct),2) "average COMMISSION",
round(avg(nvl(commission_pct,0)),2) "avg COMMISSION without NULL",
count(DISTINCT job_id)"distinct jobs",
min(commission_pct)
FROM employees;
Select Count(*) from employees
Select count(employee_id) from employees
Select COUNT(DISTINCT first_name) from employees
SELECT department_id, count(department_id) quantity
from employees
GROUP BY department_id
ORDER BY department_id
SELECT department_id, count(*) quantity from employees
GROUP BY department_id
HAVING department_id > 90 --condition II gr
ORDER BY department_id;
SELECT department_id, count(*) quantity
from employees
WHERE department_id > 90 --condition II wier
GROUP BY department_id
order by department_id;
Select department_id ,
job_id ,
count(*)
from employees
group by department_id , job_id
order by department_id
select department_id , count(employee_id) from employees
where extract (month from hire_date) = 6
group by department_id
select last_name , hire_date , extract (month from hire_date)
from employees
where department_id = 50
and extract (month from hire_date) =6
select to_char (hire_date , 'D') , to_char(hire_date , 'Day') from employees
select to_char(hire_date , 'D') "day of week" , Count(employee_id) from employees
group by to_char(hire_date , 'D')
ORDER BY 2
SELECT DEPARTMENT_ID , MAX (LENGTH(last_name))
from employees
group by department_id
order by 1
--------------------------------------------------------------------------------------------------------------------------
SELECT field , year , count(*) from students
GROUP BY (field , year)
ORDER BY 1,2
SELECT field , year , count(*) from students
GROUP BY ROLLUP(field , year)
ORDER BY 1,2
SELECT field , year , count(*) from students
GROUP BY CUBE(field , year)
ORDER BY 1,2
SELECT field , year , count(*) from students
GROUP BY GROUPING SETS((field , year),())
ORDER BY 1,2
Select department_id , job_id , COUNT(employee_id) from employees
GROUP BY Rollup (department_id , job_id)
order by 1
Select department_id , job_id , COUNT(employee_id) from employees
GROUP BY CUBE (department_id , job_id)
order by 1
Select department_id , job_id , COUNT(employee_id) from employees
GROUP BY GROUPING SETS ((department_id , job_id),())
order by 1
--Task6
SELECT name From students WHERE name like 'Ad%'
UNION
SELECT first_name FROM employees WHERE first_name like 'Ad%' --number of column and type of column should be same
SELECT DISTINCT name FROM students where name like 'Ad%'
SELECT count(name), name
FROM students
WHERE name like 'Ad%'
GROUP BY name;
SELECT name FROM students WHERE name like 'Ad%'
UNION ALL
SELECT first_name FROM employees WHERE first_name like 'Ad%';
SELECT name FROM students WHERE name like 'Ad%'
INTERSECT
SELECT first_name FROM employees WHERE first_name like 'Ad%';
SELECT name FROM students WHERE name like 'Ad%'
MINUS
SELECT first_name FROM employees WHERE first_name like 'Ad%';
SELECT first_name FROM employees WHERE first_name like 'Ad%'
MINUS
SELECT name FROM students WHERE name like 'Ad%';
SELECT name, first_name
FROM students
CROSS JOIN employees WHERE name like 'Ad%' AND first_name like 'Ad%';
SELECT name , surname , 0 AS "commission" , field "field/hire_date "
FROM students WHERE average_grade > 4.95
UNION
SELECT first_name , last_name , commission_pct to char(hire_date)
FROM employees WHERE commission_pct is not null order by 3
SELECT * FROM employees order by hire_date
SELECT MAX(hire_date) FROM employees order by hire_date
select last_name , department_id , job_id , salary ,
to_char (hire_date , 'dd-mm-yyyy') "hire_date" 'young employee' comments
from employees
where hire_date>= to_date ('2010-01-13' , 'yyyy-mm-dd') - interval '4' year
and department_id =60
union
SELECT last_name , department_id , job_id , salary,
to_char (hire_date , 'dd-mm-yyyy') "hire_date" 'experinced employee' comments
from employees
where hire_date >= to_date ('2010-01-13' , 'yyyy-mm-dd') - interval '9' year
where hire_date < to_date ('2010-01-13' , 'yyyy-mm-dd') - interval '4' year
and department_id=60
Editor is loading...
Leave a Comment