SHARE
TWEET

Untitled

a guest Dec 9th, 2019 100 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. zadanka z 2-1
  2.  
  3. zad 1
  4. The manager of Global Fast Foods would like to send out coupons for the upcoming sale.
  5. He wants to send one coupon to each household. Create the SELECT statement that returns the customer last name and a mailing address.
  6.  
  7. select distinct last_name, address from f_customers
  8.  
  9.  
  10. zad 3
  11. 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.
  12.  
  13. select ' *** '||first_name||' *** '||first_name||' *** ' "SUPER STAR" from f_staffs
  14.  
  15.  
  16. zad 5
  17. 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.
  18.  
  19. select last_name "EMPLOYEE LAST NAME", salary "CURRENT SALARY", salary*1.05 "SALARY WITH 5% RAISE" from f_staffs
  20.  
  21.  
  22. zad6
  23. Create a query that will return the structure of the Oracle database EMPLOYEES  table. Which columns are marked “nullable”? What does this mean?
  24.  
  25. describe employees
  26.  
  27. zad7
  28. The owners of DJs on Demand would like a report of all items in their D_CDs table with the following
  29. column headings: Inventory Item, CD Title, Music Producer, and Year Purchased. Prepare this report
  30.  
  31. select * from d_cds    - bez aliasów
  32.  
  33.  
  34.  
  35.  
  36. zadanka z 2-2
  37.  
  38. zad1
  39. Using the Global Fast Foods database, retrieve the customer’s first name, last name, and address for the customer who uses ID 456.
  40.  
  41. select first_name, last_name, address from f_customers where id=456
  42.  
  43. zad2
  44. Show the name, start date, and end date for Global Fast Foods' promotional item “ballpen and highlighter” giveaway.
  45.  
  46. select name, start_date, end_date from f_promotional_menus where give_away='ballpen and highlighter'
  47.  
  48. zad5
  49. The manager of DJs on Demand would like a report of all the CD titles and years of CDs that were produced before 2000.
  50.  
  51. select title, year from d_cds where year < 2000
  52.  
  53. zad7
  54. Write a SQL statement that will display the student number (studentno),
  55. first name (fname), and last name (lname) for all students who are female (F) in the table named students.
  56. kolumna znajduje sie w poleceniu
  57.  
  58. select studentno, fname, lname from students where sex = 'F'
  59.  
  60. zad10
  61. Write a SQL statement that will list the titles and years of all the DJs on Demand CDs that were not produced in 2000.
  62. select title, year from d_cds where year != 2000
  63.  
  64. zad11
  65. Write a SQL statement that lists the Global Fast Foods employees who were born before 1980.
  66.  
  67. select * from f_staffs where birthdate<'01-Jan-1980'
  68.  
  69.  
  70.  
  71. zadanka 2-3
  72.  
  73. zad1
  74. 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.
  75.  
  76. select first_name, last_name, salary from f_staffs where salary between 5 and 10
  77.  
  78.  
  79. zad4
  80. Create a list of all the DJs on Demand CD titles that have “a” as the second letter in the title.
  81.  
  82. select title from d_cds where title like '_a%'
  83.  
  84. zad5
  85. Who are the partners of DJs on Demand who do not get an authorized expense amount?
  86.  
  87. select * from d_partners where auth_expense_amt is null
  88.  
  89. zad6
  90. Select all the Oracle database employees whose last names end with “s”. Change the heading of the column to read Possible Candidates.
  91.  
  92. select last_name "Possible Candidates" from employees where last_name like '%s'
  93.  
  94. zad8
  95. Write a SQL statement that lists the songs in the DJs on Demand inventory that are type code 77, 12, or 1.
  96.  
  97. select * from d_songs where type_code in (77,12,1)
  98.  
  99.  
  100.  
  101.  
  102. zadanka 3-1
  103.  
  104. zad2
  105. Display the last names of all Global Fast Foods employees who have “e” and “i” in their last names
  106.  
  107. select last_name from f_staffs where last_name like '%e%' and last_name like '%i%'
  108.  
  109. zad3
  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.  
  112. select * from f_staffs where salary>6.5 and staff_type!='Order Taker'
  113.  
  114. zad4
  115. 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.
  116.  
  117. select last_name from employees where last_name like 'D%e%' and last_name like 'D%a%'
  118. select last_name from employees where last_name like 'D%e%a%' or last_name like 'D%a%e%'
  119.  
  120.  
  121.  
  122.  
  123. zadanka 3-2
  124.  
  125. zad1
  126. 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.
  127. SELECT employee_id, first_name, last_name
  128. FROM employees;
  129.  
  130. odp:
  131. SELECT employee_id "Number", first_name, last_name
  132. FROM employees
  133. order by "Number"
  134.  
  135. zad2
  136. Create a query that will return all the DJs on Demand CD titles ordered by year with titles in alphabetical order by year
  137.  
  138. select year,title from d_cds order by year, title
  139.  
  140. zad3
  141. Order the DJs on Demand songs by descending title. Use the alias “Our Collection” for the song title
  142.  
  143. select title "Our Collection" from d_songs order by title DESC
  144.  
  145.  
  146.  
  147. zadanka 4-1
  148.  
  149. zad3
  150. What is the length of the string “Oracle Internet Academy”?
  151.  
  152. select length('Oracle Internet Academy') from dual
  153.  
  154. zad4
  155. What’s the position of “I” in “Oracle Internet Academy”?
  156.  
  157. select instr('Oracle Internet Academy','I') from dual
  158.  
  159. zad5
  160. Starting with the string “Oracle Internet Academy”, pad the string to create  ****Oracle****Internet****Academy****
  161.  
  162. select rpad(lpad('Oracle',10,'*'),14,'*') || rpad('Internet',12,'*') || rpad('Academy',11,'*') from dual
  163.  
  164. zad9
  165. Write a query that will output a column called “ADDRESS” which has the following information:
  166. ZOE TWEE 1009 OLIVER AVENUE BOSTON, MA 12889. Use the Global Fast Foods F_CUSTOMERS table.
  167.  
  168. select upper(first_name)||' '||upper(last_name)||' '||upper(address)||' '||upper(city)||' '||upper(state)||' '||zip "address" from f_customers
  169.  
  170. zad10
  171. 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.  
  172. 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.  
  173. Run the query for department 30 and 50 without changing the original where-clause in your statement
  174.  
  175. select substr(first_name,1,1)||last_name "Name", salary, department_id from employees where department_id = :podaj_dept
  176.  
  177.  
  178.  
  179.  
  180. zadanka 4-2
  181.  
  182. 1.  Display Oracle database employee last_name and salary for employee_ids between 100 and 102.
  183. Include a third column that divides each salary by 1.55 and rounds the result to two decimal places.
  184.  
  185. select last_name, salary, round(salary/1.55,2) from employees where employee_id between 100 and 102
  186.  
  187. 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.
  188.  
  189. select last_name, salary, trunc(salary*1.05333,2) from employees where department_id = 80
  190.  
  191. 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.
  192.  
  193. select last_name, salary, mod(salary,3) from employees where mod(salary,3)=0
  194.  
  195.  
  196.  
  197.  
  198. zadanka 4-3
  199.  
  200. 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.
  201.  
  202. select round(months_between(sysdate, event_date)) from d_events where name='Vigil wedding'
  203.  
  204. 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.”
  205.  
  206. select months_between('01-Oct-2019','20-Jun-2019')*30.5 as "Days" from dual
  207.  
  208. 5.   What is the last day of the month for June 2005? Use an alias for the output.
  209.  
  210. select last_day('15-Jun-2005') as"LasTDAy" from dual
  211.  
  212. 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.”
  213.  
  214. select add_months(sysdate,6) as"Appointment." from dual
  215.  
  216. 10. What’s the date of the next Friday after your birthday this year? Name the output, “First Friday.”
  217.  
  218. select next_day(sysdate, 'Friday') as"First Friday." from dual
  219.  
  220.  
  221.  
  222.  
  223. zadanka 5-1
  224.  
  225. 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.
  226.  
  227. select last_name, to_char(birthdate,'fmMonth DD, YYYY') from f_staffs
  228.  
  229. 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.
  230.  
  231. select 'The promotion began on the '||to_char(start_date,'ddspth "of" Month YYYY') from f_promotional_menus where code=110
  232.  
  233. 5.  List the ID, name, and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places.
  234.  
  235. select id, last_name, to_char(salary, '$99.99') from f_staffs
  236.  
  237. 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)
  238. :December 25th, 2004      DECEMBER 25TH, 2004      25th december, 2004
  239.  
  240. select to_char(sysdate, 'Month ddth, YYYY') from dual
  241. select to_char(sysdate, 'MONTH DDTH, YYYY') from dual
  242. select to_char(sysdate, 'ddth month, YYYY') from dual
  243.  
  244.  
  245.  
  246. zadanka 5-2
  247.  
  248. 1.  Create a report that shows the Global Fast Foods promotional name, start date, and end date from the f_promotional_menus table.
  249. 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.
  250.  
  251. select name, start_date, NVL2(end_date, 'end in two weeks', sysdate)  from f_promotional_menus
  252.  
  253. 2.  Not all Global Fast Foods staff members receive overtime pay. Instead of displaying a null value for these employees, replace null with zero.
  254. Include the employee’s last name and overtime rate in the output. Label the overtime rate as “Overtime Status”.
  255.  
  256. select last_name, overtime_rate, NVL(overtime_rate,0) "Overtime Status" from f_staffs
  257.  
  258. 7.  
  259. 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).
  260. b)  Modify the report to display null if the month of hire is September. Use the NULLIF function.
  261.  
  262. a) select first_name, last_name, to_char(hire_date, 'Month') from employees
  263. b) select first_name, last_name, NULLIF(to_char(hire_date, 'Month'),'September') from employees
  264.  
  265.  
  266. zadanka 5-3
  267.  
  268. 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”.
  269.  
  270. select title, decode(duration,'2 min','shortest','10 min','longest') "Play Times" from d_songs
  271.  
  272. 2.  Use the Oracle database employees table and CASE expression to decode the depart-ment id.
  273. Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions:
  274. If the department id is 10 then 1.25 * salary
  275. If the department id is 90 then 1.5 * salary
  276. If the department id is 130 then 1.75 * salary
  277. Otherwise, display the old salary.
  278.  
  279. select last_name, salary,
  280. case 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
  285. end "New Salary"
  286. from employees 
  287.  
  288. 3.  Display the first name, last name, manager ID, and commission percentage of all employ-ees in departments 80 and 90.
  289. 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.
  290.  
  291. select 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.  
  294. zadanka 12-1
  295.  
  296. 2.  DJs on Demand just purchased four new CDs. Use an explicit INSERT statement to add each CD to the copy_d_cds table.
  297. After completing the entries, execute a SELECT * statement to verify your work.
  298.  
  299. create table copy_d_cds as select * from d_cds
  300. insert into copy_d_cds values (97, 'Celebrate the Day','R&B Inc.','2003')
  301. insert into copy_d_cds values (98, 'Holiday Tunes for All Ages','Tunes are Us','2004')
  302. insert into copy_d_cds values (99, 'Party Music','Old Town Records','2004')
  303. insert into copy_d_cds values (100, 'Best of Rock and Roll','Old Town Records','2004')
  304.  
  305. 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.
  306. 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.
  307.  
  308. create table copy_d_songs as select * from d_songs
  309. insert into copy_d_songs values(52,'Surfing Summer','Not known',null,12)
  310. insert into copy_d_songs (ID, Title, Duration, Type_Code) values(53,'Victory Victory','5 min',12)    drugi sposob bez null
  311.  
  312. 6.
  313. Create a table called rep_email using the following statement:
  314. 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))
  315.  
  316. insert into rep_email
  317. select employee_id, first_name, last_name, email from employees where job_id like '%REP%'
  318. select employee_id, first_name, last_name, email from employees where job_id like '%REP%'
  319.  
  320.  
  321. zadanka 12-2
  322.  
  323. 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.
  324.  
  325. create table copy_f_food_items as select * from f_food_items
  326.  
  327. update copy_f_food_items
  328. set price=3.75
  329. where description='Strawberry Shake'
  330.  
  331. update copy_f_food_items
  332. set price=1.20
  333. where description='Fries'
  334.  
  335. 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?)
  336.  
  337. create table copy_f_staffs as select * from f_staffs
  338.  
  339. update copy_f_staffs
  340. set overtime_rate=nvl(overtime_rate,0)+0.75
  341. where first_name='Bob' and last_name='Miller'
  342.  
  343. update copy_f_staffs
  344. set overtime_rate=nvl(overtime_rate,0)+0.85
  345. where first_name='Sue' and last_name='Doe'
  346.  
  347. 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.
  348.  
  349. update copy_f_staffs
  350. set salary=(select salary from copy_f_staffs where first_name='Bob' and last_name='Miller')
  351. where first_name='Sue' and last_name='Doe'
  352.  
  353. 8.  Execute the following SQL statement. Record your results.
  354. DELETE from departments
  355. WHERE department_id = 60;
  356.  
  357. 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.
  358.  
  359. DELETE from copy_f_staffs
  360. where first_name='Kim' and last_name='Kai'
  361.  
  362.  
  363. zadanka 12-3   tego na kartkowce niue bedzie w sensie ze insert all
  364.  
  365. 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:
  366. a.  In your schema, Make a copy of the Global Foods F_PROMOTIONAL_MENUS table using the following SQL statement:
  367. CREATE TABLE copy_f_promotional_menus
  368. AS (SELECT * FROM f_promotional_menus)
  369. b.  Alter the current START_DATE column attributes using:
  370. ALTER TABLE copy_f_promotional_menus
  371. MODIFY(start_date DATE DEFAULT SYSDATE)
  372. 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?
  373.  
  374. CREATE TABLE copy_f_promotional_menus
  375. AS (SELECT * FROM f_promotional_menus)
  376. -----------------------------------
  377. ALTER TABLE copy_f_promotional_menus
  378. MODIFY(start_date DATE DEFAULT SYSDATE)
  379. ----------------------------------------
  380. insert into copy_f_promotional_menus
  381. values(120,'New Customer',default,'01-Jun-2005','10% discount coupon')
  382. ------------------------------------------
  383. select * from copy_f_promotional_menus
  384.  
  385. 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.
  386.  
  387. tworze podane tabele
  388.  
  389. 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.
  390.  
  391. insert 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)
  397. select employee_id, hire_date, salary, manager_id from employees
  398.  
  399.  
  400.  
  401. zadanka 13-1
  402.  
  403. 2.  Write the syntax to create the grad_candidates table.
  404.  
  405. create table GRADUATE_CANDIDATE (student_id number(6), last_name varchar2(50), first_name varchar2(50),credits number(3),graduation_date date)
  406.  
  407. 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.
  408.  
  409. create table smith_table as
  410. select * from GRADUATE_CANDIDATE
  411.  
  412. 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
  413.  
  414. select * from USER_TABLES
  415. select * from USER_OBJECTS
  416. select * from USER_CATALOG
  417.  
  418.  
  419.  
  420. zadanka 13-3
  421.  
  422. 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
  423. modyfikacja istniejacej tabeli na kartkowce bedzie (polecenia: alter, drop, create)
  424. alter table o_employees
  425. add (Termination VARCHAR2(50) default to_char(sysdate,'MONTH ddth, yyyy'))
  426.  
  427. 5.   Truncate the o_jobs table. Then do a SELECT * statement. Are the columns still there? Is the data still there?
  428.  
  429. select * from o_jobs
  430. truncate table o_jobs
  431. select * from o_jobs
  432.  
  433. 9.   Rename the o_jobs table to o_job_description
  434.  
  435. rename o_jobs to o_jobs_description
  436. select * from o_jobs_description
  437.  
  438. 10. F_staffs table exercises:
  439. 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.
  440. b.   Describe the new table to make sure it exists.
  441. c.   Drop the table.
  442. d.   Try to select from the table.
  443. e.   Investigate your recyclebin to see where the table went.
  444. 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”.
  445. g.   Undrop the table.
  446. h.   Describe the table.
  447.  
  448. create table copy_f_staffs
  449. as select * from f_staffs
  450.  
  451. drop table copy_f_staffs
  452. select * from copy_f_staffs
  453. zobaczyc czy jest w koszu select * from user_recyclebin
  454.  
  455. flashback table copy_f_staffs to before drop
  456. select * from copy_f_staffs
  457.  
  458. drop 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.  
  464. zadanka 14-1
  465.  
  466. 6.  Write the CREATE TABLE statement for the Global Fast Foods locations table to define the constraints at the column level.
  467.  
  468. create table Global_Fast_Foods_Locations(
  469. id NUMBER primary key,
  470. name varchar2(50),
  471. date_opened DATE NOT NULL,
  472. address varchar2(50)NOT NULL,
  473. city varchar2(50)NOT NULL,
  474. zip varchar2(50),
  475. phone varchar2(50),
  476. email varchar2(50) UNIQUE,
  477. manager_id NUMBER,
  478. emergency_contact varchar2(50))
  479.  
  480. 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.
  481.  
  482. create table Global_Fast_Foods_Locations(
  483. id NUMBER primary key,
  484. name varchar2(50),
  485. date_opened DATE NOT NULL,
  486. address varchar2(50)NOT NULL,
  487. city varchar2(50)NOT NULL,
  488. zip varchar2(50),
  489. phone varchar2(50),
  490. email varchar2(50),
  491. manager_id NUMBER,
  492. emergency_contact varchar2(50),
  493. CONSTRAINT email_uk UNIQUE(email)
  494. )
  495.  
  496.  
  497.  
  498.  
  499. zadanka 14-2
  500.  
  501. każda tabela powinnna miec klucz główny, nie moze byc nullem
  502.  
  503. 3.  Create the animals table. Write the syntax you will use to create the table.
  504. create table animals(
  505. animal_id NUMBER(6) PRIMARY KEY,
  506. name VARCHAR2(25),
  507. license_tag_number NUMBER(10) UNIQUE,
  508. admit_date DATE NOT NULL,
  509. adoption_id NUMBER(5) NOT NULL,
  510. vaccination_date DATE)
  511.  
  512. 4.  Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to the graphic below for input
  513.  
  514. insert into animals values (101, 'Spot', 35540, '10-Oct-2004', 205, '12-Oct-2004')
  515.  
  516. 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.
  517.  
  518. create table animals(
  519. animal_id NUMBER(6) PRIMARY KEY,
  520. name VARCHAR2(25),
  521. license_tag_number NUMBER(10) UNIQUE,
  522. admit_date DATE NOT NULL,
  523. adoption_id NUMBER(5) REFERENCES adoptions(adoption_id),
  524. vaccination_date DATE NOT NULL)
  525.  
  526.  
  527. ZADANIE DODOTKOWE do 14-2 wiezy integralności
  528.  
  529. tworzymy 2 tablele ksiazka i wydawnictwo i nadjemy im pewne uprawnienia
  530.  
  531. create table wydawnictwo(
  532. id_wyd NUMBER PRIMARY KEY,
  533. rok NUMBER CHECK (rok like '____'))
  534.  
  535. create table ksiazka(
  536. id NUMBER PRIMARY KEY,
  537. tytul VARCHAR2(50) NOT NULL,
  538. cena NUMBER CHECK (cena>0),
  539. id_wyd NUMBER REFERENCES wydawnictwo(id_wyd))
  540.  
  541.  
  542.  
  543. zadanka 14-3
  544.  
  545. 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?
  546.  
  547. create table copy_d_clients as select * from d_clients
  548.  
  549. alter table copy_d_clients
  550. add constraint copy_d_clients_pk PRIMARY KEY(client_number)
  551.  
  552. 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?
  553.  
  554. create table copy_d_events as select * from d_events
  555.  
  556. alter table copy_d_events
  557. add constraint nnnnnn FOREIGN KEY(client_number) references copy_d_clients(client_number)
  558.  
  559.  
  560.  
  561. ----------------------------------------------------------------------------------------------
  562. 16.12.2019 Kartkówka z działów 12,13,14
  563. Insert, update, delate
  564. create table, alter table
  565. foreign, primary key, check, not unique, not null
  566. ----------------------------------------------------------------------------------------------
  567.  
  568.  
  569. zadanka 6-1
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top