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