zWXq4Rju

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'