Untitled

 avatar
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