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