SQL help Homework #1-Creating Tables Create a database with the following tables

Question

SQL help

Homework #1-Creating Tables Create a database with the following tables, correctly identifying the data type for each field and implementing constraints as described. Primary keys and relationships will be added in the next assignment. Operators (Seniority Number, First Name, Last Name, Hire Date). All fields are required. Seniority number is four digits. Hire date must be less than tomorrow. Trips(Route Number, Start Location, Start Time, End Location, End Time, Effective Date) All fields are required. End Time must be greater than Start Time. Effective Date must be on or after January 1, 2000. Vehicle(Manufacturer, Model, Model Year, Purchase Date). Default value for Manufacturer is Gillig'; default value for Model Year is the current year. Model year must be less than or equal to the current year Turn in this file with your SQL code and screen prints.

Explanation / Answer

1.Operator Table )

create table Operators(seniority_Number int not null,first_name varchar(20) not null,last_name varchar(20) not null,hire_date date not null);

alter table Operators add constraint check_dates check(hiredate<=now());

2.Trips Table )

create table trips(route_num int not null,start_loc varchar(20) not null,start_time time not null,end_loc varchar(20) not null,end_time time not null,effective_date date not null);

alter table trips add constraint check_times check(start_time<end_time);

alter table trips add constraint check_dates check(effective_date>2000-01-01);

3.Vechicle Table )

create table vehicle(manuf varchar(20) default "Gilling",model varchar(20),model_year int default 2018,purchase_date date);

alter table vehicle add constraint check(model_year<=cast(year(now()) as int));

// here now() function will return present date and time .then with year(now()) function I'm taking present year and then I'm converting or casting it as integer using cast() function.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.