Structured Query Language

 

Goal: Create a Referential Integrity constraint.

Using the class example database (SQLFUN):

• Create a Referential Integrity constraint between the valid_phone_numbers table and the L_employees table. (This validates the phone_number value whenever a new row is added to the L_employees table.)
• Add one more value to this table: 4707.
• Create a new table, valid_phone_numbers, containing the phone numbers for all the employees. (Get this list of valid values from the phone_number column of the L_employees table.)

Goal: Use the date functions. Construct and use a table of constants.

• Find the number of days between December 21, 2020 (winter solstice), and March 21, 2021 (spring equinox).

Put both dates in a table of constants, so they both have a datatype of date.

Starting points:

Oracle SQL

drop table ex0904a; create table ex0904a (beg_date date, end_date date); insert into ex0904a values ('21-dec-2020', '21-mar-2021'); commit; select end_date - beg_date as number_of_days from ex0904a;

 

mySQL:

• drop table ex0904a;
• create table ex0904a
• (beg_date date,
• end_date date);
• insert into ex0904a
• values ('2020-21-21', '2021-03-21');
• commit;
• select end_date - beg_date as number_of_days from ex0904a;

 

 

create database sqlfun;commit;use sqlfun;CREATE TABLE IF NOT EXISTS L_EMPLOYEES(EMPLOYEE_IDINT NOT NULL PRIMARY KEY AUTO_INCREMENT,FIRST_NAMEVARCHAR(10),LAST_NAMEVARCHAR(20),DEPT_CODEVARCHAR(3),HIRE_DATEDATE,CREDIT_LIMITDECIMAL(4,2),PHONE_NUMBERVARCHAR(4),MANAGER_IDDECIMAL(3));COMMIT;ANALYZE TABLE L_EMPLOYEES;CREATE TABLE IF NOT EXISTS L_FOODS(SUPPLIER_IDVARCHAR(3),PRODUCT_CODEVARCHAR(2),MENU_ITEMINT(2),DESCRIPTIONVARCHAR(20),PRICEDECIMAL(4,2),PRICE_INCREASEDECIMAL(4,2),PRIMARY KEY (SUPPLIER_ID, PRODUCT_CODE));COMMIT;ANALYZE TABLE L_FOODS;-- CREATE THE L_DEPARTMENTS TABLECREATE TABLE IF NOT EXISTS L_DEPARTMENTS(DEPT_CODEVARCHAR(3) PRIMARY KEY,DEPARTMENT_NAMEVARCHAR(30));COMMIT;ANALYZE TABLE L_DEPARTMENTS;CREATE TABLE IF NOT EXISTS L_LUNCHES(LUNCH_IDINT(3) PRIMARY KEY,LUNCH_DATEDATE,EMPLOYEE_IDINT(3),DATE_ENTEREDDATE);COMMIT;ANALYZE TABLE L_LUNCHES;CREATE TABLE IF NOT EXISTS L_LUNCH_ITEMS(LUNCH_IDINT(3),ITEM_NUMBERINT(2),SUPPLIER_IDVARCHAR(3),PRODUCT_CODEVARCHAR(2),QUANTITYINT(1));ALTER TABLE L_LUNCH_ITEMSADD PRIMARY KEY (LUNCH_ID, ITEM_NUMBER);

ComputerScienceExpert

(11)

Status NEW Posted 20 May 2017 08:05 AM My Price 9.00

-----------

Attachments

file 1495269958-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer