1: attributes in each entity
Ans:hospital Table:
FIELD NAME |
DATA TYPE |
h_code |
int(50) |
address |
Varchar(50) |
no_of_beds |
Int(50) |
customer Table:
FIELD NAME |
DATA TYPE |
cust_id |
Int(50) |
cust_name |
Varchar(50) |
address |
Varchar(50) |
email_id |
Varchar(50) |
pets Table:
FIELD NAME |
DATA TYPE |
registration_no |
Varchar(50) |
pets_name |
Varchar(50) |
species |
Varchar(50) |
birth_date |
Varchar(50) |
fee_detail Table:
FIELD NAME |
DATA TYPE |
registration_no |
Varchar(50) |
receipt_no |
Varchar(50) |
receipt_date |
Varchar(50) |
total_fee |
Varchar(50) |
doctor Table:
FIELD NAME |
DATA TYPE |
dr_id |
Int(50) |
dr_name |
Varchar(50) |
sex |
Varchar(50) |
dr_birthday |
Varchar(50) |
dr_address |
Varchar(50) |
mobile_no |
Varchar(50) |
email_id |
Varchar(50) |
specialization |
Varchar(50) |
degree |
Varchar(50) |
backup_pets |
text |
inventory Table:
FIELD NAME |
DATA TYPE |
inventory_identification_no |
Int(50) |
name |
Varchar(50) |
description |
Varchar(50) |
price |
Varchar(50) |
quantity |
Varchar(50) |
safety_stock_level |
Varchar(50) |
2): relationship between the entities
Ans: hospital.doctor_identification_no = doctor. doctor_identification_no
hospital.customer_identification_no = customer. customer_identification_no
customer.inventory_identification_no =inventory.inventory_identification_no
doctor.customer_identification_no=customer.customer_identification_no
3: Retrieve the names and IDs of all faculty members, arranged in alphabetical order by name :
Ans: Select a. FacultyName, a. FacultyID from Faculty a
Order by a.FacultyName
4: Retrieve the IDs and Names of all students taking ART103A
Ans: Select a.StudentID, a.StudentName from student a, Enrolment b
Where a. StudentID = b.StudentID
And b.CourseNumber = 'ART103A'
4: Retrieve the number of students that are taking MTH103C :
Ans: Select count (a.StudentID,) from student a, Enrolment b
Where a. StudentID = b.StudentID
And b.CourseNumber = 'MTH103C'
5: Retrieve the number of students in each course :
Ans: Select b. CourseNumber, count (a.StudentID) “Number of student “ from student a, Enrolment b
Where a. StudentID = b.StudentID
group by b. CourseNumber
6: Change the major of S1013 from Math to Null :
Ans: update Student
set Major = NULL
where StudentID = ‘S1013’
7: New record to the faculty table is:
Ans: INSERT INTO ‘faculty’ ( ‘FacultyID’, ’FacultyName’, ’Department’, ’Rank’ )
VALUES( ‘F330’, ‘Jones’, ‘CSC’, ‘Instructor’ );
STUDENT table:
Student_Name |
Student_Number |
Grade |
Mark |
936 |
13 |
Spencer |
474 |
81 |
George |
821 |
27 |
Mary |
748 |
54 |
1: SQL statements for “The complete Student table”:
Ans: SELECT * FROM STUDENT
2: Spencer’s details :
Ans: SELECT * FROM STUDENT a
Where a. Student_Name = ‘Spencer’
3: The number of students:
Ans: SELECT count (Student_Number) Number of student FROM student
4: The number of students with a grade of greater than 50:
Ans: select count (Student_Number) from student where Grade>50;
5: The details of students with a grade of greater than 50:
Ans: select * from student where Grade>50;
SELECT*FROM "PartB"."dbo"."client" ORDER BY name DESC;
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More
Follow Us