zadanka z 2-1

zad 1
The manager of Global Fast Foods would like to send out coupons for the upcoming sale.
He wants to send one coupon to each household. Create the SELECT statement that returns the customer last name and a mailing address.

select distinct last_name, address from f_customers


zad 3
Sue, Bob, and Monique were the employees of the month. Using the f_staffs table, create a SELECT statement to display the results as shown in the Super Star chart.

select ' *** '||first_name||' *** '||first_name||' *** ' "SUPER STAR" from f_staffs


zad 5
Global Fast Foods has decided to give all staff members a 5% raise. Prepare a report that presents the output as shown in the chart.

select last_name "EMPLOYEE LAST NAME", salary "CURRENT SALARY", salary*1.05 "SALARY WITH 5% RAISE" from f_staffs


zad6
Create a query that will return the structure of the Oracle database EMPLOYEES  table. Which columns are marked “nullable”? What does this mean?

describe employees

zad7
The owners of DJs on Demand would like a report of all items in their D_CDs table with the following
column headings: Inventory Item, CD Title, Music Producer, and Year Purchased. Prepare this report

select * from d_cds    - bez aliasów




zadanka z 2-2

zad1
Using the Global Fast Foods database, retrieve the customer’s first name, last name, and address for the customer who uses ID 456.

select first_name, last_name, address from f_customers where id=456

zad2
Show the name, start date, and end date for Global Fast Foods' promotional item “ballpen and highlighter” giveaway.

select name, start_date, end_date from f_promotional_menus where give_away='ballpen and highlighter'

zad5
The manager of DJs on Demand would like a report of all the CD titles and years of CDs that were produced before 2000.

select title, year from d_cds where year < 2000

zad7
Write a SQL statement that will display the student number (studentno),
first name (fname), and last name (lname) for all students who are female (F) in the table named students.
kolumna znajduje sie w poleceniu

select studentno, fname, lname from students where sex = 'F'

zad10
Write a SQL statement that will list the titles and years of all the DJs on Demand CDs that were not produced in 2000.
select title, year from d_cds where year != 2000

zad11
Write a SQL statement that lists the Global Fast Foods employees who were born before 1980.

select * from f_staffs where birthdate<'01-Jan-1980'



zadanka 2-3

zad1
Display the first name, last name, and salary of all Global Fast Foods staff whose salary is between $5.00 and $10.00 per hour.

select first_name, last_name, salary from f_staffs where salary between 5 and 10


zad4
Create a list of all the DJs on Demand CD titles that have “a” as the second letter in the title.

select title from d_cds where title like '_a%'

zad5
Who are the partners of DJs on Demand who do not get an authorized expense amount?

select * from d_partners where auth_expense_amt is null

zad6
Select all the Oracle database employees whose last names end with “s”. Change the heading of the column to read Possible Candidates.

select last_name "Possible Candidates" from employees where last_name like '%s'

zad8
Write a SQL statement that lists the songs in the DJs on Demand inventory that are type code 77, 12, or 1.

select * from d_songs where type_code in (77,12,1)




zadanka 3-1

zad2
Display the last names of all Global Fast Foods employees who have “e” and “i” in their last names

select last_name from f_staffs where last_name like '%e%' and last_name like '%i%'

zad3
 I need to know who the Global Fast Foods employees are that make more than $6.50/hour and their position is not order taker.

select * from f_staffs where salary>6.5 and staff_type!='Order Taker'

zad4
Using the employees table, write a query to display all employees whose last names start with “D” and have “a” and “e” anywhere in their last name.

select last_name from employees where last_name like 'D%e%' and last_name like 'D%a%'
select last_name from employees where last_name like 'D%e%a%' or last_name like 'D%a%e%'




zadanka 3-2

zad1
In the example below, assign the employee_id column the alias of “Number.” Complete the SQL statement to order the result set by the column alias.
SELECT employee_id, first_name, last_name
FROM employees;

odp:
SELECT employee_id "Number", first_name, last_name
FROM employees
order by "Number"

zad2
Create a query that will return all the DJs on Demand CD titles ordered by year with titles in alphabetical order by year

select year,title from d_cds order by year, title

zad3
Order the DJs on Demand songs by descending title. Use the alias “Our Collection” for the song title

select title "Our Collection" from d_songs order by title DESC



zadanka 4-1

zad3
What is the length of the string “Oracle Internet Academy”?

select length('Oracle Internet Academy') from dual

zad4
What’s the position of “I” in “Oracle Internet Academy”?

select instr('Oracle Internet Academy','I') from dual

zad5
Starting with the string “Oracle Internet Academy”, pad the string to create  ****Oracle****Internet****Academy****

select rpad(lpad('Oracle',10,'*'),14,'*') || rpad('Internet',12,'*') || rpad('Academy',11,'*') from dual

zad9
Write a query that will output a column called “ADDRESS” which has the following information:
ZOE TWEE 1009 OLIVER AVENUE BOSTON, MA 12889. Use the Global Fast Foods F_CUSTOMERS table.

select upper(first_name)||' '||upper(last_name)||' '||upper(address)||' '||upper(city)||' '||upper(state)||' '||zip "address" from f_customers

zad10
Write a query to return the first character of the first name concatenated to the last_name, the salary, and the department id for employees working in department 20.
Give the first expression an alias of Name. Use the EMPLOYEES table.  Change the query to use a substitution variable instead of the hard coded value 20 for department id.
Run the query for department 30 and 50 without changing the original where-clause in your statement

select substr(first_name,1,1)||last_name "Name", salary, department_id from employees where department_id = :podaj_dept




zadanka 4-2

1.  Display Oracle database employee last_name and salary for employee_ids between 100 and 102.
Include a third column that divides each salary by 1.55 and rounds the result to two decimal places.

select last_name, salary, round(salary/1.55,2) from employees where employee_id between 100 and 102

2.  Display employee last_name and salary for those employees who work in department 80. Give each of them a raise of 5.333% and truncate the result to two decimal places.

select last_name, salary, trunc(salary*1.05333,2) from employees where department_id = 80

5.  Divide each employee’s salary by 3. Display only those employees’ last names and sala-ries who earn a salary that is a multiple of 3.

select last_name, salary, mod(salary,3) from employees where mod(salary,3)=0




zadanka 4-3

1.   For DJs on Demand, display the number of months between the event_date of the Vigil wedding and today’s date. Round to the nearest month.

select round(months_between(sysdate, event_date)) from d_events where name='Vigil wedding'

2.   Display the days between the start of last summer’s school vacation break and the day school started this year. Assume 30.5 days per month. Name the output “Days.”

select months_between('01-Oct-2019','20-Jun-2019')*30.5 as "Days" from dual

5.   What is the last day of the month for June 2005? Use an alias for the output.

select last_day('15-Jun-2005') as"LasTDAy" from dual

7.   Your next appointment with the dentist is six months from today. On what day will you go to the dentist? Name the output, “Appointment.”

select add_months(sysdate,6) as"Appointment." from dual

10. What’s the date of the next Friday after your birthday this year? Name the output, “First Friday.”

select next_day(sysdate, 'Friday') as"First Friday." from dual




zadanka 5-1

1.  List the last names and birthdays of Global Fast Food Employees. Convert the birth dates to character data in the Month DD, YYYY format. Suppress any leading zeros.

select last_name, to_char(birthdate,'fmMonth DD, YYYY') from f_staffs

3.  Format a query from the Global Fast Foods f_promotional_menus table to print out the start_date of promotional code 110 as: The promotion began on the tenth of February 2004.

select 'The promotion began on the '||to_char(start_date,'ddspth "of" Month YYYY') from f_promotional_menus where code=110

5.  List the ID, name, and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places.

select id, last_name, to_char(salary, '$99.99') from f_staffs

8.  Create one query that will convert 25-Dec-2004 into each of the following (you will have to convert 25-Dec-2004 to a date and then to character data)
:December 25th, 2004      DECEMBER 25TH, 2004      25th december, 2004

select to_char(sysdate, 'Month ddth, YYYY') from dual
select to_char(sysdate, 'MONTH DDTH, YYYY') from dual
select to_char(sysdate, 'ddth month, YYYY') from dual



zadanka 5-2

1.  Create a report that shows the Global Fast Foods promotional name, start date, and end date from the f_promotional_menus table.
If there is an end date, temporarily replace it with “end in two weeks.” If there is no end date, replace it with today’s date.

select name, start_date, NVL2(end_date, 'end in two weeks', sysdate)  from f_promotional_menus

2.  Not all Global Fast Foods staff members receive overtime pay. Instead of displaying a null value for these employees, replace null with zero.
Include the employee’s last name and overtime rate in the output. Label the overtime rate as “Overtime Status”.

select last_name, overtime_rate, NVL(overtime_rate,0) "Overtime Status" from f_staffs

7.
a)  Create a report listing the first and last names and month of hire for all employees in the EMPLOYEES table (use TO_CHAR to convert hire_date to display the month).
b)  Modify the report to display null if the month of hire is September. Use the NULLIF function.

a) select first_name, last_name, to_char(hire_date, 'Month') from employees
b) select first_name, last_name, NULLIF(to_char(hire_date, 'Month'),'September') from employees


zadanka 5-3

1.  From the DJs on Demand d_songs table, create a query that replaces the 2-minute songs with “shortest” and the 10-minute songs with “longest”. Label the output column “Play Times”.

select title, decode(duration,'2 min','shortest','10 min','longest') "Play Times" from d_songs

2.  Use the Oracle database employees table and CASE expression to decode the depart-ment id.
Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions:
If the department id is 10 then 1.25 * salary
If the department id is 90 then 1.5 * salary
If the department id is 130 then 1.75 * salary
Otherwise, display the old salary.

select last_name, salary,
case department_id
	when 10 then 1.25 * salary
	when 90 then 1.5 * salary
	when 130 then 1.75 * salary
	else salary
end "New Salary"
from employees

3.  Display the first name, last name, manager ID, and commission percentage of all employ-ees in departments 80 and 90.
In a 5th column called “Review”, again display the manager ID. If they don’t have a manager, display the commission percentage. If they don’t have a commission, display 99999.

select first_name, last_name, manager_id, commission_pct, coalesce(manager_id, commission_pct, 99999) "Review" from employees where department_id in (80,90)


zadanka 12-1

2.  DJs on Demand just purchased four new CDs. Use an explicit INSERT statement to add each CD to the copy_d_cds table.
After completing the entries, execute a SELECT * statement to verify your work.

create table copy_d_cds as select * from d_cds
insert into copy_d_cds values (97, 'Celebrate the Day','R&B Inc.','2003')
insert into copy_d_cds values (98, 'Holiday Tunes for All Ages','Tunes are Us','2004')
insert into copy_d_cds values (99, 'Party Music','Old Town Records','2004')
insert into copy_d_cds values (100, 'Best of Rock and Roll','Old Town Records','2004')

3.  DJs on Demand has two new events coming up. One event is a fall football party and the other event is a sixties theme party.
The DJs on Demand clients requested the songs shown in the table for their events. Add these songs to the copy_d_songs table using an implicit INSERT statement.

create table copy_d_songs as select * from d_songs
insert into copy_d_songs values(52,'Surfing Summer','Not known',null,12)
insert into copy_d_songs (ID, Title, Duration, Type_Code) values(53,'Victory Victory','5 min',12)    drugi sposob bez null

6.
Create a table called rep_email using the following statement:
CREATE TABLE rep_email (id NUMBER(2) CONSTRAINT rel_id_pk PRIMARY KEY,first_name VARCHAR2(10),last_name VARCHAR2(10),email_address VARCHAR2(10))

insert into rep_email
select employee_id, first_name, last_name, email from employees where job_id like '%REP%'
select employee_id, first_name, last_name, email from employees where job_id like '%REP%'


zadanka 12-2

1.  Monique Tuttle, the manager of Global Fast Foods, sent a memo requesting an immediate change in prices. The price for a strawberry shake will be raised from $3.59 to $3.75, and the price for fries will increase to $1.20. Make these changes to the copy_f_food_items ta-ble.

create table copy_f_food_items as select * from f_food_items

update copy_f_food_items
set price=3.75
where description='Strawberry Shake'

update copy_f_food_items
set price=1.20
where description='Fries'

2.  Bob Miller and Sue Doe have been outstanding employees at Global Fast Foods. Man-agement has decided to reward them by increasing their overtime pay. Bob Miller will re-ceive an additional $0.75 per hour and Sue Doe will receive an additional $0.85 per hour.  Update the copy_f_staffs table to show these new values. (Note: Bob Miller cur-rently doesn’t get overtime pay. What function do you need to use to convert a null value to 0?)

create table copy_f_staffs as select * from f_staffs

update copy_f_staffs
set overtime_rate=nvl(overtime_rate,0)+0.75
where first_name='Bob' and last_name='Miller'

update copy_f_staffs
set overtime_rate=nvl(overtime_rate,0)+0.85
where first_name='Sue' and last_name='Doe'

5.  Sue Doe has been an outstanding Global Foods staff member and has been given a sal-ary raise. She will now be paid the same as Bob Miller. Update her record in copy_f_staffs.

update copy_f_staffs
set salary=(select salary from copy_f_staffs where first_name='Bob' and last_name='Miller')
where first_name='Sue' and last_name='Doe'

8.  Execute the following SQL statement. Record your results.
DELETE from departments
WHERE department_id = 60;

9.  Kim Kai has decided to go back to college and does not have the time to work and go to school. Delete him from the Global Fast Foods staff. Verify that the change was made.

DELETE from copy_f_staffs
where first_name='Kim' and last_name='Kai'


zadanka 12-3   tego na kartkowce niue bedzie w sensie ze insert all

2.  Currently, the Global Foods F_PROMOTIONAL_MENUS table START_DATE column does not have SYSDATE set as DEFAULT. Your manager has decided she would like to be able to set the starting date of promotions to the current day for some entries. This will require three steps:
a.  In your schema, Make a copy of the Global Foods F_PROMOTIONAL_MENUS table using the following SQL statement:
CREATE TABLE copy_f_promotional_menus
AS (SELECT * FROM f_promotional_menus)
b.  Alter the current START_DATE column attributes using:
ALTER TABLE copy_f_promotional_menus
MODIFY(start_date DATE DEFAULT SYSDATE)
c.   INSERT the new information and check to verify the results.INSERT a new row into the copy_f_promotional_menus table for the manager’s new promotion. The promotion code is 120. The name of the promotion is ‘New Customer.’ Enter DEFAULT for the start date and '01-Jun-2005' for the ending date. The giveaway is a 10% discount coupon. What was the correct syntax used?

CREATE TABLE copy_f_promotional_menus
AS (SELECT * FROM f_promotional_menus)
-----------------------------------
ALTER TABLE copy_f_promotional_menus
MODIFY(start_date DATE DEFAULT SYSDATE)
----------------------------------------
insert into copy_f_promotional_menus
values(120,'New Customer',default,'01-Jun-2005','10% discount coupon')
------------------------------------------
select * from copy_f_promotional_menus

4.  Run the following 3 statements to create 3 new tables for use in a Multi-table insert state-ment.  All 3 tables should be empty on creation, hence the WHERE 1=2 condition in the WHERE clause.

tworze podane tabele

Once the tables exist in your account, write a Multi-Table insert statement to first select the employee_id, hire_date, salary, and manager_id of all employees.  If the salary is more than 20000 insert the employee_id and salary into the special_sal table.  Insert the details of employee_id, hire_date, and salary into the sal_history table.  Insert the em-ployee_id, manager_id, and salary into the mgr_history table.

insert all
	when salary>20000 then
		into special_sal values (employee_id,salary)
	else
		into sal_history values (employee_id, hire_date, salary)
		into mgr_history values (employee_id, manager_id, salary)
select employee_id, hire_date, salary, manager_id from employees



zadanka 13-1

2.  Write the syntax to create the grad_candidates table.

create table GRADUATE_CANDIDATE (student_id number(6), last_name varchar2(50), first_name varchar2(50),credits number(3),graduation_date date)

4.  Create a new table using a subquery(podzapytania). Name the new table your last name -- e.g., smith_table. Using a subquery, copy grad_candidates into smith_table.

create table smith_table as
select * from GRADUATE_CANDIDATE

6.  Query the data dictionary for each of the following:  •USER_TABLES •USER_OBJECTS •USER_CATALOG or USER_CAT In separate sentences, summarize what each query will return

select * from USER_TABLES
select * from USER_OBJECTS
select * from USER_CATALOG



zadanka 13-3

3.   In your o_employees table, enter a new column called “Termination.” The datatype for the new column should be VARCHAR2. Set the DEFAULT for this column as SYSDATE to appear as character data in the format: February 20th, 2003
modyfikacja istniejacej tabeli na kartkowce bedzie (polecenia: alter, drop, create)
alter table o_employees
add (Termination VARCHAR2(50) default to_char(sysdate,'MONTH ddth, yyyy'))

5.   Truncate the o_jobs table. Then do a SELECT * statement. Are the columns still there? Is the data still there?

select * from o_jobs
truncate table o_jobs
select * from o_jobs

9.   Rename the o_jobs table to o_job_description

rename o_jobs to o_jobs_description
select * from o_jobs_description

10. F_staffs table exercises:
a.   Create a copy of the f_staffs table called copy_f_staffs and use this copy table for the remain-ing labs in this lesson.
b.   Describe the new table to make sure it exists.
c.   Drop the table.
d.   Try to select from the table.
e.   Investigate your recyclebin to see where the table went.
f.    Try to select from the dropped table by using the value stored in the OBJECT_NAME column.  You will need to copy and paste the name as it is exactly, and enclose the new name in “ “ (double quotes).  So if the dropped name returned to you is BIN$Q+x1nJdcUnngQESYELVIdQ==$0, you need to write a query that refers to “BIN$Q+x1nJdcUnngQESYELVIdQ==$0”.
g.   Undrop the table.
h.   Describe the table.

create table copy_f_staffs
as select * from f_staffs

drop table copy_f_staffs
select * from copy_f_staffs
zobaczyc czy jest w koszu select * from user_recyclebin

flashback table copy_f_staffs to before drop
select * from copy_f_staffs

drop table copy_f_staffs purge (usuniece na stałe bez kosza, juz nie przywrócimy tabeli)

!!bedzie zadanie na kartkowce z constraintsami(wiezy integralności, na egz tez bedzie jest ich 5)!!



zadanka 14-1

6.  Write the CREATE TABLE statement for the Global Fast Foods locations table to define the constraints at the column level.

create table Global_Fast_Foods_Locations(
id NUMBER primary key,
name varchar2(50),
date_opened DATE NOT NULL,
address varchar2(50)NOT NULL,
city varchar2(50)NOT NULL,
zip varchar2(50),
phone varchar2(50),
email varchar2(50) UNIQUE,
manager_id NUMBER,
emergency_contact varchar2(50))

9.  Rewrite the CREATE TABLE statement for the Global Fast Foods locations table to define the UNIQUE constraints at the table level. Do not execute this statement.

create table Global_Fast_Foods_Locations(
id NUMBER primary key,
name varchar2(50),
date_opened DATE NOT NULL,
address varchar2(50)NOT NULL,
city varchar2(50)NOT NULL,
zip varchar2(50),
phone varchar2(50),
email varchar2(50),
manager_id NUMBER,
emergency_contact varchar2(50),
CONSTRAINT email_uk UNIQUE(email)
)




zadanka 14-2

każda tabela powinnna miec klucz główny, nie moze byc nullem

3.  Create the animals table. Write the syntax you will use to create the table.
create table animals(
animal_id NUMBER(6) PRIMARY KEY,
name VARCHAR2(25),
license_tag_number NUMBER(10) UNIQUE,
admit_date DATE NOT NULL,
adoption_id NUMBER(5) NOT NULL,
vaccination_date DATE)

4.  Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to the graphic below for input

insert into animals values (101, 'Spot', 35540, '10-Oct-2004', 205, '12-Oct-2004')

5.  Write the syntax to create a foreign key (adoption_id) in the animals table that has a corresponding primary- key reference in the adoptions table. Show both the column-level and table-level syntax. Note that because you have not actually created an adoptions table, no adoption_id primary key exists, so the foreign key cannot be added to the animals table.

create table animals(
animal_id NUMBER(6) PRIMARY KEY,
name VARCHAR2(25),
license_tag_number NUMBER(10) UNIQUE,
admit_date DATE NOT NULL,
adoption_id NUMBER(5) REFERENCES adoptions(adoption_id),
vaccination_date DATE NOT NULL)


ZADANIE DODOTKOWE do 14-2 wiezy integralności

tworzymy 2 tablele ksiazka i wydawnictwo i nadjemy im pewne uprawnienia

create table wydawnictwo(
id_wyd NUMBER PRIMARY KEY,
rok NUMBER CHECK (rok like '____'))

create table ksiazka(
id NUMBER PRIMARY KEY,
tytul VARCHAR2(50) NOT NULL,
cena NUMBER CHECK (cena>0),
id_wyd NUMBER REFERENCES wydawnictwo(id_wyd))



zadanka 14-3

2.  Since the tables are copies of the original tables, the integrity rules are not passed onto the new tables; only the column datatype definitions remain. You will need to add a PRIMARY KEY constraint to the copy_d_clients table. Name the primary key copy_d_clients_pk . What is the syntax you used to create the PRIMARY KEY constraint to the copy_d_clients.table?

create table copy_d_clients as select * from d_clients

alter table copy_d_clients
add constraint copy_d_clients_pk PRIMARY KEY(client_number)

3.  Create a FOREIGN KEY constraint in the copy_d_events table. Name the foreign key copy_d_events_fk. This key references the copy_d_clients table client_number column. What is the syntax you used to create the FOREIGN KEY constraint in the copy_d_events table?

create table copy_d_events as select * from d_events

alter table copy_d_events
add constraint nnnnnn FOREIGN KEY(client_number) references copy_d_clients(client_number)



----------------------------------------------------------------------------------------------
16.12.2019 Kartkówka z działów 12,13,14
Insert, update, delate
create table, alter table
foreign, primary key, check, not unique, not null
----------------------------------------------------------------------------------------------



zadanka 6-1

1.   Create a cross-join that displays the last name and department name from the employees and de-partments tables.

select last_name, department_name
from employees cross join departments

3.   Create a query that uses a natural join to join the departments table and the locations table. Re-strict the output to only department IDs of 20 and 50. Display the department id, department name, location id, and city.

select department_id, department_name, location_id, city
from departments natural join locations





zadanka 6-2

1.  Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.

select * from departments d join locations l on (d.location_id = l.location_id)
where d.location_id=1400

2.  Join DJs on Demand d_play_list_items, d_track_listings, and d_cds tables with the JOIN USING syntax. Include the song ID, CD number, title, and comments in the output.

select song_ID, CD_number, title, comments
from d_cds join d_track_listings using(CD_number) join d_play_list_items using(song_ID)

3.  Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle.

select city, department_name, d.location_id, department_id
from departments d join locations l on (d.location_id = l.location_id)
where city='Seattle' and department_id in (10,20,30)

5.  Write a statement joining the employees and jobs tables. Display the first and last names, hire date, job id, job title, and maximum salary. Limit the query to those employees who are in jobs that can earn more than $12,000.

select first_name, last_name, hire_date, e.job_id, job_title, max_salary
from employees e join jobs j on (e.job_id = j.job_id)
where salary > 12000

7.  Write a statement that displays the employee ID, first name, last name, manager ID, man-ager first name, and manager last name for every employee in the employees table. Hint: this is a self-join.

pomijamy te zadanie narazie
robimy
select w.last_name, m.last_name from employees w join employees m on (w.manager_id=m.employee_id)

8.  Use JOIN ON syntax to query and display the location ID, city, and department name for all Canadian locations.

select d.location_id, city, department_name
from departments d join locations l on (d.location_id = l.location_id) join countries c on (l.country_id = c.country_id)
where country_name='Canada'

9.  Query and display manager ID, department ID, department name, first name, and last name for all employees in departments 80, 90, 110, and 190

select e.manager_ID, e.department_id, department_name, first_name, last_name from employees e join departments d on (e.department_id=d.department_id)
where d.department_id in (80,90,110,190)

10. Display employee ID, last name, department ID, department name, and hire date for those employees whose hire date was June 7, 1994.

select e.employee_id, e.department_id, department_name, hire_date, last_name from employees e join departments d on (e.department_id=d.department_id)
where hire_date ='07-Jun-1994'



zadanka 6-3

1.  Return the first name, last name, and department name for all employees including those employees not assigned to a department.

select first_name, last_name, department_name from employees e left join departments d on (e.department_id = d.department_id)

2.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them.

select first_name, last_name, department_name from employees e right join departments d on (e.department_id = d.department_id)

3.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them and those employees not assigned to a department.

select first_name, last_name, department_name from employees e full join departments d on (e.department_id = d.department_id)

4.  Create a query of the DJs on Demand database to return the first name, last name, event date, and description of the event the client held. Include all the clients even if they have not had an event scheduled.

select first_name, last_name, event_date, description from d_clients c left join d_events e on (c.client_number=e.client_number)



zadanka 8-1

2.  Create a query that will show the average cost of the DJs on Demand events. Round to two decimal places.

select round(avg(cost),2) from d_events

4.  Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.

select sum(salary) from f_staffs where id in (9,12)

5.  Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.

select min(salary), max(hire_date), min(last_name), max(last_name) from employees where department_id in (50,60)

9.  Create a query that will return the average order total for all Global Fast Foods orders fromJanuary 1, 2002, to December 21, 2002.

select avg(order_total) from f_orders where order_date between '01-Jan-2002' and '21-Dec-2002'



zadanka 8-2

1.  How many songs are listed in the DJs on Demand D_SONGS table?

select count(*) from d_songs

2.  In how many different location types has DJs on Demand had venues?

select count(distinct loc_type) from d_venues       distinct bierze tylko rozne lokalizacje

4.  How many of the DJs on Demand customers have email addresses?

select count(email) from d_clients

7.  Create a query that will convert any null values in the auth_expense_amt column on the DJs on Demand D_PARTNERS table to 100000 and find the average of the values in this column. Round the result to two decimal places.

select round(avg(nvl(auth_expense_amt, 100000))) from d_partners



zadanka 9-1

2.  Each of the following SQL queries has an error. Find the error and correct it. Use Oracle Application Express to verify that your corrections produce the desired results.

select min(avg(salary)), max(avg(salary))
from employees
group by department_id;

5.  Write a query that will return both the maximum and minimum average salary grouped by department from the employees table.

select avg(max(salary))
from employees
group by department_id;


zadania z maila do 9.1

1.
select manager_id, job_id, count(*)
from employees
group by manager_id, job_id
order by manager_id;

2.
select job_id, avg(salary)
from employees
group by job_id
having count(*)>3

3.
select job_id, count(*)
from employees
where manager_id<>101
group by job_id
having avg(salary)>3000

4.
select max(salary)-min(salary) from employees

5.
select manager_id, count(manager_id)
from employees
where manager_id is not null
group by manager_id

6.
select department_id
from employees
group by department_id
having sum(nvl(commission_pct,0))=0




zadanka 9-3

2.  Write one query to return the employee_id, job_id, hire_date, and department_id of all employees and a second query listing employee_id, job_id, start_date, and department_id from the job_history table and combine the results as one single output.  Make sure you suppress duplicates in the output.

select employee_id, job_id, hire_date, department_id from employees
union
select employee_id, job_id, start_date, department_id from job_history

3.  Amend the previous statement to not suppress duplicates and examine the output.  How many extra rows did you get returned and which were they?  Sort the output by employ-ee_id to make it easier to spot.

select employee_id, job_id, hire_date, department_id from employees
union all
select employee_id, job_id, start_date, department_id from job_history
order by employee_id

4.  List all employees who have not changed jobs even once. (Such employees are not found in the job_history table)

select employee_id from employees
minus
select employee_id from job_history

5.  List the employees that HAVE changed their jobs at least once.

select employee_id from employees
intersect
select employee_id from job_history




zadanka 10-1

3.  What DJs on Demand d_play_list_items song_id’s have the same event_id as song_id 45?

select song_id from d_play_list_items where event_id=(select event_id from d_play_list_items where song_id=45)

4.  Which events in the DJs on Demand database cost more than event_id = 100?

select id from d_events where cost>(select cost from d_events where id=100)

8.  What are the names of the Global Fast Foods staff members whose staff types are not the same as Bob Miller’s?

select last_name from f_staffs where staff_type!=(select staff_type from f_staffs where last_name='Miller')

natatka -> jak mam where staff... to pozniej w nawiasie select (to samo zawsze) czyli staff...




zadanka 10-2

1.  Write a query to return all those employees who have a salary greater than that of Lorentz and are in the same department as Abel.

select * from employees where salary>(select salary from employees where last_name='Lorentz')
and
department_id=(select department_id from employees where last_name='Abel')

tutaj tak samo jak wczesniej, czyli jak jest salary to w podzapytaniu tez musi byc salary.

4.  What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?

select staff_type from f_staffs where salary>(select min(salary) from f_staffs where staff_type='Cook')

5.  Write a query to return a list of department id’s and average salaries where the depart-ment’s average salary is greater than Ernst’s salary.

select department_id, avg(salary) from employees
group by department_id
having avg(salary)>(select salary from employees where last_name='Ernst')

6.  Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

select department_id, min(salary) from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id<>50)




zadanka 10-3

2.  Write a query that returns jazz and pop songs. Write a multi-row subquery and use the d_songs and d_types tables. Include the id, title, duration, and the artist name.

select id, title, duration, artist from d_songs where TYPE_CODE in (select code from d_types where description in ('Jazz', 'Pop'))

3.  Find the last names of all employees whose salaries are the same as the minimum salary for any department.

select last_name from employees where salary=ANY(select min(salary) from employees group by department_id)

4.  Which Global Fast Foods employee earns the lowest salary? Hint: You can use either a single-row or a multiple-row subquery.

single-row
select last_name from employees where salary=(select min(salary) from employees)

multi-row
select last_name from employees where salary<=ALL(select salary from employees)

9.  Write a pair-wise subquery listing the last_name, first_name, department_id, and manager_id for all employees that have the same department_ id and manager_id as em-ployee 141.  Exclude employee 141 from the result set.

select last_name, first_name, department_id, manager_id from employees where(department_id,manager_id) in (select department_id,manager_id from employees where employee_id=141)
and employee_id!=141




zadanka 10-4

2.  Write a query that lists the highest earners for each department.  Include the last_name, department_id, and the salary for each employee.

select last_name, department_id, salary from employees o
where salary=(select max(salary) from employees i where i.department_id=o.department_id)

3.  Examine the following select statement and finish it so that it will return the last_name, de-partment_id, and salary of employees who have at least one person reporting to them.  So we are effectively looking for managers only.  In the partially written SELECT statement, the WHERE clause will work as it is.  It is simply testing for the existence of a row in the subquery.
SELECT (enter columns here)
FROM   (enter table name here) outer
WHERE  'x' IN (SELECT 'x'
FROM   (enter table name here) inner
WHERE  inner(enter column name here)  = inner(enter column name here)
Finish off the statement by sorting the rows on the department_id column.

SELECT last_name, department_id, salary
FROM   employees outer
WHERE  'x' IN (SELECT 'x'
FROM   employees inner
WHERE  inner.manager_id  = outer.employee_id)


27.01.2020 ostatnia kartkowa z dzialow 6,8,9,10
złaczenia, złaczenia zewnetrze, grupowanie z hevingiem min max, podzapytanie, podzapytanie skorelowane
mozna pocwiczyc dział 11 sobie

04.02.2020 oddajemy projekty we wtorek

diagram, wymagania co wyslane byly kiedys
pozniej wyslac na maila opis slowny, diagram i cały kodzik