1. Create faculty and dependants tables. CREATE TABLE Faculty ( FacultyID int ID
ID: 3740718 • Letter: 1
Question
1. Create faculty and dependants tables.
CREATE TABLE Faculty (
FacultyID int IDENTITY(1000,1) PRIMARY KEY,
FacFirstname VARCHAR (30) NOT NULL,
FacLastname VARCHAR (30) NOT NULL,
FacAddress VARCHAR (100),
FacCity VARCHAR (30),
FacState CHAR (2),
FacZIP CHAR (12),
FacSex VARCHAR (6),
FacSalary MONEY, /* This allows for US currency to be entered */
FacHireDate DATE NOT NULL DEFAULT GETDATE(),
);
CREATE TABLE Dependants(
DependantID int IDENTITY(1000,1) PRIMARY KEY,
DepFirstname VARCHAR (30) NOT NULL,
DepLastname VARCHAR (30) NOT NULL,
FacultyID int NOT NULL,
CONSTRAINT FK_FacultyID FOREIGN KEY (FacultyID)
REFERENCES Faculty(FacultyID) ON DELETE CASCADE
)
2. Add the data (at least 3 records) into Faculty table using the insert SQL command.
3. Add the data (at least 5 records) into Dependants table using the insert SQL command.
4. Get Faculty names (fistname and last name) and FacultyIds of all faculty members, arranged in alphabetical order by name.
5. Create a view table that retrieve all records of faulty table that the salary is between 50000 and 90000.
6. Find the sum of the salaries of all faculties, as well as the maximum salary, the minimum salary, and the average salary.
7. Change the salary of FacultyID 1004 to 89500.
8. For each faculty, retrieve the faculty's ID, first and last name and the first name and ID of his or her dependants.
Explanation / Answer
2.Ans-:
Row 1
Insert into Faculty ( FacultyID, FacFirstname,FacLastname,FacAddress , FacCity,FacState,FacZIP,FacSex, FacSalary,FacHireDate) values(1004,'Kim','John','Tower-C,Plot-1','New York','New York','220','Male',60000,'2015-12-17');
Row 2
Insert into Faculty ( FacultyID, FacFirstname,FacLastname,FacAddress , FacCity,FacState,FacZIP,FacSex, FacSalary,FacHireDate) values(1005 ,'Angela','Pear','Tower-B,Plot-88','San Jose','California','102','Female',70000,'2014-10-11');
Row 3
Insert into Faculty ( FacultyID, FacFirstname,FacLastname,FacAddress , FacCity,FacState,FacZIP,FacSex, FacSalary,FacHireDate) values(1006,'Jack','Johnson','Q Road,Flat No-:101','San Jose','California','102','Male',40000,'2011-02-05');
3.answer-:
!st row
insert into Dependants ( DependantID,DepFirstname,DepLastname, FacultyID) values(9000,'Kortana','Lewis',1004);
2nd row
insert into Dependants ( DependantID,DepFirstname,DepLastname, FacultyID) values(8000,'July','Hawk',1005);
3rd row
insert into Dependants ( DependantID,DepFirstname,DepLastname, FacultyID) values(7000,'Andrew','Lewis',1006);
4th row
insert into Dependants ( DependantID,DepFirstname,DepLastname, FacultyID) values(6000,'Jenny','Paul',1006);
5th row
insert into Dependants ( DependantID,DepFirstname,DepLastname, FacultyID) values(5000,'Kortana','Lobo',1004);
4.Ans-:Select FacFirstname, FacLastname, FacultyID order by FacFirstname ASC,FacLastname ASC;
5.Ans-:CREATE VIEW facultyview AS SELECT FacultyID, FacFirstname,FacLastname,FacAddress , FacCity,FacState,FacZIP,FacSex, FacSalary,FacHireDate FROM Faculty
WHERE FacSalary BETWEEN 50000 AND 90000;
6.Ans-: SELECT ROUND(MAX(FacSalary),0) 'Maximum', ROUND(MIN(FacSalary),0) 'Minimum', ROUND(SUM(FacSalary),0) 'Sum', ROUND(AVG(FacSalary),0) 'Average' FROM Faculty;
7.Ans-: update Faculty set FacultyID=89500 where FacultyID=1004;
8.Ans-: SELECT f.FacFirstname, f.FactLastname ,d.DepFirstname,d. DepLastname FROM Faculty f, Dependants d WHERE f.FacultyID = d.FacultyID;
If got your question right you want to do something like
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.