tKyZvubJ

1zadanka z 2-1
2
3zad 1
4The manager of Global Fast Foods would like to send out coupons for the upcoming sale. 
5He wants to send one coupon to each household. Create the SELECT statement that returns the customer last name and a mailing address.
6
7select distinct last_name, address from f_customers
8
9
10zad 3
11Sue, 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. 
12
13select ' *** '||first_name||' *** '||first_name||' *** ' "SUPER STAR" from f_staffs
14
15
16zad 5
17Global Fast Foods has decided to give all staff members a 5% raise. Prepare a report that presents the output as shown in the chart.
18
19select last_name "EMPLOYEE LAST NAME", salary "CURRENT SALARY", salary*1.05 "SALARY WITH 5% RAISE" from f_staffs
20
21
22zad6
23Create a query that will return the structure of the Oracle database EMPLOYEES  table. Which columns are marked “nullable”? What does this mean?
24
25describe employees
26
27zad7
28The owners of DJs on Demand would like a report of all items in their D_CDs table with the following 
29column headings: Inventory Item, CD Title, Music Producer, and Year Purchased. Prepare this report
30
31select * from d_cds    - bez aliasów 
32
33
34
35
36zadanka z 2-2
37
38zad1
39Using the Global Fast Foods database, retrieve the customer’s first name, last name, and address for the customer who uses ID 456.
40
41select first_name, last_name, address from f_customers where id=456
42
43zad2
44Show the name, start date, and end date for Global Fast Foods' promotional item “ballpen and highlighter” giveaway.
45
46select name, start_date, end_date from f_promotional_menus where give_away='ballpen and highlighter'
47
48zad5
49The manager of DJs on Demand would like a report of all the CD titles and years of CDs that were produced before 2000.
50
51select title, year from d_cds where year < 2000
52
53zad7
54Write a SQL statement that will display the student number (studentno), 
55first name (fname), and last name (lname) for all students who are female (F) in the table named students.
56kolumna znajduje sie w poleceniu
57
58select studentno, fname, lname from students where sex = 'F'
59
60zad10
61Write a SQL statement that will list the titles and years of all the DJs on Demand CDs that were not produced in 2000.
62select title, year from d_cds where year != 2000
63
64zad11
65Write a SQL statement that lists the Global Fast Foods employees who were born before 1980.
66
67select * from f_staffs where birthdate<'01-Jan-1980'
68
69
70
71zadanka 2-3
72
73zad1
74Display the first name, last name, and salary of all Global Fast Foods staff whose salary is between $5.00 and $10.00 per hour.
75
76select first_name, last_name, salary from f_staffs where salary between 5 and 10
77
78
79zad4
80Create a list of all the DJs on Demand CD titles that have “a” as the second letter in the title.
81
82select title from d_cds where title like '_a%'
83
84zad5
85Who are the partners of DJs on Demand who do not get an authorized expense amount?
86
87select * from d_partners where auth_expense_amt is null
88
89zad6
90Select all the Oracle database employees whose last names end with “s”. Change the heading of the column to read Possible Candidates.
91
92select last_name "Possible Candidates" from employees where last_name like '%s'
93
94zad8
95Write a SQL statement that lists the songs in the DJs on Demand inventory that are type code 77, 12, or 1.
96
97select * from d_songs where type_code in (77,12,1)
98
99
100
101
102zadanka 3-1
103
104zad2
105Display the last names of all Global Fast Foods employees who have “e” and “i” in their last names
106
107select last_name from f_staffs where last_name like '%e%' and last_name like '%i%'
108
109zad3
110 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.
111 
112select * from f_staffs where salary>6.5 and staff_type!='Order Taker'
113
114zad4
115Using 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.
116
117select last_name from employees where last_name like 'D%e%' and last_name like 'D%a%'
118select last_name from employees where last_name like 'D%e%a%' or last_name like 'D%a%e%'
119
120
121
122
123zadanka 3-2
124
125zad1
126In 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.
127SELECT employee_id, first_name, last_name
128FROM employees;
129
130odp:
131SELECT employee_id "Number", first_name, last_name
132FROM employees
133order by "Number"
134
135zad2
136Create a query that will return all the DJs on Demand CD titles ordered by year with titles in alphabetical order by year
137
138select year,title from d_cds order by year, title
139
140zad3
141Order the DJs on Demand songs by descending title. Use the alias “Our Collection” for the song title
142
143select title "Our Collection" from d_songs order by title DESC
144
145
146
147zadanka 4-1
148
149zad3
150What is the length of the string “Oracle Internet Academy”?
151
152select length('Oracle Internet Academy') from dual
153
154zad4
155What’s the position of “I” in “Oracle Internet Academy”?
156
157select instr('Oracle Internet Academy','I') from dual
158
159zad5
160Starting with the string “Oracle Internet Academy”, pad the string to create  ****Oracle****Internet****Academy**** 
161
162select rpad(lpad('Oracle',10,'*'),14,'*') || rpad('Internet',12,'*') || rpad('Academy',11,'*') from dual
163
164zad9
165Write a query that will output a column called “ADDRESS” which has the following information: 
166ZOE TWEE 1009 OLIVER AVENUE BOSTON, MA 12889. Use the Global Fast Foods F_CUSTOMERS table.
167
168select upper(first_name)||' '||upper(last_name)||' '||upper(address)||' '||upper(city)||' '||upper(state)||' '||zip "address" from f_customers
169
170zad10
171Write 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.   
172Give 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.  
173Run the query for department 30 and 50 without changing the original where-clause in your statement
174
175select substr(first_name,1,1)||last_name "Name", salary, department_id from employees where department_id = :podaj_dept
176
177
178
179
180zadanka 4-2
181
1821.  Display Oracle database employee last_name and salary for employee_ids between 100 and 102. 
183Include a third column that divides each salary by 1.55 and rounds the result to two decimal places.
184
185select last_name, salary, round(salary/1.55,2) from employees where employee_id between 100 and 102
186
1872.  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.
188
189select last_name, salary, trunc(salary*1.05333,2) from employees where department_id = 80
190
1915.  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.
192
193select last_name, salary, mod(salary,3) from employees where mod(salary,3)=0
194
195
196
197
198zadanka 4-3
199
2001.   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.
201
202select round(months_between(sysdate, event_date)) from d_events where name='Vigil wedding'
203
2042.   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.”
205
206select months_between('01-Oct-2019','20-Jun-2019')*30.5 as "Days" from dual
207
2085.   What is the last day of the month for June 2005? Use an alias for the output.
209
210select last_day('15-Jun-2005') as"LasTDAy" from dual
211
2127.   Your next appointment with the dentist is six months from today. On what day will you go to the dentist? Name the output, “Appointment.”
213
214select add_months(sysdate,6) as"Appointment." from dual
215
21610. What’s the date of the next Friday after your birthday this year? Name the output, “First Friday.”
217
218select next_day(sysdate, 'Friday') as"First Friday." from dual
219
220
221
222
223zadanka 5-1
224
2251.  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.
226
227select last_name, to_char(birthdate,'fmMonth DD, YYYY') from f_staffs
228
2293.  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.
230
231select 'The promotion began on the '||to_char(start_date,'ddspth "of" Month YYYY') from f_promotional_menus where code=110
232
2335.  List the ID, name, and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places.
234
235select id, last_name, to_char(salary, '$99.99') from f_staffs
236
2378.  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)
238:December 25th, 2004      DECEMBER 25TH, 2004      25th december, 2004
239
240select to_char(sysdate, 'Month ddth, YYYY') from dual
241select to_char(sysdate, 'MONTH DDTH, YYYY') from dual
242select to_char(sysdate, 'ddth month, YYYY') from dual
243
244
245
246zadanka 5-2
247
2481.  Create a report that shows the Global Fast Foods promotional name, start date, and end date from the f_promotional_menus table. 
249If 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.
250
251select name, start_date, NVL2(end_date, 'end in two weeks', sysdate)  from f_promotional_menus
252
2532.  Not all Global Fast Foods staff members receive overtime pay. Instead of displaying a null value for these employees, replace null with zero. 
254Include the employee’s last name and overtime rate in the output. Label the overtime rate as “Overtime Status”.
255
256select last_name, overtime_rate, NVL(overtime_rate,0) "Overtime Status" from f_staffs
257
2587.   
259a)  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).
260b)  Modify the report to display null if the month of hire is September. Use the NULLIF function.
261
262a) select first_name, last_name, to_char(hire_date, 'Month') from employees
263b) select first_name, last_name, NULLIF(to_char(hire_date, 'Month'),'September') from employees
264
265
266zadanka 5-3
267
2681.  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”.
269
270select title, decode(duration,'2 min','shortest','10 min','longest') "Play Times" from d_songs
271
2722.  Use the Oracle database employees table and CASE expression to decode the depart-ment id. 
273Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions:
274If the department id is 10 then 1.25 * salary
275If the department id is 90 then 1.5 * salary
276If the department id is 130 then 1.75 * salary
277Otherwise, display the old salary.
278
279select last_name, salary,
280case department_id
281	when 10 then 1.25 * salary
282	when 90 then 1.5 * salary
283	when 130 then 1.75 * salary
284	else salary
285end "New Salary"
286from employees	
287
2883.  Display the first name, last name, manager ID, and commission percentage of all employ-ees in departments 80 and 90. 
289In 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. 
290
291select first_name, last_name, manager_id, commission_pct, coalesce(manager_id, commission_pct, 99999) "Review" from employees where department_id in (80,90) 
292
293
294zadanka 12-1
295
2962.  DJs on Demand just purchased four new CDs. Use an explicit INSERT statement to add each CD to the copy_d_cds table. 
297After completing the entries, execute a SELECT * statement to verify your work. 
298
299create table copy_d_cds as select * from d_cds
300insert into copy_d_cds values (97, 'Celebrate the Day','R&B Inc.','2003')
301insert into copy_d_cds values (98, 'Holiday Tunes for All Ages','Tunes are Us','2004')
302insert into copy_d_cds values (99, 'Party Music','Old Town Records','2004')
303insert into copy_d_cds values (100, 'Best of Rock and Roll','Old Town Records','2004')
304
3053.  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. 
306The 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.
307
308create table copy_d_songs as select * from d_songs
309insert into copy_d_songs values(52,'Surfing Summer','Not known',null,12)
310insert into copy_d_songs (ID, Title, Duration, Type_Code) values(53,'Victory Victory','5 min',12)    drugi sposob bez null
311
3126.
313Create a table called rep_email using the following statement:
314CREATE TABLE rep_email (id NUMBER(2) CONSTRAINT rel_id_pk PRIMARY KEY,first_name VARCHAR2(10),last_name VARCHAR2(10),email_address VARCHAR2(10))
315
316insert into rep_email
317select employee_id, first_name, last_name, email from employees where job_id like '%REP%'
318select employee_id, first_name, last_name, email from employees where job_id like '%REP%'
319
320
321zadanka 12-2
322
3231.  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.
324
325create table copy_f_food_items as select * from f_food_items
326
327update copy_f_food_items
328set price=3.75
329where description='Strawberry Shake'
330
331update copy_f_food_items
332set price=1.20
333where description='Fries'
334
3352.  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?)
336
337create table copy_f_staffs as select * from f_staffs
338
339update copy_f_staffs
340set overtime_rate=nvl(overtime_rate,0)+0.75
341where first_name='Bob' and last_name='Miller'
342
343update copy_f_staffs
344set overtime_rate=nvl(overtime_rate,0)+0.85
345where first_name='Sue' and last_name='Doe'
346
3475.  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.
348
349update copy_f_staffs
350set salary=(select salary from copy_f_staffs where first_name='Bob' and last_name='Miller')
351where first_name='Sue' and last_name='Doe'
352
3538.  Execute the following SQL statement. Record your results.
354DELETE from departments
355WHERE department_id = 60;
356
3579.  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.
358
359DELETE from copy_f_staffs
360where first_name='Kim' and last_name='Kai'
361
362
363zadanka 12-3   tego na kartkowce niue bedzie w sensie ze insert all
364
3652.  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:
366a.  In your schema, Make a copy of the Global Foods F_PROMOTIONAL_MENUS table using the following SQL statement:
367CREATE TABLE copy_f_promotional_menus
368AS (SELECT * FROM f_promotional_menus)
369b.  Alter the current START_DATE column attributes using:
370ALTER TABLE copy_f_promotional_menus
371MODIFY(start_date DATE DEFAULT SYSDATE)
372c.   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?
373
374CREATE TABLE copy_f_promotional_menus
375AS (SELECT * FROM f_promotional_menus)
376-----------------------------------
377ALTER TABLE copy_f_promotional_menus
378MODIFY(start_date DATE DEFAULT SYSDATE)
379----------------------------------------
380insert into copy_f_promotional_menus
381values(120,'New Customer',default,'01-Jun-2005','10% discount coupon')
382------------------------------------------
383select * from copy_f_promotional_menus
384
3854.  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.
386
387tworze podane tabele 
388
389Once 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.
390
391insert all
392	when salary>20000 then
393		into special_sal values (employee_id,salary)
394	else
395		into sal_history values (employee_id, hire_date, salary)
396		into mgr_history values (employee_id, manager_id, salary)
397select employee_id, hire_date, salary, manager_id from employees
398
399
400
401zadanka 13-1
402
4032.  Write the syntax to create the grad_candidates table.
404
405create table GRADUATE_CANDIDATE (student_id number(6), last_name varchar2(50), first_name varchar2(50),credits number(3),graduation_date date)
406
4074.  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.
408
409create table smith_table as 
410select * from GRADUATE_CANDIDATE
411
4126.  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
413
414select * from USER_TABLES
415select * from USER_OBJECTS
416select * from USER_CATALOG
417
418
419
420zadanka 13-3
421
4223.   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
423modyfikacja istniejacej tabeli na kartkowce bedzie (polecenia: alter, drop, create)
424alter table o_employees
425add (Termination VARCHAR2(50) default to_char(sysdate,'MONTH ddth, yyyy'))
426
4275.   Truncate the o_jobs table. Then do a SELECT * statement. Are the columns still there? Is the data still there?
428
429select * from o_jobs
430truncate table o_jobs
431select * from o_jobs
432
4339.   Rename the o_jobs table to o_job_description
434
435rename o_jobs to o_jobs_description
436select * from o_jobs_description
437
43810. F_staffs table exercises:
439a.   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.
440b.   Describe the new table to make sure it exists.
441c.   Drop the table.
442d.   Try to select from the table.
443e.   Investigate your recyclebin to see where the table went.
444f.    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”.
445g.   Undrop the table.
446h.   Describe the table.
447
448create table copy_f_staffs
449as select * from f_staffs
450
451drop table copy_f_staffs
452select * from copy_f_staffs
453zobaczyc czy jest w koszu select * from user_recyclebin
454
455flashback table copy_f_staffs to before drop
456select * from copy_f_staffs
457
458drop table copy_f_staffs purge (usuniece na stałe bez kosza, juz nie przywrócimy tabeli)
459
460!!bedzie zadanie na kartkowce z constraintsami(wiezy integralności, na egz tez bedzie jest ich 5)!! 
461
462
463
464zadanka 14-1
465
4666.  Write the CREATE TABLE statement for the Global Fast Foods locations table to define the constraints at the column level.
467
468create table Global_Fast_Foods_Locations(
469id NUMBER primary key,
470name varchar2(50),
471date_opened DATE NOT NULL,
472address varchar2(50)NOT NULL,
473city varchar2(50)NOT NULL,
474zip varchar2(50),
475phone varchar2(50),
476email varchar2(50) UNIQUE,
477manager_id NUMBER,
478emergency_contact varchar2(50))
479
4809.  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.
481
482create table Global_Fast_Foods_Locations(
483id NUMBER primary key,
484name varchar2(50),
485date_opened DATE NOT NULL,
486address varchar2(50)NOT NULL,
487city varchar2(50)NOT NULL,
488zip varchar2(50),
489phone varchar2(50),
490email varchar2(50),
491manager_id NUMBER,
492emergency_contact varchar2(50),
493CONSTRAINT email_uk UNIQUE(email)
494)
495
496
497
498
499zadanka 14-2
500
501każda tabela powinnna miec klucz główny, nie moze byc nullem
502
5033.  Create the animals table. Write the syntax you will use to create the table.
504create table animals(
505animal_id NUMBER(6) PRIMARY KEY,
506name VARCHAR2(25),
507license_tag_number NUMBER(10) UNIQUE,
508admit_date DATE NOT NULL,
509adoption_id NUMBER(5) NOT NULL,
510vaccination_date DATE)
511
5124.  Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to the graphic below for input
513
514insert into animals values (101, 'Spot', 35540, '10-Oct-2004', 205, '12-Oct-2004')
515
5165.  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.
517
518create table animals(
519animal_id NUMBER(6) PRIMARY KEY,
520name VARCHAR2(25),
521license_tag_number NUMBER(10) UNIQUE,
522admit_date DATE NOT NULL,
523adoption_id NUMBER(5) REFERENCES adoptions(adoption_id),
524vaccination_date DATE NOT NULL)
525
526
527ZADANIE DODOTKOWE do 14-2 wiezy integralności
528
529tworzymy 2 tablele ksiazka i wydawnictwo i nadjemy im pewne uprawnienia
530
531create table wydawnictwo(
532id_wyd NUMBER PRIMARY KEY,
533rok NUMBER CHECK (rok like '____'))
534
535create table ksiazka(
536id NUMBER PRIMARY KEY,
537tytul VARCHAR2(50) NOT NULL,
538cena NUMBER CHECK (cena>0),
539id_wyd NUMBER REFERENCES wydawnictwo(id_wyd))
540
541
542
543zadanka 14-3
544
5452.  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?
546
547create table copy_d_clients as select * from d_clients
548
549alter table copy_d_clients
550add constraint copy_d_clients_pk PRIMARY KEY(client_number)
551
5523.  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?
553
554create table copy_d_events as select * from d_events
555
556alter table copy_d_events
557add constraint nnnnnn FOREIGN KEY(client_number) references copy_d_clients(client_number)
558
559
560
561----------------------------------------------------------------------------------------------
56216.12.2019 Kartkówka z działów 12,13,14
563Insert, update, delate
564create table, alter table
565foreign, primary key, check, not unique, not null
566----------------------------------------------------------------------------------------------
567
568
569
570zadanka 6-1
571
5721.   Create a cross-join that displays the last name and department name from the employees and de-partments tables.
573
574select last_name, department_name
575from employees cross join departments
576
5773.   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.
578
579select department_id, department_name, location_id, city
580from departments natural join locations
581
582
583
584
585
586zadanka 6-2
587
5881.  Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.
589
590select * from departments d join locations l on (d.location_id = l.location_id)
591where d.location_id=1400 
592
5932.  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.
594
595select song_ID, CD_number, title, comments
596from d_cds join d_track_listings using(CD_number) join d_play_list_items using(song_ID)
597
5983.  Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle. 
599
600select city, department_name, d.location_id, department_id
601from departments d join locations l on (d.location_id = l.location_id)
602where city='Seattle' and department_id in (10,20,30)
603
6045.  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.
605
606select first_name, last_name, hire_date, e.job_id, job_title, max_salary
607from employees e join jobs j on (e.job_id = j.job_id)
608where salary > 12000
609
6107.  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.
611
612pomijamy te zadanie narazie
613robimy
614select w.last_name, m.last_name from employees w join employees m on (w.manager_id=m.employee_id)
615
6168.  Use JOIN ON syntax to query and display the location ID, city, and department name for all Canadian locations. 
617
618select d.location_id, city, department_name
619from departments d join locations l on (d.location_id = l.location_id) join countries c on (l.country_id = c.country_id)
620where country_name='Canada'
621
6229.  Query and display manager ID, department ID, department name, first name, and last name for all employees in departments 80, 90, 110, and 190
623
624select 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)
625where d.department_id in (80,90,110,190)
626
62710. Display employee ID, last name, department ID, department name, and hire date for those employees whose hire date was June 7, 1994.
628
629select 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)
630where hire_date ='07-Jun-1994'
631
632
633
634zadanka 6-3
635
6361.  Return the first name, last name, and department name for all employees including those employees not assigned to a department.
637
638select first_name, last_name, department_name from employees e left join departments d on (e.department_id = d.department_id)
639
6402.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them.
641
642select first_name, last_name, department_name from employees e right join departments d on (e.department_id = d.department_id)
643
6443.  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.
645
646select first_name, last_name, department_name from employees e full join departments d on (e.department_id = d.department_id)
647
6484.  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.
649
650select first_name, last_name, event_date, description from d_clients c left join d_events e on (c.client_number=e.client_number)
651
652
653
654zadanka 8-1
655
6562.  Create a query that will show the average cost of the DJs on Demand events. Round to two decimal places.
657
658select round(avg(cost),2) from d_events
659
6604.  Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.
661
662select sum(salary) from f_staffs where id in (9,12)
663
6645.  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.
665
666select min(salary), max(hire_date), min(last_name), max(last_name) from employees where department_id in (50,60)
667
6689.  Create a query that will return the average order total for all Global Fast Foods orders fromJanuary 1, 2002, to December 21, 2002.
669
670select avg(order_total) from f_orders where order_date between '01-Jan-2002' and '21-Dec-2002'
671
672
673
674zadanka 8-2
675
6761.  How many songs are listed in the DJs on Demand D_SONGS table? 
677
678select count(*) from d_songs
679
6802.  In how many different location types has DJs on Demand had venues?
681
682select count(distinct loc_type) from d_venues       distinct bierze tylko rozne lokalizacje
683
6844.  How many of the DJs on Demand customers have email addresses?
685
686select count(email) from d_clients
687
6887.  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.
689
690select round(avg(nvl(auth_expense_amt, 100000))) from d_partners
691
692
693
694zadanka 9-1
695
6962.  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.
697
698select min(avg(salary)), max(avg(salary))
699from employees
700group by department_id;
701
7025.  Write a query that will return both the maximum and minimum average salary grouped by department from the employees table.
703
704select avg(max(salary))
705from employees
706group by department_id;
707
708
709zadania z maila do 9.1
710
7111.
712select manager_id, job_id, count(*)
713from employees
714group by manager_id, job_id
715order by manager_id;
716
7172.
718select job_id, avg(salary)
719from employees
720group by job_id
721having count(*)>3
722
7233.
724select job_id, count(*)
725from employees
726where manager_id<>101
727group by job_id
728having avg(salary)>3000
729
7304.
731select max(salary)-min(salary) from employees
732
7335.
734select manager_id, count(manager_id)
735from employees
736where manager_id is not null
737group by manager_id
738
7396.
740select department_id
741from employees
742group by department_id
743having sum(nvl(commission_pct,0))=0
744
745
746
747
748zadanka 9-3
749
7502.  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.
751
752select employee_id, job_id, hire_date, department_id from employees
753union
754select employee_id, job_id, start_date, department_id from job_history
755
7563.  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.
757
758select employee_id, job_id, hire_date, department_id from employees
759union all
760select employee_id, job_id, start_date, department_id from job_history
761order by employee_id
762
7634.  List all employees who have not changed jobs even once. (Such employees are not found in the job_history table)
764
765select employee_id from employees
766minus
767select employee_id from job_history
768
7695.  List the employees that HAVE changed their jobs at least once.
770
771select employee_id from employees
772intersect
773select employee_id from job_history
774
775
776
777
778zadanka 10-1
779
7803.  What DJs on Demand d_play_list_items song_id’s have the same event_id as song_id 45?
781
782select song_id from d_play_list_items where event_id=(select event_id from d_play_list_items where song_id=45)
783
7844.  Which events in the DJs on Demand database cost more than event_id = 100?
785
786select id from d_events where cost>(select cost from d_events where id=100)
787
7888.  What are the names of the Global Fast Foods staff members whose staff types are not the same as Bob Miller’s?
789
790select last_name from f_staffs where staff_type!=(select staff_type from f_staffs where last_name='Miller')
791
792natatka -> jak mam where staff... to pozniej w nawiasie select (to samo zawsze) czyli staff...
793
794
795
796
797zadanka 10-2
798
7991.  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.
800
801select * from employees where salary>(select salary from employees where last_name='Lorentz')
802and
803department_id=(select department_id from employees where last_name='Abel')
804
805tutaj tak samo jak wczesniej, czyli jak jest salary to w podzapytaniu tez musi byc salary.
806
8074.  What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?
808
809select staff_type from f_staffs where salary>(select min(salary) from f_staffs where staff_type='Cook')
810
8115.  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.
812
813select department_id, avg(salary) from employees
814group by department_id
815having avg(salary)>(select salary from employees where last_name='Ernst') 
816
8176.  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.
818
819select department_id, min(salary) from employees
820group by department_id
821having min(salary)>(select min(salary) from employees where department_id<>50)
822
823
824
825
826zadanka 10-3
827
8282.  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. 
829
830select id, title, duration, artist from d_songs where TYPE_CODE in (select code from d_types where description in ('Jazz', 'Pop'))
831
8323.  Find the last names of all employees whose salaries are the same as the minimum salary for any department.
833
834select last_name from employees where salary=ANY(select min(salary) from employees group by department_id)
835
8364.  Which Global Fast Foods employee earns the lowest salary? Hint: You can use either a single-row or a multiple-row subquery.
837
838single-row
839select last_name from employees where salary=(select min(salary) from employees)
840
841multi-row
842select last_name from employees where salary<=ALL(select salary from employees)
843
8449.  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.
845
846select 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)
847and employee_id!=141
848
849
850
851
852zadanka 10-4 
853
8542.  Write a query that lists the highest earners for each department.  Include the last_name, department_id, and the salary for each employee.
855
856select last_name, department_id, salary from employees o
857where salary=(select max(salary) from employees i where i.department_id=o.department_id)
858
8593.  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.
860SELECT (enter columns here)
861FROM   (enter table name here) outer
862WHERE  'x' IN (SELECT 'x'                
863FROM   (enter table name here) inner
864WHERE  inner(enter column name here)  = inner(enter column name here)  
865Finish off the statement by sorting the rows on the department_id column.
866
867SELECT last_name, department_id, salary
868FROM   employees outer
869WHERE  'x' IN (SELECT 'x'                
870FROM   employees inner
871WHERE  inner.manager_id  = outer.employee_id)
872
873
87427.01.2020 ostatnia kartkowa z dzialow 6,8,9,10
875złaczenia, złaczenia zewnetrze, grupowanie z hevingiem min max, podzapytanie, podzapytanie skorelowane
876mozna pocwiczyc dział 11 sobie 
877
87804.02.2020 oddajemy projekty we wtorek
879
880diagram, wymagania co wyslane byly kiedys
881pozniej wyslac na maila opis slowny, diagram i cały kodzik